subselect.test 22.3 KB
Newer Older
1 2 3 4 5
# Initialise
--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
--enable_warnings

unknown's avatar
unknown committed
6
select (select 2);
7
explain select (select 2);
8
SELECT (SELECT 1) UNION SELECT (SELECT 2);
9
explain SELECT (SELECT 1) UNION SELECT (SELECT 2);
unknown's avatar
unknown committed
10
SELECT (SELECT (SELECT 0 UNION SELECT 0));
11
explain SELECT (SELECT (SELECT 0 UNION SELECT 0));
unknown's avatar
unknown committed
12
-- error 1245
13
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
unknown's avatar
unknown committed
14
-- error 1245
15 16
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b;
SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;
unknown's avatar
unknown committed
17 18
-- error 1245
SELECT (SELECT a) as a;
unknown's avatar
unknown committed
19 20
EXPLAIN SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
21 22
-- error 1054
SELECT (SELECT 1), a;
23
SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
24
-- error 1054
25
SELECT 1 FROM (SELECT (SELECT a) b) c;
26
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
unknown's avatar
unknown committed
27
-- error 1239
28
SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
unknown's avatar
unknown committed
29
SELECT 1 IN (SELECT 1);
unknown's avatar
unknown committed
30
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
31 32 33 34
-- error 1221
select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
-- error 1108
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
unknown's avatar
unknown committed
35 36
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
SELECT (SELECT 1,2,3) = ROW(1,2,3);
SELECT (SELECT 1,2,3) = ROW(1,2,1);
SELECT (SELECT 1,2,3) < ROW(1,2,1);
SELECT (SELECT 1,2,3) > ROW(1,2,1);
SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
SELECT ROW(1,2,3) = (SELECT 1,2,3);
SELECT ROW(1,2,3) = (SELECT 1,2,1);
SELECT ROW(1,2,3) < (SELECT 1,2,1);
SELECT ROW(1,2,3) > (SELECT 1,2,1);
SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
53

unknown's avatar
unknown committed
54 55 56
-- error 1239
SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);

57 58 59 60 61 62 63
create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
create table t4 (a int, b int);
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
unknown's avatar
unknown committed
64
-- error 1245
65
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
66 67
select (select a from t1 where t1.a=t2.a), a from t2;
select (select a from t1 where t1.a=t2.b), a from t2;
68 69 70 71
select (select a from t1), a from t2;
select (select a from t3), a from t2;
select * from t2 where t2.a=(select a from t1);
insert into t3 values (6),(7),(3);
unknown's avatar
unknown committed
72
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
unknown's avatar
unknown committed
73 74 75
(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 order by a limit 2) limit 3;
(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
explain (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
unknown's avatar
unknown committed
76 77 78
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
(select * from t2 where a>1) as tt;
unknown's avatar
unknown committed
79 80
explain select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
(select * from t2 where a>1) as tt;
unknown's avatar
unknown committed
81 82 83 84
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
unknown's avatar
unknown committed
85
explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
unknown's avatar
unknown committed
86 87
select * from t3 where exists (select * from t2 where t2.b=t3.a);
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
unknown's avatar
unknown committed
88 89
select * from t3 where a in (select b from t2);
select * from t3 where a not in (select b from t2);
unknown's avatar
unknown committed
90 91 92 93 94 95 96 97 98 99
select * from t3 where a = some (select b from t2);
select * from t3 where a <> any (select b from t2);
select * from t3 where a = all (select b from t2);
select * from t3 where a <> all (select b from t2);
insert into t2 values (100, 5);
select * from t3 where a < any (select b from t2);
select * from t3 where a < all (select b from t2);
select * from t3 where a >= any (select b from t2);
select * from t3 where a >= all (select b from t2);
delete from t2 where a=100;
unknown's avatar
unknown committed
100 101 102 103
-- error 1239
select * from t3 where a in (select a,b from t2);
-- error 1239
select * from t3 where a in (select * from t2);
unknown's avatar
unknown committed
104 105 106 107 108
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9);
select b,max(a) as ma from t4 group by b having b < (select max(t2.a)
from t2 where t2.b=t4.b);
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a)
from t2 where t2.b=t4.b);
unknown's avatar
unknown committed
109 110 111 112 113 114
create table t5 (a int);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
insert into t5 values (5);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
insert into t5 values (2);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
unknown's avatar
unknown committed
115
explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
unknown's avatar
unknown committed
116
-- error 1240
unknown's avatar
unknown committed
117
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
118 119 120 121 122
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
create table t7( uq int primary key, name char(25));
insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
insert into t6 values (1,1),(1,2),(2,2),(1,3);
select * from t6 where exists (select * from t7 where uq = clinic_uq);
123

