bk commit into 4.1 tree (bar:1.2467) BUG#9509

[bar]

Below is the list of changes that have just been committed into a local
4.1 repository of bar. When bar does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2467 06/04/20 15:09:01 bar@stripped +3 -0
  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.

  sql/sql_select.cc
    1.452 06/04/20 15:08:56 bar@stripped +4 -0
    Set proper collation of the new item

  mysql-test/t/ctype_utf8.test
    1.72 06/04/20 15:08:56 bar@stripped +18 -0
    Adding test case

  mysql-test/r/ctype_utf8.result
    1.70 06/04/20 15:08:56 bar@stripped +31 -0
    Adding test case

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	bar
# Host:	bar.intranet.mysql.r18.ru
# Root:	/usr/home/bar/mysql-4.1.b9509

--- 1.451/sql/sql_select.cc	2006-04-12 23:54:11 +05:00
+++ 1.452/sql/sql_select.cc	2006-04-20 15:08:56 +05:00
@@ -4456,6 +4456,8 @@ change_cond_ref_to_const(THD *thd, I_Lis
        left_item->collation.collation == value->collation.collation))
   {
     Item *tmp=value->new_item();
+    tmp->collation.set(right_item->collation);
+    
     if (tmp)
     {
       thd->change_item_tree(args + 1, tmp);
@@ -4477,6 +4479,8 @@ change_cond_ref_to_const(THD *thd, I_Lis
             right_item->collation.collation == value->collation.collation))
   {
     Item *tmp=value->new_item();
+    tmp->collation.set(left_item->collation);
+    
     if (tmp)
     {
       thd->change_item_tree(args, tmp);

--- 1.69/mysql-test/r/ctype_utf8.result	2006-03-30 03:50:50 +05:00
+++ 1.70/mysql-test/r/ctype_utf8.result	2006-04-20 15:08:56 +05:00
@@ -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;

--- 1.71/mysql-test/t/ctype_utf8.test	2006-03-30 03:50:50 +05:00
+++ 1.72/mysql-test/t/ctype_utf8.test	2006-04-20 15:08:56 +05:00
@@ -728,6 +728,24 @@ select repeat(_utf8'+',3) as h union sel
 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
 #
 create table t1 (a varchar(255)) default character set utf8;