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
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';
|
|
|