MDEV-6322: The PARTITION engine can return wrong query results

MySQL Bug#71095: Wrong results with PARTITION BY LIST COLUMNS()
MySQL Bug#72803: Wrong "Impossible where" with LIST partitioning
MDEV-6240: Wrong "Impossible where" with LIST partitioning
- Backprot the fix from MySQL Bug#71095.

5 files changed, 150 insertions, 4 deletions

diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 4379cf17ec0..435ed6270ca 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2521,3 +2521,47 @@ id	id2	dob	address	city	hours_worked_per_week	weeks_worked_last_year
 16	16	1949-11-07	address16	city16	40	52
 50	50	1923-09-08	address50	city50	40	52
 drop table t1;
+#
+# MDEV-6322: The PARTITION engine can return wrong query results
+#
+CREATE TABLE t1 (
+CustomerID varchar(5) DEFAULT NULL,
+CompanyName varchar(40) DEFAULT NULL,
+ContactName varchar(30) DEFAULT NULL,
+ContactTitle varchar(30) DEFAULT NULL,
+Address varchar(60) DEFAULT NULL,
+City varchar(15) DEFAULT NULL,
+Region varchar(15) DEFAULT NULL,
+PostalCode varchar(10) DEFAULT NULL,
+Country varchar(15) NOT NULL,
+Phone varchar(24) DEFAULT NULL,
+Fax varchar(24) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY LIST  COLUMNS(Country)
+(PARTITION p1 VALUES IN ('Germany','Austria','Switzerland','Poland'),
+PARTITION p2 VALUES IN ('USA','Canada','Mexico'),
+PARTITION p3 VALUES IN ('Spain','Portugal','Italy'),
+PARTITION p4 VALUES IN ('UK','Ireland'),
+PARTITION p5 VALUES IN ('France','Belgium'),
+PARTITION p6 VALUES IN ('Sweden','Finland','Denmark','Norway'),
+PARTITION p7 VALUES IN ('Venezuela','Argentina','Brazil')
+);
+INSERT INTO t1 (CustomerID, City, Country) VALUES 
+('ANATR','México D.F','Mexico'),
+('ANTON','México D.F','Mexico'),
+('BOTTM','Tsawassen','Canada'),
+('CENTC','México D.F','Mexico'),
+('GREAL','Eugene','USA'),
+('HUNGC','Elgin','USA'),
+('LAUGB','Vancouver','Canada'),
+('LAZYK','Walla Walla','USA'),
+('LETSS','San Francisco','USA'),
+('LONEP','Portland','USA');
+SELECT * FROM t1 WHERE Country = 'USA';
+CustomerID	CompanyName	ContactName	ContactTitle	Address	City	Region	PostalCode	Country	Phone	Fax
+GREAL	NULL	NULL	NULL	NULL	Eugene	NULL	NULL	USA	NULL	NULL
+HUNGC	NULL	NULL	NULL	NULL	Elgin	NULL	NULL	USA	NULL	NULL
+LAZYK	NULL	NULL	NULL	NULL	Walla Walla	NULL	NULL	USA	NULL	NULL
+LETSS	NULL	NULL	NULL	NULL	San Francisco	NULL	NULL	USA	NULL	NULL
+LONEP	NULL	NULL	NULL	NULL	Portland	NULL	NULL	USA	NULL	NULL
+DROP TABLE t1;
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 30a225cadeb..72cfa84dbe0 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -678,4 +678,32 @@ count(*)
 802
 drop table t3;
 drop table t1,t2;
+#
+# MySQL Bug#71095: Wrong results with PARTITION BY LIST COLUMNS()
+#
+create table t1(c1 int, c2 int, c3 int, c4 int,
+primary key(c1,c2)) engine=InnoDB
+partition by list columns(c2)
+(partition p1 values in (1,2) engine=InnoDB,
+partition p2 values in (3,4) engine=InnoDB);
+insert into t1 values (1,1,1,1),(2,3,1,1);
+select * from t1 where c1=2 and c2=3;
+c1	c2	c3	c4
+2	3	1	1
+drop table t1;
+#
+# MySQL Bug#72803: Wrong "Impossible where" with LIST partitioning
+#  also MDEV-6240: Wrong "Impossible where" with LIST partitioning
+#
+CREATE TABLE t1 ( d DATE) ENGINE = InnoDB
+PARTITION BY LIST COLUMNS (d)
+(
+PARTITION p0 VALUES IN ('1990-01-01','1991-01-01'),
+PARTITION p1 VALUES IN ('1981-01-01')
+);
+INSERT INTO t1 (d) VALUES ('1991-01-01');
+SELECT *  FROM t1 WHERE d = '1991-01-01';
+d
+1991-01-01
+DROP TABLE t1;
 set global default_storage_engine=default;
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index bb67dddf14a..206f9042efb 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -2780,3 +2780,45 @@ select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 5
 select * from t1 IGNORE INDEX(dob, weeks_worked_last_year, hours_worked_per_week) where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21';
 
 drop table t1;
+
+--echo #
+--echo # MDEV-6322: The PARTITION engine can return wrong query results
+--echo #
+CREATE TABLE t1 (
+  CustomerID varchar(5) DEFAULT NULL,
+  CompanyName varchar(40) DEFAULT NULL,
+  ContactName varchar(30) DEFAULT NULL,
+  ContactTitle varchar(30) DEFAULT NULL,
+  Address varchar(60) DEFAULT NULL,
+  City varchar(15) DEFAULT NULL,
+  Region varchar(15) DEFAULT NULL,
+  PostalCode varchar(10) DEFAULT NULL,
+  Country varchar(15) NOT NULL,
+  Phone varchar(24) DEFAULT NULL,
+  Fax varchar(24) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY LIST  COLUMNS(Country)
+(PARTITION p1 VALUES IN ('Germany','Austria','Switzerland','Poland'),
+ PARTITION p2 VALUES IN ('USA','Canada','Mexico'),
+ PARTITION p3 VALUES IN ('Spain','Portugal','Italy'),
+ PARTITION p4 VALUES IN ('UK','Ireland'),
+ PARTITION p5 VALUES IN ('France','Belgium'),
+ PARTITION p6 VALUES IN ('Sweden','Finland','Denmark','Norway'),
+  PARTITION p7 VALUES IN ('Venezuela','Argentina','Brazil')
+);
+
+INSERT INTO t1 (CustomerID, City, Country) VALUES 
+('ANATR','México D.F','Mexico'),
+('ANTON','México D.F','Mexico'),
+('BOTTM','Tsawassen','Canada'),
+('CENTC','México D.F','Mexico'),
+('GREAL','Eugene','USA'),
+('HUNGC','Elgin','USA'),
+('LAUGB','Vancouver','Canada'),
+('LAZYK','Walla Walla','USA'),
+('LETSS','San Francisco','USA'),
+('LONEP','Portland','USA');
+
+SELECT * FROM t1 WHERE Country = 'USA';
+DROP TABLE t1;
+
diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index f6611255eb7..1ef367a3f38 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -763,5 +763,32 @@ drop table t3;
 
 drop table t1,t2;
 
+--echo #
+--echo # MySQL Bug#71095: Wrong results with PARTITION BY LIST COLUMNS()
+--echo #
+create table t1(c1 int, c2 int, c3 int, c4 int,
+primary key(c1,c2)) engine=InnoDB
+partition by list columns(c2)
+(partition p1 values in (1,2) engine=InnoDB,
+partition p2 values in (3,4) engine=InnoDB);
+
+insert into t1 values (1,1,1,1),(2,3,1,1);
+select * from t1 where c1=2 and c2=3;
+drop table t1;
+
+--echo #
+--echo # MySQL Bug#72803: Wrong "Impossible where" with LIST partitioning
+--echo #  also MDEV-6240: Wrong "Impossible where" with LIST partitioning
+--echo #
+CREATE TABLE t1 ( d DATE) ENGINE = InnoDB
+PARTITION BY LIST COLUMNS (d)
+(
+  PARTITION p0 VALUES IN ('1990-01-01','1991-01-01'),
+  PARTITION p1 VALUES IN ('1981-01-01')
+);
+
+INSERT INTO t1 (d) VALUES ('1991-01-01');
+SELECT *  FROM t1 WHERE d = '1991-01-01';
+DROP TABLE t1;
 
 set global default_storage_engine=default;
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index 357bd8efc27..3eff6ee5cf4 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -3261,11 +3261,16 @@ uint32 get_partition_id_cols_list_for_endpoint(partition_info *part_info,
                                            nparts, left_endpoint,
                                            include_endpoint)));
 
-  if (!left_endpoint)
+  if (!left_endpoint && list_index < part_info->num_list_values)
   {
-    /* Set the end after this list tuple if not already after the last. */
-    if (list_index < part_info->num_parts)
-      list_index++;
+    /*
+      Set the end after this list tuple if it is not already after the last
+      and it matches.  ???
+    */
+    int cmp = cmp_rec_and_tuple_prune(list_col_array + list_index*num_columns,
+                                      nparts, left_endpoint, include_endpoint);
+    if (cmp >= 0)
+       list_index++;
   }
 
   DBUG_RETURN(list_index);