|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
1 |
|
2 |
--echo #
|
|
3 |
--echo # Bug #44139: Table scan when NULL appears in IN clause
|
|
4 |
--echo #
|
|
5 |
||
|
1165.2.3
by Stewart Smith
use MyISAM for tests so results for EXPLAIN are deterministic |
6 |
CREATE TEMPORARY TABLE t1 ( |
|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
7 |
c_int INT NOT NULL, |
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), |
|
|
1165.2.3
by Stewart Smith
use MyISAM for tests so results for EXPLAIN are deterministic |
15 |
INDEX(c_char)) ENGINE=MYISAM; |
|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
16 |
|
17 |
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 |
18 |
CREATE TABLE t2 AS SELECT * FROM t1; |
19 |
INSERT INTO t1 (c_int) SELECT 0 FROM t2; |
|
20 |
DROP TABLE t2; |
|
21 |
CREATE TABLE t2 AS SELECT * FROM t1; |
|
22 |
INSERT INTO t1 (c_int) SELECT 0 FROM t2; |
|
23 |
DROP TABLE t2; |
|
|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
24 |
|
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); |
|
27 |
||
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); |
|
32 |
||
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); |
|
37 |
||
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); |
|
42 |
||
43 |
||
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); |
|
50 |
||
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); |
|
57 |
||
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); |
|
62 |
||
63 |
DROP TABLE t1; |
|
64 |
||
65 |
--echo #
|
|
66 |
||
67 |
--echo End of 5.1 tests
|