Re: [PATCH] GROUP BY ALL

On 7/22/24 15:43, Tom Lane wrote:
> Isaac Morland <isaac(dot)morland(at)gmail(dot)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(at)illuminatedcomputing(dot)com