Re: [HACKERS] GROUP BY fixes committed

Bruce Momjian <maillist@candle.pha.pa.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