Bug#9509 Optimizer: wrong result after AND with latin1_german2_ci

We cannot propagate constants with tricky collations.

22 files changed, 115 insertions, 17 deletions

diff --git a/include/m_ctype.h b/include/m_ctype.h
index 61524dc4ddd..6f304f4ba43 100644
--- a/include/m_ctype.h
+++ b/include/m_ctype.h
@@ -132,6 +132,7 @@ typedef struct my_collation_handler_st
   /* Hash calculation */
   void (*hash_sort)(struct charset_info_st *cs, const uchar *key, uint len,
 		    ulong *nr1, ulong *nr2); 
+  my_bool (*propagate)(struct charset_info_st *cs, const uchar *str, uint len);
 } MY_COLLATION_HANDLER;
 
 extern MY_COLLATION_HANDLER my_collation_mb_bin_handler;
@@ -385,6 +386,10 @@ int my_wildcmp_unicode(CHARSET_INFO *cs,
 extern my_bool my_parse_charset_xml(const char *bug, uint len,
 				    int (*add)(CHARSET_INFO *cs));
 
+my_bool my_propagate_simple(CHARSET_INFO *cs, const uchar *str, uint len);
+my_bool my_propagate_complex(CHARSET_INFO *cs, const uchar *str, uint len);
+
+
 #define	_MY_U	01	/* Upper case */
 #define	_MY_L	02	/* Lower case */
 #define	_MY_NMR	04	/* Numeral (digit) */
diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result
index 43947edbd48..f60dc175cd6 100644
--- a/mysql-test/r/ctype_latin1_de.result
+++ b/mysql-test/r/ctype_latin1_de.result
@@ -338,3 +338,9 @@ ss
 ss
 ß
 DROP TABLE t1;
+create table t1 (s1 char(5) character set latin1 collate latin1_german2_ci);
+insert into t1 values (0xf6) /* this is o-umlaut */;
+select * from t1 where length(s1)=1 and s1='oe';
+s1
+ö
+drop table t1;
diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index e17806a54aa..88eb840a787 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -132,3 +132,11 @@ INSERT INTO t1 VALUES ('ß'),('ss'),('ss');
 ALTER TABLE t1 ADD KEY ifword(col1);
 SELECT * FROM t1 WHERE col1='ß' ORDER BY col1, BINARY col1;
 DROP TABLE t1;
+
+#
+# Bug#9509
+#
+create table t1 (s1 char(5) character set latin1 collate latin1_german2_ci);
+insert into t1 values (0xf6) /* this is o-umlaut */;
+select * from t1 where length(s1)=1 and s1='oe';
+drop table t1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 32624bb3305..6e888c73f42 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6435,10 +6435,13 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal)
       {
         bool copyfl;
 
-        if (field_item->result_type() == STRING_RESULT &&
-              ((Field_str *) field_item->field)->charset() !=
-               ((Item_cond *) item)->compare_collation())
-          return FALSE;
+        if (field_item->result_type() == STRING_RESULT)
+        {
+          CHARSET_INFO *cs= ((Field_str*) field_item->field)->charset();
+          if ((cs != ((Item_cond *) item)->compare_collation()) ||
+              !cs->coll->propagate(cs, 0, 0))
+            return FALSE;
+        }
 
         Item_equal *item_equal = find_item_equal(cond_equal,
                                                  field_item->field, &copyfl);
diff --git a/strings/ctype-big5.c b/strings/ctype-big5.c
index ab6691e68b0..e12ff189eaf 100644
--- a/strings/ctype-big5.c
+++ b/strings/ctype-big5.c
@@ -6335,7 +6335,8 @@ static MY_COLLATION_HANDLER my_collation_big5_chinese_ci_handler =
   my_wildcmp_mb,
   my_strcasecmp_mb,
   my_instr_mb,
-  my_hash_sort_simple
+  my_hash_sort_simple,
+  my_propagate_simple
 };
 
 static MY_CHARSET_HANDLER my_charset_big5_handler=
