From: Jorgen Loland Date: November 19 2010 12:19pm Subject: bzr commit into mysql-trunk branch (jorgen.loland:3287) Bug#54641 List-Archive: http://lists.mysql.com/commits/124431 X-Bug: 54641 Message-Id: <20101119121933.3B016AE2@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8768312551100174852==" --===============8768312551100174852== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/jl208045/mysql/mysql-next-mr-opt-backporting-54641-2/ based on revid:tor.didriksen@stripped 3287 Jorgen Loland 2010-11-19 Bug#54641: semijoin loosescan gives duplicate rows The loosescan semijoin strategy does not handle join buffering on any of the tables in the loosescan range. Prior to this bug fix, check_join_cache_usage() would not do join buffering on a join table if the loosescan_match_tab pointer was set for it. However, loosescan_match_tab is a pointer from the first to the last join table handled by the loosescan strategy. Join buffering could therefore be used on all join tables except the first in the loosescan range. The fix is to skip join buffering if the join table is in the loosescan range, i.e., if join_tab->get_sj_strategy() == SJ_OPT_LOOSE_SCAN, instead of checking loosescan_match_tab. @ mysql-test/r/subquery_sj_loosescan.result Updated test result after fixing BUG#54641 @ mysql-test/r/subquery_sj_loosescan_jcl6.result Updated test result after fixing BUG#54641 @ mysql-test/r/subquery_sj_loosescan_jcl7.result Updated test result after fixing BUG#54641 @ sql/sql_select.cc In check_join_cache_usage: skip join buffering if the join table is in the loosescan range. This was previously done by checking if the join table's loosescan_match_tab was set, but is now done by checking if it's semijoin strategy is SJ_OPT_LOOSE_SCAN. modified: mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result sql/sql_select.cc === modified file 'mysql-test/r/subquery_sj_loosescan.result' --- a/mysql-test/r/subquery_sj_loosescan.result 2010-11-01 15:11:10 +0000 +++ b/mysql-test/r/subquery_sj_loosescan.result 2010-11-19 12:19:28 +0000 @@ -3495,8 +3495,6 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 ); int_key 9 -9 -7 7 SELECT t0.int_key FROM t0, t2 @@ -3506,8 +3504,6 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 ); int_key 9 -9 -7 7 DROP TABLE t0, t1, t2; # End of bug#46550 === modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result' --- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-11-01 15:11:10 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-11-19 12:19:28 +0000 @@ -3499,8 +3499,6 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 ); int_key 9 -9 -7 7 SELECT t0.int_key FROM t0, t2 @@ -3510,8 +3508,6 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 ); int_key 9 -9 -7 7 DROP TABLE t0, t1, t2; # End of bug#46550 === modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result' --- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-11-01 15:11:10 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-11-19 12:19:28 +0000 @@ -3499,8 +3499,6 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 ); int_key 9 -9 -7 7 SELECT t0.int_key FROM t0, t2 @@ -3510,8 +3508,6 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 ); int_key 9 -9 -7 7 DROP TABLE t0, t1, t2; # End of bug#46550 === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-11-16 16:17:25 +0000 +++ b/sql/sql_select.cc 2010-11-19 12:19:28 +0000 @@ -10509,9 +10509,13 @@ uint check_join_cache_usage(JOIN_TAB *ta goto no_join_cache; /* No join buffering if prevented by no_jbuf_after */ - if (!(i <= no_jbuf_after) || tab->loosescan_match_tab) + if (!(i <= no_jbuf_after)) goto no_join_cache; + /* No join buffering if this semijoin nest is handled by loosescan */ + if (tab_sj_strategy == SJ_OPT_LOOSE_SCAN) + goto no_join_cache; + /* Neither if semijoin Materialization */ if (sj_is_materialize_strategy(tab_sj_strategy)) goto no_join_cache; --===============8768312551100174852== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jorgen.loland@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jorgen.loland@stripped\ # a5y0s172kywdo1h7 # target_branch: file:///export/home/jl208045/mysql/mysql-next-mr-opt-\ # backporting-54641-2/ # testament_sha1: f7e209a6d918016c1d9b92e1f74856a8c782f08d # timestamp: 2010-11-19 13:19:33 +0100 # base_revision_id: tor.didriksen@stripped\ # 86cs5woahaule77b # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWUKJcyYABDRfgFAQePf//3r1 zgC////wYAiufe9594lS7VQ+94XHu0nsppQaswGSIMSZI09TynlPBE09RoHqHqANANAElCZTT1T8 RIxNNDTRoAAAAANAalH6k/VHpMgDRoaAyANBkMQaDQYSJENNVPah6p7VPU3pJtIAAYCMmQaMmgRS IaJoaMRJ5AE9TNKbSMIZNRk0MnlBJECaJkaaniKeSYmk/VPNUAeoxBoABeKCT426DblaoetM1ONQ bW62xlSWRhTH0sZBNmQSoAI2mClNpVmvqyVK47Lv3f0Oj0fmAzjv6PJuVsuC9FdxznwB0hsBtptj FPrV86M0vHdykWPTbqvnDWoavGUZyg9CP5Z8exa7uj9Rocsk5xZEOUC4eN3G6yB/lRKESIneDJKh mqLwVAKYosc0JxkY/UliEgygyHtNMgixkqbIPvxtgMu3GzrDNeuRCieiXM97E2TgKoqAB7ZmMBkU IH1NdG5aslGFYrzvq5NMRvGdBoaBlRc9AAWgIHeadx1s6+hYl16phPzQPu4HXw0nUZTKQLiQN94H TmvBtevIHDr83wntjwb/+49NXa1tTSlkm/X0VpUCWB37b0Kq738GZ+vNEZVC1TwZXLSwFqMkzAwN FOghdmHzIMUyQSQloRB08MtTnbNEyRTbVBdE1LVBKxS1H5wfA9F0WWYKGSoWqy6KiRlGD5Ua1WHn kzbUvNlnTdJsYk5zUJgFdlZWDkOimfdu1bqt3qHZpfncMJofbhm4vGAvDGkg/AFLXwv57VQTLWmO wE4gqr4bHfEKmZWRkVPULIOEPHh1A9HFplsMQXhczENZkazpy9ds8XVplMDkC5mgxqYRHN2pwxU0 txlsP6VpOe1C20syHmL2EFGgpQ5jw3liS048votwNC/bhguwTXzNBzmoV0cLcTa3Mh1cag3wOE07 LTgeplc7G0tGG3HFK4ksaxESFg5RnU3IxRQ5SvjlXUZ1JZ5LCOmF4YWF6ZONGfcq2e4Iy7lQagT3 Kp2TNi2ea0xC8bIreswsQKc5NoUy5X2AxMYKhradqJW3Fjqcp0eUHMJcGRQjALjKQ6zF2dq5mpEa UYg7XDBFSmnuTULMyCgqoLBZIJV0sjhgJ8yiYQfXaNKUHIDPipK9Zq43grXzGQYf8JyY0zFVOKjh cRJmxYdoVNrMX25WGYxcWA1zFu5XXMFNKuVuBb2BORIE4ikYq7Vor7VpxPHjsKbDLY0sCsShkeCi by4WE6bNyYZ1FybNcF9Wo04odDO5OAphE8lEkWWUSkrqOwL8uVD5Dp9+1amN/ANYpNIYRdgBy2YF gDCDrLZAiBCEDj6X7C+h/DA8swK/WX8sf0zf7GcmTIp/5gl5Nr9n7Ot8cXYEcAkHtmEfqTX5q1BW /H4+iTA+ivulQraRabTn8EIqE8TkbDvB5zPlqRufph7wTXURTVBcVFhrezNbl3vvryDXcIWbxc2Q WfDap8WzzG3m2pYFVz8jiDobEUQdI6slE7IW856w0YoIrHBYhFyhusNQQMJ9yS8F5eNPNhDJLLvV 7rJ3GMD3rxLbhyTmpGqDxW84EBB7IfALy0fMyj78T219oarRaM8eKH4AwIvBGU/JcVRHLiK+C9dr g8Q7TDXt0sMQ+/dZ6iqYt8nRTFkfBAxC21ScShgB6Ms1FXVh3gTFJ0pzne4ezyoYhNgIiEOqNk/Y RDtkA4lWg9jOIyfr5tNorfkPVVNPEYI39Q6hXLFwe0CiVCuOnaSStb+ywbe3OVd2MY5m/udCdGej fHP+ZbEMxYXQn3YHZeM+PCe/zbeA981beB2WKPb1puvQltQSN3fADvpRBSxVZDNj61wHY7hj1msj yCRA5UO4z5jqpyOvcr+SRtRUXL4B06AwcEnZVDhknLSy15BFbAT9RDBq1WQDDWmgYl56bfbfamXU HFT2MTTuEudLY/CFiGt2wkmfue9zeTvT+KXwYW+g7vGOj8n8IE5BK7QhNPIKBreKBaewuGJNMM0s a0205sbbustMeDxzzSuwvMDA84aSlFayMJPnYlwZ+jJAzS0eQRfDNl9T8UgNm3B7A3O3xdC1Oo5s shwX0BxxHz7Q69C3E2IKK6URzEtJLzM7w6hFntBDBJMBaiRWY+JdT8Uc0OkCBgJoa3Z1ONHaCc47 jQEaQyTExDhfb50olvL0ZhJ0NcaLWSfeqRCQRAEIoUB0OEE8AHyBx0mAMFFRpJJTUwnUnrv7GlEg hiBxZNvJ2f66e8Jsgc5cz2uqvA+P6QIdp9OtGngiUO/FdB8qKND1YWbQ+wJCzwwRk4FTxUcSOOWN 9f31hM3cFyd3ZAQJ+7Wmrl7XxodeU6VXTe27Zc+uHWLo8laBTviTp8WYe5wGto1KMYBwSdVylTzG /b4VJY0FjTuCQ/Wa0/HDZTRXQszwVQ0lvyUDG/9EYuUEG6C2Cw15pGpuBZa01h0B+zs2KNRIOUWB rWESo0i1V8S4w3nfXQESkpzW0jyvJqGlSiWGA3Ss4iId2dmZpoIXY29FjNskoLfGd/pHNFFWQOpA U2o9Ny7WU7EUd1sTI5K0bWi0U04DTB6sDMg/Oe3fmRmzsag1LqYSLIHIKVK+r3vJXwA+uk1OVqFs Td5hZbyeeA8NnoMgNreN3yb3uBnN97VO44PF9G/J7bGYmO2It4SnjEpxey1Awr62jmsgbxx0pp0u b1P/xdyRThQkEKJcyYA= --===============8768312551100174852==--