dongjoon-hyun commented on a change in pull request #26290: [SPARK-29120][SQL][TESTS] Port create_view.sql URL: https://github.com/apache/spark/pull/26290#discussion_r340494736
########## File path: sql/core/src/test/resources/sql-tests/inputs/postgreSQL/create_view.sql ########## @@ -0,0 +1,778 @@ +-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group +-- +-- CREATE VIEW +-- https://github.com/postgres/postgres/blob/REL_12_STABLE/src/test/regress/sql/create_view.sql + +-- Spark doesn't support geometric types +-- CREATE VIEW street AS +-- SELECT r.name, r.thepath, c.cname AS cname +-- FROM ONLY road r, real_city c +-- WHERE c.outline ## r.thepath; + +-- Spark doesn't support geometric types +-- CREATE VIEW iexit AS +-- SELECT ih.name, ih.thepath, +-- interpt_pp(ih.thepath, r.thepath) AS exit +-- FROM ihighway ih, ramp r +-- WHERE ih.thepath ## r.thepath; + +CREATE TABLE emp ( + name string, + age int, + -- Spark doesn't support a geometric type `point` + -- location point + salary int, + manager string +) USING parquet; + +CREATE VIEW toyemp AS + SELECT name, age, /* location ,*/ 12*salary AS annualsal + FROM emp; + +-- Spark doesn't support the COMMENT clause that is not defined in the SQL standard +-- Test comments +-- COMMENT ON VIEW noview IS 'no view'; +-- COMMENT ON VIEW toyemp IS 'is a view'; +-- COMMENT ON VIEW toyemp IS NULL; + +DROP VIEW toyemp; +DROP TABLE emp; + +-- These views are left around mainly to exercise special cases in pg_dump. + +-- [SPARK-19842] Informational Referential Integrity Constraints Support in Spark +-- CREATE TABLE view_base_table (key int PRIMARY KEY, data varchar(20)); +-- +-- CREATE VIEW key_dependent_view AS +-- SELECT * FROM view_base_table GROUP BY key; +-- +-- ALTER TABLE view_base_table DROP CONSTRAINT view_base_table_pkey; -- fails + +-- CREATE VIEW key_dependent_view_no_cols AS +-- SELECT FROM view_base_table GROUP BY key HAVING length(data) > 0; + +-- +-- CREATE OR REPLACE VIEW +-- + +CREATE TABLE viewtest_tbl (a int, b int) using parquet; +-- [SPARK-29386] Copy data between a file and a table +-- COPY viewtest_tbl FROM stdin; +-- 5 10 +-- 10 15 +-- 15 20 +-- 20 25 +-- \. +INSERT INTO viewtest_tbl VALUES (5, 10), (10, 15), (15, 20), (20, 25); + +CREATE OR REPLACE VIEW viewtest AS + SELECT * FROM viewtest_tbl; + +CREATE OR REPLACE VIEW viewtest AS + SELECT * FROM viewtest_tbl WHERE a > 10; + +SELECT * FROM viewtest; + +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC; + +SELECT * FROM viewtest; + +-- should fail +-- Spark can accept the DDL query below +CREATE OR REPLACE VIEW viewtest AS + SELECT a FROM viewtest_tbl WHERE a <> 20; + +-- should fail +-- Spark can accept the DDL query below +CREATE OR REPLACE VIEW viewtest AS + SELECT 1, * FROM viewtest_tbl; + +-- should fail +-- Spark can accept the DDL query below +CREATE OR REPLACE VIEW viewtest AS + SELECT a, decimal(b) FROM viewtest_tbl; + +-- should work +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b, 0 AS c FROM viewtest_tbl; + +DROP VIEW viewtest; +DROP TABLE viewtest_tbl; + +-- tests for temporary views + +-- Spark doesn't support the cascaded syntax below in `CREATE SCHEMA` +-- CREATE SCHEMA temp_view_test +-- CREATE TABLE base_table (a int, id int) using parquet +-- CREATE TABLE base_table2 (a int, id int) using parquet; +CREATE SCHEMA temp_view_test; +CREATE TABLE temp_view_test.base_table (a int, id int) using parquet; +CREATE TABLE temp_view_test.base_table2 (a int, id int) using parquet; + +-- Replace SET with USE +-- SET search_path TO temp_view_test, public; +USE temp_view_test; + +-- Since Spark doesn't support CREATE TEMPORARY TABLE, we used CREATE TEMPORARY VIEW instead +-- CREATE TEMPORARY TABLE temp_table (a int, id int); +CREATE TEMPORARY VIEW temp_table AS SELECT * FROM VALUES + (1, 1) as temp_table(a, id); + +-- should be created in temp_view_test schema +CREATE VIEW v1 AS SELECT * FROM base_table; +DESC TABLE EXTENDED v1; +-- should be created in temp object schema +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW v1_temp AS SELECT * FROM temp_table; +-- should be created in temp object schema +CREATE TEMPORARY VIEW v2_temp AS SELECT * FROM base_table; +DESC TABLE EXTENDED v2_temp; +-- should be created in temp_views schema +CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table; +DESC TABLE EXTENDED temp_view_test.v2; +-- should fail +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table; +-- should fail +-- Spark doesn't support the cascaded syntax below in `CREATE SCHEMA` +-- CREATE SCHEMA test_view_schema +-- CREATE TEMP VIEW testview AS SELECT 1; + +-- joins: if any of the join relations are temporary, the view +-- should also be temporary + +-- should be non-temp +CREATE VIEW v3 AS + SELECT t1.a AS t1_a, t2.a AS t2_a + FROM base_table t1, base_table2 t2 + WHERE t1.id = t2.id; +DESC TABLE EXTENDED v3; +-- should be temp (one join rel is temp) +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW v4_temp AS + SELECT t1.a AS t1_a, t2.a AS t2_a + FROM base_table t1, temp_table t2 + WHERE t1.id = t2.id; +-- should be temp +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW v5_temp AS + SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a + FROM base_table t1, base_table2 t2, temp_table t3 + WHERE t1.id = t2.id and t2.id = t3.id; + +-- subqueries +CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2); +DESC TABLE EXTENDED v4; +CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2; +DESC TABLE EXTENDED v5; +CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2); +DESC TABLE EXTENDED v6; +CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2); +DESC TABLE EXTENDED v7; +CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1); +DESC TABLE EXTENDED v8; + +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table); +CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2; +-- [SPARK-29630] Not allowed to create a permanent view by referencing a temporary view in EXISTS +CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table); +CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table); + +-- a view should also be temporary if it references a temporary view +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW v10_temp AS SELECT * FROM v7_temp; +CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2; +CREATE VIEW v12_temp AS SELECT true FROM v11_temp; + +-- [SPARK-27764] Support ANSI SQL CREATE SEQUENCE +-- a view should also be temporary if it references a temporary sequence +-- CREATE SEQUENCE seq1; +-- CREATE TEMPORARY SEQUENCE seq1_temp; +-- CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1; +-- CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp; + +-- Skip the tests below because of PostgreSQL specific cases +-- SELECT relname FROM pg_class +-- WHERE relname LIKE 'v_' +-- AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test') +-- ORDER BY relname; +-- SELECT relname FROM pg_class +-- WHERE relname LIKE 'v%' +-- AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%') +-- ORDER BY relname; + +CREATE SCHEMA testviewschm2; +-- Replace SET with USE +-- SET search_path TO testviewschm2, public; +USE testviewschm2; + +CREATE TABLE t1 (num int, name string) using parquet; +CREATE TABLE t2 (num2 int, value string) using parquet; +-- Since Spark doesn't support CREATE TEMPORARY TABLE, we used CREATE TEMPORARY VIEW instead +-- CREATE TEMPORARY TABLE tt (num2 int, value string); +CREATE TEMPORARY VIEW tt AS SELECT * FROM VALUES + (1, 'a') AS tt(num2, value); + +CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2; +DESC TABLE EXTENDED nontemp1; +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt; +CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2; +DESC TABLE EXTENDED nontemp2; +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2; +CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2; +DESC TABLE EXTENDED nontemp3; +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2; +CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx'; +DESC TABLE EXTENDED nontemp4; +-- [SPARK-29628] Forcibly create a temporary view in CREATE VIEW if referencing a temporary view +CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx'; + +-- Skip the tests below because of PostgreSQL specific cases +-- SELECT relname FROM pg_class +-- WHERE relname LIKE 'nontemp%' +-- AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2') +-- ORDER BY relname; +-- SELECT relname FROM pg_class +-- WHERE relname LIKE 'temporal%' +-- AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%') +-- ORDER BY relname; + +CREATE TABLE tbl1 ( a int, b int) using parquet; +CREATE TABLE tbl2 (c int, d int) using parquet; +CREATE TABLE tbl3 (e int, f int) using parquet; +CREATE TABLE tbl4 (g int, h int) using parquet; +-- Since Spark doesn't support CREATE TEMPORARY TABLE, we used CREATE TEMPORARY VIEW instead +-- CREATE TEMPORARY TABLE tmptbl (i int, j int); +CREATE TEMPORARY VIEW tmptbl AS SELECT * FROM VALUES + (1, 1) AS temptbl(i, j); + +--Should be in testviewschm2 +CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a +BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) +AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f); +DESC TABLE EXTENDED pubview; + +-- Skip the test below because of PostgreSQL specific cases +-- SELECT count(*) FROM pg_class where relname = 'pubview' +-- AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2'); + +--Should be in temp object schema +CREATE VIEW mytempview AS SELECT * FROM tbl1 WHERE tbl1.a +BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) +AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f) +AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j); +DESC TABLE EXTENDED mytempview; + +-- Skip the test below because of PostgreSQL specific cases +-- SELECT count(*) FROM pg_class where relname LIKE 'mytempview' +-- And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%'); + +-- +-- CREATE VIEW and WITH(...) clause +-- CREATE VIEW mysecview1 +-- AS SELECT * FROM tbl1 WHERE a = 0; +-- +-- Skip the tests below because Spark doesn't support `WITH options` +-- CREATE VIEW mysecview2 WITH (security_barrier=true) +-- AS SELECT * FROM tbl1 WHERE a > 0; +-- CREATE VIEW mysecview3 WITH (security_barrier=false) +-- AS SELECT * FROM tbl1 WHERE a < 0; +-- CREATE VIEW mysecview4 WITH (security_barrier) +-- AS SELECT * FROM tbl1 WHERE a <> 0; +-- Spark cannot support options in WITH clause +-- CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error +-- AS SELECT * FROM tbl1 WHERE a > 100; +-- CREATE VIEW mysecview6 WITH (invalid_option) -- Error +-- AS SELECT * FROM tbl1 WHERE a < 100; +-- Skip the test below because of PostgreSQL specific cases +-- SELECT relname, relkind, reloptions FROM pg_class +-- WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass, +-- 'mysecview3'::regclass, 'mysecview4'::regclass) +-- ORDER BY relname; + +-- CREATE OR REPLACE VIEW mysecview1 +-- AS SELECT * FROM tbl1 WHERE a = 256; +-- CREATE OR REPLACE VIEW mysecview2 +-- AS SELECT * FROM tbl1 WHERE a > 256; +-- CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true) +-- AS SELECT * FROM tbl1 WHERE a < 256; +-- CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false) +-- AS SELECT * FROM tbl1 WHERE a <> 256; +-- Skip the test below because of PostgreSQL specific cases +-- SELECT relname, relkind, reloptions FROM pg_class +-- WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass, +-- 'mysecview3'::regclass, 'mysecview4'::regclass) +-- ORDER BY relname; + +-- Check that unknown literals are converted to "text" in CREATE VIEW, +-- so that we don't end up with unknown-type columns. + +-- Skip the tests below because of PostgreSQL specific cases +-- CREATE VIEW unspecified_types AS +-- SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n; +-- \d+ unspecified_types +-- SELECT * FROM unspecified_types; + +-- This test checks that proper typmods are assigned in a multi-row VALUES + +CREATE VIEW tt1 AS + SELECT * FROM ( + VALUES + ('abc', '0123456789', 42, 'abcd'), + ('0123456789', 'abc', 42.12, 'abc') + ) vv(a,b,c,d); +-- Skip the test below because of PostgreSQL specific cases Review comment: For consistency, shall we do the following? ``` -- Replace the PostgreSQL meta command `\d` with `DESC` ``` ---------------------------------------------------------------- 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
