Hi,
In the below example, t1 points to a table with just 1 column (lastName) and so I expect the 2 SELECT queries (pasted below) using t1.lastName or t1.* syntax to produce the exact same results. But the latter produces one extra row of output (3 rows vs 2 rows).
$ psql --version
psql (PostgreSQL) 13.4 (Ubuntu 13.4-0ubuntu0.21.04.1)
$ psql db
db=> DROP TABLE IF EXISTS tmp;
DROP TABLE
db=> CREATE TABLE tmp (id INTEGER PRIMARY KEY, firstName VARCHAR(30), lastName VARCHAR(30));
CREATE TABLE
db=> INSERT INTO tmp VALUES (1,'Acid','Burn');
INSERT 0 1
db=> INSERT INTO tmp VALUES (2,'Joey',NULL);
INSERT 0 1
db=> SELECT COUNT(t1.lastName) FROM tmp t2 LEFT JOIN (SELECT lastName FROM tmp) AS t1 ON (t2.firstName <= 'Acid') GROUP BY t1.lastName;
count
0
1
(2 rows)
db=> SELECT COUNT(t1.*) FROM tmp t2 LEFT JOIN (SELECT lastName FROM tmp) AS t1 ON (t2.firstName <= 'Acid') GROUP BY t1.*;
count
1
1
0
(3 rows)
It seems like a Postgres bug to me. Not sure if there is a Postgres setting that I need to enable in order to avoid this discrepancy.
Let me know if you need any more information.
Thanks,
Narayanan.
