Create 'main' test directory and move 't' and 'r' there

1 files changed, 1538 insertions, 0 deletions

diff --git a/mysql-test/main/partition_pruning.test b/mysql-test/main/partition_pruning.test
new file mode 100644
index 00000000000..9d72e9c0d01
--- /dev/null
+++ b/mysql-test/main/partition_pruning.test
@@ -0,0 +1,1538 @@
+#
+# Partition pruning tests. Currently we only detect which partitions to
+# prune, so the test is EXPLAINs.
+#
+-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+--enable_warnings
+
+--echo #
+--echo # Bug#53806: Wrong estimates for range query in partitioned MyISAM table
+--echo # Bug#46754: 'rows' field doesn't reflect partition pruning
+--echo #
+CREATE TABLE t1 (a INT PRIMARY KEY)
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (1),
+PARTITION p1 VALUES LESS THAN (2),
+PARTITION p2 VALUES LESS THAN (3),
+PARTITION p3 VALUES LESS THAN (4),
+PARTITION p4 VALUES LESS THAN (5),
+PARTITION p5 VALUES LESS THAN (6),
+PARTITION max VALUES LESS THAN MAXVALUE);
+
+INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
+
+--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
+--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#49742: Partition Pruning not working correctly for RANGE
+--echo #
+CREATE TABLE t1 (a INT PRIMARY KEY)
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (1),
+PARTITION p1 VALUES LESS THAN (2),
+PARTITION p2 VALUES LESS THAN (3),
+PARTITION p3 VALUES LESS THAN (4),
+PARTITION p4 VALUES LESS THAN (5),
+PARTITION p5 VALUES LESS THAN (6),
+PARTITION max VALUES LESS THAN MAXVALUE);
+
+INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
+
+SELECT * FROM t1 WHERE a < 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1;
+SELECT * FROM t1 WHERE a < 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
+SELECT * FROM t1 WHERE a < 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
+SELECT * FROM t1 WHERE a < 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
+SELECT * FROM t1 WHERE a < 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
+SELECT * FROM t1 WHERE a < 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
+SELECT * FROM t1 WHERE a < 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
+SELECT * FROM t1 WHERE a <= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+SELECT * FROM t1 WHERE a <= 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
+SELECT * FROM t1 WHERE a <= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
+SELECT * FROM t1 WHERE a <= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
+SELECT * FROM t1 WHERE a <= 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
+SELECT * FROM t1 WHERE a <= 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
+SELECT * FROM t1 WHERE a <= 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7;
+SELECT * FROM t1 WHERE a = 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1;
+SELECT * FROM t1 WHERE a = 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2;
+SELECT * FROM t1 WHERE a = 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
+SELECT * FROM t1 WHERE a = 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
+SELECT * FROM t1 WHERE a = 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5;
+SELECT * FROM t1 WHERE a = 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
+SELECT * FROM t1 WHERE a = 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 7;
+SELECT * FROM t1 WHERE a >= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
+SELECT * FROM t1 WHERE a >= 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
+SELECT * FROM t1 WHERE a >= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
+SELECT * FROM t1 WHERE a >= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
+SELECT * FROM t1 WHERE a >= 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
+SELECT * FROM t1 WHERE a >= 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
+SELECT * FROM t1 WHERE a >= 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 7;
+SELECT * FROM t1 WHERE a > 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
+SELECT * FROM t1 WHERE a > 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
+SELECT * FROM t1 WHERE a > 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
+SELECT * FROM t1 WHERE a > 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
+SELECT * FROM t1 WHERE a > 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
+SELECT * FROM t1 WHERE a > 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
+SELECT * FROM t1 WHERE a > 7;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 7;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT PRIMARY KEY)
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (1),
+PARTITION p1 VALUES LESS THAN (2),
+PARTITION p2 VALUES LESS THAN (3),
+PARTITION p3 VALUES LESS THAN (4),
+PARTITION p4 VALUES LESS THAN (5),
+PARTITION max VALUES LESS THAN MAXVALUE);
+
+INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7);
+
+SELECT * FROM t1 WHERE a < 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1;
+SELECT * FROM t1 WHERE a < 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
+SELECT * FROM t1 WHERE a < 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
+SELECT * FROM t1 WHERE a < 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
+SELECT * FROM t1 WHERE a < 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
+SELECT * FROM t1 WHERE a < 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
+SELECT * FROM t1 WHERE a <= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+SELECT * FROM t1 WHERE a <= 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
+SELECT * FROM t1 WHERE a <= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
+SELECT * FROM t1 WHERE a <= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
+SELECT * FROM t1 WHERE a <= 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
+SELECT * FROM t1 WHERE a <= 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
+SELECT * FROM t1 WHERE a = 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1;
+SELECT * FROM t1 WHERE a = 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2;
+SELECT * FROM t1 WHERE a = 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
+SELECT * FROM t1 WHERE a = 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
+SELECT * FROM t1 WHERE a = 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5;
+SELECT * FROM t1 WHERE a = 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
+SELECT * FROM t1 WHERE a >= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
+SELECT * FROM t1 WHERE a >= 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
+SELECT * FROM t1 WHERE a >= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
+SELECT * FROM t1 WHERE a >= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
+SELECT * FROM t1 WHERE a >= 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
+SELECT * FROM t1 WHERE a >= 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
+SELECT * FROM t1 WHERE a > 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
+SELECT * FROM t1 WHERE a > 2;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
+SELECT * FROM t1 WHERE a > 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
+SELECT * FROM t1 WHERE a > 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
+SELECT * FROM t1 WHERE a > 5;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
+SELECT * FROM t1 WHERE a > 6;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
+DROP TABLE t1;
+
+#
+# Bug#20577: Partitions: use of to_days() function leads to selection failures
+#
+--let $explain_partitions= 1;
+--let $verify_without_partitions= 0;
+--echo # test of RANGE and index
+CREATE TABLE t1 (a DATE, KEY(a))
+PARTITION BY RANGE (TO_DAYS(a))
+(PARTITION `pNULL` VALUES LESS THAN (0),
+ PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1),
+ PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1),
+ PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1));
+if ($verify_without_partitions)
+{
+ALTER TABLE t1 REMOVE PARTITIONING;
+}
+INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
+      ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
+--source include/partition_date_range.inc
+--echo # test without index
+ALTER TABLE t1 DROP KEY a;
+--source include/partition_date_range.inc
+DROP TABLE t1;
+
+--echo # test of LIST and index
+CREATE TABLE t1 (a DATE, KEY(a))
+PARTITION BY LIST (TO_DAYS(a))
+(PARTITION `p0001-01-01` VALUES IN (TO_DAYS('0001-01-01')),
+ PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')),
+ PARTITION `pNULL` VALUES IN (NULL),
+ PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')),
+ PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01')));
+if ($verify_without_partitions)
+{
+ALTER TABLE t1 REMOVE PARTITIONING;
+}
+INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
+      ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
+--source include/partition_date_range.inc
+--echo # test without index
+ALTER TABLE t1 DROP KEY a;
+--source include/partition_date_range.inc
+DROP TABLE t1;
+--echo # TO_SECONDS, test of LIST and index
+CREATE TABLE t1 (a DATE, KEY(a))
+PARTITION BY LIST (TO_SECONDS(a))
+(PARTITION `p0001-01-01` VALUES IN (TO_SECONDS('0001-01-01')),
+ PARTITION `p2001-01-01` VALUES IN (TO_SECONDS('2001-01-01')),
+ PARTITION `pNULL` VALUES IN (NULL),
+ PARTITION `p0000-01-02` VALUES IN (TO_SECONDS('0000-01-02')),
+ PARTITION `p1001-01-01` VALUES IN (TO_SECONDS('1001-01-01')));
+if ($verify_without_partitions)
+{
+ALTER TABLE t1 REMOVE PARTITIONING;
+}
+INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
+      ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
+--source include/partition_date_range.inc
+--echo # test without index
+ALTER TABLE t1 DROP KEY a;
+--source include/partition_date_range.inc
+DROP TABLE t1;
+
+
+#
+# Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
+# There is a problem when comparing DATE with DATETIME.
+# In pruning it is converted into the field type
+# and in row evaluation it is converted to longlong
+# (like a DATETIME).
+--echo # Test with DATETIME column NOT NULL
+CREATE TABLE t1 (
+ a int(10) unsigned NOT NULL,
+ b DATETIME NOT NULL,
+ PRIMARY KEY (a, b)
+) PARTITION BY RANGE (TO_DAYS(b))
+(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
+ PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
+ PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
+ PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
+ PARTITION p20090405 VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
+  (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'),
+  (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'),
+  (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07');
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
+DROP TABLE t1;
+
+--echo # Test with DATE column NOT NULL
+CREATE TABLE t1 (
+ a int(10) unsigned NOT NULL,
+ b DATE NOT NULL,
+ PRIMARY KEY (a, b)
+) PARTITION BY RANGE (TO_DAYS(b))
+(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
+ PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
+ PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
+ PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
+ PARTITION p20090405 VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
+  (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'),
+  (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'),
+  (1, '2009-04-07');
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
+DROP TABLE t1;
+
+--echo # Test with DATETIME column NULL
+CREATE TABLE t1 (
+ a int(10) unsigned NOT NULL,
+ b DATETIME NULL
+) PARTITION BY RANGE (TO_DAYS(b))
+(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
+ PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
+ PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
+ PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
+ PARTITION p20090405 VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
+  (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'),
+  (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'),
+  (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07');
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
+DROP TABLE t1;
+
+--echo # Test with DATE column NULL
+CREATE TABLE t1 (
+ a int(10) unsigned NOT NULL,
+ b DATE NULL
+) PARTITION BY RANGE (TO_DAYS(b))
+(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
+ PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
+ PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
+ PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
+ PARTITION p20090405 VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
+  (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'),
+  (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'),
+  (1, '2009-04-07');
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
+EXPLAIN PARTITIONS SELECT * FROM t1
+  WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
+DROP TABLE t1;
+
+--echo # For better code coverage of the patch
+CREATE TABLE t1 (
+ a int(10) unsigned NOT NULL,
+ b DATE
+) PARTITION BY RANGE ( TO_DAYS(b) )
+(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
+ PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
+ PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
+ PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
+ PARTITION p20090405 VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL);
+--echo # test with an invalid date, which lead to item->null_value is set.
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME);
+DROP TABLE t1;
+
+#
+# Bug#40972: some sql execution lead the whole database crashing
+#
+# Setup so the start is at partition pX and end is at p1
+# Pruning does handle 'bad' dates differently.
+CREATE TABLE t1
+(a INT NOT NULL AUTO_INCREMENT,
+ b DATETIME,
+ PRIMARY KEY (a,b),
+ KEY (b))
+PARTITION BY RANGE (to_days(b))
+(PARTITION p0 VALUES LESS THAN (733681) COMMENT = 'LESS THAN 2008-10-01',
+ PARTITION p1 VALUES LESS THAN (733712) COMMENT = 'LESS THAN 2008-11-01',
+ PARTITION pX VALUES LESS THAN MAXVALUE);
+SELECT a,b FROM t1 WHERE b >= '2008-12-01' AND b < '2009-12-00';
+DROP TABLE t1;
+
+# Check if we can infer from condition on partition fields that 
+# no records will match.
+create table t1 ( a int not null) partition by hash(a) partitions 2;
+insert into t1 values (1),(2),(3);
+explain select * from t1 where a=5 and a=6;
+drop table t1;
+
+# Simple HASH partitioning
+create table t1 (
+  a int(11) not null
+) partition by hash (a) partitions 2;
+insert into t1 values (1),(2),(3);
+
+explain partitions select * from t1 where a=1;
+explain partitions select * from t1 where a=2;
+explain partitions select * from t1 where a=1 or a=2;
+
+# Partitioning over several fields
+create table t2 (
+  a int not null,
+  b int not null
+) partition by key(a,b) partitions 2;
+insert into t2 values (1,1),(2,2),(3,3);
+
+explain partitions select * from t2 where a=1;
+explain partitions select * from t2 where b=1;
+
+explain partitions select * from t2 where a=1 and b=1;
+
+# RANGE(expr) partitioning 
+create table t3 (
+  a int
+)
+partition by range (a*1) ( 
+  partition p0 values less than (10),
+  partition p1 values less than (20)
+);
+insert into t3 values (5),(15);
+
+explain partitions select * from t3 where a=11;
+explain partitions select * from t3 where a=10;
+explain partitions select * from t3 where a=20;
+explain partitions select * from t3 where a=30;
+
+# LIST(expr) partitioning
+create table t4 (a int not null, b int not null) partition by LIST (a+b) (
+  partition p0 values in (12),
+  partition p1 values in (14)
+);
+insert into t4 values (10,2), (10,4);
+
+# empty OR one
+explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2);
+
+# empty OR one OR empty
+explain partitions select * from t4 
+  where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
+
+# one OR empty OR one
+explain partitions select * from t4 where (a=10 and b=2) or (a=10 and b=3)
+  or (a=10 and b = 4);
+
+# empty OR full
+explain partitions select * from t4 where (a=10 and b=1) or a=11;
+
+# one OR full
+explain partitions select * from t4 where (a=10 and b=2) or a=11;
+
+drop table t1, t2, t3, t4;
+
+# LIST(expr)/HASH subpartitioning.
+create table t5 (a int not null, b int not null, 
+                 c int not null, d int not null) 
+partition by LIST(a+b) subpartition by HASH (c+d) subpartitions 2
+(
+  partition p0 values in (12),
+  partition p1 values in (14)
+);
+ 
+insert into t5 values (10,2,0,0), (10,4,0,0), (10,2,0,1), (10,4,0,1);
+explain partitions select * from t5;
+
+# empty OR one OR empty
+explain partitions select * from t5
+  where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
+
+# one OR empty OR one
+explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3)
+  or (a=10 and b = 4);
+
+# conditions on subpartitions only
+explain partitions select * from t5 where (c=1 and d=1);
+explain partitions select * from t5 where (c=2 and d=1);
+
+# mixed partition/subpartitions.
+explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or 
+(c=2 and d=1);
+
+# same as above
+explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or 
+(b=2 and c=2 and d=1);
+
+# LIST(field) partitioning, interval analysis.
+create table t6 (a int not null) partition by LIST(a) (
+  partition p1 values in (1),
+  partition p3 values in (3),
+  partition p5 values in (5),
+  partition p7 values in (7),
+  partition p9 values in (9)
+);
+insert into t6 values (1),(3),(5);
+
+explain partitions select * from t6 where a <  1;
+explain partitions select * from t6 where a <= 1;
+explain partitions select * from t6 where a >  9;
+explain partitions select * from t6 where a >= 9;
+
+explain partitions select * from t6 where a > 0 and a < 5;
+explain partitions select * from t6 where a > 5 and a < 12;
+explain partitions select * from t6 where a > 3 and a < 8 ;
+
+explain partitions select * from t6 where a >= 0 and a <= 5;
+explain partitions select * from t6 where a >= 5 and a <= 12;
+explain partitions select * from t6 where a >= 3 and a <= 8;
+
+explain partitions select * from t6 where a > 3 and a < 5;
+
+drop table t6;
+
+create table t6 (a int unsigned not null) partition by LIST(a) (
+  partition p1 values in (1),
+  partition p3 values in (3),
+  partition p5 values in (5),
+  partition p7 values in (7),
+  partition p9 values in (9)
+);
+insert into t6 values (1),(3),(5);
+
+explain partitions select * from t6 where a <  1;
+explain partitions select * from t6 where a <= 1;
+explain partitions select * from t6 where a >  9;
+explain partitions select * from t6 where a >= 9;
+
+explain partitions select * from t6 where a > 0 and a < 5;
+explain partitions select * from t6 where a > 5 and a < 12;
+explain partitions select * from t6 where a > 3 and a < 8 ;
+
+explain partitions select * from t6 where a >= 0 and a <= 5;
+explain partitions select * from t6 where a >= 5 and a <= 12;
+explain partitions select * from t6 where a >= 3 and a <= 8;
+
+explain partitions select * from t6 where a > 3 and a < 5;
+
+# RANGE(field) partitioning, interval analysis.
+create table t7 (a int not null) partition by RANGE(a) (
+  partition p10 values less than (10),
+  partition p30 values less than (30),
+  partition p50 values less than (50),
+  partition p70 values less than (70),
+  partition p90 values less than (90)
+);
+insert into t7 values (10),(30),(50);
+
+# leftmost intervals
+explain partitions select * from t7 where a < 5;
+explain partitions select * from t7 where a < 9;
+explain partitions select * from t7 where a <= 9;
+explain partitions select * from t7 where a = 9;
+explain partitions select * from t7 where a >= 9;
+explain partitions select * from t7 where a > 9;
+explain partitions select * from t7 where a < 10;
+explain partitions select * from t7 where a <= 10;
+explain partitions select * from t7 where a = 10;
+explain partitions select * from t7 where a >= 10;
+explain partitions select * from t7 where a > 10;
+
+#rightmost intervals
+explain partitions select * from t7 where a < 89;
+explain partitions select * from t7 where a <= 89;
+explain partitions select * from t7 where a = 89;
+explain partitions select * from t7 where a > 89;
+explain partitions select * from t7 where a >= 89;
+explain partitions select * from t7 where a < 90;
+explain partitions select * from t7 where a <= 90;
+explain partitions select * from t7 where a = 90;
+explain partitions select * from t7 where a > 90;
+explain partitions select * from t7 where a >= 90;
+explain partitions select * from t7 where a > 91;
+
+# misc intervals
+explain partitions select * from t7 where a > 11 and a < 29;
+
+drop table t7;
+
+create table t7 (a int unsigned not null) partition by RANGE(a) (
+  partition p10 values less than (10),
+  partition p30 values less than (30),
+  partition p50 values less than (50),
+  partition p70 values less than (70),
+  partition p90 values less than (90)
+);
+insert into t7 values (10),(30),(50);
+
+# leftmost intervals
+explain partitions select * from t7 where a < 5;
+explain partitions select * from t7 where a < 9;
+explain partitions select * from t7 where a <= 9;
+explain partitions select * from t7 where a = 9;
+explain partitions select * from t7 where a >= 9;
+explain partitions select * from t7 where a > 9;
+explain partitions select * from t7 where a < 10;
+explain partitions select * from t7 where a <= 10;
+explain partitions select * from t7 where a = 10;
+explain partitions select * from t7 where a >= 10;
+explain partitions select * from t7 where a > 10;
+
+#rightmost intervals
+explain partitions select * from t7 where a < 89;
+explain partitions select * from t7 where a <= 89;
+explain partitions select * from t7 where a = 89;
+explain partitions select * from t7 where a > 89;
+explain partitions select * from t7 where a >= 89;
+explain partitions select * from t7 where a < 90;
+explain partitions select * from t7 where a <= 90;
+explain partitions select * from t7 where a = 90;
+explain partitions select * from t7 where a > 90;
+explain partitions select * from t7 where a >= 90;
+explain partitions select * from t7 where a > 91;
+
+# misc intervals
+explain partitions select * from t7 where a > 11 and a < 29;
+
+# LIST(monontonic_func) partitioning
+create table t8 (a date not null) partition by RANGE(YEAR(a)) (
+  partition p0 values less than (1980),
+  partition p1 values less than (1990),
+  partition p2 values less than (2000)
+);
+insert into t8 values ('1985-05-05'),('1995-05-05');
+
+explain partitions select * from t8 where a < '1980-02-02';
+
+# LIST(strict_monotonic_func) partitioning
+create table t9 (a date not null) partition by RANGE(TO_DAYS(a)) (
+  partition p0 values less than (732299), -- 2004-12-19
+  partition p1 values less than (732468), -- 2005-06-06
+  partition p2 values less than (732664)  -- 2005-12-19
+);
+insert into t9 values ('2005-05-05'), ('2005-04-04');
+
+explain partitions select * from t9 where a <  '2004-12-19';
+explain partitions select * from t9 where a <= '2004-12-19';
+
+drop table t5,t6,t7,t8,t9;
+
+# Test the case where we can't create partitioning 'index'
+#
+# Not supported after bug#18198 is fixed
+#
+#create table t1 (a enum('a','b','c','d') default 'a') 
+#  partition by hash (ascii(a)) partitions 2;
+#insert into t1 values ('a'),('b'),('c');
+#explain partitions select * from t1 where a='b';
+#drop table t1;
+
+#
+# Test cases for bugs found in code review:
+#
+create table t1 (
+  a1 int not null
+)
+partition by range (a1) (
+  partition p0 values less than (3),
+  partition p1 values less than (6),
+  partition p2 values less than (9)
+);
+insert into t1 values (1),(2),(3);
+explain partitions select * from t1 where a1 > 3;
+explain partitions select * from t1 where a1 >= 3;
+
+explain partitions select * from t1 where a1 < 3 and a1 > 3;
+drop table t1;
+
+#
+create table t3 (a int, b int) 
+  partition by list(a) subpartition by hash(b) subpartitions 4 (
+    partition p0 values in (1),
+    partition p1 values in (2),
+    partition p2 values in (3),
+    partition p3 values in (4)
+  );
+insert into t3 values (1,1),(2,2),(3,3);
+
+explain partitions select * from t3 where a=2 or b=1;
+explain partitions select * from t3 where a=4 or b=2;
+explain partitions select * from t3 where (a=2 or b=1) and (a=4 or b=2) ;
+drop table t3;
+
+# Test for NULLs
+create table t1 (a int) partition by hash(a) partitions 2;
+insert into t1 values (1),(2);
+explain partitions select * from t1 where a is null;
+
+# this uses both partitions
+explain partitions select * from t1 where a is not null;
+drop table t1;
+
+# Join tests
+create table t1 (a int not null, b int not null, key(a), key(b))
+  partition by hash(a) partitions 4;
+insert into t1 values (1,1),(2,2),(3,3),(4,4);
+insert into t1 values (5,5),(6,6),(7,7),(8,8);
+
+explain partitions 
+select * from t1 X, t1 Y 
+where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3);
+
+explain partitions
+select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2);
+
+drop table t1;
+
+# Tests for "short ranges"
+create table t1 (a int) partition by hash(a) partitions 20;
+insert into t1 values (1),(2),(3);
+explain partitions select * from t1 where a >  1 and a < 3;
+explain partitions select * from t1 where a >= 1 and a < 3;
+explain partitions select * from t1 where a >  1 and a <= 3;
+explain partitions select * from t1 where a >= 1 and a <= 3;
+drop table t1;
+
+create table t1 (a int, b int) 
+ partition by list(a) subpartition by hash(b) subpartitions 20 
+(
+  partition p0 values in (0),
+  partition p1 values in (1),
+  partition p2 values in (2),
+  partition p3 values in (3)
+);
+insert into t1 values (1,1),(2,2),(3,3);
+
+explain partitions select * from t1 where b >  1 and b < 3;
+explain partitions select * from t1 where b >  1 and b < 3 and (a =1 or a =2);
+drop table t1;
+
+# Test partition pruning for single-table UPDATE/DELETE.
+# TODO: Currently we test only "all partitions pruned away" case. Add more
+# tests when the patch that makes use of partition pruning results at
+# execution phase is pushed.
+
+create table t1 (a int) partition by list(a) (
+  partition p0 values in (1,2),
+  partition p1 values in (3,4)
+);
+insert into t1 values (1),(1),(2),(2),(3),(4),(3),(4);
+
+# This won't do any table access
+flush status;
+update t1 set a=100 where a=5;
+show status like 'Handler_read_rnd_next';
+
+# ... as compared to this, which will scan both partitions
+flush status;
+update t1 set a=100 where a+1=5+1;
+show status like 'Handler_read_rnd_next';
+
+# Same as above for DELETE:
+flush status;
+delete from t1 where a=5;
+show status like 'Handler_read_rnd_next';
+
+flush status;
+delete from t1 where a+1=5+1;
+show status like 'Handler_read_rnd_next';
+
+# Same as above multi-table UPDATE/DELETE
+create table t2 like t1;
+insert into t2 select * from t2;
+
+flush status;
+update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5;
+show status like 'Handler_read_rnd_next';
+# ^ This shows 3 accesses, these are caused by const table reads. 
+# They should vanish when partition pruning results are used.
+
+flush status;
+delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5;
+show status like 'Handler_read_rnd_next';
+drop table t1,t2;
+
+#
+# WL#2986 Tests (Checking if partition pruning results are used at query
+#   execution phase)
+#
+CREATE TABLE `t1` (
+  `a` int(11) default NULL
+);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+CREATE TABLE `t2` (
+  `a` int(11) default NULL,
+  KEY `a` (`a`)
+) ;
+
+insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ;
+insert into t1 select a from t2;
+
+drop table t2;
+CREATE TABLE `t2` (
+  `a` int(11) default NULL,
+  `b` int(11) default NULL
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+
+insert into t2 select a,1 from t1 where a < 200;
+insert into t2 select a,2 from t1 where a >= 200 and a < 400;
+insert into t2 select a,3 from t1 where a >= 400 and a < 600;
+insert into t2 select a,4 from t1 where a >= 600 and a < 800;
+insert into t2 select a,5 from t1 where a >= 800 and a < 1001;
+
+explain partitions select * from t2;
+explain partitions select * from t2 where a < 801 and a > 200;
+explain partitions select * from t2 where a < 801 and a > 800;
+explain partitions select * from t2 where a > 600;
+explain partitions select * from t2 where a > 600 and b = 1;
+explain partitions select * from t2 where a > 600 and b = 4;
+explain partitions select * from t2 where a > 600 and b = 5;
+explain partitions select * from t2 where b = 5;
+
+flush status;
+update t2 set b = 100 where b = 6;
+show status like 'Handler_read_rnd_next';
+flush status;
+update t2 set a = 1002 where a = 1001;
+show status like 'Handler_read_rnd_next';
+flush status;
+update t2 set b = 6 where a = 600;
+show status like 'Handler_read_rnd_next';
+flush status;
+update t2 set b = 6 where a > 600 and a < 800;
+show status like 'Handler_read_rnd_next';
+flush status;
+delete from t2 where a > 600;
+show status like 'Handler_read_rnd_next';
+
+drop table t2;
+CREATE TABLE `t2` (
+  `a` int(11) default NULL,
+  `b` int(11) default NULL,
+  index (b)
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+
+insert into t2 select a,1 from t1 where a < 100;
+insert into t2 select a,2 from t1 where a >= 200 and a < 300;
+insert into t2 select a,3 from t1 where a >= 300 and a < 400;
+insert into t2 select a,4 from t1 where a >= 400 and a < 500;
+insert into t2 select a,5 from t1 where a >= 500 and a < 600;
+insert into t2 select a,6 from t1 where a >= 600 and a < 700;
+insert into t2 select a,7 from t1 where a >= 700 and a < 800;
+insert into t2 select a,8 from t1 where a >= 800 and a < 900;
+insert into t2 select a,9 from t1 where a >= 900 and a < 1001;
+
+explain partitions select * from t2;
+# not using indexes
+explain partitions select * from t2 where a = 101;
+explain partitions select * from t2 where a = 550;
+explain partitions select * from t2 where a = 833;
+explain partitions select * from t2 where (a = 100 OR a = 900);
+explain partitions select * from t2 where (a > 100 AND a < 600);
+explain partitions select * from t2 where b = 4;
+
+explain extended select * from t2 where b = 6;
+explain partitions select * from t2 where b = 6;
+
+explain extended select * from t2 where b in (1,3,5);
+explain partitions select * from t2 where b in (1,3,5);
+
+explain extended select * from t2 where b in (2,4,6);
+explain partitions select * from t2 where b in (2,4,6);
+
+explain extended select * from t2 where b in (7,8,9);
+explain partitions select * from t2 where b in (7,8,9);
+
+explain extended select * from t2 where b > 5;
+explain partitions select * from t2 where b > 5;
+
+explain extended select * from t2 where b > 5 and b < 8;
+explain partitions select * from t2 where b > 5 and b < 8;
+
+explain extended select * from t2 where b > 5 and b < 7;
+explain partitions select * from t2 where b > 5 and b < 7;
+
+explain extended select * from t2 where b > 0 and b < 5;
+explain partitions select * from t2 where b > 0 and b < 5;
+
+flush status;
+update t2 set a = 111 where b = 10;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+update t2 set a = 111 where b in (5,6);
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+update t2 set a = 222 where b = 7;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+delete from t2 where b = 7;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+delete from t2 where b > 5;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_rnd_deleted';
+show status like 'Handler_read_key';
+show status like 'Handler_read_prev';
+show status like 'Handler_read_next';
+flush status;
+delete from t2 where b < 5 or b > 3;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_rnd_deleted';
+show status like 'Handler_read_key';
+show status like 'Handler_read_prev';
+show status like 'Handler_read_next';
+
+drop table t1, t2;
+
+# BUG#18025 
+#  part1: mediumint columns
+create table t1 ( f_int1 mediumint, f_int2 integer) 
+partition by list(mod(f_int1,4)) (
+  partition p_3 values in (-3),
+  partition p_2 values in (-2),
+  partition p_1 values in (-1),
+  partition p0 values in (0),
+  partition p1 values in (1),
+  partition p2 values in (2),
+  partition p3 values in (3)
+);
+
+insert into t1 values (9, 9), (8, 8), (7, 7), (6, 6), (5, 5), 
+                      (4, 4), (3, 3), (2, 2), (1, 1);
+select * from t1 where f_int1 between 5 and 15 order by f_int1;
+
+drop table t1;
+
+#  part2: bug in pruning code
+#create table t1 (a char(10) binary)
+#partition by list(ascii(a))
+# (partition p1 values in (ascii('a')),
+#  partition p2 values in (ascii('b')),
+#  partition p3 values in (ascii('c')),
+#  partition p4 values in (ascii('d')),
+#  partition p5 values in (ascii('e')));
+#insert into t1 values ('a'),('bb'),('ccc'),('dddd'),('eeEee');
+#select * from t1 where a>='a' and a <= 'dddd';
+#explain partitions select * from t1 where a>='a' and a <= 'dddd';
+#drop table t1;
+
+# BUG#18659: Assertion failure when subpartitioning is used and partition is
+# "IS NULL"
+create table t1 (f_int1 integer) partition by list(abs(mod(f_int1,2)))
+  subpartition by hash(f_int1) subpartitions 2
+(
+  partition part1 values in (0),
+  partition part2 values in (1),
+  partition part4 values in (null)
+);
+insert into t1 set f_int1 = null;
+
+select * from t1 where f_int1 is null;
+explain partitions select * from t1 where f_int1 is null;
+drop table t1;
+
+#
+# BUG#18558
+#
+create table t1 (a int not null, b int not null)
+partition by list(a) 
+  subpartition by hash(b) subpartitions 4 
+(
+  partition p0 values in (1),
+  partition p1 values in (2),
+  partition p2 values in (3)
+);
+insert into t1 values (1,1),(1,2),(1,3),(1,4),
+  (2,1),(2,2),(2,3),(2,4);
+explain partitions select * from t1 where a=1 AND (b=1 OR b=2);
+drop table t1;
+
+create table t1 (a int, b int not null)
+partition by list(a) 
+  subpartition by hash(b) subpartitions 2
+(
+  partition p0 values in (1),
+  partition p1 values in (2),
+  partition p2 values in (3),
+  partition pn values in (NULL)
+);
+insert into t1 values (1,1),(1,2),(1,3),(1,4),
+  (2,1),(2,2),(2,3),(2,4), (NULL,1);
+
+explain partitions select * from t1 where a IS NULL AND (b=1 OR b=2);
+
+explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2);
+explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2);
+explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2);
+
+drop table t1;
+
+create table t1 ( a int)  partition by list (MOD(a, 10))
+( partition p0  values in (0), partition p1 values in (1),
+   partition p2 values in (2), partition p3 values in (3),
+   partition p4 values in (4), partition p5 values in (5),
+   partition p6 values in (6), partition pn values in (NULL)
+);
+insert into t1 values (NULL), (0),(1),(2),(3),(4),(5),(6);
+explain partitions select * from t1 where a is null or a < 2;
+drop table t1;
+
+# Testcase from BUG#18751
+create table t1 (s1 int) partition by list (s1) 
+  (partition p1 values in (0), 
+   partition p2 values in (1), 
+   partition p3 values in (null));
+ 
+insert into t1 values (0),(1),(null);
+ 
+select count(*) from t1 where s1 < 0 or s1 is null;
+explain partitions select count(*) from t1 where s1 < 0 or s1 is null;
+drop table t1;
+
+# No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447
+# being fixed.
+
+#
+# BUG#17946 Like searches fail with partitioning
+#
+create table t1 (a char(32) primary key)
+partition by key()
+partitions 100;
+insert into t1 values ('na');
+select * from t1;
+select * from t1 where a like 'n%';
+drop table t1;
+
+
+# BUG#19055 Crashes for varchar_col=NUMBER or varchar_col IS NULL
+create table t1 (s1 varchar(15)) partition by key (s1);
+select * from t1 where s1 = 0 or s1 is null;
+insert into t1 values ('aa'),('bb'),('0');
+explain partitions select * from t1 where s1 = 0 or s1 is null;
+drop table t1;
+
+#
+# BUG#19684: EXPLAIN PARTITIONS produces garbage in 'partitions' column when
+# the length of string to be displayed exceeds some limit.
+create table t2 (a int, b int)
+  partition by LIST(a)
+  subpartition by HASH(b) subpartitions 40
+( partition p_0_long_partition_name values in(1),
+  partition p_1_long_partition_name values in(2));
+
+insert into t2 values (1,1),(2,2);
+
+--vertical_results
+explain partitions select * from t2;
+--horizontal_results
+drop table t2;
+
+
+# BUG#20484 "Partitions: crash with explain and union"
+create table t1 (s1 int);
+explain partitions select 1 from t1 union all select 2;
+drop table t1;
+
+
+# BUG#20257: partition pruning test coverage for BIGINT UNSIGNED
+create table t1 (a bigint unsigned not null) partition by range(a) (
+  partition p0 values less than (10),
+  partition p1 values less than (100),
+  partition p2 values less than (1000),
+  partition p3 values less than (18446744073709551000),
+  partition p4 values less than (18446744073709551614)
+);
+insert into t1 values (5),(15),(105),(1005);
+insert into t1 values (18446744073709551000+1);
+insert into t1 values (18446744073709551614-1);
+
+explain partitions select * from t1 where a < 10;
+explain partitions select * from t1 
+  where a >= 18446744073709551000-1 and a <= 18446744073709551000+1;
+
+explain partitions select * from t1 
+  where a between 18446744073709551001 and 18446744073709551002;
+
+explain partitions select * from t1 where a = 18446744073709551000;
+explain partitions select * from t1 where a = 18446744073709551613;
+explain partitions select * from t1 where a = 18446744073709551614;
+drop table t1;
+#
+# Test all variants of usage for interval_via_mapping
+# and interval_via_walking
+#
+# t1 will use interval_via_mapping since it uses a
+# monotonic function, whereas t2 will use
+# interval_via_walking since the intervals are short
+# and the function isn't monotonic (it is, but it isn't
+# discovered in this version).
+#
+  create table t1 (a int)
+  partition by range(a) (
+  partition p0 values less than (64),
+  partition p1 values less than (128),
+  partition p2 values less than (255)
+);
+
+create table t2 (a int)
+  partition by range(a+0) (
+  partition p0 values less than (64),
+  partition p1 values less than (128),
+  partition p2 values less than (255)
+);
+  
+insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
+insert into t2 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
+explain partitions select * from t1 where a=0;
+explain partitions select * from t2 where a=0;
+explain partitions select * from t1 where a=0xFE;
+explain partitions select * from t2 where a=0xFE;
+explain partitions select * from t1 where a > 0xFE AND a <= 0xFF;
+explain partitions select * from t2 where a > 0xFE AND a <= 0xFF;
+explain partitions select * from t1 where a >= 0xFE AND a <= 0xFF;
+explain partitions select * from t2 where a >= 0xFE AND a <= 0xFF;
+explain partitions select * from t1 where a < 64 AND a >= 63;
+explain partitions select * from t2 where a < 64 AND a >= 63;
+explain partitions select * from t1 where a <= 64 AND a >= 63;
+explain partitions select * from t2 where a <= 64 AND a >= 63;
+drop table t1;
+drop table t2;
+  
+create table t1(a bigint unsigned not null) partition by range(a+0) (
+  partition p1 values less than (10),
+  partition p2 values less than (20),
+  partition p3 values less than (2305561538531885056),
+  partition p4 values less than (2305561538531950591)
+);
+
+insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
+insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
+
+explain partitions select * from t1 where 
+  a >= 2305561538531885056-10 and a <= 2305561538531885056-8;
+
+explain partitions select * from t1 where 
+  a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE;
+
+explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF;
+drop table t1;
+
+create table t1 (a bigint) partition by range(a+0) (
+  partition p1 values less than (-1000),
+  partition p2 values less than (-10),
+  partition p3 values less than (10),
+  partition p4 values less than (1000)
+);
+insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15);
+explain partitions select * from t1 where a>-2 and a <=0;
+drop table t1;
+
+
+#
+# BUG#27927 Partition pruning not optimal with TO_DAYS function
+# 
+
+CREATE TABLE t1 ( recdate  DATETIME NOT NULL )
+PARTITION BY RANGE( TO_DAYS(recdate) ) (
+  PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ),
+  PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') )
+);
+INSERT INTO t1 VALUES ('2007-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2007-03-07 12:00:00');
+INSERT INTO t1 VALUES ('2007-03-08 12:00:00');
+INSERT INTO t1 VALUES ('2007-03-15 12:00:00');
+-- echo must use p0 only:
+explain partitions select * from t1 where recdate < '2007-03-08 00:00:00';
+
+drop table t1;
+CREATE TABLE t1 ( recdate  DATETIME NOT NULL )
+PARTITION BY RANGE( YEAR(recdate) ) (
+  PARTITION p0 VALUES LESS THAN (2006),
+  PARTITION p1 VALUES LESS THAN (2007)
+);
+INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
+
+-- echo must use p0 only:
+explain partitions select * from t1 where recdate < '2006-01-01 00:00:00';
+drop table t1;
+
+-- echo # 
+-- echo # BUG#33730 Full table scan instead selected partitions for query more than 10 partitions
+-- echo # 
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int)
+  partition by range(a+0) (
+  partition p0 values less than (64),
+  partition p1 values less than (128),
+  partition p2 values less than (255)
+);
+insert into t1 select A.a + 10*B.a from t0 A, t0 B;
+
+# this will use interval_via_walking
+explain partitions select * from t1 where a between 10 and 13;
+explain partitions select * from t1 where a between 10 and 10+33;
+
+drop table t0, t1;
+
+--echo #
+--echo # 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))
+PARTITION BY LIST COLUMNS (c2)
+(PARTITION p1 VALUES IN (1,2),
+ PARTITION p2 VALUES IN (3,4));
+INSERT INTO t1 VALUES (1, 1, 1, 1), (2, 3, 1, 1);
+INSERT INTO t1 VALUES (1, 2, 1, 1), (2, 4, 1, 1);
+SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-6239: Partition pruning is not working as expected in an inner query
+--echo #
+
+create table t1
+(
+  company_id bigint(5),
+  dept_id bigint(5),
+  emp_id bigint(5),
+  emp_name varchar(100),
+  primary key (company_id, emp_id)
+) partition by list (company_id) (
+	partition p_1000 values in (1000),
+	partition p_2000 values in (2000),
+	partition p_3000 values in (3000)
+);
+
+create table t2
+(
+  company_id bigint(5),
+  dept_id bigint(5),
+  dept_name varchar(100),
+  primary key (company_id, dept_id)
+) partition by list (company_id) (
+	partition p_1000 values in (1000),
+	partition p_2000 values in (2000),
+	partition p_3000 values in (3000)
+);
+
+insert into t2 values 
+  (1000, 10, 'Engineering'),
+  (1000, 20, 'Product Management'),
+  (1000, 30, 'QA'),
+  (2000, 40, 'Support'),
+  (2000, 50, 'Professional Services');
+
+insert into t1 values 
+(1000, 10, 1, 'John'),
+(1000, 10, 2, 'Smith'),
+(1000, 20, 3, 'Jacob'),
+(1000, 20, 4, 'Brian'),
+(1000, 30, 5, 'Chris'),
+(1000, 30, 6, 'Ryan'),
+(2000, 40, 7, 'Karin'),
+(2000, 40, 8, 'Jay'),
+(2000, 50, 9, 'Ana'),
+(2000, 50, 10, 'Jessica');
+
+--echo # Table t2 should have only partition p_1000.
+explain partitions
+select * from t1
+where company_id = 1000
+and dept_id in (select dept_id from t2 where COMPANY_ID = 1000);
+
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-9505: Valgrind failure in SEL_ARG::store_min,find_used_partitions,...
+--echo #
+create table t1 (a int, b char(10), c varchar(5), d int)
+partition by range columns(a,b,c)
+subpartition by key (c,d)
+subpartitions 3
+( partition p0 values less than (1,'abc','abc'),
+  partition p1 values less than (2,'abc','abc'),
+  partition p2 values less than (3,'abc','abc'),
+  partition p3 values less than (4,'abc','abc')
+);
+
+insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3);
+select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR
+                       (a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2))));
+drop table t1;
+
+