From: Ole John Aske Date: February 2 2011 9:05am Subject: bzr push into mysql-trunk branch (ole.john.aske:3587 to 3588) Bug#56690 List-Archive: http://lists.mysql.com/commits/130185 X-Bug: 56690 Message-Id: <20110202090529.9A9A7223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3588 Ole John Aske 2011-02-02 Fix for bug#56690 'Wrong results with subquery with GROUP BY inside < ANY clause' A subquery of the form : 1) SELECT ... WHERE < ANY(select ...) Is transformed into the form by the optimizer: 2) SELECT ... WHERE < (select MAX()...) The Min/Max aggregation of subquery columns is implemented in 'class select_max_min_finder_subselect'. The handling of NULL values in this implementation was incorrect as they was interpreted as a NULL-value being '>' than any other value being compared. This is incorrect wrt. SQL semantics which specifies that NULL values are 'undefined' and should be removed as soon as a non-NULL value is encountered. This fix changes implementation of all select_max_min_finder_subselect::cmp_() methods to follow the correct SQL semantics as described above. (Which also simplifies the logic IMHO) It also changes the methods to be 'private' within class select_max_min_finder_subselect. modified: mysql-test/include/subquery.inc mysql-test/r/subquery_nomat_nosj.result mysql-test/r/subquery_none.result sql/sql_class.cc sql/sql_class.h 3587 Ole John Aske 2011-02-02 Fixed failing testcase after commit of fix for bug#58490. Added '--sorted_result' to new testcases to get deterministic order of result set. modified: mysql-test/r/join_outer.result mysql-test/t/join_outer.test === modified file 'mysql-test/include/subquery.inc' --- a/mysql-test/include/subquery.inc 2011-01-18 11:42:09 +0000 +++ b/mysql-test/include/subquery.inc 2011-02-02 09:04:55 +0000 @@ -4999,6 +4999,34 @@ SELECT * FROM t1 DROP TABLE t1,t1s,t2s; +--echo # +--echo # Bug #56690 Wrong results with subquery with +--echo # GROUP BY inside < ANY clause +--echo # + +CREATE TABLE t1 ( + pk INT NOT NULL PRIMARY KEY, + number INT, + KEY key_number (number) +); +INSERT INTO t1 VALUES (8,8); + +CREATE TABLE t2 ( + pk INT NOT NULL PRIMARY KEY, + number INT, + KEY key_number (number) +); + +INSERT INTO t2 VALUES (1,2); +INSERT INTO t2 VALUES (2,8); +INSERT INTO t2 VALUES (3,NULL); +INSERT INTO t2 VALUES (4,166); + +SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number); +SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2); + +DROP TABLE t1,t2; + --echo End of 5.1 tests --echo # === modified file 'mysql-test/r/subquery_nomat_nosj.result' --- a/mysql-test/r/subquery_nomat_nosj.result 2011-01-24 09:02:03 +0000 +++ b/mysql-test/r/subquery_nomat_nosj.result 2011-02-02 09:04:55 +0000 @@ -6152,6 +6152,32 @@ HAVING t2s.i = 999 ) IS UNKNOWN; i DROP TABLE t1,t1s,t2s; +# +# Bug #56690 Wrong results with subquery with +# GROUP BY inside < ANY clause +# +CREATE TABLE t1 ( +pk INT NOT NULL PRIMARY KEY, +number INT, +KEY key_number (number) +); +INSERT INTO t1 VALUES (8,8); +CREATE TABLE t2 ( +pk INT NOT NULL PRIMARY KEY, +number INT, +KEY key_number (number) +); +INSERT INTO t2 VALUES (1,2); +INSERT INTO t2 VALUES (2,8); +INSERT INTO t2 VALUES (3,NULL); +INSERT INTO t2 VALUES (4,166); +SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number); +pk number +8 8 +SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2); +pk number +8 8 +DROP TABLE t1,t2; End of 5.1 tests # # BUG#50257: Missing info in REF column of the EXPLAIN === modified file 'mysql-test/r/subquery_none.result' --- a/mysql-test/r/subquery_none.result 2011-01-24 09:02:03 +0000 +++ b/mysql-test/r/subquery_none.result 2011-02-02 09:04:55 +0000 @@ -6151,6 +6151,32 @@ HAVING t2s.i = 999 ) IS UNKNOWN; i DROP TABLE t1,t1s,t2s; +# +# Bug #56690 Wrong results with subquery with +# GROUP BY inside < ANY clause +# +CREATE TABLE t1 ( +pk INT NOT NULL PRIMARY KEY, +number INT, +KEY key_number (number) +); +INSERT INTO t1 VALUES (8,8); +CREATE TABLE t2 ( +pk INT NOT NULL PRIMARY KEY, +number INT, +KEY key_number (number) +); +INSERT INTO t2 VALUES (1,2); +INSERT INTO t2 VALUES (2,8); +INSERT INTO t2 VALUES (3,NULL); +INSERT INTO t2 VALUES (4,166); +SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number); +pk number +8 8 +SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2); +pk number +8 8 +DROP TABLE t1,t2; End of 5.1 tests # # BUG#50257: Missing info in REF column of the EXPLAIN === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2011-02-01 07:52:46 +0000 +++ b/sql/sql_class.cc 2011-02-02 09:04:55 +0000 @@ -2558,26 +2558,24 @@ bool select_max_min_finder_subselect::cm { Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0); double val1= cache->val_real(), val2= maxmin->val_real(); - if (fmax) - return (cache->null_value && !maxmin->null_value) || - (!cache->null_value && !maxmin->null_value && - val1 > val2); - return (maxmin->null_value && !cache->null_value) || - (!cache->null_value && !maxmin->null_value && - val1 < val2); + if (cache->null_value) + return false; + else if (maxmin->null_value) + return true; + else + return (fmax) ? (val1 > val2) : (val1 < val2); } bool select_max_min_finder_subselect::cmp_int() { Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0); longlong val1= cache->val_int(), val2= maxmin->val_int(); - if (fmax) - return (cache->null_value && !maxmin->null_value) || - (!cache->null_value && !maxmin->null_value && - val1 > val2); - return (maxmin->null_value && !cache->null_value) || - (!cache->null_value && !maxmin->null_value && - val1 < val2); + if (cache->null_value) + return false; + else if (maxmin->null_value) + return true; + else + return (fmax) ? (val1 > val2) : (val1 < val2); } bool select_max_min_finder_subselect::cmp_decimal() @@ -2585,13 +2583,14 @@ bool select_max_min_finder_subselect::cm Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0); my_decimal cval, *cvalue= cache->val_decimal(&cval); my_decimal mval, *mvalue= maxmin->val_decimal(&mval); - if (fmax) - return (cache->null_value && !maxmin->null_value) || - (!cache->null_value && !maxmin->null_value && - my_decimal_cmp(cvalue, mvalue) > 0) ; - return (maxmin->null_value && !cache->null_value) || - (!cache->null_value && !maxmin->null_value && - my_decimal_cmp(cvalue,mvalue) < 0); + if (cache->null_value) + return false; + else if (maxmin->null_value) + return true; + else + return (fmax) + ? (my_decimal_cmp(cvalue,mvalue) > 0) + : (my_decimal_cmp(cvalue,mvalue) < 0); } bool select_max_min_finder_subselect::cmp_str() @@ -2604,13 +2603,14 @@ bool select_max_min_finder_subselect::cm */ val1= cache->val_str(&buf1); val2= maxmin->val_str(&buf1); - if (fmax) - return (cache->null_value && !maxmin->null_value) || - (!cache->null_value && !maxmin->null_value && - sortcmp(val1, val2, cache->collation.collation) > 0) ; - return (maxmin->null_value && !cache->null_value) || - (!cache->null_value && !maxmin->null_value && - sortcmp(val1, val2, cache->collation.collation) < 0); + if (cache->null_value) + return false; + else if (maxmin->null_value) + return true; + else + return (fmax) + ? (sortcmp(val1, val2, cache->collation.collation) > 0) + : (sortcmp(val1, val2, cache->collation.collation) < 0); } bool select_exists_subselect::send_data(List &items) === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2011-02-01 07:52:46 +0000 +++ b/sql/sql_class.h 2011-02-02 09:04:55 +0000 @@ -3393,6 +3393,7 @@ public: {} void cleanup(); bool send_data(List &items); +private: bool cmp_real(); bool cmp_int(); bool cmp_decimal(); No bundle (reason: useless for push emails).