From: Ole John Aske Date: February 2 2011 9:04am Subject: bzr commit into mysql-trunk branch (ole.john.aske:3588) Bug#56690 List-Archive: http://lists.mysql.com/commits/130184 X-Bug: 56690 Message-Id: <20110202090501.7EF24223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============4840614205679294457==" --===============4840614205679294457== 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-trunk/ based on revid:ole.john.aske@stripped 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 === 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(); --===============4840614205679294457== 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\ # 99sq3a34nhkkq66n # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-trunk/ # testament_sha1: e2df0b32b2533a7a05612e4d2bce675346d6e56b # timestamp: 2011-02-02 10:05:01 +0100 # source_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.5/ # base_revision_id: ole.john.aske@stripped\ # fv7dqwj11afpumsi # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWaA0DYMABkpfgFBw2Pf///// /+C////wYAw/Be30uYAAAAwBqwCgGgdNBoNASmqJiTaMk0yaNPUyPU9EPU9QNANNAaDQAAcZGmTE 0GTJhNMgZDQGgNMmhgBNAY4yNMmJoMmTCaZAyGgNAaZNDACaAwlGoTUagMmQANGgAAMgAAAABFIo 0yGqfok9MRkQ2oGmg0NGhoZDEMgHigkkCAEZMRoCaZRtTJTYp6ChmkzUHqekxDNSSQbg/4DjxK3K 5mllpt2nQJgo1OH/p/Sg/mP/rR1sC0Daw4bi6ywDA8h/EOA8/3MidUifPCF4Y7NgbsyHnhXLVUNr GsHJzJDb7w/s+CiOIsINX53vA7i1Vvdq7xjuywk6M9GoLgzCmi3T7XAjSgMj/pcmb/uSBFqEXpgX MMgfQbLkx+KSs/QKwi4Ovx6jUOuoR1gTseTETCBIEkkkjAQEPoEamgisOs20TgWqmLbjmO2jQqCo ovBy3Si8nIjEJxqxeMj26F7HANwn1H3mEZEBjFIx/il4XyFUJyzW74igOdW/mPsNk7QPb6vCOEo0 YnCwysBxMiB7bvn402U+vz3TQme93q2Blx+wrlmH+KLgGqClDpTPNJnTAwSVYyoBnPcPE7LwjdoL eSXxuK6GvoRMQkDxXjOV4Z48nukGd6XoGQMMgYbomanVFV2WHYkwF8vkhasD0ECBBDdo255aI6dW ka+YK3O4X3Nquxaedu0YwH0A2CUSBIQSvyyORvD6ApJmb2mGcY+hIBzJOPUnAtO2QtPU9iV7M8fu +66aGrFxWQIIenLqolcBjUL4F4wMECs5plQnr5rWQVYU/+DczgTG4MzmJH3nYSrFuR9T8QyUIUe4 3HRXPPR52B7FlJw2mwm9LbH4B/6MHI5/TLDYIPCYqSRYVwWyAgiuInWMSK0RJQQ5GiwyCQBgyZkF O9wcxvoazgHQwKygIVwIgwQBwCYKZORSMkFZIkgJhxQ8iFcCQlAIUkAcs0xImJKhmGEqQcBFVjt4 c6SM/I1A9oNsTJ3EHoNciMDzUUH2TATr8MAzekEAjQnFBs9rYCRjJ0frNvW8lOTFNrs9LtkMn7dp Ej01/ykli4Ww9dk49MWlMzA9jWvkmBwUUl7+NrqNgeWsrOIYElUixybBPMERWs0eKYnP7npXbXjN yWAySzJOcZpw45qsWAwkMkEwyKmEqqiozHl5AgYEDIiXUlDmYJmQOOYOloQjEgbXga1Iz4VFklyO hMVFpNdN5M7FajUTzFEAmtKiFAxrUaiFolvqKCmtOTz+4lMeCmiXlrLT5Gh/grN6LbHT62e7FRKS 2Qy3MEryBKCWxjziJSJGY8rvVpInJCiYDj7S08x5MayspJ3GZWJaV6yehMJNrVxxp1jwK2fvLR5u IdR5eRtEoh/mDeQosQHzjFTWgxsujU3zNQ86+VJ+sjpsHlJWNkWsZMq1qH7cTU+gdQGZcDicxoOB XcTSIUjPqaxWvDQSbIqKgzsimEwsCLyIlqNh16mvC2a4cGpwsC0wPuIRGNZqdkQKoFRrKCmJMT0S SeRK5zkdV5FR4IGB6nzC4l+QX2trPAvenmuGkh3AisHhycTVNjHLQ85QGqHjyRTPKUzwhtBgB0hu jrRjgby2icH1XWBUUE3oJaiY7dqSRAoKKC88WiXLdl/IcU1XqepyrtqeZZNOyzYUzBF6TPaAqQJT NMP+QDFoDkkViG1GcY14KErTdTZQmxBGTpSktkHFRMzHmgDKUFTbKBCvjQ1K0KfAfiMSooYghIiO VCa24ZjIWHSUjyDIYYG5IXnF/QEK6lf8DdB3oeuHWBZeGdasKMEMBA3l1kDwIYBb6OFXnB0jTGaS yETakN6OFi4pjV2bFdYwuKta2kcZYB/R6x0g6XUttWwYVwjUeAbFZF0CxWY1jMZDaQpGStxnkVmj Qs1SnRJuVDQGULwwyWQ9PqA5DQG+mhIgCHlbQyYGD780hONpAsOI9LPs2ETHEdJy4hmf4Jn+GyYT EYzJFXpDP2GZi26c4+6GW85LgFXMD7TmnjCC0gZ0zbVNvMzZ1FrljYFQeIsowUwKcDEIqncnguiD meZisVSbKR5DTPyHHmbd4x165Eux0DhA1oQfm8gW9iCFRxHIntPMd9ggplT5FrjQdf5ncjlj3Lzs XHxD3m0mKjusZ1kw6RsNjVG0VCTP+Of5CkEUkTPOxvtP0P6hO9JEKCdcPFg8tQsRl72T2xQyJ58+ RHcSKd9CWRApSfMcjfImOBQkTKkpOU5EOMSJuJE2jLPM7iiQ9NSrPzGbJbhg8vTAP575bByHKYHu JtcOkQURT+s6BFyZF1FuZXoMaFJbEgSv7Hh2hWc3HA2bArD3I96N+0QSnsE2KKwNuB7GB/AVUCY3 pAPQ6S1Y5ICzcNYhJ8F4eLRelkvK41StFnCuTpbLhdEdwGNyn5J5B4s1onJWFYJcmQW8+DOTuhVM ZdiPOQlc2Q6h10kNagYYF5jAPSLqxRkBUHLQVZ2H+vLqXjdOpvGKTyLxjeHId3PcUlQqzBjAWg5x 8EeEVo8LW6w+AanHeAtnStfB/wUt6YTaxtRZRRWMAymHcF3Nh0PPebx4bhg8J3cuHWgo4vHYMfiw zDxYuPyzdFmwEr0gmCBTeeRwQoBnj8zaTmfuH2Tl/JImD0HuVJHECItXwpNp1HE6SiTqdUNYxzD5 MuF0DwBSGOJqiLz7cpXIsghaLCCJbYG4hg0GIs5LiWUSbj1HkrODdNwyiGCtN4jeVpBytInwMhcA O5tErjiAPKTHYvQXIGFACHkQCIokAOy1gOBg4fhSh6SIiBwicRsCmg50ATIZ2aqnAkKIVqDgqggJ 0cjH0NDwPNkhI9mvLSI9NQGa7BahBYiMy40aDUbT6hUuMYllaLyrALWjYKcLSdEOJ/E8i8nyXwKS kG6WVQBiDgdETDwYiIGLU9RYsCYrhIk0LLGOQpdpZxCADtXTKbJ6UCB58RK2WaqYVB8jprLqawtQ UueM9MyD5gfoFQvIVgHUMxOXvKFuKvcc9WgR0ZmfeQHcyoO1ZryCE3deg9YwkvL3QwQC8DBw72NL UCQzwBsY1T7D9UkMBqTxVKY9oqlT1wMZtmWzMcipIb0KXbHIZKkeL6Sr9D7TkFAwTBPiWnaA/8JW RIVYr5loliXiWI8E8tLwuQveo/t9ASr83PZBA9xEevAwOSSn4bii9SdNtche8HBd+oLPbAVRhSWp uWqwwKzWSUwpzcC4ECr7wqo9iNlK3sPadDk7Du7jbtllQtAg46T36xiEWrHhOkKsxk98TKy8wGqc HsbEZGbHQ3X1OFjkiWskHmn2AfZJGa/htEHkZI3MUmhiV4nsj2ZmZn/USwO8HDMOTaFoG9LMCcU1 DJmwJxpAS7lBQbVzYuG5DjTvBXCEftXo+iSJ3p6cfUQ0CcgQthtY9CtViG3nMB4HLq99B9SuVJZi dYpdz3kxSTzF4Dk/MlINvCF+0tlgPDIpDZuiGioAqD0GdGZfPe+PdcXsBxrAchBOjNOP2WTS2nEE wfsPUsMz4nE1GpeBb0tnPuBI8yJxLzFYH0LgNRWZk1RSUGpwjReB3MzuGDeVkh6mYLUiOiqug9LW WFo8UBTGjHzIhWVB90R0i5K8+h3q1H/4u5IpwoSFAaBsGA== --===============4840614205679294457==--