union.result 31.2 KB
Newer Older
1
drop table if exists t1,t2,t3,t4,t5,t6;
2 3 4 5
CREATE TABLE t1 (a int not null, b char (10) not null);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
CREATE TABLE t2 (a int not null, b char (10) not null);
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;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
7 8 9 10 11 12 13
a	b
1	a
2	b
3	c
4	d
5	f
6	e
14
select a,b from t1 union all select a,b from t2;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
15 16 17 18 19 20 21 22 23
a	b
1	a
2	b
3	c
3	c
3	c
4	d
5	f
6	e
24
select a,b from t1 union all select a,b from t2 order by b;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
25 26 27 28 29 30 31 32 33
a	b
1	a
2	b
3	c
3	c
3	c
4	d
6	e
5	f
34
select a,b from t1 union all select a,b from t2 union select 7,'g';
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
35 36 37 38 39 40 41 42
a	b
1	a
2	b
3	c
4	d
5	f
6	e
7	g
43
select 0,'#' union select a,b from t1 union all select a,b from t2 union select 7,'gg';
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
44 45 46 47 48 49 50 51
0	#
0	#
1	a
2	b
3	c
4	d
5	f
6	e
52
7	gg
53
select a,b from t1 union select a,b from t1;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
54 55 56 57
a	b
1	a
2	b
3	c
58
select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
59 60 61 62 63 64 65 66
t1	b	count(*)
t1	a	1
t1	b	1
t1	c	2
t2	c	1
t2	d	1
t2	e	1
t2	f	1
67 68 69 70 71 72
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 4;
a	b
1	a
2	b
3	c
4	d
73 74 75
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1);
a	b
1	a
76 77
2	b
3	c
78 79 80 81 82
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by b desc;
a	b
3	c
2	b
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;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
86
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
87
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
88
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	4	Using filesort
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
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
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
92 93 94 95 96 97 98
(select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 2;
a	b
1	a
2	b
select found_rows();
found_rows()
6
99 100 101 102 103 104
select sql_calc_found_rows  a,b from t1  union all select a,b from t2 limit 2;
a	b
1	a
2	b
select found_rows();
found_rows()
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
105
8
106
explain select a,b from t1 union all select a,b from t2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
107
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
108
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
109
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	4	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
110
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
111
explain select xx from t1 union select 1;
112
ERROR 42S22: Unknown column 'xx' in 'field list'
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
113
explain select a,b from t1 union select 1;
114
ERROR 21000: The used SELECT statements have a different number of columns
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
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
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
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 136 137 138
create table t3 select a,b from t1 union all select a,b from t2;
insert into t3 select a,b from t1 union all select a,b from t2;
replace into t3 select a,b as c from t1 union all select a,b from t2;
drop table t1,t2,t3;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
139
select * union select 1;
140
ERROR HY000: No tables used
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
141 142 143 144
select 1 as a,(select a union select a);
a	(select a union select a)
1	1
(select 1) union (select 2) order by 0;
145
ERROR 42S22: Unknown column '0' in 'order clause'
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
146 147 148 149 150
SELECT @a:=1 UNION SELECT @a:=@a+1;
@a:=1
1
2
(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a);
151
ERROR 42S22: Unknown column 'a' in 'field list'
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
152 153 154 155
(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);
1	3
1	3
2	1
156 157 158 159 160 161
CREATE TABLE t1 (
`pseudo` char(35) NOT NULL default '',
`pseudo1` char(35) NOT NULL default '',
`same` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY  (`pseudo1`),
KEY `pseudo` (`pseudo`)
162
) ENGINE=MyISAM;
163 164
INSERT INTO t1 (pseudo,pseudo1,same) VALUES ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1);
SELECT pseudo FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo FROM t1 WHERE pseudo='joce';
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
165 166 167
pseudo
dekad
joce
168
SELECT pseudo1 FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t1 WHERE pseudo='joce';
169 170 171 172
pseudo1
joce
testtt
tsestset
173
SELECT * FROM t1 WHERE pseudo1='joce' UNION SELECT * FROM t1 WHERE pseudo='joce' order by pseudo desc,pseudo1 desc;
174 175
pseudo	pseudo1	same
joce	tsestset	1
176 177
joce	testtt	1
dekad	joce	1
178
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT pseudo FROM t1 WHERE pseudo1='joce';
179 180 181 182
pseudo1
testtt
tsestset
dekad
183
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION ALL SELECT pseudo FROM t1 WHERE pseudo1='joce';
184 185 186 187
pseudo1
testtt
tsestset
dekad
188
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT 1;
189 190 191 192
pseudo1
testtt
tsestset
1
193
drop table t1;
194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210
create table t1 (a int);
create table t2 (a int);
insert into t1 values (1),(2),(3),(4),(5);
insert into t2 values (11),(12),(13),(14),(15);
(select * from t1 limit 2) union (select * from t2 limit 3) limit 4;
a
1
2
11
12
(select * from t1 limit 2) union (select * from t2 limit 3);
a
1
2
11
12
13
211 212 213 214 215
(select * from t1 limit 2) union (select * from t2 limit 20,3);
a
1
2
set SQL_SELECT_LIMIT=2;
216
(select * from t1 limit 1) union (select * from t2 limit 3);
217 218
a
1
219
11
220
set SQL_SELECT_LIMIT=DEFAULT;
221
drop table t1,t2;
222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278
CREATE TABLE t1 (
cid smallint(5) unsigned NOT NULL default '0',
cv varchar(250) NOT NULL default '',
PRIMARY KEY  (cid),
UNIQUE KEY cv (cv)
) ;
INSERT INTO t1 VALUES (8,'dummy');
CREATE TABLE t2 (
cid bigint(20) unsigned NOT NULL auto_increment,
cap varchar(255) NOT NULL default '',
PRIMARY KEY  (cid),
KEY cap (cap)
) ;
CREATE TABLE t3 (
gid bigint(20) unsigned NOT NULL auto_increment,
gn varchar(255) NOT NULL default '',
must tinyint(4) default NULL,
PRIMARY KEY  (gid),
KEY gn (gn)
) ;
INSERT INTO t3 VALUES (1,'V1',NULL);
CREATE TABLE t4 (
uid bigint(20) unsigned NOT NULL default '0',
gid bigint(20) unsigned default NULL,
rid bigint(20) unsigned default NULL,
cid bigint(20) unsigned default NULL,
UNIQUE KEY m (uid,gid,rid,cid),
KEY uid (uid),
KEY rid (rid),
KEY cid (cid),
KEY container (gid,rid,cid)
) ;
INSERT INTO t4 VALUES (1,1,NULL,NULL);
CREATE TABLE t5 (
rid bigint(20) unsigned NOT NULL auto_increment,
rl varchar(255) NOT NULL default '',
PRIMARY KEY  (rid),
KEY rl (rl)
) ;
CREATE TABLE t6 (
uid bigint(20) unsigned NOT NULL auto_increment,
un varchar(250) NOT NULL default '',
uc smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY  (uid),
UNIQUE KEY nc (un,uc),
KEY un (un)
) ;
INSERT INTO t6 VALUES (1,'test',8);
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";
uid	rl	g1	cid	gg
1	NULL	V1	NULL	1
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";
uid	rl	g1	cid	gg
(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");
uid	rl	g1	cid	gg
1	NULL	V1	NULL	1
drop table t1,t2,t3,t4,t5,t6;
279 280 281 282 283
CREATE TABLE t1 (a int not null, b char (10) not null);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
CREATE TABLE t2 (a int not null, b char (10) not null);
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
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 289 290 291 292 293 294 295 296
a	b
1	a
2	b
3	c
4	d
5	f
6	e
1	a
2	b
297
select * from t4;
298 299 300 301 302 303 304 305 306
a	b
1	a
2	b
1	a
2	b
3	c
4	d
5	f
6	e
307
drop table t1,t2,t3,t4;
308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343
create table t1 (a int);
insert into t1 values (1),(2),(3);
create table t2 (a int);
insert into t2 values (3),(4),(5);
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1;
a
1
select found_rows();
found_rows()
6
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;
a
1
3
select found_rows();
found_rows()
4
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);
a
1
3
4
5
select found_rows();
found_rows()
4
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);
a
1
2
3
3
select found_rows();
found_rows()
4
(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 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;
a
1
3
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
a
1
2
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;
a
1
2
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
a
1
2
3
4
5
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
a
1
2
3
4
5
select found_rows();
found_rows()
5
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
a
1
3
4
5
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
a
1
3
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
a
3
405
4
406 407 408 409 410 411 412 413 414 415 416 417 418
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
a
3
4
5
select found_rows();
found_rows()
5
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
a
419
5
420 421 422 423 424 425 426
(SELECT * FROM t1 ORDER by a) UNION ALL (SELECT * FROM t2 ORDER BY a) ORDER BY A desc LIMIT 4;
a
5
4
3
3
(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;
monty@mysql.com's avatar
monty@mysql.com committed
429
drop temporary table t1;
430
create table t1 select a from t1 union select a from t2;
monty@mysql.com's avatar
monty@mysql.com committed
431
ERROR HY000: You can't specify target table 't1' for update in FROM clause
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
432
select a from t1 union select a from t2 order by t2.a;
433
ERROR 42S02: Unknown table 't2' in order clause
434
drop table t1,t2;
435 436 437 438
select length(version()) > 1 as `*` UNION select 2;
*
1
2
439 440 441
create table t1 (a int);
insert into t1 values (0), (3), (1), (2);
explain (select * from t1) union (select * from t1) order by a;
monty@mysql.com's avatar
monty@mysql.com committed
442 443 444 445
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
2	UNION	t1	ALL	NULL	NULL	NULL	NULL	4	
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 451 452 453 454 455 456 457 458 459 460
INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1",
"foo1", "bar1");
INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1",
"foo2", "bar2");
INSERT INTO t2 (id, id_master, text1, text2) VALUES("3", "1", NULL,
"bar3");
INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1",
"foo4", "bar4");
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;
id_master	id	text1	text2
1	1	NULL	ABCDE
461 462
1	1	foo1	bar1
1	2	foo2	bar2
463
1	3	NULL	bar3
464
1	4	foo4	bar4
465 466 467 468 469 470 471 472
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;
id_master	id	text1	text2
1	1	ABCDE	ABCDE
1	1	foo1	bar1
1	2	foo2	bar2
1	3	NULL	bar3
1	4	foo4	bar4
drop table if exists t1,t2;
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
473 474 475 476
create table t1 (a int not null primary key auto_increment, b int, key(b));
create table t2 (a int not null primary key auto_increment, b int);
insert into t1 (b) values (1),(2),(2),(3);
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);
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
478 479 480
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	
2	UNION	t2	const	PRIMARY	PRIMARY	4	const	1	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
481
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
482
Warnings:
483
Note	1003	(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = 1)) union (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` = 1))
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
484 485 486 487 488 489 490 491 492 493 494 495 496 497 498
(select * from t1 where a=5) union (select * from t2 where a=1);
a	b
1	10
(select * from t1 where a=5 and a=6) union (select * from t2 where a=1);
a	b
1	10
(select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1);
a	b
1	10
(select * from t1 where a=1) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);
a	b
1	1
2	2
3	3
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);
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
500 501 502
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	UNION	t1	index	PRIMARY	PRIMARY	4	NULL	4	Using index
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
503
2	UNION	t2	index	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
504
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
505 506 507 508
explain (select * from t1 where a=1) union (select * from t1 where b=1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	
2	UNION	t1	ref	b	b	5	const	1	Using where
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
509
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
510
drop table t1,t2;
511 512 513 514 515 516 517 518 519 520 521 522 523
create table t1 (   id int not null auto_increment, primary key (id)   ,user_name text );
create table t2 (    id int not null auto_increment, primary key (id)   ,group_name text );
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) );
insert into t1 (user_name) values ('Tester');
insert into t2 (group_name) values ('Group A');
insert into t2 (group_name) values ('Group B');
insert into t3 (user_id, group_id) values (1,1);
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;
is_in_group	user_name	group_name	id
1	Tester	Group A	1
0	Tester	Group A	NULL
0	Tester	Group B	NULL
drop table t1, t2, t3;
524 525 526 527 528 529 530 531 532 533 534 535 536 537
create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
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);
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
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;
pla_id	matintnum
100	a
101	a
102	a
103	b
104	b
105	c
0	0
drop table t1, t2;
538 539 540 541 542 543 544 545
create table t1 SELECT "a" as a UNION select "aa" as a;
select * from t1;
a
a
aa
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
546
  `a` char(2) NOT NULL default ''
547
) ENGINE=MyISAM DEFAULT CHARSET=latin1
548 549 550 551 552 553 554 555 556
drop table t1;
create table t1 SELECT 12 as a UNION select "aa" as a;
select * from t1;
a
12
aa
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
557
  `a` char(20) NOT NULL default ''
558
) ENGINE=MyISAM DEFAULT CHARSET=latin1
559 560 561 562 563 564 565 566 567
drop table t1;
create table t1 SELECT 12 as a UNION select 12.2 as a;
select * from t1;
a
12.0
12.2
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
568
  `a` double(53,1) NOT NULL default '0.0'
569
) ENGINE=MyISAM DEFAULT CHARSET=latin1
570
drop table t1;
571 572
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);
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 574 575 576 577 578 579 580 581
create table t1 SELECT it2 from t2 UNION select it1 from t2;
select * from t1;
it2
1
NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `it2` tinyint(4) default NULL
582
) ENGINE=MyISAM DEFAULT CHARSET=latin1
583 584 585 586 587 588 589 590 591
drop table t1;
create table t1 SELECT it2 from t2 UNION select i from t2;
select * from t1;
it2
1
3
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
592
  `it2` int(11) NOT NULL default '0'
593
) ENGINE=MyISAM DEFAULT CHARSET=latin1
594 595 596 597 598 599 600 601 602
drop table t1;
create table t1 SELECT i from t2 UNION select f from t2;
select * from t1;
i
3
1.5
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
603
  `i` double default NULL
604
) ENGINE=MyISAM DEFAULT CHARSET=latin1
605 606 607 608 609 610 611 612 613 614
drop table t1;
create table t1 SELECT f from t2 UNION select d from t2;
select * from t1;
f
1.5
2.5
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f` double default NULL
615
) ENGINE=MyISAM DEFAULT CHARSET=latin1
616
drop table t1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
617 618 619 620 621 622 623 624 625
create table t1 SELECT ib from t2 UNION select f from t2;
select * from t1;
ib
4
1.5
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `ib` double default NULL
626
) ENGINE=MyISAM DEFAULT CHARSET=latin1
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
627 628 629 630 631 632 633 634 635 636
drop table t1;
create table t1 SELECT ib from t2 UNION select d from t2;
select * from t1;
ib
4
2.5
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `ib` double default NULL
637
) ENGINE=MyISAM DEFAULT CHARSET=latin1
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
638
drop table t1;
639 640 641 642 643 644 645 646
create table t1 SELECT f from t2 UNION select y from t2;
select * from t1;
f
1.5
1972
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
647
  `f` float default NULL
