59
59
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
60
60
DROP TABLE t1,t2,t3;
63
# Bug#54568: create view cause Assertion failed: 0,
64
# file .\item_subselect.cc, line 836
66
EXPLAIN SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
67
id select_type table type possible_keys key key_len ref rows Extra
68
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
70
Note 1249 Select 2 was reduced during optimization
71
DESCRIBE SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
72
id select_type table type possible_keys key key_len ref rows Extra
73
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
75
Note 1249 Select 2 was reduced during optimization
76
# None of the below should crash
77
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
78
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
81
# Bug#51070: Query with a NOT IN subquery predicate returns a wrong
84
CREATE TABLE t1 ( a INT, b INT );
85
INSERT INTO t1 VALUES ( 1, NULL ), ( 2, NULL );
86
CREATE TABLE t2 ( c INT, d INT );
87
INSERT INTO t2 VALUES ( NULL, 3 ), ( NULL, 4 );
88
CREATE TABLE t3 ( e INT, f INT );
89
INSERT INTO t3 VALUES ( NULL, NULL ), ( NULL, NULL );
90
CREATE TABLE t4 ( a INT );
91
INSERT INTO t4 VALUES (1), (2), (3);
92
CREATE TABLE t5 ( a INT );
93
INSERT INTO t5 VALUES (NULL), (2);
95
SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
96
id select_type table type possible_keys key key_len ref rows Extra
97
x PRIMARY x x x x x x x x
98
x DEPENDENT SUBQUERY x x x x x x x x
99
SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
102
SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL;
103
id select_type table type possible_keys key key_len ref rows Extra
104
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
105
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
106
SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL;
110
SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, d FROM t2 ) IS NULL;
114
SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS UNKNOWN;
118
SELECT * FROM t1 WHERE (( a, b ) NOT IN ( SELECT c, d FROM t2 )) IS UNKNOWN;
122
SELECT * FROM t1 WHERE 1 = 1 AND ( a, b ) NOT IN ( SELECT c, d FROM t2 );
125
SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 );
126
id select_type table type possible_keys key key_len ref rows Extra
127
x PRIMARY x x x x x x x x
128
x DEPENDENT SUBQUERY x x x x x x x x
129
SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 );
132
SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 );
133
id select_type table type possible_keys key key_len ref rows Extra
134
x PRIMARY x x x x x x x x
135
x DEPENDENT SUBQUERY x x x x x x x x
136
SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 );
139
SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 );
140
id select_type table type possible_keys key key_len ref rows Extra
141
x PRIMARY x x x x x x x x
142
x DEPENDENT SUBQUERY x x x x x x x x
143
SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 );
146
SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 );
147
id select_type table type possible_keys key key_len ref rows Extra
148
x PRIMARY x x x x x x x x
149
x DEPENDENT SUBQUERY x x x x x x x x
150
SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 );
152
SELECT * FROM t1 WHERE ( a, b ) NOT IN
153
( SELECT c, d FROM t2 WHERE c = 1 AND c <> 1 );
157
SELECT * FROM t1 WHERE b NOT IN ( SELECT c FROM t2 WHERE c = 1 );
161
SELECT * FROM t1 WHERE NULL NOT IN ( SELECT c FROM t2 WHERE c = 1 AND c <> 1 );
165
DROP TABLE t1, t2, t3, t4, t5;
167
# Bug#58207: invalid memory reads when using default column value and
170
CREATE TABLE t(a VARCHAR(245) DEFAULT
171
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
172
INSERT INTO t VALUES (''),(''),(''),(''),(''),(''),(''),(''),(''),(''),('');
173
SELECT * FROM (SELECT default(a) FROM t GROUP BY a) d;
175
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa