Skip to content
  • Jorgen Loland's avatar
    3d7f08a8
    Bug#11752201: TABLE/INDEX SCAN WHEN QUOTING LARGE · 3d7f08a8
    Jorgen Loland authored
                  NUMBERS IN AN 'IN' OF A SELECT (...)
    
    When the range optimizer constructs the SEL_ARG graph from the
    predicates, get_mm_leaf() will convert constant values to the 
    appropriate type so a field vs constant comparison can be done.
    This is done by calling Item::save_in_field_no_warning().
    
    If this conversion is not OK for some reason, get_mm_leaf() will
    conclude that the predicate is either always false or always
    true. Consider
    
    Q1: EXPLAIN SELECT * FROM t1 WHERE a > value;
     and
    Q2: EXPLAIN SELECT * FROM t1 WHERE a > 'value';
    
    where value is a number higher than the maximum value for int
    column a. In Q1, get_mm_leaf() finds that the predicate is always
    false. In Q2 on the other hand, get_mm_leaf() intreprets the
    conversion error from string to int incorrectly and assumes that
    the condition is always true to be on the safe side. Due to this,
    the range access method is not used for this query.
    
    Before this fix, get_mm_leaf() would only come to the 
    conclusion that the range was impossible if both the constant
    and the field had an INT type. With this bugfix, quoted numbers
    will be treated the same way.
    3d7f08a8
    Bug#11752201: TABLE/INDEX SCAN WHEN QUOTING LARGE
    Jorgen Loland authored
                  NUMBERS IN AN 'IN' OF A SELECT (...)
    
    When the range optimizer constructs the SEL_ARG graph from the
    predicates, get_mm_leaf() will convert constant values to the 
    appropriate type so a field vs constant comparison can be done.
    This is done by calling Item::save_in_field_no_warning().
    
    If this conversion is not OK for some reason, get_mm_leaf() will
    conclude that the predicate is either always false or always
    true. Consider
    
    Q1: EXPLAIN SELECT * FROM t1 WHERE a > value;
     and
    Q2: EXPLAIN SELECT * FROM t1 WHERE a > 'value';
    
    where value is a number higher than the maximum value for int
    column a. In Q1, get_mm_leaf() finds that the predicate is always
    false. In Q2 on the other hand, get_mm_leaf() intreprets the
    conversion error from string to int incorrectly and assumes that
    the condition is always true to be on the safe side. Due to this,
    the range access method is not used for this query.
    
    Before this fix, get_mm_leaf() would only come to the 
    conclusion that the range was impossible if both the constant
    and the field had an INT type. With this bugfix, quoted numbers
    will be treated the same way.
Loading