You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-11-03 17:13:17 +03:00
923 lines
15 KiB
Plaintext
923 lines
15 KiB
Plaintext
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;
|