SET default_storage_engine=ColumnStore;
DROP DATABASE IF EXISTS mcs_union;
CREATE DATABASE mcs_union;
USE mcs_union;
#
# MCOL-4700 Wrong result of a UNION for INT and INT UNSIGNED
#
# Move the UNIONs from the subqueries to outer selects and add
# ORDER BY clause after MCOL-5222 is fixed
#
CREATE TABLE t1 (a INT, b INT UNSIGNED);
INSERT INTO t1 VALUES (-1, 1), (-1, 1), (-2, 2);
SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t1) tu ORDER BY b;
a	b
-1	1
-2	2
SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu ORDER BY a;
a
-2
-1
1
2
SELECT * FROM (SELECT b FROM t1 UNION SELECT a FROM t1) tu ORDER BY b;
b
-2
-1
1
2
DROP TABLE t1;
#
# Multiple Columns Union
#
CREATE TABLE t1 (a INT, b INT, c INT);
INSERT INTO t1 VALUES (-1, 1, 0), (-2, 2, 0), (-3, 3, 0), (-4, 4, 0), (-5, 5, 0), (-1, 1, 0), (-2, 2, 0), (-3, 3, 0), (-4, 4, 0), (-5, 5, 0);
SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t1) tu ORDER BY b;
a	b	c
-1	1	0
-1	1	0
-1	1	0
-1	1	0
-2	2	0
-2	2	0
-2	2	0
-2	2	0
-3	3	0
-3	3	0
-3	3	0
-3	3	0
-4	4	0
-4	4	0
-4	4	0
-4	4	0
-5	5	0
-5	5	0
-5	5	0
-5	5	0
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT b FROM t1) tu ORDER BY a;
a
-5
-5
-4
-4
-3
-3
-2
-2
-1
-1
1
1
2
2
3
3
4
4
5
5
SELECT * FROM (SELECT b FROM t1 UNION ALL SELECT a FROM t1) tu ORDER BY b;
b
-5
-5
-4
-4
-3
-3
-2
-2
-1
-1
1
1
2
2
3
3
4
4
5
5
DROP TABLE t1;
#
# Same Int Type Union
#
CREATE TABLE t1 (a INT UNSIGNED, b INT UNSIGNED);
INSERT INTO t1 VALUES (1, 1), (1, 1), (2, 1), (2, 1), (3, 2), (4, 2), (5, 2);
SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu ORDER BY a;
a
1
2
3
4
5
SELECT * FROM (SELECT b FROM t1 UNION SELECT a FROM t1) tu ORDER BY b;
b
1
2
3
4
5
SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t1) tu ORDER BY a;
a	b
1	1
2	1
3	2
4	2
5	2
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (-1, -1), (-1, -1), (2, 1), (2, 1), (3, 2), (4, 2), (5, 2);
SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu ORDER BY a;
a
-1
1
2
3
4
5
SELECT * FROM (SELECT b FROM t1 UNION SELECT a FROM t1) tu ORDER BY b;
b
-1
1
2
3
4
5
SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t1) tu ORDER BY a;
a	b
-1	-1
2	1
3	2
4	2
5	2
DROP TABLE t1;
#
# Signed Int Union
#
CREATE TABLE t1 (a TINYINT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFFFF80 AS SIGNED)+2),(-1),(0),(1),(0x7F);
CREATE TABLE t2 (a SMALLINT);
INSERT INTO t2 VALUES (CAST(0xFFFFFFFFFFFF8000 AS SIGNED)+2),(-1),(0),(1),(0x7FFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-32766
-126
-1
-1
0
0
1
1
127
32767
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-32766
-126
-1
-1
0
0
1
1
127
32767
DROP TABLE t2;
CREATE TABLE t2 (a MEDIUMINT);
INSERT INTO t2 VALUES (CAST(0xFFFFFFFFFF800000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-8388606
-126
-1
-1
0
0
1
1
127
8388607
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-8388606
-126
-1
-1
0
0
1
1
127
8388607
DROP TABLE t2;
CREATE TABLE t2 (a INT);
INSERT INTO t2 VALUES (CAST(0xFFFFFFFF80000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-2147483646
-126
-1
-1
0
0
1
1
127
2147483647
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-2147483646
-126
-1
-1
0
0
1
1
127
2147483647
DROP TABLE t2;
CREATE TABLE t2 (a BIGINT);
INSERT INTO t2 VALUES (CAST(0x8000000000000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFFFFFFFFFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-9223372036854775806
-126
-1
-1
0
0
1
1
127
9223372036854775807
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-9223372036854775806
-126
-1
-1
0
0
1
1
127
9223372036854775807
DROP TABLE t1,t2;
CREATE TABLE t1 (a SMALLINT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFF8000 AS SIGNED)+2),(-1),(0),(1),(0x7FFF);
CREATE TABLE t2 (a MEDIUMINT);
INSERT INTO t2 VALUES (CAST(0xFFFFFFFFFF800000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-8388606
-32766
-1
-1
0
0
1
1
32767
8388607
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-8388606
-32766
-1
-1
0
0
1
1
32767
8388607
DROP TABLE t2;
CREATE TABLE t2 (a INT);
INSERT INTO t2 VALUES (CAST(0xFFFFFFFF80000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-2147483646
-32766
-1
-1
0
0
1
1
32767
2147483647
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-2147483646
-32766
-1
-1
0
0
1
1
32767
2147483647
DROP TABLE t2;
CREATE TABLE t2 (a BIGINT);
INSERT INTO t2 VALUES (CAST(0x8000000000000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFFFFFFFFFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-9223372036854775806
-32766
-1
-1
0
0
1
1
32767
9223372036854775807
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-9223372036854775806
-32766
-1
-1
0
0
1
1
32767
9223372036854775807
DROP TABLE t1,t2;
CREATE TABLE t1 (a MEDIUMINT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFF800000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFF);
CREATE TABLE t2 (a INT);
INSERT INTO t2 VALUES (CAST(0xFFFFFFFF80000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-2147483646
-8388606
-1
-1
0
0
1
1
8388607
2147483647
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-2147483646
-8388606
-1
-1
0
0
1
1
8388607
2147483647
DROP TABLE t2;
CREATE TABLE t2 (a BIGINT);
INSERT INTO t2 VALUES (CAST(0x8000000000000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFFFFFFFFFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-9223372036854775806
-8388606
-1
-1
0
0
1
1
8388607
9223372036854775807
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-9223372036854775806
-8388606
-1
-1
0
0
1
1
8388607
9223372036854775807
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFF80000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFF);
CREATE TABLE t2 (a BIGINT);
INSERT INTO t2 VALUES (CAST(0x8000000000000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFFFFFFFFFF);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-9223372036854775806
-2147483646
-1
-1
0
0
1
1
2147483647
9223372036854775807
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-9223372036854775806
-2147483646
-1
-1
0
0
1
1
2147483647
9223372036854775807
DROP TABLE t1,t2;
#
# Unsigned Int Union
#
CREATE TABLE t1 (a TINYINT UNSIGNED);
INSERT INTO t1 VALUES (0),(1),(0xFF-2);
CREATE TABLE t2 (a SMALLINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
253
65533
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
253
65533
DROP TABLE t2;
CREATE TABLE t2 (a MEDIUMINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
253
16777213
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
253
16777213
DROP TABLE t2;
CREATE TABLE t2 (a INT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
253
4294967293
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
253
4294967293
DROP TABLE t2;
CREATE TABLE t2 (a BIGINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFFFFFFFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
253
18446744073709551613
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
253
18446744073709551613
DROP TABLE t1,t2;
CREATE TABLE t1 (a SMALLINT UNSIGNED);
INSERT INTO t1 VALUES (0),(1),(0xFFFF-2);
CREATE TABLE t2 (a MEDIUMINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
65533
16777213
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
65533
16777213
DROP TABLE t2;
CREATE TABLE t2 (a INT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
65533
4294967293
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
65533
4294967293
DROP TABLE t2;
CREATE TABLE t2 (a BIGINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFFFFFFFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
65533
18446744073709551613
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
65533
18446744073709551613
DROP TABLE t1,t2;
CREATE TABLE t1 (a MEDIUMINT UNSIGNED);
INSERT INTO t1 VALUES (0),(1),(0xFFFFFF-2);
CREATE TABLE t2 (a INT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
16777213
4294967293
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
16777213
4294967293
DROP TABLE t2;
CREATE TABLE t2 (a BIGINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFFFFFFFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
16777213
18446744073709551613
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
16777213
18446744073709551613
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT UNSIGNED);
INSERT INTO t1 VALUES (0),(1),(0xFFFFFFFF-2);
CREATE TABLE t2 (a BIGINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFFFFFFFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
0
0
1
1
4294967293
18446744073709551613
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
0
0
1
1
4294967293
18446744073709551613
DROP TABLE t1,t2;
#
# Same Size Different Signedness Int Union
#
CREATE TABLE t1 (a TINYINT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFFFF80 AS SIGNED)+2),(-1),(0),(1),(0x7F);
CREATE TABLE t2 (a TINYINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-126
-1
0
0
1
1
127
253
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-126
-1
0
0
1
1
127
253
DROP TABLE t1,t2;
CREATE TABLE t1 (a SMALLINT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFF8000 AS SIGNED)+2),(-1),(0),(1),(0x7FFF);
CREATE TABLE t2 (a SMALLINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT * FROM t2) tu ORDER BY a;
a
-32766
-1
0
0
1
1
32767
65533
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-32766
-1
0
0
1
1
32767
65533
DROP TABLE t1,t2;
CREATE TABLE t1 (a MEDIUMINT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFF800000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFF);
CREATE TABLE t2 (a MEDIUMINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-8388606
-1
0
0
1
1
8388607
16777213
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-8388606
-1
0
0
1
1
8388607
16777213
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFF80000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFF);
CREATE TABLE t2 (a INT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-2147483646
-1
0
0
1
1
2147483647
4294967293
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-2147483646
-1
0
0
1
1
2147483647
4294967293
DROP TABLE t1,t2;
CREATE TABLE t1 (a BIGINT);
INSERT INTO t1 VALUES (CAST(0x8000000000000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFFFFFFFFFF);
CREATE TABLE t2 (a BIGINT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFFFFFFFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-9223372036854775806
-1
0
0
1
1
9223372036854775807
18446744073709551613
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-9223372036854775806
-1
0
0
1
1
9223372036854775807
18446744073709551613
DROP TABLE t1,t2;
#
# Different Size Different Signedness Int Union
#
CREATE TABLE t1 (a TINYINT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFFFF80 AS SIGNED)+2),(-1),(0),(1),(0x7F);
CREATE TABLE t2 (a INT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT * FROM t2) tu ORDER BY a;
a
-126
-1
0
0
1
1
127
65533
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-126
-1
0
0
1
1
127
65533
DROP TABLE t1,t2;
CREATE TABLE t1 (a SMALLINT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFF8000 AS SIGNED)+2),(-1),(0),(1),(0x7FFF);
CREATE TABLE t2 (a INT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT * FROM t2) tu ORDER BY a;
a
-32766
-1
0
0
1
1
32767
65533
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-32766
-1
0
0
1
1
32767
65533
DROP TABLE t1,t2;
CREATE TABLE t1 (a MEDIUMINT);
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFF800000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFF);
CREATE TABLE t2 (a INT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-8388606
-1
0
0
1
1
8388607
16777213
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-8388606
-1
0
0
1
1
8388607
16777213
DROP TABLE t1,t2;
CREATE TABLE t1 (a BIGINT);
INSERT INTO t1 VALUES (CAST(0x8000000000000000 AS SIGNED)+2),(-1),(0),(1),(0x7FFFFFFFFFFFFFFF);
CREATE TABLE t2 (a INT UNSIGNED);
INSERT INTO t2 VALUES (0),(1),(0xFFFFFF-2);
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) tu ORDER BY a;
a
-9223372036854775806
-1
0
0
1
1
16777213
9223372036854775807
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
a
-9223372036854775806
-1
0
0
1
1
16777213
9223372036854775807
DROP TABLE t1,t2;
#
# Union of tables containing different string data types
#
# Remove the sorted_result MTR qualifier and add ORDER BY clause
# after MCOL-5222 is fixed
#
CREATE TABLE t1 (a CHAR(6));
INSERT INTO t1 VALUES ('t13abc'), ('t13xx'), ('common');
CREATE TABLE t2 (a VARCHAR(8));
INSERT INTO t2 VALUES ('t14abcde'), ('t14xyzzz'), ('common');
SELECT * FROM t1 UNION SELECT * FROM t2;
a
common
t13abc
t13xx
t14abcde
t14xyzzz
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT * FROM t2 UNION ALL SELECT * FROM t1;
a
common
common
t13abc
t13xx
t14abcde
t14xyzzz
EXPLAIN SELECT * FROM t2 UNION ALL SELECT * FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT '123456789000';
a
123456789000
common
t13abc
t13xx
t14abcde
t14xyzzz
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT '123456789000';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT * FROM t1 UNION SELECT '123456789000' UNION SELECT * FROM t2;
a
123456789000
common
t13abc
t13xx
t14abcde
t14xyzzz
EXPLAIN SELECT * FROM t1 UNION SELECT '123456789000' UNION SELECT * FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
DROP TABLE t1,t2;
DROP DATABASE mcs_union;