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