648
) ENGINE=MyISAM DEFAULT CHARSET=latin1
649 650 651 652 653 654 655 656 657
drop table t1;
create table t1 SELECT f from t2 UNION select da from t2;
select * from t1;
f
1.5
1972-10-22
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
658
  `f` binary(24) default NULL
659
) ENGINE=MyISAM DEFAULT CHARSET=latin1
660 661 662 663 664 665 666 667 668
drop table t1;
create table t1 SELECT y from t2 UNION select da from t2;
select * from t1;
y
1972
1972-10-22
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
669
  `y` binary(10) default NULL
670
) ENGINE=MyISAM DEFAULT CHARSET=latin1
671 672 673 674 675 676 677 678 679
drop table t1;
create table t1 SELECT y from t2 UNION select dt from t2;
select * from t1;
y
1972
1972-10-22 11:50:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
680
  `y` binary(19) default NULL
681
) ENGINE=MyISAM DEFAULT CHARSET=latin1
682 683 684 685 686 687 688 689 690 691
drop table t1;
create table t1 SELECT da from t2 UNION select dt from t2;
select * from t1;
da
1972-10-22 00:00:00
1972-10-22 11:50:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `da` datetime default NULL
692
) ENGINE=MyISAM DEFAULT CHARSET=latin1
693 694 695 696 697 698 699 700 701
drop table t1;
create table t1 SELECT dt from t2 UNION select sc from t2;
select * from t1;
dt
1972-10-22 11:50:00
testc
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
702
  `dt` binary(19) default NULL
