--source ../include/have_columnstore.inc

SET default_storage_engine=ColumnStore;

--disable_warnings
DROP DATABASE IF EXISTS mcs_union;
--enable_warnings

CREATE DATABASE mcs_union;
USE mcs_union;

--echo #
--echo # MCOL-4700 Wrong result of a UNION for INT and INT UNSIGNED
--echo #
--echo # Move the UNIONs from the subqueries to outer selects and add
--echo # ORDER BY clause after MCOL-5222 is fixed
--echo #

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;
SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu ORDER BY a;
SELECT * FROM (SELECT b FROM t1 UNION SELECT a FROM t1) tu ORDER BY b;
DROP TABLE t1;

--echo #
--echo # Multiple Columns Union
--echo #

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;
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT b FROM t1) tu ORDER BY a;
SELECT * FROM (SELECT b FROM t1 UNION ALL SELECT a FROM t1) tu ORDER BY b;
DROP TABLE t1;

--echo #
--echo # Same Int Type Union
--echo #

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;
SELECT * FROM (SELECT b FROM t1 UNION SELECT a FROM t1) tu ORDER BY b;
SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT b FROM t1 UNION SELECT a FROM t1) tu ORDER BY b;
SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t1) tu ORDER BY a;
DROP TABLE t1;

--echo #
--echo # Signed Int Union
--echo #

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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
DROP TABLE t1,t2;

--echo #
--echo # Unsigned Int Union
--echo #

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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
DROP TABLE t1,t2;

--echo #
--echo # Same Size Different Signedness Int Union
--echo #

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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
DROP TABLE t1,t2;

--echo #
--echo # Different Size Different Signedness Int Union
--echo #

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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
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;
SELECT * FROM (SELECT a FROM t2 UNION ALL SELECT a FROM t1) tu ORDER BY a;
DROP TABLE t1,t2;

--echo #
--echo # Union of tables containing different string data types
--echo #
--echo # Remove the sorted_result MTR qualifier and add ORDER BY clause
--echo # after MCOL-5222 is fixed
--echo #

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');
--sorted_result
SELECT * FROM t1 UNION SELECT * FROM t2;
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;
--sorted_result
SELECT * FROM t2 UNION ALL SELECT * FROM t1;
EXPLAIN SELECT * FROM t2 UNION ALL SELECT * FROM t1;
--sorted_result
SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT '123456789000';
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT '123456789000';
--sorted_result
SELECT * FROM t1 UNION SELECT '123456789000' UNION SELECT * FROM t2;
EXPLAIN SELECT * FROM t1 UNION SELECT '123456789000' UNION SELECT * FROM t2;
DROP TABLE t1,t2;

DROP DATABASE mcs_union;