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

On Friday, October 8, 2021, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Narayanan Iyer" <nars(at)yottadb(dot)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.