Bug #53562 EXPLAIN statement should hint when index is not used due to type conversion
Submitted: 11 May 2010 12:25 Modified: 11 Dec 2010 17:50
Reporter: Andrii Nikitin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Jørgen Løland
Triage: Needs Triage: D5 (Feature request)

[11 May 2010 12:25] Andrii Nikitin
Description:
Following chapter of manual describes how MySQL will compare values:

http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

On practice it may be difficult to understand which exactly conversion was chosen by optimizer, so it would be nice to have a hint from server which will explain what exact conversion was chosen.

(see bug #32308):
mysql> explain select id from objects_resources where url=1\G
*************************** 1. row ***************************
  select_type: SIMPLE
         type: ALL
         rows: 3
...
        Extra: Using where; Comparing as float
1 row in set (0.00 sec)

How to repeat:
fr

Suggested fix:
fr
[11 May 2010 12:35] Andrii Nikitin
In fact the conversion is performed at runtime, so it is impossible to choose type for comparison when EXPLAIN executes. 

Instead EXPLAIN should just produce comment in 'Extra' column that type conversion needed - this will help to understand why index is not used.
[3 Aug 2010 12:55] Evgeny Potemkin
See also bug#25574.
[31 Aug 2010 7:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117179

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.
[31 Aug 2010 7:13] Jørgen Løland
Discussed with PeterG, Evgeny, Roy:

The hint that an index is not used for lookup due to type conversion or collation mismatch should be given as a warning to EXPLAIN EXTENDED.
[1 Sep 2010 14:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117351

3267 Jorgen Loland	2010-09-01
      BUG#53562: EXPLAIN statement should hint when index is not used 
                 due to type conversion
      
      An index may not be used for ref or range access 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, 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 ref or range
      access, 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/opt_range.cc
        Make EXPLAIN EXTENDED issue a warning if an index cannot be used
        for range access due to type conversion of collation mismatch.
     @ sql/share/errmsg-utf8.txt
        Added ER_WARN_INDEX_NOT_APPLICABLE for use when an index cannot
        be used for ref or range access due to type conversion or
        collation mismatch.
     @ sql/sql_select.cc
        Make EXPLAIN EXTENDED issue a warning if an index cannot be used
        for ref access due to type conversion of collation mismatch.
[9 Sep 2010 11:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117835

3276 Jorgen Loland	2010-09-09
      BUG#53562: EXPLAIN statement should hint when index is not used 
                 due to type conversion
      
      An index may not be used for ref or range access 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, 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 ref or range
      access, 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/opt_range.cc
        Make EXPLAIN EXTENDED issue a warning if an index cannot be used
        for range access due to type conversion of collation mismatch.
     @ sql/share/errmsg-utf8.txt
        Added ER_WARN_INDEX_NOT_APPLICABLE for use when an index cannot
        be used for ref or range access due to type conversion or
        collation mismatch.
     @ sql/sql_select.cc
        Make EXPLAIN EXTENDED issue a warning if an index cannot be used
        for ref access due to type conversion of collation mismatch.
[9 Sep 2010 11:10] Jørgen Løland
Pushed to next-mr-bugfixing
[2 Oct 2010 18:14] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[13 Nov 2010 16:04] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[11 Dec 2010 17:50] Paul Dubois
Bug does not appear in any released 5.6.x version. No changelog entry needed.

(If this gets pushed to 5.5.x, please set back to Documenting)