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;
14
select a,b from t1 union all select a,b from t2;
24
select a,b from t1 union all select a,b from t2 order by b;
34
select a,b from t1 union all select a,b from t2 union select 7,'g';
43
select 0,'#' union select a,b from t1 union all select a,b from t2 union select 7,'gg';
53
select a,b from t1 union select a,b from t1;
58
select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b;
67
(select a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 4;
73
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1);
78
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc;
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
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;
99
select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2;
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;
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)
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;
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);
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`)
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';
168
SELECT pseudo1 FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t1 WHERE pseudo='joce';
173
SELECT * FROM t1 WHERE pseudo1='joce' UNION SELECT * FROM t1 WHERE pseudo='joce' order by pseudo desc,pseudo1 desc;
178
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT pseudo FROM t1 WHERE pseudo1='joce';
183
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION ALL SELECT pseudo FROM t1 WHERE pseudo1='joce';
188
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT 1;
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;
204
(select * from t1 limit 2) union (select * from t2 limit 3);
211
(select * from t1 limit 2) union (select * from t2 limit 20,3);
215
set SQL_SELECT_LIMIT=2;
216
(select * from t1 limit 1) union (select * from t2 limit 3);
220
set SQL_SELECT_LIMIT=DEFAULT;
223
cid smallint(5) unsigned NOT NULL default '0',
224
cv varchar(250) NOT NULL default '',
228
INSERT INTO t1 VALUES (8,'dummy');
230
cid bigint(20) unsigned NOT NULL auto_increment,
231
cap varchar(255) NOT NULL default '',
236
gid bigint(20) unsigned NOT NULL auto_increment,
237
gn varchar(255) NOT NULL default '',
238
must tinyint(4) default NULL,
242
INSERT INTO t3 VALUES (1,'V1',NULL);
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),
252
KEY container (gid,rid,cid)
254
INSERT INTO t4 VALUES (1,1,NULL,NULL);
256
rid bigint(20) unsigned NOT NULL auto_increment,
257
rl varchar(255) NOT NULL default '',
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',
266
UNIQUE KEY nc (un,uc),
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";
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";
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");
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;
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;
318
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;
325
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);
334
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);
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;
352
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
359
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;
366
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
376
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
386
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
395
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
402
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
409
SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
417
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
420
(SELECT * FROM t1 ORDER by a) UNION ALL (SELECT * FROM t2 ORDER BY a) ORDER BY A desc LIMIT 4;
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'
435
select length(version()) > 1 as `*` UNION select 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
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",
452
INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1",
454
INSERT INTO t2 (id, id_master, text1, text2) VALUES("3", "1", NULL,
456
INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1",
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
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
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
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);
487
(select * from t1 where a=5 and a=6) union (select * from t2 where a=1);
490
(select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1);
493
(select * from t1 where a=1) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);
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
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
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;
538
create table t1 SELECT "a" as a UNION select "aa" as a;
543
show create table t1;
545
t1 CREATE TABLE `t1` (
546
`a` varchar(2) NOT NULL DEFAULT ''
547
) ENGINE=MyISAM DEFAULT CHARSET=latin1
549
create table t1 SELECT 12 as a UNION select "aa" as a;
554
show create table t1;
556
t1 CREATE TABLE `t1` (
557
`a` varbinary(2) NOT NULL DEFAULT ''
558
) ENGINE=MyISAM DEFAULT CHARSET=latin1
560
create table t1 SELECT 12 as a UNION select 12.2 as a;
565
show create table t1;
567
t1 CREATE TABLE `t1` (
568
`a` decimal(3,1) NOT NULL DEFAULT '0.0'
569
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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;
578
show create table t1;
580
t1 CREATE TABLE `t1` (
581
`it2` tinyint(4) DEFAULT NULL
582
) ENGINE=MyISAM DEFAULT CHARSET=latin1
584
create table t1 SELECT it2 from t2 UNION select i from t2;
589
show create table t1;
591
t1 CREATE TABLE `t1` (
592
`it2` int(11) NOT NULL DEFAULT '0'
593
) ENGINE=MyISAM DEFAULT CHARSET=latin1
595
create table t1 SELECT i from t2 UNION select f from t2;
600
show create table t1;
602
t1 CREATE TABLE `t1` (
603
`i` double DEFAULT NULL
604
) ENGINE=MyISAM DEFAULT CHARSET=latin1
606
create table t1 SELECT f from t2 UNION select d from t2;
611
show create table t1;
613
t1 CREATE TABLE `t1` (
614
`f` double DEFAULT NULL
615
) ENGINE=MyISAM DEFAULT CHARSET=latin1
617
create table t1 SELECT ib from t2 UNION select f from t2;
622
show create table t1;
624
t1 CREATE TABLE `t1` (
625
`ib` double DEFAULT NULL
626
) ENGINE=MyISAM DEFAULT CHARSET=latin1
628
create table t1 SELECT ib from t2 UNION select d from t2;
633
show create table t1;
635
t1 CREATE TABLE `t1` (
636
`ib` double DEFAULT NULL
637
) ENGINE=MyISAM DEFAULT CHARSET=latin1
639
create table t1 SELECT f from t2 UNION select y from t2;
644
show create table t1;
646
t1 CREATE TABLE `t1` (
647
`f` float DEFAULT NULL
648
) ENGINE=MyISAM DEFAULT CHARSET=latin1
650
create table t1 SELECT f from t2 UNION select da from t2;
655
show create table t1;
657
t1 CREATE TABLE `t1` (
658
`f` varbinary(12) DEFAULT NULL
659
) ENGINE=MyISAM DEFAULT CHARSET=latin1
661
create table t1 SELECT y from t2 UNION select da from t2;
666
show create table t1;
668
t1 CREATE TABLE `t1` (
669
`y` varbinary(10) DEFAULT NULL
670
) ENGINE=MyISAM DEFAULT CHARSET=latin1
672
create table t1 SELECT y from t2 UNION select dt from t2;
677
show create table t1;
679
t1 CREATE TABLE `t1` (
680
`y` varbinary(19) DEFAULT NULL
681
) ENGINE=MyISAM DEFAULT CHARSET=latin1
683
create table t1 SELECT da from t2 UNION select dt from t2;
688
show create table t1;
690
t1 CREATE TABLE `t1` (
691
`da` datetime DEFAULT NULL
692
) ENGINE=MyISAM DEFAULT CHARSET=latin1
694
create table t1 SELECT dt from t2 UNION select trim(sc) from t2;
695
select trim(dt) from t1;
699
show create table t1;
701
t1 CREATE TABLE `t1` (
702
`dt` varbinary(19) DEFAULT NULL
703
) ENGINE=MyISAM DEFAULT CHARSET=latin1
705
create table t1 SELECT dt from t2 UNION select sv from t2;
710
show create table t1;
712
t1 CREATE TABLE `t1` (
713
`dt` varbinary(19) DEFAULT NULL
714
) ENGINE=MyISAM DEFAULT CHARSET=latin1
716
create table t1 SELECT sc from t2 UNION select sv from t2;
721
show create table t1;
723
t1 CREATE TABLE `t1` (
724
`sc` varchar(10) DEFAULT NULL
725
) ENGINE=MyISAM DEFAULT CHARSET=latin1
727
create table t1 SELECT dt from t2 UNION select b from t2;
732
show create table t1;
734
t1 CREATE TABLE `t1` (
736
) ENGINE=MyISAM DEFAULT CHARSET=latin1
738
create table t1 SELECT sv from t2 UNION select b from t2;
743
show create table t1;
745
t1 CREATE TABLE `t1` (
747
) ENGINE=MyISAM DEFAULT CHARSET=latin1
749
create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
755
show create table t1;
757
t1 CREATE TABLE `t1` (
759
) ENGINE=MyISAM DEFAULT CHARSET=latin1
761
create table t1 SELECT sv from t2 UNION select tx from t2;
766
show create table t1;
768
t1 CREATE TABLE `t1` (
770
) ENGINE=MyISAM DEFAULT CHARSET=latin1
772
create table t1 SELECT b from t2 UNION select tx from t2;
777
show create table t1;
779
t1 CREATE TABLE `t1` (
781
) ENGINE=MyISAM DEFAULT CHARSET=latin1
783
create table t1 select 1 union select -1;
788
show create table t1;
790
t1 CREATE TABLE `t1` (
791
`1` bigint(20) NOT NULL DEFAULT '0'
792
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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;
799
t1 CREATE TABLE `t1` (
800
`test` varchar(5) CHARACTER SET latin2 NOT NULL DEFAULT ''
801
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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;
815
select count(*) from t1;
818
select count(*) from 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;
831
show status like 'Slow_queries';
834
select count(*) from t1 where a=7;
837
show status like 'Slow_queries';
840
select count(*) from t1 where b=13;
843
show status like 'Slow_queries';
846
select count(*) from t1 where b=13 union select count(*) from t1 where a=7;
850
show status like 'Slow_queries';
853
select count(*) from t1 where a=7 union select count(*) from t1 where b=13;
857
show status like 'Slow_queries';
861
select a from t1 where b not in (1,2,3) union select a from t1 where b not in (4,5,6);
873
show status like 'Slow_queries';
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
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;
901
alter table t1 add index myindex (col2);
902
select col1 n from t1 union select col2 n from t1 order by n;
915
create table t1 (i int);
916
insert into t1 values (1);
917
select * from t1 UNION select * from t1;
920
select * from t1 UNION ALL select * from t1;
924
select * from t1 UNION select * from t1 UNION ALL select * from t1;
929
select 1 as a union all select 1 union all select 2 union select 1 union all select 2;
934
set sql_select_limit=1;
935
select 1 union select 2;
938
(select 1) union (select 2);
941
(select 1) union (select 2) union (select 3) limit 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;
954
SET SQL_SELECT_LIMIT=1;
955
select a from t1 union select a from t2 order by a;
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'
965
CREATE TABLE t1 (uid int(1));
966
INSERT INTO t1 SELECT 150;
967
SELECT 'a' UNION SELECT uid FROM 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
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;
1012
select a as a from t1 union select "1";
1016
select a as a from t2 union select a from t1;
1020
select "1" as a union select a from t3;
1024
select a as a from t3 union select "1";
1028
select a as a from t4 union select a from t3;
1032
select a as a from t1 union select a from t4;
1036
drop table t1,t2,t3,t4;
1038
(select _latin1'test') union
1039
(select _latin1'TEST') union
1040
(select _latin1'TeST');
1041
show create table t1;
1043
t1 CREATE TABLE `t1` (
1044
`test` varchar(4) NOT NULL DEFAULT ''
1045
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1046
select count(*) from t1;
1051
(select _latin1'test' collate latin1_bin) union
1052
(select _latin1'TEST') union
1053
(select _latin1'TeST');
1054
show create table t1;
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;
1064
(select _latin1'test') union
1065
(select _latin1'TEST' collate latin1_bin) union
1066
(select _latin1'TeST');
1067
show create table t1;
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;
1077
(select _latin1'test') union
1078
(select _latin1'TEST') union
1079
(select _latin1'TeST' collate latin1_bin);
1080
show create table t1;
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;
1090
a char character set latin1 collate latin1_swedish_ci,
1091
b char character set latin1 collate latin1_german1_ci);
1093
(select a from t2) union
1095
ERROR HY000: Illegal mix of collations for operation 'UNION'
1097
(select a collate latin1_german1_ci from t2) union
1099
show create table t1;
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
1106
(select a from t2) union
1107
(select b collate latin1_german1_ci from t2);
1108
show create table t1;
1110
t1 CREATE TABLE `t1` (
1111
`a` varchar(1) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL
1112
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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;
1120
t1 CREATE TABLE `t1` (
1121
`a` varchar(1) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL
1122
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1125
create table t1(a1 int, f1 char(10));
1127
select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
1129
select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
1131
show columns from t2;
1132
Field Type Null Key Default Extra
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));
1141
select t2.f3 as sdate
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
1149
select cast('2004-12-31' as date) as sdate
1151
left outer join t2 on (t1.f1 = t2.f1)
1152
inner join t3 on (t2.f2 = t3.f1)
1154
order by t1.f1, t3.f1, t2.f3
1157
show columns from t4;
1158
Field Type Null Key Default Extra
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;
1166
select * from ((select * from t1 limit 1) union (select * from t1 limit 1)) a;
1169
select * from ((select * from t1 limit 1) union (select * from t1 limit 1) union (select * from t1 limit 1)) a;
1172
select * from ((((select * from t1))) union (select * from t1) union (select * from t1)) a;
1177
select * from ((select * from t1) union (((select * from t1))) union (select * from t1)) a;
1184
select concat('value is: ', @val) union select 'some text';
1185
concat('value is: ', @val)
1188
select concat(_latin1'a', _ascii'b' collate ascii_bin);
1189
concat(_latin1'a', _ascii'b' collate ascii_bin)
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;
1199
a ENUM('�','�','�') character set utf8 not null default '�',
1200
b ENUM("one", "two") character set utf8,
1201
c ENUM("one", "two")
1203
show create table t1;
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
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
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
1226
create table t2 select a from t1 union select c from t1;
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
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;
1237
0.0000000000000000000000000
1238
0.0000000000000000000000000
1239
select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1
1241
select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;
1243
XXXXXXXXXXXXXXXXXXXX 0.0000000000000000000000000
1244
YYYYYYYYYYYYYYYYYYYY 0.0000000000000000000000000
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;
1250
0.000000000000000000000000
1251
0.000000000000000000000000
1252
select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1
1254
select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;
1256
XXXXXXXXXXXXXXXXXXXX 0.000000000000000000000000
1257
YYYYYYYYYYYYYYYYYYYY 0.000000000000000000000000
1259
create table t1 (a varchar(5));
1260
create table t2 select * from t1 union select 'abcdefghijkl';
1261
show create table t2;
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";
1269
alter table t2 ROW_FORMAT=fixed;
1270
show create table t2;
1272
t2 CREATE TABLE `t2` (
1273
`a` varchar(12) DEFAULT NULL
1274
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
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;
1283
create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;
1284
show create table t3;
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;
1297
create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;
1298
show create table t3;
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
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;
1314
t3 CREATE TABLE `t3` (
1316
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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;
1323
t3 CREATE TABLE `t3` (
1324
`a` varchar(510) DEFAULT NULL
1325
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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;
1332
t3 CREATE TABLE `t3` (
1334
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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;
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;
1356
select id from t1 union all select 99 order by 1;
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;
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,
1377
`f8` mediumtext CHARACTER SET utf8
1378
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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));
1425
select _utf8'12' union select _latin1'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;
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'
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);
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);
1454
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1463
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1472
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
1473
ORDER BY a ASC LIMIT 3;
1480
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP
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
1494
SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a
1497
ERROR HY000: Incorrect usage of UNION and ORDER BY
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;
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;
1507
Field Type Null Key Default Extra
1509
CREATE TABLE t4 SELECT NULL;
1511
Field Type Null Key Default Extra
1512
NULL binary(0) YES NULL
1513
CREATE TABLE t5 SELECT NULL UNION SELECT NULL;
1515
Field Type Null Key Default Extra
1516
NULL binary(0) YES NULL
1518
SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1;
1520
Field Type Null Key Default Extra
1521
NULL int(11) YES NULL
1522
DROP TABLE t1, t2, t3, t4, t5, t6;
added
removed
mysql-test/r/union.result
