EVOLUTION-MANAGER

Edit File: partition_pruning.result

drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; # # Bug#53806: Wrong estimates for range query in partitioned MyISAM table # Bug#46754: 'rows' field doesn't reflect partition pruning # 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); EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra # # # # # # # # # 3 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; id select_type table partitions type possible_keys key key_len ref rows Extra # # # # # # # # # 9 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra # # # # # # # # # 3 # DROP TABLE t1; # # Bug#49742: Partition Pruning not working correctly for RANGE # 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; a -1 0 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index SELECT * FROM t1 WHERE a < 2; a -1 0 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a < 3; a -1 0 1 2 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT * FROM t1 WHERE a < 4; a -1 0 1 2 3 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index SELECT * FROM t1 WHERE a < 5; a -1 0 1 2 3 4 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index SELECT * FROM t1 WHERE a < 6; a -1 0 1 2 3 4 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4,p5 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index SELECT * FROM t1 WHERE a < 7; a -1 0 1 2 3 4 5 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 0 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a <= 2; a -1 0 1 2 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT * FROM t1 WHERE a <= 3; a -1 0 1 2 3 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index SELECT * FROM t1 WHERE a <= 4; a -1 0 1 2 3 4 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index SELECT * FROM t1 WHERE a <= 5; a -1 0 1 2 3 4 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4,p5 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index SELECT * FROM t1 WHERE a <= 6; a -1 0 1 2 3 4 5 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 7; a -1 0 1 2 3 4 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 SELECT * FROM t1 WHERE a = 2; a 2 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 system PRIMARY NULL NULL NULL 1 SELECT * FROM t1 WHERE a = 3; a 3 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3 system PRIMARY NULL NULL NULL 1 SELECT * FROM t1 WHERE a = 4; a 4 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4 system PRIMARY NULL NULL NULL 1 SELECT * FROM t1 WHERE a = 5; a 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p5 system PRIMARY NULL NULL NULL 1 SELECT * FROM t1 WHERE a = 6; a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max const PRIMARY PRIMARY 4 const 1 Using index SELECT * FROM t1 WHERE a = 7; a 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 7; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max const PRIMARY PRIMARY 4 const 1 Using index SELECT * FROM t1 WHERE a >= 1; a 1 2 3 4 5 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 8 Using where; Using index SELECT * FROM t1 WHERE a >= 2; a 2 3 4 5 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 7 Using where; Using index SELECT * FROM t1 WHERE a >= 3; a 3 4 5 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index SELECT * FROM t1 WHERE a >= 4; a 4 5 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4,p5,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index SELECT * FROM t1 WHERE a >= 5; a 5 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p5,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT * FROM t1 WHERE a >= 6; a 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a >= 7; a 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 7; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a > 1; a 2 3 4 5 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 7 Using where; Using index SELECT * FROM t1 WHERE a > 2; a 3 4 5 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index SELECT * FROM t1 WHERE a > 3; a 4 5 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4,p5,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index SELECT * FROM t1 WHERE a > 4; a 5 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p5,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT * FROM t1 WHERE a > 5; a 6 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a > 6; a 7 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a > 7; a 8 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 7; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index 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; a -1 0 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index SELECT * FROM t1 WHERE a < 2; a -1 0 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a < 3; a -1 0 1 2 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT * FROM t1 WHERE a < 4; a -1 0 1 2 3 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index SELECT * FROM t1 WHERE a < 5; a -1 0 1 2 3 4 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index SELECT * FROM t1 WHERE a < 6; a -1 0 1 2 3 4 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 0 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a <= 2; a -1 0 1 2 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT * FROM t1 WHERE a <= 3; a -1 0 1 2 3 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index SELECT * FROM t1 WHERE a <= 4; a -1 0 1 2 3 4 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index SELECT * FROM t1 WHERE a <= 5; a -1 0 1 2 3 4 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index SELECT * FROM t1 WHERE a <= 6; a -1 0 1 2 3 4 5 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 SELECT * FROM t1 WHERE a = 2; a 2 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 system PRIMARY NULL NULL NULL 1 SELECT * FROM t1 WHERE a = 3; a 3 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3 system PRIMARY NULL NULL NULL 1 SELECT * FROM t1 WHERE a = 4; a 4 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4 system PRIMARY NULL NULL NULL 1 SELECT * FROM t1 WHERE a = 5; a 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max const PRIMARY PRIMARY 4 const 1 Using index SELECT * FROM t1 WHERE a = 6; a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max const PRIMARY PRIMARY 4 const 1 Using index SELECT * FROM t1 WHERE a >= 1; a 1 2 3 4 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 7 Using where; Using index SELECT * FROM t1 WHERE a >= 2; a 2 3 4 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index SELECT * FROM t1 WHERE a >= 3; a 3 4 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3,p4,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index SELECT * FROM t1 WHERE a >= 4; a 4 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT * FROM t1 WHERE a >= 5; a 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a >= 6; a 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a > 1; a 2 3 4 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index SELECT * FROM t1 WHERE a > 2; a 3 4 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3,p4,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index SELECT * FROM t1 WHERE a > 3; a 4 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index SELECT * FROM t1 WHERE a > 4; a 5 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a > 5; a 6 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index SELECT * FROM t1 WHERE a > 6; a 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index DROP TABLE t1; # 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)); 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'); SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a 1001-01-01 SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a 1001-00-00 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a 1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a > '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a = '1999-02-31'; a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 1001-00-00 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using index # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index # test without index ALTER TABLE t1 DROP KEY a; SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a 1001-01-01 SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a 1001-00-00 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a 1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a > '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a = '1999-02-31'; a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 1001-00-00 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where DROP TABLE t1; # 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'))); 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'); SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a 1001-01-01 SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a 1001-00-00 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a 1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a > '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a = '1999-02-31'; a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 1001-00-00 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using index # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 range a a 4 NULL 3 Using where; Using index # test without index ALTER TABLE t1 DROP KEY a; SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a 1001-01-01 SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a 1001-00-00 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a 1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a > '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a = '1999-02-31'; a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 1001-00-00 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where DROP TABLE t1; # 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'))); 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'); SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a 1001-01-01 SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a 1001-00-00 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a 1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a > '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a = '1999-02-31'; a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 1001-00-00 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using index # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 range a a 4 NULL 3 Using where; Using index # test without index ALTER TABLE t1 DROP KEY a; SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a 1001-01-01 SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a 1001-00-00 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a 1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a > '1999-02-31'; a 2001-01-01 SELECT * FROM t1 WHERE a = '1999-02-31'; a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 0000-01-02 0001-01-01 1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a 1001-00-00 1001-01-01 1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 1001-00-00 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 ALL NULL NULL NULL NULL 4 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where DROP TABLE t1; # 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090402 index NULL PRIMARY 9 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090402 index NULL PRIMARY 9 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090402 index NULL PRIMARY 9 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 Using where; Using index DROP TABLE t1; # 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index DROP TABLE t1; # 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 3 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 3 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 3 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where DROP TABLE t1; # 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where DROP TABLE t1; # 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); # 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Warning 1292 Incorrect datetime value: '2009-04-99' DROP TABLE t1; 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'; a b DROP TABLE t1; 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE drop table t1; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a=2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 system NULL NULL NULL NULL 1 explain partitions select * from t1 where a=1 or a=2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 3 Using where 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t2 where b=1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t2 where a=1 and b=1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t3 where a=10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t3 where a=20; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t3 where a=30; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 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); explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t4 p0 system NULL NULL NULL NULL 1 explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t4 p0 system NULL NULL NULL NULL 1 explain partitions select * from t4 where (a=10 and b=2) or (a=10 and b=3) or (a=10 and b = 4); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t4 p0,p1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t4 where (a=10 and b=1) or a=11; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t4 p0,p1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t4 where (a=10 and b=2) or a=11; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t4 p0,p1 ALL NULL NULL NULL NULL 2 Using where drop table t1, t2, t3, t4; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 explain partitions select * from t5 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t5 p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3) or (a=10 and b = 4); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where explain partitions select * from t5 where (c=1 and d=1); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t5 p0_p0sp0,p1_p1sp0 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t5 where (c=2 and d=1); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t5 p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or (c=2 and d=1); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 3 Using where 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 3 Using where 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p1 system NULL NULL NULL NULL 1 explain partitions select * from t6 where a > 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a >= 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a > 0 and a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p1,p3 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t6 where a > 5 and a < 12; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a > 3 and a < 8 ; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p5,p7 system NULL NULL NULL NULL 1 explain partitions select * from t6 where a >= 0 and a <= 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p1,p3,p5 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t6 where a >= 5 and a <= 12; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p5,p7,p9 system NULL NULL NULL NULL 1 explain partitions select * from t6 where a >= 3 and a <= 8; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t6 where a > 3 and a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p1 system NULL NULL NULL NULL 1 explain partitions select * from t6 where a > 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a >= 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a > 0 and a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p1,p3 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t6 where a > 5 and a < 12; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a > 3 and a < 8 ; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p5,p7 system NULL NULL NULL NULL 1 explain partitions select * from t6 where a >= 0 and a <= 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p1,p3,p5 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t6 where a >= 5 and a <= 12; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p5,p7,p9 system NULL NULL NULL NULL 1 explain partitions select * from t6 where a >= 3 and a <= 8; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t6 where a > 3 and a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 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); explain partitions select * from t7 where a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a < 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a <= 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a = 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a >= 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a > 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a < 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a <= 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30 system NULL NULL NULL NULL 1 explain partitions select * from t7 where a = 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30 system NULL NULL NULL NULL 1 explain partitions select * from t7 where a >= 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a > 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a < 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a <= 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a = 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a >= 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a < 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a <= 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a = 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a >= 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 91; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 11 and a < 29; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 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); explain partitions select * from t7 where a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a < 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a <= 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a = 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a >= 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a > 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a < 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a <= 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30 system NULL NULL NULL NULL 1 explain partitions select * from t7 where a = 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30 system NULL NULL NULL NULL 1 explain partitions select * from t7 where a >= 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a > 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a < 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a <= 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a = 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a >= 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a < 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a <= 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a = 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a >= 90; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 91; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 11 and a < 29; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 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'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 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'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t9 where a <= '2004-12-19'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t9 p0,p1 ALL NULL NULL NULL NULL 2 Using where drop table t5,t6,t7,t8,t9; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t1 where a1 >= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 system NULL NULL NULL NULL 1 explain partitions select * from t1 where a1 < 3 and a1 > 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t3 p0_p0sp1,p1_p1sp0,p1_p1sp1,p1_p1sp2,p1_p1sp3,p2_p2sp1,p3_p3sp1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t3 where a=4 or b=2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t3 p0_p0sp2,p1_p1sp2,p2_p2sp2,p3_p3sp0,p3_p3sp1,p3_p3sp2,p3_p3sp3 system NULL NULL NULL NULL 1 explain partitions select * from t3 where (a=2 or b=1) and (a=4 or b=2) ; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t3 p1_p1sp2,p3_p3sp1 system NULL NULL NULL NULL 1 drop table t3; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t1 where a is not null; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where drop table t1; 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE X p1,p2 range a,b a 4 NULL 4 Using where 1 SIMPLE Y p2,p3 ref a,b b 4 test.X.b 2 Using where explain partitions select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE X p1,p2 range a a 4 NULL 4 Using where 1 SIMPLE Y p1,p2 ref a a 4 test.X.a 2 drop table t1; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 system NULL NULL NULL NULL 1 explain partitions select * from t1 where a >= 1 and a < 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a > 1 and a <= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a >= 1 and a <= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2,p3 ALL NULL NULL NULL NULL 3 Using where 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0_p0sp2,p1_p1sp2,p2_p2sp2,p3_p3sp2 system NULL NULL NULL NULL 1 explain partitions select * from t1 where b > 1 and b < 3 and (a =1 or a =2); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1_p1sp2,p2_p2sp2 system NULL NULL NULL NULL 1 drop table t1; 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); flush status; update t1 set a=100 where a=5; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 0 flush status; update t1 set a=100 where a+1=5+1; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 10 flush status; delete from t1 where a=5; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 0 flush status; delete from t1 where a+1=5+1; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 10 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'; Variable_name Value Handler_read_rnd_next 0 flush status; delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 0 drop table t1,t2; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 explain partitions select * from t2 where a < 801 and a > 200; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p1,p2,p3,p4 ALL NULL NULL NULL NULL 800 Using where explain partitions select * from t2 where a < 801 and a > 800; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 200 Using where explain partitions select * from t2 where a > 600; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where explain partitions select * from t2 where a > 600 and b = 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where explain partitions select * from t2 where a > 600 and b = 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where explain partitions select * from t2 where a > 600 and b = 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where explain partitions select * from t2 where b = 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 Using where flush status; update t2 set b = 100 where b = 6; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 1015 flush status; update t2 set a = 1002 where a = 1001; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 0 flush status; update t2 set b = 6 where a = 600; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 201 flush status; update t2 set b = 6 where a > 600 and a < 800; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 201 flush status; delete from t2 where a > 600; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 402 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 910 explain partitions select * from t2 where a = 101; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 110 Using where explain partitions select * from t2 where a = 550; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 200 Using where explain partitions select * from t2 where a = 833; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 200 Using where explain partitions select * from t2 where (a = 100 OR a = 900); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p4 ALL NULL NULL NULL NULL 310 Using where explain partitions select * from t2 where (a > 100 AND a < 600); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 510 Using where explain partitions select * from t2 where b = 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 explain extended select * from t2 where b = 6; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ref b b 5 const 76 100.00 Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` = 6 explain partitions select * from t2 where b = 6; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 explain extended select * from t2 where b in (1,3,5); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` in (1,3,5) explain partitions select * from t2 where b in (1,3,5); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (2,4,6); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL b NULL NULL NULL 910 25.05 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` in (2,4,6) explain partitions select * from t2 where b in (2,4,6); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (7,8,9); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL b NULL NULL NULL 910 36.70 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` in (7,8,9) explain partitions select * from t2 where b in (7,8,9); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL b NULL NULL NULL 910 44.84 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` > 5 explain partitions select * from t2 where b > 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5 and b < 8; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL b NULL NULL NULL 910 22.09 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` > 5 and `test`.`t2`.`b` < 8 explain partitions select * from t2 where b > 5 and b < 8; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5 and b < 7; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 range b b 5 NULL 76 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` > 5 and `test`.`t2`.`b` < 7 explain partitions select * from t2 where b > 5 and b < 7; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 76 Using where explain extended select * from t2 where b > 0 and b < 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL b NULL NULL NULL 910 41.65 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` > 0 and `test`.`t2`.`b` < 5 explain partitions select * from t2 where b > 0 and b < 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where flush status; update t2 set a = 111 where b = 10; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 0 show status like 'Handler_read_key'; Variable_name Value Handler_read_key 5 flush status; update t2 set a = 111 where b in (5,6); show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 915 show status like 'Handler_read_key'; Variable_name Value Handler_read_key 0 flush status; update t2 set a = 222 where b = 7; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 0 show status like 'Handler_read_key'; Variable_name Value Handler_read_key 5 flush status; delete from t2 where b = 7; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 0 show status like 'Handler_read_key'; Variable_name Value Handler_read_key 5 flush status; delete from t2 where b > 5; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 815 show status like 'Handler_read_rnd_deleted'; Variable_name Value Handler_read_rnd_deleted 400 show status like 'Handler_read_key'; Variable_name Value Handler_read_key 0 show status like 'Handler_read_prev'; Variable_name Value Handler_read_prev 0 show status like 'Handler_read_next'; Variable_name Value Handler_read_next 0 flush status; delete from t2 where b < 5 or b > 3; show status like 'Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 515 show status like 'Handler_read_rnd_deleted'; Variable_name Value Handler_read_rnd_deleted 700 show status like 'Handler_read_key'; Variable_name Value Handler_read_key 0 show status like 'Handler_read_prev'; Variable_name Value Handler_read_prev 0 show status like 'Handler_read_next'; Variable_name Value Handler_read_next 0 drop table t1, t2; 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; f_int1 f_int2 5 5 6 6 7 7 8 8 9 9 drop table t1; 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; f_int1 NULL explain partitions select * from t1 where f_int1 is null; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 part4_part4sp0 system NULL NULL NULL NULL 1 drop table t1; 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0_p0sp1,p0_p0sp2 ALL NULL NULL NULL NULL 2 Using where 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pn_pnsp0,pn_pnsp1 system NULL NULL NULL NULL 1 explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pn_pnsp0,pn_pnsp1 system NULL NULL NULL NULL 1 explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0_p0sp0,p0_p0sp1,pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL 5 Using where explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0_p0sp0,p0_p0sp1,pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL 5 Using where 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,pn ALL NULL NULL NULL NULL 8 Using where drop table t1; 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; count(*) 1 explain partitions select count(*) from t1 where s1 < 0 or s1 is null; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3 system NULL NULL NULL NULL 1 drop table t1; create table t1 (a char(32) primary key) partition by key() partitions 100; insert into t1 values ('na'); select * from t1; a na select * from t1 where a like 'n%'; a na drop table t1; create table t1 (s1 varchar(15)) partition by key (s1); select * from t1 where s1 = 0 or s1 is null; s1 insert into t1 values ('aa'),('bb'),('0'); explain partitions select * from t1 where s1 = 0 or s1 is null; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where drop table t1; 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); explain partitions select * from t2; id 1 select_type SIMPLE table t2 partitions p_0_long_partition_name_p_0_long_partition_namesp0,p_0_long_partition_name_p_0_long_partition_namesp1,p_0_long_partition_name_p_0_long_partition_namesp2,p_0_long_partition_name_p_0_long_partition_namesp3,p_0_long_partition_name_p_0_long_partition_namesp4,p_0_long_partition_name_p_0_long_partition_namesp5,p_0_long_partition_name_p_0_long_partition_namesp6,p_0_long_partition_name_p_0_long_partition_namesp7,p_0_long_partition_name_p_0_long_partition_namesp8,p_0_long_partition_name_p_0_long_partition_namesp9,p_0_long_partition_name_p_0_long_partition_namesp10,p_0_long_partition_name_p_0_long_partition_namesp11,p_0_long_partition_name_p_0_long_partition_namesp12,p_0_long_partition_name_p_0_long_partition_namesp13,p_0_long_partition_name_p_0_long_partition_namesp14,p_0_long_partition_name_p_0_long_partition_namesp15,p_0_long_partition_name_p_0_long_partition_namesp16,p_0_long_partition_name_p_0_long_partition_namesp17,p_0_long_partition_name_p_0_long_partition_namesp18,p_0_long_partition_name_p_0_long_partition_namesp19,p_0_long_partition_name_p_0_long_partition_namesp20,p_0_long_partition_name_p_0_long_partition_namesp21,p_0_long_partition_name_p_0_long_partition_namesp22,p_0_long_partition_name_p_0_long_partition_namesp23,p_0_long_partition_name_p_0_long_partition_namesp24,p_0_long_partition_name_p_0_long_partition_namesp25,p_0_long_partition_name_p_0_long_partition_namesp26,p_0_long_partition_name_p_0_long_partition_namesp27,p_0_long_partition_name_p_0_long_partition_namesp28,p_0_long_partition_name_p_0_long_partition_namesp29,p_0_long_partition_name_p_0_long_partition_namesp30,p_0_long_partition_name_p_0_long_partition_namesp31,p_0_long_partition_name_p_0_long_partition_namesp32,p_0_long_partition_name_p_0_long_partition_namesp33,p_0_long_partition_name_p_0_long_partition_namesp34,p_0_long_partition_name_p_0_long_partition_namesp35,p_0_long_partition_name_p_0_long_partition_namesp36,p_0_long_partition_name_p_0_long_partition_namesp37,p_0_long_partition_name_p_0_long_partition_namesp38,p_0_long_partition_name_p_0_long_partition_namesp39,p_1_long_partition_name_p_1_long_partition_namesp0,p_1_long_partition_name_p_1_long_partition_namesp1,p_1_long_partition_name_p_1_long_partition_namesp2,p_1_long_partition_name_p_1_long_partition_namesp3,p_1_long_partition_name_p_1_long_partition_namesp4,p_1_long_partition_name_p_1_long_partition_namesp5,p_1_long_partition_name_p_1_long_partition_namesp6,p_1_long_partition_name_p_1_long_partition_namesp7,p_1_long_partition_name_p_1_long_partition_namesp8,p_1_long_partition_name_p_1_long_partition_namesp9,p_1_long_partition_name_p_1_long_partition_namesp10,p_1_long_partition_name_p_1_long_partition_namesp11,p_1_long_partition_name_p_1_long_partition_namesp12,p_1_long_partition_name_p_1_long_partition_namesp13,p_1_long_partition_name_p_1_long_partition_namesp14,p_1_long_partition_name_p_1_long_partition_namesp15,p_1_long_partition_name_p_1_long_partition_namesp16,p_1_long_partition_name_p_1_long_partition_namesp17,p_1_long_partition_name_p_1_long_partition_namesp18,p_1_long_partition_name_p_1_long_partition_namesp19,p_1_long_partition_name_p_1_long_partition_namesp20,p_1_long_partition_name_p_1_long_partition_namesp21,p_1_long_partition_name_p_1_long_partition_namesp22,p_1_long_partition_name_p_1_long_partition_namesp23,p_1_long_partition_name_p_1_long_partition_namesp24,p_1_long_partition_name_p_1_long_partition_namesp25,p_1_long_partition_name_p_1_long_partition_namesp26,p_1_long_partition_name_p_1_long_partition_namesp27,p_1_long_partition_name_p_1_long_partition_namesp28,p_1_long_partition_name_p_1_long_partition_namesp29,p_1_long_partition_name_p_1_long_partition_namesp30,p_1_long_partition_name_p_1_long_partition_namesp31,p_1_long_partition_name_p_1_long_partition_namesp32,p_1_long_partition_name_p_1_long_partition_namesp33,p_1_long_partition_name_p_1_long_partition_namesp34,p_1_long_partition_name_p_1_long_partition_namesp35,p_1_long_partition_name_p_1_long_partition_namesp36,p_1_long_partition_name_p_1_long_partition_namesp37,p_1_long_partition_name_p_1_long_partition_namesp38,p_1_long_partition_name_p_1_long_partition_namesp39 type ALL possible_keys NULL key NULL key_len NULL ref NULL rows 2 Extra drop table t2; create table t1 (s1 int); explain partitions select 1 from t1 union all select 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 PRIMARY t1 NULL system NULL NULL NULL NULL 0 Const row not found 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used drop table t1; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 system NULL NULL NULL NULL 1 explain partitions select * from t1 where a >= 18446744073709551000-1 and a <= 18446744073709551000+1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3,p4 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t1 where a between 18446744073709551001 and 18446744073709551002; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a = 18446744073709551000; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a = 18446744073709551613; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a = 18446744073709551614; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables drop table t1; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t2 where a=0; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a=0xFE; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t2 where a=0xFE; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a > 0xFE AND a <= 0xFF; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t2 where a > 0xFE AND a <= 0xFF; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t1 where a >= 0xFE AND a <= 0xFF; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t2 where a >= 0xFE AND a <= 0xFF; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a < 64 AND a >= 63; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t2 where a < 64 AND a >= 63; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a <= 64 AND a >= 63; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where explain partitions select * from t2 where a <= 64 AND a >= 63; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 4 Using where 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3 ALL NULL NULL NULL NULL 4 Using where explain partitions select * from t1 where a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2,p3,p4 ALL NULL NULL NULL NULL 8 Using where 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p3 ALL NULL NULL NULL NULL 4 Using where drop table t1; 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'); must use p0 only: explain partitions select * from t1 where recdate < '2007-03-08 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where 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'); must use p0 only: explain partitions select * from t1 where recdate < '2006-01-01 00:00:00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where drop table t1; # # BUG#33730 Full table scan instead selected partitions for query more than 10 partitions # 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; explain partitions select * from t1 where a between 10 and 13; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 64 Using where explain partitions select * from t1 where a between 10 and 10+33; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 100 Using where drop table t0, t1; # # Bug#71095: Wrong results with PARTITION BY LIST COLUMNS() # 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; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1; c1 c2 c3 c4 1 1 1 1 SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1; c1 c2 c3 c4 1 1 1 1 SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1; c1 c2 c3 c4 1 1 1 1 1 2 1 1 SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1; c1 c2 c3 c4 1 2 1 1 SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3; c1 c2 c3 c4 1 1 1 1 1 2 1 1 SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3; c1 c2 c3 c4 1 1 1 1 1 2 1 1 SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3; c1 c2 c3 c4 2 3 1 1 SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3; c1 c2 c3 c4 2 3 1 1 SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3; c1 c2 c3 c4 2 3 1 1 2 4 1 1 SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3; c1 c2 c3 c4 2 4 1 1 SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4; c1 c2 c3 c4 2 3 1 1 SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4; c1 c2 c3 c4 2 3 1 1 2 4 1 1 SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4; c1 c2 c3 c4 2 4 1 1 SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4; c1 c2 c3 c4 2 4 1 1 SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4; c1 c2 c3 c4 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 range PRIMARY PRIMARY 8 NULL 1 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 const PRIMARY PRIMARY 8 const,const 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 range PRIMARY PRIMARY 8 NULL 1 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 const PRIMARY PRIMARY 8 const,const 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 range PRIMARY PRIMARY 8 NULL 1 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 range PRIMARY PRIMARY 8 NULL 1 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 const PRIMARY PRIMARY 8 const,const 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 range PRIMARY PRIMARY 8 NULL 1 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; # # MDEV-6239: Partition pruning is not working as expected in an inner query # 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'); # 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); id select_type table partitions type possible_keys key key_len ref rows Extra 1 PRIMARY t2 p_1000 ref PRIMARY PRIMARY 8 const 2 Using index 1 PRIMARY t1 p_1000 ALL PRIMARY NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) drop table t1,t2; # # MDEV-9505: Valgrind failure in SEL_ARG::store_min,find_used_partitions,... # 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)))); a b c d 1 a b 1 drop table t1; # # MDEV-17493: Partition pruning doesn't work for nested outer joins # create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int); insert into t1 select a,a,a from t0; create table t2 (a int, b int, c int); insert into t2 select a,a,a from t0; create table t3 ( part_id int, a int ) partition by list (part_id) ( 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) ); insert into t3 select mod(a,5), a from t0; explain partitions select * from t1 left join t3 on (t1.a=t3.a and t3.part_id=3); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 1 SIMPLE t3 p3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) # The following should have partitions="p3", NOT p0,p1,p2,p3,p4: explain partitions select * from t1 left join (t3 join t2) on (t1.a=t3.a and t3.a=t2.b and t3.part_id=3); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 1 SIMPLE t3 p3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 10 Using where; Using join buffer (incremental, BNL join) drop table t0,t1,t2,t3;