From 9328e8874ac3eb2351ad2a283482efd097c8515e Mon Sep 17 00:00:00 2001
From: unknown <bar@mysql.com>
Date: Thu, 20 Apr 2006 15:09:01 +0500
Subject: Bug#9509: Optimizer: wrong result after AND with latin1_german2_ci
 comparisons

Fixing part2 of this problem: AND didn't work well
with utf8_czech_ci and utf8_lithianian_ci in some cases.

The problem was because when during condition optimization
field was replaced with a constant, the constant's collation
and collation derivation was used later for comparison instead
of the field collation and derivation, which led to non-equal
new condition in some cases.

This patch copies collation and derivation from the field being removed
to the new constant, which makes comparison work using the same collation
with the one which would be used if no condition optimization were done.

In other words:

  where s1 < 'K' and s1 = 'Y';

was rewritten to:

  where 'Y' < 'K' and s1 = 'Y';

Now it's rewritten to:

  where 'Y' collate collation_of_s1 < 'K' and s1 = 'Y'

  (using derivation of s1)


Note, the first problem of this bug (with latin1_german2_ci) was fixed
earlier in 5.0 tree, in a separate changeset.


mysql-test/r/ctype_utf8.result:
  Adding test case
mysql-test/t/ctype_utf8.test:
  Adding test case
sql/sql_select.cc:
  Set proper collation of the new item
---
 mysql-test/r/ctype_utf8.result | 31 +++++++++++++++++++++++++++++++
 mysql-test/t/ctype_utf8.test   | 18 ++++++++++++++++++
 2 files changed, 49 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 69d7577ee77..615b0ae2d52 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -924,6 +924,37 @@ NULL
 select ifnull(NULL, _utf8'string');
 ifnull(NULL, _utf8'string')
 string
+set names utf8;
+create table t1 (s1 char(5) character set utf8 collate utf8_lithuanian_ci);
+insert into t1 values ('I'),('K'),('Y');
+select * from t1 where s1 < 'K' and s1 = 'Y';
+s1
+I
+Y
+select * from t1 where 'K' > s1 and s1 = 'Y';
+s1
+I
+Y
+drop table t1;
+create table t1 (s1 char(5) character set utf8 collate utf8_czech_ci);
+insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i');
+select * from t1 where s1 > 'd' and s1 = 'CH';
+s1
+ch
+CH
+Ch
+select * from t1 where 'd' < s1 and s1 = 'CH';
+s1
+ch
+CH
+Ch
+select * from t1 where s1 = 'cH' and s1 <> 'ch';
+s1
+cH
+select * from t1 where 'cH' = s1 and s1 <> 'ch';
+s1
+cH
+drop table t1;
 create table t1 (a varchar(255)) default character set utf8;
 insert into t1 values (1.0);
 drop table t1;
diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test
index 5044f7979f1..159e8490f12 100644
--- a/mysql-test/t/ctype_utf8.test
+++ b/mysql-test/t/ctype_utf8.test
@@ -727,6 +727,24 @@ drop table t1;
 select repeat(_utf8'+',3) as h union select NULL;
 select ifnull(NULL, _utf8'string');
 
+#
+# Bug#9509 Optimizer: wrong result after AND with comparisons
+#
+set names utf8;
+create table t1 (s1 char(5) character set utf8 collate utf8_lithuanian_ci);
+insert into t1 values ('I'),('K'),('Y');
+select * from t1 where s1 < 'K' and s1 = 'Y';
+select * from t1 where 'K' > s1 and s1 = 'Y';
+drop table t1;
+
+create table t1 (s1 char(5) character set utf8 collate utf8_czech_ci);
+insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i');
+select * from t1 where s1 > 'd' and s1 = 'CH';
+select * from t1 where 'd' < s1 and s1 = 'CH';
+select * from t1 where s1 = 'cH' and s1 <> 'ch';
+select * from t1 where 'cH' = s1 and s1 <> 'ch';
+drop table t1;
+
 #
 # Bug#10714: Inserting double value into utf8 column crashes server
 #
-- 
cgit v1.2.1