Strings comparison is now done according to "Collating sequences used for

comparison" table, "SQL Complete, Really", page 162

8 files changed, 50 insertions, 16 deletions

diff --git a/mysql-test/r/ctype_many.result b/mysql-test/r/ctype_many.result
index 096ddf8188b..4d31ab6c274 100644
--- a/mysql-test/r/ctype_many.result
+++ b/mysql-test/r/ctype_many.result
@@ -1327,10 +1327,10 @@ INSERT INTO t1 (ucs2_f,comment) VALUES (0x0565,'ARMENIAN SMALL ECH');
 INSERT INTO t1 (ucs2_f,comment) VALUES (0x0566,'ARMENIAN SMALL ZA');
 ALTER TABLE t1 ADD armscii8_f CHAR(32) CHARACTER SET armscii8 NOT NULL;
 ALTER TABLE t1 ADD greek_f CHAR(32) CHARACTER SET greek NOT NULL;
-UPDATE t1 SET greek_f=CONVERT(ucs2_f USING greek) WHERE comment LIKE 'GRE%';
-UPDATE t1 SET armscii8_f=CONVERT(ucs2_f USING armscii8) WHERE comment LIKE 'ARM%';
-UPDATE t1 SET utf8_f=CONVERT(ucs2_f USING utf8) WHERE utf8_f='';
-UPDATE t1 SET ucs2_f=CONVERT(utf8_f USING ucs2) WHERE ucs2_f='';
+UPDATE t1 SET greek_f=CONVERT(ucs2_f USING greek) WHERE comment LIKE _latin2'GRE%';
+UPDATE t1 SET armscii8_f=CONVERT(ucs2_f USING armscii8) WHERE comment LIKE _latin2'ARM%';
+UPDATE t1 SET utf8_f=CONVERT(ucs2_f USING utf8) WHERE utf8_f=_utf8'';
+UPDATE t1 SET ucs2_f=CONVERT(utf8_f USING ucs2) WHERE ucs2_f=_ucs2'';
 SELECT * FROM t1;
 comment	koi8_ru_f	utf8_f	bin_f	ucs2_f	armscii8_f	greek_f
 LAT SMALL A	a	a	a	a		
diff --git a/mysql-test/r/func_system.result b/mysql-test/r/func_system.result
index 83c2ad6e020..83e78a30396 100644
--- a/mysql-test/r/func_system.result
+++ b/mysql-test/r/func_system.result
@@ -1,8 +1,8 @@
 select database(),user() like "%@%";
 database()	user() like "%@%"
 test	1
-select version()>="3.23.29";
-version()>="3.23.29"
+select version()>=_utf8"3.23.29";
+version()>=_utf8"3.23.29"
 1
 select TRUE,FALSE,NULL;
 TRUE	FALSE	NULL
diff --git a/mysql-test/t/ctype_many.test b/mysql-test/t/ctype_many.test
index ddb7034be10..05f3687d330 100644
--- a/mysql-test/t/ctype_many.test
+++ b/mysql-test/t/ctype_many.test
@@ -194,10 +194,10 @@ INSERT INTO t1 (ucs2_f,comment) VALUES (0x0566,'ARMENIAN SMALL ZA');
 
 ALTER TABLE t1 ADD armscii8_f CHAR(32) CHARACTER SET armscii8 NOT NULL;
 ALTER TABLE t1 ADD greek_f CHAR(32) CHARACTER SET greek NOT NULL;
-UPDATE t1 SET greek_f=CONVERT(ucs2_f USING greek) WHERE comment LIKE 'GRE%';
-UPDATE t1 SET armscii8_f=CONVERT(ucs2_f USING armscii8) WHERE comment LIKE 'ARM%';
-UPDATE t1 SET utf8_f=CONVERT(ucs2_f USING utf8) WHERE utf8_f='';
-UPDATE t1 SET ucs2_f=CONVERT(utf8_f USING ucs2) WHERE ucs2_f='';
+UPDATE t1 SET greek_f=CONVERT(ucs2_f USING greek) WHERE comment LIKE _latin2'GRE%';
+UPDATE t1 SET armscii8_f=CONVERT(ucs2_f USING armscii8) WHERE comment LIKE _latin2'ARM%';
+UPDATE t1 SET utf8_f=CONVERT(ucs2_f USING utf8) WHERE utf8_f=_utf8'';
+UPDATE t1 SET ucs2_f=CONVERT(utf8_f USING ucs2) WHERE ucs2_f=_ucs2'';
 SELECT * FROM t1;
 SET NAMES 'binary';
 SELECT * FROM t1;
