|
977.2.1
by Stewart Smith
Add test for SELECT IN () with NULL producing bad query plan. See https://bugs.launchpad.net/drizzle/+bug/357560 and http://bugs.mysql.com/bug.php?id=44139 |
1 |
drop table if exists foo;
|
2 |
Warnings:
|
|
3 |
Note 1051 Unknown table 'foo'
|
|
|
1165.2.3
by Stewart Smith
use MyISAM for tests so results for EXPLAIN are deterministic |
4 |
create temporary table foo (
|
|
977.2.1
by Stewart Smith
Add test for SELECT IN () with NULL producing bad query plan. See https://bugs.launchpad.net/drizzle/+bug/357560 and http://bugs.mysql.com/bug.php?id=44139 |
5 |
a int not null auto_increment,
|
6 |
b int,
|
|
7 |
primary key( a )
|
|
|
1165.2.3
by Stewart Smith
use MyISAM for tests so results for EXPLAIN are deterministic |
8 |
) engine=myisam;
|
|
977.2.1
by Stewart Smith
Add test for SELECT IN () with NULL producing bad query plan. See https://bugs.launchpad.net/drizzle/+bug/357560 and http://bugs.mysql.com/bug.php?id=44139 |
9 |
begin;
|
10 |
insert into foo( b ) values (1),(1),(1),(1),(1);
|
|
|
1165.2.3
by Stewart Smith
use MyISAM for tests so results for EXPLAIN are deterministic |
11 |
create table t2 as select * from foo;
|
12 |
insert into foo( b ) select b from t2;
|
|
13 |
drop table t2;
|
|
14 |
create table t2 as select * from foo;
|
|
15 |
insert into foo( b ) select b from t2;
|
|
16 |
drop table t2;
|
|
17 |
create table t2 as select * from foo;
|
|
18 |
insert into foo( b ) select b from t2;
|
|
19 |
drop table t2;
|
|
20 |
create table t2 as select * from foo;
|
|
21 |
insert into foo( b ) select b from t2;
|
|
22 |
drop table t2;
|
|
23 |
create table t2 as select * from foo;
|
|
24 |
insert into foo( b ) select b from t2;
|
|
25 |
drop table t2;
|
|
|
977.2.1
by Stewart Smith
Add test for SELECT IN () with NULL producing bad query plan. See https://bugs.launchpad.net/drizzle/+bug/357560 and http://bugs.mysql.com/bug.php?id=44139 |
26 |
commit;
|
27 |
explain select * from foo where a in (160000, 160001, 160002);
|
|
28 |
id select_type table type possible_keys key key_len ref rows Extra
|
|
29 |
1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 3 Using where
|
|
30 |
explain select * from foo where a in (null, 160000, 160001, 160002);
|
|
31 |
id select_type table type possible_keys key key_len ref rows Extra
|
|
|
1165.2.2
by gshchepa at mysql
MySQL Bug #44139: Table scan when NULL appears in IN clause |
32 |
1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 3 Using where
|
33 |
DROP TABLE foo;
|