diff options
| -rw-r--r-- | mysql-test/r/insert_update.result | 26 | ||||
| -rw-r--r-- | mysql-test/t/insert_update.test | 23 | ||||
| -rw-r--r-- | sql/item.h | 6 |
3 files changed, 52 insertions, 3 deletions
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index 9e674cc4aae..c41aab29853 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -63,9 +63,9 @@ Warnings: Note 1003 select test.t1.a AS `a`,test.t1.b AS `b`,test.t1.c AS `c`,values(test.t1.a) AS `VALUES(a)` from test.t1 explain extended select * from t1 where values(a); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where Warnings: -Note 1003 select test.t1.a AS `a`,test.t1.b AS `b`,test.t1.c AS `c` from test.t1 +Note 1003 select test.t1.a AS `a`,test.t1.b AS `b`,test.t1.c AS `c` from test.t1 where values(test.t1.a) DROP TABLE t1; create table t1(a int primary key, b int); insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5); @@ -197,3 +197,25 @@ PRIMARY KEY (a) ) ENGINE=MyISAM; INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ; DROP TABLE t1; +CREATE TABLE t1 +( +a BIGINT UNSIGNED, +b BIGINT UNSIGNED, +PRIMARY KEY (a) +); +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = +IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +a b +45 1 +INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b = +IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +a b +45 2 +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = +IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +a b +45 2 +DROP TABLE t1; diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index 8038bd7bfe7..56885a555fd 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -115,4 +115,27 @@ INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ; DROP TABLE t1; +# +# Bug#21555: incorrect behavior with INSERT ... ON DUPL KEY UPDATE and VALUES +# + + # End of 4.1 tests +CREATE TABLE t1 +( + a BIGINT UNSIGNED, + b BIGINT UNSIGNED, + PRIMARY KEY (a) +); + +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = + IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b = + IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; +INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = + IF(VALUES(b) > t1.b, VALUES(b), t1.b); +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/sql/item.h b/sql/item.h index 3eab695cb5e..ad8bea663f1 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1241,7 +1241,11 @@ public: { return Item_field::save_in_field(field_arg, no_conversions); } - table_map used_tables() const { return (table_map)0L; } + /* + We use RAND_TABLE_BIT to prevent Item_insert_value from + being treated as a constant and precalculated before execution + */ + table_map used_tables() const { return RAND_TABLE_BIT; } bool walk(Item_processor processor, byte *args) { |