bk commit into 5.0 tree (igor:1.2217) BUG#18165

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-07-27 19:40:02-07:00, igor@stripped +4 -0
  Preliminary patch for bug #18165 to be reviewed by SergeyP.

  mysql-test/r/range.result@stripped, 2006-07-27 19:39:58-07:00, igor@stripped +22 -0
    Added a test case for bug #18165.

  mysql-test/t/range.test@stripped, 2006-07-27 19:39:58-07:00, igor@stripped +23 -0
    Added a test case for bug #18165.

  sql/opt_range.cc@stripped, 2006-07-27 19:39:58-07:00, igor@stripped +111 -58
    Preliminary patch for bug #18165 to be reviewed by SergeyP.

  sql/sql_select.cc@stripped, 2006-07-27 19:39:59-07:00, igor@stripped +17 -1
    Preliminary patch for bug #18165 to be reviewed by SergeyP.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/mysql-5.0-opt

--- 1.217/sql/opt_range.cc	2006-07-27 19:40:08 -07:00
+++ 1.218/sql/opt_range.cc	2006-07-27 19:40:08 -07:00
@@ -3580,25 +3580,34 @@
     break;
 
   case Item_func::BETWEEN:
-    if (inv)
-    {
-      tree= get_ne_mm_tree(param, cond_func, field, cond_func->arguments()[1],
-                           cond_func->arguments()[2], cmp_type);
-    }
-    else
+  {
+    int i= (int ) value;
+    if (! i)
     {
-      tree= get_mm_parts(param, cond_func, field, Item_func::GE_FUNC,
-		         cond_func->arguments()[1],cmp_type);
-      if (tree)
+      if (inv)
+      {
+        tree= get_ne_mm_tree(param, cond_func, field, cond_func->arguments()[1],
+                             cond_func->arguments()[2], cmp_type);
+      }
+      else
       {
-        tree= tree_and(param, tree, get_mm_parts(param, cond_func, field,
-					         Item_func::LE_FUNC,
-					         cond_func->arguments()[2],
-                                                 cmp_type));
+        tree= get_mm_parts(param, cond_func, field, Item_func::GE_FUNC,
+		           cond_func->arguments()[1], cmp_type);
+        if (tree)
+        {
+          tree= tree_and(param, tree, get_mm_parts(param, cond_func, field,
+					           Item_func::LE_FUNC,
+					           cond_func->arguments()[2],
+                                                   cmp_type));
+        }
       }
     }
+    else
+       tree= get_mm_parts(param, cond_func, field,
+                          i == 1 ? Item_func::LT_FUNC : Item_func::GE_FUNC,
+		          cond_func->arguments()[0], cmp_type);
     break;
-
+  }
   case Item_func::IN_FUNC:
   {
     Item_func_in *func=(Item_func_in*) cond_func;
@@ -3783,6 +3792,75 @@
 
 }
 
