vladimirg-db commented on code in PR #49835: URL: https://github.com/apache/spark/pull/49835#discussion_r1948752710
########## sql/core/src/test/resources/sql-tests/inputs/view-correctness.sql: ########## @@ -0,0 +1,421 @@ +-- This test suite checks the correctness of queries over views + +-- SPARK-51113, UNION + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num + UNION + SELECT 2 + UNION + SELECT 3 + UNION + SELECT 4 +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 + UNION + SELECT 2 + UNION + SELECT 3 + UNION + SELECT 4 +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 'a' AS a + UNION + SELECT 'b' + UNION + SELECT 'c' + UNION + SELECT 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 'a' + UNION + SELECT 'b' + UNION + SELECT 'c' + UNION + SELECT 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1, 'a' + UNION + SELECT 2, 'b' + UNION + SELECT 3, 'c' + UNION + SELECT 4, 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num, 'a' + UNION + SELECT 2, 'b' + UNION + SELECT 3, 'c' + UNION + SELECT 4, 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1, 'a' AS a + UNION + SELECT 2, 'b' + UNION + SELECT 3, 'c' + UNION + SELECT 4, 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num, 'a' AS a + UNION + SELECT 2, 'b' + UNION + SELECT 3, 'c' + UNION + SELECT 4, 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +-- SPARK-51113, UNION ALL + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num + UNION ALL + SELECT 2 + UNION ALL + SELECT 3 + UNION ALL + SELECT 4 +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 + UNION ALL + SELECT 2 + UNION ALL + SELECT 3 + UNION ALL + SELECT 4 +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 'a' AS a + UNION ALL + SELECT 'b' + UNION ALL + SELECT 'c' + UNION ALL + SELECT 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 'a' + UNION ALL + SELECT 'b' + UNION ALL + SELECT 'c' + UNION ALL + SELECT 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1, 'a' + UNION ALL + SELECT 2, 'b' + UNION ALL + SELECT 3, 'c' + UNION ALL + SELECT 4, 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num, 'a' + UNION ALL + SELECT 2, 'b' + UNION ALL + SELECT 3, 'c' + UNION ALL + SELECT 4, 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1, 'a' AS a + UNION ALL + SELECT 2, 'b' + UNION ALL + SELECT 3, 'c' + UNION ALL + SELECT 4, 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num, 'a' AS a + UNION ALL + SELECT 2, 'b' + UNION ALL + SELECT 3, 'c' + UNION ALL + SELECT 4, 'd' +; +SELECT * FROM v1; +DROP VIEW v1; + +-- SPARK-51113, EXCEPT + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num + EXCEPT + SELECT 2 + EXCEPT + SELECT 1 + EXCEPT + SELECT 2 +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 + EXCEPT + SELECT 2 + EXCEPT + SELECT 1 + EXCEPT + SELECT 2 +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 'a' AS a + EXCEPT + SELECT 'b' + EXCEPT + SELECT 'a' + EXCEPT + SELECT 'b' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 'a' + EXCEPT + SELECT 'b' + EXCEPT + SELECT 'a' + EXCEPT + SELECT 'b' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1, 'a' + EXCEPT + SELECT 2, 'b' + EXCEPT + SELECT 1, 'a' + EXCEPT + SELECT 2, 'b' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num, 'a' + EXCEPT + SELECT 2, 'b' + EXCEPT + SELECT 1, 'a' + EXCEPT + SELECT 2, 'b' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1, 'a' AS a + EXCEPT + SELECT 2, 'b' + EXCEPT + SELECT 1, 'a' + EXCEPT + SELECT 2, 'b' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num, 'a' AS a + EXCEPT + SELECT 2, 'b' + EXCEPT + SELECT 1, 'a' + EXCEPT + SELECT 2, 'b' +; +SELECT * FROM v1; +DROP VIEW v1; + +-- SPARK-51113, INTERSECT + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 AS num + INTERSECT + SELECT 1 + INTERSECT + SELECT 2 + INTERSECT + SELECT 2 +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 1 + INTERSECT + SELECT 1 + INTERSECT + SELECT 2 + INTERSECT + SELECT 2 +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 'a' AS a + INTERSECT + SELECT 'a' + INTERSECT + SELECT 'b' + INTERSECT + SELECT 'b' +; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY VIEW v1 AS + SELECT 'a' + INTERSECT + SELECT 'a' + INTERSECT + SELECT 'b' + INTERSECT + SELECT 'b' Review Comment: What I mean by that is the issue is not specific to view and UNION. I've found that it affects EXECUTE IMMEDIATE, and EXCEPT/INTERSECT, and maybe even something else. -- 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. To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org For additional commands, e-mail: reviews-h...@spark.apache.org
