From: Ole John Aske Date: February 2 2011 9:15am Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4164) Bug#56690 List-Archive: http://lists.mysql.com/commits/130186 X-Bug: 56690 Message-Id: <20110202091602.66C11223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============7558198376297941398==" --===============7558198376297941398== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0/ based on revid:ole.john.aske@stripped 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 === 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(); --===============7558198376297941398== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/ole.john.aske@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: ole.john.aske@stripped\ # umif4kvy3wvcqosw # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0/ # testament_sha1: d333d3afdfb3d0ece818ac1ce2e3fdbae96788f8 # timestamp: 2011-02-02 10:16:02 +0100 # base_revision_id: ole.john.aske@stripped\ # 4c6jzaiwxvf7rngl # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWR8nrBEABRdfgFAwWHf///// 36C////wYAnd9UlSJAKCFQa0CgAFKoA1KehqGQDQ0DQAwJoGTIaAGgAHGTJoYjE0YBGAmEAYCaaN MjQDHGTJoYjE0YBGAmEAYCaaNMjQDCREITUxNGFT2EJHlH6k8kABjUPUPUPUGgcZMmhiMTRgEYCY QBgJpo0yNAMFSkJkyaAAmp5EwJT9TJo0p6jxT1NqNPKN6p6jZqlADWeJtwedrRpHbge3wbbbM11D yPN+f+HTycoaxKLxoORxXadl1x7DvvbWOJfXffoddmaVU7qX9+rB52YwQ0uYiCYSF13jPQDcvGY5 21FLy80tt2sNhpfqrBeFqhOJ+rRpU/JHsPmKQG3jtCIYz2B2B+gZgY2A222uB6THyS6939X56nXE 9M0lS7mUcsl89Kkm5w1ZkxWcFN5VWtcz2TcWqq1SjRJo/rHeu+ooVO3Q9xYcUY9r/k6RYPzP82dR gNaeDu82E6fPINxpHu5v9pUKh5+rIoYce0NIayhdsqGNTipR0LWfmeJUKKhRYNR7nc4s02Ts+Psp uspr5vXX41etXn9gp593n9wFWIOve2Ntb4EoEukGH9jQhnUByGgPp7DYV4NyUpk+SC8pRSQekrhR 4h2GUYyx0fFRercmlYxOxycz/6Q9WphKnVgm8/Iy1U6OD1Z63hW+sa2gnydl7PW8leTOh5RQtg10 rlEKXmlPrr6G//W/XeeJ+HXeZ1JhIb2ctYRDqwkHzhtCrOcLChIGrhoCQlJaSWYHAyUDfE1IgoWB jCb2TKjIQCocl+SS2XNPnRaxyafo1QvclU05uwsTVU/BWvULn14pi9lSqSV1FTU/TB2++2C1Qusy bmGvWlbHL792jJy4b+tzTi2XpRrUmb1165oJjdzYclvwZ+nZu6PDDf5H3aG85txS5X0sGRsX6GvR k6raqkZOCqjaqqdrRNUkXwaSkRpqYGZZKlWxngraNFqy26yswbut9lrdysk1rGhexZ2h/jm7DNnx y5um5tyW5ritauVTueDJlzOd7qouzK6cduLY2lfoyL2ene3PrexT6Mqtr2k1bZopiY8Gi2hrpTfr VQG1hDHsMlxIUMXCxHJAngZVJqMbRdGEy3xwUa5a4OCzp0yfyyEq06b2DLlyM+kxYrr5pWu19F+q bqEuru2ks/7j8pfGbu0c11WiwxelzN91LHBqwZNK3bbdh2bM9eGSu3IqZYmRp93ius1Zeaq813tG idLb2nQPK5ZmYjfilyWWZ983t7vajLTVr1UqvdVhistfFr23tdctwlmVU4q1S3YmfXZ0yN129g98 Z2jWJ3HefKbtQ/E5PjDicDP0mo0k4DvhTrNlrrG62idfoNJcZTDIXl/QXFitntxchglYeYkohYRa Qzld1gi8zS+2xyl5cb/E2FO+uWQh43wZ+V2xNUosxWkTudkXpX3OPAHlAgQciRtQ+qhgdDlCs9fs LjxfKg3q1qFz70/hFknrD2hFjONjTb/UX/IoirVkVCQGrw9Ye4ooX1D9R+5RH8o/gZh4VxVH0LzM iuIooXp6JnFyakfsP4LoUJKHxWSa0ZkfQpGi+L2U/xGYofM/kfy/SMiM5RGw+80IqvNCKzErKjFM pUjajbFSWxkguK5ZMCioqNEfYPxeXodJAcAWLWUgBgA5j0AqAIMruQGQcDBEnQ8M8UB0E0ENyEMu 78WlUrsk+9mnH9iVv67islhyu1Z5H9kOwWQvcC5mvIkC86tZlPLwNRuz/gULGg6K1qYEwYia25/1 Pt4KkrXuL3yHTnScqYrXR02753Wu17nwNttFLWuplXuO3bwSxKVPyTGVoVOx3M791Ii1+nPwJ0eD Q0Jn3OSt5HXr2K/Y6LuFjTil6clWZbDumZ2urq0Wzo6MI20jyjut/XejRP790Vn56CeqV2CPPyVd ede1md1y6qxdunliZjzO81IdHS6qVYmEmv9ntzv7Gxcnhj3OHf7baLotZTpod2bl6G+MGk2C/cOI DR3o4LbAs5sAWjdk342va6eOSeGOWKYwlUMpsWZImuOY/X+na5eHgueC8l6x31PRqOyWP6MGK7oe auXu9mipcxji/c8zAw+ootjCybnNXKcXZ1cWnhSTtZr5xm1ke7sdU+E5YP7syvQiibPx8JCs0b+U kxNvybszwVb+m74wtlV/nGI5kqj3RhwYOZNi9evPf0k22Hd7eyzK10UTjJ8M3vSjvbO+fnYyl3y6 ux4IVXvWck4r5CmDI7XA59ZPB6+xh6E3bIomoolkm322eaktS1kGtKHwVTI/DOVoeiFR4KGRKvuv 9vUXFLSWFUxY1Ssnt8vV3McIjxaHSXHmZhMGY7cuVxWk4GSeLBiaW8zZo0LMFrufoyNT25fHcyGz 7KiqhgqKVoUZlNqkWXnFp4LJNkcou9FlUxme581bJLopSUpD5D5vM2GlO1vKnexYTteWzR6cjJXX 1dyvtfTeh1jxf5RQq+F8SdrVIxzRp+iGmTdXPa99FgC6nJqNKLArdhhysGKqMMCn2m4gZjmOy9Zf Xyfaz89MStkbyezF8it1nn5I3UHpXUnjSWQmTgtkeM4T6E3b64ztzMxx051iubG9tJ2ToLGTyWOP EriN2B7pUHzUB4m0Urf2zkv9QXDBShsTh0UmijSVt08ZKz/johr1p9ux8XvfUfU22285wUQNkGQW lLQln6VdcNlRcS3wG5IzKYYceco38OKaST4vPvYWl/2CjQ9rJZtc/SaWcU1J9zLsw3KyvvUadMlJ jt71ecpnlufGxKvXsJUyrYd2heS89N8/OYo+F6VCcCUVS/zbCWK5W97i5u14pwTuFbk4eRPwZ53T jrfH2SbGx8YmSJlU0pvdHMmtqMDPb6Nb8WQ0wrnWTK8idjMtMx71SuL3FSydF8aHg6MNjNv//F3J FOFCQHyesEQ= --===============7558198376297941398==--