diff --git a/strings/ctype-bin.c b/strings/ctype-bin.c
index 9f0e334d3a9..56df289158a 100644
--- a/strings/ctype-bin.c
+++ b/strings/ctype-bin.c
@@ -459,7 +459,8 @@ MY_COLLATION_HANDLER my_collation_8bit_bin_handler =
     my_wildcmp_bin,
     my_strcasecmp_bin,
     my_instr_bin,
-    my_hash_sort_bin
+    my_hash_sort_bin,
+    my_propagate_simple
 };
 
 
@@ -474,7 +475,8 @@ static MY_COLLATION_HANDLER my_collation_binary_handler =
     my_wildcmp_bin,
     my_strcasecmp_bin,
     my_instr_bin,
-    my_hash_sort_bin
+    my_hash_sort_bin,
+    my_propagate_simple
 };
 
 
diff --git a/strings/ctype-cp932.c b/strings/ctype-cp932.c
index 47bf1167c8c..f2f31b1064e 100644
--- a/strings/ctype-cp932.c
+++ b/strings/ctype-cp932.c
@@ -5462,6 +5462,7 @@ static MY_COLLATION_HANDLER my_collation_ci_handler =
   my_strcasecmp_8bit,
   my_instr_mb,
   my_hash_sort_simple,
+  my_propagate_simple
 };
 
 
diff --git a/strings/ctype-czech.c b/strings/ctype-czech.c
index f5a410afc50..aaf87e97cb8 100644
--- a/strings/ctype-czech.c
+++ b/strings/ctype-czech.c
@@ -599,6 +599,7 @@ static MY_COLLATION_HANDLER my_collation_latin2_czech_ci_handler =
   my_strcasecmp_8bit,
   my_instr_simple,
   my_hash_sort_simple,
+  my_propagate_simple
 };
 
 CHARSET_INFO my_charset_latin2_czech_ci =
diff --git a/strings/ctype-euc_kr.c b/strings/ctype-euc_kr.c
index 289b7309ea0..21b7b56fdaa 100644
--- a/strings/ctype-euc_kr.c
+++ b/strings/ctype-euc_kr.c
@@ -8647,6 +8647,7 @@ static MY_COLLATION_HANDLER my_collation_ci_handler =
   my_strcasecmp_mb,
   my_instr_mb,
   my_hash_sort_simple,
+  my_propagate_simple
 };
 
 static MY_CHARSET_HANDLER my_charset_handler=
diff --git a/strings/ctype-eucjpms.c b/strings/ctype-eucjpms.c
index ab12446754a..d9171c800bf 100644
--- a/strings/ctype-eucjpms.c
+++ b/strings/ctype-eucjpms.c
@@ -8648,6 +8648,7 @@ static MY_COLLATION_HANDLER my_collation_ci_handler =
     my_strcasecmp_mb,
     my_instr_mb,
     my_hash_sort_simple,
+    my_propagate_simple
 };
 
 static MY_CHARSET_HANDLER my_charset_handler=
diff --git a/strings/ctype-gb2312.c b/strings/ctype-gb2312.c
index 73e4132dd7f..592ee341781 100644
--- a/strings/ctype-gb2312.c
+++ b/strings/ctype-gb2312.c
@@ -5698,6 +5698,7 @@ static MY_COLLATION_HANDLER my_collation_ci_handler =
   my_strcasecmp_mb,     /* instr      */
   my_instr_mb,
   my_hash_sort_simple,
+  my_propagate_simple
 };
 
 static MY_CHARSET_HANDLER my_charset_handler=
diff --git a/strings/ctype-gbk.c b/strings/ctype-gbk.c
index d4f9627ecf7..ec96caa6b91 100644
--- a/strings/ctype-gbk.c
+++ b/strings/ctype-gbk.c
@@ -9945,6 +9945,7 @@ static MY_COLLATION_HANDLER my_collation_ci_handler =
   my_strcasecmp_mb,
   my_instr_mb,
   my_hash_sort_simple,
+  my_propagate_simple
 };
 
 static MY_CHARSET_HANDLER my_charset_handler=
diff --git a/strings/ctype-latin1.c b/strings/ctype-latin1.c
index fdf9f4a6d91..7a75992dc4f 100644
--- a/strings/ctype-latin1.c
+++ b/strings/ctype-latin1.c
@@ -698,7 +698,8 @@ static MY_COLLATION_HANDLER my_collation_german2_ci_handler=
   my_wildcmp_8bit,
   my_strcasecmp_8bit,
   my_instr_simple,
