Repository: incubator-hawq Updated Branches: refs/heads/master 3b54677d9 -> 8b3934fdb
HAWQ-807. Refactor feature test for gpcopy with new framework. Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/8b3934fd Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/8b3934fd Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/8b3934fd Branch: refs/heads/master Commit: 8b3934fdbc61618b2fce11226b171c3ca2da93e2 Parents: 3b54677 Author: xunzhang <xunzhang...@gmail.com> Authored: Sat Jul 9 12:56:22 2016 +0800 Committer: xunzhang <xunzhang...@gmail.com> Committed: Sat Jul 9 12:56:22 2016 +0800 ---------------------------------------------------------------------- src/test/feature/UtilityCommand/ans/gpcopy.ans | 574 ++++++++++++++++++++ src/test/feature/UtilityCommand/sql/gpcopy.sql | 572 +++++++++++++++++++ src/test/feature/UtilityCommand/test_cmd.cpp | 15 + src/test/regress/expected/gpcopy.out | 513 ----------------- src/test/regress/known_good_schedule | 1 - src/test/regress/parallel_schedule | 1 - src/test/regress/serial_schedule | 1 - src/test/regress/sql/gpcopy.sql | 566 ------------------- 8 files changed, 1161 insertions(+), 1082 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b3934fd/src/test/feature/UtilityCommand/ans/gpcopy.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/UtilityCommand/ans/gpcopy.ans b/src/test/feature/UtilityCommand/ans/gpcopy.ans new file mode 100644 index 0000000..c0efb23 --- /dev/null +++ b/src/test/feature/UtilityCommand/ans/gpcopy.ans @@ -0,0 +1,574 @@ +-- start_ignore +SET SEARCH_PATH=TestCommand_TestCOPY; +SET +-- end_ignore +-- +-- This test suite tests COPY code that is unique to greenplum db. +-- +-- ###################################################### +-- different distribution columns and distribution types +-- ###################################################### +CREATE TABLE copy_regression_hash1(a text, b text, c text) distributed by (b); +CREATE TABLE +CREATE TABLE copy_regression_hash2(a text, b text, c text) distributed by (a,c); +CREATE TABLE +CREATE TABLE copy_regression_hash3(a varchar, b int, c int, d text) distributed by (a,c,d); +CREATE TABLE +CREATE TABLE copy_regression_hash4(a int[], b text) distributed by (a); +CREATE TABLE +CREATE TABLE copy_regression_hash5(a text[][]) distributed by (a); +CREATE TABLE +CREATE TABLE copy_regression_hash6(a int[], b text[]) distributed by (a,b); +CREATE TABLE +CREATE TABLE copy_regression_hash7(a text,b text) distributed randomly; +CREATE TABLE +-- single key, not first +COPY copy_regression_hash1 from stdin segment reject limit 10 rows; +COPY copy_regression_hash1(b,c,a) from stdin segment reject limit 10 rows; +SELECT * FROM copy_regression_hash1; + a | b | c +--------+--------+-------- + a data | b data | c data + a data | b data | c data +(2 rows) + +-- two keys +COPY copy_regression_hash2 from stdin; +COPY copy_regression_hash2(b,c,a) from stdin; +SELECT * FROM copy_regression_hash2; + a | b | c +--------+--------+-------- + a data | b data | c data + a data | b data | c data +(2 rows) + +-- three keys +COPY copy_regression_hash3 from stdin; +COPY copy_regression_hash3(c,d,b,a) from stdin; +COPY copy_regression_hash3(a,c) from stdin; +COPY copy_regression_hash3(d) from stdin; +SELECT * FROM copy_regression_hash3; + a | b | c | d +--------+---+---+-------- + a data | 2 | 3 | d data + a data | 2 | 3 | d data + a data | | 3 | + | | | d data +(4 rows) + +-- hash on int array +COPY copy_regression_hash4 from stdin; +SELECT * FROM copy_regression_hash4 ORDER BY a; + a | b +------------------+---------------------------- + {1,2,3,4,5} | hashed on an integer array + {10,20,30,40,50} | hashed on an integer array +(2 rows) + +-- hash on 2 dim varchar array +COPY copy_regression_hash5 from stdin; +SELECT * FROM copy_regression_hash5 ORDER BY a; + a +------------------------------------------------- + {{hashing,on},{"two dimentional","text array"}} +(1 row) + +-- hash on int array and varchar array +COPY copy_regression_hash6 from stdin; +SELECT * FROM copy_regression_hash6 ORDER BY a; + a | b +-------------+---------------------------------------- + {1,2,3,4,5} | {hashing,on,intarray,and,varchararray} +(1 row) + +-- hash randomly +COPY copy_regression_hash7 from stdin; +SELECT * FROM copy_regression_hash7; + a | b +--------+-------- + a data | b data + a data | b data +(2 rows) + +DROP TABLE copy_regression_hash1; +DROP TABLE +DROP TABLE copy_regression_hash2; +DROP TABLE +DROP TABLE copy_regression_hash3; +DROP TABLE +DROP TABLE copy_regression_hash4; +DROP TABLE +DROP TABLE copy_regression_hash5; +DROP TABLE +DROP TABLE copy_regression_hash6; +DROP TABLE +DROP TABLE copy_regression_hash7; +DROP TABLE +-- ###################################################### +-- default values and default functions +-- ###################################################### +CREATE TABLE copy_regression_default1(a text not null default 'a default data', b text not null default 'b default data') distributed by(a); +CREATE TABLE +CREATE TABLE copy_regression_default2(a text, b serial, c text) distributed by(a); +psql:/tmp/TestCommand_TestCOPY.sql:99: NOTICE: CREATE TABLE will create implicit sequence "copy_regression_default2_b_seq" for serial column "copy_regression_default2.b" +CREATE TABLE +CREATE TABLE copy_regression_default3(a serial, b text, c text) distributed by(a); +psql:/tmp/TestCommand_TestCOPY.sql:100: NOTICE: CREATE TABLE will create implicit sequence "copy_regression_default3_a_seq" for serial column "copy_regression_default3.a" +CREATE TABLE +-- constant defaults on key and non key attributes +COPY copy_regression_default1(a) from stdin; +COPY copy_regression_default1(b) from stdin; +SELECT * FROM copy_regression_default1; + a | b +----------------+---------------- + a data | b default data + a default data | b data +(2 rows) + +-- non constant default on non hash key in both text and csv +COPY copy_regression_default2(a,c) from stdin; +COPY copy_regression_default2(a,c) from stdin csv; +SELECT * FROM copy_regression_default2; + a | b | c +--------+---+--------- + a data | 1 | c data + a data | 2 | c data + a data | 3 | c data + a data | 4 | c data + a data | 5 | c data + a data | 6 | c data +(6 rows) + +-- non constant default on hash key in both text and csv +COPY copy_regression_default3(b,c) from stdin; +COPY copy_regression_default3(b,c) from stdin csv; +SELECT * FROM copy_regression_default2; + a | b | c +--------+---+--------- + a data | 1 | c data + a data | 2 | c data + a data | 3 | c data + a data | 4 | c data + a data | 5 | c data + a data | 6 | c data +(6 rows) + +DROP TABLE copy_regression_default1; +DROP TABLE +DROP TABLE copy_regression_default2; +DROP TABLE +DROP TABLE copy_regression_default3; +DROP TABLE +-- ###################################################### +-- COPY with OIDS +-- ###################################################### +CREATE TABLE copy_regression_oids(a text) WITH OIDS; +psql:/tmp/TestCommand_TestCOPY.sql:148: NOTICE: OIDS=TRUE is not recommended for user-created tables. Use OIDS=FALSE to prevent wrap-around of the OID counter +CREATE TABLE +COPY copy_regression_oids from stdin with oids delimiter '|'; +COPY copy_regression_oids from stdin with oids csv; +SELECT * FROM copy_regression_oids ORDER BY oid; + a +------------- + a text data + a text data + a text data + a text data +(4 rows) + +DROP TABLE copy_regression_oids; +DROP TABLE +-- ###################################################### +-- COPY OUT +-- ###################################################### +CREATE TABLE copy_regression_out1(a text, b varchar, c int[], d bool) distributed by(a); +CREATE TABLE +-- populating table with data for copy out tests. NOTE: since we can't control the order +-- of rows COPY OUT produces from the segdb, we must have only one row for each test table. +COPY copy_regression_out1 from stdin null 'nullval'; +-- copy out default parameters text format.. +COPY copy_regression_out1 to stdout; +a copyout data line 2 \N {2,2,2} t +-- copy out '|' delimiter 'mynull' null.. +COPY copy_regression_out1 to stdout with delimiter '|' null 'mynull'; +a copyout data line 2|mynull|{2,2,2}|t +-- copy out '|' delimiter "i'm null" null.. +COPY copy_regression_out1 to stdout with delimiter '|' null 'i''m null'; +a copyout data line 2|i'm null|{2,2,2}|t +-- copy out default parameters csv format.. +COPY copy_regression_out1 to stdout with csv; +a copyout data line 2,,"{2,2,2}",t +-- copy out default parameters csv format with header.. +COPY copy_regression_out1 to stdout with csv header; +a,b,c,d +a copyout data line 2,,"{2,2,2}",t +-- copy out partial and mixed column list (c,a,b).. +COPY copy_regression_out1(c,a,b) to stdout; +{2,2,2} a copyout data line 2 \N +-- copy out csv default quotes and escapes and special cases.. +COPY copy_regression_out1 to stdout with csv quote ''''; -- escape should be the same as quote +a copyout data line 2,,'{2,2,2}',t +COPY copy_regression_out1 to stdout with csv escape ''''; +a copyout data line 2,,"{2,2,2}",t +COPY copy_regression_out1 to stdout with csv quote '''' escape E'\\'; -- make sure E'' is accepted +a copyout data line 2,,'{2,2,2}',t +DROP TABLE copy_regression_out1; +DROP TABLE +-- ###################################################### +-- Zero column table +-- ###################################################### +CREATE TABLE copy_regression_nocol(); +CREATE TABLE +-- copy in and out of zero column table.. +COPY copy_regression_nocol from stdin; +COPY copy_regression_nocol from stdin; +psql:/tmp/TestCommand_TestCOPY.sql:214: ERROR: extra data after last expected column +CONTEXT: COPY copy_regression_nocol, line 1: "we should get an "extra data" error here" +COPY copy_regression_nocol from stdin with csv; +COPY copy_regression_nocol from stdin with csv; -- should fail +psql:/tmp/TestCommand_TestCOPY.sql:219: ERROR: extra data after last expected column +CONTEXT: COPY copy_regression_nocol, line 1: "we should get an "extra data" error here" +COPY copy_regression_nocol to stdout; +COPY copy_regression_nocol to stdout with csv; +DROP TABLE copy_regression_nocol; +DROP TABLE +-- ###################################################### +-- Various text format escape and default format tests +-- ###################################################### +-- for text format +CREATE TABLE copy_regression_text1(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE +CREATE TABLE copy_regression_text2(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE +CREATE TABLE copy_regression_text3(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE +-- column order tests +COPY copy_regression_text1 from stdin; +COPY copy_regression_text1(a,b,c,d,e) from stdin; +COPY copy_regression_text1(e,d,c,b,a) from stdin; +COPY copy_regression_text1(c,a,b,e,d) from stdin; +COPY copy_regression_text1(a,c) from stdin; +SELECT * FROM copy_regression_text1; + a | b | c | d | e +-------------+-------------+-------------+-------------+------------- + a text data | b text data | c text data | d text data | e text data + a text data | b text data | c text data | d text data | e text data + a text data | b text data | c text data | d text data | e text data + a text data | b text data | c text data | d text data | e text data + a text data | | c text data | | +(5 rows) + +-- null print tests +COPY copy_regression_text2 from stdin; +COPY copy_regression_text2 from stdin with null 'nullvalue'; +COPY copy_regression_text2 from stdin with delimiter '|' null ''; +COPY copy_regression_text2 from stdin with delimiter '|' null 'i''m null'; +SELECT * FROM copy_regression_text2; + a | b | c | d | e +-------------+-------------+-------------+---+------------- + a text data | | c text data | | e text data + a text data | | c text data | | + a text data | | c text data | | e text data + a text data | b text data | | | e text data + | b text data | c text data | | e text data +(5 rows) + +-- escape tests +COPY copy_regression_text3 from stdin with delimiter '|' escape '#'; +COPY copy_regression_text3 from stdin with delimiter '|' escape 'off'; +COPY copy_regression_text3 from stdin with delimiter '|'; +COPY copy_regression_text3 from stdin with delimiter '|'; +psql:/tmp/TestCommand_TestCOPY.sql:290: ERROR: missing data for column "b" +CONTEXT: COPY copy_regression_text3, line 1: "an embedded linefeed is not supported\" +COPY copy_regression_text3 from stdin with delimiter '|'; +SELECT * FROM copy_regression_text3 ORDER BY b; + a | b | c | d | e +------------------------------------+---+-------------+-------------+-------------- + the at sign: @ | 1 | c text data | d text data | e text data + an embedded delimiter | character | 2 | c text data | d text data | e text data + a single backslash \ in col a | 3 | c text data | d text data | e text data + a single backslash \ in col a | 4 | c text data | d text data | e text data + c:\\file\data\neew\path | 5 | c text data | d text data | e text data + the at sign: @ | 6 | c text data | d text data | e text data + an embedded linefeed sequence | 7 | c text data | d text data | e text data + in column a + a single backslash \ in col a | 8 | c text data | d text data | e text data +(8 rows) + +DROP TABLE copy_regression_text1; +DROP TABLE +DROP TABLE copy_regression_text2; +DROP TABLE +DROP TABLE copy_regression_text3; +DROP TABLE +-- ###################################################### +-- Various text format escape and default format tests +-- ###################################################### +-- for csv format +CREATE TABLE copy_regression_csv1(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE +CREATE TABLE copy_regression_csv2(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE +CREATE TABLE copy_regression_csv3(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE +-- column order tests +COPY copy_regression_csv1 from stdin with csv; +COPY copy_regression_csv1(a,b,c,d,e) from stdin with csv; +COPY copy_regression_csv1(e,d,c,b,a) from stdin with csv; +COPY copy_regression_csv1(c,a,b,e,d) from stdin with csv; +COPY copy_regression_csv1(a,c) from stdin with csv; +SELECT * FROM copy_regression_csv1; + a | b | c | d | e +-------------+-------------+-------------+-------------+------------- + a csv data | b csv data | c csv data | d csv data | e csv data + a csv data | b csv data | c csv data | d csv data | e csv data + a csv data | b csv data | c csv data | d csv data | e csv data + a csv data | b csv data | c csv data | d csv data | e csv data + a csv data | | c csv data | | +(5 rows) + +-- null print tests +COPY copy_regression_csv2 from stdin with null E'\\N' csv ; +COPY copy_regression_csv2 from stdin with null 'nullvalue' csv; +COPY copy_regression_csv2 from stdin with delimiter '|' null '' csv; +SELECT * FROM copy_regression_csv2; + a | b | c | d | e +-------------+-------------+-------------+---+------------- + a csv data | | c csv data | | e csv data + a csv data | | c csv data | | + a csv data | | c csv data | | e csv data + | b csv data | c csv data | | e csv data +(4 rows) + +-- escape tests +COPY copy_regression_csv3 from stdin with csv escape E'\\'; +COPY copy_regression_csv3 from stdin with delimiter E'\t' csv; --default quote and escape - " +COPY copy_regression_csv3 from stdin with delimiter '|' csv quote ';' escape '*'; +-- check defaults +COPY copy_regression_csv3 from stdin with csv quote ''''; -- escape should be the same as quote +COPY copy_regression_csv3 from stdin with csv escape ''''; +COPY copy_regression_csv3 from stdin with csv quote '''' escape E'\\'; -- make sure E'' is accepted +SELECT * FROM copy_regression_csv3 ORDER BY b; + a | b | c | d | e +---------------------------------------------------+----+-------------+-------------+-------------- + an embedded delimiter (comma), is printed | 01 | c csv data | d csv data | e csv data + an embedded quote (doubleq)" is printed | 02 | c csv data | d csv data | e csv data + an embedded escape \ is printed | 03 | c csv data | d csv data | e csv data + an embedded line feed | 04 | c csv data | d csv data | e csv data + is printed + an embedded delimiter (tab) is printed | 05 | c csv data | d csv data | e csv data + an embedded quote or escape (doubleq)" is printed | 06 | c csv data | d csv data | e csv data + an embedded line feed | 07 | c csv data | d csv data | e csv data + is printed + an embedded delimiter (pipe)| is printed | 08 | c csv data | d csv data | e csv data + an embedded quote (semicolon); is printed | 09 | c csv data | d csv data | e csv data + an embedded escape (asterisk)* is printed | 10 | c csv data | d csv data | e csv data + an embedded line feed | 11 | c csv data | d csv data | e csv data + is printed + an embedded single quote ' here | 12 | c csv data | d csv data | e csv data + an embedded single quote ' here | 13 | c csv data | d csv data | e csv data + an embedded backslash \ here | 14 | c csv data | d csv data | e csv data +(14 rows) + +DROP TABLE copy_regression_csv1; +DROP TABLE +DROP TABLE copy_regression_csv2; +DROP TABLE +DROP TABLE copy_regression_csv3; +DROP TABLE +-- ###################################################### +-- FILL MISSING FIELDS +-- ###################################################### +CREATE TABLE copy_regression_fill1(a int, b int, c text) distributed by(a); +CREATE TABLE +CREATE TABLE copy_regression_fill2(a int, b int, c text) distributed by(c); +CREATE TABLE +-- text +COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; +COPY copy_regression_fill1(c,b) from stdin with delimiter '|' fill missing fields; +COPY copy_regression_fill2(a,c) from stdin with delimiter '|' fill missing fields; +SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; + a | b | c +---+---+------- + 1 | 1 | one + 2 | 2 | + 3 | | + | 1 | one + | | three + | | two +(6 rows) + +SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; + a | b | c +---+---+------- + 1 | | one + 2 | | + 3 | | three +(3 rows) + +TRUNCATE copy_regression_fill1; +TRUNCATE TABLE +TRUNCATE copy_regression_fill2; +TRUNCATE TABLE +-- csv +COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; +COPY copy_regression_fill1(c,b) from stdin with csv delimiter '|' fill missing fields; +COPY copy_regression_fill2(a,c) from stdin with csv delimiter '|' fill missing fields; +SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; + a | b | c +---+---+------- + 1 | 1 | one + 2 | 2 | + 3 | | + | 1 | one + | | three + | | two +(6 rows) + +SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; + a | b | c +---+---+------- + 1 | | one + 2 | | + 3 | | three +(3 rows) + +-- empty row should fail +COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; +psql:/tmp/TestCommand_TestCOPY.sql:434: ERROR: missing data for column "b", found empty data line +CONTEXT: COPY copy_regression_fill1, line 1: "" +COPY copy_regression_fill2 from stdin with delimiter '|' fill missing fields; +psql:/tmp/TestCommand_TestCOPY.sql:437: ERROR: missing data for column "b", found empty data line +CONTEXT: COPY copy_regression_fill2, line 1: "" +COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; +psql:/tmp/TestCommand_TestCOPY.sql:440: ERROR: missing data for column "b", found empty data line +CONTEXT: COPY copy_regression_fill1, line 1: "" +COPY copy_regression_fill2 from stdin with csv delimiter '|' fill missing fields; +psql:/tmp/TestCommand_TestCOPY.sql:443: ERROR: missing data for column "b", found empty data line +CONTEXT: COPY copy_regression_fill2, line 1: "" +DROP TABLE copy_regression_fill1; +DROP TABLE +DROP TABLE copy_regression_fill2; +DROP TABLE +-- ###################################################### +-- FORCE NOT NULL +-- ###################################################### +CREATE TABLE copy_regression_fnn(a text, b text, c text) distributed by(a); +CREATE TABLE +COPY copy_regression_fnn from stdin with csv; +SELECT * FROM copy_regression_fnn WHERE b is null order by a; + a | b | c +-----+---+----- + one | | one + two | | +(2 rows) + +SELECT * FROM copy_regression_fnn WHERE c is null order by a; + a | b | c +-----+---+--- + two | | +(1 row) + +TRUNCATE copy_regression_fnn; +TRUNCATE TABLE +COPY copy_regression_fnn from stdin with csv force not null b; +SELECT * FROM copy_regression_fnn WHERE b is null order by a; + a | b | c +---+---+--- +(0 rows) + +SELECT * FROM copy_regression_fnn WHERE c is null order by a; + a | b | c +-----+---+--- + two | | +(1 row) + +TRUNCATE copy_regression_fnn; +TRUNCATE TABLE +COPY copy_regression_fnn from stdin with csv force not null b,c; +SELECT * FROM copy_regression_fnn WHERE b is null order by a; + a | b | c +---+---+--- +(0 rows) + +SELECT * FROM copy_regression_fnn WHERE c is null order by a; + a | b | c +---+---+--- +(0 rows) + +TRUNCATE copy_regression_fnn; +TRUNCATE TABLE +-- now combine with fill missing fields +COPY copy_regression_fnn from stdin with csv fill missing fields force not null b; +SELECT * FROM copy_regression_fnn WHERE b is null order by a; + a | b | c +---+---+--- +(0 rows) + +SELECT * FROM copy_regression_fnn WHERE c is null order by a; + a | b | c +-----+---+--- + two | | +(1 row) + +DROP TABLE copy_regression_fnn; +DROP TABLE +-- ########################################################### +-- distributed data error consolidation + original row numbers +-- ########################################################### +CREATE TABLE copy_regression_error1(a int, b int) distributed by(a); +CREATE TABLE +-- parse error on QE (extra column on line 6) +-- start_ignore +COPY copy_regression_error1 from stdin; +psql:/tmp/TestCommand_TestCOPY.sql:503: ERROR: extra data after last expected column (seg5 localhost:40000 pid=74724) +CONTEXT: COPY copy_regression_error1, line 6: "6 6 6" +-- end_ignore +-- parse error on QD (missing column on line 3) +-- start_ignore +COPY copy_regression_error1 from stdin; +psql:/tmp/TestCommand_TestCOPY.sql:513: ERROR: missing data for column "b" +CONTEXT: COPY copy_regression_error1, line 3: "3" +-- end_ignore +-- convert error on QD (invalid type line 2) +COPY copy_regression_error1 from stdin; +psql:/tmp/TestCommand_TestCOPY.sql:522: ERROR: invalid input syntax for integer: "two" +CONTEXT: COPY copy_regression_error1, line 2, column a +-- convert error on QE (invalid type line 5) +COPY copy_regression_error1 from stdin; +psql:/tmp/TestCommand_TestCOPY.sql:533: ERROR: invalid input syntax for integer: "five" (seg2 localhost:40000 pid=74715) +CONTEXT: COPY copy_regression_error1, line 5, column b +DROP TABLE copy_regression_error1; +DROP TABLE +-- ###################################################### +-- NEWLINE +-- ###################################################### +CREATE TABLE copy_regression_newline(a text, b text) distributed by(a); +CREATE TABLE +-- positive: text +COPY copy_regression_newline from stdin with delimiter '|' newline 'lf'; +-- positive: csv +COPY copy_regression_newline from stdin with delimiter '|' newline 'lf' csv; +-- start_ignore +-- negative: text +COPY copy_regression_newline from stdin with delimiter '|' newline 'cr'; +psql:/tmp/TestCommand_TestCOPY.sql:560: ERROR: extra data after last expected column (seg4 localhost:40000 pid=74723) +CONTEXT: COPY copy_regression_newline, line 1: "1|1 +2|2 +" +-- negative: csv +COPY copy_regression_newline from stdin with delimiter '|' newline 'cr' csv; +psql:/tmp/TestCommand_TestCOPY.sql:566: ERROR: extra data after last expected column (seg4 localhost:40000 pid=74717) +CONTEXT: COPY copy_regression_newline, line 1: "1|1 +2|2 +" +-- end_ignore +-- negative: invalid newline +COPY copy_regression_newline from stdin with delimiter '|' newline 'blah'; +psql:/tmp/TestCommand_TestCOPY.sql:571: ERROR: invalid value for NEWLINE (blah) +HINT: valid options are: 'LF', 'CRLF', 'CR' +-- negative: newline not yet supported for COPY TO +COPY copy_regression_newline to stdout with delimiter '|' newline 'blah'; +psql:/tmp/TestCommand_TestCOPY.sql:573: ERROR: newline currently available for data loading only, not unloading +DROP TABLE copy_regression_newline; +DROP TABLE http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b3934fd/src/test/feature/UtilityCommand/sql/gpcopy.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/UtilityCommand/sql/gpcopy.sql b/src/test/feature/UtilityCommand/sql/gpcopy.sql new file mode 100644 index 0000000..6c48586 --- /dev/null +++ b/src/test/feature/UtilityCommand/sql/gpcopy.sql @@ -0,0 +1,572 @@ +-- +-- This test suite tests COPY code that is unique to greenplum db. +-- + +-- ###################################################### +-- different distribution columns and distribution types +-- ###################################################### + +CREATE TABLE copy_regression_hash1(a text, b text, c text) distributed by (b); +CREATE TABLE copy_regression_hash2(a text, b text, c text) distributed by (a,c); +CREATE TABLE copy_regression_hash3(a varchar, b int, c int, d text) distributed by (a,c,d); +CREATE TABLE copy_regression_hash4(a int[], b text) distributed by (a); +CREATE TABLE copy_regression_hash5(a text[][]) distributed by (a); +CREATE TABLE copy_regression_hash6(a int[], b text[]) distributed by (a,b); +CREATE TABLE copy_regression_hash7(a text,b text) distributed randomly; + +-- single key, not first + +COPY copy_regression_hash1 from stdin segment reject limit 10 rows; +a data b data c data +\. +COPY copy_regression_hash1(b,c,a) from stdin segment reject limit 10 rows; +b data c data a data +\. +SELECT * FROM copy_regression_hash1; + +-- two keys + +COPY copy_regression_hash2 from stdin; +a data b data c data +\. +COPY copy_regression_hash2(b,c,a) from stdin; +b data c data a data +\. +SELECT * FROM copy_regression_hash2; + +-- three keys + +COPY copy_regression_hash3 from stdin; +a data 2 3 d data +\. +COPY copy_regression_hash3(c,d,b,a) from stdin; +3 d data 2 a data +\. +COPY copy_regression_hash3(a,c) from stdin; +a data 3 +\. +COPY copy_regression_hash3(d) from stdin; +d data +\. +SELECT * FROM copy_regression_hash3; + +-- hash on int array + +COPY copy_regression_hash4 from stdin; +{1,2,3,4,5} hashed on an integer array +{10,20,30,40,50} hashed on an integer array +\. +SELECT * FROM copy_regression_hash4 ORDER BY a; + +-- hash on 2 dim varchar array + +COPY copy_regression_hash5 from stdin; +{{hashing,on},{two dimentional,text array}} +\. +SELECT * FROM copy_regression_hash5 ORDER BY a; + +-- hash on int array and varchar array + +COPY copy_regression_hash6 from stdin; +{1,2,3,4,5} {hashing,on,intarray,and,varchararray} +\. +SELECT * FROM copy_regression_hash6 ORDER BY a; + +-- hash randomly + +COPY copy_regression_hash7 from stdin; +a data b data +a data b data +\. +SELECT * FROM copy_regression_hash7; + +DROP TABLE copy_regression_hash1; +DROP TABLE copy_regression_hash2; +DROP TABLE copy_regression_hash3; +DROP TABLE copy_regression_hash4; +DROP TABLE copy_regression_hash5; +DROP TABLE copy_regression_hash6; +DROP TABLE copy_regression_hash7; + +-- ###################################################### +-- default values and default functions +-- ###################################################### + +CREATE TABLE copy_regression_default1(a text not null default 'a default data', b text not null default 'b default data') distributed by(a); +CREATE TABLE copy_regression_default2(a text, b serial, c text) distributed by(a); +CREATE TABLE copy_regression_default3(a serial, b text, c text) distributed by(a); + +-- constant defaults on key and non key attributes + +COPY copy_regression_default1(a) from stdin; +a data +\. +COPY copy_regression_default1(b) from stdin; +b data +\. +SELECT * FROM copy_regression_default1; + +-- non constant default on non hash key in both text and csv + +COPY copy_regression_default2(a,c) from stdin; +a data c data +a data c data +a data c data +\. +COPY copy_regression_default2(a,c) from stdin csv; +a data,c data +a data,c data +a data,c data +\. +SELECT * FROM copy_regression_default2; + +-- non constant default on hash key in both text and csv + +COPY copy_regression_default3(b,c) from stdin; +b data c data +b data c data +b data c data +\. +COPY copy_regression_default3(b,c) from stdin csv; +b data,c data +b data,c data +b data,c data +\. +SELECT * FROM copy_regression_default2; + +DROP TABLE copy_regression_default1; +DROP TABLE copy_regression_default2; +DROP TABLE copy_regression_default3; + +-- ###################################################### +-- COPY with OIDS +-- ###################################################### + +CREATE TABLE copy_regression_oids(a text) WITH OIDS; + +COPY copy_regression_oids from stdin with oids delimiter '|'; +50000|a text data +50001|a text data +\. +COPY copy_regression_oids from stdin with oids csv; +60000,a text data +60001,a text data +\. +SELECT * FROM copy_regression_oids ORDER BY oid; + +DROP TABLE copy_regression_oids; + + + +-- ###################################################### +-- COPY OUT +-- ###################################################### + +CREATE TABLE copy_regression_out1(a text, b varchar, c int[], d bool) distributed by(a); + +-- populating table with data for copy out tests. NOTE: since we can't control the order +-- of rows COPY OUT produces from the segdb, we must have only one row for each test table. + +COPY copy_regression_out1 from stdin null 'nullval'; +a copyout data line 2 nullval {2,2,2} true +\. + +-- copy out default parameters text format.. +COPY copy_regression_out1 to stdout; + +-- copy out '|' delimiter 'mynull' null.. +COPY copy_regression_out1 to stdout with delimiter '|' null 'mynull'; + +-- copy out '|' delimiter "i'm null" null.. +COPY copy_regression_out1 to stdout with delimiter '|' null 'i''m null'; + +-- copy out default parameters csv format.. +COPY copy_regression_out1 to stdout with csv; + +-- copy out default parameters csv format with header.. +COPY copy_regression_out1 to stdout with csv header; + +-- copy out partial and mixed column list (c,a,b).. +COPY copy_regression_out1(c,a,b) to stdout; + +-- copy out csv default quotes and escapes and special cases.. +COPY copy_regression_out1 to stdout with csv quote ''''; -- escape should be the same as quote +COPY copy_regression_out1 to stdout with csv escape ''''; +COPY copy_regression_out1 to stdout with csv quote '''' escape E'\\'; -- make sure E'' is accepted + +DROP TABLE copy_regression_out1; + + +-- ###################################################### +-- Zero column table +-- ###################################################### + +CREATE TABLE copy_regression_nocol(); + +-- copy in and out of zero column table.. +COPY copy_regression_nocol from stdin; +\. +COPY copy_regression_nocol from stdin; +we should get an "extra data" error here +\. +COPY copy_regression_nocol from stdin with csv; +\. +COPY copy_regression_nocol from stdin with csv; -- should fail +we should get an "extra data" error here +\. +COPY copy_regression_nocol to stdout; +COPY copy_regression_nocol to stdout with csv; + +DROP TABLE copy_regression_nocol; + + +-- ###################################################### +-- Various text format escape and default format tests +-- ###################################################### + +-- for text format +CREATE TABLE copy_regression_text1(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE copy_regression_text2(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE copy_regression_text3(a varchar ,b varchar, c text, d text, e text) distributed by(a); + +-- column order tests + +COPY copy_regression_text1 from stdin; +a text data b text data c text data d text data e text data +\. +COPY copy_regression_text1(a,b,c,d,e) from stdin; +a text data b text data c text data d text data e text data +\. +COPY copy_regression_text1(e,d,c,b,a) from stdin; +e text data d text data c text data b text data a text data +\. +COPY copy_regression_text1(c,a,b,e,d) from stdin; +c text data a text data b text data e text data d text data +\. +COPY copy_regression_text1(a,c) from stdin; +a text data c text data +\. +SELECT * FROM copy_regression_text1; + +-- null print tests + +COPY copy_regression_text2 from stdin; +a text data \N c text data \N e text data +\. +COPY copy_regression_text2 from stdin with null 'nullvalue'; +a text data nullvalue c text data nullvalue nullvalue +\. +COPY copy_regression_text2 from stdin with delimiter '|' null ''; +a text data||c text data||e text data +|b text data|c text data||e text data +\. +COPY copy_regression_text2 from stdin with delimiter '|' null 'i''m null'; +a text data|b text data|i'm null|i'm null|e text data +\. +SELECT * FROM copy_regression_text2; + +-- escape tests + +COPY copy_regression_text3 from stdin with delimiter '|' escape '#'; +the at sign: #100 |1|c text data|d text data|e text data +an embedded delimiter #| character |2|c text data|d text data|e text data +a single backslash \ in col a|3|c text data|d text data|e text data +\. +COPY copy_regression_text3 from stdin with delimiter '|' escape 'off'; +a single backslash \ in col a|4|c text data|d text data|e text data +c:\\file\data\neew\path|5|c text data|d text data|e text data +\. +COPY copy_regression_text3 from stdin with delimiter '|'; +the at sign: \100|6|c text data|d text data|e text data +a single backslash \\ in col a|8|c text data|d text data|e text data +\. +COPY copy_regression_text3 from stdin with delimiter '|'; +an embedded linefeed is not supported\ +and another one that should fail\ +in column a|7|c text data|d text data|e text data +\. +COPY copy_regression_text3 from stdin with delimiter '|'; +an embedded linefeed sequence\nin column a|7|c text data|d text data|e text data +\. + +SELECT * FROM copy_regression_text3 ORDER BY b; + +DROP TABLE copy_regression_text1; +DROP TABLE copy_regression_text2; +DROP TABLE copy_regression_text3; + + + +-- ###################################################### +-- Various text format escape and default format tests +-- ###################################################### + +-- for csv format +CREATE TABLE copy_regression_csv1(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE copy_regression_csv2(a varchar ,b varchar, c text, d text, e text) distributed by(a); +CREATE TABLE copy_regression_csv3(a varchar ,b varchar, c text, d text, e text) distributed by(a); + +-- column order tests + +COPY copy_regression_csv1 from stdin with csv; +a csv data,b csv data,c csv data,d csv data,e csv data +\. +COPY copy_regression_csv1(a,b,c,d,e) from stdin with csv; +a csv data,b csv data,c csv data,d csv data,e csv data +\. +COPY copy_regression_csv1(e,d,c,b,a) from stdin with csv; +e csv data,d csv data,c csv data,b csv data,a csv data +\. +COPY copy_regression_csv1(c,a,b,e,d) from stdin with csv; +c csv data,a csv data,b csv data,e csv data,d csv data +\. +COPY copy_regression_csv1(a,c) from stdin with csv; +a csv data,c csv data +\. +SELECT * FROM copy_regression_csv1; + +-- null print tests + +COPY copy_regression_csv2 from stdin with null E'\\N' csv ; +a csv data,\N,c csv data,\N,e csv data +\. +COPY copy_regression_csv2 from stdin with null 'nullvalue' csv; +a csv data,nullvalue,c csv data,nullvalue,nullvalue +\. +COPY copy_regression_csv2 from stdin with delimiter '|' null '' csv; +a csv data||c csv data||e csv data +|b csv data|c csv data||e csv data +\. +SELECT * FROM copy_regression_csv2; + +-- escape tests + +COPY copy_regression_csv3 from stdin with csv escape E'\\'; +"an embedded delimiter (comma), is printed",01,c csv data,d csv data,e csv data +"an embedded quote (doubleq)\" is printed",02,c csv data,d csv data,e csv data +"an embedded escape \\ is printed",03,c csv data,d csv data,e csv data +"an embedded line feed +is printed",04,c csv data,d csv data,e csv data +\. +COPY copy_regression_csv3 from stdin with delimiter E'\t' csv; --default quote and escape - " +"an embedded delimiter (tab) is printed" 05 c csv data d csv data e csv data +"an embedded quote or escape (doubleq)"" is printed" 06 c csv data d csv data e csv data +"an embedded line feed +is printed" 07 c csv data d csv data e csv data +\. +COPY copy_regression_csv3 from stdin with delimiter '|' csv quote ';' escape '*'; +;an embedded delimiter (pipe)| is printed;|08|c csv data|d csv data|e csv data +;an embedded quote (semicolon)*; is printed;|09|c csv data|d csv data|e csv data +;an embedded escape (asterisk)** is printed;|10|c csv data|d csv data|e csv data +;an embedded line feed +is printed;|11|c csv data|d csv data|e csv data +\. + +-- check defaults +COPY copy_regression_csv3 from stdin with csv quote ''''; -- escape should be the same as quote +'an embedded single quote '' here',12,c csv data,d csv data,e csv data +\. +COPY copy_regression_csv3 from stdin with csv escape ''''; +"an embedded single quote '' here",13,c csv data,d csv data,e csv data +\. +COPY copy_regression_csv3 from stdin with csv quote '''' escape E'\\'; -- make sure E'' is accepted +'an embedded backslash \\ here',14,c csv data,d csv data,e csv data +\. +SELECT * FROM copy_regression_csv3 ORDER BY b; + +DROP TABLE copy_regression_csv1; +DROP TABLE copy_regression_csv2; +DROP TABLE copy_regression_csv3; + +-- ###################################################### +-- FILL MISSING FIELDS +-- ###################################################### + +CREATE TABLE copy_regression_fill1(a int, b int, c text) distributed by(a); +CREATE TABLE copy_regression_fill2(a int, b int, c text) distributed by(c); + +-- text +COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; +1|1|one +2|2 +3 +\. +COPY copy_regression_fill1(c,b) from stdin with delimiter '|' fill missing fields; +one|1 +two +three +\. +COPY copy_regression_fill2(a,c) from stdin with delimiter '|' fill missing fields; +1|one +2 +3|three +\. +SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; +SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; +TRUNCATE copy_regression_fill1; +TRUNCATE copy_regression_fill2; + +-- csv +COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; +1|1|one +2|2 +3 +\. +COPY copy_regression_fill1(c,b) from stdin with csv delimiter '|' fill missing fields; +one|1 +two +three +\. +COPY copy_regression_fill2(a,c) from stdin with csv delimiter '|' fill missing fields; +1|one +2 +3|three +\. +SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; +SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; + +-- empty row should fail +COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; + +\. +COPY copy_regression_fill2 from stdin with delimiter '|' fill missing fields; + +\. +COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; + +\. +COPY copy_regression_fill2 from stdin with csv delimiter '|' fill missing fields; + +\. +DROP TABLE copy_regression_fill1; +DROP TABLE copy_regression_fill2; + +-- ###################################################### +-- FORCE NOT NULL +-- ###################################################### + +CREATE TABLE copy_regression_fnn(a text, b text, c text) distributed by(a); + +COPY copy_regression_fnn from stdin with csv; +one,,one +two,, +\. +SELECT * FROM copy_regression_fnn WHERE b is null order by a; +SELECT * FROM copy_regression_fnn WHERE c is null order by a; +TRUNCATE copy_regression_fnn; + +COPY copy_regression_fnn from stdin with csv force not null b; +one,,one +two,, +\. +SELECT * FROM copy_regression_fnn WHERE b is null order by a; +SELECT * FROM copy_regression_fnn WHERE c is null order by a; +TRUNCATE copy_regression_fnn; + +COPY copy_regression_fnn from stdin with csv force not null b,c; +one,,one +two,, +\. +SELECT * FROM copy_regression_fnn WHERE b is null order by a; +SELECT * FROM copy_regression_fnn WHERE c is null order by a; +TRUNCATE copy_regression_fnn; + +-- now combine with fill missing fields +COPY copy_regression_fnn from stdin with csv fill missing fields force not null b; +one,,one +two, +\. +SELECT * FROM copy_regression_fnn WHERE b is null order by a; +SELECT * FROM copy_regression_fnn WHERE c is null order by a; + +DROP TABLE copy_regression_fnn; + +-- ########################################################### +-- distributed data error consolidation + original row numbers +-- ########################################################### + +CREATE TABLE copy_regression_error1(a int, b int) distributed by(a); + +-- parse error on QE (extra column on line 6) +-- start_ignore +COPY copy_regression_error1 from stdin; +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 6 +7 7 +\. +-- end_ignore + +-- parse error on QD (missing column on line 3) +-- start_ignore +COPY copy_regression_error1 from stdin; +1 1 +2 2 +3 +4 4 +\. +-- end_ignore + +-- convert error on QD (invalid type line 2) + +COPY copy_regression_error1 from stdin; +1 1 +two 2 +3 3 +\. + +-- convert error on QE (invalid type line 5) +COPY copy_regression_error1 from stdin; +1 1 +2 2 +3 3 +4 4 +5 five +6 6 +7 7 +\. + +DROP TABLE copy_regression_error1; + +-- ###################################################### +-- NEWLINE +-- ###################################################### + +CREATE TABLE copy_regression_newline(a text, b text) distributed by(a); + +-- positive: text +COPY copy_regression_newline from stdin with delimiter '|' newline 'lf'; +1|1 +2|2 +\. + +-- positive: csv +COPY copy_regression_newline from stdin with delimiter '|' newline 'lf' csv; +1|1 +2|2 +\. + +-- start_ignore +-- negative: text +COPY copy_regression_newline from stdin with delimiter '|' newline 'cr'; +1|1 +2|2 +\. + +-- negative: csv +COPY copy_regression_newline from stdin with delimiter '|' newline 'cr' csv; +1|1 +2|2 +\. +-- end_ignore + + +-- negative: invalid newline +COPY copy_regression_newline from stdin with delimiter '|' newline 'blah'; +-- negative: newline not yet supported for COPY TO +COPY copy_regression_newline to stdout with delimiter '|' newline 'blah'; + +DROP TABLE copy_regression_newline; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b3934fd/src/test/feature/UtilityCommand/test_cmd.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/UtilityCommand/test_cmd.cpp b/src/test/feature/UtilityCommand/test_cmd.cpp new file mode 100644 index 0000000..25af7b8 --- /dev/null +++ b/src/test/feature/UtilityCommand/test_cmd.cpp @@ -0,0 +1,15 @@ +#include "gtest/gtest.h" + +#include "lib/sql_util.h" + +class TestCommand: public ::testing::Test { + public: + TestCommand() {} + ~TestCommand() {} +}; + +TEST_F(TestCommand, TestCOPY) { + hawq::test::SQLUtility util; + util.execSQLFile("UtilityCommand/sql/gpcopy.sql", + "UtilityCommand/ans/gpcopy.ans"); +} http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b3934fd/src/test/regress/expected/gpcopy.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/gpcopy.out b/src/test/regress/expected/gpcopy.out deleted file mode 100755 index 8252337..0000000 --- a/src/test/regress/expected/gpcopy.out +++ /dev/null @@ -1,513 +0,0 @@ --- --- This test suite tests COPY code that is unique to greenplum db. --- --- ###################################################### --- different distribution columns and distribution types --- ###################################################### -CREATE TABLE copy_regression_hash1(a text, b text, c text) distributed by (b); -CREATE TABLE copy_regression_hash2(a text, b text, c text) distributed by (a,c); -CREATE TABLE copy_regression_hash3(a varchar, b int, c int, d text) distributed by (a,c,d); -CREATE TABLE copy_regression_hash4(a int[], b text) distributed by (a); -CREATE TABLE copy_regression_hash5(a text[][]) distributed by (a); -CREATE TABLE copy_regression_hash6(a int[], b text[]) distributed by (a,b); -CREATE TABLE copy_regression_hash7(a text,b text) distributed randomly; --- single key, not first -COPY copy_regression_hash1 from stdin segment reject limit 10 rows; -COPY copy_regression_hash1(b,c,a) from stdin segment reject limit 10 rows; -SELECT * FROM copy_regression_hash1; - a | b | c ---------+--------+-------- - a data | b data | c data - a data | b data | c data -(2 rows) - --- two keys -COPY copy_regression_hash2 from stdin; -COPY copy_regression_hash2(b,c,a) from stdin; -SELECT * FROM copy_regression_hash2; - a | b | c ---------+--------+-------- - a data | b data | c data - a data | b data | c data -(2 rows) - --- three keys -COPY copy_regression_hash3 from stdin; -COPY copy_regression_hash3(c,d,b,a) from stdin; -COPY copy_regression_hash3(a,c) from stdin; -COPY copy_regression_hash3(d) from stdin; -SELECT * FROM copy_regression_hash3; - a | b | c | d ---------+---+---+-------- - a data | 2 | 3 | d data - a data | 2 | 3 | d data - a data | | 3 | - | | | d data -(4 rows) - --- hash on int array -COPY copy_regression_hash4 from stdin; -SELECT * FROM copy_regression_hash4 ORDER BY a; - a | b -------------------+---------------------------- - {1,2,3,4,5} | hashed on an integer array - {10,20,30,40,50} | hashed on an integer array -(2 rows) - --- hash on 2 dim varchar array -COPY copy_regression_hash5 from stdin; -SELECT * FROM copy_regression_hash5 ORDER BY a; - a -------------------------------------------------- - {{hashing,on},{"two dimentional","text array"}} -(1 row) - --- hash on int array and varchar array -COPY copy_regression_hash6 from stdin; -SELECT * FROM copy_regression_hash6 ORDER BY a; - a | b --------------+---------------------------------------- - {1,2,3,4,5} | {hashing,on,intarray,and,varchararray} -(1 row) - --- hash randomly -COPY copy_regression_hash7 from stdin; -SELECT * FROM copy_regression_hash7; - a | b ---------+-------- - a data | b data - a data | b data -(2 rows) - -DROP TABLE copy_regression_hash1; -DROP TABLE copy_regression_hash2; -DROP TABLE copy_regression_hash3; -DROP TABLE copy_regression_hash4; -DROP TABLE copy_regression_hash5; -DROP TABLE copy_regression_hash6; -DROP TABLE copy_regression_hash7; --- ###################################################### --- default values and default functions --- ###################################################### -CREATE TABLE copy_regression_default1(a text not null default 'a default data', b text not null default 'b default data') distributed by(a); -CREATE TABLE copy_regression_default2(a text, b serial, c text) distributed by(a); -NOTICE: CREATE TABLE will create implicit sequence "copy_regression_default2_b_seq" for serial column "copy_regression_default2.b" -CREATE TABLE copy_regression_default3(a serial, b text, c text) distributed by(a); -NOTICE: CREATE TABLE will create implicit sequence "copy_regression_default3_a_seq" for serial column "copy_regression_default3.a" --- constant defaults on key and non key attributes -COPY copy_regression_default1(a) from stdin; -COPY copy_regression_default1(b) from stdin; -SELECT * FROM copy_regression_default1; - a | b -----------------+---------------- - a data | b default data - a default data | b data -(2 rows) - --- non constant default on non hash key in both text and csv -COPY copy_regression_default2(a,c) from stdin; -COPY copy_regression_default2(a,c) from stdin csv; -SELECT * FROM copy_regression_default2; - a | b | c ---------+---+--------- - a data | 1 | c data - a data | 2 | c data - a data | 3 | c data - a data | 4 | c data - a data | 5 | c data - a data | 6 | c data -(6 rows) - --- non constant default on hash key in both text and csv -COPY copy_regression_default3(b,c) from stdin; -COPY copy_regression_default3(b,c) from stdin csv; -SELECT * FROM copy_regression_default2; - a | b | c ---------+---+--------- - a data | 1 | c data - a data | 2 | c data - a data | 3 | c data - a data | 4 | c data - a data | 5 | c data - a data | 6 | c data -(6 rows) - -DROP TABLE copy_regression_default1; -DROP TABLE copy_regression_default2; -DROP TABLE copy_regression_default3; --- ###################################################### --- COPY with OIDS --- ###################################################### -CREATE TABLE copy_regression_oids(a text) WITH OIDS; -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -COPY copy_regression_oids from stdin with oids delimiter '|'; -COPY copy_regression_oids from stdin with oids csv; -SELECT * FROM copy_regression_oids ORDER BY oid; - a -------------- - a text data - a text data - a text data - a text data -(4 rows) - -DROP TABLE copy_regression_oids; --- ###################################################### --- COPY OUT --- ###################################################### -CREATE TABLE copy_regression_out1(a text, b varchar, c int[], d bool) distributed by(a); --- populating table with data for copy out tests. NOTE: since we can't control the order --- of rows COPY OUT produces from the segdb, we must have only one row for each test table. -COPY copy_regression_out1 from stdin null 'nullval'; --- copy out default parameters text format.. -COPY copy_regression_out1 to stdout; -a copyout data line 2 \N {2,2,2} t --- copy out '|' delimiter 'mynull' null.. -COPY copy_regression_out1 to stdout with delimiter '|' null 'mynull'; -a copyout data line 2|mynull|{2,2,2}|t --- copy out '|' delimiter "i'm null" null.. -COPY copy_regression_out1 to stdout with delimiter '|' null 'i''m null'; -a copyout data line 2|i'm null|{2,2,2}|t --- copy out default parameters csv format.. -COPY copy_regression_out1 to stdout with csv; -a copyout data line 2,,"{2,2,2}",t --- copy out default parameters csv format with header.. -COPY copy_regression_out1 to stdout with csv header; -a,b,c,d -a copyout data line 2,,"{2,2,2}",t --- copy out partial and mixed column list (c,a,b).. -COPY copy_regression_out1(c,a,b) to stdout; -{2,2,2} a copyout data line 2 \N --- copy out csv default quotes and escapes and special cases.. -COPY copy_regression_out1 to stdout with csv quote ''''; -- escape should be the same as quote -a copyout data line 2,,'{2,2,2}',t -COPY copy_regression_out1 to stdout with csv escape ''''; -a copyout data line 2,,"{2,2,2}",t -COPY copy_regression_out1 to stdout with csv quote '''' escape E'\\'; -- make sure E'' is accepted -a copyout data line 2,,'{2,2,2}',t -DROP TABLE copy_regression_out1; --- ###################################################### --- Zero column table --- ###################################################### -CREATE TABLE copy_regression_nocol(); -NOTICE: Table has no attributes to distribute on. --- copy in and out of zero column table.. -COPY copy_regression_nocol from stdin; -COPY copy_regression_nocol from stdin; -ERROR: extra data after last expected column -CONTEXT: COPY copy_regression_nocol, line 1: "we should get an "extra data" error here" -COPY copy_regression_nocol from stdin with csv; -COPY copy_regression_nocol from stdin with csv; -- should fail -ERROR: extra data after last expected column -CONTEXT: COPY copy_regression_nocol, line 1: "we should get an "extra data" error here" -COPY copy_regression_nocol to stdout; -COPY copy_regression_nocol to stdout with csv; -DROP TABLE copy_regression_nocol; --- ###################################################### --- Various text format escape and default format tests --- ###################################################### --- for text format -CREATE TABLE copy_regression_text1(a varchar ,b varchar, c text, d text, e text) distributed by(a); -CREATE TABLE copy_regression_text2(a varchar ,b varchar, c text, d text, e text) distributed by(a); -CREATE TABLE copy_regression_text3(a varchar ,b varchar, c text, d text, e text) distributed by(a); --- column order tests -COPY copy_regression_text1 from stdin; -COPY copy_regression_text1(a,b,c,d,e) from stdin; -COPY copy_regression_text1(e,d,c,b,a) from stdin; -COPY copy_regression_text1(c,a,b,e,d) from stdin; -COPY copy_regression_text1(a,c) from stdin; -SELECT * FROM copy_regression_text1; - a | b | c | d | e --------------+-------------+-------------+-------------+------------- - a text data | b text data | c text data | d text data | e text data - a text data | b text data | c text data | d text data | e text data - a text data | b text data | c text data | d text data | e text data - a text data | b text data | c text data | d text data | e text data - a text data | | c text data | | -(5 rows) - --- null print tests -COPY copy_regression_text2 from stdin; -COPY copy_regression_text2 from stdin with null 'nullvalue'; -COPY copy_regression_text2 from stdin with delimiter '|' null ''; -COPY copy_regression_text2 from stdin with delimiter '|' null 'i''m null'; -SELECT * FROM copy_regression_text2; - a | b | c | d | e --------------+-------------+-------------+---+------------- - a text data | | c text data | | e text data - a text data | | c text data | | - a text data | | c text data | | e text data - a text data | b text data | | | e text data - | b text data | c text data | | e text data -(5 rows) - --- escape tests -COPY copy_regression_text3 from stdin with delimiter '|' escape '#'; -COPY copy_regression_text3 from stdin with delimiter '|' escape 'off'; -COPY copy_regression_text3 from stdin with delimiter '|'; -COPY copy_regression_text3 from stdin with delimiter '|'; -ERROR: missing data for column "b" -CONTEXT: COPY copy_regression_text3, line 1: "an embedded linefeed is not supported\" -COPY copy_regression_text3 from stdin with delimiter '|'; -SELECT * FROM copy_regression_text3 ORDER BY b; - a | b | c | d | e -------------------------------------+---+-------------+-------------+-------------- - the at sign: @ | 1 | c text data | d text data | e text data - an embedded delimiter | character | 2 | c text data | d text data | e text data - a single backslash \ in col a | 3 | c text data | d text data | e text data - a single backslash \ in col a | 4 | c text data | d text data | e text data - c:\\file\data\neew\path | 5 | c text data | d text data | e text data - the at sign: @ | 6 | c text data | d text data | e text data - an embedded linefeed sequence | 7 | c text data | d text data | e text data - in column a - a single backslash \ in col a | 8 | c text data | d text data | e text data -(8 rows) - -DROP TABLE copy_regression_text1; -DROP TABLE copy_regression_text2; -DROP TABLE copy_regression_text3; --- ###################################################### --- Various text format escape and default format tests --- ###################################################### --- for csv format -CREATE TABLE copy_regression_csv1(a varchar ,b varchar, c text, d text, e text) distributed by(a); -CREATE TABLE copy_regression_csv2(a varchar ,b varchar, c text, d text, e text) distributed by(a); -CREATE TABLE copy_regression_csv3(a varchar ,b varchar, c text, d text, e text) distributed by(a); --- column order tests -COPY copy_regression_csv1 from stdin with csv; -COPY copy_regression_csv1(a,b,c,d,e) from stdin with csv; -COPY copy_regression_csv1(e,d,c,b,a) from stdin with csv; -COPY copy_regression_csv1(c,a,b,e,d) from stdin with csv; -COPY copy_regression_csv1(a,c) from stdin with csv; -SELECT * FROM copy_regression_csv1; - a | b | c | d | e --------------+-------------+-------------+-------------+------------- - a csv data | b csv data | c csv data | d csv data | e csv data - a csv data | b csv data | c csv data | d csv data | e csv data - a csv data | b csv data | c csv data | d csv data | e csv data - a csv data | b csv data | c csv data | d csv data | e csv data - a csv data | | c csv data | | -(5 rows) - --- null print tests -COPY copy_regression_csv2 from stdin with null E'\\N' csv ; -COPY copy_regression_csv2 from stdin with null 'nullvalue' csv; -COPY copy_regression_csv2 from stdin with delimiter '|' null '' csv; -SELECT * FROM copy_regression_csv2; - a | b | c | d | e --------------+-------------+-------------+---+------------- - a csv data | | c csv data | | e csv data - a csv data | | c csv data | | - a csv data | | c csv data | | e csv data - | b csv data | c csv data | | e csv data -(4 rows) - --- escape tests -COPY copy_regression_csv3 from stdin with csv escape E'\\'; -COPY copy_regression_csv3 from stdin with delimiter E'\t' csv; --default quote and escape - " -COPY copy_regression_csv3 from stdin with delimiter '|' csv quote ';' escape '*'; --- check defaults -COPY copy_regression_csv3 from stdin with csv quote ''''; -- escape should be the same as quote -COPY copy_regression_csv3 from stdin with csv escape ''''; -COPY copy_regression_csv3 from stdin with csv quote '''' escape E'\\'; -- make sure E'' is accepted -SELECT * FROM copy_regression_csv3 ORDER BY b; - a | b | c | d | e ----------------------------------------------------+----+-------------+-------------+-------------- - an embedded delimiter (comma), is printed | 01 | c csv data | d csv data | e csv data - an embedded quote (doubleq)" is printed | 02 | c csv data | d csv data | e csv data - an embedded escape \ is printed | 03 | c csv data | d csv data | e csv data - an embedded line feed | 04 | c csv data | d csv data | e csv data - is printed - an embedded delimiter (tab) is printed | 05 | c csv data | d csv data | e csv data - an embedded quote or escape (doubleq)" is printed | 06 | c csv data | d csv data | e csv data - an embedded line feed | 07 | c csv data | d csv data | e csv data - is printed - an embedded delimiter (pipe)| is printed | 08 | c csv data | d csv data | e csv data - an embedded quote (semicolon); is printed | 09 | c csv data | d csv data | e csv data - an embedded escape (asterisk)* is printed | 10 | c csv data | d csv data | e csv data - an embedded line feed | 11 | c csv data | d csv data | e csv data - is printed - an embedded single quote ' here | 12 | c csv data | d csv data | e csv data - an embedded single quote ' here | 13 | c csv data | d csv data | e csv data - an embedded backslash \ here | 14 | c csv data | d csv data | e csv data -(14 rows) - -DROP TABLE copy_regression_csv1; -DROP TABLE copy_regression_csv2; -DROP TABLE copy_regression_csv3; --- ###################################################### --- FILL MISSING FIELDS --- ###################################################### -CREATE TABLE copy_regression_fill1(a int, b int, c text) distributed by(a); -CREATE TABLE copy_regression_fill2(a int, b int, c text) distributed by(c); --- text -COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; -COPY copy_regression_fill1(c,b) from stdin with delimiter '|' fill missing fields; -COPY copy_regression_fill2(a,c) from stdin with delimiter '|' fill missing fields; -SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; - a | b | c ----+---+------- - 1 | 1 | one - 2 | 2 | - 3 | | - | 1 | one - | | three - | | two -(6 rows) - -SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; - a | b | c ----+---+------- - 1 | | one - 2 | | - 3 | | three -(3 rows) - -TRUNCATE copy_regression_fill1; -TRUNCATE copy_regression_fill2; --- csv -COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; -COPY copy_regression_fill1(c,b) from stdin with csv delimiter '|' fill missing fields; -COPY copy_regression_fill2(a,c) from stdin with csv delimiter '|' fill missing fields; -SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; - a | b | c ----+---+------- - 1 | 1 | one - 2 | 2 | - 3 | | - | 1 | one - | | three - | | two -(6 rows) - -SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; - a | b | c ----+---+------- - 1 | | one - 2 | | - 3 | | three -(3 rows) - --- empty row should fail -COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; -ERROR: missing data for column "b", found empty data line -CONTEXT: COPY copy_regression_fill1, line 1: "" -COPY copy_regression_fill2 from stdin with delimiter '|' fill missing fields; -ERROR: missing data for column "b", found empty data line -CONTEXT: COPY copy_regression_fill2, line 1: "" -COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; -ERROR: missing data for column "b", found empty data line -CONTEXT: COPY copy_regression_fill1, line 1: "" -COPY copy_regression_fill2 from stdin with csv delimiter '|' fill missing fields; -ERROR: missing data for column "b", found empty data line -CONTEXT: COPY copy_regression_fill2, line 1: "" -DROP TABLE copy_regression_fill1; -DROP TABLE copy_regression_fill2; --- ###################################################### --- FORCE NOT NULL --- ###################################################### -CREATE TABLE copy_regression_fnn(a text, b text, c text) distributed by(a); -COPY copy_regression_fnn from stdin with csv; -SELECT * FROM copy_regression_fnn WHERE b is null order by a; - a | b | c ------+---+----- - one | | one - two | | -(2 rows) - -SELECT * FROM copy_regression_fnn WHERE c is null order by a; - a | b | c ------+---+--- - two | | -(1 row) - -TRUNCATE copy_regression_fnn; -COPY copy_regression_fnn from stdin with csv force not null b; -SELECT * FROM copy_regression_fnn WHERE b is null order by a; - a | b | c ----+---+--- -(0 rows) - -SELECT * FROM copy_regression_fnn WHERE c is null order by a; - a | b | c ------+---+--- - two | | -(1 row) - -TRUNCATE copy_regression_fnn; -COPY copy_regression_fnn from stdin with csv force not null b,c; -SELECT * FROM copy_regression_fnn WHERE b is null order by a; - a | b | c ----+---+--- -(0 rows) - -SELECT * FROM copy_regression_fnn WHERE c is null order by a; - a | b | c ----+---+--- -(0 rows) - -TRUNCATE copy_regression_fnn; --- now combine with fill missing fields -COPY copy_regression_fnn from stdin with csv fill missing fields force not null b; -SELECT * FROM copy_regression_fnn WHERE b is null order by a; - a | b | c ----+---+--- -(0 rows) - -SELECT * FROM copy_regression_fnn WHERE c is null order by a; - a | b | c ------+---+--- - two | | -(1 row) - -DROP TABLE copy_regression_fnn; --- ########################################################### --- distributed data error consolidation + original row numbers --- ########################################################### -CREATE TABLE copy_regression_error1(a int, b int) distributed by(a); --- parse error on QE (extra column on line 6) -COPY copy_regression_error1 from stdin; -ERROR: extra data after last expected column (seg3 minithump2:50004 pid=16198) -CONTEXT: COPY copy_regression_error1, line 6: "6 6 6" --- parse error on QD (missing column on line 3) -COPY copy_regression_error1 from stdin; -ERROR: missing data for column "b" -CONTEXT: COPY copy_regression_error1, line 3: "3" --- convert error on QD (invalid type line 2) -COPY copy_regression_error1 from stdin; -ERROR: invalid input syntax for integer: "two" -CONTEXT: COPY copy_regression_error1, line 2, column a --- convert error on QE (invalid type line 5) -COPY copy_regression_error1 from stdin; -ERROR: invalid input syntax for integer: "five" (seg2 minithump2:50003 pid=16196) -CONTEXT: COPY copy_regression_error1, line 5, column b -DROP TABLE copy_regression_error1; --- ###################################################### --- NEWLINE --- ###################################################### -CREATE TABLE copy_regression_newline(a text, b text) distributed by(a); --- positive: text -COPY copy_regression_newline from stdin with delimiter '|' newline 'lf'; --- positive: csv -COPY copy_regression_newline from stdin with delimiter '|' newline 'lf' csv; --- negative: text -COPY copy_regression_newline from stdin with delimiter '|' newline 'cr'; -ERROR: extra data after last expected column -CONTEXT: COPY copy_regression_newline, line 1: "1|1 -2|2 -" --- negative: csv -COPY copy_regression_newline from stdin with delimiter '|' newline 'cr' csv; -ERROR: extra data after last expected column -CONTEXT: COPY copy_regression_newline, line 1: "1|1 -2|2 -" --- negative: invalid newline -COPY copy_regression_newline from stdin with delimiter '|' newline 'blah'; -ERROR: invalid value for NEWLINE (blah) -HINT: valid options are: 'LF', 'CRLF', 'CR' --- negative: newline not yet supported for COPY TO -COPY copy_regression_newline to stdout with delimiter '|' newline 'blah'; -ERROR: newline currently available for data loading only, not unloading -DROP TABLE copy_regression_newline; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b3934fd/src/test/regress/known_good_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule index ab43b95..d408439 100755 --- a/src/test/regress/known_good_schedule +++ b/src/test/regress/known_good_schedule @@ -138,7 +138,6 @@ ignore: gpdiffcheck ignore: custom_format ignore: resource_queue ignore: gptokencheck -test: gpcopy ignore: sreh ignore: olap_setup ignore: olap_group http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b3934fd/src/test/regress/parallel_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 92f5d4c..a610932 100755 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -63,7 +63,6 @@ ignore: tablespace test: gpdiffcheck test: resource_queue test: gptokencheck -test: gpcopy test: sreh test: olap_setup test: olap_group http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b3934fd/src/test/regress/serial_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 7257fc0..f790608 100755 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -107,6 +107,5 @@ test: returning test: stats ignore: tablespace test: resource_queue -test: gpcopy test: sreh test: appendonly http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b3934fd/src/test/regress/sql/gpcopy.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/gpcopy.sql b/src/test/regress/sql/gpcopy.sql deleted file mode 100644 index fc5dc04..0000000 --- a/src/test/regress/sql/gpcopy.sql +++ /dev/null @@ -1,566 +0,0 @@ --- --- This test suite tests COPY code that is unique to greenplum db. --- - --- ###################################################### --- different distribution columns and distribution types --- ###################################################### - -CREATE TABLE copy_regression_hash1(a text, b text, c text) distributed by (b); -CREATE TABLE copy_regression_hash2(a text, b text, c text) distributed by (a,c); -CREATE TABLE copy_regression_hash3(a varchar, b int, c int, d text) distributed by (a,c,d); -CREATE TABLE copy_regression_hash4(a int[], b text) distributed by (a); -CREATE TABLE copy_regression_hash5(a text[][]) distributed by (a); -CREATE TABLE copy_regression_hash6(a int[], b text[]) distributed by (a,b); -CREATE TABLE copy_regression_hash7(a text,b text) distributed randomly; - --- single key, not first - -COPY copy_regression_hash1 from stdin segment reject limit 10 rows; -a data b data c data -\. -COPY copy_regression_hash1(b,c,a) from stdin segment reject limit 10 rows; -b data c data a data -\. -SELECT * FROM copy_regression_hash1; - --- two keys - -COPY copy_regression_hash2 from stdin; -a data b data c data -\. -COPY copy_regression_hash2(b,c,a) from stdin; -b data c data a data -\. -SELECT * FROM copy_regression_hash2; - --- three keys - -COPY copy_regression_hash3 from stdin; -a data 2 3 d data -\. -COPY copy_regression_hash3(c,d,b,a) from stdin; -3 d data 2 a data -\. -COPY copy_regression_hash3(a,c) from stdin; -a data 3 -\. -COPY copy_regression_hash3(d) from stdin; -d data -\. -SELECT * FROM copy_regression_hash3; - --- hash on int array - -COPY copy_regression_hash4 from stdin; -{1,2,3,4,5} hashed on an integer array -{10,20,30,40,50} hashed on an integer array -\. -SELECT * FROM copy_regression_hash4 ORDER BY a; - --- hash on 2 dim varchar array - -COPY copy_regression_hash5 from stdin; -{{hashing,on},{two dimentional,text array}} -\. -SELECT * FROM copy_regression_hash5 ORDER BY a; - --- hash on int array and varchar array - -COPY copy_regression_hash6 from stdin; -{1,2,3,4,5} {hashing,on,intarray,and,varchararray} -\. -SELECT * FROM copy_regression_hash6 ORDER BY a; - --- hash randomly - -COPY copy_regression_hash7 from stdin; -a data b data -a data b data -\. -SELECT * FROM copy_regression_hash7; - -DROP TABLE copy_regression_hash1; -DROP TABLE copy_regression_hash2; -DROP TABLE copy_regression_hash3; -DROP TABLE copy_regression_hash4; -DROP TABLE copy_regression_hash5; -DROP TABLE copy_regression_hash6; -DROP TABLE copy_regression_hash7; - --- ###################################################### --- default values and default functions --- ###################################################### - -CREATE TABLE copy_regression_default1(a text not null default 'a default data', b text not null default 'b default data') distributed by(a); -CREATE TABLE copy_regression_default2(a text, b serial, c text) distributed by(a); -CREATE TABLE copy_regression_default3(a serial, b text, c text) distributed by(a); - --- constant defaults on key and non key attributes - -COPY copy_regression_default1(a) from stdin; -a data -\. -COPY copy_regression_default1(b) from stdin; -b data -\. -SELECT * FROM copy_regression_default1; - --- non constant default on non hash key in both text and csv - -COPY copy_regression_default2(a,c) from stdin; -a data c data -a data c data -a data c data -\. -COPY copy_regression_default2(a,c) from stdin csv; -a data,c data -a data,c data -a data,c data -\. -SELECT * FROM copy_regression_default2; - --- non constant default on hash key in both text and csv - -COPY copy_regression_default3(b,c) from stdin; -b data c data -b data c data -b data c data -\. -COPY copy_regression_default3(b,c) from stdin csv; -b data,c data -b data,c data -b data,c data -\. -SELECT * FROM copy_regression_default2; - -DROP TABLE copy_regression_default1; -DROP TABLE copy_regression_default2; -DROP TABLE copy_regression_default3; - --- ###################################################### --- COPY with OIDS --- ###################################################### - -CREATE TABLE copy_regression_oids(a text) WITH OIDS; - -COPY copy_regression_oids from stdin with oids delimiter '|'; -50000|a text data -50001|a text data -\. -COPY copy_regression_oids from stdin with oids csv; -60000,a text data -60001,a text data -\. -SELECT * FROM copy_regression_oids ORDER BY oid; - -DROP TABLE copy_regression_oids; - - - --- ###################################################### --- COPY OUT --- ###################################################### - -CREATE TABLE copy_regression_out1(a text, b varchar, c int[], d bool) distributed by(a); - --- populating table with data for copy out tests. NOTE: since we can't control the order --- of rows COPY OUT produces from the segdb, we must have only one row for each test table. - -COPY copy_regression_out1 from stdin null 'nullval'; -a copyout data line 2 nullval {2,2,2} true -\. - --- copy out default parameters text format.. -COPY copy_regression_out1 to stdout; - --- copy out '|' delimiter 'mynull' null.. -COPY copy_regression_out1 to stdout with delimiter '|' null 'mynull'; - --- copy out '|' delimiter "i'm null" null.. -COPY copy_regression_out1 to stdout with delimiter '|' null 'i''m null'; - --- copy out default parameters csv format.. -COPY copy_regression_out1 to stdout with csv; - --- copy out default parameters csv format with header.. -COPY copy_regression_out1 to stdout with csv header; - --- copy out partial and mixed column list (c,a,b).. -COPY copy_regression_out1(c,a,b) to stdout; - --- copy out csv default quotes and escapes and special cases.. -COPY copy_regression_out1 to stdout with csv quote ''''; -- escape should be the same as quote -COPY copy_regression_out1 to stdout with csv escape ''''; -COPY copy_regression_out1 to stdout with csv quote '''' escape E'\\'; -- make sure E'' is accepted - -DROP TABLE copy_regression_out1; - - --- ###################################################### --- Zero column table --- ###################################################### - -CREATE TABLE copy_regression_nocol(); - --- copy in and out of zero column table.. -COPY copy_regression_nocol from stdin; -\. -COPY copy_regression_nocol from stdin; -we should get an "extra data" error here -\. -COPY copy_regression_nocol from stdin with csv; -\. -COPY copy_regression_nocol from stdin with csv; -- should fail -we should get an "extra data" error here -\. -COPY copy_regression_nocol to stdout; -COPY copy_regression_nocol to stdout with csv; - -DROP TABLE copy_regression_nocol; - - --- ###################################################### --- Various text format escape and default format tests --- ###################################################### - --- for text format -CREATE TABLE copy_regression_text1(a varchar ,b varchar, c text, d text, e text) distributed by(a); -CREATE TABLE copy_regression_text2(a varchar ,b varchar, c text, d text, e text) distributed by(a); -CREATE TABLE copy_regression_text3(a varchar ,b varchar, c text, d text, e text) distributed by(a); - --- column order tests - -COPY copy_regression_text1 from stdin; -a text data b text data c text data d text data e text data -\. -COPY copy_regression_text1(a,b,c,d,e) from stdin; -a text data b text data c text data d text data e text data -\. -COPY copy_regression_text1(e,d,c,b,a) from stdin; -e text data d text data c text data b text data a text data -\. -COPY copy_regression_text1(c,a,b,e,d) from stdin; -c text data a text data b text data e text data d text data -\. -COPY copy_regression_text1(a,c) from stdin; -a text data c text data -\. -SELECT * FROM copy_regression_text1; - --- null print tests - -COPY copy_regression_text2 from stdin; -a text data \N c text data \N e text data -\. -COPY copy_regression_text2 from stdin with null 'nullvalue'; -a text data nullvalue c text data nullvalue nullvalue -\. -COPY copy_regression_text2 from stdin with delimiter '|' null ''; -a text data||c text data||e text data -|b text data|c text data||e text data -\. -COPY copy_regression_text2 from stdin with delimiter '|' null 'i''m null'; -a text data|b text data|i'm null|i'm null|e text data -\. -SELECT * FROM copy_regression_text2; - --- escape tests - -COPY copy_regression_text3 from stdin with delimiter '|' escape '#'; -the at sign: #100 |1|c text data|d text data|e text data -an embedded delimiter #| character |2|c text data|d text data|e text data -a single backslash \ in col a|3|c text data|d text data|e text data -\. -COPY copy_regression_text3 from stdin with delimiter '|' escape 'off'; -a single backslash \ in col a|4|c text data|d text data|e text data -c:\\file\data\neew\path|5|c text data|d text data|e text data -\. -COPY copy_regression_text3 from stdin with delimiter '|'; -the at sign: \100|6|c text data|d text data|e text data -a single backslash \\ in col a|8|c text data|d text data|e text data -\. -COPY copy_regression_text3 from stdin with delimiter '|'; -an embedded linefeed is not supported\ -and another one that should fail\ -in column a|7|c text data|d text data|e text data -\. -COPY copy_regression_text3 from stdin with delimiter '|'; -an embedded linefeed sequence\nin column a|7|c text data|d text data|e text data -\. - -SELECT * FROM copy_regression_text3 ORDER BY b; - -DROP TABLE copy_regression_text1; -DROP TABLE copy_regression_text2; -DROP TABLE copy_regression_text3; - - - --- ###################################################### --- Various text format escape and default format tests --- ###################################################### - --- for csv format -CREATE TABLE copy_regression_csv1(a varchar ,b varchar, c text, d text, e text) distributed by(a); -CREATE TABLE copy_regression_csv2(a varchar ,b varchar, c text, d text, e text) distributed by(a); -CREATE TABLE copy_regression_csv3(a varchar ,b varchar, c text, d text, e text) distributed by(a); - --- column order tests - -COPY copy_regression_csv1 from stdin with csv; -a csv data,b csv data,c csv data,d csv data,e csv data -\. -COPY copy_regression_csv1(a,b,c,d,e) from stdin with csv; -a csv data,b csv data,c csv data,d csv data,e csv data -\. -COPY copy_regression_csv1(e,d,c,b,a) from stdin with csv; -e csv data,d csv data,c csv data,b csv data,a csv data -\. -COPY copy_regression_csv1(c,a,b,e,d) from stdin with csv; -c csv data,a csv data,b csv data,e csv data,d csv data -\. -COPY copy_regression_csv1(a,c) from stdin with csv; -a csv data,c csv data -\. -SELECT * FROM copy_regression_csv1; - --- null print tests - -COPY copy_regression_csv2 from stdin with null E'\\N' csv ; -a csv data,\N,c csv data,\N,e csv data -\. -COPY copy_regression_csv2 from stdin with null 'nullvalue' csv; -a csv data,nullvalue,c csv data,nullvalue,nullvalue -\. -COPY copy_regression_csv2 from stdin with delimiter '|' null '' csv; -a csv data||c csv data||e csv data -|b csv data|c csv data||e csv data -\. -SELECT * FROM copy_regression_csv2; - --- escape tests - -COPY copy_regression_csv3 from stdin with csv escape E'\\'; -"an embedded delimiter (comma), is printed",01,c csv data,d csv data,e csv data -"an embedded quote (doubleq)\" is printed",02,c csv data,d csv data,e csv data -"an embedded escape \\ is printed",03,c csv data,d csv data,e csv data -"an embedded line feed -is printed",04,c csv data,d csv data,e csv data -\. -COPY copy_regression_csv3 from stdin with delimiter E'\t' csv; --default quote and escape - " -"an embedded delimiter (tab) is printed" 05 c csv data d csv data e csv data -"an embedded quote or escape (doubleq)"" is printed" 06 c csv data d csv data e csv data -"an embedded line feed -is printed" 07 c csv data d csv data e csv data -\. -COPY copy_regression_csv3 from stdin with delimiter '|' csv quote ';' escape '*'; -;an embedded delimiter (pipe)| is printed;|08|c csv data|d csv data|e csv data -;an embedded quote (semicolon)*; is printed;|09|c csv data|d csv data|e csv data -;an embedded escape (asterisk)** is printed;|10|c csv data|d csv data|e csv data -;an embedded line feed -is printed;|11|c csv data|d csv data|e csv data -\. - --- check defaults -COPY copy_regression_csv3 from stdin with csv quote ''''; -- escape should be the same as quote -'an embedded single quote '' here',12,c csv data,d csv data,e csv data -\. -COPY copy_regression_csv3 from stdin with csv escape ''''; -"an embedded single quote '' here",13,c csv data,d csv data,e csv data -\. -COPY copy_regression_csv3 from stdin with csv quote '''' escape E'\\'; -- make sure E'' is accepted -'an embedded backslash \\ here',14,c csv data,d csv data,e csv data -\. -SELECT * FROM copy_regression_csv3 ORDER BY b; - -DROP TABLE copy_regression_csv1; -DROP TABLE copy_regression_csv2; -DROP TABLE copy_regression_csv3; - --- ###################################################### --- FILL MISSING FIELDS --- ###################################################### - -CREATE TABLE copy_regression_fill1(a int, b int, c text) distributed by(a); -CREATE TABLE copy_regression_fill2(a int, b int, c text) distributed by(c); - --- text -COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; -1|1|one -2|2 -3 -\. -COPY copy_regression_fill1(c,b) from stdin with delimiter '|' fill missing fields; -one|1 -two -three -\. -COPY copy_regression_fill2(a,c) from stdin with delimiter '|' fill missing fields; -1|one -2 -3|three -\. -SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; -SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; -TRUNCATE copy_regression_fill1; -TRUNCATE copy_regression_fill2; - --- csv -COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; -1|1|one -2|2 -3 -\. -COPY copy_regression_fill1(c,b) from stdin with csv delimiter '|' fill missing fields; -one|1 -two -three -\. -COPY copy_regression_fill2(a,c) from stdin with csv delimiter '|' fill missing fields; -1|one -2 -3|three -\. -SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; -SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; - --- empty row should fail -COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; - -\. -COPY copy_regression_fill2 from stdin with delimiter '|' fill missing fields; - -\. -COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; - -\. -COPY copy_regression_fill2 from stdin with csv delimiter '|' fill missing fields; - -\. -DROP TABLE copy_regression_fill1; -DROP TABLE copy_regression_fill2; - --- ###################################################### --- FORCE NOT NULL --- ###################################################### - -CREATE TABLE copy_regression_fnn(a text, b text, c text) distributed by(a); - -COPY copy_regression_fnn from stdin with csv; -one,,one -two,, -\. -SELECT * FROM copy_regression_fnn WHERE b is null order by a; -SELECT * FROM copy_regression_fnn WHERE c is null order by a; -TRUNCATE copy_regression_fnn; - -COPY copy_regression_fnn from stdin with csv force not null b; -one,,one -two,, -\. -SELECT * FROM copy_regression_fnn WHERE b is null order by a; -SELECT * FROM copy_regression_fnn WHERE c is null order by a; -TRUNCATE copy_regression_fnn; - -COPY copy_regression_fnn from stdin with csv force not null b,c; -one,,one -two,, -\. -SELECT * FROM copy_regression_fnn WHERE b is null order by a; -SELECT * FROM copy_regression_fnn WHERE c is null order by a; -TRUNCATE copy_regression_fnn; - --- now combine with fill missing fields -COPY copy_regression_fnn from stdin with csv fill missing fields force not null b; -one,,one -two, -\. -SELECT * FROM copy_regression_fnn WHERE b is null order by a; -SELECT * FROM copy_regression_fnn WHERE c is null order by a; - -DROP TABLE copy_regression_fnn; - --- ########################################################### --- distributed data error consolidation + original row numbers --- ########################################################### - -CREATE TABLE copy_regression_error1(a int, b int) distributed by(a); - --- parse error on QE (extra column on line 6) - -COPY copy_regression_error1 from stdin; -1 1 -2 2 -3 3 -4 4 -5 5 -6 6 6 -7 7 -\. - --- parse error on QD (missing column on line 3) -COPY copy_regression_error1 from stdin; -1 1 -2 2 -3 -4 4 -\. - --- convert error on QD (invalid type line 2) - -COPY copy_regression_error1 from stdin; -1 1 -two 2 -3 3 -\. - --- convert error on QE (invalid type line 5) -COPY copy_regression_error1 from stdin; -1 1 -2 2 -3 3 -4 4 -5 five -6 6 -7 7 -\. - -DROP TABLE copy_regression_error1; - --- ###################################################### --- NEWLINE --- ###################################################### - -CREATE TABLE copy_regression_newline(a text, b text) distributed by(a); - --- positive: text -COPY copy_regression_newline from stdin with delimiter '|' newline 'lf'; -1|1 -2|2 -\. - --- positive: csv -COPY copy_regression_newline from stdin with delimiter '|' newline 'lf' csv; -1|1 -2|2 -\. - --- negative: text -COPY copy_regression_newline from stdin with delimiter '|' newline 'cr'; -1|1 -2|2 -\. - --- negative: csv -COPY copy_regression_newline from stdin with delimiter '|' newline 'cr' csv; -1|1 -2|2 -\. - --- negative: invalid newline -COPY copy_regression_newline from stdin with delimiter '|' newline 'blah'; --- negative: newline not yet supported for COPY TO -COPY copy_regression_newline to stdout with delimiter '|' newline 'blah'; - -DROP TABLE copy_regression_newline; \ No newline at end of file