From: Ole John Aske Date: December 1 2010 10:12am Subject: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58626 List-Archive: http://lists.mysql.com/commits/125629 X-Bug: 58626 Message-Id: <20101201101211.A7330222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2105789153791743948==" --===============2105789153791743948== 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-01 Fix for bug#58626, Incorrect result for WHERE IN () IS UNKNOWN. NOTE: This fix is backported from 5.6.99 which already seems to have fixed this problem. The fix ensures that if 'Full scan on NULL key' access method may be used for a table, we can't assume that any part of the predicate is covered by the REF-key. (The join_tab is known to have 'Full scan on NULL key' if any cond_guards[] has been defined for 'join_tab->ref') part_of_refkey() will therefore return '0' if a potential'Full scan on NULL key' is detected - Which will force make_cond_for_table() to include all part of a predicate covering the specified 'tables' and 'used_table' mask. modified: mysql-test/r/join_outer.result mysql-test/t/join_outer.test sql/sql_select.cc === modified file 'mysql-test/r/join_outer.result' --- a/mysql-test/r/join_outer.result 2010-10-29 08:23:06 +0000 +++ b/mysql-test/r/join_outer.result 2010-12-01 10:12:05 +0000 @@ -1427,4 +1427,44 @@ WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AN GROUP BY t2.f1, t2.f2; f1 f1 f2 DROP TABLE t1,t2; +# +# Bug#58626 Incorrect result for WHERE IN () IS UNKNOWN +# +CREATE TABLE t1 (I INT NOT NULL); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (I INT NOT NULL); +INSERT INTO t2 VALUES (3); +CREATE TABLE t3 (PK1 INT, PK2 INT, PRIMARY KEY(PK1,PK2)); +INSERT INTO t3 VALUES (1,1),(2,2),(3,3); +SELECT * FROM +t1 LEFT JOIN t2 ON t2.i = t1.i +WHERE t2.i IN +( +SELECT STRAIGHT_JOIN t3.pk1 FROM t3 JOIN t3 as t4 +ON t4.pk1=t3.pk1 +WHERE t3.pk2 = t2.i +) +IS UNKNOWN; +I I +SELECT * FROM +t1 LEFT JOIN t2 ON t2.i = t1.i +WHERE t2.i IN +( +SELECT t3.pk1 FROM t3 LEFT JOIN t3 as t4 +ON t4.pk1=t3.pk1 +WHERE t3.pk2 = t2.i +) +IS UNKNOWN; +I I +SELECT * FROM +t1 LEFT JOIN t2 ON t2.i = t1.i +WHERE t2.i IN +( +SELECT t3.pk1 FROM t3 JOIN t3 as t4 +ON t4.pk1=t3.pk1 +WHERE t3.pk2 = t2.i +) +IS UNKNOWN; +I I +DROP TABLE t1,t2,t3; End of 5.1 tests === modified file 'mysql-test/t/join_outer.test' --- a/mysql-test/t/join_outer.test 2010-10-29 08:23:06 +0000 +++ b/mysql-test/t/join_outer.test 2010-12-01 10:12:05 +0000 @@ -1010,4 +1010,48 @@ GROUP BY t2.f1, t2.f2; DROP TABLE t1,t2; +--echo # +--echo # Bug#58626 Incorrect result for WHERE IN () IS UNKNOWN +--echo # + +CREATE TABLE t1 (I INT NOT NULL); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (I INT NOT NULL); +INSERT INTO t2 VALUES (3); +CREATE TABLE t3 (PK1 INT, PK2 INT, PRIMARY KEY(PK1,PK2)); +INSERT INTO t3 VALUES (1,1),(2,2),(3,3); + +##'IS UNKNOWN' should not return any rows as subquery returns an empty set +SELECT * FROM + t1 LEFT JOIN t2 ON t2.i = t1.i + WHERE t2.i IN + ( + SELECT STRAIGHT_JOIN t3.pk1 FROM t3 JOIN t3 as t4 + ON t4.pk1=t3.pk1 + WHERE t3.pk2 = t2.i + ) + IS UNKNOWN; + +SELECT * FROM + t1 LEFT JOIN t2 ON t2.i = t1.i + WHERE t2.i IN + ( + SELECT t3.pk1 FROM t3 LEFT JOIN t3 as t4 + ON t4.pk1=t3.pk1 + WHERE t3.pk2 = t2.i + ) + IS UNKNOWN; + +SELECT * FROM + t1 LEFT JOIN t2 ON t2.i = t1.i + WHERE t2.i IN + ( + SELECT t3.pk1 FROM t3 JOIN t3 as t4 + ON t4.pk1=t3.pk1 + WHERE t3.pk2 = t2.i + ) + IS UNKNOWN; + +DROP TABLE t1,t2,t3; + --echo End of 5.1 tests === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-10-29 08:23:06 +0000 +++ b/sql/sql_select.cc 2010-12-01 10:12:05 +0000 @@ -12917,10 +12917,23 @@ part_of_refkey(TABLE *table,Field *field KEY_PART_INFO *key_part= table->key_info[table->reginfo.join_tab->ref.key].key_part; - for (uint part=0 ; part < ref_parts ; part++,key_part++) + uint part; + + /* If execution plan may use 'Full scan on NULL key', There might + * not by any 'REF' access and entire predicate should be preserved. + */ + for (part=0 ; part < ref_parts ; part++) + { + if (table->reginfo.join_tab->ref.cond_guards[part]) + return (Item*) 0; + } + + for (part=0 ; part < ref_parts ; part++,key_part++) + { if (field->eq(key_part->field) && !(key_part->key_part_flag & (HA_PART_KEY_SEG | HA_NULL_PART))) return table->reginfo.join_tab->ref.items[part]; + } } return (Item*) 0; } --===============2105789153791743948== 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\ # esmqasng1tcpa3gr # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1/ # testament_sha1: d670c93d189c3a0d0877353be0ebd04e6349fe5a # timestamp: 2010-12-01 11:12:11 +0100 # base_revision_id: georgi.kodinov@stripped\ # l819wohslm0k87fn # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWRZmHY8ABM1fgHQwWP///3// 36q////6YAnPfPqfXp28x0AFoxOwfdi9np1VHtps0MomQp6m9NU2k9QPUepmmkPSNGnqAyHqNAAD QPUGSjVH4UepqYn6IjIDQ0MjCA00ADQ0aNAxEieEQaNAaaNAANDTQaMgAAAaAEiRGhA0lPxR6nk1 PU9U9T9TBT1HmqMjTajQaHpGhp6j1A2pSbSnlHlNkmTaTQxDEDIA9QAADQAASSCA0AmTRGp5DQjV HtI0mnoekR4hlGgaNqDoIF8yitGaq0gIj0JZmorggcHr8DJ+Zbd2SxMWm446sMX9ZUzs8rGIpDFB XfnkeHDU34xXmivQuppPWXhJxShJXEIlqfJVL+j0BJfLLOZ0eBfGfCI3hsX/XiC9eQHf8oAgoQhl yX64/oVdewoXvFJZmRVmcYUkqV8BV9K/ZeZNsbG1g260atflpjZliNbDisgptpO10+FCtg7Ss9q3 0CqFdzPoFeKIvBdwPJzaLYtVRkeuItDY3j7i4UiYZAYYBUCm5TMp7RhV6dwjAYC42q9ejzEvOZP4 1Se/XmlhFKOSqLZTtqqikPwrwVBU5GER11EndcEkDhvEEb53EAfnmMkMa4pxHwFAEyCYBOEeBSsX LB9pUpOloO0GTxOmw0r3EHsEzsFIkKBSAcAKg4DOhCEBQLwLgLWAwKkytjtLoifvU3FpyYX8evqH 09e+KbVzcLNgpsS6trY3w0rR+jyvD4tmud2tq5mpowqyuQagYxiOsaOT8DpkvTauf0L1HYagZxsZ 1BzHVC9v8vua45pvt8Qy21itLSwcUMPLMV2kgtACQTiw08ZvjnBD6hKm2BOs4BgmSsRii9ZW7sZh YwkSEoko6ISuTjDDJDgVGAmSMCGRURd584px45AX4oBEbEzNhVWXQdAKytlQInicUkwiETITHJhI eDSDIctwyLScQydURU4+JVWoVkx66RUKxxWRJ48jYulT71Skyl0j08X6DwI+B4jECAH1WAUDCLBH linoh+feZ9UREGVtN808dzCRMYVlatWBonpz117HX5LT49SHMyBIjvycZmvvDN7OqwMvQYcdE4vX AU1Zeo3brLdJhVHzXsEUnSNdaLgQAY4nEHgjZwzBFII4GjgIuVuacYWvNMlQKkOiRMoC1YUTiROR Wb+P7bS2os7MyxhLr11GY2EBBeTLIrK9umhPZB55uUCqdVm6FI4Rs39Nu4+wpVDK9MLS8tUDpZZE 8S4g5h5vKIDdmZpOLgNG82GkDgEC82j+I/53oxQtZRqKS6t5VkTFtXCYLr8OYvtU5TYXFv85yt28 u1NU6ytSTjtsHaEVNPmnB5i4dUpt9fT0QKyYYyMs0VakrTDb3V4+BvoJ1sNNIW+mwviRI1xL/UTK oeqJ0OUzpOUB48lSdDs0TJ16zHUG6gK1EuHGfcoTyrryKMI2ya18Rh6g044dfYOCs2zFBXSryDt9 t7YqAe2WNBWxFsGwwyTp4gKnVJDrhRUFgqUfxavoikB1ppNZkHAHzJpGNJaGTLCWAefT6LdBAWzU vn6iIFQkvhfSOlcYr6ziG2237wJzJigEyisF2igTGKGKkgUDA9oL4JJi7xfUWlwJgdXqDuP8SXeZ F/S2BxF5gQdwlWKAC5wmlWwXgP3gM3CNgH0gYAoAZAWCkCwRgyZkDhMAwnAQERCzIRMCHgbRfMSx BPF+KnTy5JaSMgnPcMVF6S/YWkMz4gvmTCzGFiIvFOzMzyCguFeK0CYZAmKAqAtFOQtRrLctDSKs CBImsyasESwmM7Tw585t6FyzUkDgXL6yTs6MRUS6X+yE1PEUhBwzVM8ARzBuMROjkriADRnCCpJA qpK/J8Bez4lJoq3bbkTvXmY1UTX8jBJGsyVnvSPzEPfAaSbyXvgUED+MpNo49w6V2/3fE915hxPg 4d2DuJxLS4PybcHZOuhIlyYpvJmccCUi6fsKQr6lA4rSYvNhLCc6yJSdbbwe5hA/9VJAbPwufFOO Yiw7zYSSrZTtxZzLe92KZjfpQQMKEbtZ0TcziTTBhMRbsFcuqNKWmlYMOyBAtYJg9Te+HlU23/VZ RUuOKGGHuc5Z50SA0Ru5jMp7ySosiRdm0NOWBUFyYse89Ctep1X8tNz44O2TZ5lE67O22pnxmmMW N+9xgNTzdpUzf5cVrDLjAacIOwT0aqArOh2szcqwfuc8KZbnYiNx01oaAqLHvEXRS0FemjUuLeTs sxNYausNwOK+TykrDaSDjMIeJ8y3rMrWnYedbSfAarfO6M5W6qkMRFaGQrEKzZwiHiZh+6XK1vVQ pk0pGmuJKp1DDfQMXOd4fjWTkY8QVKdAqVBbMud4LcAwKh6YxbLrXcO1vzSbSfM+bUhl3YE6DE7S ktKckDbAXs+1FCdXFdkeZpgwhLApxKLIMSBDUgPminaLTrEjhkGYs90OPrOEv3AK886hAF35VhjZ 7/VWYLmKfxwVlngbUrtpsCIvpI8oWmCcp0/+BEvPuku8LUB3pDxXKZkk6BG2L1OBYjJdvWBbtMpY mAOtYSS0G9M8VKxds8WbhKmdeNFAVSwEVjpribUnJu6sH0VCf4OipIelNZE7EF6rjsUECmspDiFe IxMoSXRn9JecjCZkDMigcpBRK9ZJ19UIp1h1fDVIzTIPcc4NeP5eNXMAoMuDKlC31F6D/kBxCgzp qZdpBajujq0MxMoqcavbuQQEX5MmQwUMvUeXOB9ZSHBVXXeACvJyXmxd212PC+EMDJwjZXcl1V94 YjzEBzgy1E5ModAySJpDpFBNNDv3KFa9hkPVG9xApLWZin+ghLEA4CgUnpEaLjcSdLXZG1hoAF3Q RDbGrLC7Rtz2MJOEqaLIZpOkuq+S3HjC21Rcsgg9y87QVBAtE9W/Bkj0WlzLzKQiIjAyPOuVtt4r +iUEFisYsoq+ZSFnndhnTaPs8m5SmUvVVV1Sggz11rEjYmCPnGID+RMFAwXC7C1Ym7oBScQu1Irj BDSujI67S1gdPcMBICiCCAO4ntpz6gk+G8pWIoK9qEyOqQczmrFAl6CoOvH6vUH4Hj5UqZbD7A9N aUE1rdAeBE2nKkWuMVtMCIqzWCeWfWtwspzHZlLzgMt7ViI1LDbgk0vHjdIyNvVinWipg7hk64iY rgukoYkFtEsjkdIf/F3JFOFCQFmYdjw= --===============2105789153791743948==--