From: Jorgen Loland Date: August 31 2010 7:08am Subject: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3264) Bug#53562 List-Archive: http://lists.mysql.com/commits/117179 X-Bug: 53562 Message-Id: <20100831070839.EC65F351@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1210834176646418933==" --===============1210834176646418933== 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-bugfixing-53562/ based on revid:jorgen.loland@stripped 3264 Jorgen Loland 2010-08-31 BUG#53562: EXPLAIN statement should hint when index is not used due to type conversion An index may not be used for lookup if a) a type conversion is needed to compare an indexed field to a value, or b) there is a collation mismatch between an indexed field and the value compared to Before, the index would not be used for lookup, but no hint was given that this was the case. With this patch, EXPLAIN EXTENDED will issue a warning for the cases above. Note that even if an index cannot be used for lookup, the index may still be scanned. @ mysql-test/r/explain.result Added test for BUG#53562 @ mysql-test/t/explain.test Added test for BUG#53562 @ sql/share/errmsg-utf8.txt Added ER_WARN_INDEX_NO_LOOKUP_CAST and ER_WARN_INDEX_NO_LOOKUP_COLLATION for use when an index cannot be used for lookup due to type conversion or collation mismatch. @ sql/sql_select.cc Make EXPLAIN EXTENDED issue a warning if an index cannot be used for lookup due to type conversion of collation mismatch. modified: mysql-test/r/explain.result mysql-test/t/explain.test sql/share/errmsg-utf8.txt sql/sql_select.cc === modified file 'mysql-test/r/explain.result' --- a/mysql-test/r/explain.result 2010-08-26 12:02:59 +0000 +++ b/mysql-test/r/explain.result 2010-08-31 07:08:36 +0000 @@ -324,4 +324,43 @@ id select_type table type possible_keys 25 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL # End BUG#30597 +# +# BUG#53562: EXPLAIN statement should hint when +# index is not used due to type conversion +# +CREATE TABLE t1 (url char(1) PRIMARY KEY); +INSERT INTO t1 VALUES ('1'),('2'); + +# Normally, primary key lookup is done +EXPLAIN EXTENDED SELECT * FROM t1 WHERE url='1'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY PRIMARY 1 const 1 100.00 Using index +Warnings: +Note 1003 select '1' AS `url` from `test`.`t1` where 1 + +# Test that index can't be used for lookup due to type conversion +# (comparing char and int) +SELECT * FROM t1 WHERE url=1; +url +1 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE url=1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index PRIMARY PRIMARY 1 NULL 2 100.00 Using where; Using index +Warnings: +Warning 1708 Can't use index 'PRIMARY' for lookups due to type conversion on field 'url' +Note 1003 select `test`.`t1`.`url` AS `url` from `test`.`t1` where (`test`.`t1`.`url` = 1) + +# Test that index can't be used for lookup due to collation mismatch +SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci; +url +1 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index PRIMARY PRIMARY 1 NULL 2 100.00 Using where; Using index +Warnings: +Warning 1709 Can't use index 'PRIMARY' for lookups due to collation mismatch on field 'url' +Note 1003 select `test`.`t1`.`url` AS `url` from `test`.`t1` where (`test`.`t1`.`url` = (('1' collate latin1_german2_ci))) + +DROP TABLE t1; +# End BUG#53562 End of 6.0 tests. === modified file 'mysql-test/t/explain.test' --- a/mysql-test/t/explain.test 2010-08-26 12:02:59 +0000 +++ b/mysql-test/t/explain.test 2010-08-31 07:08:36 +0000 @@ -279,4 +279,30 @@ EXPLAIN --echo # End BUG#30597 +--echo # +--echo # BUG#53562: EXPLAIN statement should hint when +--echo # index is not used due to type conversion +--echo # + +CREATE TABLE t1 (url char(1) PRIMARY KEY); +INSERT INTO t1 VALUES ('1'),('2'); + +--echo +--echo # Normally, primary key lookup is done +EXPLAIN EXTENDED SELECT * FROM t1 WHERE url='1'; +--echo +--echo # Test that index can't be used for lookup due to type conversion +--echo # (comparing char and int) +SELECT * FROM t1 WHERE url=1; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE url=1; +--echo +--echo # Test that index can't be used for lookup due to collation mismatch +SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci; + +--echo +DROP TABLE t1; + +--echo # End BUG#53562 + --echo End of 6.0 tests. === modified file 'sql/share/errmsg-utf8.txt' --- a/sql/share/errmsg-utf8.txt 2010-07-29 14:15:38 +0000 +++ b/sql/share/errmsg-utf8.txt 2010-08-31 07:08:36 +0000 @@ -6387,3 +6387,7 @@ ER_TABLES_DIFFERENT_METADATA ER_ROW_DOES_NOT_MATCH_PARTITION eng "Found row that does not match the partition" swe "Hittade rad som inte passar i partitionen" +ER_WARN_INDEX_NO_LOOKUP_CAST + eng "Can't use index '%-.64s' for lookups due to type conversion on field '%-.64s'" +ER_WARN_INDEX_NO_LOOKUP_COLLATION + eng "Can't use index '%-.64s' for lookups due to collation mismatch on field '%-.64s'" === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-08-26 13:54:59 +0000 +++ b/sql/sql_select.cc 2010-08-31 07:08:36 +0000 @@ -5159,6 +5159,41 @@ static uint get_semi_join_select_list_in } /** + If EXPLAIN EXTENDED, add a warning for each index that cannot be + used for lookup due to either type conversion or different + collations on the field used for comparison + + Example type conversion (char compared to int): + + CREATE TABLE t1 (url char(1) PRIMARY KEY); + SELECT * FROM t1 WHERE url=1; + + Example different collations (danish vs german2): + + CREATE TABLE t1 (url char(1) PRIMARY KEY) collate latin1_danish_ci; + SELECT * FROM t1 WHERE url='1' collate latin1_german2_ci; + + @param thd Thread for the connection that submitted the query + @param field Field used in comparision + @param cant_use_indexes Indexes that cannot be used for lookup + @param errorno The error message number to use in the warning + */ +static void +warn_index_not_applicable(THD *thd, const Field *field, + const key_map cant_use_index, const uint errorno) +{ + if (thd->lex->describe & DESCRIBE_EXTENDED) + for (uint j=0 ; j < field->table->s->keys ; j++) + if (cant_use_index.is_set(j)) + push_warning_printf(thd, + MYSQL_ERROR::WARN_LEVEL_WARN, + errorno, + ER(errorno), + field->table->key_info[j].name, + field->field_name); +} + +/** Add a possible key to array of possible keys if it's usable as a key @param key_fields Pointer to add key, if usable @@ -5183,6 +5218,7 @@ add_key_field(KEY_FIELD **key_fields,uin Field *field, bool eq_func, Item **value, uint num_values, table_map usable_tables, SARGABLE_PARAM **sargables) { + DBUG_PRINT("info",("add_key_field for field %s",field->field_name)); uint exists_optimize= 0; if (!(field->flags & PART_KEY_FLAG)) { @@ -5284,7 +5320,11 @@ add_key_field(KEY_FIELD **key_fields,uin if ((*value)->result_type() != STRING_RESULT) { if (field->cmp_type() != (*value)->result_type()) + { + warn_index_not_applicable(stat->join->thd, field, possible_keys, + ER_WARN_INDEX_NO_LOOKUP_CAST); return; + } } else { @@ -5294,7 +5334,11 @@ add_key_field(KEY_FIELD **key_fields,uin */ if (field->cmp_type() == STRING_RESULT && ((Field_str*)field)->charset() != cond->compare_collation()) + { + warn_index_not_applicable(stat->join->thd, field, possible_keys, + ER_WARN_INDEX_NO_LOOKUP_COLLATION); return; + } } } } --===============1210834176646418933== 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\ # fndg8l3ks8vta0kd # target_branch: file:///export/home/jl208045/mysql/mysql-next-mr-\ # bugfixing-53562/ # testament_sha1: 98be8de846d18b4f390eb8402cf7f5a586afaffb # timestamp: 2010-08-31 09:08:39 +0200 # base_revision_id: jorgen.loland@stripped\ # y0wk5s0v3wkjlked # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWThB36IABqp/gF3+AUBb//// f///6v////pgDmXeR723zdJ3en3sb1Pcu0+dOaat6binq77m2MNsXtTPo928mocMkU9TaCak9pT2 p6aTKfpJs0AJtTTTUxAAD1BoAekB6gklT1HiE2SGSaJvVPUA9Ro0A0AAAAAABoaCSmgSeqnjT1Cj 9TUeptQPSaBoNGgNADQAAANDQJEmkJqp+aIxAFPNU9I9NTah5IAyA9NQ8UBoGj1PSaAIpFNoI1PQ IwTSbU1NqT3qYkzU0nqeTT1EAeUAAYgGCKQgEyaJhBoajQn6jUxT1H6pptQyABkAHqB6g2kQgbEA O3yma4GH3DnGHD7S4Gn9h5ZfnKh1lAh/hqH7n4nGYDzlgMVFARqYIJFjcy7tnbWdf0cnMLtLNCIU Rhgd64vlcV9OZYtYoHbw24Mwxnx7dqKcppsq8nqr1ad3VOxr65gx036xWdtPzLzmZFGXxtckXO57 mrHhfpIikHF8Bd2l5iOLtqAfJe8MhLw6CqlRpjXLBlZVjt8KGZiBHwveoNxKUZc9EV7MgWwOwg2W dkTArIRgCIE0+EZVBO5rEGOCDykmrNjMNmdUBxAst/1cIdocwMbbbE2seT+QOHefBouw6Xyulsat V9RkwRLIVL4CbGyLWVtiaRp7r9fuN4GcdDaNwquidG7xr2EEyZsgrtUftBwpGsENAOcbQfAiGuZh Ll7qwF42YL8cYnZiikDFV5Q5BnQgZncRC0ROSq+MgaBVmBiHhZrZce9N867jKMFDzmUY9DbIHNtx ZmvLosvCNDysEydXJFoM3E3ejpVTFJVretgYHpyMAQRc+RUWztKb3f4uwv0kWrtQmQNkWr7Azsrx m215QRPdMuuClArmkXOxvl4w9x7gZGJaBlhfaGJ0zgUwMRG/mukxA22gt0ojfgKVwBUgKVhWQpEB UjlDq1mo4FpKZRJFwCji0qVbe1vjeTToXRo7IFJxit/PzoLNkOugNoUcjYxjG22PypaAg4zOli5m 2SFDYIvvQxjJKHCAzVy1oUPHqDblLzOVYyrBfuzbFdDRYpgXa9pzmuQtlEj6nI8r5azliAPQhihq TNgU/aGGO8y+gagzikvK8EukHYBhOAyEDMSBfyQwiJfq9nGuafUhbC1hYVtgqcmJY+kE1DDqRuhv k6JiPndOiTafYC8yzD2KZJnYoY801D/cCa07ujxhZZmus5agnpoxLM2IPydk2fsZvEfBqGaDMIhQ kyYGAzHwIoUGgYG44dOJU91GQNmFkhCQN8SbStwXC1sJUK6Wkys9YPtOLsfpedjNzVX4BGIYnKTo A02k/FkHiWmnbSI7gapqaZSSAYh70ZUVxkmyLGiLeKBdK+KwtvAwJPHToVThPU7AGWxpqgO3KuuH FlA5txZIqw21bDn5eLO++/yY0piXPQdkHd2EBtDyLNbyontDXQ38OupC7dw6t/OYc0XdWztM9qHG FDmOizfPCBrL/h1dZkRTqppNR2M6jiaWVV8FIUCpVyJbBgMPFZaXpWikSyvv0c7ZNae8tVqzFpbl VqxxcSVxyDIkscWsmOrFNa5jRok9WRAsBQtTpvninE4ChIuGzRoHbILf8quF6rrljT2qL8lrEBX2 FIRjV1YkDPQiGORPgTFIzZnqzEYm0vc6+iRnWEYvF4ixQebys3sqiF11VRW4L7TplioJnJ1xkzsT hPuYD8oc8NmfFd0vNfMCg4bR1VbDWyJRhHnqNxN21HCCR2WcXg9PJi4txl1S1g66GbIk0PGWhkwH Zxk4EPPoCZgYFYaJbKEjzSMSnnvNu4tKox3zksJf2uFOHDK00ZNTpc+eoxylzSckwYoarjF80s+7 P5WJnHl1rhw8Mtd+rHkeKD9mOMzSHXKh6cssI53rXRA1U3EzqtKyqjbluuciFaoBvCymFVxcXTs1 my4oKqanFRE0VYrcJDWBXgqXU6GwU2Vi2HEFkjGsoMBMzFhoiaSlFDmHGdzlQRuGkxhKLYDQKiJ0 TOrA1sBeLRQ2JyZZicbsDCjq0rZRCKxpmBVCLXCKTY4vSuue/iyREKHBy0ihSKDoZTZDmQ1wnURB Dog7QkBLlXthN/4gxcfVANahjDq9o9gL0H9gdAKxfwvIFi4VpMGNsG33dorhV+a6z/hVIOyQWk8Z 56CLPIIbFaYLODFyAGaAWQTQDsrE9+gHTUX6NMiPgX4UGqIBsig/UHrmGthH33AZdAOtHJ3A6BxK XFjH9d8g/cOo/iDDDlHYDjwKMhvP/VW1A7ldeSc0IhjCgkxuGE2FFtNQai0eDUDY6hk8BqPrB7HA cA7zHmHfndAOSbkdoHwB4GdbQGAIY0Ghtg29Yyr8LFkxgsRFjuFUNcR2Qqkhi9Gk3QKjtGqsgg07 V6Zg6kigLpBgOf5+/eOVWK7Qs4vzreL2l7i+drn6GLwI8NgQiE59mAUBB0r6KfRd6klMJQpOS3M1 gPzLreBBIIf1sLUDMR4pncVGKm52yxTTi9jfuX0gpDwkuq/IoERSSm5xOHV4e4+BWG5cuQwTKHcF i14skHrkoc5WhTyu1/PAZ0ghA+D7SEhoSKyVSGBIaQXLRUCkUAOnqUKTng6sz3bT7N76+o71uO/N 3++tJ1m0VLQkZ9sL4Q7IybgKk+mX2+79NGzqvQLcfNAYEd+envKSd9r7tAXwPqhr4HqxhjCXSlRg Q6sbDi7ofPBniZQFXFHyrKq5koZtYBpBOeTcQ/KR9UI660cCwBek1kXWRqZrzTeYqKdhhRiLYUEk QoiUEFiVyhgIg6gpQOY4aJBheSJYEOA8bnC4dtKv2HQWoTULrTbz3GfDDvfJuOJ5dlpHztps7SM0 xof8cwATeSHAtpF5zX5E41aGwI3WAlNUC13RsxI3OvykuJKLQbZEBxlARVevondO0WyYlCFWFbSf Q5Na9yQOYKbmWmy+6b0/XTSeRJlRuBlCeu+MkMuQDBhMi811nV5kaKa6mz0xoglClaLFRikprNgV ESYYWLd4XUzPF3Hm7BeisPQQpf4lWiCKxJzO13G+K+yUTUMTmOlhKMDxr08MJjDwOOWhfD9pHr0v f9GYPmv7sIz3bBzPoCFZRtSzblNbQHfAGbe6JSZb7kOlAybXB98HGniE+I8xyWEziTKJOMAWNYUG 1S0Sl8MNwmEng47BOfUrqQfTthWAagJ9IICgfcfRmbAeIY1qWwpwgu2v5Wb6GngXAHnPJG/sBpg+ JOUAQCYdA5Sx4a7nQ/JTxj3UkEq4c2RSkKQsFEOaEO+B9RVt8ApDEFDwNIUKI2tHC3Q6xa64O55p jzFyVdzPlseOvTAMbtS++GIQu0f6h51v0tECnLJwJlbeaRJrtXgVAH/SHMkEZ/RonpchDyp6ZBz9 JDc07+vpvuAzLCRCwZmxbT15DHqXvWVD3dOIqfL3O7i4B0XaY90bmm5WOQb4OoWyEAVLWqT8oTEh vHhKGjSAZ2mzOa4va5s5s4Pl9jJeTofT7AfjQO3N8ppgIg7R3hufaDWtOip77AbBhcomMErXQsIY Nrah2hAm+D0NuUIkkudQgGSYlOV1Udph3th58wBfQ7VDOgTSjoNFDz8ZnmuaggEvQy2kjnWNGWNq 62m1LSS7OOnHhdJbUkaOHjrmmLW0OhwWJJjwhOAi9P44KCVHNAGRdKQUEUaXtqWaXOg15wqf0ENi woQq/PnBtPSVyy0W8aSjGIxFWKLGrQgbWNbU2HEmi+9saYmlrKTEDZsceJoD2c/J3hhCE1lCk5O2 R17ct/p4GRA/JwUN4BWwC58y2hAsQvkPLVMhAsQLCvJiIhAlD9ahrWX/slD9RNrDlWwm1GoNXLe+ jle3vLzdsCSg0cOdHc1bLnYFbyoECYGYGRsiCteDjWqIkYQ2FQaqrklE04Yo3tTox3C+bGV+GsMi IgEiDykSCeX2qnBrEawz4tTOKSJGMMjWyJKehOb05JVaCRdjmXEQbFHusLFh2diVozD8WvrcMLE1 N6pbEXEHEOtpoVMtA6BeasCmjFq26wGjjyFcRrnGAZjXe7V+4DFZqiyOeFDU7Eh34p3H44chXRO6 idWNvT+KdFsKQtYb9Y/FReDHtDCMZSlKUseDInNpFDq2jxS0uaDYNgHiNdd6kY0nnKI8E+rJde+D cmCV+QNSVHUYRG3CpQG6hVSuaeXHKlSpVpt1pejTYfCdrqBPevLDJwC3ACMGbJawbn4ia2gdBcyW PXf2V6p1tJuR8YdZMzWdAJ6dycLgqE4fm1EpoVQ+umaOrNvJi6oKq1Cpb2rQysgbjr/Bv5tKdD1u INrtyxfJI3yFj5Ork+t9mHTvCl6ba2XpRiA2fGWB5C4HpWB0sAHZd4+1iL3Gxt63C07s+/dYr5nZ xCpd7aavaDc9Ds+koGqcs8IepjQ8z4n5HFTG4COauTpfYSCXc4piVhAkVPqdrW+do8QgxFxMte1q Hr1tWp3PIIUHrTrOdozb8E5K/H45pjVO4Wwg/4u5IpwoSBwg79EA --===============1210834176646418933==--