You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 

1017 lines
38 KiB

set @@session.tidb_enable_table_partition=1;
#
# 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 SELECT * FROM t1 WHERE a <= 1;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
EXPLAIN SELECT * FROM t1 WHERE a < 7;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
EXPLAIN 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 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 1;
SELECT * FROM t1 WHERE a < 2 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 2;
SELECT * FROM t1 WHERE a < 3 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 3;
SELECT * FROM t1 WHERE a < 4 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 4;
SELECT * FROM t1 WHERE a < 5 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 5;
SELECT * FROM t1 WHERE a < 6 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 6;
SELECT * FROM t1 WHERE a < 7 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 7;
SELECT * FROM t1 WHERE a <= 1 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 1;
SELECT * FROM t1 WHERE a <= 2 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 2;
SELECT * FROM t1 WHERE a <= 3 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 3;
SELECT * FROM t1 WHERE a <= 4 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 4;
SELECT * FROM t1 WHERE a <= 5 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 5;
SELECT * FROM t1 WHERE a <= 6 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 6;
SELECT * FROM t1 WHERE a <= 7 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 7;
SELECT * FROM t1 WHERE a = 1 order by a;
EXPLAIN SELECT * FROM t1 WHERE a = 1;
SELECT * FROM t1 WHERE a = 2 order by a;
EXPLAIN SELECT * FROM t1 WHERE a = 2;
SELECT * FROM t1 WHERE a = 3 order by a;
EXPLAIN SELECT * FROM t1 WHERE a = 3;
SELECT * FROM t1 WHERE a = 4 order by a;
EXPLAIN SELECT * FROM t1 WHERE a = 4;
SELECT * FROM t1 WHERE a = 5 order by a;
EXPLAIN SELECT * FROM t1 WHERE a = 5;
SELECT * FROM t1 WHERE a = 6 order by a;
EXPLAIN SELECT * FROM t1 WHERE a = 6;
SELECT * FROM t1 WHERE a = 7 order by a;
EXPLAIN SELECT * FROM t1 WHERE a = 7;
SELECT * FROM t1 WHERE a >= 1 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 1;
SELECT * FROM t1 WHERE a >= 2 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 2;
SELECT * FROM t1 WHERE a >= 3 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 3;
SELECT * FROM t1 WHERE a >= 4 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 4;
SELECT * FROM t1 WHERE a >= 5 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 5;
SELECT * FROM t1 WHERE a >= 6 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 6;
SELECT * FROM t1 WHERE a >= 7 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 7;
SELECT * FROM t1 WHERE a > 1 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 1;
SELECT * FROM t1 WHERE a > 2 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 2;
SELECT * FROM t1 WHERE a > 3 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 3;
SELECT * FROM t1 WHERE a > 4 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 4;
SELECT * FROM t1 WHERE a > 5 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 5;
SELECT * FROM t1 WHERE a > 6 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 6;
SELECT * FROM t1 WHERE a > 7 order by a;
EXPLAIN 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 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 1;
SELECT * FROM t1 WHERE a < 2 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 2;
SELECT * FROM t1 WHERE a < 3 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 3;
SELECT * FROM t1 WHERE a < 4 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 4;
SELECT * FROM t1 WHERE a < 5 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 5;
SELECT * FROM t1 WHERE a < 6 order by a;
EXPLAIN SELECT * FROM t1 WHERE a < 6;
SELECT * FROM t1 WHERE a <= 1 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 1;
SELECT * FROM t1 WHERE a <= 2 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 2;
SELECT * FROM t1 WHERE a <= 3 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 3;
SELECT * FROM t1 WHERE a <= 4 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 4;
SELECT * FROM t1 WHERE a <= 5 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 5;
SELECT * FROM t1 WHERE a <= 6 order by a;
EXPLAIN SELECT * FROM t1 WHERE a <= 6;
SELECT * FROM t1 WHERE a = 1;
EXPLAIN SELECT * FROM t1 WHERE a = 1;
SELECT * FROM t1 WHERE a = 2;
EXPLAIN SELECT * FROM t1 WHERE a = 2;
SELECT * FROM t1 WHERE a = 3;
EXPLAIN SELECT * FROM t1 WHERE a = 3;
SELECT * FROM t1 WHERE a = 4;
EXPLAIN SELECT * FROM t1 WHERE a = 4;
SELECT * FROM t1 WHERE a = 5;
EXPLAIN SELECT * FROM t1 WHERE a = 5;
SELECT * FROM t1 WHERE a = 6;
EXPLAIN SELECT * FROM t1 WHERE a = 6;
SELECT * FROM t1 WHERE a >= 1 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 1;
SELECT * FROM t1 WHERE a >= 2 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 2;
SELECT * FROM t1 WHERE a >= 3 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 3;
SELECT * FROM t1 WHERE a >= 4 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 4;
SELECT * FROM t1 WHERE a >= 5 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 5;
SELECT * FROM t1 WHERE a >= 6 order by a;
EXPLAIN SELECT * FROM t1 WHERE a >= 6;
SELECT * FROM t1 WHERE a > 1 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 1;
SELECT * FROM t1 WHERE a > 2 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 2;
SELECT * FROM t1 WHERE a > 3 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 3;
SELECT * FROM t1 WHERE a > 4 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 4;
SELECT * FROM t1 WHERE a > 5 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 5;
SELECT * FROM t1 WHERE a > 6 order by a;
EXPLAIN SELECT * FROM t1 WHERE a > 6;
DROP TABLE t1;
#
# Bug#20577: Partitions: use of to_days() function leads to selection failures
#
--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));
SET SQL_MODE = '';
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 SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03';
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN 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 SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03';
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN 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 SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03';
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN 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 SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03';
EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03';
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
EXPLAIN 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 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;
# 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 select * from t3 where a=11;
explain select * from t3 where a=10;
explain select * from t3 where a=20;
explain select * from t3 where a=30;
# 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 select * from t7 where a < 5;
explain select * from t7 where a < 9;
explain select * from t7 where a <= 9;
explain select * from t7 where a = 9;
explain select * from t7 where a >= 9;
explain select * from t7 where a > 9;
explain select * from t7 where a < 10;
explain select * from t7 where a <= 10;
explain select * from t7 where a = 10;
explain select * from t7 where a >= 10;
explain select * from t7 where a > 10;
#rightmost intervals
explain select * from t7 where a < 89;
explain select * from t7 where a <= 89;
explain select * from t7 where a = 89;
explain select * from t7 where a > 89;
explain select * from t7 where a >= 89;
explain select * from t7 where a < 90;
explain select * from t7 where a <= 90;
explain select * from t7 where a = 90;
explain select * from t7 where a > 90;
explain select * from t7 where a >= 90;
explain select * from t7 where a > 91;
# misc intervals
explain 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 select * from t7 where a < 5;
explain select * from t7 where a < 9;
explain select * from t7 where a <= 9;
explain select * from t7 where a = 9;
explain select * from t7 where a >= 9;
explain select * from t7 where a > 9;
explain select * from t7 where a < 10;
explain select * from t7 where a <= 10;
explain select * from t7 where a = 10;
explain select * from t7 where a >= 10;
explain select * from t7 where a > 10;
#rightmost intervals
explain select * from t7 where a < 89;
explain select * from t7 where a <= 89;
explain select * from t7 where a = 89;
explain select * from t7 where a > 89;
explain select * from t7 where a >= 89;
explain select * from t7 where a < 90;
explain select * from t7 where a <= 90;
explain select * from t7 where a = 90;
explain select * from t7 where a > 90;
explain select * from t7 where a >= 90;
explain select * from t7 where a > 91;
# misc intervals
explain 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 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 select * from t9 where a < '2004-12-19';
explain select * from t9 where a <= '2004-12-19';
drop table t7,t8,t9;
#
# 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 select * from t1 where a1 > 3;
explain select * from t1 where a1 >= 3;
explain select * from t1 where a1 < 3 and a1 > 3;
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.
#
# 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 select * from t2;
explain select * from t2 where a < 801 and a > 200;
explain select * from t2 where a < 801 and a > 800;
explain select * from t2 where a > 600;
explain select * from t2 where a > 600 and b = 1;
explain select * from t2 where a > 600 and b = 4;
explain select * from t2 where a > 600 and b = 5;
explain 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 select * from t2;
# not using indexes
explain select * from t2 where a = 101;
explain select * from t2 where a = 550;
explain select * from t2 where a = 833;
explain select * from t2 where a in (10,20,30);
explain select * from t2 where (a = 100 OR a = 900);
explain select * from t2 where (a > 100 AND a < 600);
explain select * from t2 where b = 4;
explain select * from t2 where b = 6;
explain select * from t2 where b in (1,3,5);
explain select * from t2 where b in (2,4,6);
explain select * from t2 where b in (7,8,9);
explain select * from t2 where b > 5;
explain select * from t2 where b > 5 and b < 8;
explain select * from t2 where b > 5 and b < 7;
explain 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_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_key';
show status like 'Handler_read_prev';
show status like 'Handler_read_next';
drop table t1, t2;
# BUG#20484 "Partitions: crash with explain and union"
create table t1 (s1 int);
explain select 1 from t1 union all select 2;
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 select * from t1 where a=0;
explain select * from t2 where a=0;
explain select * from t1 where a=0xFE;
explain select * from t2 where a=0xFE;
explain select * from t1 where a > 0xFE AND a <= 0xFF;
explain select * from t2 where a > 0xFE AND a <= 0xFF;
explain select * from t1 where a >= 0xFE AND a <= 0xFF;
explain select * from t2 where a >= 0xFE AND a <= 0xFF;
explain select * from t1 where a < 64 AND a >= 63;
explain select * from t2 where a < 64 AND a >= 63;
explain select * from t1 where a <= 64 AND a >= 63;
explain 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 select * from t1 where
a >= 2305561538531885056-10 and a <= 2305561538531885056-8;
explain select * from t1 where
a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE;
explain 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 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 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 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 select * from t1 where a between 10 and 13;
explain select * from t1 where a between 10 and 10+33;
drop table t0, t1;
drop table if exists t;
create table t(a timestamp) partition by range(unix_timestamp(a)) (partition p0 values less than(unix_timestamp('2019-02-16 14:20:00')), partition p1 values less than (maxvalue));
explain select * from t where a between timestamp'2019-02-16 14:19:00' and timestamp'2019-02-16 14:21:00';
drop table if exists t;
create table t(a int) partition by range(a) (partition p0 values less than (100), partition p1 values less than (200), partition p2 values less than (300));
begin;
explain select * from t;
insert into t values(1);
explain select * from t;
rollback;
begin;
insert into t values(101);
explain select * from t;
rollback;
begin;
insert into t values(201);
explain select * from t;
rollback;
explain select * from t;
drop table if exists t;
CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE COLUMNS(a) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (100)
);
desc select * from t where a = 11 and b = 1 or a = 12 and b = 1;
#
# MySQL doesn't support partition pruning for 'floor(unix_timestamp(ts))' but it works on TiDB
# https://github.com/pingcap/tidb/issues/16354
#
drop table if exists t;
create table t (ts timestamp(3) not null default current_timestamp(3))
partition by range (floor(unix_timestamp(ts))) (
partition p0 values less than (unix_timestamp('2020-04-05 00:00:00')),
partition p1 values less than (unix_timestamp('2020-04-15 00:00:00')),
partition p2 values less than (unix_timestamp('2020-04-25 00:00:00'))
);
explain select * from t where ts = '2020-04-06 00:00:00' -- p1;
explain select * from t where ts = '2020-04-05 00:00:00.001' -- p1;
explain select * from t where ts > '2020-04-15 00:00:00' -- p2;
explain select * from t where ts > '2020-04-14 23:59:59.999' -- p1,p2;
explain select * from t where ts > '2020-04-15 00:00:00.001' -- p2;
explain select * from t where ts > '2020-04-26 00:00:00.001' -- dual;
explain select * from t where ts >= '2020-04-04 12:22:32' -- p0,p1,p2;
explain select * from t where ts >= '2020-04-05 00:00:00' -- p1,p2;
explain select * from t where ts >= '2020-04-25 00:00:00' -- dual;
explain select * from t where ts < '2020-04-25 00:00:00' -- p0,p1,p2;
explain select * from t where ts < '2020-04-15 00:00:00.001' -- p0,p1,p2;
explain select * from t where ts < '2020-04-15 00:00:00' -- expect perfect : p0,p1, obtain: p0,p1,p2;
explain select * from t where ts < '2020-04-14 23:59:59.999' -- p0,p1;
explain select * from t where ts < '2020-04-03 00:00:00' -- p0;
explain select * from t where ts < '2021-05-03 00:00:00' -- p0,p1,p2;
explain select * from t where ts <= '2020-04-05 00:00:00' -- p0,p1;
explain select * from t where ts <= '2020-04-03 00:00:00' -- p0;
explain select * from t where ts <= '2020-04-14 23:59:59.123' -- p0,p1;
explain select * from t where ts <= '2020-04-25 00:00:00' -- p0,p1,p2;
explain select * from t where ts > '2020-04-25 00:00:00' or ts < '2020-01-02 00:00:00' -- p0;
explain select * from t where ts > '2020-04-02 00:00:00' and ts < '2020-04-07 00:00:00' -- p0,p1;
drop table if exists t;
create table t (id int, name varchar(20)) partition by hash(id) partitions 128;
explain SELECT * FROM t partition (p1) where name = '1';