• Daniel Black's avatar
    MDEV-8553: Impossible where for a!=a, a<a, a>a · d2fa5f8c
    Daniel Black authored
    For a table column `a`, the above expressions logically
    equate to false in all cases.
    
    With this patch the optimizer knows about this and queries
    like:
    
    SELECT * FROM t1 WHERE a!=a
    
    no longer need to evaluate a!=a for every row.
    
    The same applies if the expression was `a<a`, or `a>a`
    
    An `EXPLAIN SELECT COOUNT(*) FROM t1 WHERE a<a` will show:
    
    id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
    1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
    
    Similarly `NOT (a!=a)` is always true.
    
    EXPLAIN SELECT COUNT(*) FROM t1 WHERE not (a!=a);
    id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
    1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
    d2fa5f8c
sql_select.cc 918 KB