-  my_hash_sort_latin1_de
+  my_hash_sort_latin1_de,
+  my_propagate_complex
 };
 
 
diff --git a/strings/ctype-mb.c b/strings/ctype-mb.c
index b603a8ea0a8..b3ec476b8f5 100644
--- a/strings/ctype-mb.c
+++ b/strings/ctype-mb.c
@@ -920,7 +920,8 @@ MY_COLLATION_HANDLER my_collation_mb_bin_handler =
     my_wildcmp_mb_bin,
     my_strcasecmp_mb_bin,
     my_instr_mb,
-    my_hash_sort_mb_bin
+    my_hash_sort_mb_bin,
+    my_propagate_simple
 };
 
 
diff --git a/strings/ctype-simple.c b/strings/ctype-simple.c
index 5fa1a1b18a0..d19326d9265 100644
--- a/strings/ctype-simple.c
+++ b/strings/ctype-simple.c
@@ -1340,6 +1340,60 @@ longlong my_strtoll10_8bit(CHARSET_INFO *cs __attribute__((unused)),
 }
 
 
+/*
+  Check if a constant can be propagated
+
+  SYNOPSIS:
+    my_propagate_simple()
+    cs		Character set information
+    str		String to convert to double
+    length	Optional length for string.
+    
+  NOTES:
+   Takes the string in the given charset and check
+   if it can be safely propagated in the optimizer.
+   
+   create table t1 (
+     s char(5) character set latin1 collate latin1_german2_ci);
+   insert into t1 values (0xf6); -- o-umlaut
+   select * from t1 where length(s)=1 and s='oe';
+
+   The above query should return one row.
+   We cannot convert this query into:
+   select * from t1 where length('oe')=1 and s='oe';
+   
+   Currently we don't check the constant itself,
+   and decide not to propagate a constant
+   just if the collation itself allows tricky things
+   like expansions and contractions. In the future
+   we can write a more sophisticated functions to
+   check the constants. For example, 'oa' can always
+   be safety propagated in German2 because unlike 
+   'oe' it does not have any special meaning.
+
+  RETURN
+    1 if constant can be safely propagated
+    0 if it is not safe to propagate the constant
+*/
+
+
+
+my_bool my_propagate_simple(CHARSET_INFO *cs __attribute__((unused)),
+                            const uchar *str __attribute__((unused)),
+                            uint length __attribute__((unused)))
+{
+  return 1;
+}
+
+
+my_bool my_propagate_complex(CHARSET_INFO *cs __attribute__((unused)),
+                             const uchar *str __attribute__((unused)),
+                             uint length __attribute__((unused)))
+{
+  return 0;
+}
+
+
 MY_CHARSET_HANDLER my_charset_8bit_handler=
 {
     my_cset_init_8bit,
@@ -1380,5 +1434,6 @@ MY_COLLATION_HANDLER my_collation_8bit_simple_ci_handler =
     my_wildcmp_8bit,
     my_strcasecmp_8bit,
     my_instr_simple,
-    my_hash_sort_simple
+    my_hash_sort_simple,
+    my_propagate_simple
 };
diff --git a/strings/ctype-sjis.c b/strings/ctype-sjis.c
index 62cb5427dd9..9b7fd5097fa 100644
--- a/strings/ctype-sjis.c
+++ b/strings/ctype-sjis.c
@@ -4631,6 +4631,7 @@ static MY_COLLATION_HANDLER my_collation_ci_handler =
   my_strcasecmp_8bit,
   my_instr_mb,
   my_hash_sort_simple,
+  my_propagate_simple
 };
 
 
diff --git a/strings/ctype-tis620.c b/strings/ctype-tis620.c
index b6c54f1b375..c40e74c3343 100644
--- a/strings/ctype-tis620.c
+++ b/strings/ctype-tis620.c
@@ -933,6 +933,7 @@ static MY_COLLATION_HANDLER my_collation_ci_handler =
     my_strcasecmp_8bit,
     my_instr_simple,				/* QQ: To be fixed */
     my_hash_sort_simple,
