Message-ID: <376ba068-4f12-4ee0-99d8-e19defbb6d29@illuminatedcomputing.com>
Date: Tue, 23 Jul 2024 10:02:36 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [PATCH] GROUP BY ALL
To: pgsql-hackers@lists.postgresql.org
References: <CAHM0NXjz0kDwtzoe-fnHAqPB1qA8_VJN0XAmCgUZ+iPnvP5LbA@mail.gmail.com>
 <CAKFQuwY0vhNG8T+pC3BQJurFi3NN_L1KbEPGagRN3V5nKZcpDQ@mail.gmail.com>
 <CAMsGm5eUihhgcNqDr1h=PwRZxt=3GUnOHqAhvam8X7j2jioaeQ@mail.gmail.com>
 <932963.1721688221@sss.pgh.pa.us>
Content-Language: en-US
From: Paul Jungwirth <pj@illuminatedcomputing.com>
In-Reply-To: <932963.1721688221@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/376ba068-4f12-4ee0-99d8-e19defbb6d29%40illuminatedcomputing.com>
Precedence: bulk

On 7/22/24 15:43, Tom Lane wrote:
> Isaac Morland <isaac.morland@gmail.com> writes:
>> And for when this might be useful, the syntax for it already exists,
>> although a spurious error message is generated:
> 
>> odyssey=> select (uw_term).*, count(*) from uw_term group by uw_term;
>> ERROR:  column "uw_term.term_id" must appear in the GROUP BY clause or be
>> used in an aggregate function
>> LINE 1: select (uw_term).*, count(*) from uw_term group by uw_term;
>>                  ^
> 
>> I'm not sure exactly what's going on here
> 
> The SELECT entry is expanded into "uw_term.col1, uw_term.col2,
> uw_term.col3, ...", and those single-column Vars don't match the
> whole-row Var appearing in the GROUP BY list.  I guess if we
> think this is important, we could add a proof rule saying that
> a per-column Var is functionally dependent on a whole-row Var
> of the same relation.  Odd that the point hasn't come up before
> (though I guess that suggests that few people try this).

I was just using this group-by-row feature last week to implement a temporal outer join in a way 
that would work for arbitrary tables. Here is some example SQL:

https://github.com/pjungwir/temporal_ops/blob/b10d65323749faa6c47956db2e8f95441e508fce/sql/outer_join.sql#L48-L66

That does `GROUP BY a` then `SELECT (x.a).*`.[1]

It is very useful for writing queries that don't want to know about the structure of the row.

I noticed the same error as Isaac. I worked around the problem by wrapping it in a subquery and 
decomposing the row outside. It's already an obscure feature, and an easy workaround might be why 
you haven't heard complaints before. I wouldn't mind writing a patch for that rule when I get a 
chance (if no one else gets to it first.)

[1] Actually I see it does `GROUP BY a, a.valid_at`, but that is surely more than I need. I think 
that `a.valid_at` is leftover from a previous version of the query.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com