• bar@mysql.com's avatar
    Bug#9509: Optimizer: wrong result after AND with latin1_german2_ci comparisons · 093792c6
    bar@mysql.com authored
    Fixing part2 of this problem: AND didn't work well 
    with utf8_czech_ci and utf8_lithianian_ci in some cases.
    
    The problem was because when during condition optimization
    field was replaced with a constant, the constant's collation
    and collation derivation was used later for comparison instead
    of the field collation and derivation, which led to non-equal
    new condition in some cases.
    
    This patch copies collation and derivation from the field being removed
    to the new constant, which makes comparison work using the same collation
    with the one which would be used if no condition optimization were done.
    
    In other words:
    
      where s1 < 'K' and s1 = 'Y';
    
    was rewritten to:
    
      where 'Y' < 'K' and s1 = 'Y';
    
    Now it's rewritten to:
    
      where 'Y' collate collation_of_s1 < 'K' and s1 = 'Y'
    
      (using derivation of s1)
    
    
    Note, the first problem of this bug (with latin1_german2_ci) was fixed
    earlier in 5.0 tree, in a separate changeset.
    093792c6
sql_select.cc 289 KB