From: Ole John Aske Date: December 1 2010 10:21am Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3380) Bug#58626 List-Archive: http://lists.mysql.com/commits/125632 X-Bug: 58626 Message-Id: <20101201102110.6A0FA222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1846159431603965038==" --===============1846159431603965038== 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-spj-scan-scan/ based on revid:ole.john.aske@stripped 3380 Ole John Aske 2010-12-01 SPJ-scan-scan: Cherry picked proposed fix for bug#58626 into SPJ branch 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-11-25 14:13:23 +0000 +++ b/mysql-test/r/join_outer.result 2010-12-01 10:21:04 +0000 @@ -1441,4 +1441,44 @@ i i i i 3 3 NULL NULL 4 4 4 NULL DROP TABLE t1,t2,t3,t4; +# +# 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-11-25 14:13:23 +0000 +++ b/mysql-test/t/join_outer.test 2010-12-01 10:21:04 +0000 @@ -1022,4 +1022,48 @@ SELECT * FROM DROP TABLE t1,t2,t3,t4; +--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-11-25 14:13:23 +0000 +++ b/sql/sql_select.cc 2010-12-01 10:21:04 +0000 @@ -13128,10 +13128,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; } --===============1846159431603965038== 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\ # grubtn0qgwtf1adv # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: 8d454a80addd5121259c20d478a716eca5101a26 # timestamp: 2010-12-01 11:21:10 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: ole.john.aske@stripped\ # jrrm0vz97m5xy2re # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWRErJQoABN9fgHQwWP///3// 36q////6YAnE75e3nbe3ZqSitTDVKEQrKmJjZQyVNlMIymNJowxE9R6T0g0HpMh6QANGgMjQSSEa AAJT08RCNqajExGmam0mgZBoaA0BFGp4p6Ro0aGhpkAAGgaZNAAAANAEiQhMRMpmmpkyMR6Qek0G 0RoANABoGgcZMmjQGjTEZGhiGBNGmIMRoMIADBJEjQ0JiamySeibRkFNqeSn6p7SaR6j09NTCnqe oZPUPapRBBwGEavCvUU8ybGxFHV1U2G0Gft+tOkXtulGXjtMLfDq9GvRGb0fuq8/6lGbRzlgoZzc VA3QKyldVUlqBlT4FCKgQFqKykISFapVCkQGUyz8HhulaEnM0PFisRCtXrAcCFIhJl0L+dv1Ob4c SZe0VBmZFGBnCcqnX3ip8a96zJttptXbeyjTi6teJmzVKQShwUk57Lp+qiph2ik9xw0iyFo5j2d4 qxRF2HOECkzFwGtU7iYWpmGbR9SoUCYLgYJC9gUblunMT5jBHHcgsLCs3TIuXeJcBj4tqT7O3lle KUZ6RzWzpbVVFkP+1X0CKRFYR11AnhUEjHjeLoPmz+QBAEYyQx2TpxEQKAJkOtgnDEBRFZkq5eBQ TNRIGkDE1rU7TYvqOeImPUKBATigATAqDAMiIiBQLILANR0FxVz81qz68FZq/kjXjxbrc922MfIk s05v0rMQKkBPHeRAiYrZ2cyVkp2YoVOQoXEECEvVlUg2wYxoNI0Z+Y4ZLLgWjxHmNJugzaYz0hvH kL3e/2tcb03YGnKQrS0sHKGIFmC3lYWgBaE18O4zvhmcESiJi8BlMmEYbB59DYklZcFloZLC+hp7 zUNhhWgMFmgLdlcmTJgT0BvvBTiYV4u/4+dWYVLuX4SvrpAGZu4u3+E+AZGQVMQAKBhDAEA7gYLi mQUYjYJRWTvJswiord20undmxEaqfVUCsZy0HKvoIC95IBZ4jyjECBmwuzAQfXiIrHmYh3Wb+yQR 7mE0b8ZY4xEt8Uqn8YZaw2UXVDAa09rFSN6e8HUSDRP+3dC01F4XlirzY5PPAXBMuAugRYqDub3i jqRFsOAY4nEHgiwEREZl92kRkpwnWwvJx5tjJOthNxpOTbM3Sz67DMoNpYHFYUGgvMznT2l1uooc cStzzGYxWZIIJ3m4tHoJBluKy2uFchprhDprKpp6rCy83hCr7Fr246HaUq9kyuZraWuEZSi3wXBi ZlsOUoxDGI4bmRpLnMp8pEScHC6R5yyiaerfOfcn+8g5b7JBaE+6JPbI8O+K6z1K8kLDbA4k5nxk 2PGtInFZ8zLjuPitds+GhDFUg2BdOcOd85UZb0UJU2FJLa/kUbizWK4vLymlqrTAhKbpS+53lNIY oqJYFO3PSpikxKa6iOASMZrgMXzfKRwLBgrrGgaKrnKy19EYzkxPUTGVZZKSH/J7481pEiFR4frj CoJ2ax2EzSYwGRpUkh1ugWCrsdnU5IrgPQmk1qgcgPQMFHeVswxet6ToEBa9qlOg6elTID0gf0fA XsNRalRkG223zgTmTFAKIKywXpFAmMUMVcgUDA+AL9iTF/BeMtLQTA8gfQ6BLoMS+y4gwLiQH1Eo icAk6cUf9DgvIZwGNqDEDsAvBTAaQLRWAsEYMmYBxMAwnSmQTh9sUEQRADMXzEsQUBfJYqBeJd9p rNQUniMVmkS/8LXNmfQF9yIsxhYoMBUMzM1wnXMUivFQBIF4QE4JAKBQeK84G7GJoKIDgi1akyqQ QtLz+h5ejM5etdUyggZxdXrINN6uxQSlU3ssTvP6GdMh4yf2DyBR20ATE5+XrcgI3+sQzwF6vkUl 6k+HgBQQHbkY+KnPh8S9JGUUrfegHeA0k3jAiQO+UmY6/FZDGjYZcR4fIw8RlEpD2tmoVuEOXWkZ 7Mc/cOl5qSBFg4rVWFZQRqqOUnOSoZtQQcYQQ4fsWIDT7b4RHLTeXlgq2UWrZ2DVBzcm1GWr4SMj GtEUUD6zVOlBSyLCOw2HDLVi1xJcoPSZGjVSkfXV9M9+f26ZlNRB8rHbe00OiahAjnx2dR/YgMZD t0Znz5+Ym4JFQx0nIWK9ZoDiyucmfgqhZZ9P4LN5KqPdtzvkf1P2rjwliGo1fJ2mDVR4yFLGrGA0 vGujRu5/DjGtNn3oDUhB06eh4q9Xc0jcqHv0fBTU6O0NqEM9EanvEVyJZpQmXU30uv1E2kwjphkb Th5h9MwTkumbabiIWQOA4zAOFutcyzWhgas2kO8Y9LdjRY6mbAo2rjiefrSuhuy6Ei083aItCsbc BftGG/gMaXf29vwtKSMeUQSTzFBf29wLiAYFFcDZ7V1j7Nt+tJuGnI42kGn0YFCDSdpUXFWpA2go 5Z488vp73CNWgqx0TJ7mvFgxYEV8m/oXRwQHdsGYs8JoLk6jjsrAWghAQc731hfL390jmFH8aq+o ypYdYyBQXCUzzvGqlGkmT3DluW+GFAb6AkKpTMUnWEbkSaLzDgwG41mzxewB51eSOFPXJpllq30T M3FXTQc1NIVVyGntJ+MiR3SCFNQodTuxOgIwCJQsI7EGJTI4iCBTTiIV20VHb41raDGbTCTCZkzI qHUFYFWrBYBnC5abItecvj/gQsxf3OsGT9lpepOAeYYrDFJ10zrADyQGInROdY9NzDbd3MzFElo3 aILcWTIYIsvIerywOomChT2WUP8gCtJiHG+Z/Kmt4XNCGBZ51l6BFlyXVZ4FwLkmz5IH8BkkSFY6 0iSyw+PUhQvlXaZlcj6EGcYnmTeTGvscKiTWLQsK1oazPbVAlSdKrvBQdx8hEHtdxiustz35SYIM 6VGdLscR6SU+iq+8PQEpFzn0lr0lFTU1gJmoKS1Pa0BwaiMeQ1TQtDbbuxHglBBjVzFjFVwEkZZ2 7069gfHm3iUyvGUpbRQQZaqlhR1kwRpGIDnEwUDBclt7VidvhE7kV+S2Eylusnz82HOLXrWZaJu8 IceJgq9FtGVmhW+Gx4REJLgE6xapMjdaHQdCgWdJUqRG/g9fcvcX+3E+IdlYqhs24g6yczOWoWvT rLzSgsMXmVBo9i4RZHKd/mWJeecYosGuEVLVWJ1NcO6nJblpFqHYsw2NsIonZudYjkMSJyLznUqn LgwptzNxyB/8XckU4UJARKyUKA== --===============1846159431603965038==--