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

1 files changed, 35 insertions, 0 deletions

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 ('\''),