1657
# Bug#50939: Loose Index Scan unduly relies on engine to remember range
1664
) PARTITION BY HASH (a) PARTITIONS 1;
1670
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
1671
INSERT INTO t1 SELECT a + 5, b + 5 FROM t1;
1672
INSERT INTO t1 SELECT a + 10, b + 10 FROM t1;
1673
INSERT INTO t1 SELECT a + 20, b + 20 FROM t1;
1674
INSERT INTO t1 SELECT a + 40, b + 40 FROM t1;
1675
INSERT INTO t2 SELECT * FROM t1;
1676
# plans should be identical
1677
EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a;
1678
id select_type table type possible_keys key key_len ref rows Extra
1679
1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by
1680
EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a;
1681
id select_type table type possible_keys key key_len ref rows Extra
1682
1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by
1684
SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a;
1687
# Should be no more than 4 reads.
1688
SHOW status LIKE 'handler_read_key';
1692
SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a;
1695
# Should be no more than 4 reads.
1696
SHOW status LIKE 'handler_read_key';
1657
# Bug #54802: 'NOT BETWEEN' evaluation is incorrect
1659
CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key));
1660
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
1661
EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
1662
id select_type table type possible_keys key key_len ref rows Extra
1663
1 SIMPLE t1 ALL c_key NULL NULL NULL 3 Using where
1664
SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
1670
# Bug #57030: 'BETWEEN' evaluation is incorrect
1672
CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT);
1673
CREATE UNIQUE INDEX i4_uq ON t1(i4);
1674
INSERT INTO t1 VALUES (1,10), (2,20), (3,30);
1676
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10;
1677
id select_type table type possible_keys key key_len ref rows Extra
1678
1 SIMPLE t1 const i4_uq i4_uq 5 const 1
1679
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10;
1683
SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4;
1684
id select_type table type possible_keys key key_len ref rows Extra
1685
1 SIMPLE t1 const i4_uq i4_uq 5 const 1
1686
SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4;
1690
SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4;
1691
id select_type table type possible_keys key key_len ref rows Extra
1692
1 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using where
1693
SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4;
1699
SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10;
1700
id select_type table type possible_keys key key_len ref rows Extra
1701
1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where
1702
SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10;
1706
SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10;
1707
id select_type table type possible_keys key key_len ref rows Extra
1708
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
1709
SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10;
1715
SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11;
1716
id select_type table type possible_keys key key_len ref rows Extra
1717
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1718
SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11;
1721
SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0;
1722
id select_type table type possible_keys key key_len ref rows Extra
1723
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1724
SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0;
1727
SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0;
1728
id select_type table type possible_keys key key_len ref rows Extra
1729
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1730
SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0;
1733
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999;
1734
id select_type table type possible_keys key key_len ref rows Extra
1735
1 SIMPLE t1 range i4_uq i4_uq 5 NULL 2 Using where
1736
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999;
1742
SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30;
1743
id select_type table type possible_keys key key_len ref rows Extra
1744
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1745
SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30;
1748
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
1749
id select_type table type possible_keys key key_len ref rows Extra
1750
1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where
1751
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
1756
SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
1757
id select_type table type possible_keys key key_len ref rows Extra
1758
1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3
1759
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where
1760
SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
1763
SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
1764
id select_type table type possible_keys key key_len ref rows Extra
1765
1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3
1766
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where
1767
SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
1700
1770
End of 5.1 tests