| 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: | |
| 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 | ||
[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.
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.