From ddb5a5636cfac14560d4a15c608b0c6f0e3477cd Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 22 Feb 2007 14:11:01 +0100 Subject: Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it doesn't select. This bug was fixed along with bug #16861: User defined variable can have a wrong value if a tmp table was used. There the fix consisted of Item_func_set_user_var overloading the method Item::save_in_field. Consider the query from the test case: INSERT INTO foo( bar, baz ) SELECT bar, @newBaz := 1 + baz FROM foo WHERE quux <= 0.1; Here the assignment expression '@newBaz := 1 + baz' is represented by an Item_func_set_user_var. Its member method save_in_field, which writes the value of this assignment into the result field, writes the val_xxx() value, which is not updated at this point. In the fix introduced by the patch, the save_in_field method reads the actual variable value instead. See also comment for ChangeSet@1.2368.1.3, 2007-01-09 23:24:56+03:00, evgen@moonbone.local +4 -0 and comment for Item_func_set_user_var::save_in_field (item_func.cc) mysql-test/r/update.result: BUG#24010 The correct, and expected, result mysql-test/t/update.test: BUG#24010 The test case for this bug. When the bug is active, the INSERT ... SELECT statement will try to insert the values <1, 2> which gives an error --- mysql-test/r/update.result | 19 +++++++++++++++++++ mysql-test/t/update.test | 26 ++++++++++++++++++++++++++ 2 files changed, 45 insertions(+) diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 9e2bc52657a..b3c5760e2c9 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -434,3 +434,22 @@ Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 21 DROP TABLE t1; +CREATE TABLE t1 ( +a INT(11), +quux decimal( 31, 30 ), +UNIQUE KEY bar (a), +KEY quux (quux) +); +INSERT INTO +t1 ( a, quux ) +VALUES +( 1, 1 ), +( 2, 0.1 ); +INSERT INTO t1( a ) +SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1; +SELECT * FROM t1; +a quux +1 1.000000000000000000000000000000 +2 0.100000000000000000000000000000 +3 NULL +DROP TABLE t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 3ce7ef72670..23ee75d61ea 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -342,3 +342,29 @@ UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999; show status like '%Handler_read%'; DROP TABLE t1; + +# +# Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it +# doesn't select +# +CREATE TABLE t1 ( + + a INT(11), + quux decimal( 31, 30 ), + + UNIQUE KEY bar (a), + KEY quux (quux) +); + +INSERT INTO + t1 ( a, quux ) +VALUES + ( 1, 1 ), + ( 2, 0.1 ); + +INSERT INTO t1( a ) + SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1; + +SELECT * FROM t1; + +DROP TABLE t1; -- cgit v1.2.1