dongjoon-hyun commented on a change in pull request #26360: [SPARK-29714][SQL][TESTS] Port insert.sql URL: https://github.com/apache/spark/pull/26360#discussion_r342397723
########## File path: sql/core/src/test/resources/sql-tests/inputs/postgreSQL/insert.sql ########## @@ -0,0 +1,652 @@ +-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group +-- +-- INSERT +-- https://github.com/postgres/postgres/blob/REL_12_STABLE/src/test/regress/sql/insert.sql + +-- +-- insert with DEFAULT in the target_list +-- +-- [SPARK-19842] Informational Referential Integrity Constraints Support in Spark +-- [SPARK-29119] DEFAULT option is not supported in Spark +create table inserttest (col1 int, col2 int /* NOT NULL */, col3 string /* default 'testing' */) using parquet; +-- [SPARK-29119] DEFAULT option is not supported in Spark +-- [SPARK-20845] Support specification of column names in INSERT INTO +-- Skip a test below because the PK constraint is violated and the query fails in PostgreSQL +-- insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT); +-- insert into inserttest (col2, col3) values (3, DEFAULT); +insert into inserttest values (NULL, 3, 'testing'); +-- insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT); +insert into inserttest values (NULL, 5, 'testing'); +-- insert into inserttest values (DEFAULT, 5, 'test'); +insert into inserttest values (NULL, 5, 'test'); +-- insert into inserttest values (DEFAULT, 7); +insert into inserttest values (NULL, 7, 'testing'); + +select * from inserttest; + +-- +-- insert with similar expression / target_list values (all fail) +-- +-- [SPARK-20845] Support specification of column names in INSERT INTO +-- [SPARK-29119] DEFAULT option is not supported in Spark +-- insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT); +-- insert into inserttest (col1, col2, col3) values (1, 2); +-- insert into inserttest (col1) values (1, 2); +-- insert into inserttest (col1) values (DEFAULT, DEFAULT); + +-- select * from inserttest; + +-- +-- VALUES test +-- +-- [SPARK-29119] DEFAULT option is not supported in Spark +-- [SPARK-29715] Support SELECT statements in VALUES of INSERT INTO +-- insert into inserttest values(10, 20, '40'), (-1, 2, 'testing' /* DEFAULT */), +-- ((select 2), (select i from (values(3)) as foo (i)), 'values are fun!'); + +-- select * from inserttest; + +-- +-- TOASTed value test +-- +insert into inserttest values(30, 50, repeat('x', 10000)); + +select col1, col2, char_length(col3) from inserttest; + +drop table inserttest; + +-- +-- check indirection (field/array assignment), cf bug #14265 +-- +-- these tests are aware that transformInsertStmt has 3 separate code paths +-- + +-- [SPARK-29716] Support [CREATE|DROP] TYPE +-- create type insert_test_type as (if1 int, if2 array<string>); + +-- create table inserttest (f1 int, f2 int[], +-- f3 insert_test_type, f4 insert_test_type[]); +-- +-- insert into inserttest (f2[1], f2[2]) values (1,2); +-- insert into inserttest (f2[1], f2[2]) values (3,4), (5,6); +-- insert into inserttest (f2[1], f2[2]) select 7,8; +-- insert into inserttest (f2[1], f2[2]) values (1,default); -- not supported +-- +-- insert into inserttest (f3.if1, f3.if2) values (1,array['foo']); +-- insert into inserttest (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); +-- insert into inserttest (f3.if1, f3.if2) select 3, '{baz,quux}'; +-- insert into inserttest (f3.if1, f3.if2) values (1,default); -- not supported +-- +-- insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); +-- insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); +-- insert into inserttest (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; +-- +-- insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'); +-- insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'), ('baz', 'quux'); +-- insert into inserttest (f4[1].if2[1], f4[1].if2[2]) select 'bear', 'beer'; +-- +-- select * from inserttest; + +-- also check reverse-listing +-- create table inserttest2 (f1 bigint, f2 string); +-- [SPARK-29717] Support [CREATE|DROP] RULE - define a new plan rewrite rule +-- create rule irule1 as on insert to inserttest2 do also +-- insert into inserttest (f3.if2[1], f3.if2[2]) +-- values (new.f1,new.f2); +-- create rule irule2 as on insert to inserttest2 do also +-- insert into inserttest (f4[1].if1, f4[1].if2[2]) +-- values (1,'fool'),(new.f1,new.f2); +-- create rule irule3 as on insert to inserttest2 do also +-- insert into inserttest (f4[1].if1, f4[1].if2[2]) +-- select new.f1, new.f2; +-- \d+ inserttest2 + +-- drop table inserttest2; +-- drop table inserttest; +-- [SPARK-29716] Support [CREATE|DROP] TYPE +-- drop type insert_test_type; + +-- direct partition inserts should check partition bound constraint +-- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE +-- create table range_parted ( +-- a string, +-- b int +-- ) partition by range (a, (b+0)); + +-- no partitions, so fail +-- insert into range_parted values ('a', 11); + +-- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE +-- create table part1 partition of range_parted for values from ('a', 1) to ('a', 10); +-- create table part2 partition of range_parted for values from ('a', 10) to ('a', 20); +-- create table part3 partition of range_parted for values from ('b', 1) to ('b', 10); +-- create table part4 partition of range_parted for values from ('b', 10) to ('b', 20); + +-- fail +-- insert into part1 values ('a', 11); +-- insert into part1 values ('b', 1); +-- ok +-- insert into part1 values ('a', 1); +-- fail +-- insert into part4 values ('b', 21); +-- insert into part4 values ('a', 10); +-- ok +-- insert into part4 values ('b', 10); + +-- fail (partition key a has a NOT NULL constraint) +-- insert into part1 values (null); +-- fail (expression key (b+0) cannot be null either) +-- insert into part1 values (1); + +-- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE +-- create table list_parted ( +-- a text, +-- b int +-- ) partition by list (lower(a)); +-- create table part_aa_bb partition of list_parted FOR VALUES IN ('aa', 'bb'); +-- create table part_cc_dd partition of list_parted FOR VALUES IN ('cc', 'dd'); +-- create table part_null partition of list_parted FOR VALUES IN (null); + +-- fail +-- insert into part_aa_bb values ('cc', 1); +-- insert into part_aa_bb values ('AAa', 1); +-- insert into part_aa_bb values (null); +-- ok +-- insert into part_cc_dd values ('cC', 1); +-- insert into part_null values (null, 0); + +-- check in case of multi-level partitioned table +-- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE +-- create table part_ee_ff partition of list_parted for values in ('ee', 'ff') partition by range (b); +-- create table part_ee_ff1 partition of part_ee_ff for values from (1) to (10); +-- create table part_ee_ff2 partition of part_ee_ff for values from (10) to (20); + +-- test default partition +-- create table part_default partition of list_parted default; Review comment: Add the following before line 165? ``` -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE ``` ---------------------------------------------------------------- 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: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
