From 1dead07d1416201aba6d04ddfc7dc331dbf5a883 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 23 Jan 2007 15:03:48 +0300 Subject: 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. mysql-test/r/ps.result: Added test case for bug#24491 "using alias from source table in insert ... on duplicate key" mysql-test/r/sp-error.result: Added test case for bug#24491 "using alias from source table in insert ... on duplicate key" mysql-test/t/ps.test: Added test case for bug#24491 "using alias from source table in insert ... on duplicate key" mysql-test/t/sp-error.test: Added test case for bug#24491 "using alias from source table in insert ... on duplicate key" sql/item.h: Name_resolution_context::save_state/restore_state(): At the moment these methods are used only by code implementing INSERT and INSERT ... SELECT statements. This code doesn't modify 'next_name_resolution_table' member of table list element corresponding to the first table of SELECT clause (pointed by 'first_name_resolution_table'). But it modifies table list element corresponding to the target table of INSERT (pointed by 'table_list') So these methods were changed to reflect this. --- mysql-test/t/sp-error.test | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) (limited to 'mysql-test/t/sp-error.test') diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 77bd5259eb5..e77e3df8301 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1808,6 +1808,38 @@ PREPARE stmt FROM "CREATE VIEW v AS SELECT ?"; DROP TABLE t1; +# +# BUG#24491 "using alias from source table in insert ... on duplicate key" +# +--disable_warnings +drop tables if exists t1; +drop procedure if exists bug24491; +--enable_warnings +create table t1 (id int primary key auto_increment, value varchar(10)); +insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD'); +# Let us create routine with INSERT ... SELECT ... ON DUPLICATE KEY UPDATE +# statement which in its ON DUPLICATE KEY clause erroneously tries to assign +# value to a column which is mentioned only in SELECT part. +create procedure bug24491() + insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP'; +# Both first and second calls to it should fail +--error ER_BAD_FIELD_ERROR +call bug24491(); +--error ER_BAD_FIELD_ERROR +call bug24491(); +drop procedure bug24491; +# And now the same test for more complex case which is more close +# to the one that was reported originally. +create procedure bug24491() + insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP'; +--error ER_BAD_FIELD_ERROR +call bug24491(); +--error ER_BAD_FIELD_ERROR +call bug24491(); +drop procedure bug24491; +drop tables t1; + + # # BUG#NNNN: New bug synopsis # -- cgit v1.2.1