1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
--echo #
--echo # Bug #44139: Table scan when NULL appears in IN clause
--echo #
CREATE TEMPORARY TABLE t1 (
c_int INT NOT NULL,
c_decimal DECIMAL(5,2) NOT NULL DEFAULT 0,
c_float DOUBLE NOT NULL DEFAULT 0,
c_datetime DATETIME NOT NULL DEFAULT '2009-04-12',
c_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
c_char CHAR(10) NOT NULL DEFAULT '',
INDEX(c_int), INDEX(c_decimal), INDEX(c_float),
INDEX(c_datetime), INDEX(c_timestamp),
INDEX(c_char)) ENGINE=MYISAM;
INSERT INTO t1 (c_int) VALUES (1), (2), (3), (4), (5);
CREATE TABLE t2 AS SELECT * FROM t1;
INSERT INTO t1 (c_int) SELECT 0 FROM t2;
DROP TABLE t2;
CREATE TABLE t2 AS SELECT * FROM t1;
INSERT INTO t1 (c_int) SELECT 0 FROM t2;
DROP TABLE t2;
EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3);
EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3);
EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3);
EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL);
EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL);
EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL);
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3);
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3);
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL);
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL);
EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3);
EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3);
EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL);
EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL);
EXPLAIN SELECT * FROM t1 WHERE c_datetime
IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
EXPLAIN SELECT * FROM t1 WHERE c_datetime
IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL);
EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL);
EXPLAIN SELECT * FROM t1 WHERE c_timestamp
IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03');
EXPLAIN SELECT * FROM t1 WHERE c_timestamp
IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03');
EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL);
EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL);
EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3');
EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3');
EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL);
EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL);
DROP TABLE t1;
--echo #
--echo End of 5.1 tests
|