703
) ENGINE=MyISAM DEFAULT CHARSET=latin1
704 705 706 707 708 709 710 711 712
drop table t1;
create table t1 SELECT dt from t2 UNION select sv from t2;
select * from t1;
dt
1972-10-22 11:50:00
testv
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
713
  `dt` binary(19) default NULL
714
) ENGINE=MyISAM DEFAULT CHARSET=latin1
715 716 717 718 719 720 721 722 723 724
drop table t1;
create table t1 SELECT sc from t2 UNION select sv from t2;
select * from t1;
sc
testc
testv
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `sc` varchar(10) default NULL
725
) ENGINE=MyISAM DEFAULT CHARSET=latin1
726 727 728 729 730 731 732 733 734
drop table t1;
create table t1 SELECT dt from t2 UNION select b from t2;
select * from t1;
dt
1972-10-22 11:50:00
tetetetetest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
735
  `dt` blob
736
) ENGINE=MyISAM DEFAULT CHARSET=latin1
737 738 739 740 741 742 743 744 745
drop table t1;
create table t1 SELECT sv from t2 UNION select b from t2;
select * from t1;
sv
testv
tetetetetest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
746
  `sv` longblob
747
) ENGINE=MyISAM DEFAULT CHARSET=latin1
748 749 750 751 752 753 754 755 756 757 758
drop table t1;
create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
select * from t1;
i
3
2.5
tetetetetest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` blob
759
) ENGINE=MyISAM DEFAULT CHARSET=latin1
760 761 762 763 764 765 766 767 768 769
drop table t1;
create table t1 SELECT sv from t2 UNION select tx from t2;
select * from t1;
sv
testv
teeeeeeeeeeeest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `sv` text
770
) ENGINE=MyISAM DEFAULT CHARSET=latin1
771 772 773 774 775 776 777 778 779 780
drop table t1;
create table t1 SELECT b from t2 UNION select tx from t2;
select * from t1;
b
tetetetetest
teeeeeeeeeeeest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `b` longblob
781
) ENGINE=MyISAM DEFAULT CHARSET=latin1
782
drop table t1,t2;
783 784 785 786 787 788 789 790 791 792 793 794 795
create table t1 (d decimal(10,1));
create table t2 (d decimal(10,9));
insert into t1 values ("100000000.0");
insert into t2 values ("1.23456780");
create table t3 select * from t2 union select * from t1;
select * from t3;
d
1.234567800
100000000.0
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `d` decimal(10,9) default NULL
796
) ENGINE=MyISAM DEFAULT CHARSET=latin1
797 798 799 800 801 802 803 804 805
drop table t1,t2,t3;
create table t1 select 1 union select -1;
select * from t1;
1
1
-1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
806
  `1` bigint(20) NOT NULL default '0'
807
) ENGINE=MyISAM DEFAULT CHARSET=latin1
808 809 810 811 812 813 814
drop table t1;
create table t1 select _latin1"test" union select _latin2"testt" ;
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'UNION'
create table t1 select _latin2"test" union select _latin2"testt" ;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
815
  `test` char(5) character set latin2 NOT NULL default ''
816
) ENGINE=MyISAM DEFAULT CHARSET=latin1
817
drop table t1;
818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837
create table t1 (s char(200));
insert into t1 values (repeat("1",200));
create table t2 select * from t1;
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
set local tmp_table_size=1024;
select count(*) from (select * from t1 union all select * from t2 order by 1) b;
count(*)
21
select count(*) from t1;
count(*)
8
select count(*) from t2;
count(*)
13
drop table t1,t2;
set local tmp_table_size=default;
838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862
create table t1 (a int, index (a), b int);
insert t1 values (1,1),(2,2),(3,3),(4,4),(5,5);
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
FLUSH STATUS;
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	0
select count(*) from t1 where a=7;
count(*)
26
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	0
select count(*) from t1 where b=13;
count(*)
10
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	1
select count(*) from t1 where b=13 union select count(*) from t1 where a=7;
count(*)
863
10
864 865 866 867 868 869 870 871 872 873 874 875
26
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	2
select count(*) from t1 where a=7 union select count(*) from t1 where b=13;
count(*)
26
10
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	3
drop table t1;
876 877 878 879 880 881 882 883 884 885
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;
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');
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);
NAME	PHONE	NAME	PHONE
a	111	NULL	NULL
b	222	NULL	NULL
d	444	d	454
NULL	NULL	f	666
NULL	NULL	g	777
drop  table t1;
886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913
create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);
insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
select col1 n from t1 union select col2 n from t1 order by n;
n
1
2
3
4
5
6
7
8
9
10
alter table t1 add index myindex (col2);
select col1 n from t1 union select col2 n from t1 order by n;
n
1
2
3
4
5
6
7
8
9
10
drop  table t1;
914 915 916 917 918 919 920 921 922 923 924 925 926 927
create table t1 (i int);
insert into t1 values (1);
select * from t1 UNION select * from t1;
i
1
select * from t1 UNION ALL select * from t1;
i
1
1
select * from t1 UNION select * from t1 UNION ALL select * from t1;
i
1
1
drop table t1;
928 929 930 931 932
select 1 as a union all select 1 union all select 2 union select 1 union all select 2;
a
1
2
2
933 934 935 936 937 938 939 940 941 942 943
set sql_select_limit=1;
select 1 union select 2;
1
1
(select 1) union (select 2);
1
1
(select 1) union (select 2) union (select 3) limit 2;
1
1
2
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
944
set sql_select_limit=default;
945 946 947 948 949 950 951 952 953 954 955 956 957 958
create table t1 (a int);
insert into t1 values (100), (1);
create table t2 (a int);
insert into t2 values (100);
select a from t1 union select a from t2 order by a;
a
1
100
SET SQL_SELECT_LIMIT=1;
select a from t1 union select a from t2 order by a;
a
1
drop table t1, t2;
set sql_select_limit=default;
959 960 961 962 963
CREATE TABLE t1 (i int(11) default NULL,c char(1) default NULL,KEY i (i));
CREATE TABLE t2 (i int(11) default NULL,c char(1) default NULL,KEY i (i));
explain (select * from t1) union (select * from t2) order by not_existing_column;
ERROR 42S22: Unknown column 'not_existing_column' in 'order clause'
drop table t1, t2;
964 965 966 967 968 969 970
CREATE TABLE t1 (uid int(1));
INSERT INTO t1 SELECT 150;
SELECT 'a' UNION SELECT uid FROM t1;
a
a
150
drop table t1;
971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998
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));
CREATE TABLE t2 ( ID int(3) unsigned NOT NULL DEFAULT '0' , DATA1 timestamp DEFAULT '0000-00-00 00:00:00' , PRIMARY KEY (ID));
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1);
ID1	ID2	DATA1	DATA2	DATA3	ID	DATA1
drop table t1,t2;
999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035
create table t1 (a ENUM('Yes', 'No') NOT NULL);
create table t2 (a ENUM('aaa', 'bbb') NOT NULL);
insert into t1 values ('No');
insert into t2 values ('bbb');
create table t3 (a SET('Yes', 'No') NOT NULL);
create table t4 (a SET('aaa', 'bbb') NOT NULL);
insert into t3 values (1);
insert into t4 values (3);
select "1" as a union select a from t1;
a
1
No
select a as a from t1 union select "1";
a
No
1
select a as a from t2 union select a from t1;
a
bbb
No
select "1" as a union select a from t3;
a
1
Yes
select a as a from t3 union select "1";
a
Yes
1
select a as a from t4 union select a from t3;
a
aaa,bbb
Yes
select a as a from t1 union select a from t4;
a
No
aaa,bbb
drop table t1,t2,t3,t4;
1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113
create table t1 as
(select _latin1'test') union
(select _latin1'TEST') union
(select _latin1'TeST');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `test` char(4) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select count(*) from t1;
count(*)
1
drop table t1;
create table t1 as
(select _latin1'test' collate latin1_bin) union
(select _latin1'TEST') union
(select _latin1'TeST');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `_latin1'test' collate latin1_bin` char(4) character set latin1 collate latin1_bin NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select count(*) from t1;
count(*)
3
drop table t1;
create table t1 as
(select _latin1'test') union
(select _latin1'TEST' collate latin1_bin) union
(select _latin1'TeST');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `test` char(4) character set latin1 collate latin1_bin NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select count(*) from t1;
count(*)
3
drop table t1;
create table t1 as
(select _latin1'test') union
(select _latin1'TEST') union
(select _latin1'TeST' collate latin1_bin);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `test` char(4) character set latin1 collate latin1_bin NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select count(*) from t1;
count(*)
3
drop table t1;
create table t2 (
a char character set latin1 collate latin1_swedish_ci,
b char character set latin1 collate latin1_bin);
create table t1 as
(select a from t2) union
(select b from t2);
ERROR HY000: Illegal mix of collations for operation 'UNION'
create table t1 as
(select a collate latin1_german1_ci from t2) union
(select b from t2);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a collate latin1_german1_ci` char(1) character set latin1 collate latin1_german1_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as
(select a from t2) union
(select b collate latin1_german1_ci from t2);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(1) character set latin1 collate latin1_german1_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
drop table t2;