Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Mon, Dec 7, 2020 at 8:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If foo.id is a primary key, it knows that the "group by" doesn't really
>> merge any rows of foo, so it lets you get away with that. I think this
>> is actually required by spec, but am too lazy to go check right now.
> If I do that without the Primary Key, it does indeed complain about f1 & f2
> not being grouped. But what is the "It" in "it lets you get away with
> that" referring to?
Sorry I was vague there, it's the parse analysis phase that understands
that "GROUP BY a primary key" should be treated as allowing any column of
that pkey's table to be referenced without also explicitly grouping by
that other column. If you then join to some other table, the free pass
doesn't extend to the other table.
> Or more specifically, is this some specialized case
> because of something related to use of the jsonb_recordset function?
Nope, unrelated to that.
> I've
> gotten so used to having to group on every non-aggregate field that I
> didn't realize there could be any exception to that.
We did not use to have this bit of logic, so maybe your habits were
formed a few years ago. But as I said, I think the SQL spec says
this should be OK. Definitely, there are other DBMSes that also
allow it.
regards, tom lane