On Friday, October 8, 2021, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
"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).
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.