+
+/*
+  Build a full set of SEL_TREEs for a simple predicate
+ 
+  SYNOPSIS
+    get_full_func_mm_tree()
+      param       PARAM from SQL_SELECT::test_quick_select
+      cond_func   item for the predicate
+      field_item  field in the predicate
+      value       constant in the predicate
+      inv         TRUE <> NOT cond_func is considered
+                  (makes sense only when cond_func is BETWEEN or IN) 
+
+  RETURN 
+    Pointer to the tree representing the built set of SEL_TREEs
+*/
+static SEL_TREE *get_full_func_mm_tree(PARAM *param, Item_func *cond_func, 
+                                       Item_field *field_item, Item *value, 
+                                       bool inv)
+{
+  SEL_TREE *tree= 0;
+  SEL_TREE *ftree= 0;
+  table_map ref_tables= 0;
+  table_map param_comp= ~(param->prev_tables | param->read_tables |
+		          param->current_table);
+  DBUG_ENTER("get_full_func_mm_tree");
+
+  /* 
+     If the where condition contains a predicate (ti.field op const),
+     then not only SELL_TREE for this predicate is built, but
+     the trees for the results of substitution of ti.field for
+     each tj.field belonging to the same multiple equality as ti.field
+     are built as well.
+     E.g. for WHERE t1.a=t2.a AND t2.a > 10 
+     a SEL_TREE for t2.a > 10 will be built for quick select from t2
+     and   
+     a SEL_TREE for t1.a > 10 will be built for quick select from t1.
+  */
+     
+  for (uint i= 0; i < cond_func->arg_count; i++)
+  {
+    Item *arg= cond_func->arguments()[i]->real_item();
+    if (arg != field_item)
+      ref_tables|= arg->used_tables();
+  }
+  Field *field= field_item->field;
+  Item_result cmp_type= field->cmp_type();
+  if (!((ref_tables | field->table->map) & param_comp))
+    ftree= get_func_mm_tree(param, cond_func, field, value, cmp_type, inv);
+  Item_equal *item_equal= field_item->item_equal;
+  if (item_equal)
+  {
+    Item_equal_iterator it(*item_equal);
+    Item_field *item;
+    while ((item= it++))
+    {
+      Field *f= item->field;
+      if (field->eq(f))
+        continue;
+      if (!((ref_tables | f->table->map) & param_comp))
+      {
+        tree= get_func_mm_tree(param, cond_func, f, value, cmp_type, inv);
+        ftree= !ftree ? tree : tree_and(param, ftree, tree);
+      }
+    }
+  }
+  DBUG_RETURN(ftree);
+}
+
 	/* make a select tree of all keys in condition */
 
 static SEL_TREE *get_mm_tree(PARAM *param,COND *cond)
@@ -3871,10 +3949,23 @@
 
   switch (cond_func->functype()) {
   case Item_func::BETWEEN:
-    if (cond_func->arguments()[0]->real_item()->type() != Item::FIELD_ITEM)
-      DBUG_RETURN(0);
-    field_item= (Item_field*) (cond_func->arguments()[0]->real_item());
-    value= NULL;
+    if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM)
+    {
+      field_item= (Item_field*) (cond_func->arguments()[0]->real_item());
+      ftree= get_full_func_mm_tree(param, cond_func, field_item, NULL, inv);
+    }
+    if (!inv)
+    { 
+      for (uint i= 1 ; i < cond_func->arg_count ; i++)
+      {
+        if (cond_func->arguments()[i]->real_item()->type() == Item::FIELD_ITEM)
+        {
+          field_item= (Item_field*) (cond_func->arguments()[i]->real_item());
+          tree= get_full_func_mm_tree(param, cond_func, field_item, (Item*) i, inv);
+          ftree= !ftree ? tree : tree_and(param, ftree, tree);
+        }
+      }
+    }
     break;
   case Item_func::IN_FUNC:
   {
@@ -3882,7 +3973,7 @@
     if (func->key_item()->real_item()->type() != Item::FIELD_ITEM)
       DBUG_RETURN(0);
     field_item= (Item_field*) (func->key_item()->real_item());
-    value= NULL;
+    ftree= get_full_func_mm_tree(param, cond_func, field_item, NULL, inv);
     break;
   }
   case Item_func::MULT_EQUAL_FUNC:
@@ -3921,47 +4012,9 @@
     }
     else
       DBUG_RETURN(0);
+    ftree= get_full_func_mm_tree(param, cond_func, field_item, value, inv);
   }
 
