From cc2e929ac4b787220118f6d4dc4a72b796bf4ea0 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 15 Feb 2008 15:47:32 +0200 Subject: Bug #31887: DML Select statement not returning same results when executed in version 5 Zero fill is a field attribute only. So we can't always propagate constants for zerofill fields : the values and expression results don't have that flag. Fixed by converting the const value to a string and using that in const propagation when the context allows it. Disable const propagation for fields with ZEROFILL flag in all the other cases. mysql-test/r/compare.result: Bug #31887: test case mysql-test/t/compare.test: Bug #31887: test case sql/item.cc: Bug #31887: If the context allows conversion of an int constant to a zero-filled string constant put the string constant instead of the int constant when doing const propagation sql/mysql_priv.h: Bug #31887: a macro to get all the Field_num descendant fields. --- mysql-test/r/compare.result | 38 ++++++++++++++++++++++++++++++++++++++ mysql-test/t/compare.test | 31 +++++++++++++++++++++++++++++++ 2 files changed, 69 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result index c141b255716..c9ef41e0582 100644 --- a/mysql-test/r/compare.result +++ b/mysql-test/r/compare.result @@ -53,3 +53,41 @@ a b Warnings: Warning 1292 Truncated incorrect DOUBLE value: '' drop table if exists t1; +CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill); +INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2); +SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1; +CONCAT(b,c) CONCAT(b,c) = '0101' +0102 0 +0101 1 +0202 0 +EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (concat(_binary'01',`test`.`t1`.`c`) = _latin1'0101')) +SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; +b c +01 01 +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (1),(2); +SELECT a, +(SELECT COUNT(*) FROM t1 +WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; +a x +1 1 +2 0 +EXPLAIN EXTENDED +SELECT a, +(SELECT COUNT(*) FROM t1 +WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using filesort +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +Warnings: +Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,(select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = concat(_latin1'0',`test`.`t2`.`a`,_latin1'01')))) AS `x` from `test`.`t2` order by `test`.`t2`.`a` +DROP TABLE t1,t2; +End of 5.0 tests diff --git a/mysql-test/t/compare.test b/mysql-test/t/compare.test index 337035a8095..8863ed825c2 100644 --- a/mysql-test/t/compare.test +++ b/mysql-test/t/compare.test @@ -46,3 +46,34 @@ insert into t1 values (0x01,0x01); select * from t1 where a=b; select * from t1 where a=b and b=0x01; drop table if exists t1; + +# +# Bug #31887: DML Select statement not returning same results when executed +# in version 5 +# + +CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill); +INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2); + +SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1; + +EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; +SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; + +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (1),(2); + +SELECT a, + (SELECT COUNT(*) FROM t1 + WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; + +EXPLAIN EXTENDED +SELECT a, + (SELECT COUNT(*) FROM t1 + WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; + +DROP TABLE t1,t2; + +--echo End of 5.0 tests -- cgit v1.2.1