Merge rkalimullin@bk-internal.mysql.com:/home/bk/mysql-4.1

into gw.mysql.r18.ru:/usr/home/ram/work/4.1.b2419


sql/opt_range.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged

5 files changed, 69 insertions, 40 deletions

diff --git a/sql/item.h b/sql/item.h
index 7b5c506079d..a5648c5889b 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -204,6 +204,7 @@ public:
   virtual Item *get_tmp_table_item(THD *thd) { return copy_or_same(thd); }
 
   CHARSET_INFO *default_charset() const;
+  virtual CHARSET_INFO *compare_collation() { return NULL; }
 
   virtual bool walk(Item_processor processor, byte *arg)
   {
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 3ad3b928c01..7079fcf193d 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -191,6 +191,7 @@ public:
   bool have_rev_func() const { return rev_functype() != UNKNOWN_FUNC; }
   void print(String *str) { Item_func::print_op(str); }
   bool is_null() { return test(args[0]->is_null() || args[1]->is_null()); }
+  CHARSET_INFO *compare_collation() { return cmp.cmp_collation.collation; }
 
   friend class  Arg_comparator;
 };
@@ -340,6 +341,7 @@ public:
   const char *func_name() const { return "between"; }
   void fix_length_and_dec();
   void print(String *str);
+  CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
 };
 
 
@@ -479,6 +481,7 @@ public:
   const char *func_name() const { return "case"; }
   void print(String *str);
   Item *find_item(String *str);
+  CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
 };
 
 
@@ -726,6 +729,7 @@ class Item_func_in :public Item_int_func
   enum Functype functype() const { return IN_FUNC; }
   const char *func_name() const { return " IN "; }
   bool nulls_in_row();
+  CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
 };
 
 /* Functions used by where clause */
@@ -766,6 +770,7 @@ public:
   table_map not_null_tables() const { return 0; }
   optimize_type select_optimize() const { return OPTIMIZE_NULL; }
   Item *neg_transformer();
+  CHARSET_INFO *compare_collation() { return args[0]->collation.collation; }
 };
 
 /* Functions used by HAVING for rewriting IN subquery */
@@ -800,6 +805,7 @@ public:
   table_map not_null_tables() const { return 0; }
   Item *neg_transformer();
   void print(String *str);
+  CHARSET_INFO *compare_collation() { return args[0]->collation.collation; }
 };
 
 
@@ -854,6 +860,7 @@ public:
   bool fix_fields(THD *thd, struct st_table_list *tlist, Item **ref);
   const char *func_name() const { return "regexp"; }
   void print(String *str) { print_op(str); }
+  CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
 };
 
 #else
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 2ebc60ca711..d39636fdc6c 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -291,10 +291,11 @@ typedef struct st_qsel_param {
   bool quick;				// Don't calulate possible keys
 } PARAM;
 