-  /* 
-     If the where condition contains a predicate (ti.field op const),
-     then not only SELL_TREE for this predicate is built, but
-     the trees for the results of substitution of ti.field for
-     each tj.field belonging to the same multiple equality as ti.field
-     are built as well.
-     E.g. for WHERE t1.a=t2.a AND t2.a > 10 
-     a SEL_TREE for t2.a > 10 will be built for quick select from t2
-     and   
-     a SEL_TREE for t1.a > 10 will be built for quick select from t1.
-  */
-     
-  for (uint i= 0; i < cond_func->arg_count; i++)
-  {
-    Item *arg= cond_func->arguments()[i]->real_item();
-    if (arg != field_item)
-      ref_tables|= arg->used_tables();
-  }
-  Field *field= field_item->field;
-  Item_result cmp_type= field->cmp_type();
-  if (!((ref_tables | field->table->map) & param_comp))
-    ftree= get_func_mm_tree(param, cond_func, field, value, cmp_type, inv);
-  Item_equal *item_equal= field_item->item_equal;
-  if (item_equal)
-  {
-    Item_equal_iterator it(*item_equal);
-    Item_field *item;
-    while ((item= it++))
-    {
-      Field *f= item->field;
-      if (field->eq(f))
-        continue;
-      if (!((ref_tables | f->table->map) & param_comp))
-      {
-        tree= get_func_mm_tree(param, cond_func, f, value, cmp_type, inv);
-        ftree= !ftree ? tree : tree_and(param, ftree, tree);
-      }
-    }
-  }
   DBUG_RETURN(ftree);
 }
 

--- 1.437/sql/sql_select.cc	2006-07-27 19:40:08 -07:00
+++ 1.438/sql/sql_select.cc	2006-07-27 19:40:08 -07:00
@@ -2757,11 +2757,12 @@
     break;
   case Item_func::OPTIMIZE_KEY:
   {
+    Item **values;
     // BETWEEN, IN, NE
     if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM
&&
 	!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
     {
-      Item **values= cond_func->arguments()+1;
+      values= cond_func->arguments()+1;
       if (cond_func->functype() == Item_func::NE_FUNC &&
         cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM
&&
 	     !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
@@ -2773,6 +2774,21 @@
                            0, values,
                            cond_func->argument_count()-1,
                            usable_tables);
+    }
+    if (cond_func->functype() == Item_func::BETWEEN)
+    {
+      values= cond_func->arguments();
+      for (uint i= 1 ; i < cond_func->argument_count() ; i++)
+      {
+        Item_field *field_item;
+        if (cond_func->arguments()[i]->real_item()->type() == Item::FIELD_ITEM
&&
+            !(cond_func->arguments()[i]->used_tables() & OUTER_REF_TABLE_BIT))
+        {
+          field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
+          add_key_equal_fields(key_fields, *and_level, cond_func,
+                               field_item, 0, values, 1, usable_tables);
+        }
+      }  
     }
     break;
   }

--- 1.46/mysql-test/r/range.result	2006-07-27 19:40:08 -07:00
+++ 1.47/mysql-test/r/range.result	2006-07-27 19:40:08 -07:00
@@ -838,3 +838,25 @@
 a	hex(filler)
 a	0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
 drop table t1,t2,t3;
+CREATE TABLE t1 (
+id int NOT NULL DEFAULT '0',
+b int NOT NULL DEFAULT '0',
+c int NOT NULL DEFAULT '0', 
+INDEX idx1(b,c), INDEX idx2(c));
+INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+INSERT INTO t1(b,c) VALUES (3,4), (3,4);
+SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+id	b	c
+0	3	4
+0	3	4
+SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+id	b	c
+0	3	4
+0	3	4
+EXPLAIN  SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	3	Using where
+EXPLAIN  SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx1,idx2	idx2	4	NULL	3	Using where
+DROP TABLE t1;

--- 1.39/mysql-test/t/range.test	2006-07-27 19:40:08 -07:00
+++ 1.40/mysql-test/t/range.test	2006-07-27 19:40:08 -07:00
@@ -656,3 +656,26 @@
 select a, hex(filler) from t1 where a not between 'b' and 'b'; 
 
 drop table t1,t2,t3;
+
+#
+# Bug #18165: range access for BETWEEN with a constant for the first argument 
+#
+
+CREATE TABLE t1 (
+  id int NOT NULL DEFAULT '0',
+  b int NOT NULL DEFAULT '0',
+  c int NOT NULL DEFAULT '0', 
+  INDEX idx1(b,c), INDEX idx2(c));
+
+INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+INSERT INTO t1(b,c) VALUES (3,4), (3,4);
+
+SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+
+EXPLAIN  SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+EXPLAIN  SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+
+DROP TABLE t1;
+