| -- |
| -- 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 optimizer_disable_missing_stats_collection = on; |
| CREATE TABLE REG_REGION (R_REGIONKEY INT, R_NAME CHAR(25), R_COMMENT VARCHAR(152)) DISTRIBUTED BY (R_REGIONKEY); |
| |
| -- start_ignore |
| -- -------------------------------------- |
| -- check platform |
| -- -------------------------------------- |
| drop external web table if exists check_echo; |
| CREATE EXTERNAL WEB TABLE check_echo (x text) |
| execute E'(echo gpfdist)' |
| on SEGMENT 0 |
| format 'text'; |
| select * from check_echo; |
| |
| |
| -- 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 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 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 '|'); |
| |
| -- start_ignore |
| select * from gpfdist_stop; |
| select * from gpfdist_status; |
| select * from gpfdist_start; |
| select * from gpfdist_status; |
| -- 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 EXT_REGION (LIKE REG_REGION) |
| location ('gpfdist://@hostname@:7070/region.tbl' ) |
| FORMAT 'text' (delimiter '|'); |
| |
| SELECT * FROM EXT_NATION; |
| SELECT * FROM EXT_REGION; |
| SELECT * FROM EXT_REGION as r, EXT_NATION as n WHERE n.N_REGIONKEY = r.R_REGIONKEY; |
| |
| -- start_ignore |
| select * from gpfdist_status; |
| select * from gpfdist_stop; |
| select * from gpfdist_status; |
| -- end_ignore |
| |
| -- drop tables |
| DROP EXTERNAL TABLE EXT_NATION; |
| DROP EXTERNAL TABLE EXT_REGION; |
| |
| -- |
| -- 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 '|'); |
| -- |
| -- " |
| -- (end the double quote so emacs highlighting works correctly) |
| -- |
| -- start_ignore |
| select * from gpfdist_status; |
| select * from gpfdist_stop; |
| select * from gpfdist_status; |
| select * from gpfdist_csv_start; |
| select * from gpfdist_status; |
| -- 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); |
| -- " |
| -- start_ignore |
| select count(*) from ext_whois; |
| -- 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 ext_fill2(a int, b int, c int) |
| location('gpfdist://@hostname@:7070/missing_fields2.data' ) |
| format 'text' ( delimiter '|' fill missing fields); |
| |
| -- 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 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); |
| |
| select * from ext_fill1 order by a,b,c; -- should pad missing attrs with nulls |
| -- start_ignore |
| select * from ext_fill2 order by a,b,c; -- should fail due to empty data line |
| -- end_ignore |
| select * from ext_fill3_fnn where c is null; -- should be empty |
| select * from ext_fill3_fnn where b is null; -- one row result |
| select * from ext_fill4_fnn where c is null or b is null; -- should be empty |
| |
| drop external table ext_fill1; |
| drop external table ext_fill2; |
| drop external table ext_fill3_fnn; |
| drop external table ext_fill4_fnn; |
| |
| -- 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 ext_newline2(like ext_newline1) |
| location('gpfdist://@hostname@:7070/nation.tbl' ) |
| format 'text' ( delimiter '|' newline 'cr'); |
| |
| create external table ext_willfail(a int) |
| location('gpfdist://@hostname@:7070/nation.tbl' ) |
| format 'text' ( delimiter '|' newline 'blah'); -- should fail with invalid newline. |
| |
| select * from ext_newline1; -- should pass. using the correct linefeed. file has 'lf'. |
| -- start_ignore |
| select * from ext_newline2; -- should fail. using an incorrect linefeed. file has 'lf'. |
| -- end_ignore |
| |
| drop external table ext_newline1; |
| drop external table ext_newline2; |
| |
| -- MPP-6698 |
| create external table ext_mpp6698(a text) |
| location('gpfdist://@hostname@:7070/mpp6698.data' ) |
| format 'csv' (quote ''''); |
| |
| select * from ext_mpp6698 order by a; -- should ignore the quotes |
| drop external table ext_mpp6698; |
| |
| -- 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; |
| |
| select * from ext_mpp12839; -- should not reach reject limit |
| drop external table ext_mpp12839; |
| |
| -- -------------------------------------- |
| -- 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 web table ext_stderr2(a text) execute 'cat nosuchfile.txt' ON 0 format 'text'; |
| |
| -- |
| -- 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'); |
| select count(*) from bad_whois; |
| drop external table bad_whois; |
| |
| -- |
| -- 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 '|'); |
| |
| -- |
| -- 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 writable external table wet_pos2(a text, b text) location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(b); |
| create writable external table wet_pos3(like wet_pos2) location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(a,b); |
| create writable external table wet_region(like reg_region) location('gpfdist://@hostname@:7070/wet_region.out') format 'text'; |
| create readable external table ret_region(like wet_region) location('gpfdist://@hostname@:7070/wet_region.out') format 'text'; |
| |
| -- negative |
| create writable external table wet_neg1(a text, b text) location('gpfdist://@hostname@:7070@abs_srcdir@/badt1.tbl') format 'text'; |
| create writable external table wet_neg1(a text, b text) location('gpfdist://@hostname@:7070/wet.out', 'gpfdist://@hostname@:7070/wet.out') format 'text'; |
| create writable external web table wet_pos5(a text, b text) execute 'some command' on segment 0 format 'text'; |
| |
| -- |
| -- SELECT from WET (negative) |
| -- |
| select * from wet_pos1; |
| -- |
| -- 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; |
| |
| -- 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; |
| select * from gpfdist_stop; |
| select * from gpfdist_status; |
| -- end_ignore |
| |
| -- |
| -- get an error for missing gpfdist |
| -- |
| select count(*) from ext_whois; |
| |
| -- |
| -- 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 gpfdist_csv_start; |
| drop external table check_echo; |
| drop external table wet_pos1; |
| drop external table wet_pos2; |
| drop external table wet_pos3; |
| drop external table wet_region; |
| drop external table ret_region; |
| drop table reg_region; |
| |
| -- 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 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 '|'); |
| -- 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')); |
| drop schema exttabletest cascade; |
| 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')); |
| SET gp_foreign_data_access = false; |
| |
| CREATE USER nonsuperproto; |
| GRANT SELECT ON PROTOCOL pxf TO nonsuperproto; |
| GRANT INSERT ON PROTOCOL pxf TO nonsuperproto; |
| REVOKE SELECT ON PROTOCOL pxf FROM nonsuperproto; |
| REVOKE INSERT ON PROTOCOL pxf FROM nonsuperproto; |
| DROP USER nonsuperproto; |
| |
| -- start_ignore |
| select * from gpfdist_status; |
| select * from gpfdist_start; |
| select * from gpfdist_status; |
| -- 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 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') ); |
| |
| INSERT INTO mpp17980 VALUES ( 1,'2008-02-20',122.11); |
| |
| SELECT COUNT(*) FROM ext_mpp17980 WHERE id IN ( 1 ) ; -- This returns 18 tuples |
| |
| SELECT COUNT(*) FROM ext_mpp17980 WHERE id IN ( SELECT id FROM mpp17980 ) ; -- This should return 18 tuples but returns only 1 |
| |
| SELECT ctid, * FROM ext_mpp17980; |
| |
| DROP EXTERNAL TABLE ext_mpp17980; |
| DROP TABLE mpp17980; |
| |
| -- start_ignore |
| select * from gpfdist_status; |
| select * from gpfdist_stop; |
| select * from gpfdist_status; |
| -- end_ignore |
| |
| drop external table gpfdist_status; |
| drop external table gpfdist_start; |
| drop external table gpfdist_stop; |
