MySQL Bugs: #25831: Deficiencies in INSERT ... SELECT ... field name resolving.

Bug #25831	Deficiencies in INSERT ... SELECT ... field name resolving.
Submitted:	24 Jan 2007 15:32	Modified:	6 Mar 2007 19:54
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.36-bk, 5.1.15-bk	OS:	Linux (Linux Suse 10.1)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
There are several deficiences in field name resolving of INSERT ... SELECT:

- In SELECT part with missing FROM clause it is allowed to reference fields from target table. See "How to repeat" #1
- In cases when one has NATURAL JOIN in SELECT part one cannot reference fields on which join happens from ON DUPLICATE KEY UPDATE clause. (See "How to repeat" #2).
- Finally altough one cannot reference in ON DUPLICATE KEY UPDATE fields from  SELECT part which contains GROUP BY clause, one can still do this SELECT part contains only a group function. (See "How to repeat" #3).

How to repeat:
#
# How to repeat #1
#
create table t1 (i int primary key, j int);
# The following is allowed altough it should not be
insert into t1 select 1, j;

#
# How to repeat #2
#

create table t1 (i int primary key, j int);
create table t2 (a int, b int);
create table t3 (a int, c int);
# This is allowed
select a from t2 natural join t3;
# And this too
insert into t1 select 1, a from t2 on duplicate key update j= a;
# But not this !
insert into t1 select 1, a from t2 natural join t3 on duplicate key update j= a;

# 
# How to repeat #3
#

create table t1 (i int primary key, j int);
create table t2 (a int);
insert into t1 values (1, 1);
insert into t2 values (1), (3);
# This is allowed ! 
insert into t1 select 1, count(*) from t2 on duplicate key update j= a;

Tentative patch for this bug

Attachment: iodku.patch (text/x-patch), 2.95 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19203

ChangeSet@1.2387, 2007-02-01 19:13:27+02:00, gkodinov@macbook.gmz +10 -0
  Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
   Several problems fixed: 
    1. There was a "catch-all" context initialization in setup_tables()
      that was causing the table that we insert into to be visible in the 
      SELECT part of an INSERT .. SELECT .. statement with no tables in
      its FROM clause.
      Fixed by removing the "catch-all" statement and initializing the 
      context in the parser.
    2. Incomplete name resolution context when resolving the right-hand
      values in the ON DUPLICATE KEY UPDATE ... part of an INSERT ... SELECT ...
      caused columns from NATURAL JOIN/JOIN USING table references in the
      FROM clause of the select to be unavailable.
      Fixed by establishing a proper name resolution context.
    3. When setting up the special name resolution context for problem 2
      there was no check for cases where an aggregate function without a
      GROUP BY effectively takes the column from the SELECT part of an 
      INSERT ... SELECT unavailable for ON DUPLICATE KEY UPDATE.
      Fixed by checking for that condition when setting up the name 
      resolution context.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19992

ChangeSet@1.2387, 2007-02-16 13:10:15+02:00, gkodinov@macbook.gmz +8 -0
  Bug #25831: Deficiencies in INSERT ... SELECT ... field name resolving.
   Several problems fixed: 
    1. There was a "catch-all" context initialization in setup_tables()
      that was causing the table that we insert into to be visible in the 
      SELECT part of an INSERT .. SELECT .. statement with no tables in
      its FROM clause. This was making sure all the under-initialized
      contexts in various parts of the code are not left uninitialized.
      Fixed by removing the "catch-all" statement and initializing the 
      context in the parser.
    2. Incomplete name resolution context when resolving the right-hand
      values in the ON DUPLICATE KEY UPDATE ... part of an INSERT ... SELECT ...
      caused columns from NATURAL JOIN/JOIN USING table references in the
      FROM clause of the select to be unavailable.
      Fixed by establishing a proper name resolution context.
    3. When setting up the special name resolution context for problem 2
      there was no check for cases where an aggregate function without a
      GROUP BY effectively takes the column from the SELECT part of an 
      INSERT ... SELECT unavailable for ON DUPLICATE KEY UPDATE.
      Fixed by checking for that condition when setting up the name 
      resolution context.

Fixed in 5.0.38, 5.1.17-beta

Noted in 5.0.38, 5.1.17 changelogs.