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
|
set @tmp_subselect_nulls=@@optimizer_switch;
set optimizer_switch='semijoin=off';
create table x1(k int primary key, d1 int, d2 int);
create table x2(k int primary key, d1 int, d2 int);
insert into x1 values
(10, 10, 10),
(20, 20, 20),
(21, 20, null),
(30, null, 30),
(40, 40, 40);
insert into x2 values
(10, 10, 10),
(20, 20, 20),
(21, 20, null),
(30, null, 30);
select *
from x1
where (d1, d2) in (select d1, d2
from x2);
k d1 d2
10 10 10
20 20 20
select *
from x1
where (d1, d2) in (select d1, d2
from x2) is true;
k d1 d2
10 10 10
20 20 20
select *
from x1
where (d1, d2) in (select d1, d2
from x2) is false;
k d1 d2
40 40 40
select *
from x1
where (d1, d2) in (select d1, d2
from x2) is unknown;
k d1 d2
21 20 NULL
30 NULL 30
select *
from x1
where d1 in (select d1
from x2
where x1.d2=x2.d2);
k d1 d2
10 10 10
20 20 20
select *
from x1
where d1 in (select d1
from x2
where x1.d2=x2.d2) is true;
k d1 d2
10 10 10
20 20 20
select *
from x1
where d1 in (select d1
from x2
where x1.d2=x2.d2) is false;
k d1 d2
21 20 NULL
40 40 40
select *
from x1
where d1 in (select d1
from x2
where x1.d2=x2.d2) is unknown;
k d1 d2
30 NULL 30
select *
from x1
where 1 in (select 1
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2);
k d1 d2
10 10 10
20 20 20
select *
from x1
where 1 in (select 1
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2) is true;
k d1 d2
10 10 10
20 20 20
select *
from x1
where 1 in (select 1
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2) is false;
k d1 d2
21 20 NULL
30 NULL 30
40 40 40
select *
from x1
where 1 in (select 1
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2) is unknown;
k d1 d2
select *
from x1
where exists (select *
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2);
k d1 d2
10 10 10
20 20 20
set optimizer_switch= @tmp_subselect_nulls;
drop table x1;
drop table x2;
#
# MDEV-7339 Server crashes in Item_func_trig_cond::val_int
#
select (select 1, 2) in (select 3, 4);
(select 1, 2) in (select 3, 4)
0
select (select NULL, NULL) in (select 3, 4);
(select NULL, NULL) in (select 3, 4)
NULL
#
# End of 5.5 tests
#
#
# MDEV-32555 wrong result with an index and a partially null-rejecting condition
#
create table t1 (a int primary key);
insert t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (
b int not null,
c int default null,
d int not null,
e int not null,
unique key (d,b,c)
);
insert t2 values (1,null,1,1),(1,null,2,2),(1,null,3,3),(1,null,4,4),(2,null,1,2),(3,null,1,3),(4,null,2,2),(4,null,1,4);
select (
select sum(t2_.e) from t2 t2_ where t2_.b = a and t2_.c <=> t2.c and t2_.d = 1
) x from t2 left join t1 on a = b;
x
1
2
3
4
1
4
1
1
drop table t1, t2;
#
# End of 10.10 tests
#
|
