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

Tom, is this fixed?

> 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.
> 
>             regards, tom lane
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026