Обсуждение: Partial fix for INSERT...SELECT problems

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

Tom Lane wrote:

>
> 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?

    Does  it really require that substantial rearrangement? Looks
    to me that the renaming of the target columns is only done  a
    little   too   early.    Could   the   per  Query  unique  ID
    Resno.resgroupref <-> GroupClause.tleGroupref help here?

    I wonder if the renaming of the target columns  during  parse
    is  required at all. I think in the case of an INSERT this is
    done allways in the planner again at preprocess_targetlist().

    I  agree  that changing it that close to release isn't a good
    idea, but we should move this item to the  top  ten  of  TODO
    after v6.5.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

jwieck@debis.com (Jan Wieck) writes:
>> 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?

>     Does  it really require that substantial rearrangement? Looks
>     to me that the renaming of the target columns is only done  a
>     little   too   early.

Yeah, what I wanted to do was move both renaming and type-coercion
of target columns down to the end of transformInsertStmt (ditto for
UPDATE I suppose).  However there is a lot of crufty code in that
area, including some array stuff that I am pretty sure has bugs of
its own; and the DEFAULT issue needs to be fixed right in that same
routine, as well.  So I'd rather punt for now and tackle all these
issues in an unhurried manner after 6.5 release, rather than take a
risk of breaking things worse for the release.  Most of these bugs
have been around for quite a while, so I think we can live with 'em
for one more release cycle.
        regards, tom lane

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
 

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.