From: Jorgen Loland Date: December 8 2010 9:58am Subject: bzr commit into mysql-trunk-bugfixing branch (jorgen.loland:3427) Bug#58456 List-Archive: http://lists.mysql.com/commits/126298 X-Bug: 58456 Message-Id: <20101208095858.C04F172A@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3850363521987788948==" --===============3850363521987788948== 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-trunk-bugfixing/ based on revid:marc.alff@stripped 3427 Jorgen Loland 2010-12-08 BUG#58456 - Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output in opt_range.h In this bug, there are two alternative access plans: * Index merge range access * Const ref access best_access_path() decided that the ref access was preferrable, but make_join_select() still decided to point SQL_SELECT::quick to the index merge because the table had type==JT_CONST which was not handled. At the same time the table's ref.key still referred to the index the ref access would use indicating that ref access should be used. In this state, different parts of the optimizer code have different perceptions of which access path is in use (ref or range). test_if_skip_sort_order() was called to check if the ref access needed ordering, but test_if_skip_sort_order() got confused and requested the index merge to return records in sorted order. Index merge cannot do this, and fired an ASSERT. The fix is to take join_tab->type==JT_CONST into concideration when make_join_select() decides whether or not to use the range access method. @ mysql-test/r/join_outer_innodb.result Add test for BUG#58456 @ mysql-test/t/join_outer_innodb.test Add test for BUG#58456 modified: mysql-test/r/join_outer_innodb.result mysql-test/t/join_outer_innodb.test sql/sql_select.cc === modified file 'mysql-test/r/join_outer_innodb.result' --- a/mysql-test/r/join_outer_innodb.result 2010-04-20 07:22:51 +0000 +++ b/mysql-test/r/join_outer_innodb.result 2010-12-08 09:58:55 +0000 @@ -17,3 +17,38 @@ id select_type table type possible_keys 1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where DROP TABLE t1,t2; +# +# BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output +# in opt_range.h +# +CREATE TABLE t1 ( +col_int INT, +col_int_key INT, +pk INT NOT NULL, +PRIMARY KEY (pk), +KEY col_int_key (col_int_key) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4); +INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5); +CREATE TABLE t2 ( +pk INT PRIMARY KEY +) ENGINE=InnoDB; + +EXPLAIN SELECT t1.pk +FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int +WHERE t1.col_int_key BETWEEN 5 AND 6 +AND t1.pk IS NULL OR t1.pk IN (5) +ORDER BY pk; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY,col_int_key PRIMARY 4 const 2 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col_int 1 Using index + +SELECT t1.pk +FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int +WHERE t1.col_int_key BETWEEN 5 AND 6 +AND t1.pk IS NULL OR t1.pk IN (5) +ORDER BY pk; +pk + +DROP TABLE t1,t2; +# End BUG#58456 === modified file 'mysql-test/t/join_outer_innodb.test' --- a/mysql-test/t/join_outer_innodb.test 2006-05-22 11:57:32 +0000 +++ b/mysql-test/t/join_outer_innodb.test 2010-12-08 09:58:55 +0000 @@ -24,3 +24,40 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON WHERE t1.name LIKE 'A%' OR FALSE; DROP TABLE t1,t2; + +--echo # +--echo # BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output +--echo # in opt_range.h +--echo # + +CREATE TABLE t1 ( + col_int INT, + col_int_key INT, + pk INT NOT NULL, + PRIMARY KEY (pk), + KEY col_int_key (col_int_key) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4); +INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5); + +CREATE TABLE t2 ( + pk INT PRIMARY KEY +) ENGINE=InnoDB; + +let $query= +SELECT t1.pk +FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int +WHERE t1.col_int_key BETWEEN 5 AND 6 + AND t1.pk IS NULL OR t1.pk IN (5) +ORDER BY pk; + +--echo +--eval EXPLAIN $query +--echo +--eval $query +--echo + +DROP TABLE t1,t2; + +--echo # End BUG#58456 === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-12-06 13:12:51 +0000 +++ b/sql/sql_select.cc 2010-12-08 09:58:55 +0000 @@ -9653,8 +9653,8 @@ static bool make_join_select(JOIN *join, /* Use quick key read if it's a constant and it's not used with key reading */ if (tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF - && tab->type != JT_FT && (tab->type != JT_REF || - (uint) tab->ref.key == tab->quick->index)) + && tab->type != JT_FT && tab->type != JT_CONST && + (tab->type != JT_REF || (uint)tab->ref.key == tab->quick->index)) { sel->quick=tab->quick; // Use value from get_quick_... sel->quick_keys.clear_all(); @@ -20050,7 +20050,7 @@ use_filesort: SYNOPSIS create_sort_index() thd Thread handler - tab Table to sort (in join structure) + join Join with table to sort order How table should be sorted filesort_limit Max number of rows that needs to be sorted select_limit Max number of rows in final output @@ -20060,8 +20060,8 @@ use_filesort: IMPLEMENTATION - - If there is an index that can be used, 'tab' is modified to use - this index. + - If there is an index that can be used, the first non-const join_tab in + 'join' is modified to use this index. - If no index, create with filesort() an index file that can be used to retrieve rows in order (should be done with 'read_record'). The sorted data is stored in tab->table and will be freed when calling --===============3850363521987788948== 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\ # 3e6qh47ya5tf7jgi # target_branch: file:///export/home/jl208045/mysql/mysql-trunk-\ # bugfixing/ # testament_sha1: 1898e9f3a00b769cf4a31e340e0aba7c9bf92469 # timestamp: 2010-12-08 10:58:58 +0100 # source_branch: file:///export/home/jl208045/mysql/mysql-5.5-bugteam/ # base_revision_id: marc.alff@stripped\ # qwfvqs0jen4n8813 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQoi4tsABNFfgHBwfff//3// /+C////0YAtN19udvbvOn02YVo+946Stu72ygGvI6Hve48jlDEppPJT0p5NqmmyTxT9FP1T9TTTa keapmk0NkQPUPU0NBpoJKITTTNNSn4aEp6A2lDyagaaANAANAABE0yCMplGZqGKeo0AbUGgAAAAA ADCSZEyap6YU9Tyj0jahk09Q000A0ADQAAaCKgiaaGmQTI1PU0DTQ0AAMRoAaANPUAkiTTQBMhoJ gIaqf6SemVHoTRtENqG0hoAeke55O2HaNY7Ze6LZHgOGdmg+zLDZnDpFDipW7SL0X47GG3upmseO sEFJDNdZ9egeU8tMdCZ0S4Q0hAbZ2prcHqruCNYZTRa15h89W0BaKtCFvadivR6BYaVMlCuKUleU 0CMtTCHBemgVajICfEmWZgXVLc8sGJLBoFCKX4+nsKMFKdfRI+QBWdWNgxBhTrcj/Hwr/A4bWb8C KvAcYOZHoW8uEZeZwn7HbKCQSCWkDeIxyu17HEKoLbUSchGQrUU6NWE9X8h3qT4MQ53GwP6JxtPR mAxuFknxSJYwIjLTI45jiAwUGaMUFC8dhYWIzgrH1CDwOXhQXnTDLfsHCpxJMxvEvIkuAJKqqGJp iiMQSFFKzMT114ESB7rDGh515O/bE3AExsMPKhUXmuDmZzWUZjGZW0wUhIStyTBG3JJXZeaF0A04 ExWLFbVEdClWTlKd9j4FdQr6DVfZWRm1grBZwIWJYAhBNbUmtKwILtprxjniLsqRinqq9VEw0DRn gxT4r5DkSZa0gkkthKOEodM3TwOBrv7M0BI/Qyrbu7HwxUQ/Wjv6HuPU3lBrN5iVn1GOYze7oO7k oAunhDq95IzGYvLi4iGYYgOOMSO0weWiOMpFn/KSpnEdt6zQ1iZwd4DyDDCJjtouWw571ZeuUZGZ UcOOmeZuParWsytSrzJKml1aYJE61JhZIpZG1UwnxpUOybfxoSDKvlCA5C0ZhG5tBAWSBO0xV0lC QzNSzJEGAFVETMZDjJUI3SEWDioVhgx576Y61yPs0zaUOMnHS9A6jmkYA9apMAe9h9hm4HDdlcMf HWlGeoS12Pug+azIjZOUK8boQyqpfp+mZLTbvE/I+ihxxZ39QdXe1sqqUeDQMkNeqvNDakmsNyLM sWBTFWwXCu/hxrp1GogVVyqv6CkovAToKjPEzyZjYXG5rNmzbrwyd63CZb+gLphsRpqfCukhcT69 DVLA8sN08eDxkUF9ZYUMRibBDOMclD29VRxJca8Nwz6gs0sb2WmueEJAPiIk6gdHLKziENfhkVma f5ygs5cVkvELttncpu7qtgwxERqVAWE8iZAyYyGVV85fERjirmJEHOVUjJMcR6VqBjXwR6LexGHA 07pkmJAupstZ25TMUtlS0tJkwxmXNyB45nxcnVJtKSgkac7sDYBEq7bnok1J2CcI18ZW4ZaVKExc S2ZU8YtbN60hfqJbM5c2RLRVPMiLQHKhGnQYiNi1ZG0YYmhUZ0J9JgoxLhy5KeIw4i06U3lPNbh6 M8DXO2y4nva04SO5bbzIzlVcjAFYqSn8rS4qzdGurIsXFvKBZTce0IFi5UTdgpnVaVDIwAslNw2S IoHEnAGQRWDZgW5ldybxZi0QqA3+T5Fjpx5BaSCE5nJawh6wHjh+Zr31vu81qax8JzBifJlOICWM N0izugwYQhDjCT9BgUQWn7v8agks+QfAHqYtBjLQgE2BMIhikuxlF961tYY38tzQmwOk02Xn9j7m ZyYKXLFUpXLAMygoAWmVSb4qlWBWEQBmZuYMDEDFpik+Zzq8oZhyLS2q2BsljdQFrAbzAJNIxWQU EQmwBinEHGEDnBsdpobWJFudZUfI3GSnU77IK9SEyqDB1pA/FbyCNADBoVlTUBeFlpNRMpqswMoF wxNcmcIwCoUgmCxuSvSGldDSrYXwzt5vphYBAIFTGYa3aYxVfzcjq6dMfER9oBAgIZL1ciI7L3wD 73/hEoMYNQ+ggYowpdIgR1BH4a3atYtP67gIdqHUNYjEOvR33KXW1HRQ4NqhW7NuZ782gdDmcPCf 45X5bdtepqlOGBakiRigGUX3Ge0COQxWVZIB12YEc8U3brkgcYJxJfe/Ph8TYdDjvKCcyXlajZym +6L1OI0dzGS6BUrXUj8S1yzx76mEeqr4UvEOLjMaWUUAXphGyhkiwUEmd4CzcbEFRxiAAMUhwDTb 6Tuhg24b2jA6LZ1DCg38UxRfv3G4rCpDFNEkaOxArlJJkiAtqsEVfSP3HPSpUlCxd2qEauoSKum8 n5xopaVdJoj4qDn6g36mHMzC0sTnyUkDIdZKKT3g8Unv1FFgAQmVr5MbIQbhN1JhHmrFiaqK3T1o wGqFhisOgDqGE4mz7rJxovHVPM0fLxdXJbOXDaE2zfJtjXCHNLmcolmp2t7f9viK3Pr1Mkfio8bx MmRlszdpuNXcD2pD8JlwOIed08okplFRmWVoihUC5MDyewz2RhsGFWgnS2pKOQHG37X5ATF52GMF BcTrYIneKqIlEDwGFQb12PzaVOUi2N+S4exMeQqpHIgE3VND86YOG2hQzDG4GDSnYTAplPBd68ED pn0228dfku7czMzHqIwIeAFJFMwfQwGmOmDMMmBwGniKai5BI1JhBqtl1YkVxfNY5cqBgZYrp3VS mRKe8kCnF3yR8kw5pYSYThb7HHtxoav+twlUxiwIkrxXrMoEoKyG2LgkuvTJLwg8GVhI8bhdThzH 0HKSPz+vGa+xizXhMGEQCO/domO6aiAKCgvMmPGXWdCOzIZh20TkYC1Grh83toUfNQLCR3IwCxXl Amj5RXy6gFCYFRE6mr3WpgatJMA9Qq5Y+N4QkQhVkkMPcUa+nngAcDBexW1BMEyg7bWsVOFPpKmk GCW5B5WLbFYAq4ZYbNa4gjUJEhbGeEwZKExKxSomY940RMkMUjCeaxZPbEdkRFbmoCYRN3iVnpJQ gaLqpd5mgFSTjMDJDJmRZyIKvP6ZMl6bAN7EY7cQ2qhxHKbH59J1+5kl4SAPs5pDoDOFbI0NCHZ4 MvhaWLlJYkDRySTC6kCnDBIvNi3VMN3Ax172jVYjdldOEGJd04MEgUxKA4VnPPb1UEbRjViNVJzJ pn3Y4ukpicEhBQthHHoW4Q+6CIW+U95ktaeI2yHJcbMOjA0VbEG5O4gjPdwqBfMa0/NUIdQ4KXOu nfMjji+TVFSC6sm0QcIotBNX5vBbuEPZplbmiY0pOF7ygOm49+QSJXOQaFF0xD6IkKWe/wUy6FE9 Pf4wWjGioDOjP7dVC5OEVrm/mYimRwYgOasYpimEj3jrVzbbeHQBDDHZOQg+U6rjAWtMEXIIYAWF osNV4pnEFKmphYBMJVKQUoVtXrHcSxZ1DFjbmSObJJ2A/YeX0q6VSYYRgPxXoJ7bq/9MdoXQCcrv VNc2Dp8d2kRGkoq7iHOJGAn4iLeTKZJbWE3rC2+8n4GU5FO/epT62AlQKCxWvTFXQNQ9z57IbAhs WwmH2KLXzbA3DDfvN15yKHDFQ261RMlYXKgzIXZlLyuaeNalxbVKKBSuFz1YsGh4hmdrcQrwYC7h xm1KKX1b8ygbBgGZMbGe8RSol6Ue4UiwoRgn2KXKqrzE/sP+LuSKcKEgFEXFtg== --===============3850363521987788948==--