YQL-19206 Move contrib/ydb/library/yql/tests/postgresql -> yql/essentials/tests/postgresql

commit_hash:46fdf59714b20cf2b61233a06e58365227d3c8b2

1 files changed, 222 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/original/cases/select_into.out b/yql/essentials/tests/postgresql/original/cases/select_into.out
new file mode 100644
index 0000000000..43b8209d22
--- /dev/null
+++ b/yql/essentials/tests/postgresql/original/cases/select_into.out
@@ -0,0 +1,222 @@
+--
+-- SELECT_INTO
+--
+SELECT *
+   INTO TABLE sitmp1
+   FROM onek
+   WHERE onek.unique1 < 2;
+DROP TABLE sitmp1;
+SELECT *
+   INTO TABLE sitmp1
+   FROM onek2
+   WHERE onek2.unique1 < 2;
+DROP TABLE sitmp1;
+--
+-- SELECT INTO and INSERT permission, if owner is not allowed to insert.
+--
+CREATE SCHEMA selinto_schema;
+CREATE USER regress_selinto_user;
+ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
+	  REVOKE INSERT ON TABLES FROM regress_selinto_user;
+GRANT ALL ON SCHEMA selinto_schema TO public;
+SET SESSION AUTHORIZATION regress_selinto_user;
+-- WITH DATA, passes.
+CREATE TABLE selinto_schema.tbl_withdata1 (a)
+  AS SELECT generate_series(1,3) WITH DATA;
+INSERT INTO selinto_schema.tbl_withdata1 VALUES (4);
+ERROR:  permission denied for table tbl_withdata1
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
+  SELECT generate_series(1,3) WITH DATA;
+              QUERY PLAN              
+--------------------------------------
+ ProjectSet (actual rows=3 loops=1)
+   ->  Result (actual rows=1 loops=1)
+(2 rows)
+
+-- WITH NO DATA, passes.
+CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
+  SELECT generate_series(1,3) WITH NO DATA;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE selinto_schema.tbl_nodata2 (a) AS
+  SELECT generate_series(1,3) WITH NO DATA;
+          QUERY PLAN           
+-------------------------------
+ ProjectSet (never executed)
+   ->  Result (never executed)
+(2 rows)
+
+-- EXECUTE and WITH DATA, passes.
+PREPARE data_sel AS SELECT generate_series(1,3);
+CREATE TABLE selinto_schema.tbl_withdata3 (a) AS
+  EXECUTE data_sel WITH DATA;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE selinto_schema.tbl_withdata4 (a) AS
+  EXECUTE data_sel WITH DATA;
+              QUERY PLAN              
+--------------------------------------
+ ProjectSet (actual rows=3 loops=1)
+   ->  Result (actual rows=1 loops=1)
+(2 rows)
+
+-- EXECUTE and WITH NO DATA, passes.
+CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
+  EXECUTE data_sel WITH NO DATA;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
+  EXECUTE data_sel WITH NO DATA;
+          QUERY PLAN           
+-------------------------------
+ ProjectSet (never executed)
+   ->  Result (never executed)
+(2 rows)
+
+RESET SESSION AUTHORIZATION;
+ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
+	  GRANT INSERT ON TABLES TO regress_selinto_user;
+SET SESSION AUTHORIZATION regress_selinto_user;
+RESET SESSION AUTHORIZATION;
+DEALLOCATE data_sel;
+DROP SCHEMA selinto_schema CASCADE;
+NOTICE:  drop cascades to 8 other objects
+DETAIL:  drop cascades to table selinto_schema.tbl_withdata1
+drop cascades to table selinto_schema.tbl_withdata2
+drop cascades to table selinto_schema.tbl_nodata1
+drop cascades to table selinto_schema.tbl_nodata2
+drop cascades to table selinto_schema.tbl_withdata3
+drop cascades to table selinto_schema.tbl_withdata4
+drop cascades to table selinto_schema.tbl_nodata3
+drop cascades to table selinto_schema.tbl_nodata4
+DROP USER regress_selinto_user;
+-- Tests for WITH NO DATA and column name consistency
+CREATE TABLE ctas_base (i int, j int);
+INSERT INTO ctas_base VALUES (1, 2);
+CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base; -- Error
+ERROR:  too many column names were specified
+CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base WITH NO DATA; -- Error
+ERROR:  too many column names were specified
+CREATE TABLE ctas_nodata (ii, jj) AS SELECT i, j FROM ctas_base; -- OK
+CREATE TABLE ctas_nodata_2 (ii, jj) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
+CREATE TABLE ctas_nodata_3 (ii) AS SELECT i, j FROM ctas_base; -- OK
+CREATE TABLE ctas_nodata_4 (ii) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
+SELECT * FROM ctas_nodata;
+ ii | jj 
+----+----
+  1 |  2
+(1 row)
+
+SELECT * FROM ctas_nodata_2;
+ ii | jj 
+----+----
+(0 rows)
+
+SELECT * FROM ctas_nodata_3;
+ ii | j 
+----+---
+  1 | 2
+(1 row)
+
+SELECT * FROM ctas_nodata_4;
+ ii | j 
+----+---
+(0 rows)
+
+DROP TABLE ctas_base;
+DROP TABLE ctas_nodata;
+DROP TABLE ctas_nodata_2;
+DROP TABLE ctas_nodata_3;
+DROP TABLE ctas_nodata_4;
+--
+-- CREATE TABLE AS/SELECT INTO as last command in a SQL function
+-- have been known to cause problems
+--
+CREATE FUNCTION make_table() RETURNS VOID
+AS $$
+  CREATE TABLE created_table AS SELECT * FROM int8_tbl;
+$$ LANGUAGE SQL;
+SELECT make_table();
+ make_table 
+------------
+ 
+(1 row)
+
+SELECT * FROM created_table;
+        q1        |        q2         
+------------------+-------------------
+              123 |               456
+              123 |  4567890123456789
+ 4567890123456789 |               123
+ 4567890123456789 |  4567890123456789
+ 4567890123456789 | -4567890123456789
+(5 rows)
+
+-- Try EXPLAIN ANALYZE SELECT INTO and EXPLAIN ANALYZE CREATE TABLE AS
+-- WITH NO DATA, but hide the outputs since they won't be stable.
+DO $$
+BEGIN
+	EXECUTE 'EXPLAIN ANALYZE SELECT * INTO TABLE easi FROM int8_tbl';
+	EXECUTE 'EXPLAIN ANALYZE CREATE TABLE easi2 AS SELECT * FROM int8_tbl WITH NO DATA';
+END$$;
+DROP TABLE created_table;
+DROP TABLE easi, easi2;
+--
+-- Disallowed uses of SELECT ... INTO.  All should fail
+--
+DECLARE foo CURSOR FOR SELECT 1 INTO b;
+ERROR:  SELECT ... INTO is not allowed here
+LINE 1: DECLARE foo CURSOR FOR SELECT 1 INTO b;
+                                             ^
+COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob';
+ERROR:  COPY (SELECT INTO) is not supported
+SELECT * FROM (SELECT 1 INTO f) bar;
+ERROR:  SELECT ... INTO is not allowed here
+LINE 1: SELECT * FROM (SELECT 1 INTO f) bar;
+                                     ^
+CREATE VIEW foo AS SELECT 1 INTO b;
+ERROR:  views must not contain SELECT INTO
+INSERT INTO b SELECT 1 INTO f;
+ERROR:  SELECT ... INTO is not allowed here
+LINE 1: INSERT INTO b SELECT 1 INTO f;
+                                    ^
+-- Test CREATE TABLE AS ... IF NOT EXISTS
+CREATE TABLE ctas_ine_tbl AS SELECT 1;
+CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
+ERROR:  relation "ctas_ine_tbl" already exists
+CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
+NOTICE:  relation "ctas_ine_tbl" already exists, skipping
+CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
+ERROR:  relation "ctas_ine_tbl" already exists
+CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
+NOTICE:  relation "ctas_ine_tbl" already exists, skipping
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
+ERROR:  relation "ctas_ine_tbl" already exists
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
+NOTICE:  relation "ctas_ine_tbl" already exists, skipping
+ QUERY PLAN 
+------------
+(0 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
+ERROR:  relation "ctas_ine_tbl" already exists
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
+NOTICE:  relation "ctas_ine_tbl" already exists, skipping
+ QUERY PLAN 
+------------
+(0 rows)
+
+PREPARE ctas_ine_query AS SELECT 1 / 0;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
+ERROR:  relation "ctas_ine_tbl" already exists
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+  CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
+NOTICE:  relation "ctas_ine_tbl" already exists, skipping
+ QUERY PLAN 
+------------
+(0 rows)
+
+DROP TABLE ctas_ine_tbl;