BETWEEN now works according to collation rules

4 files changed, 62 insertions, 10 deletions

diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 96c23ab1e22..959cedeeb0d 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -276,6 +276,26 @@ select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substr_index'
 select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
 ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substr_index'
+select _latin1'B' between _latin1'a' and _latin1'c';
+_latin1'B' between _latin1'a' and _latin1'c'
+1
+select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
+_latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'
+0
+select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
+_latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'
+0
+select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
+_latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin
+0
+select _latin2'B' between _latin1'a' and _latin1'b';
+ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
+select _latin1'B' between _latin2'a' and _latin1'b';
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
+select _latin1'B' between _latin1'a' and _latin2'b';
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between'
+select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
+ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between'
 select collation(bin(130)), coercibility(bin(130));
 collation(bin(130))	coercibility(bin(130))
 latin1_swedish_ci	3
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 7e6d2648e1e..41a37c0f7ab 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -162,6 +162,18 @@ select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
 --error 1265
 select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
 
+select _latin1'B' between _latin1'a' and _latin1'c';
+select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
+select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
+select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
+--error 1268
+select _latin2'B' between _latin1'a' and _latin1'b';
+--error 1268
+select _latin1'B' between _latin2'a' and _latin1'b';
+--error 1268
+select _latin1'B' between _latin1'a' and _latin2'b';
+--error 1268
+select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
 
 select collation(bin(130)), coercibility(bin(130));
 select collation(oct(130)), coercibility(oct(130));
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index ca3f55a9804..b764bb322d3 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -32,6 +32,18 @@ static void my_coll_agg_error(DTCollation &c1, DTCollation &c2, const char *fnam
 	   fname);
 }
 
+static void my_coll_agg3_error(DTCollation &c1, 
+			       DTCollation &c2,
+			       DTCollation &c3,
+			       const char *fname)
+{
+  my_error(ER_CANT_AGGREGATE_3COLLATIONS,MYF(0),
+  	   c1.collation->name,c1.derivation_name(),
+	   c2.collation->name,c2.derivation_name(),
+	   c3.collation->name,c3.derivation_name(),
+	   fname);
+}
+
 Item_bool_func2* Item_bool_func2::eq_creator(Item *a, Item *b)
 {
   return new Item_func_eq(a, b);
@@ -575,11 +587,19 @@ void Item_func_between::fix_length_and_dec()
   cmp_type=item_cmp_type(args[0]->result_type(),
            item_cmp_type(args[1]->result_type(),
                          args[2]->result_type()));
-  /* QQ: COERCIBILITY */
-  if (args[0]->binary() | args[1]->binary() | args[2]->binary())
-    cmp_charset= &my_charset_bin;
-  else
-    cmp_charset= args[0]->charset();
+
+  if (cmp_type == STRING_RESULT)
+  {
+    cmp_collation.set(args[0]->collation);
+    if (!cmp_collation.aggregate(args[1]->collation))
+      cmp_collation.aggregate(args[2]->collation);
+    if (cmp_collation.derivation == DERIVATION_NONE)
+    {
+      my_coll_agg3_error(args[0]->collation, args[1]->collation, 
+			 args[2]->collation, func_name());
+      return;
+    }
+  }
 
   /*
     Make a special case of compare with date/time and longlong fields.
@@ -611,17 +631,17 @@ longlong Item_func_between::val_int()
     a=args[1]->val_str(&value1);
     b=args[2]->val_str(&value2);
     if (!args[1]->null_value && !args[2]->null_value)
-      return (sortcmp(value,a,cmp_charset) >= 0 && 
-	      sortcmp(value,b,cmp_charset) <= 0) ? 1 : 0;
+      return (sortcmp(value,a,cmp_collation.collation) >= 0 && 
+	      sortcmp(value,b,cmp_collation.collation) <= 0) ? 1 : 0;
     if (args[1]->null_value && args[2]->null_value)
       null_value=1;
     else if (args[1]->null_value)
     {
-      null_value= sortcmp(value,b,cmp_charset) <= 0; // not null if false range.
+      null_value= sortcmp(value,b,cmp_collation.collation) <= 0; // not null if false range.
     }
     else
     {
-      null_value= sortcmp(value,a,cmp_charset) >= 0; // not null if false range.
+      null_value= sortcmp(value,a,cmp_collation.collation) >= 0; // not null if false range.
     }
   }
   else if (cmp_type == INT_RESULT)
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 1221e316a72..792f2e2411c 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -245,7 +245,7 @@ public:
 
 class Item_func_between :public Item_int_func
 {
-  CHARSET_INFO *cmp_charset;
+  DTCollation cmp_collation;
 public:
   Item_result cmp_type;
   String value0,value1,value2;