1# 2# Partition pruning tests. Currently we only detect which partitions to 3# prune, so the test is EXPLAINs. 4# 5-- source include/have_partition.inc 6--source include/default_optimizer_switch.inc 7 8--disable_warnings 9drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; 10--enable_warnings 11 12--echo # 13--echo # Bug#53806: Wrong estimates for range query in partitioned MyISAM table 14--echo # Bug#46754: 'rows' field doesn't reflect partition pruning 15--echo # 16CREATE TABLE t1 (a INT PRIMARY KEY) 17PARTITION BY RANGE (a) ( 18PARTITION p0 VALUES LESS THAN (1), 19PARTITION p1 VALUES LESS THAN (2), 20PARTITION p2 VALUES LESS THAN (3), 21PARTITION p3 VALUES LESS THAN (4), 22PARTITION p4 VALUES LESS THAN (5), 23PARTITION p5 VALUES LESS THAN (6), 24PARTITION max VALUES LESS THAN MAXVALUE); 25 26INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8); 27 28--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 # 29EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; 30--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 # 31EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; 32--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 # 33EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; 34DROP TABLE t1; 35 36--echo # 37--echo # Bug#49742: Partition Pruning not working correctly for RANGE 38--echo # 39CREATE TABLE t1 (a INT PRIMARY KEY) 40PARTITION BY RANGE (a) ( 41PARTITION p0 VALUES LESS THAN (1), 42PARTITION p1 VALUES LESS THAN (2), 43PARTITION p2 VALUES LESS THAN (3), 44PARTITION p3 VALUES LESS THAN (4), 45PARTITION p4 VALUES LESS THAN (5), 46PARTITION p5 VALUES LESS THAN (6), 47PARTITION max VALUES LESS THAN MAXVALUE); 48 49INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8); 50 51SELECT * FROM t1 WHERE a < 1; 52EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1; 53SELECT * FROM t1 WHERE a < 2; 54EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2; 55SELECT * FROM t1 WHERE a < 3; 56EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3; 57SELECT * FROM t1 WHERE a < 4; 58EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4; 59SELECT * FROM t1 WHERE a < 5; 60EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5; 61SELECT * FROM t1 WHERE a < 6; 62EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6; 63SELECT * FROM t1 WHERE a < 7; 64EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; 65SELECT * FROM t1 WHERE a <= 1; 66EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; 67SELECT * FROM t1 WHERE a <= 2; 68EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2; 69SELECT * FROM t1 WHERE a <= 3; 70EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3; 71SELECT * FROM t1 WHERE a <= 4; 72EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4; 73SELECT * FROM t1 WHERE a <= 5; 74EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5; 75SELECT * FROM t1 WHERE a <= 6; 76EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; 77SELECT * FROM t1 WHERE a <= 7; 78EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7; 79SELECT * FROM t1 WHERE a = 1; 80EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1; 81SELECT * FROM t1 WHERE a = 2; 82EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2; 83SELECT * FROM t1 WHERE a = 3; 84EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3; 85SELECT * FROM t1 WHERE a = 4; 86EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4; 87SELECT * FROM t1 WHERE a = 5; 88EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5; 89SELECT * FROM t1 WHERE a = 6; 90EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6; 91SELECT * FROM t1 WHERE a = 7; 92EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 7; 93SELECT * FROM t1 WHERE a >= 1; 94EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1; 95SELECT * FROM t1 WHERE a >= 2; 96EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2; 97SELECT * FROM t1 WHERE a >= 3; 98EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3; 99SELECT * FROM t1 WHERE a >= 4; 100EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4; 101SELECT * FROM t1 WHERE a >= 5; 102EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5; 103SELECT * FROM t1 WHERE a >= 6; 104EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6; 105SELECT * FROM t1 WHERE a >= 7; 106EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 7; 107SELECT * FROM t1 WHERE a > 1; 108EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1; 109SELECT * FROM t1 WHERE a > 2; 110EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2; 111SELECT * FROM t1 WHERE a > 3; 112EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3; 113SELECT * FROM t1 WHERE a > 4; 114EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4; 115SELECT * FROM t1 WHERE a > 5; 116EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5; 117SELECT * FROM t1 WHERE a > 6; 118EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6; 119SELECT * FROM t1 WHERE a > 7; 120EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 7; 121DROP TABLE t1; 122 123CREATE TABLE t1 (a INT PRIMARY KEY) 124PARTITION BY RANGE (a) ( 125PARTITION p0 VALUES LESS THAN (1), 126PARTITION p1 VALUES LESS THAN (2), 127PARTITION p2 VALUES LESS THAN (3), 128PARTITION p3 VALUES LESS THAN (4), 129PARTITION p4 VALUES LESS THAN (5), 130PARTITION max VALUES LESS THAN MAXVALUE); 131 132INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7); 133 134SELECT * FROM t1 WHERE a < 1; 135EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1; 136SELECT * FROM t1 WHERE a < 2; 137EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2; 138SELECT * FROM t1 WHERE a < 3; 139EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3; 140SELECT * FROM t1 WHERE a < 4; 141EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4; 142SELECT * FROM t1 WHERE a < 5; 143EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5; 144SELECT * FROM t1 WHERE a < 6; 145EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6; 146SELECT * FROM t1 WHERE a <= 1; 147EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; 148SELECT * FROM t1 WHERE a <= 2; 149EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2; 150SELECT * FROM t1 WHERE a <= 3; 151EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3; 152SELECT * FROM t1 WHERE a <= 4; 153EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4; 154SELECT * FROM t1 WHERE a <= 5; 155EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5; 156SELECT * FROM t1 WHERE a <= 6; 157EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; 158SELECT * FROM t1 WHERE a = 1; 159EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1; 160SELECT * FROM t1 WHERE a = 2; 161EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2; 162SELECT * FROM t1 WHERE a = 3; 163EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3; 164SELECT * FROM t1 WHERE a = 4; 165EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4; 166SELECT * FROM t1 WHERE a = 5; 167EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5; 168SELECT * FROM t1 WHERE a = 6; 169EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6; 170SELECT * FROM t1 WHERE a >= 1; 171EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1; 172SELECT * FROM t1 WHERE a >= 2; 173EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2; 174SELECT * FROM t1 WHERE a >= 3; 175EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3; 176SELECT * FROM t1 WHERE a >= 4; 177EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4; 178SELECT * FROM t1 WHERE a >= 5; 179EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5; 180SELECT * FROM t1 WHERE a >= 6; 181EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6; 182SELECT * FROM t1 WHERE a > 1; 183EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1; 184SELECT * FROM t1 WHERE a > 2; 185EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2; 186SELECT * FROM t1 WHERE a > 3; 187EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3; 188SELECT * FROM t1 WHERE a > 4; 189EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4; 190SELECT * FROM t1 WHERE a > 5; 191EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5; 192SELECT * FROM t1 WHERE a > 6; 193EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6; 194DROP TABLE t1; 195 196# 197# Bug#20577: Partitions: use of to_days() function leads to selection failures 198# 199--let $explain_partitions= 1; 200--let $verify_without_partitions= 0; 201--echo # test of RANGE and index 202CREATE TABLE t1 (a DATE, KEY(a)) 203PARTITION BY RANGE (TO_DAYS(a)) 204(PARTITION `pNULL` VALUES LESS THAN (0), 205 PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1), 206 PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1), 207 PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1)); 208if ($verify_without_partitions) 209{ 210ALTER TABLE t1 REMOVE PARTITIONING; 211} 212INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), 213 ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); 214--source include/partition_date_range.inc 215--echo # test without index 216ALTER TABLE t1 DROP KEY a; 217--source include/partition_date_range.inc 218DROP TABLE t1; 219 220--echo # test of LIST and index 221CREATE TABLE t1 (a DATE, KEY(a)) 222PARTITION BY LIST (TO_DAYS(a)) 223(PARTITION `p0001-01-01` VALUES IN (TO_DAYS('0001-01-01')), 224 PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')), 225 PARTITION `pNULL` VALUES IN (NULL), 226 PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')), 227 PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01'))); 228if ($verify_without_partitions) 229{ 230ALTER TABLE t1 REMOVE PARTITIONING; 231} 232INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), 233 ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); 234--source include/partition_date_range.inc 235--echo # test without index 236ALTER TABLE t1 DROP KEY a; 237--source include/partition_date_range.inc 238DROP TABLE t1; 239--echo # TO_SECONDS, test of LIST and index 240CREATE TABLE t1 (a DATE, KEY(a)) 241PARTITION BY LIST (TO_SECONDS(a)) 242(PARTITION `p0001-01-01` VALUES IN (TO_SECONDS('0001-01-01')), 243 PARTITION `p2001-01-01` VALUES IN (TO_SECONDS('2001-01-01')), 244 PARTITION `pNULL` VALUES IN (NULL), 245 PARTITION `p0000-01-02` VALUES IN (TO_SECONDS('0000-01-02')), 246 PARTITION `p1001-01-01` VALUES IN (TO_SECONDS('1001-01-01'))); 247if ($verify_without_partitions) 248{ 249ALTER TABLE t1 REMOVE PARTITIONING; 250} 251INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), 252 ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); 253--source include/partition_date_range.inc 254--echo # test without index 255ALTER TABLE t1 DROP KEY a; 256--source include/partition_date_range.inc 257DROP TABLE t1; 258 259 260# 261# Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) 262# There is a problem when comparing DATE with DATETIME. 263# In pruning it is converted into the field type 264# and in row evaluation it is converted to longlong 265# (like a DATETIME). 266--echo # Test with DATETIME column NOT NULL 267CREATE TABLE t1 ( 268 a int(10) unsigned NOT NULL, 269 b DATETIME NOT NULL, 270 PRIMARY KEY (a, b) 271) PARTITION BY RANGE (TO_DAYS(b)) 272(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 273 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 274 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 275 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 276 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 277INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), 278 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), 279 (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), 280 (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); 281EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); 282EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); 283EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); 284EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); 285EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); 286EXPLAIN PARTITIONS SELECT * FROM t1 287 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); 288EXPLAIN PARTITIONS SELECT * FROM t1 289 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); 290EXPLAIN PARTITIONS SELECT * FROM t1 291 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); 292EXPLAIN PARTITIONS SELECT * FROM t1 293 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); 294EXPLAIN PARTITIONS SELECT * FROM t1 295 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); 296EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); 297EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); 298EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); 299EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); 300EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); 301EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; 302EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; 303EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; 304EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; 305EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; 306EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; 307EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; 308EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; 309EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; 310EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; 311EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; 312EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; 313EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; 314EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; 315EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; 316EXPLAIN PARTITIONS SELECT * FROM t1 317 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); 318EXPLAIN PARTITIONS SELECT * FROM t1 319 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); 320EXPLAIN PARTITIONS SELECT * FROM t1 321 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); 322EXPLAIN PARTITIONS SELECT * FROM t1 323 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); 324EXPLAIN PARTITIONS SELECT * FROM t1 325 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); 326EXPLAIN PARTITIONS SELECT * FROM t1 327 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); 328EXPLAIN PARTITIONS SELECT * FROM t1 329 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); 330EXPLAIN PARTITIONS SELECT * FROM t1 331 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); 332EXPLAIN PARTITIONS SELECT * FROM t1 333 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); 334EXPLAIN PARTITIONS SELECT * FROM t1 335 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); 336DROP TABLE t1; 337 338--echo # Test with DATE column NOT NULL 339CREATE TABLE t1 ( 340 a int(10) unsigned NOT NULL, 341 b DATE NOT NULL, 342 PRIMARY KEY (a, b) 343) PARTITION BY RANGE (TO_DAYS(b)) 344(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 345 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 346 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 347 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 348 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 349INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), 350 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), 351 (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), 352 (1, '2009-04-07'); 353EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); 354EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); 355EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); 356EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); 357EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); 358EXPLAIN PARTITIONS SELECT * FROM t1 359 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); 360EXPLAIN PARTITIONS SELECT * FROM t1 361 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); 362EXPLAIN PARTITIONS SELECT * FROM t1 363 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); 364EXPLAIN PARTITIONS SELECT * FROM t1 365 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); 366EXPLAIN PARTITIONS SELECT * FROM t1 367 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); 368EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); 369EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); 370EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); 371EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); 372EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); 373EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; 374EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; 375EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; 376EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; 377EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; 378EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; 379EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; 380EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; 381EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; 382EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; 383EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; 384EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; 385EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; 386EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; 387EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; 388EXPLAIN PARTITIONS SELECT * FROM t1 389 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); 390EXPLAIN PARTITIONS SELECT * FROM t1 391 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); 392EXPLAIN PARTITIONS SELECT * FROM t1 393 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); 394EXPLAIN PARTITIONS SELECT * FROM t1 395 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); 396EXPLAIN PARTITIONS SELECT * FROM t1 397 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); 398EXPLAIN PARTITIONS SELECT * FROM t1 399 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); 400EXPLAIN PARTITIONS SELECT * FROM t1 401 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); 402EXPLAIN PARTITIONS SELECT * FROM t1 403 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); 404EXPLAIN PARTITIONS SELECT * FROM t1 405 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); 406EXPLAIN PARTITIONS SELECT * FROM t1 407 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); 408DROP TABLE t1; 409 410--echo # Test with DATETIME column NULL 411CREATE TABLE t1 ( 412 a int(10) unsigned NOT NULL, 413 b DATETIME NULL 414) PARTITION BY RANGE (TO_DAYS(b)) 415(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 416 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 417 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 418 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 419 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 420INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), 421 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), 422 (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), 423 (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); 424EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); 425EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); 426EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); 427EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); 428EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); 429EXPLAIN PARTITIONS SELECT * FROM t1 430 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); 431EXPLAIN PARTITIONS SELECT * FROM t1 432 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); 433EXPLAIN PARTITIONS SELECT * FROM t1 434 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); 435EXPLAIN PARTITIONS SELECT * FROM t1 436 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); 437EXPLAIN PARTITIONS SELECT * FROM t1 438 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); 439EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); 440EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); 441EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); 442EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); 443EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); 444EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; 445EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; 446EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; 447EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; 448EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; 449EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; 450EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; 451EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; 452EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; 453EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; 454EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; 455EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; 456EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; 457EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; 458EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; 459EXPLAIN PARTITIONS SELECT * FROM t1 460 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); 461EXPLAIN PARTITIONS SELECT * FROM t1 462 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); 463EXPLAIN PARTITIONS SELECT * FROM t1 464 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); 465EXPLAIN PARTITIONS SELECT * FROM t1 466 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); 467EXPLAIN PARTITIONS SELECT * FROM t1 468 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); 469EXPLAIN PARTITIONS SELECT * FROM t1 470 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); 471EXPLAIN PARTITIONS SELECT * FROM t1 472 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); 473EXPLAIN PARTITIONS SELECT * FROM t1 474 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); 475EXPLAIN PARTITIONS SELECT * FROM t1 476 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); 477EXPLAIN PARTITIONS SELECT * FROM t1 478 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); 479DROP TABLE t1; 480 481--echo # Test with DATE column NULL 482CREATE TABLE t1 ( 483 a int(10) unsigned NOT NULL, 484 b DATE NULL 485) PARTITION BY RANGE (TO_DAYS(b)) 486(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 487 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 488 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 489 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 490 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 491INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), 492 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), 493 (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), 494 (1, '2009-04-07'); 495EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); 496EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); 497EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); 498EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); 499EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); 500EXPLAIN PARTITIONS SELECT * FROM t1 501 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); 502EXPLAIN PARTITIONS SELECT * FROM t1 503 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); 504EXPLAIN PARTITIONS SELECT * FROM t1 505 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); 506EXPLAIN PARTITIONS SELECT * FROM t1 507 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); 508EXPLAIN PARTITIONS SELECT * FROM t1 509 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); 510EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); 511EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); 512EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); 513EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); 514EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); 515EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; 516EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; 517EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; 518EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; 519EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; 520EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; 521EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; 522EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; 523EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; 524EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; 525EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; 526EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; 527EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; 528EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; 529EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; 530EXPLAIN PARTITIONS SELECT * FROM t1 531 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); 532EXPLAIN PARTITIONS SELECT * FROM t1 533 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); 534EXPLAIN PARTITIONS SELECT * FROM t1 535 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); 536EXPLAIN PARTITIONS SELECT * FROM t1 537 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); 538EXPLAIN PARTITIONS SELECT * FROM t1 539 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); 540EXPLAIN PARTITIONS SELECT * FROM t1 541 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); 542EXPLAIN PARTITIONS SELECT * FROM t1 543 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); 544EXPLAIN PARTITIONS SELECT * FROM t1 545 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); 546EXPLAIN PARTITIONS SELECT * FROM t1 547 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); 548EXPLAIN PARTITIONS SELECT * FROM t1 549 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); 550DROP TABLE t1; 551 552--echo # For better code coverage of the patch 553CREATE TABLE t1 ( 554 a int(10) unsigned NOT NULL, 555 b DATE 556) PARTITION BY RANGE ( TO_DAYS(b) ) 557(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 558 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 559 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 560 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 561 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 562INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL); 563--echo # test with an invalid date, which lead to item->null_value is set. 564EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME); 565DROP TABLE t1; 566 567# 568# Bug#40972: some sql execution lead the whole database crashing 569# 570# Setup so the start is at partition pX and end is at p1 571# Pruning does handle 'bad' dates differently. 572CREATE TABLE t1 573(a INT NOT NULL AUTO_INCREMENT, 574 b DATETIME, 575 PRIMARY KEY (a,b), 576 KEY (b)) 577PARTITION BY RANGE (to_days(b)) 578(PARTITION p0 VALUES LESS THAN (733681) COMMENT = 'LESS THAN 2008-10-01', 579 PARTITION p1 VALUES LESS THAN (733712) COMMENT = 'LESS THAN 2008-11-01', 580 PARTITION pX VALUES LESS THAN MAXVALUE); 581SELECT a,b FROM t1 WHERE b >= '2008-12-01' AND b < '2009-12-00'; 582DROP TABLE t1; 583 584# Check if we can infer from condition on partition fields that 585# no records will match. 586create table t1 ( a int not null) partition by hash(a) partitions 2; 587insert into t1 values (1),(2),(3); 588explain select * from t1 where a=5 and a=6; 589drop table t1; 590 591# Simple HASH partitioning 592create table t1 ( 593 a int(11) not null 594) partition by hash (a) partitions 2; 595insert into t1 values (1),(2),(3); 596 597explain partitions select * from t1 where a=1; 598explain partitions select * from t1 where a=2; 599explain partitions select * from t1 where a=1 or a=2; 600 601# Partitioning over several fields 602create table t2 ( 603 a int not null, 604 b int not null 605) partition by key(a,b) partitions 2; 606insert into t2 values (1,1),(2,2),(3,3); 607 608explain partitions select * from t2 where a=1; 609explain partitions select * from t2 where b=1; 610 611explain partitions select * from t2 where a=1 and b=1; 612 613# RANGE(expr) partitioning 614create table t3 ( 615 a int 616) 617partition by range (a*1) ( 618 partition p0 values less than (10), 619 partition p1 values less than (20) 620); 621insert into t3 values (5),(15); 622 623explain partitions select * from t3 where a=11; 624explain partitions select * from t3 where a=10; 625explain partitions select * from t3 where a=20; 626explain partitions select * from t3 where a=30; 627 628# LIST(expr) partitioning 629create table t4 (a int not null, b int not null) partition by LIST (a+b) ( 630 partition p0 values in (12), 631 partition p1 values in (14) 632); 633insert into t4 values (10,2), (10,4); 634 635# empty OR one 636explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2); 637 638# empty OR one OR empty 639explain partitions select * from t4 640 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3); 641 642# one OR empty OR one 643explain partitions select * from t4 where (a=10 and b=2) or (a=10 and b=3) 644 or (a=10 and b = 4); 645 646# empty OR full 647explain partitions select * from t4 where (a=10 and b=1) or a=11; 648 649# one OR full 650explain partitions select * from t4 where (a=10 and b=2) or a=11; 651 652drop table t1, t2, t3, t4; 653 654# LIST(expr)/HASH subpartitioning. 655create table t5 (a int not null, b int not null, 656 c int not null, d int not null) 657partition by LIST(a+b) subpartition by HASH (c+d) subpartitions 2 658( 659 partition p0 values in (12), 660 partition p1 values in (14) 661); 662 663insert into t5 values (10,2,0,0), (10,4,0,0), (10,2,0,1), (10,4,0,1); 664explain partitions select * from t5; 665 666# empty OR one OR empty 667explain partitions select * from t5 668 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3); 669 670# one OR empty OR one 671explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3) 672 or (a=10 and b = 4); 673 674# conditions on subpartitions only 675explain partitions select * from t5 where (c=1 and d=1); 676explain partitions select * from t5 where (c=2 and d=1); 677 678# mixed partition/subpartitions. 679explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or 680(c=2 and d=1); 681 682# same as above 683explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or 684(b=2 and c=2 and d=1); 685 686# LIST(field) partitioning, interval analysis. 687create table t6 (a int not null) partition by LIST(a) ( 688 partition p1 values in (1), 689 partition p3 values in (3), 690 partition p5 values in (5), 691 partition p7 values in (7), 692 partition p9 values in (9) 693); 694insert into t6 values (1),(3),(5); 695 696explain partitions select * from t6 where a < 1; 697explain partitions select * from t6 where a <= 1; 698explain partitions select * from t6 where a > 9; 699explain partitions select * from t6 where a >= 9; 700 701explain partitions select * from t6 where a > 0 and a < 5; 702explain partitions select * from t6 where a > 5 and a < 12; 703explain partitions select * from t6 where a > 3 and a < 8 ; 704 705explain partitions select * from t6 where a >= 0 and a <= 5; 706explain partitions select * from t6 where a >= 5 and a <= 12; 707explain partitions select * from t6 where a >= 3 and a <= 8; 708 709explain partitions select * from t6 where a > 3 and a < 5; 710 711drop table t6; 712 713create table t6 (a int unsigned not null) partition by LIST(a) ( 714 partition p1 values in (1), 715 partition p3 values in (3), 716 partition p5 values in (5), 717 partition p7 values in (7), 718 partition p9 values in (9) 719); 720insert into t6 values (1),(3),(5); 721 722explain partitions select * from t6 where a < 1; 723explain partitions select * from t6 where a <= 1; 724explain partitions select * from t6 where a > 9; 725explain partitions select * from t6 where a >= 9; 726 727explain partitions select * from t6 where a > 0 and a < 5; 728explain partitions select * from t6 where a > 5 and a < 12; 729explain partitions select * from t6 where a > 3 and a < 8 ; 730 731explain partitions select * from t6 where a >= 0 and a <= 5; 732explain partitions select * from t6 where a >= 5 and a <= 12; 733explain partitions select * from t6 where a >= 3 and a <= 8; 734 735explain partitions select * from t6 where a > 3 and a < 5; 736 737# RANGE(field) partitioning, interval analysis. 738create table t7 (a int not null) partition by RANGE(a) ( 739 partition p10 values less than (10), 740 partition p30 values less than (30), 741 partition p50 values less than (50), 742 partition p70 values less than (70), 743 partition p90 values less than (90) 744); 745insert into t7 values (10),(30),(50); 746 747# leftmost intervals 748explain partitions select * from t7 where a < 5; 749explain partitions select * from t7 where a < 9; 750explain partitions select * from t7 where a <= 9; 751explain partitions select * from t7 where a = 9; 752explain partitions select * from t7 where a >= 9; 753explain partitions select * from t7 where a > 9; 754explain partitions select * from t7 where a < 10; 755explain partitions select * from t7 where a <= 10; 756explain partitions select * from t7 where a = 10; 757explain partitions select * from t7 where a >= 10; 758explain partitions select * from t7 where a > 10; 759 760#rightmost intervals 761explain partitions select * from t7 where a < 89; 762explain partitions select * from t7 where a <= 89; 763explain partitions select * from t7 where a = 89; 764explain partitions select * from t7 where a > 89; 765explain partitions select * from t7 where a >= 89; 766explain partitions select * from t7 where a < 90; 767explain partitions select * from t7 where a <= 90; 768explain partitions select * from t7 where a = 90; 769explain partitions select * from t7 where a > 90; 770explain partitions select * from t7 where a >= 90; 771explain partitions select * from t7 where a > 91; 772 773# misc intervals 774explain partitions select * from t7 where a > 11 and a < 29; 775 776drop table t7; 777 778create table t7 (a int unsigned not null) partition by RANGE(a) ( 779 partition p10 values less than (10), 780 partition p30 values less than (30), 781 partition p50 values less than (50), 782 partition p70 values less than (70), 783 partition p90 values less than (90) 784); 785insert into t7 values (10),(30),(50); 786 787# leftmost intervals 788explain partitions select * from t7 where a < 5; 789explain partitions select * from t7 where a < 9; 790explain partitions select * from t7 where a <= 9; 791explain partitions select * from t7 where a = 9; 792explain partitions select * from t7 where a >= 9; 793explain partitions select * from t7 where a > 9; 794explain partitions select * from t7 where a < 10; 795explain partitions select * from t7 where a <= 10; 796explain partitions select * from t7 where a = 10; 797explain partitions select * from t7 where a >= 10; 798explain partitions select * from t7 where a > 10; 799 800#rightmost intervals 801explain partitions select * from t7 where a < 89; 802explain partitions select * from t7 where a <= 89; 803explain partitions select * from t7 where a = 89; 804explain partitions select * from t7 where a > 89; 805explain partitions select * from t7 where a >= 89; 806explain partitions select * from t7 where a < 90; 807explain partitions select * from t7 where a <= 90; 808explain partitions select * from t7 where a = 90; 809explain partitions select * from t7 where a > 90; 810explain partitions select * from t7 where a >= 90; 811explain partitions select * from t7 where a > 91; 812 813# misc intervals 814explain partitions select * from t7 where a > 11 and a < 29; 815 816# LIST(monontonic_func) partitioning 817create table t8 (a date not null) partition by RANGE(YEAR(a)) ( 818 partition p0 values less than (1980), 819 partition p1 values less than (1990), 820 partition p2 values less than (2000) 821); 822insert into t8 values ('1985-05-05'),('1995-05-05'); 823 824explain partitions select * from t8 where a < '1980-02-02'; 825 826# LIST(strict_monotonic_func) partitioning 827create table t9 (a date not null) partition by RANGE(TO_DAYS(a)) ( 828 partition p0 values less than (732299), -- 2004-12-19 829 partition p1 values less than (732468), -- 2005-06-06 830 partition p2 values less than (732664) -- 2005-12-19 831); 832insert into t9 values ('2005-05-05'), ('2005-04-04'); 833 834explain partitions select * from t9 where a < '2004-12-19'; 835explain partitions select * from t9 where a <= '2004-12-19'; 836 837drop table t5,t6,t7,t8,t9; 838 839# Test the case where we can't create partitioning 'index' 840# 841# Not supported after bug#18198 is fixed 842# 843#create table t1 (a enum('a','b','c','d') default 'a') 844# partition by hash (ascii(a)) partitions 2; 845#insert into t1 values ('a'),('b'),('c'); 846#explain partitions select * from t1 where a='b'; 847#drop table t1; 848 849# 850# Test cases for bugs found in code review: 851# 852create table t1 ( 853 a1 int not null 854) 855partition by range (a1) ( 856 partition p0 values less than (3), 857 partition p1 values less than (6), 858 partition p2 values less than (9) 859); 860insert into t1 values (1),(2),(3); 861explain partitions select * from t1 where a1 > 3; 862explain partitions select * from t1 where a1 >= 3; 863 864explain partitions select * from t1 where a1 < 3 and a1 > 3; 865drop table t1; 866 867# 868create table t3 (a int, b int) 869 partition by list(a) subpartition by hash(b) subpartitions 4 ( 870 partition p0 values in (1), 871 partition p1 values in (2), 872 partition p2 values in (3), 873 partition p3 values in (4) 874 ); 875insert into t3 values (1,1),(2,2),(3,3); 876 877explain partitions select * from t3 where a=2 or b=1; 878explain partitions select * from t3 where a=4 or b=2; 879explain partitions select * from t3 where (a=2 or b=1) and (a=4 or b=2) ; 880drop table t3; 881 882# Test for NULLs 883create table t1 (a int) partition by hash(a) partitions 2; 884insert into t1 values (1),(2); 885explain partitions select * from t1 where a is null; 886 887# this uses both partitions 888explain partitions select * from t1 where a is not null; 889drop table t1; 890 891# Join tests 892create table t1 (a int not null, b int not null, key(a), key(b)) 893 partition by hash(a) partitions 4; 894insert into t1 values (1,1),(2,2),(3,3),(4,4); 895insert into t1 values (5,5),(6,6),(7,7),(8,8); 896insert into t1 values (9,9),(10,10),(11,11),(12,12); 897insert into t1 values (13,13),(14,14),(15,15),(16,16); 898 899explain partitions 900select * from t1 X, t1 Y 901where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3); 902 903explain partitions 904select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2); 905 906drop table t1; 907 908# Tests for "short ranges" 909create table t1 (a int) partition by hash(a) partitions 20; 910insert into t1 values (1),(2),(3); 911explain partitions select * from t1 where a > 1 and a < 3; 912explain partitions select * from t1 where a >= 1 and a < 3; 913explain partitions select * from t1 where a > 1 and a <= 3; 914explain partitions select * from t1 where a >= 1 and a <= 3; 915drop table t1; 916 917create table t1 (a int, b int) 918 partition by list(a) subpartition by hash(b) subpartitions 20 919( 920 partition p0 values in (0), 921 partition p1 values in (1), 922 partition p2 values in (2), 923 partition p3 values in (3) 924); 925insert into t1 values (1,1),(2,2),(3,3); 926 927explain partitions select * from t1 where b > 1 and b < 3; 928explain partitions select * from t1 where b > 1 and b < 3 and (a =1 or a =2); 929drop table t1; 930 931# Test partition pruning for single-table UPDATE/DELETE. 932# TODO: Currently we test only "all partitions pruned away" case. Add more 933# tests when the patch that makes use of partition pruning results at 934# execution phase is pushed. 935 936create table t1 (a int) partition by list(a) ( 937 partition p0 values in (1,2), 938 partition p1 values in (3,4) 939); 940insert into t1 values (1),(1),(2),(2),(3),(4),(3),(4); 941 942# This won't do any table access 943flush status; 944update t1 set a=100 where a=5; 945show status like 'Handler_read_rnd_next'; 946 947# ... as compared to this, which will scan both partitions 948flush status; 949update t1 set a=100 where a+1=5+1; 950show status like 'Handler_read_rnd_next'; 951 952# Same as above for DELETE: 953flush status; 954delete from t1 where a=5; 955show status like 'Handler_read_rnd_next'; 956 957flush status; 958delete from t1 where a+1=5+1; 959show status like 'Handler_read_rnd_next'; 960 961# Same as above multi-table UPDATE/DELETE 962create table t2 like t1; 963insert into t2 select * from t2; 964 965flush status; 966update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5; 967show status like 'Handler_read_rnd_next'; 968# ^ This shows 3 accesses, these are caused by const table reads. 969# They should vanish when partition pruning results are used. 970 971flush status; 972delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5; 973show status like 'Handler_read_rnd_next'; 974drop table t1,t2; 975 976# 977# WL#2986 Tests (Checking if partition pruning results are used at query 978# execution phase) 979# 980CREATE TABLE `t1` ( 981 `a` int(11) default NULL 982); 983INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 984 985CREATE TABLE `t2` ( 986 `a` int(11) default NULL, 987 KEY `a` (`a`) 988) ; 989 990insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ; 991insert into t1 select a from t2; 992 993drop table t2; 994CREATE TABLE `t2` ( 995 `a` int(11) default NULL, 996 `b` int(11) default NULL 997) 998PARTITION BY RANGE (a) ( 999PARTITION p0 VALUES LESS THAN (200), 1000PARTITION p1 VALUES LESS THAN (400), 1001PARTITION p2 VALUES LESS THAN (600), 1002PARTITION p3 VALUES LESS THAN (800), 1003PARTITION p4 VALUES LESS THAN (1001)); 1004 1005insert into t2 select a,1 from t1 where a < 200; 1006insert into t2 select a,2 from t1 where a >= 200 and a < 400; 1007insert into t2 select a,3 from t1 where a >= 400 and a < 600; 1008insert into t2 select a,4 from t1 where a >= 600 and a < 800; 1009insert into t2 select a,5 from t1 where a >= 800 and a < 1001; 1010 1011explain partitions select * from t2; 1012explain partitions select * from t2 where a < 801 and a > 200; 1013explain partitions select * from t2 where a < 801 and a > 800; 1014explain partitions select * from t2 where a > 600; 1015explain partitions select * from t2 where a > 600 and b = 1; 1016explain partitions select * from t2 where a > 600 and b = 4; 1017explain partitions select * from t2 where a > 600 and b = 5; 1018explain partitions select * from t2 where b = 5; 1019 1020flush status; 1021update t2 set b = 100 where b = 6; 1022show status like 'Handler_read_rnd_next'; 1023flush status; 1024update t2 set a = 1002 where a = 1001; 1025show status like 'Handler_read_rnd_next'; 1026flush status; 1027update t2 set b = 6 where a = 600; 1028show status like 'Handler_read_rnd_next'; 1029flush status; 1030update t2 set b = 6 where a > 600 and a < 800; 1031show status like 'Handler_read_rnd_next'; 1032flush status; 1033delete from t2 where a > 600; 1034show status like 'Handler_read_rnd_next'; 1035 1036drop table t2; 1037CREATE TABLE `t2` ( 1038 `a` int(11) default NULL, 1039 `b` int(11) default NULL, 1040 index (b) 1041) 1042PARTITION BY RANGE (a) ( 1043PARTITION p0 VALUES LESS THAN (200), 1044PARTITION p1 VALUES LESS THAN (400), 1045PARTITION p2 VALUES LESS THAN (600), 1046PARTITION p3 VALUES LESS THAN (800), 1047PARTITION p4 VALUES LESS THAN (1001)); 1048 1049insert into t2 select a,1 from t1 where a < 100; 1050insert into t2 select a,2 from t1 where a >= 200 and a < 300; 1051insert into t2 select a,3 from t1 where a >= 300 and a < 400; 1052insert into t2 select a,4 from t1 where a >= 400 and a < 500; 1053insert into t2 select a,5 from t1 where a >= 500 and a < 600; 1054insert into t2 select a,6 from t1 where a >= 600 and a < 700; 1055insert into t2 select a,7 from t1 where a >= 700 and a < 800; 1056insert into t2 select a,8 from t1 where a >= 800 and a < 900; 1057insert into t2 select a,9 from t1 where a >= 900 and a < 1001; 1058 1059explain partitions select * from t2; 1060# not using indexes 1061explain partitions select * from t2 where a = 101; 1062explain partitions select * from t2 where a = 550; 1063explain partitions select * from t2 where a = 833; 1064explain partitions select * from t2 where (a = 100 OR a = 900); 1065explain partitions select * from t2 where (a > 100 AND a < 600); 1066explain partitions select * from t2 where b = 4; 1067 1068explain extended select * from t2 where b = 6; 1069explain partitions select * from t2 where b = 6; 1070 1071explain extended select * from t2 where b in (1,3,5); 1072explain partitions select * from t2 where b in (1,3,5); 1073 1074explain extended select * from t2 where b in (2,4,6); 1075explain partitions select * from t2 where b in (2,4,6); 1076 1077explain extended select * from t2 where b in (7,8,9); 1078explain partitions select * from t2 where b in (7,8,9); 1079 1080explain extended select * from t2 where b > 5; 1081explain partitions select * from t2 where b > 5; 1082 1083explain extended select * from t2 where b > 5 and b < 8; 1084explain partitions select * from t2 where b > 5 and b < 8; 1085 1086explain extended select * from t2 where b > 5 and b < 7; 1087explain partitions select * from t2 where b > 5 and b < 7; 1088 1089explain extended select * from t2 where b > 0 and b < 5; 1090explain partitions select * from t2 where b > 0 and b < 5; 1091 1092flush status; 1093update t2 set a = 111 where b = 10; 1094show status like 'Handler_read_rnd_next'; 1095show status like 'Handler_read_key'; 1096flush status; 1097update t2 set a = 111 where b in (5,6); 1098show status like 'Handler_read_rnd_next'; 1099show status like 'Handler_read_key'; 1100flush status; 1101update t2 set a = 222 where b = 7; 1102show status like 'Handler_read_rnd_next'; 1103show status like 'Handler_read_key'; 1104flush status; 1105delete from t2 where b = 7; 1106show status like 'Handler_read_rnd_next'; 1107show status like 'Handler_read_key'; 1108flush status; 1109delete from t2 where b > 5; 1110show status like 'Handler_read_rnd_next'; 1111show status like 'Handler_read_rnd_deleted'; 1112show status like 'Handler_read_key'; 1113show status like 'Handler_read_prev'; 1114show status like 'Handler_read_next'; 1115flush status; 1116delete from t2 where b < 5 or b > 3; 1117show status like 'Handler_read_rnd_next'; 1118show status like 'Handler_read_rnd_deleted'; 1119show status like 'Handler_read_key'; 1120show status like 'Handler_read_prev'; 1121show status like 'Handler_read_next'; 1122 1123drop table t1, t2; 1124 1125# BUG#18025 1126# part1: mediumint columns 1127create table t1 ( f_int1 mediumint, f_int2 integer) 1128partition by list(mod(f_int1,4)) ( 1129 partition p_3 values in (-3), 1130 partition p_2 values in (-2), 1131 partition p_1 values in (-1), 1132 partition p0 values in (0), 1133 partition p1 values in (1), 1134 partition p2 values in (2), 1135 partition p3 values in (3) 1136); 1137 1138insert into t1 values (9, 9), (8, 8), (7, 7), (6, 6), (5, 5), 1139 (4, 4), (3, 3), (2, 2), (1, 1); 1140select * from t1 where f_int1 between 5 and 15 order by f_int1; 1141 1142drop table t1; 1143 1144# part2: bug in pruning code 1145#create table t1 (a char(10) binary) 1146#partition by list(ascii(a)) 1147# (partition p1 values in (ascii('a')), 1148# partition p2 values in (ascii('b')), 1149# partition p3 values in (ascii('c')), 1150# partition p4 values in (ascii('d')), 1151# partition p5 values in (ascii('e'))); 1152#insert into t1 values ('a'),('bb'),('ccc'),('dddd'),('eeEee'); 1153#select * from t1 where a>='a' and a <= 'dddd'; 1154#explain partitions select * from t1 where a>='a' and a <= 'dddd'; 1155#drop table t1; 1156 1157# BUG#18659: Assertion failure when subpartitioning is used and partition is 1158# "IS NULL" 1159create table t1 (f_int1 integer) partition by list(abs(mod(f_int1,2))) 1160 subpartition by hash(f_int1) subpartitions 2 1161( 1162 partition part1 values in (0), 1163 partition part2 values in (1), 1164 partition part4 values in (null) 1165); 1166insert into t1 set f_int1 = null; 1167 1168select * from t1 where f_int1 is null; 1169explain partitions select * from t1 where f_int1 is null; 1170drop table t1; 1171 1172# 1173# BUG#18558 1174# 1175create table t1 (a int not null, b int not null) 1176partition by list(a) 1177 subpartition by hash(b) subpartitions 4 1178( 1179 partition p0 values in (1), 1180 partition p1 values in (2), 1181 partition p2 values in (3) 1182); 1183insert into t1 values (1,1),(1,2),(1,3),(1,4), 1184 (2,1),(2,2),(2,3),(2,4); 1185explain partitions select * from t1 where a=1 AND (b=1 OR b=2); 1186drop table t1; 1187 1188create table t1 (a int, b int not null) 1189partition by list(a) 1190 subpartition by hash(b) subpartitions 2 1191( 1192 partition p0 values in (1), 1193 partition p1 values in (2), 1194 partition p2 values in (3), 1195 partition pn values in (NULL) 1196); 1197insert into t1 values (1,1),(1,2),(1,3),(1,4), 1198 (2,1),(2,2),(2,3),(2,4), (NULL,1); 1199 1200explain partitions select * from t1 where a IS NULL AND (b=1 OR b=2); 1201 1202explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2); 1203explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2); 1204explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2); 1205 1206drop table t1; 1207 1208create table t1 ( a int) partition by list (MOD(a, 10)) 1209( partition p0 values in (0), partition p1 values in (1), 1210 partition p2 values in (2), partition p3 values in (3), 1211 partition p4 values in (4), partition p5 values in (5), 1212 partition p6 values in (6), partition pn values in (NULL) 1213); 1214insert into t1 values (NULL), (0),(1),(2),(3),(4),(5),(6); 1215explain partitions select * from t1 where a is null or a < 2; 1216drop table t1; 1217 1218# Testcase from BUG#18751 1219create table t1 (s1 int) partition by list (s1) 1220 (partition p1 values in (0), 1221 partition p2 values in (1), 1222 partition p3 values in (null)); 1223 1224insert into t1 values (0),(1),(null); 1225 1226select count(*) from t1 where s1 < 0 or s1 is null; 1227explain partitions select count(*) from t1 where s1 < 0 or s1 is null; 1228drop table t1; 1229 1230# No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447 1231# being fixed. 1232 1233# 1234# BUG#17946 Like searches fail with partitioning 1235# 1236create table t1 (a char(32) primary key) 1237partition by key() 1238partitions 100; 1239insert into t1 values ('na'); 1240select * from t1; 1241select * from t1 where a like 'n%'; 1242drop table t1; 1243 1244 1245# BUG#19055 Crashes for varchar_col=NUMBER or varchar_col IS NULL 1246create table t1 (s1 varchar(15)) partition by key (s1); 1247select * from t1 where s1 = 0 or s1 is null; 1248insert into t1 values ('aa'),('bb'),('0'); 1249explain partitions select * from t1 where s1 = 0 or s1 is null; 1250drop table t1; 1251 1252# 1253# BUG#19684: EXPLAIN PARTITIONS produces garbage in 'partitions' column when 1254# the length of string to be displayed exceeds some limit. 1255create table t2 (a int, b int) 1256 partition by LIST(a) 1257 subpartition by HASH(b) subpartitions 40 1258( partition p_0_long_partition_name values in(1), 1259 partition p_1_long_partition_name values in(2)); 1260 1261insert into t2 values (1,1),(2,2); 1262 1263--vertical_results 1264explain partitions select * from t2; 1265--horizontal_results 1266drop table t2; 1267 1268 1269# BUG#20484 "Partitions: crash with explain and union" 1270create table t1 (s1 int); 1271explain partitions select 1 from t1 union all select 2; 1272drop table t1; 1273 1274 1275# BUG#20257: partition pruning test coverage for BIGINT UNSIGNED 1276create table t1 (a bigint unsigned not null) partition by range(a) ( 1277 partition p0 values less than (10), 1278 partition p1 values less than (100), 1279 partition p2 values less than (1000), 1280 partition p3 values less than (18446744073709551000), 1281 partition p4 values less than (18446744073709551614) 1282); 1283insert into t1 values (5),(15),(105),(1005); 1284insert into t1 values (18446744073709551000+1); 1285insert into t1 values (18446744073709551614-1); 1286 1287explain partitions select * from t1 where a < 10; 1288explain partitions select * from t1 1289 where a >= 18446744073709551000-1 and a <= 18446744073709551000+1; 1290 1291explain partitions select * from t1 1292 where a between 18446744073709551001 and 18446744073709551002; 1293 1294explain partitions select * from t1 where a = 18446744073709551000; 1295explain partitions select * from t1 where a = 18446744073709551613; 1296explain partitions select * from t1 where a = 18446744073709551614; 1297drop table t1; 1298# 1299# Test all variants of usage for interval_via_mapping 1300# and interval_via_walking 1301# 1302# t1 will use interval_via_mapping since it uses a 1303# monotonic function, whereas t2 will use 1304# interval_via_walking since the intervals are short 1305# and the function isn't monotonic (it is, but it isn't 1306# discovered in this version). 1307# 1308 create table t1 (a int) 1309 partition by range(a) ( 1310 partition p0 values less than (64), 1311 partition p1 values less than (128), 1312 partition p2 values less than (255) 1313); 1314 1315create table t2 (a int) 1316 partition by range(a+0) ( 1317 partition p0 values less than (64), 1318 partition p1 values less than (128), 1319 partition p2 values less than (255) 1320); 1321 1322insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE); 1323insert into t2 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE); 1324explain partitions select * from t1 where a=0; 1325explain partitions select * from t2 where a=0; 1326explain partitions select * from t1 where a=0xFE; 1327explain partitions select * from t2 where a=0xFE; 1328explain partitions select * from t1 where a > 0xFE AND a <= 0xFF; 1329explain partitions select * from t2 where a > 0xFE AND a <= 0xFF; 1330explain partitions select * from t1 where a >= 0xFE AND a <= 0xFF; 1331explain partitions select * from t2 where a >= 0xFE AND a <= 0xFF; 1332explain partitions select * from t1 where a < 64 AND a >= 63; 1333explain partitions select * from t2 where a < 64 AND a >= 63; 1334explain partitions select * from t1 where a <= 64 AND a >= 63; 1335explain partitions select * from t2 where a <= 64 AND a >= 63; 1336drop table t1; 1337drop table t2; 1338 1339create table t1(a bigint unsigned not null) partition by range(a+0) ( 1340 partition p1 values less than (10), 1341 partition p2 values less than (20), 1342 partition p3 values less than (2305561538531885056), 1343 partition p4 values less than (2305561538531950591) 1344); 1345 1346insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1); 1347insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1); 1348 1349explain partitions select * from t1 where 1350 a >= 2305561538531885056-10 and a <= 2305561538531885056-8; 1351 1352explain partitions select * from t1 where 1353 a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE; 1354 1355explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF; 1356drop table t1; 1357 1358create table t1 (a bigint) partition by range(a+0) ( 1359 partition p1 values less than (-1000), 1360 partition p2 values less than (-10), 1361 partition p3 values less than (10), 1362 partition p4 values less than (1000) 1363); 1364insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15); 1365explain partitions select * from t1 where a>-2 and a <=0; 1366drop table t1; 1367 1368 1369# 1370# BUG#27927 Partition pruning not optimal with TO_DAYS function 1371# 1372 1373CREATE TABLE t1 ( recdate DATETIME NOT NULL ) 1374PARTITION BY RANGE( TO_DAYS(recdate) ) ( 1375 PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ), 1376 PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') ) 1377); 1378INSERT INTO t1 VALUES ('2007-03-01 12:00:00'); 1379INSERT INTO t1 VALUES ('2007-03-07 12:00:00'); 1380INSERT INTO t1 VALUES ('2007-03-08 12:00:00'); 1381INSERT INTO t1 VALUES ('2007-03-15 12:00:00'); 1382-- echo must use p0 only: 1383explain partitions select * from t1 where recdate < '2007-03-08 00:00:00'; 1384 1385drop table t1; 1386CREATE TABLE t1 ( recdate DATETIME NOT NULL ) 1387PARTITION BY RANGE( YEAR(recdate) ) ( 1388 PARTITION p0 VALUES LESS THAN (2006), 1389 PARTITION p1 VALUES LESS THAN (2007) 1390); 1391INSERT INTO t1 VALUES ('2005-03-01 12:00:00'); 1392INSERT INTO t1 VALUES ('2005-03-01 12:00:00'); 1393INSERT INTO t1 VALUES ('2006-03-01 12:00:00'); 1394INSERT INTO t1 VALUES ('2006-03-01 12:00:00'); 1395 1396-- echo must use p0 only: 1397explain partitions select * from t1 where recdate < '2006-01-01 00:00:00'; 1398drop table t1; 1399 1400-- echo # 1401-- echo # BUG#33730 Full table scan instead selected partitions for query more than 10 partitions 1402-- echo # 1403create table t0 (a int); 1404insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1405create table t1 (a int) 1406 partition by range(a+0) ( 1407 partition p0 values less than (64), 1408 partition p1 values less than (128), 1409 partition p2 values less than (255) 1410); 1411insert into t1 select A.a + 10*B.a from t0 A, t0 B; 1412 1413# this will use interval_via_walking 1414explain partitions select * from t1 where a between 10 and 13; 1415explain partitions select * from t1 where a between 10 and 10+33; 1416 1417drop table t0, t1; 1418 1419--echo # 1420--echo # Bug#71095: Wrong results with PARTITION BY LIST COLUMNS() 1421--echo # 1422CREATE TABLE t1 1423(c1 int, 1424 c2 int, 1425 c3 int, 1426 c4 int, 1427 PRIMARY KEY (c1,c2)) 1428PARTITION BY LIST COLUMNS (c2) 1429(PARTITION p1 VALUES IN (1,2), 1430 PARTITION p2 VALUES IN (3,4)); 1431INSERT INTO t1 VALUES (1, 1, 1, 1), (2, 3, 1, 1); 1432INSERT INTO t1 VALUES (1, 2, 1, 1), (2, 4, 1, 1); 1433SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1; 1434SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1; 1435SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1; 1436SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1; 1437SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1; 1438SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3; 1439SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3; 1440SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3; 1441SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3; 1442SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3; 1443SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3; 1444SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4; 1445SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4; 1446SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4; 1447SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4; 1448SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4; 1449EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1; 1450EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1; 1451EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1; 1452EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1; 1453EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1; 1454EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3; 1455EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3; 1456EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3; 1457EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3; 1458EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3; 1459EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3; 1460EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4; 1461EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4; 1462EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4; 1463EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4; 1464EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4; 1465DROP TABLE t1; 1466 1467--echo # 1468--echo # MDEV-6239: Partition pruning is not working as expected in an inner query 1469--echo # 1470 1471create table t1 1472( 1473 company_id bigint(5), 1474 dept_id bigint(5), 1475 emp_id bigint(5), 1476 emp_name varchar(100), 1477 primary key (company_id, emp_id) 1478) partition by list (company_id) ( 1479 partition p_1000 values in (1000), 1480 partition p_2000 values in (2000), 1481 partition p_3000 values in (3000) 1482); 1483 1484create table t2 1485( 1486 company_id bigint(5), 1487 dept_id bigint(5), 1488 dept_name varchar(100), 1489 primary key (company_id, dept_id) 1490) partition by list (company_id) ( 1491 partition p_1000 values in (1000), 1492 partition p_2000 values in (2000), 1493 partition p_3000 values in (3000) 1494); 1495 1496insert into t2 values 1497 (1000, 10, 'Engineering'), 1498 (1000, 20, 'Product Management'), 1499 (1000, 30, 'QA'), 1500 (2000, 40, 'Support'), 1501 (2000, 50, 'Professional Services'); 1502 1503insert into t1 values 1504(1000, 10, 1, 'John'), 1505(1000, 10, 2, 'Smith'), 1506(1000, 20, 3, 'Jacob'), 1507(1000, 20, 4, 'Brian'), 1508(1000, 30, 5, 'Chris'), 1509(1000, 30, 6, 'Ryan'), 1510(2000, 40, 7, 'Karin'), 1511(2000, 40, 8, 'Jay'), 1512(2000, 50, 9, 'Ana'), 1513(2000, 50, 10, 'Jessica'); 1514 1515--echo # Table t2 should have only partition p_1000. 1516explain partitions 1517select * from t1 1518where company_id = 1000 1519and dept_id in (select dept_id from t2 where COMPANY_ID = 1000); 1520 1521drop table t1,t2; 1522 1523--echo # 1524--echo # MDEV-9505: Valgrind failure in SEL_ARG::store_min,find_used_partitions,... 1525--echo # 1526create table t1 (a int, b char(10), c varchar(5), d int) 1527partition by range columns(a,b,c) 1528subpartition by key (c,d) 1529subpartitions 3 1530( partition p0 values less than (1,'abc','abc'), 1531 partition p1 values less than (2,'abc','abc'), 1532 partition p2 values less than (3,'abc','abc'), 1533 partition p3 values less than (4,'abc','abc') 1534); 1535 1536insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3); 1537select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR 1538 (a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2)))); 1539drop table t1; 1540 1541--echo # 1542--echo # MDEV-14667 Assertion `used_parts > 0' failed in ha_partition::init_record_priority_queue. 1543--echo # 1544 1545create table t1 (a int); 1546insert into t1 values (1),(2); 1547 1548create table t2 (b int, c int, key(c,b)) partition by hash(b) partitions 2; 1549insert into t2 values (3,4),(5,6); 1550 1551select straight_join * from t1, t2 where b != NULL; 1552drop table t1, t2; 1553 1554--echo # 1555--echo # MDEV-17493: Partition pruning doesn't work for nested outer joins 1556--echo # 1557 1558create table t0(a int); 1559insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1560create table t1 (a int, b int, c int); 1561insert into t1 select a,a,a from t0; 1562create table t2 (a int, b int, c int); 1563insert into t2 select a,a,a from t0; 1564 1565create table t3 ( 1566 part_id int, 1567 a int 1568) partition by list (part_id) ( 1569 partition p0 values in (0), 1570 partition p1 values in (1), 1571 partition p2 values in (2), 1572 partition p3 values in (3), 1573 partition p4 values in (4) 1574); 1575insert into t3 select mod(a,5), a from t0; 1576 1577explain partitions 1578select * from t1 left join t3 on (t1.a=t3.a and t3.part_id=3); 1579 1580--echo # The following should have partitions="p3", NOT p0,p1,p2,p3,p4: 1581explain partitions 1582select * from t1 left join (t3 join t2) on (t1.a=t3.a and t3.a=t2.b and t3.part_id=3); 1583 1584drop table t0,t1,t2,t3; 1585 1586