hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From y...@apache.org
Subject [01/10] incubator-hawq git commit: HAWQ-898. Add feature test for COPY with new test framework.
Date Tue, 26 Jul 2016 12:06:13 GMT
Repository: incubator-hawq
Updated Branches:
  refs/heads/master faf50470b -> b59603125


http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b5960312/src/test/feature/utility/sql/copy-stdio.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/utility/sql/copy-stdio.sql b/src/test/feature/utility/sql/copy-stdio.sql
new file mode 100644
index 0000000..fce93ad
--- /dev/null
+++ b/src/test/feature/utility/sql/copy-stdio.sql
@@ -0,0 +1,131 @@
+create temp table copytest3 (
+	c1 int, 
+	"col with , comma" text, 
+	"col with "" quote"  int) distributed by (c1);
+
+copy copytest3 from stdin csv header;
+this is just a line full of junk that would error out if parsed
+1,a,1
+2,b,2
+\.
+
+copy copytest3 to stdout csv header;
+-- copy with error table
+CREATE TABLE number (a INT) DISTRIBUTED BY (a);
+
+COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
+these are invalid line should be insert into error table.
+a
+b
+c
+d
+e
+f
+g
+h
+\.
+
+select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum;
+select * from number; --should be empty
+\d err_copy
+
+DROP TABLE err_copy;
+
+COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
+these are invalid line should be insert into error table.
+a
+1
+b
+2
+c
+3
+d
+4
+e
+5
+f
+6
+g
+7
+h
+\.
+
+select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum;
+select count(*) from number; --should be 7
+DROP TABLE err_copy;
+
+TRUNCATE number;
+
+COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
+these are invalid line should be insert into error table.
+a
+1
+b
+2
+c
+3
+d
+4
+e
+5
+f
+6
+g
+7
+h
+i
+\.
+
+select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum; -- should not exist
+select count(*) from number; --should be empty
+
+TRUNCATE number;
+CREATE TABLE err_copy (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea) distributed randomly;
+
+COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
+these are invalid line should be insert into error table.
+a
+1
+b
+2
+c
+3
+d
+4
+e
+5
+f
+6
+g
+7
+h
+\.
+
+select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum;
+select count(*) from number; --should be 7
+DROP TABLE err_copy;
+
+-- invalid error table schema
+TRUNCATE number;
+create table invalid_error_table1 (a int) distributed randomly;
+create table invalid_error_table3 (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea)
+		distributed by (cmdtime);
+		
+COPY number FROM STDIN LOG ERRORS INTO invalid_error_table1 SEGMENT REJECT LIMIT 10 ROWS; -- should fail
+these are invalid line should be insert into error table.
+1
+\.
+
+;
+
+COPY number FROM STDIN LOG ERRORS INTO invalid_error_table3 SEGMENT REJECT LIMIT 10 ROWS; -- should fail
+these are invalid line should be insert into error table.
+1
+\.
+
+;
+
+DROP TABLE invalid_error_table1;
+DROP TABLE invalid_error_table3;
+
+DROP TABLE number;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b5960312/src/test/feature/utility/sql/gpcopy.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/utility/sql/gpcopy.sql b/src/test/feature/utility/sql/gpcopy.sql
new file mode 100644
index 0000000..6c48586
--- /dev/null
+++ b/src/test/feature/utility/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/b5960312/src/test/feature/utility/test_cmd.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/utility/test_cmd.cpp b/src/test/feature/utility/test_cmd.cpp
new file mode 100644
index 0000000..69c9cbd
--- /dev/null
+++ b/src/test/feature/utility/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("utility/sql/gpcopy.sql",
+                  "utility/ans/gpcopy.ans");
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b5960312/src/test/feature/utility/test_copy.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/utility/test_copy.cpp b/src/test/feature/utility/test_copy.cpp
new file mode 100644
index 0000000..e534a56
--- /dev/null
+++ b/src/test/feature/utility/test_copy.cpp
@@ -0,0 +1,212 @@
+#include "gtest/gtest.h"
+
+#include "lib/sql_util.h"
+
+class TestCopy: public ::testing::Test {
+ public:
+  TestCopy() {}
+  ~TestCopy() {}
+};
+
+TEST_F(TestCopy, TestCOPY) {
+  hawq::test::SQLUtility util;
+
+  // prepare
+  util.execute("DROP TABLE IF EXISTS aggtest CASCADE");
+  util.execute("DROP TABLE IF EXISTS tenk1 CASCADE");
+  util.execute("DROP TABLE IF EXISTS slow_emp4000 CASCADE");
+  util.execute("DROP TABLE IF EXISTS person CASCADE");
+  util.execute("DROP TABLE IF EXISTS onek CASCADE");
+  util.execute("DROP TABLE IF EXISTS emp CASCADE");
+  util.execute("DROP TABLE IF EXISTS student CASCADE");
+  util.execute("DROP TABLE IF EXISTS stud_emp CASCADE");
+  util.execute("DROP TABLE IF EXISTS real_city CASCADE");
+  util.execute("DROP TABLE IF EXISTS road CASCADE");
+  util.execute("DROP TABLE IF EXISTS hash_i4_heap CASCADE");
+  util.execute("DROP TABLE IF EXISTS hash_name_heap CASCADE");
+  util.execute("DROP TABLE IF EXISTS hash_txt_heap CASCADE");
+  util.execute("DROP TABLE IF EXISTS hash_f8_heap CASCADE");
+  util.execute("DROP TABLE IF EXISTS bt_i4_heap CASCADE");
+  util.execute("DROP TABLE IF EXISTS bt_name_heap CASCADE");
+  util.execute("DROP TABLE IF EXISTS bt_txt_heap CASCADE");
+  util.execute("DROP TABLE IF EXISTS bt_f8_heap CASCADE");
+  util.execute("DROP TABLE IF EXISTS array_op_test CASCADE");
+  util.execute("DROP TABLE IF EXISTS array_index_op_test CASCADE");
+
+  util.execute("CREATE TABLE aggtest (a int2, b float4)");
+
+  util.execute("CREATE TABLE tenk1 (unique1     int4,"
+    							   "unique2     int4,"
+    							   "two         int4,"
+    							   "four        int4,"
+    							   "ten         int4,"
+    							   "twenty      int4,"
+    							   "hundred     int4,"
+    							   "thousand    int4,"
+    							   "twothousand int4,"
+    							   "fivethous   int4,"
+    							   "tenthous    int4,"
+    							   "odd         int4,"
+    							   "even        int4,"
+    							   "stringu1    name,"
+    							   "stringu2    name,"
+    							   "string4     name) WITH OIDS");
+
+  util.execute("CREATE TABLE slow_emp4000 (home_base  box)");
+
+  util.execute("CREATE TABLE person (name        text,"
+		                            "age         int4,"
+		                            "location    point)");
+
+  util.execute("CREATE TABLE onek (unique1     int4,"
+                                  "unique2     int4,"
+                                  "two         int4,"
+                                  "four        int4,"
+                                  "ten         int4,"
+                                  "twenty      int4,"
+                                  "hundred     int4,"
+                                  "thousand    int4,"
+                                  "twothousand int4,"
+                                  "fivethous   int4,"
+                                  "tenthous    int4,"
+                                  "odd         int4,"
+                                  "even        int4,"
+                                  "stringu1    name,"
+                                  "stringu2    name,"
+                                  "string4     name)");
+
+  util.execute("CREATE TABLE emp (salary      int4,"
+                                 "manager     name)"
+                                 " INHERITS (person) WITH OIDS");
+
+  util.execute("CREATE TABLE student (gpa float8) INHERITS (person)");
+
+  util.execute("CREATE TABLE stud_emp (percent int4) INHERITS (emp, student)");
+
+  util.execute("CREATE TABLE real_city (pop         int4,"
+                                       "cname       text,"
+                                       "outline     path)");
+
+  util.execute("CREATE TABLE road (name        text,"
+		                          "thepath     path)");
+
+
+  util.execute("CREATE TABLE hash_i4_heap (seqno       int4,"
+                                          "random      int4)");
+
+  util.execute("CREATE TABLE hash_name_heap (seqno       int4,"
+                                            "random      name)");
+
+  util.execute("CREATE TABLE hash_txt_heap (seqno       int4,"
+                                           "random      text)");
+
+  util.execute("CREATE TABLE hash_f8_heap (seqno       int4,"
+                                          "random      float8)");
+
+  util.execute("CREATE TABLE bt_i4_heap (seqno       int4,"
+                                        "random      int4)");
+
+  util.execute("CREATE TABLE bt_name_heap (seqno       name,"
+                                          "random      int4)");
+
+  util.execute("CREATE TABLE bt_txt_heap (seqno       text,"
+                                         "random      int4)");
+
+  util.execute("CREATE TABLE bt_f8_heap (seqno       float8,"
+                                        "random      int4)");
+
+  util.execute("CREATE TABLE array_op_test (seqno       int4,"
+                                           "i           int4[],"
+                                           "t           text[])");
+
+  util.execute("CREATE TABLE array_index_op_test (seqno       int4,"
+                                                 "i           int4[],"
+                                                 "t           text[])");
+
+  // test
+  std::string path = util.getTestRootPath();
+  util.execute("COPY aggtest FROM '" + path + "/utility/data/agg.data'");
+  util.execute("COPY onek FROM '" + path + "/utility/data/onek.data'");
+  util.execute("COPY onek TO '" + path + "/utility/ans/onek.data'");
+  util.execute("TRUNCATE onek");
+  util.execute("COPY onek FROM '" + path + "/utility/ans/onek.data'");
+  util.execute("COPY tenk1 FROM '" + path + "/utility/data/tenk.data'");
+  util.execute("COPY slow_emp4000 FROM '" + path + "/utility/data/rect.data'");
+  util.execute("COPY person FROM '" + path + "/utility/data/person.data'");
+  util.execute("COPY emp FROM '" + path + "/utility/data/emp.data'");
+  util.execute("COPY student FROM '" + path + "/utility/data/student.data'");
+  util.execute("COPY stud_emp FROM '" + path + "/utility/data/stud_emp.data'");
+  util.execute("COPY road FROM '" + path + "/utility/data/streets.data'");
+  util.execute("COPY real_city FROM '" + path + "/utility/data/real_city.data'");
+  util.execute("COPY hash_i4_heap FROM '" + path + "/utility/data/hash.data'");
+  util.execute("COPY hash_name_heap FROM '" + path + "/utility/data/hash.data'");
+  util.execute("COPY hash_txt_heap FROM '" + path + "/utility/data/hash.data'");
+  util.execute("COPY hash_f8_heap FROM '" + path + "/utility/data/hash.data'");
+
+  util.execute("COPY bt_i4_heap FROM '" + path + "/utility/data/desc.data'");
+  util.execute("COPY bt_name_heap FROM '" + path + "/utility/data/hash.data'");
+  util.execute("COPY bt_txt_heap FROM '" + path + "/utility/data/desc.data'");
+  util.execute("COPY bt_f8_heap FROM '" + path + "/utility/data/hash.data'");
+  util.execute("COPY array_op_test FROM '" + path + "/utility/data/array.data'");
+  util.execute("COPY array_index_op_test FROM '" + path + "/utility/data/array.data'");
+
+  // cleanup
+  util.execute("DROP TABLE array_index_op_test");
+  util.execute("DROP TABLE array_op_test");
+  util.execute("DROP TABLE bt_f8_heap");
+  util.execute("DROP TABLE bt_txt_heap");
+  util.execute("DROP TABLE bt_name_heap");
+  util.execute("DROP TABLE bt_i4_heap");
+  util.execute("DROP TABLE hash_f8_heap");
+  util.execute("DROP TABLE hash_txt_heap");
+  util.execute("DROP TABLE hash_name_heap");
+  util.execute("DROP TABLE hash_i4_heap");
+  util.execute("DROP TABLE road");
+  util.execute("DROP TABLE real_city");
+  util.execute("DROP TABLE stud_emp");
+  util.execute("DROP TABLE student");
+  util.execute("DROP TABLE emp");
+  util.execute("DROP TABLE onek");
+  util.execute("DROP TABLE person");
+  util.execute("DROP TABLE slow_emp4000");
+  util.execute("DROP TABLE tenk1");
+  util.execute("DROP TABLE aggtest");
+}
+
+TEST_F(TestCopy, TestCOPY2) {
+  hawq::test::SQLUtility util;
+
+  // prepare
+  util.execute("DROP TABLE IF EXISTS copytest CASCADE");
+  util.execute("DROP TABLE IF EXISTS copytest2 CASCADE");
+
+  // test
+  std::string path = util.getTestRootPath();
+  util.execute("CREATE TABLE copytest (style text, "
+                                      "test text,"
+                                      "filler int)");
+  util.execute("INSERT INTO copytest VALUES('DOS',E'abc\r\ndef',1)");
+  util.execute("INSERT INTO copytest VALUES('Unix',E'abc\r\ndef',2)");
+  util.execute("INSERT INTO copytest VALUES('Mac', E'abc\rdef',3)");
+  util.execute("INSERT INTO copytest VALUES(E'esc\\ape', E'a\\r\\\r\\\n\\nb',4)");
+
+  util.execute("COPY copytest TO '" + path + "/utility/ans/copytest.csv' CSV");
+  util.execute("CREATE TABLE copytest2 (like copytest)");
+  util.execute("COPY copytest2 FROM '" + path + "/utility/ans/copytest.csv' CSV");
+  util.query("SELECT * FROM copytest EXCEPT SELECT * FROM copytest2", "");
+  util.execute("TRUNCATE copytest2");
+
+  util.execute("COPY copytest to '" + path + "/utility/ans/copytest.csv' CSV "
+		       "QUOTE '''' ESCAPE E'\\\\\\\\'");
+  util.execute("COPY copytest2 FROM '" + path + "/utility/ans/copytest.csv' CSV "
+		       "QUOTE '''' ESCAPE E'\\\\\\\\'");
+  util.query("SELECT * FROM copytest EXCEPT SELECT * FROM copytest2", "");
+
+  util.execSQLFile("utility/sql/copy-stdio.sql",
+		  	  	   "utility/ans/copy-stdio.ans");
+  // clean up
+  util.execute("DROP TABLE copytest");
+  util.execute("DROP TABLE copytest2");
+}
+
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b5960312/src/test/regress/input/copy.source
----------------------------------------------------------------------
diff --git a/src/test/regress/input/copy.source b/src/test/regress/input/copy.source
deleted file mode 100755
index ccab99b..0000000
--- a/src/test/regress/input/copy.source
+++ /dev/null
@@ -1,224 +0,0 @@
---
--- COPY
---
--- CLASS POPULATION
---	(any resemblance to real life is purely coincidental)
---
-COPY aggtest FROM '@abs_srcdir@/data/agg.data';
-
-COPY onek FROM '@abs_srcdir@/data/onek.data';
-
-COPY onek TO '@abs_builddir@/results/onek.data';
-
-TRUNCATE onek;
-
-COPY onek FROM '@abs_builddir@/results/onek.data';
-
-COPY tenk1 FROM '@abs_srcdir@/data/tenk.data';
-
-COPY slow_emp4000 FROM '@abs_srcdir@/data/rect.data';
-
-COPY person FROM '@abs_srcdir@/data/person.data';
-
-COPY emp FROM '@abs_srcdir@/data/emp.data';
-
-COPY student FROM '@abs_srcdir@/data/student.data';
-
-COPY stud_emp FROM '@abs_srcdir@/data/stud_emp.data';
-
-COPY road FROM '@abs_srcdir@/data/streets.data';
-
-COPY real_city FROM '@abs_srcdir@/data/real_city.data';
-
-COPY hash_i4_heap FROM '@abs_srcdir@/data/hash.data';
-
-COPY hash_name_heap FROM '@abs_srcdir@/data/hash.data';
-
-COPY hash_txt_heap FROM '@abs_srcdir@/data/hash.data';
-
-COPY hash_f8_heap FROM '@abs_srcdir@/data/hash.data';
-
--- the data in this file has a lot of duplicates in the index key
--- fields, leading to long bucket chains and lots of table expansion.
--- this is therefore a stress test of the bucket overflow code (unlike
--- the data in hash.data, which has unique index keys).
---
--- COPY hash_ovfl_heap FROM '@abs_srcdir@/data/hashovfl.data';
-
-COPY bt_i4_heap FROM '@abs_srcdir@/data/desc.data';
-
-COPY bt_name_heap FROM '@abs_srcdir@/data/hash.data';
-
-COPY bt_txt_heap FROM '@abs_srcdir@/data/desc.data';
-
-COPY bt_f8_heap FROM '@abs_srcdir@/data/hash.data';
-
-COPY array_op_test FROM '@abs_srcdir@/data/array.data';
-
-COPY array_index_op_test FROM '@abs_srcdir@/data/array.data';
-
---- test copying in CSV mode with various styles
---- of embedded line ending characters
-
-create temp table copytest (
-	style	text,
-	test 	text,
-	filler	int);
-
-insert into copytest values('DOS',E'abc\r\ndef',1);
-insert into copytest values('Unix',E'abc\ndef',2);
-insert into copytest values('Mac',E'abc\rdef',3);
-insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
-
-copy copytest to '@abs_builddir@/results/copytest.csv' csv;
-
-create temp table copytest2 (like copytest);
-
-copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
-
-select * from copytest except select * from copytest2 order by 1,2,3;
-
-truncate copytest2;
-
---- same test but with an escape char different from quote char
-
-copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
-
-copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
-
-select * from copytest except select * from copytest2 order by 1,2,3;
-
-
--- test header line feature
-
-create temp table copytest3 (
-	c1 int, 
-	"col with , comma" text, 
-	"col with "" quote"  int) distributed by (c1);
-
-copy copytest3 from stdin csv header;
-this is just a line full of junk that would error out if parsed
-1,a,1
-2,b,2
-\.
-
-copy copytest3 to stdout csv header;
--- copy with error table
-CREATE TABLE number (a INT) DISTRIBUTED BY (a);
-
-COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
-these are invalid line should be insert into error table.
-a
-b
-c
-d
-e
-f
-g
-h
-\.
-
-select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum;
-select * from number; --should be empty
-\d err_copy
-
-DROP TABLE err_copy;
-
-COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
-these are invalid line should be insert into error table.
-a
-1
-b
-2
-c
-3
-d
-4
-e
-5
-f
-6
-g
-7
-h
-\.
-
-select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum;
-select count(*) from number; --should be 7
-DROP TABLE err_copy;
-
-TRUNCATE number;
-
-COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
-these are invalid line should be insert into error table.
-a
-1
-b
-2
-c
-3
-d
-4
-e
-5
-f
-6
-g
-7
-h
-i
-\.
-
-select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum; -- should not exist
-select count(*) from number; --should be empty
-
-TRUNCATE number;
-CREATE TABLE err_copy (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea) distributed randomly;
-
-COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
-these are invalid line should be insert into error table.
-a
-1
-b
-2
-c
-3
-d
-4
-e
-5
-f
-6
-g
-7
-h
-\.
-
-select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum;
-select count(*) from number; --should be 7
-DROP TABLE err_copy;
-
--- invalid error table schema
-TRUNCATE number;
-create table invalid_error_table1 (a int) distributed randomly;
-create table invalid_error_table3 (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea)
-		distributed by (cmdtime);
-		
-COPY number FROM STDIN LOG ERRORS INTO invalid_error_table1 SEGMENT REJECT LIMIT 10 ROWS; -- should fail
-these are invalid line should be insert into error table.
-1
-\.
-
-;
-
-COPY number FROM STDIN LOG ERRORS INTO invalid_error_table3 SEGMENT REJECT LIMIT 10 ROWS; -- should fail
-these are invalid line should be insert into error table.
-1
-\.
-
-;
-
-DROP TABLE invalid_error_table1;
-DROP TABLE invalid_error_table3;
-
-DROP TABLE number;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b5960312/src/test/regress/known_good_schedule
----------------------------------------------------------------------
diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule
index 8c9091f..dcb6a7a 100755
--- a/src/test/regress/known_good_schedule
+++ b/src/test/regress/known_good_schedule
@@ -44,7 +44,6 @@ ignore: horology
 ignore: create_type
 test: create_table_test
 ignore: create_function_2
