• Alexey Kopytov's avatar
    Bug #54802: 'NOT BETWEEN' evaluation is incorrect · e7b26882
    Alexey Kopytov authored
    Queries involving predicates of the form "const NOT BETWEEN
    not_indexed_column AND indexed_column" could return wrong data
    due to incorrect handling by the range optimizer.
    
    For "c NOT BETWEEN f1 AND f2" predicates, get_mm_tree()
    produces a disjunction of the SEL_ARG trees for "f1 > c" and
    "f2 < c". If one of the trees is empty (i.e. one of the
    arguments is not sargable) the resulting tree should be empty
    as well, since the whole expression in this case is not
    sargable.
    
    The above logic is implemented in get_mm_tree() as follows. The
    initial state of the resulting tree is NULL (aka empty). We
    then iterate through arguments and compute the corresponding
    SEL_ARG tree (either "f1 > c" or "f2 < c"). If the resulting
    tree is NULL, it is simply replaced by the generated
    tree. Otherwise it is replaced by a disjunction of itself and
    the generated tree. The obvious flaw in this implementation is
    that if the first argument is not sargable and thus produces a
    NULL tree, the resulting tree will simply be replaced by the
    tree for the second argument. As a result, "c NOT BETWEEN f1
    AND f2" will end up as just "f2 < c".
    
    Fixed by adding a check so that when the first argument
    produces an empty tree for the NOT BETWEEN case, the loop is
    aborted with an empty tree as a result. The whole idea of using
    a loop for 2 arguments does not make much sense, but it was
    probably used to avoid code duplication for several BETWEEN
    variants.
    e7b26882
range.test 48 KB