124 125 126 127
# not unique fields
-- error 1052
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);

128 129 130 131 132 133 134 135 136 137 138 139 140
# different tipes & group functions
drop table if exists t1,t2,t3;

CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);

141
CREATE TABLE `t8` (
unknown's avatar
unknown committed
142 143 144 145 146 147
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
  `email` varchar(60) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`pseudo`),
  UNIQUE KEY `email` (`email`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

148 149 150 151
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
EXPLAIN SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
unknown's avatar
unknown committed
152
-- error 1239
153 154
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
unknown's avatar
unknown committed
155
-- error 1239
156
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
unknown's avatar
unknown committed
157
pseudo='joce');
158
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
unknown's avatar
unknown committed
159
-- error 1240
160
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
unknown's avatar
unknown committed
161

162
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
unknown's avatar
unknown committed
163

unknown's avatar
unknown committed
164
#searchconthardwarefr3 forumconthardwarefr7
165
CREATE TABLE `t1` (
unknown's avatar
unknown committed
166 167 168 169 170 171
  `topic` mediumint(8) unsigned NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`pseudo`,`date`,`topic`),
  KEY `topic` (`topic`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
172
INSERT INTO t1 (topic,date,pseudo) VALUES
unknown's avatar
unknown committed
173
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
174 175 176 177 178
EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
unknown's avatar
unknown committed
179
-- error 1240
180 181 182
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
drop table t1;
unknown's avatar
unknown committed
183

184 185
#forumconthardwarefr7 searchconthardwarefr7
CREATE TABLE `t1` (
unknown's avatar
unknown committed
186 187 188 189 190 191
  `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
  `maxnumrep` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`numeropost`),
  UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM ROW_FORMAT=FIXED;

192
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
unknown's avatar
unknown committed
193

194
CREATE TABLE `t2` (
unknown's avatar
unknown committed
195 196 197 198 199 200 201
      `mot` varchar(30) NOT NULL default '',
      `topic` mediumint(8) unsigned NOT NULL default '0',
      `date` date NOT NULL default '0000-00-00',
      `pseudo` varchar(35) NOT NULL default '',
       PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`)
    ) TYPE=MyISAM ROW_FORMAT=DYNAMIC;

202 203 204
INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
205
-- error 1054
206
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;
unknown's avatar
unknown committed
207 208
-- error 1054
SELECT 1 IN (SELECT 1 FROM t2 HAVING a);
209 210 211

SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
unknown's avatar
unknown committed
212
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
213 214
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
unknown's avatar
unknown committed
215
SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
216 217 218
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;
unknown's avatar
unknown committed
219 220
SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);
SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
221 222 223 224
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;
unknown's avatar
unknown committed
225
drop table t1,t2;
unknown's avatar
unknown committed
226

