2
# Bug #44139: Table scan when NULL appears in IN clause
4
CREATE TEMPORARY TABLE t1 (
6
c_decimal DECIMAL(5,2) NOT NULL DEFAULT 0,
7
c_float DOUBLE NOT NULL DEFAULT 0,
8
c_datetime DATETIME NOT NULL DEFAULT '2009-04-12',
9
c_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
10
c_char CHAR(10) NOT NULL DEFAULT '',
11
INDEX(c_int), INDEX(c_decimal), INDEX(c_float),
12
INDEX(c_datetime), INDEX(c_timestamp),
13
INDEX(c_char)) ENGINE=MYISAM;
14
INSERT INTO t1 (c_int) VALUES (1), (2), (3), (4), (5);
15
CREATE TABLE t2 AS SELECT * FROM t1;
16
INSERT INTO t1 (c_int) SELECT 0 FROM t2;
18
CREATE TABLE t2 AS SELECT * FROM t1;
19
INSERT INTO t1 (c_int) SELECT 0 FROM t2;
21
EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3);
22
id select_type table type possible_keys key key_len ref rows Extra
23
1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where
24
EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3);
25
id select_type table type possible_keys key key_len ref rows Extra
26
1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where
27
EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3);
28
id select_type table type possible_keys key key_len ref rows Extra
29
1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where
30
EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL);
31
id select_type table type possible_keys key key_len ref rows Extra
32
1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where
33
EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL);
34
id select_type table type possible_keys key key_len ref rows Extra
35
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
36
EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL);
37
id select_type table type possible_keys key key_len ref rows Extra
38
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
39
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3);
40
id select_type table type possible_keys key key_len ref rows Extra
41
1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where
42
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3);
43
id select_type table type possible_keys key key_len ref rows Extra
44
1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where
45
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL);
46
id select_type table type possible_keys key key_len ref rows Extra
47
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
48
EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL);
49
id select_type table type possible_keys key key_len ref rows Extra
50
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
51
EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3);
52
id select_type table type possible_keys key key_len ref rows Extra
53
1 SIMPLE t1 range c_float c_float 8 NULL 3 Using where
54
EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3);
55
id select_type table type possible_keys key key_len ref rows Extra
56
1 SIMPLE t1 range c_float c_float 8 NULL 3 Using where
57
EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL);
58
id select_type table type possible_keys key key_len ref rows Extra
59
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
60
EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL);
61
id select_type table type possible_keys key key_len ref rows Extra
62
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
63
EXPLAIN SELECT * FROM t1 WHERE c_datetime
64
IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
65
id select_type table type possible_keys key key_len ref rows Extra
66
1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where
67
EXPLAIN SELECT * FROM t1 WHERE c_datetime
68
IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
69
id select_type table type possible_keys key key_len ref rows Extra
70
1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where
71
EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL);
72
id select_type table type possible_keys key key_len ref rows Extra
73
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
74
EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL);
75
id select_type table type possible_keys key key_len ref rows Extra
76
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
77
EXPLAIN SELECT * FROM t1 WHERE c_timestamp
78
IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03');
79
id select_type table type possible_keys key key_len ref rows Extra
80
1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where
81
EXPLAIN SELECT * FROM t1 WHERE c_timestamp
82
IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03');
83
id select_type table type possible_keys key key_len ref rows Extra
84
1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where
85
EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL);
86
id select_type table type possible_keys key key_len ref rows Extra
87
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
88
EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL);
89
id select_type table type possible_keys key key_len ref rows Extra
90
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
91
EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3');
92
id select_type table type possible_keys key key_len ref rows Extra
93
1 SIMPLE t1 range c_char c_char 42 NULL 3 Using where
94
EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3');
95
id select_type table type possible_keys key key_len ref rows Extra
96
1 SIMPLE t1 range c_char c_char 42 NULL 3 Using where
97
EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL);
98
id select_type table type possible_keys key key_len ref rows Extra
99
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
100
EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL);
101
id select_type table type possible_keys key key_len ref rows Extra
102
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables