Re: [HACKERS] Partial fix for INSERT...SELECT problems

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Tom, is this fixed?

Yes, for 6.6.

>> I have committed some fixes that prevent resjunk targets from being
>> assigned to output columns in an INSERT/SELECT.  This partially fixes
>> the problem Michael Davis reported a few weeks ago.  However, there's
>> still a bug with confusion about column names.  Given
>> 
>> create table foo (a int4, b int4);
>> CREATE
>> create table bar (c int4, d int4);
>> CREATE
>> 
>> we can do
>> 
>> select c, sum(d) from bar group by c;
>> 
>> but not
>> 
>> insert into foo select c, sum(d) from bar group by c;
>> ERROR:  Illegal use of aggregates or non-group column in target list
>> 
>> The problem here is that the target expressions of the select have
>> been relabeled with foo's column names before GROUP BY is processed.
>> If you refer to them by the output column names then it works:
>> 
>> insert into foo select c, sum(d) from bar group by a;
>> INSERT 279412 1
>> 
>> You can think of the query as having been rewritten to
>> 
>> insert into foo select c AS a, sum(d) AS b from bar group by a;
>> 
>> in which case the behavior makes some kind of sense.  However,
>> I think that this behavior is neither intuitive nor in conformance
>> with SQL92's scoping rules.  As far as I can tell, the definition
>> of the result of "select c, sum(d) from bar group by c" is independent
>> of whether it is inside an INSERT or not.
>> 
>> Fixing this appears to require a substantial rearrangement of code
>> inside the parser, which I'm real hesitant to do with only a week to go
>> till 6.5 release.  I propose leaving this issue on the "to fix" list for
>> 6.6.  Comments?
>> 
>> BTW, although Davis claimed this was broken sometime during April, 6.4.2
>> shows the same bugs ... I think it's been wrong for a long time.