+    my_propagate_simple
 };
 
 static MY_CHARSET_HANDLER my_charset_handler=
diff --git a/strings/ctype-uca.c b/strings/ctype-uca.c
index 3204bd2e1f6..fb7e93e10b6 100644
--- a/strings/ctype-uca.c
+++ b/strings/ctype-uca.c
@@ -8029,7 +8029,8 @@ MY_COLLATION_HANDLER my_collation_ucs2_uca_handler =
     my_wildcmp_uca,
     NULL,
     my_instr_mb,
-    my_hash_sort_ucs2_uca
+    my_hash_sort_ucs2_uca,
+    my_propagate_complex
 };
 
 CHARSET_INFO my_charset_ucs2_general_uca=
@@ -8510,7 +8511,8 @@ MY_COLLATION_HANDLER my_collation_any_uca_handler =
     my_wildcmp_uca,
     NULL,
     my_instr_mb,
-    my_hash_sort_any_uca
+    my_hash_sort_any_uca,
+    my_propagate_complex
 };
 
 /* 
diff --git a/strings/ctype-ucs2.c b/strings/ctype-ucs2.c
index 73d15da8a4a..d15144b7438 100644
--- a/strings/ctype-ucs2.c
+++ b/strings/ctype-ucs2.c
@@ -1527,7 +1527,8 @@ static MY_COLLATION_HANDLER my_collation_ucs2_general_ci_handler =
     my_wildcmp_ucs2_ci,
     my_strcasecmp_ucs2,
     my_instr_mb,
-    my_hash_sort_ucs2
+    my_hash_sort_ucs2,
+    my_propagate_simple
 };
 
 
@@ -1542,7 +1543,8 @@ static MY_COLLATION_HANDLER my_collation_ucs2_bin_handler =
     my_wildcmp_ucs2_bin,
     my_strcasecmp_ucs2_bin,
     my_instr_mb,
-    my_hash_sort_ucs2_bin
+    my_hash_sort_ucs2_bin,
+    my_propagate_simple
 };
 
 
diff --git a/strings/ctype-ujis.c b/strings/ctype-ujis.c
index 7bcf1c83bab..5d0c77cee6e 100644
--- a/strings/ctype-ujis.c
+++ b/strings/ctype-ujis.c
@@ -8516,6 +8516,7 @@ static MY_COLLATION_HANDLER my_collation_ci_handler =
     my_strcasecmp_mb,
     my_instr_mb,
     my_hash_sort_simple,
+    my_propagate_simple
 };
 
 static MY_CHARSET_HANDLER my_charset_handler=
diff --git a/strings/ctype-utf8.c b/strings/ctype-utf8.c
index 14b3934e815..250c57cf265 100644
--- a/strings/ctype-utf8.c
+++ b/strings/ctype-utf8.c
@@ -2316,7 +2316,8 @@ static MY_COLLATION_HANDLER my_collation_ci_handler =
     my_wildcmp_utf8,
     my_strcasecmp_utf8,
     my_instr_mb,
-    my_hash_sort_utf8
+    my_hash_sort_utf8,
+    my_propagate_complex
 };
 
 MY_CHARSET_HANDLER my_charset_utf8_handler=
@@ -2540,7 +2541,8 @@ static MY_COLLATION_HANDLER my_collation_cs_handler =
     my_wildcmp_mb,
     my_strcasecmp_utf8,
     my_instr_mb,
-    my_hash_sort_utf8
+    my_hash_sort_utf8,
+    my_propagate_simple
 };
 
 CHARSET_INFO my_charset_utf8_general_cs=
diff --git a/strings/ctype-win1250ch.c b/strings/ctype-win1250ch.c
index 8c58520f965..cb8de8f43ac 100644
--- a/strings/ctype-win1250ch.c
+++ b/strings/ctype-win1250ch.c
@@ -631,7 +631,8 @@ static MY_COLLATION_HANDLER my_collation_czech_ci_handler =
   my_wildcmp_8bit,
   my_strcasecmp_8bit,
   my_instr_simple,
-  my_hash_sort_simple
+  my_hash_sort_simple,
+  my_propagate_simple
 };