« back to all changes in this revision
Viewing changes to tests/r/pbxt/union.result
- browse files at revision 1457.1.1
- compare with another revision
- compare with revision 2429
- download diff
- download tarball
- view history from revision 1457.1.1
- Committer: Brian Aker
- Date: 2010-04-08 17:52:00 UTC
- mfrom: (1455.3.1 drizzle)
- mto: (1455.3.3 drizzle)
- mto: This revision was merged to the branch mainline in revision 1462.
- Revision ID: brian@gaz-20100408175200-jje1xrbvn5vu38cj
Merge PBXT.
- files added:
- plugin/pbxt
- plugin/pbxt/bin
- plugin/pbxt/src
- tests/r/pbxt
- files modified:
- drizzled/sql_table.cc
added
removed
tests/r/pbxt/union.result
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 # 100.00
88
2 UNION t2 ALL NULL NULL NULL NULL # 100.00 Using filesort
89
NULL UNION RESULT <union1,2> ALL 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 #
109
2 UNION t2 ALL NULL NULL NULL NULL #
110
NULL UNION RESULT <union1,2> ALL 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_st 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_st 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` int NOT NULL default '1',
160
PRIMARY KEY (`pseudo1`),
161
KEY `pseudo` (`pseudo`)
162
);
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 int NOT NULL default '0',
224
cv varchar(190) 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 int 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 int NOT NULL auto_increment,
237
gn varchar(255) NOT NULL default '',
238
must int 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 NOT NULL default '0',
245
gid bigint default NULL,
246
rid bigint default NULL,
247
cid bigint 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 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 NOT NULL auto_increment,
263
un varchar(190) NOT NULL default '',
264
uc int 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 Drizzle 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 TEMPORARY TABLE t1 ( id int default '0') ENGINE=MyISAM;
448
INSERT INTO t1 (id) VALUES("1");
449
CREATE TEMPORARY TABLE t2 ( id int default '0', id_master int 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 t1(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references t2(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 t3, t1, t2;
524
create table t1 (mat_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test INT NULL);
525
create table t2 (mat_id INT NOT NULL, pla_id INT 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
show create table t1;
543
Table Create Table
544
t1 CREATE TABLE `t1` (
545
`a` varchar(1) NOT NULL DEFAULT ''
546
) ENGINE=DEFAULT
547
drop table t1;
548
create table t1 SELECT 12 as a UNION select "aa" as a;
549
select * from t1;
550
a
551
12
552
aa
553
show create table t1;
554
Table Create Table
555
t1 CREATE TABLE `t1` (
556
`a` varbinary(4) NOT NULL DEFAULT ''
557
) ENGINE=DEFAULT
558
drop table t1;
559
create table t1 SELECT 12 as a UNION select 12.2 as a;
560
select * from t1;
561
a
562
12.0
563
12.2
564
show create table t1;
565
Table Create Table
566
t1 CREATE TABLE `t1` (
567
`a` decimal(3,1) NOT NULL DEFAULT '0.0'
568
) ENGINE=DEFAULT
569
drop table t1;
570
create table t2 (it1 int, it2 int not null, i int not null, ib int, f float, d double, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text);
571
insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, '1972-10-22', '1972-10-22 11:50:00', 'testc', 'testv', 'tetetetetest', 'teeeeeeeeeeeest');
572
create table t1 SELECT it2 from t2 UNION select it1 from t2;
573
select * from t1;
574
it2
575
1
576
NULL
577
show create table t1;
578
Table Create Table
579
t1 CREATE TABLE `t1` (
580
`it2` int DEFAULT NULL
581
) ENGINE=DEFAULT
582
drop table t1;
583
create table t1 SELECT it2 from t2 UNION select i from t2;
584
select * from t1;
585
it2
586
1
587
3
588
show create table t1;
589
Table Create Table
590
t1 CREATE TABLE `t1` (
591
`it2` int NOT NULL DEFAULT '0'
592
) ENGINE=DEFAULT
593
drop table t1;
594
create table t1 SELECT i from t2 UNION select f from t2;
595
select * from t1;
596
i
597
3
598
1.5
599
show create table t1;
600
Table Create Table
601
t1 CREATE TABLE `t1` (
602
`i` double DEFAULT NULL
603
) ENGINE=DEFAULT
604
drop table t1;
605
create table t1 SELECT f from t2 UNION select d from t2;
606
select * from t1;
607
f
608
1.5
609
2.5
610
show create table t1;
611
Table Create Table
612
t1 CREATE TABLE `t1` (
613
`f` double DEFAULT NULL
614
) ENGINE=DEFAULT
615
drop table t1;
616
create table t1 SELECT ib from t2 UNION select f from t2;
617
select * from t1;
618
ib
619
4
620
1.5
621
show create table t1;
622
Table Create Table
623
t1 CREATE TABLE `t1` (
624
`ib` double DEFAULT NULL
625
) ENGINE=DEFAULT
626
drop table t1;
627
create table t1 SELECT ib from t2 UNION select d from t2;
628
select * from t1;
629
ib
630
4
631
2.5
632
show create table t1;
633
Table Create Table
634
t1 CREATE TABLE `t1` (
635
`ib` double DEFAULT NULL
636
) ENGINE=DEFAULT
637
drop table t1;
638
create table t1 SELECT f from t2 UNION select da from t2;
639
select * from t1;
640
f
641
1.5
642
1972-10-22
643
show create table t1;
644
Table Create Table
645
t1 CREATE TABLE `t1` (
646
`f` varbinary(22) DEFAULT NULL
647
) ENGINE=DEFAULT
648
drop table t1;
649
create table t1 SELECT da from t2 UNION select dt from t2;
650
select * from t1;
651
da
652
1972-10-22 00:00:00
653
1972-10-22 11:50:00
654
show create table t1;
655
Table Create Table
656
t1 CREATE TABLE `t1` (
657
`da` datetime DEFAULT NULL
658
) ENGINE=DEFAULT
659
drop table t1;
660
create table t1 SELECT dt from t2 UNION select trim(sc) from t2;
661
select trim(dt) from t1;
662
trim(dt)
663
1972-10-22 11:50:00
664
testc
665
show create table t1;
666
Table Create Table
667
t1 CREATE TABLE `t1` (
668
`dt` varbinary(19) DEFAULT NULL
669
) ENGINE=DEFAULT
670
drop table t1;
671
create table t1 SELECT dt from t2 UNION select sv from t2;
672
select * from t1;
673
dt
674
1972-10-22 11:50:00
675
testv
676
show create table t1;
677
Table Create Table
678
t1 CREATE TABLE `t1` (
679
`dt` varbinary(40) DEFAULT NULL
680
) ENGINE=DEFAULT
681
drop table t1;
682
create table t1 SELECT sc from t2 UNION select sv from t2;
683
select * from t1;
684
sc
685
testc
686
testv
687
show create table t1;
688
Table Create Table
689
t1 CREATE TABLE `t1` (
690
`sc` varchar(10) DEFAULT NULL
691
) ENGINE=DEFAULT
692
drop table t1;
693
create table t1 SELECT dt from t2 UNION select b from t2;
694
select * from t1;
695
dt
696
1972-10-22 11:50:00
697
tetetetetest
698
show create table t1;
699
Table Create Table
700
t1 CREATE TABLE `t1` (
701
`dt` blob
702
) ENGINE=DEFAULT
703
drop table t1;
704
create table t1 SELECT sv from t2 UNION select b from t2;
705
select * from t1;
706
sv
707
testv
708
tetetetetest
709
show create table t1;
710
Table Create Table
711
t1 CREATE TABLE `t1` (
712
`sv` blob
713
) ENGINE=DEFAULT
714
drop table t1;
715
create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
716
select * from t1;
717
i
718
3
719
2.5
720
tetetetetest
721
show create table t1;
722
Table Create Table
723
t1 CREATE TABLE `t1` (
724
`i` blob
725
) ENGINE=DEFAULT
726
drop table t1;
727
create table t1 SELECT sv from t2 UNION select tx from t2;
728
select * from t1;
729
sv
730
testv
731
teeeeeeeeeeeest
732
show create table t1;
733
Table Create Table
734
t1 CREATE TABLE `t1` (
735
`sv` text
736
) ENGINE=DEFAULT
737
drop table t1;
738
create table t1 SELECT b from t2 UNION select tx from t2;
739
select * from t1;
740
b
741
tetetetetest
742
teeeeeeeeeeeest
743
show create table t1;
744
Table Create Table
745
t1 CREATE TABLE `t1` (
746
`b` blob
747
) ENGINE=DEFAULT
748
drop table t1,t2;
749
create table t1 select 1 union select -1;
750
select * from t1;
751
1
752
1
753
-1
754
show create table t1;
755
Table Create Table
756
t1 CREATE TABLE `t1` (
757
`1` bigint NOT NULL DEFAULT '0'
758
) ENGINE=DEFAULT
759
drop table t1;
760
create table t1 select _latin1"test" union select _latin1"testt" ;
761
ERROR 42S22: Unknown column '_latin1' in 'field list'
762
create table t1 select _utf8"test" union select _utf8"testt" ;
763
ERROR 42S22: Unknown column '_utf8' in 'field list'
764
create table t1 select "test" union select "testt" ;
765
show create table t1;
766
Table Create Table
767
t1 CREATE TABLE `t1` (
768
`test` varchar(4) NOT NULL DEFAULT ''
769
) ENGINE=DEFAULT
770
drop table t1;
771
create table t1 (s char(200));
772
insert into t1 values (repeat("1",200));
773
create table t2 select * from t1;
774
insert into t2 select * from t1;
775
insert into t1 select * from t2;
776
insert into t2 select * from t1;
777
insert into t1 select * from t2;
778
insert into t2 select * from t1;
779
set local tmp_table_size=1024;
780
select count(*) from (select * from t1 union all select * from t2 order by 1) b;
781
count(*)
782
21
783
select count(*) from t1;
784
count(*)
785
8
786
select count(*) from t2;
787
count(*)
788
13
789
drop table t1,t2;
790
set local tmp_table_size=default;
791
create table t1 (a int, index (a), b int);
792
insert t1 values (1,1),(2,2),(3,3),(4,4),(5,5);
793
insert t1 select a+1, a+b from t1;
794
insert t1 select a+1, a+b from t1;
795
insert t1 select a+1, a+b from t1;
796
insert t1 select a+1, a+b from t1;
797
insert t1 select a+1, a+b from t1;
798
FLUSH STATUS;
799
show status like 'Slow_queries';
800
Variable_name Value
801
Slow_queries 0
802
select count(*) from t1 where a=7;
803
count(*)
804
26
805
show status like 'Slow_queries';
806
Variable_name Value
807
Slow_queries 0
808
select count(*) from t1 where b=13;
809
count(*)
810
10
811
show status like 'Slow_queries';
812
Variable_name Value
813
Slow_queries 0
814
select count(*) from t1 where b=13 union select count(*) from t1 where a=7;
815
count(*)
816
10
817
26
818
show status like 'Slow_queries';
819
Variable_name Value
820
Slow_queries 0
821
select count(*) from t1 where a=7 union select count(*) from t1 where b=13;
822
count(*)
823
26
824
10
825
show status like 'Slow_queries';
826
Variable_name Value
827
Slow_queries 0
828
flush status;
829
select a from t1 where b not in (1,2,3) union select a from t1 where b not in (4,5,6);
830
a
831
4
832
5
833
3
834
6
835
7
836
8
837
9
838
10
839
1
840
2
841
show status like 'Slow_queries';
842
Variable_name Value
843
Slow_queries 0
844
drop table t1;
845
create table t1 ( RID int not null default '0', IID int not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null);
846
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');
847
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);
848
NAME PHONE NAME PHONE
849
a 111 NULL NULL
850
b 222 NULL NULL
851
d 444 d 454
852
NULL NULL f 666
853
NULL NULL g 777
854
drop table t1;
855
create table t1 (col1 int, col2 int);
856
insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
857
select col1 n from t1 union select col2 n from t1 order by n;
858
n
859
1
860
2
861
3
862
4
863
5
864
6
865
7
866
8
867
9
868
10
869
alter table t1 add index myindex (col2);
870
select col1 n from t1 union select col2 n from t1 order by n;
871
n
872
1
873
2
874
3
875
4
876
5
877
6
878
7
879
8
880
9
881
10
882
drop table t1;
883
create table t1 (i int);
884
insert into t1 values (1);
885
select * from t1 UNION select * from t1;
886
i
887
1
888
select * from t1 UNION ALL select * from t1;
889
i
890
1
891
1
892
select * from t1 UNION select * from t1 UNION ALL select * from t1;
893
i
894
1
895
1
896
drop table t1;
897
select 1 as a union all select 1 union all select 2 union select 1 union all select 2;
898
a
899
1
900
2
901
2
902
set sql_select_limit=1;
903
select 1 union select 2;
904
1
905
1
906
(select 1) union (select 2);
907
1
908
1
909
(select 1) union (select 2) union (select 3) limit 2;
910
1
911
1
912
2
913
set sql_select_limit=default;
914
create table t1 (a int);
915
insert into t1 values (100), (1);
916
create table t2 (a int);
917
insert into t2 values (100);
918
select a from t1 union select a from t2 order by a;
919
a
920
1
921
100
922
SET SQL_SELECT_LIMIT=1;
923
select a from t1 union select a from t2 order by a;
924
a
925
1
926
drop table t1, t2;
927
set sql_select_limit=default;
928
CREATE TABLE t1 (i int default NULL,c char(1) default NULL,KEY i (i));
929
CREATE TABLE t2 (i int default NULL,c char(1) default NULL,KEY i (i));
930
explain (select * from t1) union (select * from t2) order by not_existing_column;
931
ERROR 42S22: Unknown column 'not_existing_column' in 'order clause'
932
drop table t1, t2;
933
CREATE TABLE t1 (uid int);
934
INSERT INTO t1 SELECT 150;
935
SELECT 'a' UNION SELECT uid FROM t1;
936
a
937
a
938
150
939
drop table t1;
940
CREATE TABLE t1 ( ID1 int NOT NULL DEFAULT '0' , ID2 datetime, DATA1 varchar(10) , DATA2 double(5,4) , DATA3 datetime , PRIMARY KEY (ID1,ID2));
941
CREATE TABLE t2 ( ID int NOT NULL DEFAULT '0' , DATA1 timestamp NULL, PRIMARY KEY (ID));
942
(SELECT * FROM t1 AS PARTITIONED, t2 AS
943
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
944
(SELECT * FROM t1 AS PARTITIONED, t2 AS
945
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
946
(SELECT * FROM t1 AS PARTITIONED, t2 AS
947
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
948
(SELECT * FROM t1 AS PARTITIONED, t2 AS
949
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
950
(SELECT * FROM t1 AS PARTITIONED, t2 AS
951
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
952
(SELECT * FROM t1 AS PARTITIONED, t2 AS
953
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
954
(SELECT * FROM t1 AS PARTITIONED, t2 AS
955
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
956
(SELECT * FROM t1 AS PARTITIONED, t2 AS
957
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
958
(SELECT * FROM t1 AS PARTITIONED, t2 AS
959
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
960
(SELECT * FROM t1 AS PARTITIONED, t2 AS
961
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
962
(SELECT * FROM t1 AS PARTITIONED, t2 AS
963
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
964
(SELECT * FROM t1 AS PARTITIONED, t2 AS
965
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1);
966
ID1 ID2 DATA1 DATA2 DATA3 ID DATA1
967
drop table t1,t2;
968
create table t1 (a ENUM('Yes', 'No') NOT NULL);
969
create table t2 (a ENUM('aaa', 'bbb') NOT NULL);
970
insert into t1 values ('No');
971
insert into t2 values ('bbb');
972
create table t3 (a ENUM('Yes', 'No') NOT NULL);
973
create table t4 (a ENUM('aaa', 'bbb') NOT NULL);
974
insert into t3 values (1);
975
insert into t4 values (3);
976
ERROR HY000: Received an invalid enum value '3'.
977
select "1" as a union select a from t1;
978
a
979
1
980
No
981
select a as a from t1 union select "1";
982
a
983
No
984
1
985
select a as a from t2 union select a from t1;
986
a
987
bbb
988
No
989
select "1" as a union select a from t3;
990
a
991
1
992
Yes
993
select a as a from t3 union select "1";
994
a
995
Yes
996
1
997
select a as a from t4 union select a from t3;
998
a
999
Yes
1000
select a as a from t1 union select a from t4;
1001
a
1002
No
1003
drop table t1,t2,t3,t4;
1004
create table t1 as
1005
(select 'test') union
1006
(select 'TEST') union
1007
(select 'TeST');
1008
show create table t1;
1009
Table Create Table
1010
t1 CREATE TABLE `t1` (
1011
`test` varchar(4) NOT NULL DEFAULT ''
1012
) ENGINE=DEFAULT
1013
select count(*) from t1;
1014
count(*)
1015
1
1016
drop table t1;
1017
create table t1 as
1018
(select 'test' collate utf8_bin) union
1019
(select 'TEST') union
1020
(select 'TeST');
1021
show create table t1;
1022
Table Create Table
1023
t1 CREATE TABLE `t1` (
1024
`'test' collate utf8_bin` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
1025
) ENGINE=DEFAULT
1026
select count(*) from t1;
1027
count(*)
1028
3
1029
drop table t1;
1030
create table t1 as
1031
(select 'test') union
1032
(select 'TEST' collate utf8_bin) union
1033
(select 'TeST');
1034
show create table t1;
1035
Table Create Table
1036
t1 CREATE TABLE `t1` (
1037
`test` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
1038
) ENGINE=DEFAULT
1039
select count(*) from t1;
1040
count(*)
1041
3
1042
drop table t1;
1043
create table t1 as
1044
(select 'test') union
1045
(select 'TEST') union
1046
(select 'TeST' collate utf8_bin);
1047
show create table t1;
1048
Table Create Table
1049
t1 CREATE TABLE `t1` (
1050
`test` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
1051
) ENGINE=DEFAULT
1052
select count(*) from t1;
1053
count(*)
1054
3
1055
drop table t1;
1056
create table t2 (
1057
a char character set utf8 collate utf8_swedish_ci,
1058
b char character set utf8 collate utf8_spanish_ci);
1059
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'character set utf8 collate utf8_swedish_ci,
1060
b char character set utf8 collate ut' at line 2
1061
create table t2 (
1062
a char collate utf8_swedish_ci,
1063
b char collate utf8_spanish_ci);
1064
create table t1 as
1065
(select a from t2) union
1066
(select b from t2);
1067
ERROR HY000: Illegal mix of collations for operation 'UNION'
1068
create table t1 as
1069
(select a collate utf8_swedish_ci from t2) union
1070
(select b from t2);
1071
ERROR 42000: Incorrect column name 'a collate utf8_swedish_ci '
1072
create table t1 as
1073
(select a from t2) union
1074
(select b collate utf8_swedish_ci from t2);
1075
show create table t1;
1076
Table Create Table
1077
t1 CREATE TABLE `t1` (
1078
`a` varchar(1) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL
1079
) ENGINE=DEFAULT
1080
drop table t1;
1081
create table t1 as
1082
(select a from t2) union
1083
(select b from t2) union
1084
(select 'c' collate utf8_spanish_ci from t2);
1085
show create table t1;
1086
Table Create Table
1087
t1 CREATE TABLE `t1` (
1088
`a` varchar(1) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL
1089
) ENGINE=DEFAULT
1090
drop table t1;
1091
drop table t2;
1092
create table t1(a1 int, f1 char(10));
1093
create table t2
1094
select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
1095
union
1096
select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
1097
order by f2, a1;
1098
show columns from t2;
1099
Field Type Null Key Default Extra
1100
f2 date YES NULL
1101
a1 int YES NULL
1102
drop table t1, t2;
1103
create table t1 (f1 int);
1104
create table t2 (f1 int, f2 int ,f3 date);
1105
create table t3 (f1 int, f2 char(10));
1106
create table t4
1107
(
1108
select t2.f3 as sdate
1109
from t1
1110
left outer join t2 on (t1.f1 = t2.f1)
1111
inner join t3 on (t2.f2 = t3.f1)
1112
order by t1.f1, t3.f1, t2.f3
1113
)
1114
union
1115
(
1116
select cast('2004-12-31' as date) as sdate
1117
from t1
1118
left outer join t2 on (t1.f1 = t2.f1)
1119
inner join t3 on (t2.f2 = t3.f1)
1120
group by t1.f1
1121
order by t1.f1, t3.f1, t2.f3
1122
)
1123
order by sdate;
1124
show columns from t4;
1125
Field Type Null Key Default Extra
1126
sdate date YES NULL
1127
drop table t1, t2, t3, t4;
1128
create table t1 (a int not null, b char (10) not null);
1129
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
1130
select * from ((select * from t1 limit 1)) a;
1131
a b
1132
1 a
1133
select * from ((select * from t1 limit 1) union (select * from t1 limit 1)) a;
1134
a b
1135
1 a
1136
select * from ((select * from t1 limit 1) union (select * from t1 limit 1) union (select * from t1 limit 1)) a;
1137
a b
1138
1 a
1139
select * from ((((select * from t1))) union (select * from t1) union (select * from t1)) a;
1140
a b
1141
1 a
1142
2 b
1143
3 c
1144
select * from ((select * from t1) union (((select * from t1))) union (select * from t1)) a;
1145
a b
1146
1 a
1147
2 b
1148
3 c
1149
drop table t1;
1150
set @val:=6;
1151
select concat('value is: ', @val) union select 'some text';
1152
concat('value is: ', @val)
1153
value is: 6
1154
some text
1155
select concat('a', 'b' collate utf8_bin);
1156
concat('a', 'b' collate utf8_bin)
1157
ab
1158
create table t1 (foo varchar(100)) collate utf8_bin;
1159
insert into t1 (foo) values ("foo");
1160
select foo from t1 union select 'bar' as foo from dual;
1161
ERROR 42S02: Table 'test.dual' doesn't exist
1162
select foo from t1 union select 'bar' as foo;
1163
foo
1164
foo
1165
bar
1166
drop table t1;
1167
create table t1 (f1 decimal(60,25), f2 decimal(60,25));
1168
insert into t1 values (0.0,0.0);
1169
select f1 from t1 union all select f2 from t1;
1170
f1
1171
0.0000000000000000000000000
1172
0.0000000000000000000000000
1173
select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1
1174
union all
1175
select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;
1176
description f1
1177
XXXXXXXXXXXXXXXXXXXX 0.0000000000000000000000000
1178
YYYYYYYYYYYYYYYYYYYY 0.0000000000000000000000000
1179
drop table t1;
1180
create table t1 (f1 decimal(60,24), f2 decimal(60,24));
1181
insert into t1 values (0.0,0.0);
1182
select f1 from t1 union all select f2 from t1;
1183
f1
1184
0.000000000000000000000000
1185
0.000000000000000000000000
1186
select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1
1187
union all
1188
select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;
1189
description f1
1190
XXXXXXXXXXXXXXXXXXXX 0.000000000000000000000000
1191
YYYYYYYYYYYYYYYYYYYY 0.000000000000000000000000
1192
drop table t1;
1193
create table t1 (a varchar(5));
1194
create table t2 select * from t1 union select 'abcdefghijkl';
1195
show create table t2;
1196
Table Create Table
1197
t2 CREATE TABLE `t2` (
1198
`a` varchar(5) DEFAULT NULL
1199
) ENGINE=DEFAULT
1200
select row_format from information_schema.TABLES where table_schema="test" and table_name="t2";
1201
row_format
1202
Dynamic
1203
alter table t2 ROW_FORMAT=fixed;
1204
show create table t2;
1205
Table Create Table
1206
t2 CREATE TABLE `t2` (
1207
`a` varchar(5) DEFAULT NULL
1208
) ENGINE=DEFAULT ROW_FORMAT=FIXED
1209
drop table t1,t2;
1210
CREATE TABLE t1 (a mediumtext);
1211
CREATE TABLE t2 (b varchar(20));
1212
INSERT INTO t1 VALUES ('a'),('b');
1213
SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;
1214
left(a,100000000)
1215
a
1216
b
1217
create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;
1218
show create table t3;
1219
Table Create Table
1220
t3 CREATE TABLE `t3` (
1221
`left(a,100000000)` text
1222
) ENGINE=DEFAULT
1223
drop tables t1,t2,t3;
1224
CREATE TABLE t1 (a longtext);
1225
CREATE TABLE t2 (b varchar(20));
1226
INSERT INTO t1 VALUES ('a'),('b');
1227
SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;
1228
left(a,100000000)
1229
a
1230
b
1231
create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;
1232
show create table t3;
1233
Table Create Table
1234
t3 CREATE TABLE `t3` (
1235
`left(a,100000000)` text
1236
) ENGINE=DEFAULT
1237
drop tables t1,t2,t3;
1238
SELECT @tmp_max:= @@max_allowed_packet;
1239
@tmp_max:= @@max_allowed_packet
1240
1048576
1241
SET max_allowed_packet=25000000;
1242
CREATE TABLE t1 (a mediumtext);
1243
CREATE TABLE t2 (b varchar(20));
1244
INSERT INTO t1 VALUES ('a');
1245
CREATE TABLE t3 SELECT REPEAT(a,20000000) AS a FROM t1 UNION SELECT b FROM t2;
1246
SHOW CREATE TABLE t3;
1247
Table Create Table
1248
t3 CREATE TABLE `t3` (
1249
`a` text
1250
) ENGINE=DEFAULT
1251
DROP TABLES t1,t3;
1252
CREATE TABLE t1 (a tinytext);
1253
INSERT INTO t1 VALUES ('a');
1254
CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;
1255
SHOW CREATE TABLE t3;
1256
Table Create Table
1257
t3 CREATE TABLE `t3` (
1258
`a` text
1259
) ENGINE=DEFAULT
1260
DROP TABLES t1,t3;
1261
CREATE TABLE t1 (a mediumtext);
1262
INSERT INTO t1 VALUES ('a');
1263
CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;
1264
SHOW CREATE TABLE t3;
1265
Table Create Table
1266
t3 CREATE TABLE `t3` (
1267
`a` text
1268
) ENGINE=DEFAULT
1269
DROP TABLES t1,t3;
1270
CREATE TABLE t1 (a tinyblob);
1271
INSERT INTO t1 VALUES ('a');
1272
CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;
1273
SHOW CREATE TABLE t3;
1274
Table Create Table
1275
t3 CREATE TABLE `t3` (
1276
`a` blob
1277
) ENGINE=DEFAULT
1278
DROP TABLES t1,t2,t3;
1279
SET max_allowed_packet:= @tmp_max;
1280
create table t1 ( id int not null auto_increment, primary key (id), col1 int);
1281
insert into t1 (col1) values (2),(3),(4),(5),(6);
1282
select 99 union all select id from t1 order by 1;
1283
99
1284
1
1285
2
1286
3
1287
4
1288
5
1289
99
1290
select id from t1 union all select 99 order by 1;
1291
id
1292
1
1293
2
1294
3
1295
4
1296
5
1297
99
1298
drop table t1;
1299
create table t1(f1 char(1), f2 char(5), f3 blob, f4 blob, f5 timestamp, f6 varchar(1) collate utf8_general_ci, f7 text);
1300
create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1;
1301
show create table t2;
1302
Table Create Table
1303
t2 CREATE TABLE `t2` (
1304
`f1` varchar(1) DEFAULT NULL,
1305
`f2` varchar(5) DEFAULT NULL,
1306
`f3` blob,
1307
`f4` blob,
1308
`f5` timestamp NULL DEFAULT NULL,
1309
`f6` varchar(1) DEFAULT NULL,
1310
`f7` text,
1311
`f8` text
1312
) ENGINE=DEFAULT
1313
drop table t1, t2;
1314
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1315
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1316
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1317
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1318
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1319
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1320
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1321
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1322
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1323
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1324
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1325
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1326
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1327
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1328
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1329
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1330
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1331
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1332
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1333
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1334
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1335
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1336
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1337
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1338
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1339
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1340
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1341
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1342
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1343
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1344
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1345
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1346
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1347
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1348
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1349
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1350
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1351
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1352
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1353
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1354
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1355
(select avg(1)) union (select avg(1)) union (select avg(1)) union
1356
(select avg(1)) union (select avg(1)) union (select avg(1));
1357
avg(1)
1358
1.0000
1359
select '12' union select '12345';
1360
12
1361
12
1362
CREATE TABLE t1 (a int);
1363
INSERT INTO t1 VALUES (3),(1),(2),(4),(1);
1364
SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a) AS test;
1365
a
1366
1
1367
2
1368
3
1369
4
1370
SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test;
1371
ERROR 42S22: Unknown column 'c' in 'order clause'
1372
DROP TABLE t1;
1373
(select 1 into @var) union (select 1);
1374
ERROR HY000: Incorrect usage of UNION and INTO
1375
(select 1) union (select 1 into @var);
1376
select @var;
1377
@var
1378
1
1379
(select 2) union (select 1 into @var);
1380
ERROR 42000: Result consisted of more than one row
1381
CREATE TABLE t1 (a int);
1382
INSERT INTO t1 VALUES (10), (20);
1383
CREATE TABLE t2 (b int);
1384
INSERT INTO t2 VALUES (10), (50), (50);
1385
SELECT a,1 FROM t1
1386
UNION
1387
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1388
ORDER BY a;
1389
a 1
1390
NULL 3
1391
10 1
1392
20 1
1393
50 2
1394
SELECT a,1 FROM t1
1395
UNION
1396
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1397
ORDER BY a DESC;
1398
a 1
1399
50 2
1400
20 1
1401
10 1
1402
NULL 3
1403
SELECT a,1 FROM t1
1404
UNION
1405
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1406
ORDER BY a ASC LIMIT 3;
1407
a 1
1408
NULL 3
1409
10 1
1410
20 1
1411
SELECT a,1 FROM t1
1412
UNION ALL
1413
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1414
ORDER BY a DESC;
1415
a 1
1416
50 2
1417
20 1
1418
10 1
1419
10 1
1420
NULL 3
1421
SELECT a,1 FROM t1
1422
UNION
1423
(SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a);
1424
ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
1425
SELECT a,1 FROM t1
1426
UNION ALL
1427
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a
1428
UNION
1429
SELECT 1,1;
1430
ERROR HY000: Incorrect usage of UNION and order_st BY
1431
DROP TABLE t1,t2;
1432
CREATE TABLE t1 (a INT);
1433
INSERT INTO t1 VALUES (1), (2), (3);
1434
CREATE TABLE t2 SELECT * FROM (SELECT NULL) a UNION SELECT a FROM t1;
1435
DESC t2;
1436
Field Type Null Key Default Extra
1437
NULL int YES NULL
1438
CREATE TABLE t3 SELECT a FROM t1 UNION SELECT * FROM (SELECT NULL) a;
1439
DESC t3;
1440
Field Type Null Key Default Extra
1441
a int YES NULL
1442
CREATE TABLE t4 SELECT NULL;
1443
DESC t4;
1444
Field Type Null Key Default Extra
1445
NULL varbinary(0) YES NULL
1446
CREATE TABLE t5 SELECT NULL UNION SELECT NULL;
1447
DESC t5;
1448
Field Type Null Key Default Extra
1449
NULL varbinary(0) YES NULL
1450
CREATE TABLE t6
1451
SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1;
1452
DESC t6;
1453
Field Type Null Key Default Extra
1454
NULL int YES NULL
1455
DROP TABLE t1, t2, t3, t4, t5, t6;
1456
End of 5.0 tests
Loggerhead is a web-based interface for Breezy
Version: 2.0.1