-static SEL_TREE * get_mm_parts(PARAM *param,Field *field,
+static SEL_TREE * get_mm_parts(PARAM *param,COND *cond_func,Field *field,
 			       Item_func::Functype type,Item *value,
 			       Item_result cmp_type);
-static SEL_ARG *get_mm_leaf(PARAM *param,Field *field,KEY_PART *key_part,
+static SEL_ARG *get_mm_leaf(PARAM *param,COND *cond_func,Field *field,
+			    KEY_PART *key_part,
 			    Item_func::Functype type,Item *value);
 static SEL_TREE *get_mm_tree(PARAM *param,COND *cond);
 static ha_rows check_quick_select(PARAM *param,uint index,SEL_ARG *key_tree);
@@ -834,10 +835,10 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond)
       Field *field=((Item_field*) (cond_func->arguments()[0]))->field;
       Item_result cmp_type=field->cmp_type();
       DBUG_RETURN(tree_and(param,
-			   get_mm_parts(param, field,
+			   get_mm_parts(param, cond_func, field,
 					Item_func::GE_FUNC,
 					cond_func->arguments()[1], cmp_type),
-			   get_mm_parts(param, field,
+			   get_mm_parts(param, cond_func, field,
 					Item_func::LE_FUNC,
 					cond_func->arguments()[2], cmp_type)));
     }
@@ -850,13 +851,14 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond)
     {
       Field *field=((Item_field*) (func->key_item()))->field;
       Item_result cmp_type=field->cmp_type();
-      tree= get_mm_parts(param,field,Item_func::EQ_FUNC,
+      tree= get_mm_parts(param,cond_func,field,Item_func::EQ_FUNC,
 			 func->arguments()[1],cmp_type);
       if (!tree)
 	DBUG_RETURN(tree);			// Not key field
       for (uint i=2 ; i < func->argument_count(); i++)
       {
-	SEL_TREE *new_tree=get_mm_parts(param,field,Item_func::EQ_FUNC,
+	SEL_TREE *new_tree=get_mm_parts(param,cond_func,field,
+					Item_func::EQ_FUNC,
 					func->arguments()[i],cmp_type);
 	tree=tree_or(param,tree,new_tree);
       }
@@ -875,7 +877,7 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond)
   /* btw, ft_func's arguments()[0] isn't FIELD_ITEM.  SerG*/
   if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM)
   {
-    tree= get_mm_parts(param,
+    tree= get_mm_parts(param, cond_func,
 		       ((Item_field*) (cond_func->arguments()[0]))->field,
 		       cond_func->functype(),
 		       cond_func->arg_count > 1 ? cond_func->arguments()[1] :
@@ -888,7 +890,7 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond)
       cond_func->have_rev_func() &&
       cond_func->arguments()[1]->type() == Item::FIELD_ITEM)
   {
-    DBUG_RETURN(get_mm_parts(param,
+    DBUG_RETURN(get_mm_parts(param, cond_func,
 			     ((Item_field*)
 			      (cond_func->arguments()[1]))->field,
 			     ((Item_bool_func2*) cond_func)->rev_functype(),
@@ -902,7 +904,8 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond)
 
 
 static SEL_TREE *
-get_mm_parts(PARAM *param, Field *field, Item_func::Functype type, 
+get_mm_parts(PARAM *param, COND *cond_func, Field *field,
+	     Item_func::Functype type, 
 	     Item *value, Item_result cmp_type)
 {
   bool ne_func= FALSE;
@@ -931,7 +934,8 @@ get_mm_parts(PARAM *param, Field *field, Item_func::Functype type,
 	DBUG_RETURN(0);				// OOM
       if (!value || !(value->used_tables() & ~param->read_tables))
       {
-	sel_arg=get_mm_leaf(param,key_part->field,key_part,type,value);
+	sel_arg=get_mm_leaf(param,cond_func,
+			    key_part->field,key_part,type,value);
 	if (!sel_arg)
 	  continue;
 	if (sel_arg->type == SEL_ARG::IMPOSSIBLE)
@@ -953,7 +957,8 @@ get_mm_parts(PARAM *param, Field *field, Item_func::Functype type,
 
   if (ne_func)
   {
-    SEL_TREE *tree2= get_mm_parts(param, field, Item_func::GT_FUNC,
+    SEL_TREE *tree2= get_mm_parts(param, cond_func,
+				  field, Item_func::GT_FUNC,
                                   value, cmp_type);
     if (tree2)
       tree= tree_or(param,tree,tree2);
@@ -963,7 +968,7 @@ get_mm_parts(PARAM *param, Field *field, Item_func::Functype type,
 
 
 static SEL_ARG *
-get_mm_leaf(PARAM *param, Field *field, KEY_PART *key_part,
+get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part,
 	    Item_func::Functype type,Item *value)
 {
   uint maybe_null=(uint) field->real_maybe_null(), copies;
@@ -972,6 +977,32 @@ get_mm_leaf(PARAM *param, Field *field, KEY_PART *key_part,
   char *str, *str2;
   DBUG_ENTER("get_mm_leaf");
 
+  if (!value)					// IS NULL or IS NOT NULL
+  {
+    if (field->table->outer_join)		// Can't use a key on this
+      DBUG_RETURN(0);
+    if (!maybe_null)				// Not null field
+      DBUG_RETURN(type == Item_func::ISNULL_FUNC ? &null_element : 0);
+    if (!(tree=new SEL_ARG(field,is_null_string,is_null_string)))
+      DBUG_RETURN(0);		// out of memory
+    if (type == Item_func::ISNOTNULL_FUNC)
+    {
+      tree->min_flag=NEAR_MIN;		    /* IS NOT NULL ->  X > NULL */
+      tree->max_flag=NO_MAX_RANGE;
+    }
+    DBUG_RETURN(tree);
+  }
+
+  /*
+    We can't use an index when comparing stings of 
+    different collations 
+  */
+  if (field->result_type() == STRING_RESULT &&
+      value->result_type() == STRING_RESULT &&
+      key_part->image_type == Field::itRAW &&
+      ((Field_str*)field)->charset() != conf_func->compare_collation())
+    DBUG_RETURN(0);
+
   if (type == Item_func::LIKE_FUNC)
   {
     bool like_error;
@@ -1035,22 +1066,6 @@ get_mm_leaf(PARAM *param, Field *field, KEY_PART *key_part,
     DBUG_RETURN(new SEL_ARG(field,min_str,max_str));
   }
 
-  if (!value)					// IS NULL or IS NOT NULL
-  {
-    if (field->table->outer_join)		// Can't use a key on this
-      DBUG_RETURN(0);
-    if (!maybe_null)				// Not null field
-      DBUG_RETURN(type == Item_func::ISNULL_FUNC ? &null_element : 0);
-    if (!(tree=new SEL_ARG(field,is_null_string,is_null_string)))
-      DBUG_RETURN(0);		// out of memory
-    if (type == Item_func::ISNOTNULL_FUNC)
-    {
-      tree->min_flag=NEAR_MIN;		    /* IS NOT NULL ->  X > NULL */
-      tree->max_flag=NO_MAX_RANGE;
-    }
-    DBUG_RETURN(tree);
-  }
-
   if (!field->optimize_range(param->real_keynr[key_part->key]) &&
       type != Item_func::EQ_FUNC &&
       type != Item_func::EQUAL_FUNC)
@@ -1064,7 +1079,7 @@ get_mm_leaf(PARAM *param, Field *field, KEY_PART *key_part,
       value->result_type() != STRING_RESULT &&
       field->cmp_type() != value->result_type())
     DBUG_RETURN(0);
-
+ 
   if (value->save_in_field(field, 1) > 0)
   {
     /* This happens when we try to insert a NULL field in a not null column */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5e2445be845..015d329cc7f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2133,7 +2133,7 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
 */
 
 static void
-add_key_field(KEY_FIELD **key_fields,uint and_level,
+add_key_field(KEY_FIELD **key_fields,uint and_level, COND *cond,
 	      Field *field,bool eq_func,Item **value, uint num_values,
 	      table_map usable_tables)
 {
@@ -2200,6 +2200,17 @@ add_key_field(KEY_FIELD **key_fields,uint and_level,
 	  (*value)->result_type() != STRING_RESULT &&
 	  field->cmp_type() != (*value)->result_type())
 	return;
+      
+      /*
+        We can't use indexes if the effective collation
+        of the operation differ from the field collation.
+      */
+      if (field->result_type() == STRING_RESULT &&
+	  (*value)->result_type() == STRING_RESULT &&
+	  field->cmp_type() == STRING_RESULT &&
+	  ((Field_str*)field)->charset() != cond->compare_collation())
+	return;
+
     }
   }
   DBUG_ASSERT(num_values == 1);
@@ -2263,7 +2274,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
     // BETWEEN or IN
     if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM &&
 	!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
-      add_key_field(key_fields,*and_level,
+      add_key_field(key_fields,*and_level,cond_func,
 		    ((Item_field*) (cond_func->key_item()->real_item()))->
 		    field, 0,
                     cond_func->arguments()+1, cond_func->argument_count()-1,
@@ -2277,7 +2288,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
     if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
 	!(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
     {
-      add_key_field(key_fields,*and_level,
+      add_key_field(key_fields,*and_level,cond_func,
 		    ((Item_field*) (cond_func->arguments()[0])->real_item())
 		    ->field,
 		    equal_func,
@@ -2287,7 +2298,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
 	cond_func->functype() != Item_func::LIKE_FUNC &&
 	!(cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT))
     {
-      add_key_field(key_fields,*and_level,
+      add_key_field(key_fields,*and_level,cond_func,
 		    ((Item_field*) (cond_func->arguments()[1])->real_item())
 		    ->field,
 		    equal_func,
@@ -2303,7 +2314,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
       Item *tmp=new Item_null;
       if (!tmp)					// Should never be true
 	return;
-      add_key_field(key_fields,*and_level,
+      add_key_field(key_fields,*and_level,cond_func,
 		    ((Item_field*) (cond_func->arguments()[0])->real_item())
 		    ->field,
 		    cond_func->functype() == Item_func::ISNULL_FUNC,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 76a0f384b15..f04cc9ec1dc 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1615,11 +1615,6 @@ opt_key_or_index:
 	| key_or_index
 	;
 
-opt_keys_or_index:
-	/* empty */ {}
-	| keys_or_index
-	;
-
 keys_or_index:
 	KEYS {}
 	| INDEX {}
@@ -2120,7 +2115,7 @@ cache_keys_spec:
 
 cache_key_list_or_empty:
 	/* empty */	{ Lex->select_lex.use_index_ptr= 0; }
-	| opt_keys_or_index '(' key_usage_list2 ')'
+	| opt_key_or_index '(' key_usage_list2 ')'
 	  {
             SELECT_LEX *sel= &Lex->select_lex;
 	    sel->use_index_ptr= &sel->use_index;