BUG#48447, BUG#48161, fixed a regression from fix of BUG#6045, where binary collations can use indexes/partition pruning for cases using equality conditions, however it cannot be used for any other condition like <, >, <=, >=, <>, also added test case for verification of BUG#47774 in this patch

5 files changed, 128 insertions, 1 deletions

diff --git a/mysql-test/r/partition_column.result b/mysql-test/r/partition_column.result
index 8c7d5b4ff77..daed67b1cce 100644
--- a/mysql-test/r/partition_column.result
+++ b/mysql-test/r/partition_column.result
@@ -1,4 +1,69 @@
 drop table if exists t1;
+create table t1 (a varchar(1) character set latin1 collate latin1_general_ci);
+insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c');
+select * from t1 where a > 'B' collate latin1_bin;
+a
+a
+b
+C
+c
+select * from t1 where a <> 'B' collate latin1_bin;
+a
+A
+a
+b
+C
+c
+create index i on t1 (a);
+select * from t1 where a > 'B' collate latin1_bin;
+a
+a
+b
+C
+c
+select * from t1 where a <> 'B' collate latin1_bin;
+a
+A
+a
+b
+C
+c
+drop index i on t1;
+alter table t1
+partition by range columns(a)
+( partition p0 values less than ('a'),
+partition p1 values less than ('b'),
+partition p2 values less than ('c'),
+partition p3 values less than ('d'));
+select * from t1 where a > 'B' collate latin1_bin;
+a
+a
+b
+C
+c
+select * from t1 where a <> 'B' collate latin1_bin;
+a
+A
+a
+b
+C
+c
+drop table t1;
+create table t1 (a varchar(2) character set latin1,
+b varchar(2) character set latin1)
+partition by list columns(a,b)
+(partition p0 values in (('a','a')));
+insert into t1 values ('A','A');
+select * from t1 where b <> 'a' collate latin1_bin AND
+a = 'A' collate latin1_bin;
+a	b
+A	A
+alter table t1 remove partitioning;
+select * from t1 where b <> 'a' collate latin1_bin AND
+a = 'A' collate latin1_bin;
+a	b
+A	A
+drop table t1;
 create table t1 (a varchar(5))
 partition by list columns(a)
 ( partition p0 values in ('\''),
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 53e0d88f3f8..d95e7dc43fd 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -1,4 +1,15 @@
 drop table if exists t1;
+create table t1 (a varchar(5), b int signed, c varchar(10), d datetime)
+partition by range columns(b,c)
+subpartition by hash(to_seconds(d))
+( partition p0 values less than (2, 'b'),
+partition p1 values less than (4, 'd'),
+partition p2 values less than (10, 'za'));
+insert into t1 values ('a', 3, 'w', '2001-10-27 04:34:00');
+insert into t1 values ('r', 7, 'w', '2001-10-27 05:34:00');
+insert into t1 values ('g', 10, 'w', '2001-10-27 06:34:00');
+update t1 set a = 'c' where a > 'f';
+drop table t1;
 create table t1 (a varchar(5))
 engine=memory
 partition by range columns(a)
diff --git a/mysql-test/t/partition_column.test b/mysql-test/t/partition_column.test
index 6b62e3576b4..78828afd484 100644
--- a/mysql-test/t/partition_column.test
+++ b/mysql-test/t/partition_column.test
@@ -8,6 +8,41 @@
 drop table if exists t1;
 --enable_warnings
 
+#
+# BUG#48161, Delivering too few records using collate syntax with partitions
+# BUG#48447, Delivering too few records with indexes using collate syntax
+#
+# Test case from BUG#48447 with some extension
+create table t1 (a varchar(1) character set latin1 collate latin1_general_ci);
+insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c');
+select * from t1 where a > 'B' collate latin1_bin;
+select * from t1 where a <> 'B' collate latin1_bin;
+create index i on t1 (a);
+select * from t1 where a > 'B' collate latin1_bin;
+select * from t1 where a <> 'B' collate latin1_bin;
+drop index i on t1;
+alter table t1
+partition by range columns(a)
+( partition p0 values less than ('a'),
+  partition p1 values less than ('b'),
+  partition p2 values less than ('c'),
+  partition p3 values less than ('d'));
+select * from t1 where a > 'B' collate latin1_bin;
+select * from t1 where a <> 'B' collate latin1_bin;
+drop table t1;
+# Test case from BUG#48161
+create table t1 (a varchar(2) character set latin1,
+                 b varchar(2) character set latin1)
+partition by list columns(a,b)
+(partition p0 values in (('a','a')));
+insert into t1 values ('A','A');
+select * from t1 where b <> 'a' collate latin1_bin AND
+                       a = 'A' collate latin1_bin;
+alter table t1 remove partitioning;
+select * from t1 where b <> 'a' collate latin1_bin AND
+                       a = 'A' collate latin1_bin;
+drop table t1;
+
 create table t1 (a varchar(5))
 partition by list columns(a)
 ( partition p0 values in ('\''),
diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index 5aef5dcaa18..2e08834cfc7 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -6,6 +6,21 @@ drop table if exists t1;
 --enable_warnings
 
 #
+# BUG#47774, Assertion failure in InnoDB using column list partitioning
+#
+create table t1 (a varchar(5), b int signed, c varchar(10), d datetime)
+partition by range columns(b,c)
+subpartition by hash(to_seconds(d))
+( partition p0 values less than (2, 'b'),
+  partition p1 values less than (4, 'd'),
+  partition p2 values less than (10, 'za'));
+insert into t1 values ('a', 3, 'w', '2001-10-27 04:34:00');
+insert into t1 values ('r', 7, 'w', '2001-10-27 05:34:00');
+insert into t1 values ('g', 10, 'w', '2001-10-27 06:34:00');
+update t1 set a = 'c' where a > 'f';
+drop table t1;
+
+#
 # BUG#47776, Failed to update for MEMORY engine, crash for InnoDB and success for MyISAM
 #
 create table t1 (a varchar(5))
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ce86fa535fe..e2be5695ccb 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -5882,7 +5882,8 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
       value->result_type() == STRING_RESULT &&
       key_part->image_type == Field::itRAW &&
       ((Field_str*)field)->charset() != conf_func->compare_collation() &&
-      !(conf_func->compare_collation()->state & MY_CS_BINSORT))
+      !(conf_func->compare_collation()->state & MY_CS_BINSORT &&
+        (type == Item_func::EQUAL_FUNC || type == Item_func::EQ_FUNC)))
     goto end;
 
   if (param->using_real_indexes)