bk commit into 5.0 tree (evgen:1.1905) BUG#11853

[eugene]

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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
  1.1905 05/07/13 23:02:51 evgen@stripped +4 -0
  Fix bug #11853 Wrong delete behaviour when index is present
  
  Function get_func_mm_tree() was ingoring NOT predicate when building SEL_TREE
  for NOT LIKE predicate, which results in creation SEL_TREE like for LIKE
  predicate.
  
  This patch adds 2 function types - LIKE_LT_FUNC and LIKE_GT_FUNC.
  get_func_mm_tree() is modified to unroll [key NOT LIKE 'a%'] to 
  [key < 'aA' OR key > 'az']. When the get_mm_leaf() is called with 'type'
  parameter == LIKE_LT_FUNC/LIKE_GT_FUNC it generates 'aA'/'az' string keys and 
  builds [key < 'aA']/[key > 'az'] SEL_ARG.

  mysql-test/r/delete.result
    1.23 05/07/13 22:36:27 evgen@stripped +7 -0
    Test case for bug #11853 Wrong delete behaviour when index is present

  mysql-test/t/delete.test
    1.21 05/07/13 22:35:49 evgen@stripped +9 -0
    Test case for bug #11853 Wrong delete behaviour when index is presen

  sql/opt_range.cc
    1.182 05/07/13 22:35:23 evgen@stripped +66 -15
     Fix bug #11853 Wrong delete behaviour when index is present

  sql/item_func.h
    1.122 05/07/13 22:33:58 evgen@stripped +2 -1
    Fix bug #11853 Wrong delete behaviour when index is present

# 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:	evgen
# Host:	moonbone.local
# Root:	/work/mysql-5.0-bug-11853

--- 1.121/sql/item_func.h	2005-07-07 19:28:42 +04:00
+++ 1.122/sql/item_func.h	2005-07-13 22:33:58 +04:00
@@ -43,7 +43,8 @@
   bool const_item_cache;
   enum Functype { UNKNOWN_FUNC,EQ_FUNC,EQUAL_FUNC,NE_FUNC,LT_FUNC,LE_FUNC,
 		  GE_FUNC,GT_FUNC,FT_FUNC,
-		  LIKE_FUNC,NOTLIKE_FUNC,ISNULL_FUNC,ISNOTNULL_FUNC,
+                  LIKE_FUNC,NOTLIKE_FUNC,LIKE_LT_FUNC,LIKE_GT_FUNC,
+                  ISNULL_FUNC,ISNOTNULL_FUNC,
 		  COND_AND_FUNC, COND_OR_FUNC, COND_XOR_FUNC,
                   BETWEEN, IN_FUNC, MULT_EQUAL_FUNC,
 		  INTERVAL_FUNC, ISNOTNULLTEST_FUNC,

--- 1.181/sql/opt_range.cc	2005-07-04 04:44:31 +04:00
+++ 1.182/sql/opt_range.cc	2005-07-13 22:35:23 +04:00
@@ -3435,6 +3435,28 @@
     }
     break;
   }
+  case Item_func::LIKE_FUNC:
+    if (inv)
+    {
+    /*
+       Build SEL_TREE for NOT LIKE predicate.
+
+       key NOT LIKE 'foo%'  <==>  key < 'fooA' || key > 'fooz'
+       '<' here is a LIKE_LT_FUNC, '>' - LIKE_GT_FUNC.
+       They differ from ususal LT_FUNC/GT_FUNC, because they
+       need 'fooA'/'fooz' strings to be created by my_like_range()
+     */
+     tree= get_mm_parts(param, cond_func, field, Item_func::LIKE_LT_FUNC,
+                         value, cmp_type);
+      if (tree)
+      {
+        tree= tree_or(param, tree, get_mm_parts(param, cond_func, field,
+                                                Item_func::LIKE_GT_FUNC,
+                                                value, cmp_type));
+      }
+      break;
+    }
+    /* fall through */
   default: 
   {
     /* 
@@ -3695,7 +3717,7 @@
   bool optimize_range;
   SEL_ARG *tree= 0;
   MEM_ROOT *alloc= param->mem_root;
-  char *str;
+  char *str,*min_str,*max_str;
   DBUG_ENTER("get_mm_leaf");
 
   /*
@@ -3748,11 +3770,15 @@
 
   optimize_range= field->optimize_range(param->real_keynr[key_part->key],
                                         key_part->part);
-
-  if (type == Item_func::LIKE_FUNC)
+  /*
+     Build SEL_ARG for LIKE_FUNC, generate max/min keys for
+     LIKE_LT_FUNC/LIKE_GT_FUNC
+   */
+  if (type == Item_func::LIKE_FUNC || type == Item_func::LIKE_LT_FUNC ||
+      type == Item_func::LIKE_GT_FUNC)
   {
     bool like_error;
-    char buff1[MAX_FIELD_WIDTH],*min_str,*max_str;
+    char buff1[MAX_FIELD_WIDTH];
     String tmp(buff1,sizeof(buff1),value->collation.collation),*res;
     uint length,offset,min_length,max_length;
     uint field_length= field->pack_length()+maybe_null;
@@ -3824,8 +3850,14 @@
       int2store(min_str+maybe_null,min_length);
       int2store(max_str+maybe_null,max_length);
     }
-    tree= new (alloc) SEL_ARG(field, min_str, max_str);
-    goto end;
+    /*
+       SEL_ARG for LIKE_LT_FUNC/LIKE_GT_FUNC is build later.
+     */
+    if (type != Item_func::LIKE_LT_FUNC && type != Item_func::LIKE_GT_FUNC)
+    {
+      tree= new (alloc) SEL_ARG(field, min_str, max_str);
+      goto end;
+    }
   }
 
   if (!optimize_range &&
@@ -3848,15 +3880,32 @@
     tree= &null_element;                        // cmp with NULL is never TRUE
     goto end;
   }