diff --git a/mysql-test/t/func_system.test b/mysql-test/t/func_system.test
index c69526644f4..db366e3ab49 100644
--- a/mysql-test/t/func_system.test
+++ b/mysql-test/t/func_system.test
@@ -3,5 +3,5 @@
 #
 
 select database(),user() like "%@%";
-select version()>="3.23.29";
+select version()>=_utf8"3.23.29";
 select TRUE,FALSE,NULL;
diff --git a/sql/item.cc b/sql/item.cc
index f2053c37fb7..fc5acd3c2fb 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -39,7 +39,7 @@ Item::Item():
 {
   marker= 0;
   maybe_null=null_value=with_sum_func=unsigned_flag=0;
-  coercibility=COER_NOCOLL;
+  coercibility=COER_IMPLICIT;
   name= 0;
   decimals= 0; max_length= 0;
   THD *thd= current_thd;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 1ae16827e0b..8d3789783a8 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -87,6 +87,35 @@ static bool convert_constant_item(Field *field, Item **item)
   return 0;
 }
 
+bool Item_bool_func2::set_cmp_charset(CHARSET_INFO *cs1, enum coercion co1,
+				      CHARSET_INFO *cs2, enum coercion co2)
+{
+  if((cs1 == &my_charset_bin) || (cs2 == &my_charset_bin))
+  {
+    cmp_charset= &my_charset_bin;
+    return 0;
+  }
+
+  if ((co1 == COER_NOCOLL) || (co2 == COER_NOCOLL))
+    return 1;
+
+  if (!my_charset_same(cs1,cs2))
+    return 1;
+
+  if (co1 < co2)
+    cmp_charset= cs1;
+  else if (co2 < co1)
+    cmp_charset= cs2;
+  else // co1==co2
+  {
+    if (cs1 == cs2)
+      cmp_charset= cs1;
+    else
+      return 1;
+  }
+  return 0;
+}
+
 void Item_bool_func2::fix_length_and_dec()
 {
   max_length= 1;				     // Function returns 0 or 1
@@ -124,9 +153,12 @@ void Item_bool_func2::fix_length_and_dec()
       }
     }
   }
-  /* QQ: COERCIBILITY */
-  cmp_charset= (args[0]->binary() || args[1]->binary()) ?
-                &my_charset_bin : args[0]->charset();
+  if (set_cmp_charset(args[0]->charset(), args[0]->coercibility,
+		      args[1]->charset(), args[1]->coercibility))
+  {
+    my_error(ER_WRONG_ARGUMENTS,MYF(0),func_name());
+    return;
+  }
   set_cmp_func();
 }
 
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index b9d8615beb4..e7670755396 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -122,6 +122,8 @@ public:
   {
     cmp.set_cmp_func(this, tmp_arg, tmp_arg+1);
   }
+  bool set_cmp_charset(CHARSET_INFO *cs1, enum coercion co1, 
+  		       CHARSET_INFO *cs2, enum coercion co2);
   optimize_type select_optimize() const { return OPTIMIZE_OP; }
   virtual enum Functype rev_functype() const { return UNKNOWN_FUNC; }
   bool have_rev_func() const { return rev_functype() != UNKNOWN_FUNC; }
diff --git a/sql/item_create.cc b/sql/item_create.cc
index eff0c8ab137..b80327b657a 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -433,7 +433,7 @@ Item *create_func_version(void)
 {
   return new Item_string(NullS,server_version, 
 			 (uint) strlen(server_version),
-			 default_charset_info);
+			 system_charset_info);
 }
 
 Item *create_func_weekday(Item* a)