1415
1415
drop table t0, t1;
1418
--echo # Bug#71095: Wrong results with PARTITION BY LIST COLUMNS()
1425
PRIMARY KEY (c1,c2))
1426
PARTITION BY LIST COLUMNS (c2)
1427
(PARTITION p1 VALUES IN (1,2),
1428
PARTITION p2 VALUES IN (3,4));
1429
INSERT INTO t1 VALUES (1, 1, 1, 1), (2, 3, 1, 1);
1430
INSERT INTO t1 VALUES (1, 2, 1, 1), (2, 4, 1, 1);
1431
SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1;
1432
SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1;
1433
SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
1434
SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1;
1435
SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1;
1436
SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3;
1437
SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3;
1438
SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3;
1439
SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3;
1440
SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3;
1441
SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3;
1442
SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4;
1443
SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4;
1444
SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4;
1445
SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4;
1446
SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4;
1447
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1;
1448
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1;
1449
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
1450
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1;
1451
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1;
1452
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3;
1453
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3;
1454
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3;
1455
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3;
1456
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3;
1457
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3;
1458
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4;
1459
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4;
1460
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4;
1461
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4;
1462
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4;
1418
1466
--echo # MDEV-6239: Partition pruning is not working as expected in an inner query