MySQL Bugs: #53562: EXPLAIN statement should hint when index is not used due to type conversion

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	CPU Architecture:	Any

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

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.

See also bug#25574.

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.

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.

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.

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.

Pushed to next-mr-bugfixing

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)

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)

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)