maropu commented on a change in pull request #25163: [SPARK-28406][SQL][TEST] Port union.sql URL: https://github.com/apache/spark/pull/25163#discussion_r308535745
########## 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 1000000 +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 from tenk1 except select unique2 from tenk1 where unique2 != 10; +select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; + +-- reset enable_hashagg; + +-- +-- Mixed types +-- + +SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl ORDER BY 1; + +SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1; + +-- +-- Operator precedence and (((((extra))))) parentheses +-- + +SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl ORDER BY 1; + +SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) ORDER BY 1; + +(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl ORDER BY 1))) UNION ALL SELECT q2 FROM int8_tbl; + +SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; + +SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1))); + +(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; + +-- +-- Subqueries with ORDER BY & LIMIT clauses +-- + +-- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT +SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl +ORDER BY q2,q1; + +-- This should fail, because q2 isn't a name of an EXCEPT output column +SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1; + +-- But this should work: +SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1; + +-- +-- New syntaxes (7.1) permit new tests +-- + +(((((select * from int8_tbl))))); + +-- [SPARK-28557] Support empty select list +-- +-- Check behavior with empty select list (allowed since 9.4) +-- + +-- select union select; +-- select intersect select; +-- select except select; + +-- check hashed implementation +-- set enable_hashagg = true; +-- set enable_sort = false; + +-- explain (costs off) +-- select from generate_series(1,5) union select from generate_series(1,3); +-- explain (costs off) +-- select from generate_series(1,5) intersect select from generate_series(1,3); + +-- [SPARK-28409] SELECT FROM syntax +-- [SPARK-27767] Built-in function: generate_series +select * from range(1,5) union select * from range(1,3); +select * from range(1,6) union all select * from range(1,4); +select * from range(1,6) intersect select * from range(1,4); +select * from range(1,6) intersect all select * from range(1,4); +select * from range(1,6) except select * from range(1,4); +select * from range(1,6) except all select * from range(1,4); + +-- check sorted implementation +-- set enable_hashagg = false; +-- set enable_sort = true; + +-- explain (costs off) +-- select from generate_series(1,5) union select from generate_series(1,3); +-- explain (costs off) +-- select from generate_series(1,5) intersect select from generate_series(1,3); + +select * from range(1,6) union select * from range(1,4); +select * from range(1,6) union all select * from range(1,4); +select * from range(1,6) intersect select * from range(1,4); +select * from range(1,6) intersect all select * from range(1,4); +select * from range(1,6) except select * from range(1,4); +select * from range(1,6) except all select * from range(1,4); + +-- reset enable_hashagg; +-- reset enable_sort; + +-- +-- Check handling of a case with unknown constants. We don't guarantee +-- an undecorated constant will work in all cases, but historically this +-- usage has worked, so test we don't break it. +-- + +-- SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a Review comment: Ur, I see. ---------------------------------------------------------------- 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
