~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
drop table if exists t1,t2,t3,t4,t5,t6;
2
CREATE TABLE t1 (a int not null, b char (10) not null);
3
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
4
CREATE TABLE t2 (a int not null, b char (10) not null);
5
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
6
select a,b from t1 union distinct select a,b from t2;
7
a	b
8
1	a
9
2	b
10
3	c
11
4	d
12
5	f
13
6	e
14
select a,b from t1 union all select a,b from t2;
15
a	b
16
1	a
17
2	b
18
3	c
19
3	c
20
3	c
21
4	d
22
5	f
23
6	e
24
select a,b from t1 union all select a,b from t2 order by b;
25
a	b
26
1	a
27
2	b
28
3	c
29
3	c
30
3	c
31
4	d
32
6	e
33
5	f
34
select a,b from t1 union all select a,b from t2 union select 7,'g';
35
a	b
36
1	a
37
2	b
38
3	c
39
4	d
40
5	f
41
6	e
42
7	g
43
select 0,'#' union select a,b from t1 union all select a,b from t2 union select 7,'gg';
44
0	#
45
0	#
46
1	a
47
2	b
48
3	c
49
4	d
50
5	f
51
6	e
52
7	gg
53
select a,b from t1 union select a,b from t1;
54
a	b
55
1	a
56
2	b
57
3	c
58
select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b;
59
t1	b	count(*)
60
t1	a	1
61
t1	b	1
62
t1	c	2
63
t2	c	1
64
t2	d	1
65
t2	e	1
66
t2	f	1
67
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 4;
68
a	b
69
1	a
70
2	b
71
3	c
72
4	d
73
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1);
74
a	b
75
1	a
76
2	b
77
3	c
78
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by b desc;
79
a	b
80
3	c
81
2	b
82
1	a
83
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by t1.b;
84
ERROR 42000: Table 't1' from one of the SELECTs cannot be used in global ORDER clause
85
explain extended (select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by b desc;
86
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
87
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
88
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using filesort
89
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
90
Warnings:
91
Note	1003	(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` limit 2) union all (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`a` limit 1) order by `b` desc
92
(select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 2;
93
a	b
94
1	a
95
2	b
96
select found_rows();
97
found_rows()
98
6
99
select sql_calc_found_rows  a,b from t1  union all select a,b from t2 limit 2;
100
a	b
101
1	a
102
2	b
103
select found_rows();
104
found_rows()
105
8
106
explain select a,b from t1 union all select a,b from t2;
107
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
108
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
109
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	4	
110
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
111
explain select xx from t1 union select 1;
112
ERROR 42S22: Unknown column 'xx' in 'field list'
113
explain select a,b from t1 union select 1;
114
ERROR 21000: The used SELECT statements have a different number of columns
115
explain select 1 union select a,b from t1 union select 1;
116
ERROR 21000: The used SELECT statements have a different number of columns
117
explain select a,b from t1 union select 1 limit 0;
118
ERROR 21000: The used SELECT statements have a different number of columns
119
select a,b from t1 into outfile 'skr' union select a,b from t2;
120
ERROR HY000: Incorrect usage of UNION and INTO
121
select a,b from t1 order by a union select a,b from t2;
122
ERROR HY000: Incorrect usage of UNION and ORDER BY
123
insert into t3 select a from t1 order by a union select a from t2;
124
ERROR HY000: Incorrect usage of UNION and ORDER BY
125
create table t3 select a,b from t1 union select a from t2;
126
ERROR 21000: The used SELECT statements have a different number of columns
127
select a,b from t1 union select a from t2;
128
ERROR 21000: The used SELECT statements have a different number of columns
129
select * from t1 union select a from t2;
130
ERROR 21000: The used SELECT statements have a different number of columns
131
select a from t1 union select * from t2;
132
ERROR 21000: The used SELECT statements have a different number of columns
133
select * from t1 union select SQL_BUFFER_RESULT * from t2;
134
ERROR 42000: Incorrect usage/placement of 'SQL_BUFFER_RESULT'
135
create table t3 select a,b from t1 union all select a,b from t2;
136
insert into t3 select a,b from t1 union all select a,b from t2;
137
replace into t3 select a,b as c from t1 union all select a,b from t2;
138
drop table t1,t2,t3;
139
select * union select 1;
140
ERROR HY000: No tables used
141
select 1 as a,(select a union select a);
142
a	(select a union select a)
143
1	1
144
(select 1) union (select 2) order by 0;
145
ERROR 42S22: Unknown column '0' in 'order clause'
146
SELECT @a:=1 UNION SELECT @a:=@a+1;
147
@a:=1
148
1
149
2
150
(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a);
151
ERROR 42S22: Unknown column 'a' in 'field list'
152
(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);
153
1	3
154
1	3
155
2	1
156
CREATE TABLE t1 (
157
`pseudo` char(35) NOT NULL default '',
158
`pseudo1` char(35) NOT NULL default '',
159
`same` tinyint(1) unsigned NOT NULL default '1',
160
PRIMARY KEY  (`pseudo1`),
161
KEY `pseudo` (`pseudo`)
162
) ENGINE=MyISAM;
163
INSERT INTO t1 (pseudo,pseudo1,same) VALUES ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1);
164
SELECT pseudo FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo FROM t1 WHERE pseudo='joce';
165
pseudo
166
dekad
167
joce
168
SELECT pseudo1 FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t1 WHERE pseudo='joce';
169
pseudo1
170
joce
171
testtt
172
tsestset
173
SELECT * FROM t1 WHERE pseudo1='joce' UNION SELECT * FROM t1 WHERE pseudo='joce' order by pseudo desc,pseudo1 desc;
174
pseudo	pseudo1	same
175
joce	tsestset	1
176
joce	testtt	1
177
dekad	joce	1
178
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT pseudo FROM t1 WHERE pseudo1='joce';
179
pseudo1
180
testtt
181
tsestset
182
dekad
183
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION ALL SELECT pseudo FROM t1 WHERE pseudo1='joce';
184
pseudo1
185
testtt
186
tsestset
187
dekad
188
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT 1;
189
pseudo1
190
testtt
191
tsestset
192
1
193
drop table t1;
194
create table t1 (a int);
195
create table t2 (a int);
196
insert into t1 values (1),(2),(3),(4),(5);
197
insert into t2 values (11),(12),(13),(14),(15);
198
(select * from t1 limit 2) union (select * from t2 limit 3) limit 4;
199
a
200
1
201
2
202
11
203
12
204
(select * from t1 limit 2) union (select * from t2 limit 3);
205
a
206
1
207
2
208
11
209
12
210
13
211
(select * from t1 limit 2) union (select * from t2 limit 20,3);
212
a
213
1
214
2
215
set SQL_SELECT_LIMIT=2;
216
(select * from t1 limit 1) union (select * from t2 limit 3);
217
a
218
1
219
11
220
set SQL_SELECT_LIMIT=DEFAULT;
221
drop table t1,t2;
222
CREATE TABLE t1 (
223
cid smallint(5) unsigned NOT NULL default '0',
224
cv varchar(250) NOT NULL default '',
225
PRIMARY KEY  (cid),
226
UNIQUE KEY cv (cv)
227
) ;
228
INSERT INTO t1 VALUES (8,'dummy');
229
CREATE TABLE t2 (
230
cid bigint(20) unsigned NOT NULL auto_increment,
231
cap varchar(255) NOT NULL default '',
232
PRIMARY KEY  (cid),
233
KEY cap (cap)
234
) ;
235
CREATE TABLE t3 (
236
gid bigint(20) unsigned NOT NULL auto_increment,
237
gn varchar(255) NOT NULL default '',
238
must tinyint(4) default NULL,
239
PRIMARY KEY  (gid),
240
KEY gn (gn)
241
) ;
242
INSERT INTO t3 VALUES (1,'V1',NULL);
243
CREATE TABLE t4 (
244
uid bigint(20) unsigned NOT NULL default '0',
245
gid bigint(20) unsigned default NULL,
246
rid bigint(20) unsigned default NULL,
247
cid bigint(20) unsigned default NULL,
248
UNIQUE KEY m (uid,gid,rid,cid),
249
KEY uid (uid),
250
KEY rid (rid),
251
KEY cid (cid),
252
KEY container (gid,rid,cid)
253
) ;
254
INSERT INTO t4 VALUES (1,1,NULL,NULL);
255
CREATE TABLE t5 (
256
rid bigint(20) unsigned NOT NULL auto_increment,
257
rl varchar(255) NOT NULL default '',
258
PRIMARY KEY  (rid),
259
KEY rl (rl)
260
) ;
261
CREATE TABLE t6 (
262
uid bigint(20) unsigned NOT NULL auto_increment,
263
un varchar(250) NOT NULL default '',
264
uc smallint(5) unsigned NOT NULL default '0',
265
PRIMARY KEY  (uid),
266
UNIQUE KEY nc (un,uc),
267
KEY un (un)
268
) ;
269
INSERT INTO t6 VALUES (1,'test',8);
270
SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test";
271
uid	rl	g1	cid	gg
272
1	NULL	V1	NULL	1
273
SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test";
274
uid	rl	g1	cid	gg
275
(SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test");
276
uid	rl	g1	cid	gg
277
1	NULL	V1	NULL	1
278
drop table t1,t2,t3,t4,t5,t6;
279
CREATE TABLE t1 (a int not null, b char (10) not null);
280
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
281
CREATE TABLE t2 (a int not null, b char (10) not null);
282
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
283
create table t3 select a,b from t1 union select a,b from t2;
284
create table t4 (select a,b from t1) union (select a,b from t2) limit 2;
285
insert into  t4 select a,b from t1 union select a,b from t2;
286
insert into  t3 (select a,b from t1) union (select a,b from t2) limit 2;
287
select * from t3;
288
a	b
289
1	a
290
2	b
291
3	c
292
4	d
293
5	f
294
6	e
295
1	a
296
2	b
297
select * from t4;
298
a	b
299
1	a
300
2	b
301
1	a
302
2	b
303
3	c
304
4	d
305
5	f
306
6	e
307
drop table t1,t2,t3,t4;
308
create table t1 (a int);
309
insert into t1 values (1),(2),(3);
310
create table t2 (a int);
311
insert into t2 values (3),(4),(5);
312
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1;
313
a
314
1
315
select found_rows();
316
found_rows()
317
6
318
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;
319
a
320
1
321
3
322
select found_rows();
323
found_rows()
324
4
325
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);
326
a
327
1
328
3
329
4
330
5
331
select found_rows();
332
found_rows()
333
4
334
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);
335
a
336
1
337
2
338
3
339
3
340
select found_rows();
341
found_rows()
342
4
343
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
344
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
345
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;
346
a
347
1
348
3
349
select found_rows();
350
found_rows()
351
6
352
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
353
a
354
1
355
2
356
select found_rows();
357
found_rows()
358
6
359
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;
360
a
361
1
362
2
363
select found_rows();
364
found_rows()
365
6
366
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
367
a
368
1
369
2
370
3
371
4
372
5
373
select found_rows();
374
found_rows()
375
6
376
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
377
a
378
1
379
2
380
3
381
4
382
5
383
select found_rows();
384
found_rows()
385
5
386
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
387
a
388
1
389
3
390
4
391
5
392
select found_rows();
393
found_rows()
394
6
395
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
396
a
397
1
398
3
399
select found_rows();
400
found_rows()
401
6
402
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
403
a
404
3
405
4
406
select found_rows();
407
found_rows()
408
6
409
SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
410
a
411
3
412
4
413
5
414
select found_rows();
415
found_rows()
416
5
417
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
418
a
419
5
420
(SELECT * FROM t1 ORDER by a) UNION ALL (SELECT * FROM t2 ORDER BY a) ORDER BY A desc LIMIT 4;
421
a
422
5
423
4
424
3
425
3
426
(SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;
427
ERROR 42000: Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'
428
create temporary table t1 select a from t1 union select a from t2;
429
drop temporary table t1;
430
create table t1 select a from t1 union select a from t2;
431
ERROR HY000: You can't specify target table 't1' for update in FROM clause
432
select a from t1 union select a from t2 order by t2.a;
433
ERROR 42S22: Unknown column 't2.a' in 'order clause'
434
drop table t1,t2;
435
select length(version()) > 1 as `*` UNION select 2;
436
*
437
1
438
2
439
create table t1 (a int);
440
insert into t1 values (0), (3), (1), (2);
441
explain (select * from t1) union (select * from t1) order by a;
442
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
443
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
444
2	UNION	t1	ALL	NULL	NULL	NULL	NULL	4	
445
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
446
drop table t1;
447
CREATE TABLE t1 (  id int(3) unsigned default '0') ENGINE=MyISAM;
448
INSERT INTO t1 (id) VALUES("1");
449
CREATE TABLE t2 ( id int(3) unsigned default '0',  id_master int(5) default '0',  text1 varchar(5) default NULL,  text2 varchar(5) default NULL) ENGINE=MyISAM;
450
INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1",
451
"foo1", "bar1");
452
INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1",
453
"foo2", "bar2");
454
INSERT INTO t2 (id, id_master, text1, text2) VALUES("3", "1", NULL,
455
"bar3");
456
INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1",
457
"foo4", "bar4");
458
SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;
459
id_master	id	text1	text2
460
1	1	NULL	ABCDE
461
1	1	foo1	bar1
462
1	2	foo2	bar2
463
1	3	NULL	bar3
464
1	4	foo4	bar4
465
SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;
466
id_master	id	text1	text2
467
1	1	ABCDE	ABCDE
468
1	1	foo1	bar1
469
1	2	foo2	bar2
470
1	3	NULL	bar3
471
1	4	foo4	bar4
472
drop table if exists t1,t2;
473
create table t1 (a int not null primary key auto_increment, b int, key(b));
474
create table t2 (a int not null primary key auto_increment, b int);
475
insert into t1 (b) values (1),(2),(2),(3);
476
insert into t2 (b) values (10),(11),(12),(13);
477
explain extended (select * from t1 where a=1) union (select * from t2 where a=1);
478
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
479
1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	
480
2	UNION	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	
481
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
482
Warnings:
483
Note	1003	(select '1' AS `a`,'1' AS `b` from `test`.`t1` where ('1' = 1)) union (select '1' AS `a`,'10' AS `b` from `test`.`t2` where ('1' = 1))
484
(select * from t1 where a=5) union (select * from t2 where a=1);
485
a	b
486
1	10
487
(select * from t1 where a=5 and a=6) union (select * from t2 where a=1);
488
a	b
489
1	10
490
(select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1);
491
a	b
492
1	10
493
(select * from t1 where a=1) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);
494
a	b
495
1	1
496
2	2
497
3	3
498
4	4
499
explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t2.a from t1,t2 where t1.a=t2.a);
500
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
501
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
502
2	UNION	t1	index	PRIMARY	PRIMARY	4	NULL	4	Using index
503
2	UNION	t2	index	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index; Using join buffer
504
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
505
explain (select * from t1 where a=1) union (select * from t1 where b=1);
506
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
507
1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	
508
2	UNION	t1	ref	b	b	5	const	1	
509
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
510
drop table t1,t2;
511
create table t1 (   id int not null auto_increment, primary key (id)   ,user_name text );
512
create table t2 (    id int not null auto_increment, primary key (id)   ,group_name text );
513
create table t3 (    id int not null auto_increment, primary key (id)   ,user_id int   ,index user_idx (user_id)   ,foreign key (user_id) references users(id)   ,group_id int   ,index group_idx (group_id)   ,foreign key (group_id) references groups(id) );
514
insert into t1 (user_name) values ('Tester');
515
insert into t2 (group_name) values ('Group A');
516
insert into t2 (group_name) values ('Group B');
517
insert into t3 (user_id, group_id) values (1,1);
518
select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION  select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c;
519
is_in_group	user_name	group_name	id
520
1	Tester	Group A	1
521
0	Tester	Group A	NULL
522
0	Tester	Group B	NULL
523
drop table t1, t2, t3;
524
create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
525
create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
526
insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);
527
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
528
SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id union SELECT 0, 0;
529
pla_id	matintnum
530
100	a
531
101	a
532
102	a
533
103	b
534
104	b
535
105	c
536
0	0
537
drop table t1, t2;
538
create table t1 SELECT "a" as a UNION select "aa" as a;
539
select * from t1;
540
a
541
a
542
aa
543
show create table t1;
544
Table	Create Table
545
t1	CREATE TABLE `t1` (
546
  `a` varchar(2) NOT NULL DEFAULT ''
547
) ENGINE=MyISAM DEFAULT CHARSET=latin1
548
drop table t1;
549
create table t1 SELECT 12 as a UNION select "aa" as a;
550
select * from t1;
551
a
552
12
553
aa
554
show create table t1;
555
Table	Create Table
556
t1	CREATE TABLE `t1` (
557
  `a` varbinary(2) NOT NULL DEFAULT ''
558
) ENGINE=MyISAM DEFAULT CHARSET=latin1
559
drop table t1;
560
create table t1 SELECT 12 as a UNION select 12.2 as a;
561
select * from t1;
562
a
563
12.0
564
12.2
565
show create table t1;
566
Table	Create Table
567
t1	CREATE TABLE `t1` (
568
  `a` decimal(3,1) NOT NULL DEFAULT '0.0'
569
) ENGINE=MyISAM DEFAULT CHARSET=latin1
570
drop table t1;
571
create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text);
572
insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, 1972, '1972-10-22', '1972-10-22 11:50', 'testc', 'testv', 'tetetetetest', 'teeeeeeeeeeeest');
573
create table t1 SELECT it2 from t2 UNION select it1 from t2;
574
select * from t1;
575
it2
576
1
577
NULL
578
show create table t1;
579
Table	Create Table
580
t1	CREATE TABLE `t1` (
581
  `it2` tinyint(4) DEFAULT NULL
582
) ENGINE=MyISAM DEFAULT CHARSET=latin1
583
drop table t1;
584
create table t1 SELECT it2 from t2 UNION select i from t2;
585
select * from t1;
586
it2
587
1
588
3
589
show create table t1;
590
Table	Create Table
591
t1	CREATE TABLE `t1` (
592
  `it2` int(11) NOT NULL DEFAULT '0'
593
) ENGINE=MyISAM DEFAULT CHARSET=latin1
594
drop table t1;
595
create table t1 SELECT i from t2 UNION select f from t2;
596
select * from t1;
597
i
598
3
599
1.5
600
show create table t1;
601
Table	Create Table
602
t1	CREATE TABLE `t1` (
603
  `i` double DEFAULT NULL
604
) ENGINE=MyISAM DEFAULT CHARSET=latin1
605
drop table t1;
606
create table t1 SELECT f from t2 UNION select d from t2;
607
select * from t1;
608
f
609
1.5
610
2.5
611
show create table t1;
612
Table	Create Table
613
t1	CREATE TABLE `t1` (
614
  `f` double DEFAULT NULL
615
) ENGINE=MyISAM DEFAULT CHARSET=latin1
616
drop table t1;
617
create table t1 SELECT ib from t2 UNION select f from t2;
618
select * from t1;
619
ib
620
4
621
1.5
622
show create table t1;
623
Table	Create Table
624
t1	CREATE TABLE `t1` (
625
  `ib` double DEFAULT NULL
626
) ENGINE=MyISAM DEFAULT CHARSET=latin1
627
drop table t1;
628
create table t1 SELECT ib from t2 UNION select d from t2;
629
select * from t1;
630
ib
631
4
632
2.5
633
show create table t1;
634
Table	Create Table
635
t1	CREATE TABLE `t1` (
636
  `ib` double DEFAULT NULL
637
) ENGINE=MyISAM DEFAULT CHARSET=latin1
638
drop table t1;
639
create table t1 SELECT f from t2 UNION select y from t2;
640
select * from t1;
641
f
642
1.5
643
1972
644
show create table t1;
645
Table	Create Table
646
t1	CREATE TABLE `t1` (
647
  `f` float DEFAULT NULL
648
) ENGINE=MyISAM DEFAULT CHARSET=latin1
649
drop table t1;
650
create table t1 SELECT f from t2 UNION select da from t2;
651
select * from t1;
652
f
653
1.5
654
1972-10-22
655
show create table t1;
656
Table	Create Table
657
t1	CREATE TABLE `t1` (
658
  `f` varbinary(12) DEFAULT NULL
659
) ENGINE=MyISAM DEFAULT CHARSET=latin1
660
drop table t1;
661
create table t1 SELECT y from t2 UNION select da from t2;
662
select * from t1;
663
y
664
1972
665
1972-10-22
666
show create table t1;
667
Table	Create Table
668
t1	CREATE TABLE `t1` (
669
  `y` varbinary(10) DEFAULT NULL
670
) ENGINE=MyISAM DEFAULT CHARSET=latin1
671
drop table t1;
672
create table t1 SELECT y from t2 UNION select dt from t2;
673
select * from t1;
674
y
675
1972
676
1972-10-22 11:50:00
677
show create table t1;
678
Table	Create Table
679
t1	CREATE TABLE `t1` (
680
  `y` varbinary(19) DEFAULT NULL
681
) ENGINE=MyISAM DEFAULT CHARSET=latin1
682
drop table t1;
683
create table t1 SELECT da from t2 UNION select dt from t2;
684
select * from t1;
685
da
686
1972-10-22 00:00:00
687
1972-10-22 11:50:00
688
show create table t1;
689
Table	Create Table
690
t1	CREATE TABLE `t1` (
691
  `da` datetime DEFAULT NULL
692
) ENGINE=MyISAM DEFAULT CHARSET=latin1
693
drop table t1;
694
create table t1 SELECT dt from t2 UNION select trim(sc) from t2;
695
select trim(dt) from t1;
696
trim(dt)
697
1972-10-22 11:50:00
698
testc
699
show create table t1;
700
Table	Create Table
701
t1	CREATE TABLE `t1` (
702
  `dt` varbinary(19) DEFAULT NULL
703
) ENGINE=MyISAM DEFAULT CHARSET=latin1
704
drop table t1;
705
create table t1 SELECT dt from t2 UNION select sv from t2;
706
select * from t1;
707
dt
708
1972-10-22 11:50:00
709
testv
710
show create table t1;
711
Table	Create Table
712
t1	CREATE TABLE `t1` (
713
  `dt` varbinary(19) DEFAULT NULL
714
) ENGINE=MyISAM DEFAULT CHARSET=latin1
715
drop table t1;
716
create table t1 SELECT sc from t2 UNION select sv from t2;
717
select * from t1;
718
sc
719
testc
720
testv
721
show create table t1;
722
Table	Create Table
723
t1	CREATE TABLE `t1` (
724
  `sc` varchar(10) DEFAULT NULL
725
) ENGINE=MyISAM DEFAULT CHARSET=latin1
726
drop table t1;
727
create table t1 SELECT dt from t2 UNION select b from t2;
728
select * from t1;
729
dt
730
1972-10-22 11:50:00
731
tetetetetest
732
show create table t1;
733
Table	Create Table
734
t1	CREATE TABLE `t1` (
735
  `dt` blob
736
) ENGINE=MyISAM DEFAULT CHARSET=latin1
737
drop table t1;
738
create table t1 SELECT sv from t2 UNION select b from t2;
739
select * from t1;
740
sv
741
testv
742
tetetetetest
743
show create table t1;
744
Table	Create Table
745
t1	CREATE TABLE `t1` (
746
  `sv` blob
747
) ENGINE=MyISAM DEFAULT CHARSET=latin1
748
drop table t1;
749
create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
750
select * from t1;
751
i
752
3
753
2.5
754
tetetetetest
755
show create table t1;
756
Table	Create Table
757
t1	CREATE TABLE `t1` (
758
  `i` blob
759
) ENGINE=MyISAM DEFAULT CHARSET=latin1
760
drop table t1;
761
create table t1 SELECT sv from t2 UNION select tx from t2;
762
select * from t1;
763
sv
764
testv
765
teeeeeeeeeeeest
766
show create table t1;
767
Table	Create Table
768
t1	CREATE TABLE `t1` (
769
  `sv` text
770
) ENGINE=MyISAM DEFAULT CHARSET=latin1
771
drop table t1;
772
create table t1 SELECT b from t2 UNION select tx from t2;
773
select * from t1;
774
b
775
tetetetetest
776
teeeeeeeeeeeest
777
show create table t1;
778
Table	Create Table
779
t1	CREATE TABLE `t1` (
780
  `b` blob
781
) ENGINE=MyISAM DEFAULT CHARSET=latin1
782
drop table t1,t2;
783
create table t1 select 1 union select -1;
784
select * from t1;
785
1
786
1
787
-1
788
show create table t1;
789
Table	Create Table
790
t1	CREATE TABLE `t1` (
791
  `1` bigint(20) NOT NULL DEFAULT '0'
792
) ENGINE=MyISAM DEFAULT CHARSET=latin1
793
drop table t1;
794
create table t1 select _latin1"test" union select _latin2"testt" ;
795
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'UNION'
796
create table t1 select _latin2"test" union select _latin2"testt" ;
797
show create table t1;
798
Table	Create Table
799
t1	CREATE TABLE `t1` (
800
  `test` varchar(5) CHARACTER SET latin2 NOT NULL DEFAULT ''
801
) ENGINE=MyISAM DEFAULT CHARSET=latin1
802
drop table t1;
803
create table t1 (s char(200));
804
insert into t1 values (repeat("1",200));
805
create table t2 select * from t1;
806
insert into t2 select * from t1;
807
insert into t1 select * from t2;
808
insert into t2 select * from t1;
809
insert into t1 select * from t2;
810
insert into t2 select * from t1;
811
set local tmp_table_size=1024;
812
select count(*) from (select * from t1 union all select * from t2 order by 1) b;
813
count(*)
814
21
815
select count(*) from t1;
816
count(*)
817
8
818
select count(*) from t2;
819
count(*)
820
13
821
drop table t1,t2;
822
set local tmp_table_size=default;
823
create table t1 (a int, index (a), b int);
824
insert t1 values (1,1),(2,2),(3,3),(4,4),(5,5);
825
insert t1 select a+1, a+b from t1;
826
insert t1 select a+1, a+b from t1;
827
insert t1 select a+1, a+b from t1;
828
insert t1 select a+1, a+b from t1;
829
insert t1 select a+1, a+b from t1;
830
FLUSH STATUS;
831
show status like 'Slow_queries';
832
Variable_name	Value
833
Slow_queries	0
834
select count(*) from t1 where a=7;
835
count(*)
836
26
837
show status like 'Slow_queries';
838
Variable_name	Value
839
Slow_queries	0
840
select count(*) from t1 where b=13;
841
count(*)
842
10
843
show status like 'Slow_queries';
844
Variable_name	Value
845
Slow_queries	1
846
select count(*) from t1 where b=13 union select count(*) from t1 where a=7;
847
count(*)
848
10
849
26
850
show status like 'Slow_queries';
851
Variable_name	Value
852
Slow_queries	2
853
select count(*) from t1 where a=7 union select count(*) from t1 where b=13;
854
count(*)
855
26
856
10
857
show status like 'Slow_queries';
858
Variable_name	Value
859
Slow_queries	3
860
flush status;
861
select a from t1 where b not in (1,2,3) union select a from t1 where b not in (4,5,6);
862
a
863
4
864
5
865
3
866
6
867
7
868
8
869
9
870
10
871
1
872
2
873
show status like 'Slow_queries';
874
Variable_name	Value
875
Slow_queries	1
876
drop table t1;
877
create table t1 (   RID int(11) not null default '0',   IID int(11) not null default '0',    nada varchar(50)  not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM;
878
insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');
879
select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
880
NAME	PHONE	NAME	PHONE
881
a	111	NULL	NULL
882
b	222	NULL	NULL
883
d	444	d	454
884
NULL	NULL	f	666
885
NULL	NULL	g	777
886
drop  table t1;
887
create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);
888
insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
889
select col1 n from t1 union select col2 n from t1 order by n;
890
n
891
1
892
2
893
3
894
4
895
5
896
6
897
7
898
8
899
9
900
10
901
alter table t1 add index myindex (col2);
902
select col1 n from t1 union select col2 n from t1 order by n;
903
n
904
1
905
2
906
3
907
4
908
5
909
6
910
7
911
8
912
9
913
10
914
drop  table t1;
915
create table t1 (i int);
916
insert into t1 values (1);
917
select * from t1 UNION select * from t1;
918
i
919
1
920
select * from t1 UNION ALL select * from t1;
921
i
922
1
923
1
924
select * from t1 UNION select * from t1 UNION ALL select * from t1;
925
i
926
1
927
1
928
drop table t1;
929
select 1 as a union all select 1 union all select 2 union select 1 union all select 2;
930
a
931
1
932
2
933
2
934
set sql_select_limit=1;
935
select 1 union select 2;
936
1
937
1
938
(select 1) union (select 2);
939
1
940
1
941
(select 1) union (select 2) union (select 3) limit 2;
942
1
943
1
944
2
945
set sql_select_limit=default;
946
create table t1 (a int);
947
insert into t1 values (100), (1);
948
create table t2 (a int);
949
insert into t2 values (100);
950
select a from t1 union select a from t2 order by a;
951
a
952
1
953
100
954
SET SQL_SELECT_LIMIT=1;
955
select a from t1 union select a from t2 order by a;
956
a
957
1
958
drop table t1, t2;
959
set sql_select_limit=default;
960
CREATE TABLE t1 (i int(11) default NULL,c char(1) default NULL,KEY i (i));
961
CREATE TABLE t2 (i int(11) default NULL,c char(1) default NULL,KEY i (i));
962
explain (select * from t1) union (select * from t2) order by not_existing_column;
963
ERROR 42S22: Unknown column 'not_existing_column' in 'order clause'
964
drop table t1, t2;
965
CREATE TABLE t1 (uid int(1));
966
INSERT INTO t1 SELECT 150;
967
SELECT 'a' UNION SELECT uid FROM t1;
968
a
969
a
970
150
971
drop table t1;
972
CREATE TABLE t1 ( ID1 int(10) unsigned NOT NULL DEFAULT '0' , ID2 datetime NOT NULL DEFAULT '0000-00-00 00:00:00' , DATA1 varchar(10) , DATA2 double(5,4) , DATA3 datetime , PRIMARY KEY (ID1,ID2));
973
CREATE TABLE t2 ( ID int(3) unsigned NOT NULL DEFAULT '0' , DATA1 timestamp DEFAULT '0000-00-00 00:00:00' , PRIMARY KEY (ID));
974
(SELECT * FROM t1 AS PARTITIONED, t2 AS
975
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
976
(SELECT * FROM t1 AS PARTITIONED, t2 AS
977
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
978
(SELECT * FROM t1 AS PARTITIONED, t2 AS
979
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
980
(SELECT * FROM t1 AS PARTITIONED, t2 AS
981
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
982
(SELECT * FROM t1 AS PARTITIONED, t2 AS
983
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
984
(SELECT * FROM t1 AS PARTITIONED, t2 AS
985
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
986
(SELECT * FROM t1 AS PARTITIONED, t2 AS
987
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
988
(SELECT * FROM t1 AS PARTITIONED, t2 AS
989
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
990
(SELECT * FROM t1 AS PARTITIONED, t2 AS
991
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
992
(SELECT * FROM t1 AS PARTITIONED, t2 AS
993
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
994
(SELECT * FROM t1 AS PARTITIONED, t2 AS
995
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
996
(SELECT * FROM t1 AS PARTITIONED, t2 AS
997
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1);
998
ID1	ID2	DATA1	DATA2	DATA3	ID	DATA1
999
drop table t1,t2;
1000
create table t1 (a ENUM('Yes', 'No') NOT NULL);
1001
create table t2 (a ENUM('aaa', 'bbb') NOT NULL);
1002
insert into t1 values ('No');
1003
insert into t2 values ('bbb');
1004
create table t3 (a SET('Yes', 'No') NOT NULL);
1005
create table t4 (a SET('aaa', 'bbb') NOT NULL);
1006
insert into t3 values (1);
1007
insert into t4 values (3);
1008
select "1" as a union select a from t1;
1009
a
1010
1
1011
No
1012
select a as a from t1 union select "1";
1013
a
1014
No
1015
1
1016
select a as a from t2 union select a from t1;
1017
a
1018
bbb
1019
No
1020
select "1" as a union select a from t3;
1021
a
1022
1
1023
Yes
1024
select a as a from t3 union select "1";
1025
a
1026
Yes
1027
1
1028
select a as a from t4 union select a from t3;
1029
a
1030
aaa,bbb
1031
Yes
1032
select a as a from t1 union select a from t4;
1033
a
1034
No
1035
aaa,bbb
1036
drop table t1,t2,t3,t4;
1037
create table t1 as
1038
(select _latin1'test') union
1039
(select _latin1'TEST') union
1040
(select _latin1'TeST');
1041
show create table t1;
1042
Table	Create Table
1043
t1	CREATE TABLE `t1` (
1044
  `test` varchar(4) NOT NULL DEFAULT ''
1045
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1046
select count(*) from t1;
1047
count(*)
1048
1
1049
drop table t1;
1050
create table t1 as
1051
(select _latin1'test' collate latin1_bin) union
1052
(select _latin1'TEST') union
1053
(select _latin1'TeST');
1054
show create table t1;
1055
Table	Create Table
1056
t1	CREATE TABLE `t1` (
1057
  `_latin1'test' collate latin1_bin` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ''
1058
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1059
select count(*) from t1;
1060
count(*)
1061
3
1062
drop table t1;
1063
create table t1 as
1064
(select _latin1'test') union
1065
(select _latin1'TEST' collate latin1_bin) union
1066
(select _latin1'TeST');
1067
show create table t1;
1068
Table	Create Table
1069
t1	CREATE TABLE `t1` (
1070
  `test` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ''
1071
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1072
select count(*) from t1;
1073
count(*)
1074
3
1075
drop table t1;
1076
create table t1 as
1077
(select _latin1'test') union
1078
(select _latin1'TEST') union
1079
(select _latin1'TeST' collate latin1_bin);
1080
show create table t1;
1081
Table	Create Table
1082
t1	CREATE TABLE `t1` (
1083
  `test` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ''
1084
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1085
select count(*) from t1;
1086
count(*)
1087
3
1088
drop table t1;
1089
create table t2 (
1090
a char character set latin1 collate latin1_swedish_ci,
1091
b char character set latin1 collate latin1_german1_ci);
1092
create table t1 as
1093
(select a from t2) union
1094
(select b from t2);
1095
ERROR HY000: Illegal mix of collations for operation 'UNION'
1096
create table t1 as
1097
(select a collate latin1_german1_ci from t2) union
1098
(select b from t2);
1099
show create table t1;
1100
Table	Create Table
1101
t1	CREATE TABLE `t1` (
1102
  `a collate latin1_german1_ci` varchar(1) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL
1103
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1104
drop table t1;
1105
create table t1 as
1106
(select a from t2) union
1107
(select b collate latin1_german1_ci from t2);
1108
show create table t1;
1109
Table	Create Table
1110
t1	CREATE TABLE `t1` (
1111
  `a` varchar(1) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL
1112
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1113
drop table t1;
1114
create table t1 as
1115
(select a from t2) union
1116
(select b from t2) union
1117
(select 'c' collate latin1_german1_ci from t2);
1118
show create table t1;
1119
Table	Create Table
1120
t1	CREATE TABLE `t1` (
1121
  `a` varchar(1) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL
1122
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1123
drop table t1;
1124
drop table t2;
1125
create table t1(a1 int, f1 char(10));
1126
create table t2
1127
select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
1128
union
1129
select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
1130
order by f2, a1;
1131
show columns from t2;
1132
Field	Type	Null	Key	Default	Extra
1133
f2	date	YES		NULL	
1134
a1	int(11)	YES		NULL	
1135
drop table t1, t2;
1136
create table t1 (f1 int);
1137
create table t2 (f1 int, f2 int ,f3 date);
1138
create table t3 (f1 int, f2 char(10));
1139
create table t4
1140
(
1141
select t2.f3 as sdate
1142
from t1
1143
left outer join t2 on (t1.f1 = t2.f1)
1144
inner join t3 on (t2.f2 = t3.f1)
1145
order by t1.f1, t3.f1, t2.f3
1146
)
1147
union
1148
(
1149
select cast('2004-12-31' as date) as sdate
1150
from t1
1151
left outer join t2 on (t1.f1 = t2.f1)
1152
inner join t3 on (t2.f2 = t3.f1)
1153
group by t1.f1
1154
order by t1.f1, t3.f1, t2.f3
1155
)
1156
order by sdate;
1157
show columns from t4;
1158
Field	Type	Null	Key	Default	Extra
1159
sdate	date	YES		NULL	
1160
drop table t1, t2, t3, t4;
1161
create table t1 (a int not null, b char (10) not null);
1162
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
1163
select * from ((select * from t1 limit 1)) a;
1164
a	b
1165
1	a
1166
select * from ((select * from t1 limit 1) union (select * from t1 limit 1)) a;
1167
a	b
1168
1	a
1169
select * from ((select * from t1 limit 1) union (select * from t1 limit 1) union (select * from t1 limit 1)) a;
1170
a	b
1171
1	a
1172
select * from ((((select * from t1))) union (select * from t1) union (select * from t1)) a;
1173
a	b
1174
1	a
1175
2	b
1176
3	c
1177
select * from ((select * from t1) union (((select * from t1))) union (select * from t1)) a;
1178
a	b
1179
1	a
1180
2	b
1181
3	c
1182
drop table t1;
1183
set @val:=6;
1184
select concat('value is: ', @val) union select 'some text';
1185
concat('value is: ', @val)
1186
value is: 6
1187
some text
1188
select concat(_latin1'a', _ascii'b' collate ascii_bin);
1189
concat(_latin1'a', _ascii'b' collate ascii_bin)
1190
ab
1191
create table t1 (foo varchar(100)) collate ascii_bin;
1192
insert into t1 (foo) values ("foo");
1193
select foo from t1 union select 'bar' as foo from dual;
1194
foo
1195
foo
1196
bar
1197
drop table t1;
1198
CREATE TABLE t1 (
1199
a ENUM('ä','ö','ü') character set utf8 not null default 'ü',
1200
b ENUM("one", "two") character set utf8,
1201
c ENUM("one", "two")
1202
);
1203
show create table t1;
1204
Table	Create Table
1205
t1	CREATE TABLE `t1` (
1206
  `a` enum('ä','ö','ü') CHARACTER SET utf8 NOT NULL DEFAULT 'ü',
1207
  `b` enum('one','two') CHARACTER SET utf8 DEFAULT NULL,
1208
  `c` enum('one','two') DEFAULT NULL
1209
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1210
insert into t1 values ('ä', 'one', 'one'), ('ö', 'two', 'one'), ('ü', NULL, NULL);
1211
create table t2 select NULL union select a from t1;
1212
show columns from t2;
1213
Field	Type	Null	Key	Default	Extra
1214
NULL	enum('ä','ö','ü')	YES		NULL	
1215
drop table t2;
1216
create table t2 select a from t1 union select NULL;
1217
show columns from t2;
1218
Field	Type	Null	Key	Default	Extra
1219
a	enum('ä','ö','ü')	YES		NULL	
1220
drop table t2;
1221
create table t2 select a from t1 union select a from t1;
1222
show columns from t2;
1223
Field	Type	Null	Key	Default	Extra
1224
a	varchar(1)	NO			
1225
drop table t2;
1226
create table t2 select a from t1 union select c from t1;
1227
drop table t2;
1228
create table t2 select a from t1 union select b from t1;
1229
show columns from t2;
1230
Field	Type	Null	Key	Default	Extra
1231
a	varchar(3)	YES		NULL	
1232
drop table t2, t1;
1233
create table t1 (f1 decimal(60,25), f2 decimal(60,25));
1234
insert into t1 values (0.0,0.0);
1235
select f1 from t1 union all select f2 from t1;
1236
f1
1237
0.0000000000000000000000000
1238
0.0000000000000000000000000
1239
select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1
1240
union all
1241
select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;
1242
description	f1
1243
XXXXXXXXXXXXXXXXXXXX	0.0000000000000000000000000
1244
YYYYYYYYYYYYYYYYYYYY	0.0000000000000000000000000
1245
drop table t1;
1246
create table t1 (f1 decimal(60,24), f2 decimal(60,24));
1247
insert into t1 values (0.0,0.0);
1248
select f1 from t1 union all select f2 from t1;
1249
f1
1250
0.000000000000000000000000
1251
0.000000000000000000000000
1252
select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1
1253
union all
1254
select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;
1255
description	f1
1256
XXXXXXXXXXXXXXXXXXXX	0.000000000000000000000000
1257
YYYYYYYYYYYYYYYYYYYY	0.000000000000000000000000
1258
drop table t1;
1259
create table t1 (a varchar(5));
1260
create table t2 select * from t1 union select 'abcdefghijkl';
1261
show create table t2;
1262
Table	Create Table
1263
t2	CREATE TABLE `t2` (
1264
  `a` varchar(12) DEFAULT NULL
1265
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1266
select row_format from information_schema.TABLES where table_schema="test" and table_name="t2";
1267
row_format
1268
Dynamic
1269
alter table t2 ROW_FORMAT=fixed;
1270
show create table t2;
1271
Table	Create Table
1272
t2	CREATE TABLE `t2` (
1273
  `a` varchar(12) DEFAULT NULL
1274
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
1275
drop table t1,t2;
1276
CREATE TABLE t1 (a mediumtext);
1277
CREATE TABLE t2 (b varchar(20));
1278
INSERT INTO t1 VALUES ('a'),('b');
1279
SELECT left(a,100000000) FROM t1 UNION  SELECT b FROM t2;
1280
left(a,100000000)
1281
a
1282
b
1283
create table t3 SELECT left(a,100000000) FROM t1 UNION  SELECT b FROM t2;
1284
show create table t3;
1285
Table	Create Table
1286
t3	CREATE TABLE `t3` (
1287
  `left(a,100000000)` mediumtext
1288
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1289
drop tables t1,t2,t3;
1290
CREATE TABLE t1 (a longtext);
1291
CREATE TABLE t2 (b varchar(20));
1292
INSERT INTO t1 VALUES ('a'),('b');
1293
SELECT left(a,100000000) FROM t1 UNION  SELECT b FROM t2;
1294
left(a,100000000)
1295
a
1296
b
1297
create table t3 SELECT left(a,100000000) FROM t1 UNION  SELECT b FROM t2;
1298
show create table t3;
1299
Table	Create Table
1300
t3	CREATE TABLE `t3` (
1301
  `left(a,100000000)` longtext
1302
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1303
drop tables t1,t2,t3;
1304
SELECT @tmp_max:= @@max_allowed_packet;
1305
@tmp_max:= @@max_allowed_packet
1306
1048576
1307
SET max_allowed_packet=25000000;
1308
CREATE TABLE t1 (a mediumtext);
1309
CREATE TABLE t2 (b varchar(20));
1310
INSERT INTO t1 VALUES ('a');
1311
CREATE TABLE t3 SELECT REPEAT(a,20000000) AS a FROM t1 UNION SELECT b FROM t2;
1312
SHOW CREATE TABLE t3;
1313
Table	Create Table
1314
t3	CREATE TABLE `t3` (
1315
  `a` longtext
1316
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1317
DROP TABLES t1,t3;
1318
CREATE TABLE t1 (a tinytext);
1319
INSERT INTO t1 VALUES ('a');
1320
CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;
1321
SHOW CREATE TABLE t3;
1322
Table	Create Table
1323
t3	CREATE TABLE `t3` (
1324
  `a` varchar(510) DEFAULT NULL
1325
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1326
DROP TABLES t1,t3;
1327
CREATE TABLE t1 (a mediumtext);
1328
INSERT INTO t1 VALUES ('a');
1329
CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;
1330
SHOW CREATE TABLE t3;
1331
Table	Create Table
1332
t3	CREATE TABLE `t3` (
1333
  `a` longtext
1334
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1335
DROP TABLES t1,t3;
1336
CREATE TABLE t1 (a tinyblob);
1337
INSERT INTO t1 VALUES ('a');
1338
CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;
1339
SHOW CREATE TABLE t3;
1340
Table	Create Table
1341
t3	CREATE TABLE `t3` (
1342
  `a` varbinary(510) DEFAULT NULL
1343
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1344
DROP TABLES t1,t2,t3;
1345
SET max_allowed_packet:= @tmp_max;
1346
create table t1 ( id int not null auto_increment, primary key (id), col1 int);
1347
insert into t1 (col1) values (2),(3),(4),(5),(6);
1348
select 99 union all select id from t1 order by 1;
1349
99
1350
1
1351
2
1352
3
1353
4
1354
5
1355
99
1356
select id from t1 union all select 99 order by 1;
1357
id
1358
1
1359
2
1360
3
1361
4
1362
5
1363
99
1364
drop table t1;
1365
create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp, f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text);
1366
create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1;
1367
show create table t2;
1368
Table	Create Table
1369
t2	CREATE TABLE `t2` (
1370
  `f1` char(1) DEFAULT NULL,
1371
  `f2` char(5) DEFAULT NULL,
1372
  `f3` binary(1) DEFAULT NULL,
1373
  `f4` binary(5) DEFAULT NULL,
1374
  `f5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
1375
  `f6` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
1376
  `f7` text,
1377
  `f8` mediumtext CHARACTER SET utf8
1378
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1379
drop table t1, t2;
1380
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1381
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1382
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1383
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1384
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1385
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1386
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1387
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1388
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1389
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1390
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1391
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1392
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1393
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1394
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1395
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1396
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1397
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1398
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1399
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1400
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1401
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1402
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1403
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1404
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1405
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1406
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1407
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1408
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1409
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1410
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1411
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1412
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1413
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1414
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1415
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1416
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1417
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1418
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1419
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1420
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1421
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1422
(select avg(1)) union (select avg(1)) union (select avg(1));
1423
avg(1)
1424
1.0000
1425
select _utf8'12' union select _latin1'12345';
1426
12
1427
12
1428
12345
1429
CREATE TABLE t1 (a int);
1430
INSERT INTO t1 VALUES (3),(1),(2),(4),(1);
1431
SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a) AS test;
1432
a
1433
1
1434
2
1435
3
1436
4
1437
SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test;
1438
ERROR 42S22: Unknown column 'c' in 'order clause'
1439
DROP TABLE t1;
1440
(select 1 into @var) union (select 1);
1441
ERROR HY000: Incorrect usage of UNION and INTO
1442
(select 1) union (select 1 into @var);
1443
select @var;
1444
@var
1445
1
1446
(select 2) union (select 1 into @var);
1447
ERROR 42000: Result consisted of more than one row
1448
CREATE TABLE t1 (a int);
1449
INSERT INTO t1 VALUES (10), (20);
1450
CREATE TABLE t2 (b int);
1451
INSERT INTO t2 VALUES (10), (50), (50);
1452
SELECT a,1 FROM t1 
1453
UNION
1454
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1455
ORDER BY a;
1456
a	1
1457
NULL	3
1458
10	1
1459
20	1
1460
50	2
1461
SELECT a,1 FROM t1 
1462
UNION
1463
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1464
ORDER BY a DESC;
1465
a	1
1466
50	2
1467
20	1
1468
10	1
1469
NULL	3
1470
SELECT a,1 FROM t1 
1471
UNION
1472
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1473
ORDER BY a ASC LIMIT 3;
1474
a	1
1475
NULL	3
1476
10	1
1477
20	1
1478
SELECT a,1 FROM t1 
1479
UNION ALL 
1480
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1481
ORDER BY a DESC;
1482
a	1
1483
50	2
1484
20	1
1485
10	1
1486
10	1
1487
NULL	3
1488
SELECT a,1 FROM t1
1489
UNION 
1490
(SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a);
1491
ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
1492
SELECT a,1 FROM t1
1493
UNION ALL
1494
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a
1495
UNION
1496
SELECT 1,1;
1497
ERROR HY000: Incorrect usage of UNION and ORDER BY
1498
DROP TABLE t1,t2;
1499
CREATE TABLE t1 (a INT);
1500
INSERT INTO t1 VALUES (1), (2), (3);
1501
CREATE TABLE t2 SELECT * FROM (SELECT NULL) a UNION SELECT a FROM t1;
1502
DESC t2;
1503
Field	Type	Null	Key	Default	Extra
1504
NULL	int(11)	YES		NULL	
1505
CREATE TABLE t3 SELECT a FROM t1 UNION SELECT * FROM (SELECT NULL) a;
1506
DESC t3;
1507
Field	Type	Null	Key	Default	Extra
1508
a	int(11)	YES		NULL	
1509
CREATE TABLE t4 SELECT NULL;
1510
DESC t4;
1511
Field	Type	Null	Key	Default	Extra
1512
NULL	binary(0)	YES		NULL	
1513
CREATE TABLE t5 SELECT NULL UNION SELECT NULL;
1514
DESC t5;
1515
Field	Type	Null	Key	Default	Extra
1516
NULL	binary(0)	YES		NULL	
1517
CREATE TABLE t6 
1518
SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1;
1519
DESC t6;
1520
Field	Type	Null	Key	Default	Extra
1521
NULL	int(11)	YES		NULL	
1522
DROP TABLE t1, t2, t3, t4, t5, t6;
1523
End of 5.0 tests