From: Narayanan Iyer <nars@yottadb.com>
To: pgsql-bugs@lists.postgresql.org
Cc: support@yottadb.com
Subject: GROUP BY using tablename.* does not work if tablename has 1 column with NULL values
Date: Fri, 8 Oct 2021 11:31:57 -0400
Message-ID: <0e0301d7bc59$a1e416b0$e5ac4410$@yottadb.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: nars@yottadb.com, pgsql-bugs@lists.postgresql.org, support@yottadb.com
Subject: Re: GROUP BY using tablename.* does not work if tablename has 1 column with NULL values
In-Reply-To: <0e0301d7bc59$a1e416b0$e5ac4410$@yottadb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox