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