GROUP BY using tablename.* does not work if tablename has 1 column with NULL values

From: "Narayanan Iyer" <nars(at)yottadb(dot)com>
To: <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc: <support(at)yottadb(dot)com>
Subject: GROUP BY using tablename.* does not work if tablename has 1 column with NULL values
Date: 2021-10-08 15:31:57
Message-ID: 0e0301d7bc59$a1e416b0$e5ac4410$@yottadb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jeff Janes 2021-10-08 16:03:23 Re: PostgreSQL 12 Authentication type questions.
Previous Message Sehrope Sarkuni 2021-10-08 12:44:26 Re: BUG #17216: No Password Provided Error - uncaught exception