|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
1 |
#
|
2 |
# Bug #44139: Table scan when NULL appears in IN clause
|
|
3 |
#
|
|
|
1165.2.3
by Stewart Smith
use MyISAM for tests so results for EXPLAIN are deterministic |
4 |
CREATE TEMPORARY TABLE t1 (
|
|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
5 |
c_int INT NOT NULL,
|
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),
|
|
|
1165.2.3
by Stewart Smith
use MyISAM for tests so results for EXPLAIN are deterministic |
13 |
INDEX(c_char)) ENGINE=MYISAM;
|
|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
14 |
INSERT INTO t1 (c_int) VALUES (1), (2), (3), (4), (5);
|
|
1165.2.3
by Stewart Smith
use MyISAM for tests so results for EXPLAIN are deterministic |
15 |
CREATE TABLE t2 AS SELECT * FROM t1;
|
16 |
INSERT INTO t1 (c_int) SELECT 0 FROM t2;
|
|
17 |
DROP TABLE t2;
|
|
18 |
CREATE TABLE t2 AS SELECT * FROM t1;
|
|
19 |
INSERT INTO t1 (c_int) SELECT 0 FROM t2;
|
|
20 |
DROP TABLE t2;
|
|
|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
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
|
|
|
1782.4.1
by Brian Aker
Fix for 64bit issue around timestamp |
80 |
1 SIMPLE t1 range c_timestamp c_timestamp 8 NULL 3 Using where
|
|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
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
|
|
|
1782.4.1
by Brian Aker
Fix for 64bit issue around timestamp |
84 |
1 SIMPLE t1 range c_timestamp c_timestamp 8 NULL 3 Using where
|
|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
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
|
|
103 |
DROP TABLE t1;
|
|
104 |
#
|
|
105 |
End of 5.1 tests
|