MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Alexey Kopytov Date:August 24 2010 3:51pm
Subject:bzr commit into mysql-5.1-bugteam branch (Alexey.Kopytov:3489)
Bug#54802
View as plain text  
#At file:///data/src/bzr/bugteam/mysql-5.1-bugteam/ based on revid:mattias.jonsson@stripped

 3489 Alexey Kopytov	2010-08-24
      Bug #54802: 'NOT BETWEEN' evaluation is incorrect
      
      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.

    modified:
      mysql-test/r/range.result
      mysql-test/t/range.test
      sql/opt_range.cc
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result	2010-08-11 12:13:59 +0000
+++ b/mysql-test/r/range.result	2010-08-24 15:51:32 +0000
@@ -1653,4 +1653,17 @@ a	b
 0	0
 1	1
 DROP TABLE t1;
+#
+# Bug #54802: 'NOT BETWEEN' evaluation is incorrect
+#
+CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key));
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	c_key	NULL	NULL	NULL	3	Using where
+SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
+c_key	c_notkey
+1	1
+3	3
+DROP TABLE t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2010-08-11 12:13:59 +0000
+++ b/mysql-test/t/range.test	2010-08-24 15:51:32 +0000
@@ -1313,4 +1313,16 @@ SELECT * FROM t1 FORCE INDEX (PRIMARY) 
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug #54802: 'NOT BETWEEN' evaluation is incorrect
+--echo #
+
+CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key));
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+
+EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
+SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
+
+DROP TABLE t1;
+
 --echo End of 5.1 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-07-15 13:01:44 +0000
+++ b/sql/opt_range.cc	2010-08-24 15:51:32 +0000
@@ -5526,7 +5526,11 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_P
         SEL_TREE *tmp= get_full_func_mm_tree(param, cond_func, 
                                     field_item, (Item*)(intptr)i, inv);
         if (inv)
+        {
           tree= !tree ? tmp : tree_or(param, tree, tmp);
+          if (tree == NULL)
+            break;
+        }
         else 
           tree= tree_and(param, tree, tmp);
       }


Attachment: [text/bzr-bundle] bzr/alexey.kopytov@sun.com-20100824155132-d3e83czeq702fz0c.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (Alexey.Kopytov:3489)Bug#54802Alexey Kopytov24 Aug