[GitHub] [spark] wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST] Port union.sql
wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST]
Port union.sql
URL: https://github.com/apache/spark/pull/25163#discussion_r308533121
##
File path: sql/core/src/test/resources/sql-tests/inputs/pgSQL/union.sql
##
@@ -0,0 +1,472 @@
+--
+-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
+--
+--
+-- UNION (also INTERSECT, EXCEPT)
+--
https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/union.sql
+--
+
+CREATE OR REPLACE TEMPORARY VIEW INT4_TBL AS SELECT * FROM
+ (VALUES (0), (123456), (-123456), (2147483647), (-2147483647))
+ AS v(f1);
+CREATE OR REPLACE TEMPORARY VIEW INT8_TBL AS SELECT * FROM
+ (VALUES
+(123, 456),
+(123, 4567890123456789),
+(4567890123456789, 123),
+(4567890123456789, 4567890123456789),
+(4567890123456789, -4567890123456789))
+ AS v(q1, q2);
+CREATE OR REPLACE TEMPORARY VIEW FLOAT8_TBL AS SELECT * FROM
+ (VALUES (0.0), (-34.84), (-1004.30),
+(CAST('-1.2345678901234e+200' AS DOUBLE)), (CAST('-1.2345678901234e-200'
AS DOUBLE)))
+ AS v(f1);
+
+-- Simple UNION constructs
+
+SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT 1 ORDER BY 1;
+
+SELECT 1 AS two UNION ALL SELECT 2;
+
+SELECT 1 AS two UNION ALL SELECT 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+-- Mixed types
+
+SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT double(1.0) ORDER BY 1;
+
+SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT double(1.0) AS two UNION ALL SELECT 1 ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT double(1.1) AS two UNION SELECT 2 UNION SELECT double(2.0) ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
+
+--
+-- Try testing from tables...
+--
+
+SELECT f1 AS five FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM FLOAT8_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM FLOAT8_TBL;
+
+SELECT f1 AS nine FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM INT4_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM INT4_TBL;
+
+SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+UNION
+SELECT f1 FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 100
+ORDER BY 1;
+
+-- [SPARK-28298] Fully support char and varchar types
+-- SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
+-- UNION
+-- SELECT f1 FROM CHAR_TBL
+-- ORDER BY 1;
+
+-- SELECT f1 AS three FROM VARCHAR_TBL
+-- UNION
+-- SELECT CAST(f1 AS varchar) FROM CHAR_TBL
+-- ORDER BY 1;
+
+-- SELECT f1 AS eight FROM VARCHAR_TBL
+-- UNION ALL
+-- SELECT f1 FROM CHAR_TBL;
+
+-- SELECT f1 AS five FROM TEXT_TBL
+-- UNION
+-- SELECT f1 FROM VARCHAR_TBL
+-- UNION
+-- SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
+-- ORDER BY 1;
+
+--
+-- INTERSECT and EXCEPT
+--
+
+SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
+
+-- Spark SQL do not support update
+-- SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY
UPDATE;
+
+-- nested cases
+(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
+
+-- exercise both hashed and sorted implementations of INTERSECT/EXCEPT
+
+-- set enable_hashagg to on;
+
+-- explain (costs off)
+-- select count(*) from
+-- ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+
+-- explain (costs off)
+-- select unique1 from tenk1 except select unique2 from tenk1 where unique2 !=
10;
+select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
+
+-- set enable_hashagg to off;
+
+-- explain (costs off)
+-- select count(*) from
+-- ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+
+-- explain (costs off)
+-- select unique1
[GitHub] [spark] wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST] Port union.sql
wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST]
Port union.sql
URL: https://github.com/apache/spark/pull/25163#discussion_r308515385
##
File path: sql/core/src/test/resources/sql-tests/inputs/pgSQL/union.sql
##
@@ -0,0 +1,471 @@
+--
+-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
+--
+--
+-- UNION (also INTERSECT, EXCEPT)
+--
https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/union.sql
+--
+
+create or replace temporary view INT4_TBL as select * from
+ (values (0), (123456), (-123456), (2147483647), (-2147483647))
+ as v(f1);
+create or replace temporary view INT8_TBL as select * from
+ (values
+(123, 456),
+(123, 4567890123456789),
+(4567890123456789, 123),
+(4567890123456789, 4567890123456789),
+(4567890123456789, -4567890123456789))
+ as v(q1, q2);
+create or replace temporary view FLOAT8_TBL as select * from
+ (values (0.0), (-34.84), (-1004.30),
+(cast('-1.2345678901234e+200' as double)), (cast('-1.2345678901234e-200'
as double)))
+ as v(f1);
+
+-- Simple UNION constructs
+
+SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT 1 ORDER BY 1;
+
+SELECT 1 AS two UNION ALL SELECT 2;
+
+SELECT 1 AS two UNION ALL SELECT 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+-- Mixed types
+
+SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT double(1.0) ORDER BY 1;
+
+SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT double(1.0) AS two UNION ALL SELECT 1 ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT double(1.1) AS two UNION SELECT 2 UNION SELECT double(2.0) ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
+
+--
+-- Try testing from tables...
+--
+
+SELECT f1 AS five FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM FLOAT8_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM FLOAT8_TBL;
+
+SELECT f1 AS nine FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM INT4_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM INT4_TBL;
+
+SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+UNION
+SELECT f1 FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 100
+ORDER BY 1;
+
+-- Skip these tests because Spark SQL can not fully support char and varchar
types.
+-- SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
+-- UNION
+-- SELECT f1 FROM CHAR_TBL
+-- ORDER BY 1;
+
+-- SELECT f1 AS three FROM VARCHAR_TBL
+-- UNION
+-- SELECT CAST(f1 AS varchar) FROM CHAR_TBL
+-- ORDER BY 1;
+
+-- SELECT f1 AS eight FROM VARCHAR_TBL
+-- UNION ALL
+-- SELECT f1 FROM CHAR_TBL;
+
+-- SELECT f1 AS five FROM TEXT_TBL
+-- UNION
+-- SELECT f1 FROM VARCHAR_TBL
+-- UNION
+-- SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
+-- ORDER BY 1;
+
+--
+-- INTERSECT and EXCEPT
+--
+
+SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
+
+-- Spark SQL do not support update
+-- SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY
UPDATE;
+
+-- nested cases
+(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
+
+-- exercise both hashed and sorted implementations of INTERSECT/EXCEPT
+
+-- set enable_hashagg to on;
+
+-- explain (costs off)
+-- select count(*) from
+-- ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+
+-- explain (costs off)
+-- select unique1 from tenk1 except select unique2 from tenk1 where unique2 !=
10;
+select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
+
+-- set enable_hashagg to off;
+
+-- explain (costs off)
+-- select count(*) from
+-- ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+
+-- explain
[GitHub] [spark] wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST] Port union.sql
wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST]
Port union.sql
URL: https://github.com/apache/spark/pull/25163#discussion_r308515399
##
File path: sql/core/src/test/resources/sql-tests/inputs/pgSQL/union.sql
##
@@ -0,0 +1,471 @@
+--
+-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
+--
+--
+-- UNION (also INTERSECT, EXCEPT)
+--
https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/union.sql
+--
+
+create or replace temporary view INT4_TBL as select * from
+ (values (0), (123456), (-123456), (2147483647), (-2147483647))
+ as v(f1);
+create or replace temporary view INT8_TBL as select * from
+ (values
+(123, 456),
+(123, 4567890123456789),
+(4567890123456789, 123),
+(4567890123456789, 4567890123456789),
+(4567890123456789, -4567890123456789))
+ as v(q1, q2);
+create or replace temporary view FLOAT8_TBL as select * from
+ (values (0.0), (-34.84), (-1004.30),
+(cast('-1.2345678901234e+200' as double)), (cast('-1.2345678901234e-200'
as double)))
+ as v(f1);
+
+-- Simple UNION constructs
+
+SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT 1 ORDER BY 1;
+
+SELECT 1 AS two UNION ALL SELECT 2;
+
+SELECT 1 AS two UNION ALL SELECT 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+-- Mixed types
+
+SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT double(1.0) ORDER BY 1;
+
+SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT double(1.0) AS two UNION ALL SELECT 1 ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT double(1.1) AS two UNION SELECT 2 UNION SELECT double(2.0) ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
+
+--
+-- Try testing from tables...
+--
+
+SELECT f1 AS five FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM FLOAT8_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM FLOAT8_TBL;
+
+SELECT f1 AS nine FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM INT4_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM INT4_TBL;
+
+SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+UNION
+SELECT f1 FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 100
+ORDER BY 1;
+
+-- Skip these tests because Spark SQL can not fully support char and varchar
types.
+-- SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
+-- UNION
+-- SELECT f1 FROM CHAR_TBL
+-- ORDER BY 1;
+
+-- SELECT f1 AS three FROM VARCHAR_TBL
+-- UNION
+-- SELECT CAST(f1 AS varchar) FROM CHAR_TBL
+-- ORDER BY 1;
+
+-- SELECT f1 AS eight FROM VARCHAR_TBL
+-- UNION ALL
+-- SELECT f1 FROM CHAR_TBL;
+
+-- SELECT f1 AS five FROM TEXT_TBL
+-- UNION
+-- SELECT f1 FROM VARCHAR_TBL
+-- UNION
+-- SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
+-- ORDER BY 1;
+
+--
+-- INTERSECT and EXCEPT
+--
+
+SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
+
+-- Spark SQL do not support update
+-- SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY
UPDATE;
+
+-- nested cases
+(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
+
+-- exercise both hashed and sorted implementations of INTERSECT/EXCEPT
+
+-- set enable_hashagg to on;
+
+-- explain (costs off)
+-- select count(*) from
+-- ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+
+-- explain (costs off)
+-- select unique1 from tenk1 except select unique2 from tenk1 where unique2 !=
10;
+select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
+
+-- set enable_hashagg to off;
+
+-- explain (costs off)
+-- select count(*) from
+-- ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+
+-- explain
[GitHub] [spark] wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST] Port union.sql
wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST]
Port union.sql
URL: https://github.com/apache/spark/pull/25163#discussion_r308512666
##
File path: sql/core/src/test/resources/sql-tests/inputs/pgSQL/union.sql
##
@@ -0,0 +1,471 @@
+--
+-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
+--
+--
+-- UNION (also INTERSECT, EXCEPT)
+--
https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/union.sql
+--
+
+create or replace temporary view INT4_TBL as select * from
+ (values (0), (123456), (-123456), (2147483647), (-2147483647))
+ as v(f1);
+create or replace temporary view INT8_TBL as select * from
+ (values
+(123, 456),
+(123, 4567890123456789),
+(4567890123456789, 123),
+(4567890123456789, 4567890123456789),
+(4567890123456789, -4567890123456789))
+ as v(q1, q2);
+create or replace temporary view FLOAT8_TBL as select * from
+ (values (0.0), (-34.84), (-1004.30),
+(cast('-1.2345678901234e+200' as double)), (cast('-1.2345678901234e-200'
as double)))
+ as v(f1);
+
+-- Simple UNION constructs
+
+SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT 1 ORDER BY 1;
+
+SELECT 1 AS two UNION ALL SELECT 2;
+
+SELECT 1 AS two UNION ALL SELECT 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+-- Mixed types
+
+SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT double(1.0) ORDER BY 1;
+
+SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT double(1.0) AS two UNION ALL SELECT 1 ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT double(1.1) AS two UNION SELECT 2 UNION SELECT double(2.0) ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
+
+--
+-- Try testing from tables...
+--
+
+SELECT f1 AS five FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM FLOAT8_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM FLOAT8_TBL;
+
+SELECT f1 AS nine FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM INT4_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM INT4_TBL;
+
+SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+UNION
+SELECT f1 FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 100
+ORDER BY 1;
+
+-- Skip these tests because Spark SQL can not fully support char and varchar
types.
Review comment:
Yes. It's https://issues.apache.org/jira/browse/SPARK-28298.
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
-
To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org
For additional commands, e-mail: reviews-h...@spark.apache.org
[GitHub] [spark] wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST] Port union.sql
wangyum commented on a change in pull request #25163: [SPARK-28406][SQL][TEST]
Port union.sql
URL: https://github.com/apache/spark/pull/25163#discussion_r303509774
##
File path: sql/core/src/test/resources/sql-tests/inputs/pgSQL/union.sql
##
@@ -0,0 +1,470 @@
+--
+-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
+--
+--
+-- UNION (also INTERSECT, EXCEPT)
+--
https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/union.sql
+--
+
+create or replace temporary view INT4_TBL as select * from
+ (values (0), (123456), (-123456), (2147483647), (-2147483647))
+ as v(f1);
+create or replace temporary view INT8_TBL as select * from
+ (values
+(123, 456),
+(123, 4567890123456789),
+(4567890123456789, 123),
+(4567890123456789, 4567890123456789),
+(4567890123456789, -4567890123456789))
+ as v(q1, q2);
+create or replace temporary view FLOAT8_TBL as select * from
+ (values (0.0), (-34.84), (-1004.30),
+(cast('-1.2345678901234e+200' as double)), (cast('-1.2345678901234e-200'
as double)))
+ as v(f1);
+
+-- Simple UNION constructs
+
+SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT 1 ORDER BY 1;
+
+SELECT 1 AS two UNION ALL SELECT 2;
+
+SELECT 1 AS two UNION ALL SELECT 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+-- Mixed types
+
+SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
+
+SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
+
+SELECT 1 AS one UNION SELECT double(1.0) ORDER BY 1;
+
+SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT double(1.0) AS two UNION ALL SELECT 1 ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
+
+SELECT double(1.1) AS two UNION SELECT 2 UNION SELECT double(2.0) ORDER BY 1;
+
+SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
+
+SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
+
+--
+-- Try testing from tables...
+--
+
+SELECT f1 AS five FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM FLOAT8_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM FLOAT8_TBL;
+
+SELECT f1 AS nine FROM FLOAT8_TBL
+UNION
+SELECT f1 FROM INT4_TBL
+ORDER BY 1;
+
+SELECT f1 AS ten FROM FLOAT8_TBL
+UNION ALL
+SELECT f1 FROM INT4_TBL;
+
+SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+UNION
+SELECT f1 FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 100
+ORDER BY 1;
+
+-- Skip these tests because Spark SQL can not fully support char and varchar
types.
+-- SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
+-- UNION
+-- SELECT f1 FROM CHAR_TBL
+-- ORDER BY 1;
+
+-- SELECT f1 AS three FROM VARCHAR_TBL
+-- UNION
+-- SELECT CAST(f1 AS varchar) FROM CHAR_TBL
+-- ORDER BY 1;
+
+-- SELECT f1 AS eight FROM VARCHAR_TBL
+-- UNION ALL
+-- SELECT f1 FROM CHAR_TBL;
+
+-- SELECT f1 AS five FROM TEXT_TBL
+-- UNION
+-- SELECT f1 FROM VARCHAR_TBL
+-- UNION
+-- SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
+-- ORDER BY 1;
+
+--
+-- INTERSECT and EXCEPT
+--
+
+SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1;
+
+SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
+
+-- Spark SQL do not support update
+-- SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY
UPDATE;
+
+-- nested cases
+(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
+
+-- exercise both hashed and sorted implementations of INTERSECT/EXCEPT
+
+-- set enable_hashagg to on;
+
+-- explain (costs off)
+-- select count(*) from
+-- ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+
+-- explain (costs off)
+-- select unique1 from tenk1 except select unique2 from tenk1 where unique2 !=
10;
+select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
+
+-- set enable_hashagg to off;
+
+-- explain (costs off)
+-- select count(*) from
+-- ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+select count(*) from
+ ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
+
+-- explain
