From: Roy Lyseng Date: March 17 2011 12:49pm Subject: bzr commit into mysql-trunk branch (roy.lyseng:3781) Bug#56881 Bug#11764086 List-Archive: http://lists.mysql.com/commits/133211 X-Bug: 56881,11764086 Message-Id: <20110317124937.D0EBD1F3@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============7913545763228046600==" --===============7913545763228046600== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/rl136806/mysql/repo/mysql-trunk/ based on revid:tor.didriksen@stripped 3781 Roy Lyseng 2011-03-17 Bug#11764086 Bug#56881: Null left operand to NOT IN in WHERE clause behaves differently than real NULL The query that triggers this problem is an outer join query with a NOT IN subquery predicate in the WHERE clause. One of the tables in the outer join contains only one row, and because it is a MyISAM table, const table optimization is applied to it. Now the problem: not_null_tables() for the NOT IN predicate reports that we can ignore NULL-complemented rows for table child, hence the query is converted from a left join to an inner join. This leads the query executor to omit the row with id 2 from the evaluation. As for why the not_null_tables() result for NOT IN is wrong: NOT IN may return TRUE for a row, even if the left-hand expression to NOT IN is NULL (as in NULL NOT IN ()). This is what happens when the query executor evaluates the row with id 2 from the parent table: There is no corresponding row in the child table, a NULL-complemented row should be added, and the predicate NULL NOT IN ( is evaluated (and it should return TRUE). The solution to the problem is to implement not_null_tables() for Item_in_optimizer. If the Item_in_subselect member is "top level", meaning that the original query is an IN predicate, return the accumulated not-null information for the object, otherwise (the predicate is NOT IN) return an empty set. mysql-test/include/subquery.inc Added test case for bug#11764086. mysql-test/r/subquery_nomat_nosj.result Added test results for bug#11764086. mysql-test/r/subquery_none.result Added test results for bug#11764086. sql/item_cmpfunc.cc Added implementation for Item_in_optimizer::not_null_tables(). sql/item_cmpfunc.h Added interface for Item_in_optimizer::not_null_tables(). modified: mysql-test/include/subquery.inc mysql-test/r/subquery_nomat_nosj.result mysql-test/r/subquery_none.result sql/item_cmpfunc.cc sql/item_cmpfunc.h === modified file 'mysql-test/include/subquery.inc' --- a/mysql-test/include/subquery.inc 2011-02-02 09:04:55 +0000 +++ b/mysql-test/include/subquery.inc 2011-03-17 12:49:15 +0000 @@ -4740,8 +4740,6 @@ CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); DROP VIEW v1, v2; ---echo End of 5.1 tests. - # # Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index # @@ -5079,7 +5077,6 @@ SELECT 1 FROM DROP TABLE t1; - --echo End of 5.5 tests. --echo # @@ -5203,6 +5200,67 @@ WHERE (col_int_key, col_int_key) IN ( DROP TABLE bb, b, cc, c; +--echo # +--echo # Bug#11764086: Null left operand to NOT IN in WHERE clause +--echo # behaves differently than real NULL +--echo # + +CREATE TABLE parent (id int); +INSERT INTO parent VALUES (1), (2); + +CREATE TABLE child (parent_id int, other int); +INSERT INTO child VALUES (1,NULL); + +--echo # Control query (c.other is always NULL) + +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id NOT IN ( + SELECT parent_id + FROM child + WHERE parent_id = 3 + ); + +--echo # Offending query (c.parent_id is NULL for null-complemented rows only) + +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id NOT IN ( + SELECT parent_id + FROM child + WHERE parent_id = 3 + ); + +--echo # Some syntactic variations with IS FALSE and IS NOT TRUE + +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id IN ( + SELECT parent_id + FROM child + WHERE parent_id = 3 + ) IS NOT TRUE; + +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id IN ( + SELECT parent_id + FROM child + WHERE parent_id = 3 + ) IS FALSE; + +DROP TABLE parent, child; + +--echo # End of test for bug#11764086. + --echo End of 5.6 tests --echo # === modified file 'mysql-test/r/subquery_nomat_nosj.result' --- a/mysql-test/r/subquery_nomat_nosj.result 2011-02-14 11:21:26 +0000 +++ b/mysql-test/r/subquery_nomat_nosj.result 2011-03-17 12:49:15 +0000 @@ -5900,7 +5900,6 @@ Note 1249 Select 2 was reduced during op CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); DROP VIEW v1, v2; -End of 5.1 tests. Set up test tables. CREATE TABLE t1 ( t1_id INT UNSIGNED, @@ -6345,6 +6344,67 @@ ORDER BY parent1.col_date_key ); col_int_key DROP TABLE bb, b, cc, c; +# +# Bug#11764086: Null left operand to NOT IN in WHERE clause +# behaves differently than real NULL +# +CREATE TABLE parent (id int); +INSERT INTO parent VALUES (1), (2); +CREATE TABLE child (parent_id int, other int); +INSERT INTO child VALUES (1,NULL); +# Control query (c.other is always NULL) +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id NOT IN ( +SELECT parent_id +FROM child +WHERE parent_id = 3 +); +id parent_id +1 1 +2 NULL +# Offending query (c.parent_id is NULL for null-complemented rows only) +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id NOT IN ( +SELECT parent_id +FROM child +WHERE parent_id = 3 +); +id parent_id +1 1 +2 NULL +# Some syntactic variations with IS FALSE and IS NOT TRUE +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id IN ( +SELECT parent_id +FROM child +WHERE parent_id = 3 +) IS NOT TRUE; +id parent_id +1 1 +2 NULL +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id IN ( +SELECT parent_id +FROM child +WHERE parent_id = 3 +) IS FALSE; +id parent_id +1 1 +2 NULL +DROP TABLE parent, child; +# End of test for bug#11764086. End of 5.6 tests # # BUG#46743 "Azalea processing correlated, aggregate SELECT === modified file 'mysql-test/r/subquery_none.result' --- a/mysql-test/r/subquery_none.result 2011-02-14 11:21:26 +0000 +++ b/mysql-test/r/subquery_none.result 2011-03-17 12:49:15 +0000 @@ -5899,7 +5899,6 @@ Note 1249 Select 2 was reduced during op CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); DROP VIEW v1, v2; -End of 5.1 tests. Set up test tables. CREATE TABLE t1 ( t1_id INT UNSIGNED, @@ -6344,6 +6343,67 @@ ORDER BY parent1.col_date_key ); col_int_key DROP TABLE bb, b, cc, c; +# +# Bug#11764086: Null left operand to NOT IN in WHERE clause +# behaves differently than real NULL +# +CREATE TABLE parent (id int); +INSERT INTO parent VALUES (1), (2); +CREATE TABLE child (parent_id int, other int); +INSERT INTO child VALUES (1,NULL); +# Control query (c.other is always NULL) +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id NOT IN ( +SELECT parent_id +FROM child +WHERE parent_id = 3 +); +id parent_id +1 1 +2 NULL +# Offending query (c.parent_id is NULL for null-complemented rows only) +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id NOT IN ( +SELECT parent_id +FROM child +WHERE parent_id = 3 +); +id parent_id +1 1 +2 NULL +# Some syntactic variations with IS FALSE and IS NOT TRUE +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id IN ( +SELECT parent_id +FROM child +WHERE parent_id = 3 +) IS NOT TRUE; +id parent_id +1 1 +2 NULL +SELECT p.id, c.parent_id +FROM parent p +LEFT JOIN child c +ON p.id = c.parent_id +WHERE c.parent_id IN ( +SELECT parent_id +FROM child +WHERE parent_id = 3 +) IS FALSE; +id parent_id +1 1 +2 NULL +DROP TABLE parent, child; +# End of test for bug#11764086. End of 5.6 tests # # BUG#46743 "Azalea processing correlated, aggregate SELECT === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2011-03-17 09:47:50 +0000 +++ b/sql/item_cmpfunc.cc 2011-03-17 12:49:15 +0000 @@ -1794,6 +1794,22 @@ void Item_in_optimizer::fix_after_pullou const_item_cache&= args[1]->const_item(); } + +/** + For a NOT IN subquery predicate (for which the Item_in_subselect member is + not a \"top level\" item), null values passed from outer tables must be + considered in the evaluation, hence return an empty set of tables. +*/ + +table_map Item_in_optimizer::not_null_tables() const +{ + if (((Item_in_subselect*)args[1])->is_top_level_item()) + return not_null_tables_cache; + + return 0; +} + + /** The implementation of optimized \ [NOT] IN \ predicates. The implementation works as follows. === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2011-03-09 20:54:55 +0000 +++ b/sql/item_cmpfunc.h 2011-03-17 12:49:15 +0000 @@ -276,6 +276,7 @@ public: bool fix_left(THD *thd, Item **ref); void fix_after_pullout(st_select_lex *parent_select, st_select_lex *removed_select, Item **ref); + virtual table_map not_null_tables() const; bool is_null(); longlong val_int(); void cleanup(); --===============7913545763228046600== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/roy.lyseng@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: roy.lyseng@stripped # target_branch: file:///home/rl136806/mysql/repo/mysql-trunk/ # testament_sha1: a682843305aebb9da787b21f032f8490f513350a # timestamp: 2011-03-17 13:49:37 +0100 # base_revision_id: tor.didriksen@stripped\ # kmx03y74jr26gp9i # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSm394QAB4xfgHUwWHf//393 346////6YA6/aPBAIhQAACAEoFAoCqVUqqSqKUFjjRkyMIxAMJoMAmg0DJk0ZMhhAYSpEDQADQAZ AAAAA0AAADjRkyMIxAMJoMAmg0DJk0ZMhhAYRJITESek2gaE9T1NTI9U/Q0p6npNPI9RHpMQxDT2 pDjRkyMIxAMJoMAmg0DJk0ZMhhAYSSAIAjQCNDRME0Q1PSepqe0p6jagBso9T0lNCFqA1gfUCC/j XzRxc7JnN5az2hb2cus7wrfMZclDqsMJuF1ECz1mCoSwVBjal3F3f7xCgIikciAmE1iBqfKUwk2A EAOve4IbPO1jY0LxrRri0FsBRF/YF6y2As3FDmOLgqG8B5K0FAGA36ERGnJ2j3KDAU6zZIu02esK dt1CW0DCML5+jJxllZ6D/e7TxojoCBAIIgfaDo5gfeD8AbAFAwcAcAabwYZ+cDQD/j7h4Wxl5PAe Lhea9/im9OhISMkjCJyH5ptxvxJu7TCxgQ3W0nTcXfSD1mcu3SoaZU3w0ZtGnPRLYXW/PUA+RPob 4O0bXG8gQXZEOt+ZuY+f1dkOk0A9X+sRczoic4nfiL+wOhi4UX+oaheQv6rFUGUc+iyUQrZbh4z/ V8ohMzdgqDqWGKqLcCkC7wVs4AvIFYv0UZp6I8HthfgwOGuoS3mL0Sa+1k6hvTWyd+tangVtOrGp jMszD98YQ5T1KCTEZvV0Fhtm77aQsrW732FrS4XhZ2FkyVENlbCXVTLXenymnYZ2hEIEwamUAVd6 4KTl1gsQWSLbiWY9xqXcnDmwQ3HAsrCGVoq6orY4Jld0cCA5aoy+LsYx+EmTFwigXNkQnxnSJtfO FsY7sX6Ro60IOdR8cPgtj5ki6x6Zvge/1dn6bMjuODUcdd0OSvXmnJUru4+wS0FOTXPYSBCEhISB OhSI+3lTghZS0AL+3b189ixh9eBIUHmn8Od1fr9F+qacz00Q6R1B/Mr6rfPk7/m9COhtb0QjEI5q gHX3/qN3ngfMqHyN4kd57rElhtILwP8QK5+ho/yD0VxtWcZnSBMuDvNhpdXN45k73YuJS/Yx0fs3 BRJE04PkAsyHvcgICEWQGYwO3q80P1jF9Zo4pmvG10Qlz1auJCkwbofSgaiyXIkFoYBQpKgWZRai 3s3VZUvo8HqcLaDEjAZxhMKLnI4kpOSH50Hs5QEXvxkwQSqs8ywgQLhi5zm3AUzK3L/IBwSqOB0T zF4vA+/ELgeDCCa5GGoOchIuKL6CJfSnUcLi0uqJbi7we7c+W/OBP/Q5JMOGC1csZCVoX8J7dZCy /A40BkBS8RWVDUebkegO07W6glVaI/akTYi3A6nioZH7naBlz3NJ2LQOxXgk/cbnHBxV9SLje8s3 m18aDOEtZIcQoX7kytJD5AcC+h4nQ7zeMYbzIy3Z5gsnvm7FyM2EDjsJuxnM1dSuGL76FtDGZUsX 70HGGgw4qV6ry2Rkbz34FVhOaNcFASySxGCZr0OE8zh3UuGrUteTVH3jjQw1ENZbYuNdC5WnWJbR J9jaCcSHH0M7A2UN5BSKdV5iNg2rQxEmrm8bbt1FSkiTyY5w/obthebi8m8ngZjsfdaL1JRnuDBJ 2Oh2F5MoETMwJkz7///4K32cXNffteMLC6eKaQaJpwMS2weVEVaTiZEpE5HOc59VLzeSsmQW3YlA MKGXMuLFMgx7zukbzuKGDbXWs+xkPa4ZEQ05kgmXzEilGd3GsqaiKd6J3BZHCqRaYvGfoT34PgW7 S+xLgSKm8qqlDqMj1OJiTphazNZAtTu3pONIhqwHEEqDJxvGNhqdrfrIFuxVEsVbayDg2WUIEMCr yglHBtxEcFhuiFbMzVXqprMgpAS81jThmr9ndu01o8VV75vdTZmOk5wpcbWFyy7yNYDmBjlMXV3b Y/Sd1FiA2DPtPSPSg6MQ1kjDypmhUhN3uTrObpw2mfZm2V70P1H5D8eMfGnFpYkCSfohduvC4bAN NPyfkA6Ryfg2S6hlpCFhzuBC4B/ZFg3jihiA5A14IsF/EGA3DBDli9MBzEfUhiA4A5CPCDhkA1VA /IUKFvglAx+wJAiH5MLoHEPgDsAbgY/gg3tsnUi/IecB8Gw5gc9D4keIcgHSOlzB0qGodQNDYG5D hHAaA0Ek0AYjcI2G4Biff7q9FusDzCIb4m5LLWiwHth1AQBSBif9HCHqYELC1g9/voC89/2fAiex ifI+4Yd+PzImoQMfeZT+m3+aSJrhA0uNkgJPDd+X5/JGLcmHeWc4bQzoK7qsGncX3fXwoo+/gcs/ jflkDIgLLtZ+LcHmpdwlxJhRtZYFf0Wh8v8ypzLN3cdwx9R59eh0HDzrOsiTf0ZZkTlv/PmQScmS LBLEiJfieLkWC6zwOZPviCxS/VkiKhH3DDgPBTAX7AnLkbheczcaz+GKlwtOTG8LS0nU6GwfkXj/ S6DRvElrYLwIQZ6HJIiYgYJI3fmiSV9RUV7j0Mi4DyBgAZAwJgUGTgAvTDQUWSwyNVGJaNJCwNto 46+fYfE3nAV3lyUSBs7yxKOBgqpHq84nEyGLT7kPtORrNBQtcWC4raLeGQd2J3VVjQh4cQ5MzMaM m83Q9NzxkbZzJxV8nIIScQbxTPskS11eAqAyH9RzMeep/Mq74mo9qFzaVKnMyKDDYEMdXkjRH9eh 1sbWHTReAxrPCx/ALIrgfoJZe4RNezHxMTlpW50BMt7e42kReHl2Tbf2DGAsw5+13GbfD/lxE1oq ajj0HvS9JS/OeBjoxYb2hDs5hygAMGBgxdh5nUWgyvNG6cmcytCMLwj6mFxC6+tZiE8Mx1XIOGOT QEnOQXDJdhrCaZ4DKSRKfiH4Odu7OwTO8RjrIHkMR4m8t7CRj29OKFLEX9EwcTmep2o8Eb0Zl7HU TjEA+xl6wcG4T7QICmCYEVOfs4YCXTGHJ3UNoN7LgSp7HoJfDoLk5N4WDsgUQ9tvJ63BkRehH4sM 1MPGlSIpM+SQDx+7SCC48i02FeoRU3scjPcG8gu8XnD3Z2AfaCRo5wLoBgO9g0D46U2aW3V0E2zX pMBPEV0+k8nLe7EgLUl3WJVA6hgYySpmO/19gNk8g2ud3uEbvR16NutJIseQ34gHGSR7t+i8DG/s nFOL6txVUZClvGsQ3NltHh9x26huJPs5YhE9F4Hidxd2YjzwNoEDjsXsYaZKtp2hqR0Th3juHJhO CID+8eKgNuxMwaghInLEjZUzslokObQkFy2pMkONIwv+2xBzZBA6hyWSwcTDAbD7HLxNe3cEdpzo HsMuUj0UUKQJFeQyLBDluLNyNIEEvduK405wQ8f7FkxawJEUXFgSH6Xdawc+JwzzyqiBCUWM6Kuq 6BVBdLgm6vWeUpL2XgakhwAXGViD3sSgLOHsid027zAwieBF6AdlW3aSCd/AD3MDloyYaEDw2D76 B8WVAlhjGACa21DaLKaQ8WQaQL2/P0CXAw3gdotGDocy7NUbi9Fh8UQWyLg5pItD9HGw+viV7LsA u0WC9wTvZdwt4VOQNgfbFvgswy/hJDAW8JxCt5JSFtYCS803DURHqOB9WA+6IFwPmxJ+4PeDqF6t Sm50Yr+4MDp/ADxgNgGA+9jSXLOszHjyT2lgPcdpS39DHk/Q9JF3xYPqyxib8UTDEu5Hl/IS5kEj E1HqYIMvQcMJSPp1YoWxIZbGcyHwieJGK+g7emRZVyoNhatITWOoUGgi25LuktAga4b5we4VN28c W2uZZdsPit5I3tAKnJL5GaqHkH87oYieM5z+gxK+Hb4tfcQorgshaDISCWtsod7AIA0DD8tsYjNp FPe3JxAGeB3NR/brIbiDKxnR7Ed6wEGvaWOF470QMa2AfOKNgYeBmjaxsLLdhxRxZmZtvzzNA7hl 8zgSya57hOZDMG0C8uuoqpJJNjqMEMMTQ5nLm6Y8di6N3Lu8ho02CgUiLOk9iY8U2E0R5KFJ0mTC kZJJA94PAceYixGplikzTCLcqCm2xoIuEfklkt8I2i8TjdjYSZDQ64AemNR8azzmVtjr5Type8MT 8DILAtZHA3TKrcBNchKbg136xnpa3cEsg/CBcZiTG6ocMEg2HZAqeohOofn4CYPXx1DrB38gYF0v QdbgNj9RqSYXvLO8tWVhuMCh2kZRDiExwYhMPoRgBrORRKqjtGDUwpDRDacjNYFSw6i08i8SjeK1 kOYx2A8H7GcmUUA206HkLH7QDmFy9hz8xk6TgfvpA5FwFuLE35EEF0SLcFsUT+LuSKcKEgU2/vCA --===============7913545763228046600==--