Re: [HACKERS] GROUP BY fixes committed

Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> writes:
> Nice summary. Where are we on the last item.

It was still broken as of a day or two ago.

I poked at it some more, and concluded that INSERT ... SELECT is pretty
broken when the SELECT includes GROUP BY. I didn't try to delve into
the code though, just experimented with different commands. Here are my
notes:

TEST CONDITION:
CREATE TABLE "si_tmpverifyaccountbalances" (
"type" int4 NOT NULL,
"memberid" int4 NOT NULL,
"categoriesid" int4 NOT NULL,
"amount" numeric);
CREATE TABLE "invoicelinedetails" (
"invoiceid" int4,
"memberid" int4,
"totshippinghandling" numeric,
"invoicelinesid" int4);

ACCEPTED:
insert into si_tmpVerifyAccountBalances select invoiceid+3,
memberid, 1, TotShippingHandling from InvoiceLineDetails
group by invoiceid+3, memberid;

NOT ACCEPTED:
insert into si_tmpVerifyAccountBalances select invoiceid+3,
memberid, 1, TotShippingHandling from InvoiceLineDetails
group by invoiceid+3, memberid, TotShippingHandling;

Probably error check is including GROUP BY targets in its count of
things-to-be-inserted :-(. The behavior is quite inconsistent though.
Also, why doesn't the first example get rejected, since
TotShippingHandling is neither GROUP BY nor an aggregate??

regards, tom lane