Bug #24491 using alias from source table in insert..on duplicate key
Submitted: 21 Nov 2006 23:20 Modified: 3 Feb 2007 2:34
Reporter: Thomas Johnson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.27, 5.1 BK OS:Linux (Linux, Windows)
Assigned to: Dmitry Lenev CPU Architecture:Any
Tags: alias, duplicate, insert, key

[21 Nov 2006 23:20] Thomas Johnson
Description:
I found that a poorly written Stored procedure can get different results upon execution a second time.  the scenario is when you insert into a table using another select statement and in the on duplicate key update phrase you reference a field common to the target table and from the source but prefix with an alias from the source table.  Upon first call the procedure will error out correctly and give the user: ERROR 1054 (42S22): Unknown column 'alias.field_name' in 'field list'.  Upon second call from the same connection, it will go through OK, when it really should of error out.  If you start a new connection and call the procedure again, the first call will error and the second (or later) will not within the connection.

How to repeat:
Within mysql command line:
use test;

DROP TABLE IF EXISTS `test`.`test`;
CREATE TABLE  `test`.`test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `value` varchar(10) default NULL,
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into test (id,value) values (1,'FIRST');
insert into test (id,value) values (2,'SECOND');
insert into test (id,value) values (3,'THIRD');

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`test` $$
CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
  insert into test (id,value)
    select id + 2, x
    from (select id, concat(value,'X') x, modified
    from test t) y
    where modified between '2006-01-01' and now()
  on duplicate key update y.modified = now();
END $$

DELIMITER ;

SELECT * FROM test t;  -- Three rows

call test();	-- Error

SELECT * FROM test t;  -- Three rows

call test();	-- No Error

SELECT * FROM test t;  -- Five rows

Suggested fix:
It should always error out because of the bad syntax.
[22 Nov 2006 10:13] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last 5.0 and 5.1 BK sources.
[9 Jan 2007 8:43] Dmitry Lenev
Note that prepared statements are also affected by this bug, so it is not something specific to SP but rather issue with general re-execution of INSERT .. SELECT .. ON DUPLICATE KEY UPDATE ... .
[10 Jan 2007 14:15] Bugs System
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/17847

ChangeSet@1.2322, 2007-01-10 17:16:51+03:00, dlenev@mockturtle.local +3 -0
  Proposed fix for bug#24491 "using alias from source table in insert ...
  on duplicate key".
  
  INSERT ... SELECT ... ON DUPLICATE KEY UPDATE which was used in
  stored routine or as prepared statement and which in its ON DUPLICATE
  KEY clause erroneously tried to assign value to a column mentioned only
  in its SELECT part was properly emitting error on the first execution
  but succeeded on the second and following executions.
  
  Code which is responsible for name resolution of fields mentioned in
  UPDATE clause (e.g. see select_insert::prepare()) modifies table list
  and Name_resolution_context used in this process. It uses
  Name_resolution_context_state::save_state/restore_state() to revert
  these modifications. Unfortunately those two methods failed to revert
  properly modifications to TABLE_LIST::next_name_resolution_table
  and this broke name resolution process for successive executions.
  
  This patch fixes Name_resolution_context_state::save_state/restore_state()
  in such way that it properly handles TABLE_LIST::next_name_resolution_table.
[23 Jan 2007 12:02] Bugs System
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/18614

ChangeSet@1.2322, 2007-01-23 15:03:48+03:00, dlenev@mockturtle.local +5 -0
  Proposed fix for bug#24491 "using alias from source table in insert ...
  on duplicate key".
  
  INSERT ... SELECT ... ON DUPLICATE KEY UPDATE which was used in
  stored routine or as prepared statement and which in its ON DUPLICATE
  KEY clause erroneously tried to assign value to a column mentioned only
  in its SELECT part was properly emitting error on the first execution
  but succeeded on the second and following executions.
  
  Code which is responsible for name resolution of fields mentioned in
  UPDATE clause (e.g. see select_insert::prepare()) modifies table list
  and Name_resolution_context used in this process. It uses
  Name_resolution_context_state::save_state/restore_state() to revert
  these modifications. Unfortunately those two methods failed to revert
  properly modifications to TABLE_LIST::next_name_resolution_table
  and this broke name resolution process for successive executions.
  
  This patch fixes Name_resolution_context_state::save_state/restore_state()
  in such way that it properly handles TABLE_LIST::next_name_resolution_table.
[24 Jan 2007 7:45] Bugs System
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/18688

ChangeSet@1.2412, 2007-01-24 10:46:25+03:00, dlenev@mockturtle.local +1 -0
  Fixed test case after merging fix for bug#24491 "using alias from source
  table in insert ... on duplicate key" in 5.1 tree.
[3 Feb 2007 2:34] Paul DuBois
Noted in 5.0.36, 5.1.16 changelogs.

Within stored routines or prepared statements, inconsistent results
occurred with multiple use of INSERT ... SELECT ... ON DUPLICATE KEY
UPDATE when the ON DUPLICATE KEY UPDATE clause erroneously tried to
assign a value to a column mentioned only in its SELECT part.