1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on';
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
create table t1 (id int);
insert into t1 values (null), (0);
create table t2 (id int);
insert into t2 values (null);
select * from t1, t2 where t1.id = t2.id;
id id
alter table t1 add key id (id);
select * from t1, t2 where t1.id = t2.id;
id id
drop table t1,t2;
create table t1 (
id integer,
id2 integer not null,
index (id),
index (id2)
);
insert into t1 values(null,null),(1,1);
Warnings:
Warning 1048 Column 'id2' cannot be null
select * from t1;
id id2
NULL 0
1 1
select * from t1 where id <=> null;
id id2
NULL 0
select * from t1 where id <=> null or id > 0;
id id2
NULL 0
1 1
select * from t1 where id is null or id > 0;
id id2
NULL 0
1 1
select * from t1 where id2 <=> null or id2 > 0;
id id2
1 1
select * from t1 where id2 is null or id2 > 0;
id id2
1 1
delete from t1 where id <=> NULL;
select * from t1;
id id2
1 1
drop table t1;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int, b int, INDEX idx(a));
CREATE TABLE t3 (b int, INDEX idx(b));
CREATE TABLE t4 (b int, INDEX idx(b));
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t2 VALUES (1, 1), (3, 1);
INSERT INTO t3 VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO t4 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t4;
INSERT INTO t4 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t4;
INSERT INTO t4 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t4;
INSERT INTO t4 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t4;
INSERT INTO t4 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t4;
INSERT INTO t4 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t4;
INSERT INTO t4 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t4;
INSERT INTO t4 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t4;
INSERT INTO t3 VALUES (2), (3);
ANALYZE table t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t2 analyze status OK
test.t3 analyze status OK
SELECT COUNT(*) FROM t3;
COUNT(*)
15972
EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
LEFT JOIN t3 ON t2.b=t3.b;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL
1 SIMPLE t2 NULL ALL idx NULL NULL NULL # 100.00 Using where; Using join buffer (hash join)
1 SIMPLE t3 NULL ref idx idx 5 test.t2.b # 100.00 Using index
Warnings:
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
Note 1003 /* select#1 */ select sql_calc_found_rows `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) left join `test`.`t3` on((`test`.`t3`.`b` = `test`.`t2`.`b`)) where true
FLUSH STATUS ;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
LEFT JOIN t3 ON t2.b=t3.b;
a a b b
1 1 1 NULL
2 NULL NULL NULL
3 3 1 NULL
4 NULL NULL NULL
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
Warnings:
SELECT FOUND_ROWS();
FOUND_ROWS()
4
Warnings:
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
SHOW STATUS LIKE "handler_read%";
Variable_name Value
Handler_read_first 2
Handler_read_key 4
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 8
DROP TABLE t1,t2,t3,t4;
CREATE TABLE t1 (
a int(11) default NULL,
b int(11) default NULL,
KEY a (a,b)
);
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (0,10),(0,11),(0,12);
CREATE TABLE t2 (
a int(11) default NULL,
b int(11) default NULL,
KEY a (a)
);
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
INSERT INTO t2 VALUES (3,NULL),(3,11),(3,12);
SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AND t2.b = t1.b;
a b a b
3 11 0 11
3 12 0 12
drop table t1, t2;
End of 5.0 tests
#
# Bug#54608 Query using IN + OR + IS TRUE and IS NULL returns
# NULL when should be empty
#
CREATE TABLE t1 (a INT, KEY (a));
INSERT INTO t1 VALUES (1), (2), (NULL);
explain SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND a IS NULL);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ref_or_null a a 5 const 2 100.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 42) or (((0 <> `test`.`t1`.`a`) is true) and (`test`.`t1`.`a` is null)))
SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND a IS NULL);
a
explain SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ref_or_null a a 5 const # 100.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 42) or (`test`.`t1`.`a` = NULL))
SELECT a FROM t1 WHERE a IN (42) OR (a=NULL);
a
drop table t1;
SET sql_mode = default;
set optimizer_switch=default;
|
