From: Ole John Aske Date: December 8 2010 3:34pm Subject: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58818 List-Archive: http://lists.mysql.com/commits/126331 X-Bug: 58818 Message-Id: <20101208153437.8B5CA223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============6998130621782938845==" --===============6998130621782938845== 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/ based on revid:georgi.kodinov@stripped 3477 Ole John Aske 2010-12-08 Fix for bug#58818 Incorrect result for IN/ANY subquery with HAVING condition The fix is to ensure that the HAVING condition 'IS TRUE' when doing a Item_in_subselect::single_value_transformer If the ::single_value_transformer() find an existing HAVING condition it used to do the transformation: 1) HAVING cond -> (HAVING Cond) AND (cond_guard (Item_ref_null_helper(...)) As the AND condition above is Mc'Carty evaluated, the idea was that the right side of the AND cond would be executed only if the HAVING evaluated to true. This logic failed to considder the 3-value logic required by NULL/UNKNOWN evaluation. Evaluating 'UNKNOWN AND ' requires the right AND-argument to be evaluated and 'false' returned if evaluated to false, else unknown. To get the required behaviour this fix change the transformation in 1) to be: 2) HAVING cond -> ((HAVING Cond) IS TRUE) AND (cond_guard (Item_ref_null_helper(...)) modified: mysql-test/r/subselect.result mysql-test/t/subselect.test sql/item_subselect.cc === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-09-09 12:46:13 +0000 +++ b/mysql-test/r/subselect.result 2010-12-08 15:34:17 +0000 @@ -4733,4 +4733,56 @@ ORDER BY (SELECT * FROM t1 WHERE MATCH(a SELECT * FROM t2 UNION SELECT * FROM t2 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); DROP TABLE t1,t2; +# +# Bug #58818: Incorrect result for IN/ANY subquery +# with HAVING condition +# +CREATE TABLE t1(i INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t1s(i INT); +INSERT INTO t1s VALUES (10), (20), (30); +CREATE TABLE t2s(i INT); +INSERT INTO t2s VALUES (100), (200), (300); +SELECT * FROM t1 +WHERE t1.i NOT IN +( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +); +i +1 +2 +3 +SELECT * FROM t1 +WHERE t1.I IN +( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +) IS UNKNOWN; +i +SELECT * FROM t1 +WHERE NOT t1.I = ANY +( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +); +i +1 +2 +3 +SELECT * FROM t1 +WHERE t1.i = ANY ( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +) IS UNKNOWN; +i +DROP TABLE t1,t1s,t2s; End of 5.1 tests === modified file 'mysql-test/t/subselect.test' --- a/mysql-test/t/subselect.test 2010-04-15 14:04:24 +0000 +++ b/mysql-test/t/subselect.test 2010-12-08 15:34:17 +0000 @@ -3725,4 +3725,53 @@ SELECT * FROM t2 UNION SELECT * FROM t2 DROP TABLE t1,t2; --enable_result_log +--echo # +--echo # Bug #58818: Incorrect result for IN/ANY subquery +--echo # with HAVING condition +--echo # + +CREATE TABLE t1(i INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t1s(i INT); +INSERT INTO t1s VALUES (10), (20), (30); +CREATE TABLE t2s(i INT); +INSERT INTO t2s VALUES (100), (200), (300); + +SELECT * FROM t1 +WHERE t1.i NOT IN +( + SELECT STRAIGHT_JOIN t2s.i + FROM + t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i + HAVING t2s.i = 999 +); + +SELECT * FROM t1 +WHERE t1.I IN +( + SELECT STRAIGHT_JOIN t2s.i + FROM + t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i + HAVING t2s.i = 999 +) IS UNKNOWN; + +SELECT * FROM t1 +WHERE NOT t1.I = ANY +( + SELECT STRAIGHT_JOIN t2s.i + FROM + t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i + HAVING t2s.i = 999 +); + +SELECT * FROM t1 + WHERE t1.i = ANY ( + SELECT STRAIGHT_JOIN t2s.i + FROM + t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i + HAVING t2s.i = 999 + ) IS UNKNOWN; + +DROP TABLE t1,t1s,t2s; + --echo End of 5.1 tests === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-10-18 12:12:27 +0000 +++ b/sql/item_subselect.cc 2010-12-08 15:34:17 +0000 @@ -1097,6 +1097,16 @@ Item_in_subselect::single_value_transfor select_lex->group_list.elements) { bool tmp; + /* + If 'having' condition may evaluate to 'unknown', we must ensure + it 'IS TRUE' before we are allowed to continue into the AND'ed + Item_ref_null_helper object. + */ + Item *having= join->having; + if (!abort_on_null && having && having->maybe_null) + { + having= new Item_func_istrue(having); + } Item *item= func->create(expr, new Item_ref_null_helper(&select_lex->context, this, @@ -1118,7 +1128,7 @@ Item_in_subselect::single_value_transfor we can assign select_lex->having here, and pass 0 as last argument (reference) to fix_fields() */ - select_lex->having= join->having= and_items(join->having, item); + select_lex->having= join->having= and_items(having, item); if (join->having == item) item->name= (char*)in_having_cond; select_lex->having_fix_field= 1; --===============6998130621782938845== 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\ # 6smkq7311b49go9d # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1/ # testament_sha1: 8469db9f1abfdb1fc9ad2fc727fb09f2096f63bb # timestamp: 2010-12-08 16:34:37 +0100 # base_revision_id: georgi.kodinov@stripped\ # l819wohslm0k87fn # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWVxXEEcABPrfgFAQeff//3// 36C////6YApPk9YWxtgAC1rWLKylGjRrQ1RThJImgmk9NI8qZmgmRlMTaE9T0EDR6jI2oBiGJoyZ GEYgGE0GATQaBkyaMmQwgDhppghkNNMjJhANNAGE0aZMACBoJFE0ap6RmmpkxJ6Q8mmmkekNGI9Q 9QNAAAOGmmCGQ00yMmEA00AYTRpkwAIGgkkJiAJkNGgjTRMgI0J5NNE9Q09RoGTah6RZYW5n7GD7 bZeL+/5xuoc48JmPkb+KfBgmw3XWTeev0B8w5h9weGuzrLT6We75MmpSi0Y2FJVUOB3BxjnCeQOn tsBwQFB7D2zGhiDlUkyAqsVAQrbjG/yfe0UddYV0pFIYhg1vEiVIWAO82N/JXfeFg7L8xcoJ1B0m 7tawPzA0LwXEmZkzMyprRZZVHbtySRGBoDzQjlyyLlMDinU3Gp9IEzFgqA2jEZzlpV+YWaT4GQOQ CY2KbK0A2sLNoVwThSo5yrx7wLX9QSnyPreBUBaDAQP7DB5JdpEOmOdngb3eh7fB21vpwOzmOBzR kqvTF+yv0zf0hPzyeueTqld+tUviR1Qat5J1+RXRBvUoa3zdBPvyvTQCD/YvaB+hxDmMLsB44tAz AePHAbgGArFqssv85DOD9boRoi6G/WRYgRtDS6Wre5CXp7dDTlFsvmfokmUulryT2Qr+ZmGZszGc Zljo4I571K5NyCabTLU0gwMJ8OkYRy9OuFt3rm+Virh7xjMJDBQN7te8eeImoPhWB8WvBZU391xr Kg7DcMYAG0+8RaaHrt4dn8Qzrk3kfBbjWe+555QiFPSg3V1tzDsKLbN4RSJJlMSt3gKgYBh6RnUU YuzFvLQvCeSZVKeRAcblScIyrGvCjgQIDFAp34gEQCoTUSFVAmmLT58h+CgROqUCQmXcPKiJ+1U5 dVhVfn3jhOSqm8c2qAOOI4Q72vCIB8WvVsTzG3k/5tJNGiNgigRDHcK/ZBWxzyvbXjGEsINX9qHL PDFVtgIm8RZabyyyXdhqedWUTBVjhHAwWdUh4qyo3iQ7QqNztBeAkaC1CvEcDNwFzaFcVKujPvue sB5GpiFevbZAUjbmVurP2lI05U5iQaszI4binFwOmIYk14h+sY1cMClbiRiTxMqTZG/Ob4FG/yvr Gp36CxcmRp9lEc9oh5EfffxOdETssUxZhCsrYe4xICJ1ttdsDnzkbiFv/cvS1JC2tsFvuThNhgM5 zCIn70d2whnq5G0dbfCOhNQJsfwWQ4sxlZxqsiRUIYjCgadSBlhIsweh3Q779pcOvMxHbRkeola1 whrMqjj3ceQjtia5mo4miuLOI8aclPNGEIki4gSN44qXYeYqPkeRGtrrRxXY7sEWK9UENVw22mkg VvPxU1IspL2ry0KAKWL7jtdQM+keUETv0m2JN9sgKis6AStwamwskbjgIrcUCJ6ltwsK43I33wfC deE9idQOcEGi9DRocwsJinN+DX0NqGaAlE1hdSVAMC9vWDGX1kScY9K4SIzODyUaRfcagPmB/hUJ FpdYv2INuiuDbbbH9gJUERQANkTClQVlwXWEBMP2nCqeAvzAKALAHCiKVIMDAVgc6xB8ANhIRSMZ /APcB5QtAkHYANYRBUKAKoZH4e5drOfOPA8gGoFX0AbYBEBuUCoAI2haMAiA1AKEoCCQpB/RREEK EsQvoAaAEAGLQcAws6ywCQDAfuL6AHzHigmFYlsAqBgqE4CYFJWKoB4DygVAForwHi4AQXECpVLW KO/nIKwGFWIJhBqIqUrAM/GdS06lxqWymIqJQ2KJSufbEUVG6MKGgBq06dLoWvYdcY48/cUyw8dr u7xKejac8W56frK81CK1XFIxW4LtR295HSg7OLX1pGC2NKhmZm1vSNpEunTvF7Kdl9cR6jtMFHGN mU9gTrMQ32M0VHrUCmtY2ESYzgHciQ8tZf04oKSomM7iLOcN7Gnl6TDdK/VENb2EqUBlQj8RkElz JpKTfmWjqGzHJ3oZA6AMO5J6379xyImqylEkcR0ShJyJaHhSSRkGnvTymcMSnSwVk0W5wLuZFb8+ MeojFxtCmIks43mkGkzUglx1U9Kvnx7owwaDuq8lkkcsebk16pFPAeX25cp9gbF/eaRl9ueETZ0f Le5ewtO/v1aVNDd1LU8z3DSYd7dytDY4rSL64KkhzjFQGRMi6XF6h6hmuOcOyz9LSoi9T8KZIZ4q 2tEwtfJ7NFKjtbcT+NQ3mdoLcMZHfwN/AoMSwgjDq0h5IyOvgFwZ/RdyqF4lqpeFbHo5B3jDv6cy QMwdnNyIlI52z0JdCpWQuxCCym4a9HBqEcW+TwWrJujtAcS9l1kNR9X78dQHU0Z3r2PhmCtDht9J BecN4dqYGYBgGEGImKoJi7gbu0oHP01JbjN9PzeeITDtOxB0kRMSujYLLZIi5BcWKnXHrpS8CTMz BPqp+sZ4vVfzgfptIY1SlonKtbULn31zZEigryZehNqtAqN84TJdqFwHVcCYjaW15GRgDxP2vzTJ 6t/D0rWJdUhwvEkkOx8xM6snNatCyYUFAryDxFXXlnqIUl4BNetzwOCLfRYroHPI334iK/XTIJ5F tH1SLpYjwtPF063LjIhBySLFC6MUq1RV5E5EmmJhBpWFjhVqKRKLHeXVEoL0m77LKQjqSaTkzIZj khynd3xxDkBzNlaKE9Ndl0RTWxQmdiO7qSL6YufE5QZYefOIO43gGj1+AyRtvccjUY8Q/8JcBO5p ksprt6xXPVnj4qjpPhAdy5oLzXvZMiASVtsru6MnRr1iLu06GGwsCJSdLfYZLJWt33g61qYOXNlQ CJYBkW0lLNHgsypUIM09l6k230ODUXlSm6mjoSgRSxt979DGrDQE3uyMZQUNnmAtrAgMgVeqmNyk pGIYhUv7eFk+av0Ee7MyDmdCyC7W2qO95kKC17Ej260ee1cgjQS5lzNtvZZmWPExWXxO1WrcmNkK RalVgMJTrxXDRbk+BVOUZJiuExFwhdAYP1GBIY8t33EbZp3jZ7xMUObay+vBkMHPzrEwE3ChPAL5 +Nu4fdhND2GIzRrYoaInLjjA2kKH1vRVm9xl6jUlNCzTjUM6OdbTlgqSVsEa6xXGoeiyOGsnE4ZZ sNeQ5k0bUfNu/eQ1LH26kiQmNR7fvhkYqpg3jj3p/E5KXhdq7DjQIrL8NHzYoGzKHwGNF9UFI4Fw WmhTgBYvtgfxdyRThQkFxXEEcA== --===============6998130621782938845==--