-test: copy
 ignore: copyselect
 ignore: constraints
 ignore: triggers

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b5960312/src/test/regress/output/copy.source
----------------------------------------------------------------------
diff --git a/src/test/regress/output/copy.source b/src/test/regress/output/copy.source
deleted file mode 100755
index d250166..0000000
--- a/src/test/regress/output/copy.source
+++ /dev/null
@@ -1,214 +0,0 @@
---
--- COPY
---
--- CLASS POPULATION
---	(any resemblance to real life is purely coincidental)
---
-COPY aggtest FROM '@abs_srcdir@/data/agg.data';
-COPY onek FROM '@abs_srcdir@/data/onek.data';
-COPY onek TO '@abs_builddir@/results/onek.data';
-TRUNCATE onek;
-COPY onek FROM '@abs_builddir@/results/onek.data';
-COPY tenk1 FROM '@abs_srcdir@/data/tenk.data';
-COPY slow_emp4000 FROM '@abs_srcdir@/data/rect.data';
-COPY person FROM '@abs_srcdir@/data/person.data';
-COPY emp FROM '@abs_srcdir@/data/emp.data';
-COPY student FROM '@abs_srcdir@/data/student.data';
-COPY stud_emp FROM '@abs_srcdir@/data/stud_emp.data';
-COPY road FROM '@abs_srcdir@/data/streets.data';
-COPY real_city FROM '@abs_srcdir@/data/real_city.data';
-COPY hash_i4_heap FROM '@abs_srcdir@/data/hash.data';
-COPY hash_name_heap FROM '@abs_srcdir@/data/hash.data';
-COPY hash_txt_heap FROM '@abs_srcdir@/data/hash.data';
-COPY hash_f8_heap FROM '@abs_srcdir@/data/hash.data';
--- the data in this file has a lot of duplicates in the index key
--- fields, leading to long bucket chains and lots of table expansion.
--- this is therefore a stress test of the bucket overflow code (unlike
--- the data in hash.data, which has unique index keys).
---
--- COPY hash_ovfl_heap FROM '@abs_srcdir@/data/hashovfl.data';
-COPY bt_i4_heap FROM '@abs_srcdir@/data/desc.data';
-COPY bt_name_heap FROM '@abs_srcdir@/data/hash.data';
-COPY bt_txt_heap FROM '@abs_srcdir@/data/desc.data';
-COPY bt_f8_heap FROM '@abs_srcdir@/data/hash.data';
-COPY array_op_test FROM '@abs_srcdir@/data/array.data';
-COPY array_index_op_test FROM '@abs_srcdir@/data/array.data';
---- test copying in CSV mode with various styles
---- of embedded line ending characters
-create temp table copytest (
-	style	text,
-	test 	text,
-	filler	int);
-insert into copytest values('DOS',E'abc\r\ndef',1);
-insert into copytest values('Unix',E'abc\ndef',2);
-insert into copytest values('Mac',E'abc\rdef',3);
-insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
-copy copytest to '@abs_builddir@/results/copytest.csv' csv;
-create temp table copytest2 (like copytest);
-copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
-select * from copytest except select * from copytest2 order by 1,2,3;
- style | test | filler 
--------+------+--------
-(0 rows)
-
-truncate copytest2;
---- same test but with an escape char different from quote char
-copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
-copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
-select * from copytest except select * from copytest2 order by 1,2,3;
- style | test | filler 
--------+------+--------
-(0 rows)
-
--- test header line feature
-create temp table copytest3 (
-	c1 int, 
-	"col with , comma" text, 
-	"col with "" quote"  int) distributed by (c1);
-copy copytest3 from stdin csv header;
-copy copytest3 to stdout csv header;
-c1,"col with , comma","col with "" quote"
-1,a,1
-2,b,2
--- copy with error table
-CREATE TABLE number (a INT) DISTRIBUTED BY (a);
-COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
-NOTICE:  Error table "err_copy" does not exist. Auto generating an error table with the same name
-WARNING:  The error table was created in the same transaction as this operation. It will get dropped if transaction rolls back even if bad rows are present
-HINT:  To avoid this create the error table ahead of time using: CREATE TABLE <name> (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea)
-NOTICE:  Found 9 data formatting errors (9 or more input rows). Errors logged into error table "err_copy"
-select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum;
- relname | filename | linenum | bytenum |                                                 errmsg                                                  |                          rawdata                          | rawbytes 
----------+----------+---------+---------+---------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+----------
- number  | <stdin>  |       1 |         | invalid input syntax for integer: "these are invalid line should be insert into error table.", column a | these are invalid line should be insert into error table. | 
- number  | <stdin>  |       2 |         | invalid input syntax for integer: "a", column a                                                         | a                                                         | 
- number  | <stdin>  |       3 |         | invalid input syntax for integer: "b", column a                                                         | b                                                         | 
- number  | <stdin>  |       4 |         | invalid input syntax for integer: "c", column a                                                         | c                                                         | 
- number  | <stdin>  |       5 |         | invalid input syntax for integer: "d", column a                                                         | d                                                         | 
- number  | <stdin>  |       6 |         | invalid input syntax for integer: "e", column a                                                         | e                                                         | 
- number  | <stdin>  |       7 |         | invalid input syntax for integer: "f", column a                                                         | f                                                         | 
- number  | <stdin>  |       8 |         | invalid input syntax for integer: "g", column a                                                         | g                                                         | 
- number  | <stdin>  |       9 |         | invalid input syntax for integer: "h", column a                                                         | h                                                         | 
-(9 rows)
-
-select * from number; --should be empty
- a 
----
-(0 rows)
-
-\d err_copy
-       Append-Only Table "public.err_copy"
-  Column  |           Type           | Modifiers 
-----------+--------------------------+-----------
- cmdtime  | timestamp with time zone | 
- relname  | text                     | 
- filename | text                     | 
- linenum  | integer                  | 
- bytenum  | integer                  | 
- errmsg   | text                     | 
- rawdata  | text                     | 
- rawbytes | bytea                    | 
-Compression Type: None
-Compression Level: 0
-Block Size: 32768
-Checksum: f
-Distributed randomly
-
-DROP TABLE err_copy;
-COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
-NOTICE:  Error table "err_copy" does not exist. Auto generating an error table with the same name
-WARNING:  The error table was created in the same transaction as this operation. It will get dropped if transaction rolls back even if bad rows are present
-HINT:  To avoid this create the error table ahead of time using: CREATE TABLE <name> (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea)
-NOTICE:  Found 9 data formatting errors (9 or more input rows). Errors logged into error table "err_copy"
-select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum;
- relname | filename | linenum | bytenum |                                                 errmsg                                                  |                          rawdata                          | rawbytes 
----------+----------+---------+---------+---------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+----------
- number  | <stdin>  |       1 |         | invalid input syntax for integer: "these are invalid line should be insert into error table.", column a | these are invalid line should be insert into error table. | 
- number  | <stdin>  |       2 |         | invalid input syntax for integer: "a", column a                                                         | a                                                         | 
- number  | <stdin>  |       4 |         | invalid input syntax for integer: "b", column a                                                         | b                                                         | 
- number  | <stdin>  |       6 |         | invalid input syntax for integer: "c", column a                                                         | c                                                         | 
- number  | <stdin>  |       8 |         | invalid input syntax for integer: "d", column a                                                         | d                                                         | 
- number  | <stdin>  |      10 |         | invalid input syntax for integer: "e", column a                                                         | e                                                         | 
- number  | <stdin>  |      12 |         | invalid input syntax for integer: "f", column a                                                         | f                                                         | 
- number  | <stdin>  |      14 |         | invalid input syntax for integer: "g", column a                                                         | g                                                         | 
- number  | <stdin>  |      16 |         | invalid input syntax for integer: "h", column a                                                         | h                                                         | 
-(9 rows)
-
-select count(*) from number; --should be 7
- count 
--------
-     7
-(1 row)
-
-DROP TABLE err_copy;
-TRUNCATE number;
-COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
-NOTICE:  Error table "err_copy" does not exist. Auto generating an error table with the same name
-WARNING:  The error table was created in the same transaction as this operation. It will get dropped if transaction rolls back even if bad rows are present
-HINT:  To avoid this create the error table ahead of time using: CREATE TABLE <name> (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea)
-ERROR:  Segment reject limit reached. Aborting operation. Last error was: invalid input syntax for integer: "i", column a
-CONTEXT:  COPY number, line 17, column a
-select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum; -- should not exist
-ERROR:  relation "err_copy" does not exist
-LINE 1: ...name,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy o...
-                                                             ^
-select count(*) from number; --should be empty
- count 
--------
-     0
-(1 row)
-
-TRUNCATE number;
-CREATE TABLE err_copy (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea) distributed randomly;
-COPY number FROM STDIN LOG ERRORS INTO err_copy SEGMENT REJECT LIMIT 10 ROWS;
-NOTICE:  Found 9 data formatting errors (9 or more input rows). Errors logged into error table "err_copy"
-select relname,filename,linenum,bytenum,errmsg,rawdata,rawbytes from err_copy order by linenum;
- relname | filename | linenum | bytenum |                                                 errmsg                                                  |                          rawdata                          | rawbytes 
----------+----------+---------+---------+---------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+----------
- number  | <stdin>  |       1 |         | invalid input syntax for integer: "these are invalid line should be insert into error table.", column a | these are invalid line should be insert into error table. | 
- number  | <stdin>  |       2 |         | invalid input syntax for integer: "a", column a                                                         | a                                                         | 
- number  | <stdin>  |       4 |         | invalid input syntax for integer: "b", column a                                                         | b                                                         | 
- number  | <stdin>  |       6 |         | invalid input syntax for integer: "c", column a                                                         | c                                                         | 
- number  | <stdin>  |       8 |         | invalid input syntax for integer: "d", column a                                                         | d                                                         | 
- number  | <stdin>  |      10 |         | invalid input syntax for integer: "e", column a                                                         | e                                                         | 
- number  | <stdin>  |      12 |         | invalid input syntax for integer: "f", column a                                                         | f                                                         | 
- number  | <stdin>  |      14 |         | invalid input syntax for integer: "g", column a                                                         | g                                                         | 
- number  | <stdin>  |      16 |         | invalid input syntax for integer: "h", column a                                                         | h                                                         | 
-(9 rows)
-
-select count(*) from number; --should be 7
- count 
--------
-     7
-(1 row)
-
-DROP TABLE err_copy;
--- invalid error table schema
-TRUNCATE number;
-create table invalid_error_table1 (a int) distributed randomly;
-create table invalid_error_table3 (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea)
-		distributed by (cmdtime);
-		
-COPY number FROM STDIN LOG ERRORS INTO invalid_error_table1 SEGMENT REJECT LIMIT 10 ROWS; -- should fail
-ERROR:  Relation "invalid_error_table1" already exists and is not of a valid error table format (expected 8 attributes, found 1)
-these are invalid line should be insert into error table.
-1
-\.
-invalid command \.
-;
-ERROR:  syntax error at or near "these"
-LINE 1: these are invalid line should be insert into error table.
-        ^
-COPY number FROM STDIN LOG ERRORS INTO invalid_error_table3 SEGMENT REJECT LIMIT 10 ROWS; -- should fail
-ERROR:  Relation "invalid_error_table3" already exists and is not of a valid error table format. It appears to not distributed randomly
-these are invalid line should be insert into error table.
-1
-\.
-invalid command \.
-;
-ERROR:  syntax error at or near "these"
-LINE 1: these are invalid line should be insert into error table.
-        ^
-DROP TABLE invalid_error_table1;
-DROP TABLE invalid_error_table3;
-DROP TABLE number;


Mime
View raw message