-  str= (char*) alloc_root(alloc, key_part->store_length+1);
-  if (!str)
-    goto end;
-  if (maybe_null)
-    *str= (char) field->is_real_null();		// Set to 1 if null
-  field->get_key_image(str+maybe_null, key_part->length, key_part->image_type);
-  if (!(tree= new (alloc) SEL_ARG(field, str, str)))
-    goto end;                                   // out of memory
-
+  /*
+     Code for LIKE predicate generates min/max str like:
+     key LIKE 'foo%'  <==>  'fooA' < key < 'fooz'
+     here 'fooA' is min_str, 'fooz' - max_str
+     but for NOT LIKE we need
+     key NOT LIKE 'foo%'  <==>  key < 'fooA' || key > 'fooz'
+     so place min_str to max_value for LIKE_LT_FUNC,
+     max_str - min_value for LIKE_GT_FUNC.
+   */
+  if (type == Item_func::LIKE_LT_FUNC || type == Item_func::LIKE_GT_FUNC)
+  {
+    if (!(tree= new (alloc) SEL_ARG(field, max_str, min_str)))
+      goto end;                                   // out of memory
+  }
+  else
+  {
+    str= (char*) alloc_root(alloc, key_part->store_length+1);
+    if (!str)
+      goto end;
+    if (maybe_null)
+      *str= (char) field->is_real_null();		// Set to 1 if null
+    field->get_key_image(str+maybe_null, key_part->length,
+                         key_part->image_type);
+    if (!(tree= new (alloc) SEL_ARG(field, str, str)))
+      goto end;                                   // out of memory
+  }
   /*
     Check if we are comparing an UNSIGNED integer with a negative constant.
     In this case we know that:
@@ -3890,6 +3939,7 @@
 
   switch (type) {
   case Item_func::LT_FUNC:
+  case Item_func::LIKE_LT_FUNC:
     if (field_is_equal_to_item(field,value))
       tree->max_flag=NEAR_MAX;
     /* fall through */
@@ -3903,6 +3953,7 @@
     }
     break;
   case Item_func::GT_FUNC:
+  case Item_func::LIKE_GT_FUNC:
     if (field_is_equal_to_item(field,value))
       tree->min_flag=NEAR_MIN;
     /* fall through */

--- 1.20/mysql-test/t/delete.test	2005-05-30 21:48:35 +04:00
+++ 1.21/mysql-test/t/delete.test	2005-07-13 22:35:49 +04:00
@@ -169,3 +169,12 @@
 # This should be empty
 select * from t3;
 drop table t1,t2,t3;
+
+#
+# Bug #11853 Wrong delete behaviour when index is present
+#
+create table t1 (f1 varchar(3) primary key);
+insert into t1 values('bbb'),('aaa');
+delete from t1 where f1 not like 'a%';
+select * from t1;
+drop table t1;

--- 1.22/mysql-test/r/delete.result	2005-05-30 21:48:35 +04:00
+++ 1.23/mysql-test/r/delete.result	2005-07-13 22:36:27 +04:00
@@ -192,3 +192,10 @@
 select * from t3;
 a	b
 drop table t1,t2,t3;
+create table t1 (f1 varchar(3) primary key);
+insert into t1 values('bbb'),('aaa');
+delete from t1 where f1 not like 'a%';
+select * from t1;
+f1
+aaa
+drop table t1;