From: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] Partial fix for INSERT...SELECT problems |
Date: | 1999-09-21 19:40:23 |
Message-ID: | 199909211940.PAA15682@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1999-09-21 19:42:31 | Re: [HACKERS] strange behavior of UPDATE |
Previous Message | Dae Weon Son | 1999-09-21 19:27:25 | unsubscribe |