From: Narayanan Iyer <nars@yottadb.com>
To: 'David G. Johnston' <david.g.johnston@gmail.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
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, 8 Oct 2021 13:37:19 -0400
Message-ID: <0f6b01d7bc6b$25561500$70023f00$@yottadb.com> (raw)
In-Reply-To: <CAKFQuwabrzFa=TEmHwKHXFJuohMcZqnscJGFjB5Fa0P98wdRJQ@mail.gmail.com>
References: <0e0301d7bc59$a1e416b0$e5ac4410$@yottadb.com>
<236280.1633710901@sss.pgh.pa.us>
<CAKFQuwabrzFa=TEmHwKHXFJuohMcZqnscJGFjB5Fa0P98wdRJQ@mail.gmail.com>
David,
This query was the simplest I could come up with to illustrate what I thought was the issue. It is not a real world query.
Narayanan.
From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Friday, October 8, 2021 12:51 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Narayanan Iyer <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
On Friday, October 8, 2021, Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us> > wrote:
"Narayanan Iyer" <nars@yottadb.com <mailto: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).
SELECT *, t1.*::record FROM tmp t2 LEFT JOIN (SELECT lastName FROM tmp) AS t1 ON (t2.firstName <= 'Acid');
The third row comes from failing to join t2 Joey,NULL to anything.
This is the part that seems the most unusual (from a “why did you write the query that way” perspective, not the result). A left join’s on clause does not act as a filter for the left side table, so a record with t1.firstName=Joey can still be output. So, on the whole, this is just a poorly written query that takes too much effort for someone to understand due to the non-traditional use of left join and an on clause that doesn’t actually join and oddly decides to restrict the left side.
The fact that nulls are not counted and are also not equal to each other in group by does indeed explain the rest.
David J.
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, david.g.johnston@gmail.com, tgl@sss.pgh.pa.us, 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: <0f6b01d7bc6b$25561500$70023f00$@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