227 228
#forumconthardwarefr7
CREATE TABLE `t1` (
unknown's avatar
unknown committed
229 230 231 232 233 234
  `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
  `maxnumrep` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`numeropost`),
  UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM ROW_FORMAT=FIXED;

235
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
unknown's avatar
unknown committed
236
-- error 1240
237
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
238
-- error 1240
239 240
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
drop table t1;
241

242 243 244
#iftest
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO t1 VALUES ();
245
-- error 1240
246
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');
247
drop table t1;
unknown's avatar
unknown committed
248

249 250
# threadhardwarefr7
CREATE TABLE `t1` (
unknown's avatar
unknown committed
251 252 253 254 255 256 257
  `numeropost` mediumint(8) unsigned NOT NULL default '0',
  `numreponse` int(10) unsigned NOT NULL auto_increment,
  `pseudo` varchar(35) NOT NULL default '',
  PRIMARY KEY  (`numeropost`,`numreponse`),
  UNIQUE KEY `numreponse` (`numreponse`),
  KEY `pseudo` (`pseudo`,`numeropost`)
) TYPE=MyISAM;
unknown's avatar
unknown committed
258
-- error 1245
259
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
260
-- error 1054
261 262 263
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
unknown's avatar
unknown committed
264
-- error 1240
265 266 267
EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
unknown's avatar
unknown committed
268
drop table t1;
unknown's avatar
unknown committed
269

270 271
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
unknown's avatar
unknown committed
272
SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;
unknown's avatar
Merge  
unknown committed
273
drop table t1;
274 275 276 277 278 279 280

#update with subselects
create table t1 (a int NOT NULL, b int, primary key (a));
create table t2 (a int NOT NULL, b int, primary key (a));
insert into t1 values (0, 10),(1, 11),(2, 12);
insert into t2 values (1, 21),(2, 22),(3, 23);
select * from t1;
281 282
-- error 1093
update t1 set b= (select b from t1);
unknown's avatar
unknown committed
283 284
-- error 1240
update t1 set b= (select b from t2);
285 286 287 288
update t1 set b= (select b from t2 where t1.a = t2.a);
select * from t1;
drop table t1, t2;

unknown's avatar
unknown committed
289 290 291 292 293 294 295
#delete with subselects
create table t1 (a int NOT NULL, b int, primary key (a));
create table t2 (a int NOT NULL, b int, primary key (a));
insert into t1 values (0, 10),(1, 11),(2, 12);
insert into t2 values (1, 21),(2, 12),(3, 23);
select * from t1;
select * from t1 where b = (select b from t2 where t1.a = t2.a);
296 297
-- error 1093
delete from t1 where b = (select b from t1);
unknown's avatar
unknown committed
298 299
-- error 1240
delete from t1 where b = (select b from t2);
unknown's avatar
unknown committed
300 301 302
delete from t1 where b = (select b from t2 where t1.a = t2.a);
select * from t1;
drop table t1, t2;
303

304
#multi-delete with subselects
305

306 307 308 309 310 311 312 313
create table t11 (a int NOT NULL, b int, primary key (a));
create table t12 (a int NOT NULL, b int, primary key (a));
create table t2 (a int NOT NULL, b int, primary key (a));
insert into t11 values (0, 10),(1, 11),(2, 12);
insert into t12 values (33, 10),(22, 11),(2, 12);
insert into t2 values (1, 21),(2, 12),(3, 23);
select * from t11;
select * from t12;
314 315
-- error 1093
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
unknown's avatar
unknown committed
316 317
-- error 1240
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
318 319 320 321 322
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
select * from t11;
select * from t12;
drop table t11, t12, t2;

323 324 325
#insert with subselects
CREATE TABLE t1 (x int);
create table t2 (a int);
unknown's avatar
unknown committed
326
create table t3 (a int);
327
insert into t2 values (1);
unknown's avatar
unknown committed
328
insert into t3 values (1),(2);
329 330
-- error 1093
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
unknown's avatar
unknown committed
331 332
-- error 1240
INSERT INTO t1 (x) VALUES ((SELECT a FROM t3));
333 334 335 336 337 338 339 340 341 342
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
select * from t1;
insert into t2 values (1);
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
-- sleep 1
select * from t1;
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
select * from t1;
-- error 1093
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
343
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
344 345
-- sleep 1
select * from t1;
unknown's avatar
unknown committed
346
drop table t1, t2, t3;
347 348 349 350

#replace with subselects
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
unknown's avatar
unknown committed
351
create table t3 (a int);
352
insert into t2 values (1);
unknown's avatar
unknown committed
353
insert into t3 values (1),(2);
354
select * from t1;
355 356
-- error 1093
replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));
unknown's avatar
unknown committed
357 358
-- error 1240
replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));
359 360 361 362 363 364 365 366 367 368 369 370
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
select * from t1;
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
select * from t1;
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
-- sleep 1
select * from t1;
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
-- sleep 1
select * from t1;
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
select * from t1;
unknown's avatar
unknown committed
371
drop table t1, t2, t3;
unknown's avatar
unknown committed
372 373

-- error 1096
374
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
375

unknown's avatar
unknown committed
376 377 378 379 380
CREATE TABLE t (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t VALUES (1),(2);
SELECT * FROM t WHERE id IN (SELECT 1);
EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1);
SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
unknown's avatar
unknown committed
381 382
SELECT * FROM t WHERE id IN (SELECT 1+(select 1));
EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1+(select 1));
unknown's avatar
unknown committed
383 384 385
EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 3);
SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2);
386 387 388
-- error 1093
INSERT INTO t VALUES ((SELECT * FROM t));
SELECT * FROM t;
unknown's avatar
unknown committed
389 390 391 392
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 values (1),(1);
-- error 1240
UPDATE t SET id=(SELECT * FROM t1);
393
drop table t, t1;
394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437


#NULL test
create table t (a int);
insert into t values (1),(2),(3);
select 1 IN (SELECT * from t);
select 10 IN (SELECT * from t);
select NULL IN (SELECT * from t);
update t set a=NULL where a=2;
select 1 IN (SELECT * from t);
select 3 IN (SELECT * from t);
select 10 IN (SELECT * from t);
select 1 > ALL (SELECT * from t);
select 10 > ALL (SELECT * from t);
select 1 > ANY (SELECT * from t);
select 10 > ANY (SELECT * from t);
drop table t;
create table t (a varchar(20));
insert into t values ('A'),('BC'),('DEF');
select 'A' IN (SELECT * from t);
select 'XYZS' IN (SELECT * from t);
select NULL IN (SELECT * from t);
update t set a=NULL where a='BC';
select 'A' IN (SELECT * from t);
select 'DEF' IN (SELECT * from t);
select 'XYZS' IN (SELECT * from t);
select 'A' > ALL (SELECT * from t);
select 'XYZS' > ALL (SELECT * from t);
select 'A' > ANY (SELECT * from t);
select 'XYZS' > ANY (SELECT * from t);
drop table t;
create table t (a float);
insert into t values (1.5),(2.5),(3.5);
select 1.5 IN (SELECT * from t);
select 10.5 IN (SELECT * from t);
select NULL IN (SELECT * from t);
update t set a=NULL where a=2.5;
select 1.5 IN (SELECT * from t);
select 3.5 IN (SELECT * from t);
select 10.5 IN (SELECT * from t);
select 1.5 > ALL (SELECT * from t);
select 10.5 > ALL (SELECT * from t);
select 1.5 > ANY (SELECT * from t);
select 10.5 > ANY (SELECT * from t);
438 439
explain select (select a+1) from t;
select (select a+1) from t;
440
drop table t;
441 442 443 444 445 446 447

#LIMIT is not supported now
create table t (a float);
-- error 1235
select 10.5 IN (SELECT * from t LIMIT 1);
-- error 1235
select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5);
unknown's avatar
unknown committed
448
drop table t;
449

450 451 452 453 454 455 456 457
create table t1 (a int, b int, c varchar(10));
create table t2 (a int);
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
insert into t2 values (1),(2),(NULL);
select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a)  from t2;
select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
drop table t1,t2;
unknown's avatar
unknown committed
458

459 460 461 462 463 464 465 466 467 468 469
create table t1 (a int, b real, c varchar(10));
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
select ROW(1, 1, 'a') IN (select a,b,c from t1);
select ROW(1, 2, 'a') IN (select a,b,c from t1);
select ROW(1, 1, 'a') IN (select b,a,c from t1);
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
470
-- error 1235
471 472
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
drop table t1;
unknown's avatar
unknown committed
473

474 475 476
create table t1 (a int);
insert into t1 values (1);
do @a:=(SELECT a from t1);
unknown's avatar
unknown committed
477
select @a;
unknown's avatar
unknown committed
478
set @a:=2;
unknown's avatar
merge  
unknown committed
479
set @a:=(SELECT a from t1);
unknown's avatar
unknown committed
480
select @a;
481
drop table t1;
unknown's avatar
unknown committed
482
-- error 1146
483
do (SELECT a from t1);
unknown's avatar
unknown committed
484
-- error 1146
unknown's avatar
merge  
unknown committed
485
set @a:=(SELECT a from t1);
486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561

CREATE TABLE t1 (a int, KEY(a)); 
HANDLER t1 OPEN;
-- error 1149
HANDLER t1 READ a=((SELECT 1));
HANDLER t1 CLOSE;
drop table t1;

create table t1 (a int);
create table t2 (b int);
insert into t1 values (1),(2);
insert into t2 values (1);
select a from t1 where a in (select a from t1 where a in (select b from t2));
drop table t1, t2;

create table t1 (a int, b int);
create table t2 like t1;
insert into t1 values (1,2),(1,3),(1,4),(1,5);
insert into t2 values (1,2),(1,3);
select * from t1 where row(a,b) in (select a,b from t2);
drop table t1, t2;

CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY  (`i`)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1);
-- error 1111
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
drop table t1;

#test of uncacheable subqueries
CREATE TABLE t (a int(1));
EXPLAIN SELECT (SELECT RAND() FROM t) FROM t;
EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t) FROM t;
EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t) FROM t;
drop table t;


CREATE TABLE `t1` (
  `mot` varchar(30) character set latin1 NOT NULL default '',
  `topic` mediumint(8) unsigned NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
  KEY `pseudo` (`pseudo`,`date`,`topic`),
  KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `t2` (
  `mot` varchar(30) character set latin1 NOT NULL default '',
  `topic` mediumint(8) unsigned NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
  KEY `pseudo` (`pseudo`,`date`,`topic`),
  KEY `topic` (`topic`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `t3` (
  `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
  `maxnumrep` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`numeropost`),
  UNIQUE KEY `maxnumrep` (`maxnumrep`)
) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');

INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');

INSERT INTO t3 VALUES (1,1);

SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
numeropost=topic);
select * from t1;
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
select * from t1;

drop table t1, t2, t3;