From: Martin Hansson Date: September 28 2010 8:41am Subject: bzr commit into mysql-5.5-bugfixing branch (martin.hansson:3204) Bug#37333 List-Archive: http://lists.mysql.com/commits/119233 X-Bug: 37333 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1879740873==" --===============1879740873== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///data0/martin/bzr/bug37333/5.5bf/ based on revid:mats.kindahl@stripped 3204 Martin Hansson 2010-09-28 Bug#37333: Optimizer uses wrong index for the right table in LEFT JOIN The range optimizer assumed that an index can't be used on an IS NULL predicate on the outer table in an outer join, and refrained from calculating the cost for it. Since range access estimates are reused for ref type access, this lead to a wrong (too low) cost value for the latter access type. Fixed by removing the bespoke assumption and thus calculating a cost for the range scan anyway. modified: mysql-test/r/range.result mysql-test/t/range.test sql/opt_range.cc === modified file 'mysql-test/r/range.result' --- a/mysql-test/r/range.result 2010-08-26 12:35:38 +0000 +++ b/mysql-test/r/range.result 2010-09-28 08:41:24 +0000 @@ -1667,3 +1667,36 @@ c_key c_notkey 3 3 DROP TABLE t1; End of 5.1 tests +# +# Bug#37333: Optimizer uses wrong index for the right table in LEFT JOIN +# +CREATE TABLE t1 ( a INT UNIQUE ); +CREATE TABLE t2 ( a INT, b INT DEFAULT NULL, KEY ( a ), KEY ( b ) ); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +INSERT INTO t1 VALUES (1000); +INSERT INTO t2( a ) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +INSERT INTO t2( a ) SELECT a + 10 FROM t2; +INSERT INTO t2( a ) SELECT a + 20 FROM t2; +INSERT INTO t2( a ) SELECT a + 40 FROM t2; +INSERT INTO t2( a ) SELECT a + 80 FROM t2; +INSERT INTO t2( a ) SELECT a + 160 FROM t2; +INSERT INTO t2( a ) VALUES (1000),(1000),(1000),(1000),(1000), +(1000),(1000),(1000),(1000),(1000), +(1000); +EXPLAIN +SELECT COUNT(*) +FROM t1 LEFT JOIN t2 USING ( a ) +WHERE t2.b IS NULL AND t2.a = 1000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const a a 5 const 1 Using index +1 SIMPLE t2 ref a,b a 5 const 15 Using where +FLUSH STATUS; +SELECT COUNT(*) +FROM t1 LEFT JOIN t2 USING ( a ) +WHERE t2.b IS NULL AND t2.a = 1000; +COUNT(*) +11 +SHOW STATUS LIKE 'Handler_read_next'; +Variable_name Value +Handler_read_next 11 +DROP TABLE t1, t2; === modified file 'mysql-test/t/range.test' --- a/mysql-test/t/range.test 2010-08-24 15:51:32 +0000 +++ b/mysql-test/t/range.test 2010-09-28 08:41:24 +0000 @@ -1326,3 +1326,38 @@ SELECT * FROM t1 WHERE 2 NOT BETWEEN c_n DROP TABLE t1; --echo End of 5.1 tests +--echo # +--echo # Bug#37333: Optimizer uses wrong index for the right table in LEFT JOIN +--echo # +CREATE TABLE t1 ( a INT UNIQUE ); +CREATE TABLE t2 ( a INT, b INT DEFAULT NULL, KEY ( a ), KEY ( b ) ); + +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); + +INSERT INTO t1 VALUES (1000); + +INSERT INTO t2( a ) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +INSERT INTO t2( a ) SELECT a + 10 FROM t2; +INSERT INTO t2( a ) SELECT a + 20 FROM t2; +INSERT INTO t2( a ) SELECT a + 40 FROM t2; +INSERT INTO t2( a ) SELECT a + 80 FROM t2; +INSERT INTO t2( a ) SELECT a + 160 FROM t2; + +INSERT INTO t2( a ) VALUES (1000),(1000),(1000),(1000),(1000), + (1000),(1000),(1000),(1000),(1000), + (1000); + +EXPLAIN +SELECT COUNT(*) +FROM t1 LEFT JOIN t2 USING ( a ) +WHERE t2.b IS NULL AND t2.a = 1000; + +FLUSH STATUS; + +SELECT COUNT(*) +FROM t1 LEFT JOIN t2 USING ( a ) +WHERE t2.b IS NULL AND t2.a = 1000; + +SHOW STATUS LIKE 'Handler_read_next'; + +DROP TABLE t1, t2; === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2010-08-26 12:35:38 +0000 +++ b/sql/opt_range.cc 2010-09-28 08:41:24 +0000 @@ -5765,8 +5765,6 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND param->thd->mem_root= param->old_root; if (!value) // IS NULL or IS NOT NULL { - if (field->table->maybe_null) // Can't use a key on this - goto end; if (!maybe_null) // Not null field { if (type == Item_func::ISNULL_FUNC) --===============1879740873== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/martin.hansson@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: martin.hansson@stripped\ # 8qjjs2c1w6t5o688 # target_branch: file:///data0/martin/bzr/bug37333/5.5bf/ # testament_sha1: 979cf0b005b54405ca779ef1b5a385fa452cf85a # timestamp: 2010-09-28 10:41:28 +0200 # base_revision_id: mats.kindahl@stripped\ # 1755fy9wcv22qfd5 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWbmaoJoABPf/gBAwAIBY//// f///4L////BgCS75Sa1QgAEEL7B0G2Oug0MMkjGqnkamjahp6jTTyjJ6TT1AA0NAaAPUAOGmmCGQ 00yMmEA00AYTRpkwAIGhw00wQyGmmRkwgGmgDCaNMmABA0EiJGkp+ieqY0m0TZRppgQBoDQA9JoA aAkklPFP1RtqjT9UfqmRoAAGRoGhoAAANBJII00BNMQCMpgmKZDRqYm0RmpmpoG00kIrPSK5Tc7Y et3c0Of+rN7P8Kg0XyReoWkD3rsrgFoUc9BjjPJZM7P93dkM7ccWWpuRbaQ8KBniaNpDbdL5e51F EKggjRgNF4X+MGylfrj9uEPhniErw6niKguA3rie0P0NDIcf3C0GBgAhdwDtGAL5szhHCuhaibbG tX6hw0v7xyhMvRQhm5UgoUzuBxaBVYHEOh7xUcAGo1lIRED3g5LMHlwaJQPqWgj9T3HhuPzGKTkE yQMewEQEMHLn2/u2Gh6zO5ZZcWFs9/9V71M2FJVauLV+fHZRtYajWoTiw4FWhV20UEUumgMMppDn CA8QQJfAJDKWCNVR5gMbM1LKGAclqSEq+Vp4NPDAvGoq3FZgxGSxYqJhQg7/I231VjINdhvCTA3k MDMAPzu6IhpAem9wOBgD5YBsC8Kw1h+AfYH2B9YWBWTQDMbbB8iGI4wzsEHKM2NgwJANrKsF1zMH mAfvAFgaGn4Km5uw/9zF5v7t53U7+N9oEVYmEpuSNpQpFtqkytVVZmLiSoljKXoQ2NOWAoAVgMNw kIAGGcoIIJGDrcGSpRZZC1WCGhUuKohSFrNkt/2MgxEWVxbR7dfAMlpJKYFZ7ASUOKQSgLBB5WaT ObfHjybhkwLqyqDAisBFkObVT3ONmNOWNJUleED+L8brgqWplduOa9vPiPIES9XHAvWh9zy/fEUK xHGmhOBHEHuAwLguxcAXh/K3pyMLDBPENacJvK7esJiHSpLcpwFeCiaHHjMmegRQfKkdUddu07TB a8LGlu3BWTMLSapOti7aaXK5RImU6IFRmUH9Dae5VVlhJYDWph7zElMwpMzbP0cvnfWbAuVQwiy7 IoyRZERRzgpHLc+hiVI86sI2JqEQ4U/TSYGol2GGmDtYcd2oiUGqsyM9xQfy/uV7LVJSnEMiyBmV Lht1j5LVyxVJM+7vsJRoYZR5iPnO8hoeZ1ZauARLC0lSXyeJ2hV2wlc0TI+4RU/pyOOaLzYU9hia CJQNRBzZA8s9GJqPHTGpazVjsKipbByp6nuLYUyR3GyR3FPMeZhWYRHBbkFw9YFAl2SOgUJlsLqH iI7xh659VakgqNbmza/NjfylyEVVFmsKy6FWCExVF7MXZvLSLnFxIjafMrldCsKm0hBTtnY0KJJn hJyHV2BcKtzg0+C1qfbJIpAbmlKd8NtG9ettdhbUJi4OdeLMrUjhXjFynSBkDmCgfcyLqtjbf2FZ wVKMGEiXuCSTqH6OkvCRdIF8CXwGl1xLSDEFQMRiDz5CRYxEJRkCpzgydBbeUNK1hhAWiCwBXDBf sYVS+Yd0WMSrYBegrA0LBKZoyCAMERhIZDAihLQQfFLYAfFGhFTPkfsVF4UhcJbh4hWkUMYBMRkI HjzaOCCKgiXCCaWY8gIZKItA1g8QME/kUgQSgUGpGQRHhaMA0ojBgLRvrOI8L31Iehc+mQL1TGJL 0Axc1H4CoDXB7HGcxyHbyjjymxkdcF1VxKtFGUXyovk9ApJqxkeat+n2WB7zVqkw03HqTgTbkh+D a0Ffze1IgNQ1THtGTbcj8/ZWftMOgTu9/wOgdOJuGPxecus6FdpQa7UdTVFp1+giS/I9IFe1jEsG EWp34nYMVFLTl37F34qqvupT5UKhzCVKA1eCrVEes61pVFE2ajBa9W15kUnhU43qaOE0bYQoSsTl 6SJKEEXD+YQLIpwXLsPFtaZr/k7xgDy1AGYnM1kVDBciigKglAUM+WcyGOGYw42lEpXM99NOzuWS qVbYumFQmMVjfxUP16uGLrf0MC2zEx7czNsqFvxdf32Hfba13xcaqsmDxTI372YZdrOit/XLlFy6 c+K4xQ3faDxhWosWiglt6kxLFU9Qug60husOPKFZHyU5lIVdusM/4Xgs10M3IPLBzuxdIoOK26Dw WpzCuXXjaeJ37Or0jcwPBI5i+tlkvdBUrZIbSnsiZWYlW2ak0qY4EmfQFCkgmStCIdBhIxzECEi3 G0SZyet7Sc5oM4wqDFT1OLANumUbI4RTM+gRw6mG1ngbLPoOPqYXavFljfYDhY4vDNeJrwPLyTBr EnLEZPIyvmpBvn5FLleXUHDp33iD4H+QZc+ncZM9TC10DiwVz+akzakKqo+BRUSaTENwm4i1QkRq nGQktBm2FsFcSYk07bFBDGmHgq/IzvgCoVyVLIZP9OPJbaeYjSfI7OztIdBBpyx2ZDkhsucJ1U6I 2hIfFRSwCWGJyg5NiKPCRrKE2/IuqIYoKjM8yfWyZGspDkFN3sJ1+HA51d1j5h/VRcvEw6aPU/rE XLoa8JE29DnMhl2stgysgCOO/cZyXPmMxVXBWbSSgg8k7dNwdh85OlpxV627ccagpvvnWCmFHV5x 5bsGeoOtW4Q3Fz9pdrlMvzcmNjGKq/IvE9b70YUAt682Zma2vWODPcC2DKFKWAmsRvqY5WVOiDdT 4HYQQa1+v1tkE2AjceYj6LaVVwVgneM5g+mO7iM/sWBzJCcy7GQzX0bfGzYYA8FEdGn6aRYrgteL 6lNyHaZbKzPmc8j1/arjv1ILeXOwMz0yTkPgYqz1xgL1ume0Rt9agTkMDtRUifftenDCPd9Q7h3t e9O0TtxyLTuCHqOi2Smr1YZFeA+m6f4u5IpwoSFzNUE0 --===============1879740873==--