From: Ole John Aske Date: February 2 2011 9:16am Subject: bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4163 to 4164) Bug#56690 List-Archive: http://lists.mysql.com/commits/130187 X-Bug: 56690 Message-Id: <20110202091626.AFD73223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4164 Ole John Aske 2011-02-02 Fix for Bug#56690: Wrong results with subquery with GROUP BY inside < ANY clause. Cherry picked into mysql-5.1-telco-7.0 Se original commit comments in http://lists.mysql.com/commits/130184 modified: mysql-test/r/subselect.result mysql-test/t/subselect.test sql/sql_class.cc sql/sql_class.h 4163 Ole John Aske 2011-02-02 Fix for bug#58490: 'Incorrect result in multi level OUTER JOIN', cherry picked into telco branches. Also fix the duplicate bug#49322. Se original commit http://lists.mysql.com/commits/130129 for comments modified: mysql-test/r/join_outer.result mysql-test/t/join_outer.test sql/sql_select.cc === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2011-01-14 13:36:47 +0000 +++ b/mysql-test/r/subselect.result 2011-02-02 09:15:56 +0000 @@ -4785,4 +4785,30 @@ 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 === modified file 'mysql-test/t/subselect.test' --- a/mysql-test/t/subselect.test 2011-01-14 13:36:47 +0000 +++ b/mysql-test/t/subselect.test 2011-02-02 09:15:56 +0000 @@ -3774,4 +3774,32 @@ 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 === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2010-10-20 11:10:46 +0000 +++ b/sql/sql_class.cc 2011-02-02 09:15:56 +0000 @@ -2394,6 +2394,14 @@ 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(); +#ifndef MCP_BUG56690 + if (cache->null_value) + return false; + else if (maxmin->null_value) + return true; + else + return (fmax) ? (val1 > val2) : (val1 < val2); +#else if (fmax) return (cache->null_value && !maxmin->null_value) || (!cache->null_value && !maxmin->null_value && @@ -2401,12 +2409,21 @@ bool select_max_min_finder_subselect::cm return (maxmin->null_value && !cache->null_value) || (!cache->null_value && !maxmin->null_value && val1 < val2); +#endif } 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(); +#ifndef MCP_BUG56690 + if (cache->null_value) + return false; + else if (maxmin->null_value) + return true; + else + return (fmax) ? (val1 > val2) : (val1 < val2); +#else if (fmax) return (cache->null_value && !maxmin->null_value) || (!cache->null_value && !maxmin->null_value && @@ -2414,6 +2431,7 @@ bool select_max_min_finder_subselect::cm return (maxmin->null_value && !cache->null_value) || (!cache->null_value && !maxmin->null_value && val1 < val2); +#endif } bool select_max_min_finder_subselect::cmp_decimal() @@ -2421,6 +2439,16 @@ 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); +#ifndef MCP_BUG56690 + 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); +#else if (fmax) return (cache->null_value && !maxmin->null_value) || (!cache->null_value && !maxmin->null_value && @@ -2428,6 +2456,7 @@ bool select_max_min_finder_subselect::cm return (maxmin->null_value && !cache->null_value) || (!cache->null_value && !maxmin->null_value && my_decimal_cmp(cvalue,mvalue) < 0); +#endif } bool select_max_min_finder_subselect::cmp_str() @@ -2440,6 +2469,16 @@ bool select_max_min_finder_subselect::cm */ val1= cache->val_str(&buf1); val2= maxmin->val_str(&buf1); +#ifndef MCP_BUG56690 + 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); +#else if (fmax) return (cache->null_value && !maxmin->null_value) || (!cache->null_value && !maxmin->null_value && @@ -2447,6 +2486,7 @@ bool select_max_min_finder_subselect::cm return (maxmin->null_value && !cache->null_value) || (!cache->null_value && !maxmin->null_value && sortcmp(val1, val2, cache->collation.collation) < 0); +#endif } bool select_exists_subselect::send_data(List &items) === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2010-10-20 11:10:46 +0000 +++ b/sql/sql_class.h 2011-02-02 09:15:56 +0000 @@ -2835,6 +2835,9 @@ public: {} void cleanup(); bool send_data(List &items); +#ifndef MCP_BUG56690 +private: +#endif bool cmp_real(); bool cmp_int(); bool cmp_decimal(); No bundle (reason: useless for push emails).