You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by hu...@apache.org on 2016/07/04 02:35:56 UTC
[3/3] incubator-hawq git commit: HAWQ-805. Add feature test for
exttab1 with new framework
HAWQ-805. Add feature test for exttab1 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/a2fa04ff
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/a2fa04ff
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/a2fa04ff
Branch: refs/heads/master
Commit: a2fa04ff66f3e14861e390cafb74b885e7a0b991
Parents: b4993b2
Author: xunzhang <xu...@gmail.com>
Authored: Thu Jun 16 19:42:04 2016 +0800
Committer: Ruilong Huo <rh...@pivotal.io>
Committed: Mon Jul 4 10:38:00 2016 +0800
----------------------------------------------------------------------
.../ExternalSource/ans/exttab1.ans.source | 887 ++++++++++++
.../ExternalSource/data/missing_fields1.data | 5 +
.../ExternalSource/data/missing_fields2.data | 6 +
.../feature/ExternalSource/data/mpp12839_1.data | 2 +
.../feature/ExternalSource/data/mpp12839_2.data | 3 +
.../feature/ExternalSource/data/mpp12839_3.data | 2 +
.../feature/ExternalSource/data/mpp17980.data | 18 +
.../feature/ExternalSource/data/mpp6698.data | 3 +
src/test/feature/ExternalSource/data/region.tbl | 5 +
src/test/feature/ExternalSource/data/whois.csv | 1303 ++++++++++++++++++
.../feature/ExternalSource/sql/errortbl.source | 93 ++
.../ExternalSource/sql/exttab1.sql.source | 551 ++++++++
.../ExternalSource/sql_source/errortbl.source | 93 --
src/test/feature/ExternalSource/test_exttab.cpp | 35 +
14 files changed, 2913 insertions(+), 93 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2fa04ff/src/test/feature/ExternalSource/ans/exttab1.ans.source
----------------------------------------------------------------------
diff --git a/src/test/feature/ExternalSource/ans/exttab1.ans.source b/src/test/feature/ExternalSource/ans/exttab1.ans.source
new file mode 100644
index 0000000..e303438
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttab1.ans.source
@@ -0,0 +1,887 @@
+-- start_ignore
+SET SEARCH_PATH=TestExternalTable_TestExternalTableAll;
+SET
+-- end_ignore
+--
+-- external tables 1 - short and simple functional tests. The full set of tests
+-- exists in cdbunit.
+--
+-- start_matchsubs
+--
+-- # replace return code in error message (platform specific)
+--
+-- m/ERROR\:\s+external table .* command ended with .* not found/
+-- s/nosuchcommand\:\s*(command)? not found/nosuchcommand\: NOT FOUND/
+--
+-- m/ERROR\:\s+external table .* command ended with .*No such file.*/
+-- s/nosuchfile\.txt\:\s*No such file (or directory)?/nosuchfile\.txt\: NO SUCH FILE/
+-- m/ERROR\:\s+external table .* command ended with .*No such file.*/i
+-- s/cat\: (cannot open)? nosuchfile\.txt/cat\: nosuchfile\.txt/
+--
+-- # remove line number - redhat
+-- m/ERROR\:\s+external table .* command ended with .*NOT FOUND.*/i
+-- s/\s+line \d+\://
+-- # remove cannot open - solaris
+-- m/ERROR\:\s+external table .* command ended with .*cat\: cannot open.*/i
+-- s/cat\: cannot open (.*)$/cat\: $1\: NO SUCH FILE/
+--
+-- end_matchsubs
+SET gp_foreign_data_access = true;
+SET
+set optimizer_disable_missing_stats_collection = on;
+SET
+CREATE TABLE REG_REGION (R_REGIONKEY INT, R_NAME CHAR(25), R_COMMENT VARCHAR(152)) DISTRIBUTED BY (R_REGIONKEY);
+CREATE TABLE
+-- start_ignore
+-- --------------------------------------
+-- check platform
+-- --------------------------------------
+drop external web table if exists check_echo;
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:36: NOTICE: table "check_echo" does not exist, skipping
+DROP EXTERNAL TABLE
+CREATE EXTERNAL WEB TABLE check_echo (x text)
+execute E'(echo gpfdist)'
+on SEGMENT 0
+format 'text';
+CREATE EXTERNAL TABLE
+select * from check_echo;
+ x
+---------
+ gpfdist
+(1 row)
+
+-- end_ignore
+-- --------------------------------------
+-- 'gpfdist' protocol
+-- --------------------------------------
+CREATE EXTERNAL WEB TABLE gpfdist_status (x text)
+execute E'( python $GPHOME/bin/lib/gppinggpfdist.py @hostname@:7070 2>&1 || echo) '
+on SEGMENT 0
+FORMAT 'text' (delimiter '|');
+CREATE EXTERNAL TABLE
+CREATE EXTERNAL WEB TABLE gpfdist_start (x text)
+execute E'((@gpwhich_gpfdist@ -p 7070 -d @abs_srcdir@/data </dev/null >/dev/null 2>&1 &); sleep 2; echo "starting...") '
+on SEGMENT 0
+FORMAT 'text' (delimiter '|');
+CREATE EXTERNAL TABLE
+CREATE EXTERNAL WEB TABLE gpfdist_stop (x text)
+execute E'(/bin/pkill gpfdist || killall gpfdist) > /dev/null 2>&1; echo "stopping..."'
+on SEGMENT 0
+FORMAT 'text' (delimiter '|');
+CREATE EXTERNAL TABLE
+-- start_ignore
+select * from gpfdist_stop;
+ x
+-------------
+ stopping...
+(1 row)
+
+select * from gpfdist_status;
+ x
+------------------------------------------------------
+ Error: gpfdist is not running (reason: socket error)
+ Exit: 1
+
+(3 rows)
+
+select * from gpfdist_start;
+ x
+-------------
+ starting...
+(1 row)
+
+select * from gpfdist_status;
+ x
+-------------------------------------------------------------------------
+ Okay, gpfdist version "2.0.0.0 build dev" is running on @hostname@:7070.
+(1 row)
+
+-- end_ignore
+CREATE EXTERNAL TABLE EXT_NATION ( N_NATIONKEY INTEGER ,
+ N_NAME CHAR(25) ,
+ N_REGIONKEY INTEGER ,
+ N_COMMENT VARCHAR(152))
+location ('gpfdist://@hostname@:7070/nation.tbl' )
+FORMAT 'text' (delimiter '|');
+CREATE EXTERNAL TABLE
+CREATE EXTERNAL TABLE EXT_REGION (LIKE REG_REGION)
+location ('gpfdist://@hostname@:7070/region.tbl' )
+FORMAT 'text' (delimiter '|');
+CREATE EXTERNAL TABLE
+SELECT * FROM EXT_NATION;
+ n_nationkey | n_name | n_regionkey | n_comment
+-------------+---------------------------+-------------+--------------------------------------------------------------------------------------------------------------------
+ 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai
+ 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon
+ 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special
+ 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold
+ 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d
+ 5 | ETHIOPIA | 0 | ven packages wake quickly. regu
+ 6 | FRANCE | 3 | refully final requests. regular, ironi
+ 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco
+ 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun
+ 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull
+ 10 | IRAN | 4 | efully alongside of the slyly final dependencies.
+ 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula
+ 12 | JAPAN | 2 | ously. final, express gifts cajole a
+ 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa
+ 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t
+ 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets?
+ 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r
+ 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun
+ 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos
+ 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account
+ 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely
+ 21 | VIETNAM | 2 | hely enticingly express accounts. even, final
+ 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint
+ 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull
+ 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be
+(25 rows)
+
+SELECT * FROM EXT_REGION;
+ r_regionkey | r_name | r_comment
+-------------+---------------------------+---------------------------------------------------------------------------------------------------------------------
+ 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to
+ 1 | AMERICA | hs use ironic, even requests. s
+ 2 | ASIA | ges. thinly even pinto beans ca
+ 3 | EUROPE | ly final courts cajole furiously final excuse
+ 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl
+(5 rows)
+
+SELECT * FROM EXT_REGION as r, EXT_NATION as n WHERE n.N_REGIONKEY = r.R_REGIONKEY;
+ r_regionkey | r_name | r_comment | n_nationkey | n_name | n_regionkey | n_comment
+-------------+---------------------------+---------------------------------------------------------------------------------------------------------------------+-------------+---------------------------+-------------+--------------------------------------------------------------------------------------------------------------------
+ 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d
+ 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 10 | IRAN | 4 | efully alongside of the slyly final dependencies.
+ 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula
+ 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa
+ 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely
+ 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai
+ 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 5 | ETHIOPIA | 0 | ven packages wake quickly. regu
+ 2 | ASIA | ges. thinly even pinto beans ca | 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun
+ 2 | ASIA | ges. thinly even pinto beans ca | 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull
+ 2 | ASIA | ges. thinly even pinto beans ca | 12 | JAPAN | 2 | ously. final, express gifts cajole a
+ 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t
+ 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets?
+ 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r
+ 2 | ASIA | ges. thinly even pinto beans ca | 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos
+ 2 | ASIA | ges. thinly even pinto beans ca | 21 | VIETNAM | 2 | hely enticingly express accounts. even, final
+ 3 | EUROPE | ly final courts cajole furiously final excuse | 6 | FRANCE | 3 | refully final requests. regular, ironi
+ 3 | EUROPE | ly final courts cajole furiously final excuse | 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco
+ 3 | EUROPE | ly final courts cajole furiously final excuse | 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account
+ 3 | EUROPE | ly final courts cajole furiously final excuse | 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint
+ 3 | EUROPE | ly final courts cajole furiously final excuse | 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull
+ 1 | AMERICA | hs use ironic, even requests. s | 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon
+ 1 | AMERICA | hs use ironic, even requests. s | 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special
+ 1 | AMERICA | hs use ironic, even requests. s | 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold
+ 1 | AMERICA | hs use ironic, even requests. s | 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun
+ 1 | AMERICA | hs use ironic, even requests. s | 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be
+(25 rows)
+
+-- start_ignore
+select * from gpfdist_status;
+ x
+-------------------------------------------------------------------------
+ Okay, gpfdist version "2.0.0.0 build dev" is running on @hostname@:7070.
+(1 row)
+
+select * from gpfdist_stop;
+ x
+-------------
+ stopping...
+(1 row)
+
+select * from gpfdist_status;
+ x
+------------------------------------------------------
+ Error: gpfdist is not running (reason: socket error)
+ Exit: 1
+
+(3 rows)
+
+-- end_ignore
+-- drop tables
+DROP EXTERNAL TABLE EXT_NATION;
+DROP EXTERNAL TABLE
+DROP EXTERNAL TABLE EXT_REGION;
+DROP EXTERNAL TABLE
+--
+-- gpfdist in csv (mpp-1519, etc)
+--
+CREATE EXTERNAL WEB TABLE gpfdist_csv_start (x text)
+execute E'((@gpwhich_gpfdist@ -p 7070 -d @abs_srcdir@/data </dev/null >/dev/null 2>&1 &); sleep 2; echo "starting...") '
+on SEGMENT 0
+FORMAT 'text' (delimiter '|');
+CREATE EXTERNAL TABLE
+--
+-- "
+-- (end the double quote so emacs highlighting works correctly)
+--
+-- start_ignore
+select * from gpfdist_status;
+ x
+------------------------------------------------------
+ Error: gpfdist is not running (reason: socket error)
+ Exit: 1
+
+(3 rows)
+
+select * from gpfdist_stop;
+ x
+-------------
+ stopping...
+(1 row)
+
+select * from gpfdist_status;
+ x
+------------------------------------------------------
+ Error: gpfdist is not running (reason: socket error)
+ Exit: 1
+
+(3 rows)
+
+select * from gpfdist_csv_start;
+ x
+-------------
+ starting...
+(1 row)
+
+select * from gpfdist_status;
+ x
+-------------------------------------------------------------------------
+ Okay, gpfdist version "2.0.0.0 build dev" is running on @hostname@:7070.
+(1 row)
+
+-- end_ignore
+create external table ext_whois (
+source_lineno int,
+domain_name varchar(350),
+subdomain varchar(150),
+tld varchar(50),
+ip_address inet,
+ip_address_int bigint,
+reverse_dns varchar(512),
+reverse_domain varchar(350),
+registrar varchar(200),
+registrar_referral varchar(512),
+whois_server varchar(512),
+harvest_date timestamp with time zone,
+created_on timestamp with time zone,
+update_date timestamp with time zone,
+expire_date timestamp with time zone,
+rank int,
+status char(1),
+update_status int,
+nameserver1 varchar(512),
+nameserver1_domain_name varchar(350),
+nameserver1_subdomain varchar(150),
+nameserver1_tld varchar(50),
+nameserver2 varchar(512),
+nameserver2_domain_name varchar(350),
+nameserver2_subdomain varchar(150),
+nameserver2_tld varchar(50),
+nameserver3 varchar(512),
+nameserver3_domain_name varchar(350),
+nameserver3_subdomain varchar(150),
+nameserver3_tld varchar(50),
+nameserver4 varchar(512),
+nameserver4_domain_name varchar(350),
+nameserver4_subdomain varchar(150),
+nameserver4_tld varchar(50),
+nameserver5 varchar(512),
+nameserver5_domain_name varchar(350),
+nameserver5_subdomain varchar(150),
+nameserver5_tld varchar(50),
+registrant_name varchar(200),
+registrant_organization varchar(200),
+registrant_email varchar(512),
+registrant_email_domain varchar(350),
+registrant_email_subdomain varchar(150),
+registrant_email_tld varchar(50),
+registrant_phone varchar(50),
+registrant_fax varchar(50),
+registrant_addrall varchar(1024),
+registrant_street1 varchar(200),
+registrant_street2 varchar(200),
+registrant_street3 varchar(200),
+registrant_city varchar(200),
+registrant_state_province varchar(100),
+registrant_postal_code varchar(50),
+registrant_country varchar(100),
+tech_name varchar(200),
+tech_organization varchar(200),
+tech_email varchar(512),
+tech_email_domain varchar(350),
+tech_email_subdomain varchar(150),
+tech_email_tld varchar(50),
+tech_phone varchar(50),
+tech_fax varchar(50),
+tech_addrall varchar(1024),
+tech_street1 varchar(200),
+tech_street2 varchar(200),
+tech_street3 varchar(200),
+tech_city varchar(200),
+tech_state_province varchar(100),
+tech_postal_code varchar(50),
+tech_country varchar(100),
+admin_name varchar(200),
+admin_organization varchar(200),
+admin_email varchar(512),
+admin_email_domain varchar(350),
+admin_email_subdomain varchar(150),
+admin_email_tld varchar(50),
+admin_phone varchar(50),
+admin_fax varchar(50),
+admin_addrall varchar(1024),
+admin_street1 varchar(200),
+admin_street2 varchar(200),
+admin_street3 varchar(200),
+admin_city varchar(200),
+admin_state_province varchar(100),
+admin_postal_code varchar(50),
+admin_country varchar(100),
+rec_path varchar(512),
+raw_record text
+)
+location ('gpfdist://@hostname@:7070/whois.csv' )
+format 'csv' ( quote as '"' header);
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:207: NOTICE: HEADER means that each one of the data files has a header row.
+CREATE EXTERNAL TABLE
+-- "
+-- start_ignore
+select count(*) from ext_whois;
+ count
+-------
+ 23
+(1 row)
+
+-- end_ignore
+-- test FILL EMPTY FIELDS
+create external table ext_fill1(a int, b int, c int)
+location('gpfdist://@hostname@:7070/missing_fields1.data' )
+format 'text' ( delimiter '|' fill missing fields);
+CREATE EXTERNAL TABLE
+create external table ext_fill2(a int, b int, c int)
+location('gpfdist://@hostname@:7070/missing_fields2.data' )
+format 'text' ( delimiter '|' fill missing fields);
+CREATE EXTERNAL TABLE
+-- also test force not null in addition to fill empty fields
+create external table ext_fill3_fnn(a text, b text, c text)
+location('gpfdist://@hostname@:7070/missing_fields1.data' )
+format 'csv' ( delimiter '|' fill missing fields force not null c);
+CREATE EXTERNAL TABLE
+create external table ext_fill4_fnn(a text, b text, c text)
+location('gpfdist://@hostname@:7070/missing_fields1.data' )
+format 'csv' ( delimiter '|' fill missing fields force not null b,c);
+CREATE EXTERNAL TABLE
+select * from ext_fill1 order by a,b,c; -- should pad missing attrs with nulls
+ a | b | c
+---+---+---
+ 1 | 1 | 1
+ 2 | 2 |
+ 3 | |
+ 4 | 4 |
+ 5 | 5 | 5
+(5 rows)
+
+-- start_ignore
+select * from ext_fill2 order by a,b,c; -- should fail due to empty data line
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:233: ERROR: missing data for column "b", found empty data line (seg5 @hostname@:40000 pid=35893)
+DETAIL: External table ext_fill2, line 3 of gpfdist://@hostname@:7070/missing_fields2.data: ""
+-- end_ignore
+select * from ext_fill3_fnn where c is null; -- should be empty
+ a | b | c
+---+---+---
+(0 rows)
+
+select * from ext_fill3_fnn where b is null; -- one row result
+ a | b | c
+---+---+---
+ 3 | |
+(1 row)
+
+select * from ext_fill4_fnn where c is null or b is null; -- should be empty
+ a | b | c
+---+---+---
+(0 rows)
+
+drop external table ext_fill1;
+DROP EXTERNAL TABLE
+drop external table ext_fill2;
+DROP EXTERNAL TABLE
+drop external table ext_fill3_fnn;
+DROP EXTERNAL TABLE
+drop external table ext_fill4_fnn;
+DROP EXTERNAL TABLE
+-- test NEWLINE
+create external table ext_newline1(N_NATIONKEY INT, N_NAME text, N_REGIONKEY INT, N_COMMENT text)
+location('gpfdist://@hostname@:7070/nation.tbl' )
+format 'text' ( delimiter '|' newline 'lf');
+CREATE EXTERNAL TABLE
+create external table ext_newline2(like ext_newline1)
+location('gpfdist://@hostname@:7070/nation.tbl' )
+format 'text' ( delimiter '|' newline 'cr');
+CREATE EXTERNAL TABLE
+create external table ext_willfail(a int)
+location('gpfdist://@hostname@:7070/nation.tbl' )
+format 'text' ( delimiter '|' newline 'blah'); -- should fail with invalid newline.
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:255: ERROR: invalid value for NEWLINE (blah)
+HINT: valid options are: 'LF', 'CRLF', 'CR'
+select * from ext_newline1; -- should pass. using the correct linefeed. file has 'lf'.
+ n_nationkey | n_name | n_regionkey | n_comment
+-------------+----------------+-------------+--------------------------------------------------------------------------------------------------------------------
+ 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai
+ 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon
+ 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special
+ 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold
+ 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d
+ 5 | ETHIOPIA | 0 | ven packages wake quickly. regu
+ 6 | FRANCE | 3 | refully final requests. regular, ironi
+ 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco
+ 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun
+ 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull
+ 10 | IRAN | 4 | efully alongside of the slyly final dependencies.
+ 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula
+ 12 | JAPAN | 2 | ously. final, express gifts cajole a
+ 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa
+ 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t
+ 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets?
+ 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r
+ 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun
+ 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos
+ 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account
+ 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely
+ 21 | VIETNAM | 2 | hely enticingly express accounts. even, final
+ 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint
+ 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull
+ 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be
+(25 rows)
+
+-- start_ignore
+select * from ext_newline2; -- should fail. using an incorrect linefeed. file has 'lf'.
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:259: ERROR: extra data after last expected column (seg4 @hostname@:40000 pid=35898)
+DETAIL:
+External table ext_newline2, line 1 of gpfdist://@hostname@:7070/nation.tbl: "0|ALGERIA|0| haggle. carefully final deposits detect slyly agai
+1|ARGENTINA|1|al foxes promise slyly..."
+-- end_ignore
+drop external table ext_newline1;
+DROP EXTERNAL TABLE
+drop external table ext_newline2;
+DROP EXTERNAL TABLE
+-- MPP-6698
+create external table ext_mpp6698(a text)
+location('gpfdist://@hostname@:7070/mpp6698.data' )
+format 'csv' (quote '''');
+CREATE EXTERNAL TABLE
+select * from ext_mpp6698 order by a; -- should ignore the quotes
+ a
+----------------------------------------------------
+ first row
+ second row with embedded single quote ' here
+ third row with another embedded quote at the end '
+(3 rows)
+
+drop external table ext_mpp6698;
+DROP EXTERNAL TABLE
+-- MPP-12839
+create external table ext_mpp12839
+(
+ userguid varchar(36),
+ action_time timestamp without time zone,
+ action_type smallint,
+ object_id integer,
+ general_param character varying(8000),
+ plu_id smallint,
+ page_name character varying(2000),
+ browser integer,
+ platform integer,
+ filtering_data character varying(8000),
+ is_new_user boolean,
+ dummy character varying(1)
+)
+location('gpfdist://@hostname@:7070/mpp12839*.data' )
+format 'text' (delimiter E'\177' null '' newline 'crlf')
+segment reject limit 100 rows;
+CREATE EXTERNAL TABLE
+select * from ext_mpp12839; -- should not reach reject limit
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:293: NOTICE: Found 6 data formatting errors (6 or more input rows). Rejected related input data.
+ userguid | action_time | action_type | object_id | general_param | plu_id | page_name | browser | platform | filtering_data | is_new_user | dummy
+----------+-------------+-------------+-----------+---------------+--------+-----------+---------+----------+----------------+-------------+-------
+(0 rows)
+
+drop external table ext_mpp12839;
+DROP EXTERNAL TABLE
+-- --------------------------------------
+-- some negative tests
+-- --------------------------------------
+--
+-- test for exec child process stderr showing in error message
+--
+create external web table ext_stderr1(a text) execute 'nosuchcommand' ON 0 format 'text';
+CREATE EXTERNAL TABLE
+create external web table ext_stderr2(a text) execute 'cat nosuchfile.txt' ON 0 format 'text';
+CREATE EXTERNAL TABLE
+--
+-- bad csv (quote must be a single char)
+--
+create external table bad_whois (
+source_lineno int,
+domain_name varchar(350)
+)
+location ('gpfdist://@hostname@:7070/whois.csv' )
+format 'csv' ( header quote as 'ggg');
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:314: NOTICE: HEADER means that each one of the data files has a header row.
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:314: ERROR: quote must be a single character
+select count(*) from bad_whois;
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:315: ERROR: relation "bad_whois" does not exist
+LINE 1: select count(*) from bad_whois;
+ ^
+drop external table bad_whois;
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:316: ERROR: table "bad_whois" does not exist
+--
+-- try a bad protocol
+--
+create external table badt2 (x text)
+location ('bad_protocol://@hostname@@abs_srcdir@/data/no/such/place/badt2.tbl' )
+format 'text' (delimiter '|');
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:323: ERROR: protocol "bad_protocol" does not exist
+--
+-- ALTER
+--
+--create external table ext (a int, x text)
+--location ('gpfdist://@hostname@:7070@abs_srcdir@/data/no/such/place/badt1.tbl' )
+--format 'text';
+--alter foreign table ext drop column a; -- should fail (wrong object)
+--alter table ext drop column a; -- should pass with warning
+--alter external table ext add column a int;
+--alter external table ext drop column a;
+--alter external table ext add column extnewcol int not null; -- should fail (constraints not allowed)
+--alter external table ext add column extnewcol int;
+--alter external table ext alter column extnewcol set default 1; -- should fail (unsupported alter type)
+--
+-- TRUNCATE/UPDATE/DELETE/INSERT (INTO RET)
+--
+--truncate ext;
+--delete from ext;
+--update ext set x='1' where x='2';
+--insert into ext(x) values('a');
+--drop table ext; -- should fail (wrong object)
+--drop foreign table ext; -- should fail (wrong object)
+--drop external table ext;
+----------------------------------------------------------------------
+-- CUSTOM PROTOCOLS
+----------------------------------------------------------------------
+-- DROP EXTERNAL TABLE IF EXISTS ext_w;
+-- DROP EXTERNAL TABLE IF EXISTS ext_r;
+-- DROP ROLE IF EXISTS extprotu;
+-- CREATE OR REPLACE FUNCTION write_to_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_export' LANGUAGE C STABLE;
+-- CREATE OR REPLACE FUNCTION read_from_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_import' LANGUAGE C STABLE;
+-- DROP PROTOCOL IF EXISTS demoprot;
+-- CREATE TRUSTED PROTOCOL demoprot (readfunc = 'nosuchfunc'); -- should fail
+-- CREATE TRUSTED PROTOCOL demoprot (readfunc = 'boolin'); -- should fail
+-- CREATE TRUSTED PROTOCOL demoprot (readfunc = 'read_from_file', writefunc = 'write_to_file'); -- should succeed
+-- CREATE PROTOCOL demoprot_untrusted (readfunc = 'read_from_file', writefunc = 'write_to_file'); -- should succeed
+-- CREATE ROLE extprotu NOSUPERUSER;
+-- SET SESSION AUTHORIZATION extprotu;
+-- CREATE WRITABLE EXTERNAL TABLE ext_w(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should fail
+-- CREATE READABLE EXTERNAL TABLE ext_r(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should fail
+-- RESET SESSION AUTHORIZATION;
+-- ALTER PROTOCOL demoprot_untrusted OWNER TO extprotu; -- should fail. protocol is not trusted
+-- GRANT SELECT ON PROTOCOL demoprot_untrusted TO extprotu; -- should fail. protocol is not trusted
+-- GRANT SELECT ON PROTOCOL demoprot TO extprotu;
+-- GRANT INSERT ON PROTOCOL demoprot TO extprotu;
+-- SET SESSION AUTHORIZATION extprotu;
+-- CREATE WRITABLE EXTERNAL TABLE ext_w(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should succeed
+-- CREATE READABLE EXTERNAL TABLE ext_r(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should succeed
+-- DROP EXTERNAL TABLE IF EXISTS ext_w;
+-- DROP EXTERNAL TABLE IF EXISTS ext_r;
+-- RESET SESSION AUTHORIZATION;
+-- REVOKE INSERT ON PROTOCOL demoprot FROM extprotu;
+-- SET SESSION AUTHORIZATION extprotu;
+-- CREATE WRITABLE EXTERNAL TABLE ext_w(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should fail
+-- CREATE READABLE EXTERNAL TABLE ext_r(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should succeed
+-- DROP EXTERNAL TABLE ext_r;
+-- RESET SESSION AUTHORIZATION;
+-- REVOKE ALL PRIVILEGES ON PROTOCOL demoprot FROM extprotu;
+-- DROP ROLE IF EXISTS extprotu;
+--
+-- WET tests
+--
+--
+-- CREATE (including LIKE, DISTRIBUTED BY)
+--
+-- positive
+create writable external table wet_pos1(a text, b text) location('gpfdist://@hostname@:7070/wet.out') format 'text';
+CREATE EXTERNAL TABLE
+create writable external table wet_pos2(a text, b text) location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(b);
+CREATE EXTERNAL TABLE
+create writable external table wet_pos3(like wet_pos2) location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(a,b);
+CREATE EXTERNAL TABLE
+create writable external table wet_region(like reg_region) location('gpfdist://@hostname@:7070/wet_region.out') format 'text';
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:405: NOTICE: Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table
+CREATE EXTERNAL TABLE
+create readable external table ret_region(like wet_region) location('gpfdist://@hostname@:7070/wet_region.out') format 'text';
+CREATE EXTERNAL TABLE
+-- negative
+create writable external table wet_neg1(a text, b text) location('gpfdist://@hostname@:7070@abs_srcdir@/badt1.tbl') format 'text';
+CREATE EXTERNAL TABLE
+create writable external table wet_neg1(a text, b text) location('gpfdist://@hostname@:7070/wet.out', 'gpfdist://@hostname@:7070/wet.out') format 'text';
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:410: ERROR: location uri "gpfdist://@hostname@:7070/wet.out" appears more than once
+create writable external web table wet_pos5(a text, b text) execute 'some command' on segment 0 format 'text';
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:411: ERROR: the ON segment syntax for writable external tables is deprecated
+--
+-- SELECT from WET (negative)
+--
+select * from wet_pos1;
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:416: ERROR: it is not possible to read from a WRITABLE external table.
+HINT: Create the table as READABLE instead
+--
+-- WET: export some data with INSERT SELECT, INSERT and COPY.
+--
+-- COPY reg_region FROM STDIN DELIMITER '|';
+-- 0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to
+-- 1|AMERICA|hs use ironic, even requests. s
+-- \.
+INSERT INTO wet_region SELECT * from reg_region;
+INSERT 0 0
+-- COPY wet_region FROM STDIN DELIMITER '|';
+-- 2|ASIA|ges. thinly even pinto beans ca
+-- 3|EUROPE|ly final courts cajole furiously final excuse
+-- \.
+-- INSERT INTO wet_region VALUES(4,'MIDDLE EAST','uickly special');
+--
+-- Now use RET to see if data was exported correctly.
+-- NOTE: since we don't bother cleaning up the exported file, it may grow bigger
+-- in between runs, so we don't check for count(*), and instead check for distinct.
+--
+-- SELECT DISTINCT(R_NAME) FROM ret_region ORDER BY R_NAME;
+-- start_ignore
+select * from gpfdist_status;
+ x
+-------------------------------------------------------------------------
+ Okay, gpfdist version "2.0.0.0 build dev" is running on @hostname@:7070.
+(1 row)
+
+select * from gpfdist_stop;
+ x
+-------------
+ stopping...
+(1 row)
+
+select * from gpfdist_status;
+ x
+------------------------------------------------------
+ Error: gpfdist is not running (reason: socket error)
+ Exit: 1
+
+(3 rows)
+
+-- end_ignore
+--
+-- get an error for missing gpfdist
+--
+select count(*) from ext_whois;
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:449: ERROR: connection with gpfdist failed for gpfdist://@hostname@:7070/whois.csv. effective url: http://127.0.0.1:7070/whois.csv. error code = 61 (Connection refused) (seg4 @hostname@:40000 pid=35904)
+--
+-- test CREATE EXTERNAL TABLE privileges
+--
+-- show gp_external_grant_privileges; -- MUST BE OFF for the following tests to work.
+-- CREATE ROLE exttab1_su SUPERUSER; -- SU with no privs in pg_auth
+-- CREATE ROLE exttab1_u1 CREATEEXTTABLE(protocol='gpfdist', type='readable');
+-- CREATE ROLE exttab1_u2 CREATEEXTTABLE(protocol='gpfdist', type='writable');
+-- CREATE ROLE exttab1_u3 CREATEEXTTABLE(protocol='gpfdist') NOCREATEEXTTABLE(protocol='gpfdist', type='readable'); -- fail due to conflict
+-- SET SESSION AUTHORIZATION exttab1_su;
+-- create readable external table auth_ext_test1(a int) location ('gpfdist://host:8000/file') format 'text';
+-- SET SESSION AUTHORIZATION exttab1_u1;
+-- create readable external table auth_ext_test2(a int) location ('gpfdist://host:8000/file') format 'text';
+-- create writable external table auth_ext_test3(a int) location ('gpfdist://host:8000/file') format 'text'; -- fail
+-- SET SESSION AUTHORIZATION exttab1_u2;
+-- create writable external table auth_ext_test3(a int) location ('gpfdist://host:8000/file') format 'text';
+-- RESET SESSION AUTHORIZATION;
+-- ALTER ROLE exttab1_u2 NOCREATEEXTTABLE(protocol='gpfdist', type='writable');
+-- SET SESSION AUTHORIZATION exttab1_u2;
+-- create writable external table auth_ext_test4(a int) location ('gpfdist://host:8000/file') format 'text'; -- fail
+-- RESET SESSION AUTHORIZATION;
+-- drop external table auth_ext_test1;
+-- drop external table auth_ext_test2;
+-- drop external table auth_ext_test3;
+-- DROP ROLE exttab1_su;
+-- DROP ROLE exttab1_u1;
+-- DROP ROLE exttab1_u2;
+drop external table ext_whois;
+DROP EXTERNAL TABLE
+drop external table gpfdist_csv_start;
+DROP EXTERNAL TABLE
+drop external table check_echo;
+DROP EXTERNAL TABLE
+drop external table wet_pos1;
+DROP EXTERNAL TABLE
+drop external table wet_pos2;
+DROP EXTERNAL TABLE
+drop external table wet_pos3;
+DROP EXTERNAL TABLE
+drop external table wet_region;
+DROP EXTERNAL TABLE
+drop external table ret_region;
+DROP EXTERNAL TABLE
+drop table reg_region;
+DROP TABLE
+-- Tests for MPP-2513: dropping an external table must result in the deletion
+-- of the relation pg_exttable row. This was done when using drop external
+-- table but not drop schema cascade;
+create schema exttabletest;
+CREATE SCHEMA
+CREATE EXTERNAL TABLE exttabletest.EXT_NATION ( N_NATIONKEY INTEGER ,
+ N_NAME CHAR(25) ,
+ N_REGIONKEY INTEGER ,
+ N_COMMENT VARCHAR(152))
+location ('gpfdist://host:8000/nation.tbl' )
+FORMAT 'text' (delimiter '|');
+CREATE EXTERNAL TABLE
+-- Don't just return the row, as we'll get skew from OID differences between
+-- runs.
+select count(*) from pg_catalog.pg_exttable where reloid in (select r.oid from pg_class r where r.relname in ('ext_nation', 'ext_whois', 'gpfdist_status'));
+ count
+-------
+ 2
+(1 row)
+
+drop schema exttabletest cascade;
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:505: NOTICE: drop cascades to external table exttabletest.ext_nation
+DROP SCHEMA
+select count(*) from pg_catalog.pg_exttable where reloid in (select r.oid from pg_class r where r.relname in ('ext_nation', 'ext_whois', 'gpfdist_status'));
+ count
+-------
+ 1
+(1 row)
+
+SET gp_foreign_data_access = false;
+SET
+CREATE USER nonsuperproto;
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:509: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+GRANT SELECT ON PROTOCOL pxf TO nonsuperproto;
+GRANT
+GRANT INSERT ON PROTOCOL pxf TO nonsuperproto;
+GRANT
+REVOKE SELECT ON PROTOCOL pxf FROM nonsuperproto;
+REVOKE
+REVOKE INSERT ON PROTOCOL pxf FROM nonsuperproto;
+REVOKE
+DROP USER nonsuperproto;
+DROP ROLE
+-- start_ignore
+select * from gpfdist_status;
+ x
+------------------------------------------------------
+ Error: gpfdist is not running (reason: socket error)
+ Exit: 1
+
+(3 rows)
+
+select * from gpfdist_start;
+ x
+-------------
+ starting...
+(1 row)
+
+select * from gpfdist_status;
+ x
+-------------------------------------------------------------------------
+ Okay, gpfdist version "2.0.0.0 build dev" is running on @hostname@:7070.
+(1 row)
+
+-- end_ignore
+-- Tests for MPP17980: generating artificial CTIDs for external table scans
+-- This is necessary because the planner currently generates plans that include
+-- the CTID attribute for external tables.
+CREATE EXTERNAL TABLE ext_mpp17980 ( id int , id1 int , id2 int)
+LOCATION ('gpfdist://@hostname@:7070/mpp17980.data')
+FORMAT 'CSV' ( DELIMITER ',' NULL ' ');
+CREATE EXTERNAL TABLE
+CREATE TABLE mpp17980 (id int, date date, amt decimal(10,2))
+DISTRIBUTED randomly PARTITION BY RANGE (date)
+( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE
+EVERY (INTERVAL '1 month') );
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_1" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_2" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_3" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_4" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_5" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_6" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_7" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_8" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_9" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_10" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_11" for table "mpp17980"
+psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_12" for table "mpp17980"
+CREATE TABLE
+INSERT INTO mpp17980 VALUES ( 1,'2008-02-20',122.11);
+INSERT 0 1
+SELECT COUNT(*) FROM ext_mpp17980 WHERE id IN ( 1 ) ; -- This returns 18 tuples
+ count
+-------
+ 18
+(1 row)
+
+SELECT COUNT(*) FROM ext_mpp17980 WHERE id IN ( SELECT id FROM mpp17980 ) ; -- This should return 18 tuples but returns only 1
+ count
+-------
+ 18
+(1 row)
+
+SELECT ctid, * FROM ext_mpp17980;
+ ctid | id | id1 | id2
+--------+----+-----+-----
+ (0,1) | 1 | 1 | 1
+ (0,2) | 1 | 2 | 2
+ (0,3) | 1 | 2 | 3
+ (0,4) | 1 | 3 | 4
+ (0,5) | 1 | 2 | 3
+ (0,6) | 1 | 1 | 1
+ (0,7) | 1 | 2 | 3
+ (0,8) | 1 | 2 | 3
+ (0,9) | 1 | 1 | 1
+ (0,10) | 1 | 1 | 1
+ (0,11) | 1 | 2 | 2
+ (0,12) | 1 | 2 | 3
+ (0,13) | 1 | 3 | 4
+ (0,14) | 1 | 2 | 3
+ (0,15) | 1 | 1 | 1
+ (0,16) | 1 | 2 | 3
+ (0,17) | 1 | 2 | 3
+ (0,18) | 1 | 1 | 1
+(18 rows)
+
+DROP EXTERNAL TABLE ext_mpp17980;
+DROP EXTERNAL TABLE
+DROP TABLE mpp17980;
+DROP TABLE
+-- start_ignore
+select * from gpfdist_status;
+ x
+-------------------------------------------------------------------------
+ Okay, gpfdist version "2.0.0.0 build dev" is running on @hostname@:7070.
+(1 row)
+
+select * from gpfdist_stop;
+ x
+-------------
+ stopping...
+(1 row)
+
+select * from gpfdist_status;
+ x
+------------------------------------------------------
+ Error: gpfdist is not running (reason: socket error)
+ Exit: 1
+
+(3 rows)
+
+-- end_ignore
+drop external table gpfdist_status;
+DROP EXTERNAL TABLE
+drop external table gpfdist_start;
+DROP EXTERNAL TABLE
+drop external table gpfdist_stop;
+DROP EXTERNAL TABLE
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2fa04ff/src/test/feature/ExternalSource/data/missing_fields1.data
----------------------------------------------------------------------
diff --git a/src/test/feature/ExternalSource/data/missing_fields1.data b/src/test/feature/ExternalSource/data/missing_fields1.data
new file mode 100644
index 0000000..2a397ec
--- /dev/null
+++ b/src/test/feature/ExternalSource/data/missing_fields1.data
@@ -0,0 +1,5 @@
+1|1|1
+2|2
+3
+4|4
+5|5|5
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2fa04ff/src/test/feature/ExternalSource/data/missing_fields2.data
----------------------------------------------------------------------
diff --git a/src/test/feature/ExternalSource/data/missing_fields2.data b/src/test/feature/ExternalSource/data/missing_fields2.data
new file mode 100644
index 0000000..f2c4db7
--- /dev/null
+++ b/src/test/feature/ExternalSource/data/missing_fields2.data
@@ -0,0 +1,6 @@
+1|1|1
+2|2
+
+3
+4|4
+5|5|5
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2fa04ff/src/test/feature/ExternalSource/data/mpp12839_1.data
----------------------------------------------------------------------
diff --git a/src/test/feature/ExternalSource/data/mpp12839_1.data b/src/test/feature/ExternalSource/data/mpp12839_1.data
new file mode 100644
index 0000000..88898ec
--- /dev/null
+++ b/src/test/feature/ExternalSource/data/mpp12839_1.data
@@ -0,0 +1,2 @@
+11e5f8d185-ab1c-47e4-a818-18b81900ac6004-06-11 14:19:05.9560905332CKDATE=3GYDG=90580124UserIP=10.10.181.190Referrer=http://10.10.1.28/QA/Eti/DEV4/automation/Basic/FWR.htmlUserAgent=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB6.6; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)FD=1
+11e5f8d185-ab1c-47e4-a818-18b81900ac6004-06-11 14:19:19.0820905330CKDATE=3GYTA=-1DG=90560124UserIP=10.10.181.190Referrer=http://10.10.1.28/QA/Eti/DEV4/automation/Basic/exp.htmlUserAgent=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB6.6; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)FD=1
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2fa04ff/src/test/feature/ExternalSource/data/mpp12839_2.data
----------------------------------------------------------------------
diff --git a/src/test/feature/ExternalSource/data/mpp12839_2.data b/src/test/feature/ExternalSource/data/mpp12839_2.data
new file mode 100644
index 0000000..e565441
--- /dev/null
+++ b/src/test/feature/ExternalSource/data/mpp12839_2.data
@@ -0,0 +1,3 @@
+44e5f8d185-ab1c-47e4-a818-18b81900ac6004-06-11 14:19:05.9560905332 CKDATE=3GY DG=90580124UserIP=10.10.181.190 Referrer=http://10.10.1.28/QA/Eti/DEV4/automation/Basic/FWR.html UserAgent=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB6.6; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E) FD=1
+44e5f8d185-ab1c-47e4-a818-18b81900ac6004-06-11 14:19:19.0820905330 CKDATE=3GY TA=-1 DG=90560124UserIP=10.10.181.190 Referrer=http://10.10.1.28/QA/Eti/DEV4/automation/Basic/exp.html UserAgent=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB6.6; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E) FD=1
+44e5f8d185-ab1c-47e4-a818-18b81900ac6004-06-11 14:19:19.0820905330 CKDATE=3GY TA=-1 DG=90560124UserIP=10.10.181.190 Referrer=http://10.10.1.28/QA/Eti/DEV4/automation/Basic/exp.html UserAgent=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB6.6; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E) FD=1
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2fa04ff/src/test/feature/ExternalSource/data/mpp12839_3.data
----------------------------------------------------------------------
diff --git a/src/test/feature/ExternalSource/data/mpp12839_3.data b/src/test/feature/ExternalSource/data/mpp12839_3.data
new file mode 100644
index 0000000..e3cdba6
--- /dev/null
+++ b/src/test/feature/ExternalSource/data/mpp12839_3.data
@@ -0,0 +1,2 @@
+55e5f8d185-ab1c-47e4-a818-18b81900ac6004-06-11 14:19:05.9560905332 CKDATE=3GY DG=90580124UserIP=10.10.181.190 Referrer=http://10.10.1.28/QA/Eti/DEV4/automation/Basic/FWR.html UserAgent=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB6.6; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E) FD=1
+55e5f8d185-ab1c-47e4-a818-18b81900ac6004-06-11 14:19:19.0820905330 CKDATE=3GY TA=-1 DG=90560124UserIP=10.10.181.190 Referrer=http://10.10.1.28/QA/Eti/DEV4/automation/Basic/exp.html UserAgent=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB6.6; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E) FD=1
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2fa04ff/src/test/feature/ExternalSource/data/mpp17980.data
----------------------------------------------------------------------
diff --git a/src/test/feature/ExternalSource/data/mpp17980.data b/src/test/feature/ExternalSource/data/mpp17980.data
new file mode 100644
index 0000000..cc922ff
--- /dev/null
+++ b/src/test/feature/ExternalSource/data/mpp17980.data
@@ -0,0 +1,18 @@
+1,1,1
+1,2,2
+1,2,3
+1,3,4
+1,2,3
+1,1,1
+1,2,3
+1,2,3
+1,1,1
+1,1,1
+1,2,2
+1,2,3
+1,3,4
+1,2,3
+1,1,1
+1,2,3
+1,2,3
+1,1,1
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2fa04ff/src/test/feature/ExternalSource/data/mpp6698.data
----------------------------------------------------------------------
diff --git a/src/test/feature/ExternalSource/data/mpp6698.data b/src/test/feature/ExternalSource/data/mpp6698.data
new file mode 100644
index 0000000..fdfa972
--- /dev/null
+++ b/src/test/feature/ExternalSource/data/mpp6698.data
@@ -0,0 +1,3 @@
+'first row'
+'second row with embedded single quote '' here'
+'third row with another embedded quote at the end '''
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2fa04ff/src/test/feature/ExternalSource/data/region.tbl
----------------------------------------------------------------------
diff --git a/src/test/feature/ExternalSource/data/region.tbl b/src/test/feature/ExternalSource/data/region.tbl
new file mode 100755
index 0000000..17441bf
--- /dev/null
+++ b/src/test/feature/ExternalSource/data/region.tbl
@@ -0,0 +1,5 @@
+0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to
+1|AMERICA|hs use ironic, even requests. s
+2|ASIA|ges. thinly even pinto beans ca
+3|EUROPE|ly final courts cajole furiously final excuse
+4|MIDDLE EAST|uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl