subselect.result 15.5 KB
Newer Older
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
1 2 3
select (select 2);
(select 2)
2
4 5 6 7
SELECT (SELECT 1) UNION SELECT (SELECT 2);
(SELECT 1)
1
2
8 9 10
SELECT (SELECT (SELECT 0 UNION SELECT 0));
(SELECT (SELECT 0 UNION SELECT 0))
0
11
SELECT (SELECT 1 FROM (SELECT 1) HAVING a=1) as a;
12
Reference 'a' not supported (forward reference in item list)
13
SELECT (SELECT 1 FROM (SELECT 1) HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) HAVING a=1) as b;
14
Reference 'b' not supported (forward reference in item list)
15 16 17
SELECT (SELECT 1),MAX(1) FROM (SELECT 1);
(SELECT 1)	MAX(1)
1	1
18 19
SELECT (SELECT a) as a;
Reference 'a' not supported (forward reference in item list)
20 21 22 23 24 25 26 27 28 29
EXPLAIN SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
3	DEPENDENT SUBSELECT								No tables used
2	DERIVED								No tables used
SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1;
1
1
SELECT (SELECT 1), a;
Unknown column 'a' in 'field list'
30 31 32
SELECT 1 as a FROM (SELECT 1) HAVING (SELECT a)=1;
a
1
33 34
SELECT 1 FROM (SELECT (SELECT a));
Unknown column 'a' in 'field list'
35
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
36 37 38 39 40 41 42
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);
43 44
select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
Reference 'a1' not supported (forward reference in item list)
45 46 47 48 49 50 51 52
select (select a from t1 where t1.a=t2.a), a from t2;
(select a from t1 where t1.a=t2.a)	a
NULL	1
2	2
select (select a from t1 where t1.a=t2.b), a from t2;
(select a from t1 where t1.a=t2.b)	a
NULL	1
NULL	2
53 54 55 56 57 58 59 60 61 62 63 64
select (select a from t1), a from t2;
(select a from t1)	a
2	1
2	2
select (select a from t3), a from t2;
(select a from t3)	a
NULL	1
NULL	2
select * from t2 where t2.a=(select a from t1);
a	b
2	7
insert into t3 values (6),(7),(3);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
65
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
66 67 68
a	b
1	7
2	7
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
69
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
70 71 72 73 74
union (select * from t4 order by a limit 2) limit 3;
a	b
1	7
2	7
3	8
75
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
76 77 78 79 80 81
union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
a	b
1	7
2	7
3	8
4	8
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
82 83 84
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);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
85
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
86
2	SUBSELECT	t3	ALL	NULL	NULL	NULL	NULL	3	Using filesort
87
3	UNION	t4	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
88
4	SUBSELECT	t2	ALL	NULL	NULL	NULL	NULL	2	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
89 90 91 92
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
(select a from t3 where a<t2.a*4 order by 1 desc limit 1)	a
3	1
7	2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
93 94 95 96
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;
(select t3.a from t3 where a<8 order by 1 desc limit 1)	a
7	2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
97 98 99
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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
100
1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	
101 102
3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
2	SUBSELECT	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
103 104 105 106 107 108 109 110 111 112 113
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);
a
2
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);
a
2
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);
a
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
b	(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
8	7.5000
114 115
8	4.5000
9	7.5000
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
116 117
explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
118
1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
119
2	DEPENDENT SUBSELECT	t2	ALL	NULL	NULL	NULL	NULL	2	
120
3	DEPENDENT SUBSELECT	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
121 122 123 124 125 126 127
select * from t3 where exists (select * from t2 where t2.b=t3.a);
a
7
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
a
6
3
128 129 130 131 132 133 134 135
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);
b	ma
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a)
from t2 where t2.b=t4.b);
b	ma
7	12
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
136 137 138 139 140 141 142 143 144 145 146 147 148 149 150
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;
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)	a
NULL	1
2	2
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;
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)	a
NULL	1
2	2
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;
(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)	a
NULL	1
2	2
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
151 152
explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
153
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
154 155
2	DEPENDENT SUBSELECT	t1	system	NULL	NULL	NULL	NULL	1	Using where
3	DEPENDENT UNION	t5	ALL	NULL	NULL	NULL	NULL	2	Using where
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
156
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
157
Subselect returns more than 1 record
158 159 160 161 162
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);
163 164 165 166
patient_uq	clinic_uq
1	1
1	2
2	2
167 168
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
Column: 'a' in field list is ambiguous
169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184
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);
a	b
W	1732-02-22
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
a	b
W	1
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);
a	b
W	a
185 186
drop table if exists t8;
CREATE TABLE `t8` (
187 188 189 190 191
`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;
192 193 194 195
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');
196
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
197 198 199 200 201 202
1	PRIMARY	t8	const	PRIMARY	PRIMARY	35	const	1	
4	SUBSELECT	t8	const	PRIMARY	PRIMARY	35	const	1	
2	SUBSELECT	t8	const	PRIMARY	PRIMARY	35	const	1	
3	SUBSELECT	t8	const	PRIMARY	PRIMARY	35	const	1	
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
203
Subselect returns more than 1 field
204
SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
205 206
pseudo='joce');
Subselect returns more than 1 field
207
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
208 209
pseudo
joce
210
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');
211
Subselect returns more than 1 record
212 213
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
CREATE TABLE `t1` (
214 215 216 217 218 219
`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;
220
INSERT INTO t1 (topic,date,pseudo) VALUES
221
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
222
EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
223
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
224 225
1	SIMPLE	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
226 227
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY								No tables used
228 229
2	SUBSELECT	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
230 231
date
2002-08-03
232 233
SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')
234
2002-08-03
235
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
236 237 238 239
1
1
1
1
240
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
241
Subselect returns more than 1 record
242
EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
243
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
244
1	PRIMARY	t1	index	NULL	topic	3	NULL	2	Using index
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
245 246
2	SUBSELECT								No tables used
3	UNION								No tables used
247 248
drop table t1;
CREATE TABLE `t1` (
249 250 251 252 253
`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;
254 255
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
CREATE TABLE `t2` (
256 257 258 259 260 261
`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;
262 263
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);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
264 265
a
40143
266
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;
267 268 269
numeropost	maxnumrep
43506	2
40143	1
270
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1);
271
Unknown column 'a' in 'having clause'
272 273 274
drop table t1, t2;
drop table if exists t1;
CREATE TABLE `t1` (
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
275 276 277 278 279
`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;
280 281
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
282
Subselect returns more than 1 record
283
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
284
Subselect returns more than 1 record
285 286 287 288
drop table t1;
CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO t1 VALUES ();
SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) HAVING field='b');
289
Subselect returns more than 1 record
290 291
drop table t1;
CREATE TABLE `t1` (
292 293 294 295 296 297 298
`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;
299
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
300
Reference 'numreponse' not supported (forward reference in item list)
301
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;
302
Unknown column 'a' in 'having clause'
303 304 305 306
SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;
numreponse	(SELECT numeropost FROM t1 HAVING numreponse=1)
INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');
EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
307
Subselect returns more than 1 record
308
EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
309 310
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE								Select tables optimized away
311
EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
312
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
313
1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	
314
2	SUBSELECT								Select tables optimized away
315
drop table t1;
316 317 318 319 320 321
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
SELECT 1 FROM (SELECT a FROM t1) HAVING (SELECT a)=1;
1
1
drop table t1;
322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337
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;
a	b
0	10
1	11
2	12
update t1 set b= (select b from t2 where t1.a = t2.a);
select * from t1;
a	b
0	NULL
1	21
2	22
drop table t1, t2;
338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355
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;
a	b
0	10
1	11
2	12
select * from t1 where b = (select b from t2 where t1.a = t2.a);
a	b
2	12
delete from t1 where b = (select b from t2 where t1.a = t2.a);
select * from t1;
a	b
0	10
1	11
drop table t1, t2;
356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382
drop table if exists t11, t12, t2;
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;
a	b
0	10
1	11
2	12
select * from t12;
a	b
33	10
22	11
2	12
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;
a	b
0	10
1	11
select * from t12;
a	b
33	10
22	11
drop table t11, t12, t2;
383 384 385 386 387 388 389 390 391 392 393 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 438 439 440 441 442 443
CREATE TABLE t1 (x int);
create table t2 (a int);
insert into t2 values (1);
INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));
select * from t1;
x
1
insert into t2 values (1);
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));
select * from t1;
x
1
2
INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;
select * from t1;
x
1
2
3
3
INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
INSERT TABLE 't1' isn't allowed in FROM table list
INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t1));
select * from t1;
x
1
2
3
3
9
drop table t1, t2;
CREATE TABLE t1 (x int not null, y int, primary key (x));
create table t2 (a int);
insert into t2 values (1);
select * from t1;
x	y
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));
select * from t1;
x	y
1	2
replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));
select * from t1;
x	y
1	3
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));
select * from t1;
x	y
1	3
4	1
replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));
select * from t1;
x	y
1	3
4	2
replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));
select * from t1;
x	y
1	3
4	2
2	1
drop table t1, t2;