From: Tom Lane <tgl@sss.pgh.pa.us>
To: Narayanan Iyer <nars@yottadb.com>
Cc: pgsql-bugs@lists.postgresql.org
Cc: support@yottadb.com
Subject: Re: GROUP BY using tablename.* does not work if tablename has 1 column with NULL values
Date: Fri, 08 Oct 2021 12:35:01 -0400
Message-ID: <236280.1633710901@sss.pgh.pa.us> (raw)
In-Reply-To: <0e0301d7bc59$a1e416b0$e5ac4410$@yottadb.com>
References: <0e0301d7bc59$a1e416b0$e5ac4410$@yottadb.com>
"Narayanan Iyer" <nars@yottadb.com> writes:
> 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).
I don't see any bug here. If you take out the grouping it's a bit easier
to see what's going on:
SELECT *, t1.*::record FROM tmp t2 LEFT JOIN (SELECT lastName FROM tmp) AS t1 ON (t2.firstName <= 'Acid');
id | firstname | lastname | lastname | t1
----+-----------+----------+----------+--------
1 | Acid | Burn | Burn | (Burn)
1 | Acid | Burn | | ()
2 | Joey | | |
(3 rows)
The first row comes from joining t2 Acid,Burn to t1 Acid,Burn.
The second row comes from joining t2 Acid,Burn to t1 Joey,NULL.
The third row comes from failing to join t2 Joey,NULL to anything.
So in the second row, there is a matched t1 row, and t1.*
therefore represents a non-null composite value that happens
to contain one null field. In the third row, there is no
matched t1 row at all, so we consider that t1.* is a composite NULL,
which is different from a composite containing NULL fields.
You can argue about whether composite NULL ought to be considered
identical to ROW(NULL). But there are a lot of contexts where
that would be a bad idea, and very few where it'd be good.
> 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.
You might be able to get the results you want by explicitly
converting to row-containing-nulls, which is not very hard:
SELECT *, row(t1.*) FROM tmp t2 LEFT JOIN (SELECT lastName FROM tmp) AS t1 ON (t2.firstName <= 'Acid');
id | firstname | lastname | lastname | row
----+-----------+----------+----------+--------
1 | Acid | Burn | Burn | (Burn)
1 | Acid | Burn | | ()
2 | Joey | | | ()
(3 rows)
regards, tom lane
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: tgl@sss.pgh.pa.us, 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: <236280.1633710901@sss.pgh.pa.us>
* 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