--
-- external tables - short and simple functional tests.
--
-- 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 line number - Debian
-- m/ERROR\:\s+external table .* command ended with .*sh: 1: .*NOT FOUND.*/i
-- s/ sh: 1: / sh: /
--
-- # 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 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);

-- --------------------------------------
-- 'file' protocol - (only CREATE, don't SELECT - won't work on distributed installation)
-- --------------------------------------
CREATE EXTERNAL TABLE EXT_NATION  ( N_NATIONKEY  INTEGER ,
                            N_NAME       CHAR(25) ,
                            N_REGIONKEY  INTEGER ,
                            N_COMMENT    VARCHAR(152))
location ('file://@hostname@@abs_srcdir@/data/nation.tbl' )
FORMAT 'text' (delimiter '|');

CREATE EXTERNAL TABLE EXT_REGION  (LIKE REG_REGION)
location ('file://@hostname@@abs_srcdir@/data/region.tbl' )
FORMAT 'text' (delimiter '|');

-- drop tables

DROP EXTERNAL TABLE EXT_NATION;
DROP EXTERNAL TABLE EXT_REGION;
-- start_ignore
-- --------------------------------------
-- check platform
-- --------------------------------------
drop external web table if exists check_ps;
CREATE EXTERNAL WEB TABLE check_ps (x text)
execute E'( (ps -ef || ps -aux) | grep gpfdist | grep -v grep)'
on SEGMENT 0
format 'text';
drop external web table if exists check_env;
CREATE EXTERNAL WEB TABLE check_env (x text)
execute E'( env | sort)'
on SEGMENT 0
format 'text';
select * from check_ps;
select * from check_env;

-- end_ignore
-- --------------------------------------
-- some negative tests
-- --------------------------------------
--
-- test for exec child process stderr showing in error message
--
create external web table ext_stderr1(a text) execute 'nosuchcommand' format 'text';
create external web table ext_stderr2(a text) execute 'cat nosuchfile.txt' format 'text';
select * from ext_stderr1;
select * from ext_stderr2;
drop external web table ext_stderr1;
drop external web table ext_stderr2;
--
-- bad csv (quote must be a single char)
--
create external table bad_whois (
source_lineno			int,
domain_name			varchar(350)
)
location ('gpfdist://@hostname@:7070/exttab1/whois.csv' )
format 'csv' ( header quote as 'ggg');
select count(*) from bad_whois;
drop external table bad_whois;
--
-- try a bad location
--
create external table badt1 (x text) 
location ('file://@hostname@@abs_srcdir@/data/no/such/place/badt1.tbl' )
format 'text' (delimiter '|');
select * from badt1;
drop external table badt1;

--
-- 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 (partial support)
--
create external table ext (a int, x text) 
location ('file://@hostname@@abs_srcdir@/data/no/such/place/badt1.tbl' )
format 'text';
alter table ext drop column a; -- should pass with warning
alter external table ext add column a int; -- pass
alter external table ext drop column a; -- pass
alter external table ext add column extnewcol int not null; -- should fail (constraints not allowed)
alter external table ext add column extnewcol int; -- pass
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 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 NO SQL;
CREATE OR REPLACE FUNCTION read_from_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_import' LANGUAGE C STABLE NO SQL;

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
ALTER PROTOCOL demoprot_untrusted RENAME TO demoprot_untrusted2;
ALTER PROTOCOL demoprot_untrusted2 RENAME TO demoprot_untrusted;

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 web table wet_pos4(a text, b text) execute 'some command' format 'text';

-- negative
create writable external table wet_neg1(a text, b text) location('file://@hostname@@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';
--
-- 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 
CREATE ROLE exttab1_u4 CREATEEXTTABLE(protocol='gphdfs', type='readable'); 
CREATE ROLE exttab1_u5 CREATEEXTTABLE(protocol='gphdfs', type='writable'); 
CREATE ROLE exttab1_u6 NOCREATEEXTTABLE(protocol='gphdfs', type='readable') NOCREATEEXTTABLE(protocol='gphdfs', type='writable');
CREATE ROLE exttab1_u7 CREATEEXTTABLE(protocol='gphdfs') NOCREATEEXTTABLE(protocol='gphdfs', type='readable'); -- fail due to conflict 
CREATE ROLE exttab1_u7 CREATEEXTTABLE(protocol='gphdfs', type='writable') NOCREATEEXTTABLE(protocol='gphdfs', type='writable'); -- 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 external table auth_ext_test5;
drop external table auth_ext_test7;

DROP ROLE exttab1_su;
DROP ROLE exttab1_u1;
DROP ROLE exttab1_u2;
DROP ROLE exttab1_u4;
DROP ROLE exttab1_u5;
DROP ROLE exttab1_u6;

drop external table check_ps;
drop external table check_env;
drop external table wet_pos4;

-- 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 ('file://@hostname@@abs_srcdir@/data/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', 'exttab1_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', 'exttab1_gpfdist_status'));

-- 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 ('file://@hostname@@abs_srcdir@/data/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;

COPY (VALUES('1,2'),('1,2,3'),('1,'),('1')) TO '@abs_srcdir@/data/tableless.csv';
CREATE TABLE tableless_heap(a int, b int);
COPY tableless_heap FROM '@abs_srcdir@/data/tableless.csv' CSV LOG ERRORS SEGMENT REJECT LIMIT 10;
SELECT relname, linenum, errmsg FROM gp_read_error_log('tableless_heap');
create table errlog_save as select * from gp_read_error_log('tableless_heap');
select count(*) from errlog_save;
SELECT gp_truncate_error_log('tableless_heap');
SELECT relname, linenum, errmsg FROM gp_read_error_log('tableless_heap');

create external table tableless_ext(a int, b int)
location ('file://@hostname@@abs_srcdir@/data/tableless.csv')
format 'csv'
log errors segment reject limit 10;
select * from tableless_ext;
SELECT relname, linenum, errmsg FROM gp_read_error_log('tableless_ext');
SELECT (gp_read_error_log('tableless_ext')).errmsg;
SELECT gp_truncate_error_log('tableless_ext');
SELECT relname, linenum, errmsg FROM gp_read_error_log('tableless_ext');
select * from tableless_ext;
SELECT gp_truncate_error_log('*');
select * from tableless_ext;
SELECT gp_truncate_error_log('*.*');
SELECT relname, linenum, errmsg FROM gp_read_error_log('tableless_ext');

-- Verify that the error log functions are a part of the catalog
-- Earlier these functions were hidden from the catalog and would not show up
-- in pg_proc
SELECT * from pg_proc where proname = 'gp_read_error_log';
SELECT * from pg_proc where proname = 'gp_truncate_error_log';

-- Test for error log functionality 

-- Scan with no errors
CREATE EXTERNAL TABLE exttab_basic_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
-- Empty error log 
SELECT * FROM gp_read_error_log('exttab_basic_1'); 
SELECT COUNT(*) FROM exttab_basic_1;
-- Error log should still be empty
SELECT * FROM gp_read_error_log('exttab_basic_1');

-- Some errors without exceeding reject limit
CREATE EXTERNAL TABLE exttab_basic_2( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|')
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- should not error out as segment reject limit will not be reached
SELECT * FROM exttab_basic_2 order by i;
-- Error rows logged
select count(*) from gp_read_error_log('exttab_basic_2');

-- Errors with exceeding reject limit
CREATE EXTERNAL TABLE exttab_basic_3( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|')
LOG ERRORS SEGMENT REJECT LIMIT 2;
-- should error out as segment reject limit will be reached
SELECT * FROM exttab_basic_3;
-- Error log should be populated
select count(*) > 0 from gp_read_error_log('exttab_basic_3');

-- Insert into another table
CREATE EXTERNAL TABLE exttab_basic_4( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 100;
CREATE TABLE exttab_insert_1 (LIKE exttab_basic_4);
-- Insert should go through fine
INSERT INTO exttab_insert_1 SELECT * FROM exttab_basic_4;
-- Error log should be populated
select count(*) > 0 from gp_read_error_log('exttab_basic_4');
-- Use the same error log above
CREATE EXTERNAL TABLE exttab_basic_5( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 5;
-- Insert should fail
INSERT INTO exttab_insert_1 select * from exttab_basic_5;
SELECT * from exttab_insert_1 order by i;
-- Error log should have additional rows that were rejected by the above query
SELECT count(*) from gp_read_error_log('exttab_basic_5');

-- CTAS
CREATE EXTERNAL TABLE exttab_basic_6( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|')
LOG ERRORS SEGMENT REJECT LIMIT 100;
CREATE TABLE exttab_ctas_1 as SELECT * FROM exttab_basic_6;
-- CTAS should go through fine
SELECT * FROM exttab_ctas_1 order by i;
-- Error log should have six rows that were rejected
select count(*) from gp_read_error_log('exttab_basic_6');
CREATE EXTERNAL TABLE exttab_basic_7( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|')
LOG ERRORS SEGMENT REJECT LIMIT 5;
-- CTAS should fail
CREATE TABLE exttab_ctas_2 AS select * from exttab_basic_7;
-- Table should not exist
SELECT * from exttab_ctas_2 order by i;
-- Error table should have additional rows that were rejected by the above query
SELECT count(*) from gp_read_error_log('exttab_basic_7');
-- pg_exttable.fmterrtbl references to itself for external table with error logs
CREATE EXTERNAL TABLE exttab_error_log( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
SELECT reloid = fmterrtbl FROM pg_exttable
WHERE reloid = (SELECT oid from pg_class where relname ilike 'exttab_error_log');

-- Drop external table gets rid off error logs
DROP EXTERNAL TABLE IF EXISTS exttab_error_log;
CREATE EXTERNAL TABLE exttab_error_log( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
SELECT COUNT(*) FROM exttab_error_log;
SELECT COUNT(*) FROM gp_read_error_log('exttab_error_log');
DROP EXTERNAL TABLE exttab_error_log;
SELECT COUNT(*) FROM gp_read_error_log('exttab_error_log');

-- Insert into another table with unique constraints
CREATE EXTERNAL TABLE exttab_constraints_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- Should not error out
SELECT COUNT(*) FROM exttab_constraints_1;
-- Error log should have a couple of rows
SELECT COUNT(*) from gp_read_error_log('exttab_constraints_1');
CREATE TABLE exttab_constraints_insert_1 (LIKE exttab_constraints_1) distributed by (i);
ALTER TABLE exttab_constraints_insert_1 ADD CONSTRAINT exttab_uniq_constraint_1 UNIQUE (j);
-- This should fail
select gp_truncate_error_log('exttab_constraints_1');
INSERT INTO exttab_constraints_insert_1 SELECT * FROM exttab_constraints_1;
SELECT COUNT(*) FROM gp_read_error_log('exttab_constraints_1');

-- CTE with segment reject limit reached
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_cte_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit, use the same err table
CREATE EXTERNAL TABLE exttab_cte_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
with cte1 as 
(
SELECT e1.i, e2.j FROM exttab_cte_2 e1, exttab_cte_1 e2
WHERE e1.i = e2.i ORDER BY e1.i
)
SELECT * FROM cte1 ORDER BY cte1.i;
select count(*) from gp_read_error_log('exttab_cte_2');
select count(*) from gp_read_error_log('exttab_cte_1');

-- CTE without segment reject limit exceeded
select gp_truncate_error_log('exttab_cte_1');
select gp_truncate_error_log('exttab_cte_2');
with cte1 as
(
SELECT e1.i, e2.j FROM exttab_cte_1 e1, exttab_cte_1 e2 WHERE e1.i = e2.i AND e1.i > 5 ORDER BY e1.i
)
SELECT  cte1.i , cte1.j FROM cte1 ORDER BY cte1.i;
select count(*) from gp_read_error_log('exttab_cte_1');

-- Define a cursor on an external table scan query with segment reject limit reached
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_cursor_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit, use the same err table
CREATE EXTERNAL TABLE exttab_cursor_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
-- Test to make sure error logs are populated when cursors are used
-- The total number of formatting errors reported by the query is dependant on the number of rows parsed on
-- the segments before COMMIT finishes. So mask the NOTICE by setting the client_min_messages guc to WARNING.
set CLIENT_MIN_MESSAGES=WARNING;
BEGIN;
DECLARE exttab_cur1 no scroll cursor FOR
SELECT e1.i, e2.j from exttab_cursor_1 e1 INNER JOIN exttab_cursor_1 e2 ON e1.i = e2.i
UNION ALL
SELECT e1.i, e2.j from exttab_cursor_1 e1 INNER JOIN exttab_cursor_1 e2 ON e1.i = e2.i
UNION ALL
SELECT e1.i, e2.j from exttab_cursor_1 e1 INNER JOIN exttab_cursor_1 e2 ON e1.i = e2.i;
COMMIT;
-- This should have the errors populated already
SELECT count(*) > 0 FROM gp_read_error_log('exttab_cursor_1');
reset CLIENT_MIN_MESSAGES;

-- Fetch on external table scans with segment reject limit reached
SELECT gp_truncate_error_log('exttab_cursor_1');
SELECT gp_truncate_error_log('exttab_cursor_2');
-- DECLARE CURSOR implicitly initiates a FETCH on the segments. If one of
-- the segments reaches the reject limit and reports the error back to the QD
-- before the others, the transaction gets aborted. The timing of fetching the
-- results will  influence the order of the ERROR in the output.
BEGIN;
DECLARE exttab_cur1 no scroll cursor FOR
SELECT e1.i, e2.j from exttab_cursor_2 e1 INNER JOIN exttab_cursor_2 e2 ON e1.i = e2.i
UNION ALL
SELECT e1.i, e2.j from exttab_cursor_2 e1 INNER JOIN exttab_cursor_2 e2 ON e1.i = e2.i
UNION ALL
SELECT e1.i, e2.j from exttab_cursor_2 e1 INNER JOIN exttab_cursor_2 e2 ON e1.i = e2.i;
-- Should not fail as we would not have reached segment reject limit yet. This fails currently though
-- because how cursors scan the rows internally is implementation dependant. 
-- The test is here to track the issue.
FETCH exttab_cur1;
COMMIT;
-- This should have errors populated already
SELECT count(*) > 0 FROM gp_read_error_log('exttab_cursor_2');

-- Fetch on external table scans without reaching segment reject limit
SELECT gp_truncate_error_log('exttab_cursor_1');
SELECT gp_truncate_error_log('exttab_cursor_2');
BEGIN;
DECLARE exttab_cur1 no scroll cursor FOR
(SELECT e1.i, e2.j from exttab_cursor_1 e1 INNER JOIN exttab_cursor_1 e2 ON e1.i = e2.i
ORDER BY e1.i);
-- Should not fail
FETCH exttab_cur1;
FETCH exttab_cur1;
FETCH exttab_cur1;
FETCH exttab_cur1;
FETCH exttab_cur1;
FETCH exttab_cur1;
FETCH exttab_cur1;
FETCH exttab_cur1;
FETCH exttab_cur1;
FETCH exttab_cur1;
COMMIT;
-- This should have errors populated already
SELECT count(*) > 0 FROM gp_read_error_log('exttab_cursor_1');

-- Check permissions with gp_truncate_error_log and gp_read_error_log
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_permissions_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit
CREATE EXTERNAL TABLE exttab_permissions_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
-- generate some error logs
SELECT COUNT(*) FROM exttab_permissions_1;
SELECT COUNT(*) FROM exttab_permissions_2;

-- Only superuser can do gp_truncate_error_log('*.*')
DROP ROLE IF EXISTS exttab_non_superuser;
CREATE ROLE exttab_non_superuser WITH NOSUPERUSER LOGIN CREATEDB;
SET ROLE exttab_non_superuser;
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_1');
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_2');
SELECT gp_truncate_error_log('exttab_permissions_1');
SELECT gp_truncate_error_log('exttab_permissions_2');
SELECT gp_truncate_error_log('*');
SELECT gp_truncate_error_log('*.*');
RESET ROLE;
DROP ROLE IF EXISTS exttab_superuser;
CREATE ROLE exttab_superuser WITH SUPERUSER LOGIN;
SET ROLE exttab_superuser;
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_1');
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_2');
SELECT gp_truncate_error_log('*');
SELECT gp_truncate_error_log('*.*');
SELECT gp_truncate_error_log('exttab_permissions_1');
SELECT gp_truncate_error_log('exttab_permissions_2');
RESET ROLE;
SELECT * FROM gp_read_error_log('exttab_permissions_1');
SELECT * FROM gp_read_error_log('exttab_permissions_2');

-- Only database owner can do gp_truncate_error_log('*')
DROP DATABASE IF EXISTS exttab_db;
DROP ROLE IF EXISTS exttab_user1;
DROP ROLE IF EXISTS exttab_user2;
CREATE ROLE exttab_user1 WITH NOSUPERUSER LOGIN;
CREATE ROLE exttab_user2 WITH NOSUPERUSER LOGIN;
CREATE DATABASE exttab_db WITH OWNER=exttab_user1;
\c exttab_db
-- generate some error logs in this db
CREATE EXTERNAL TABLE exttab_permissions_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
SELECT COUNT(*) FROM exttab_permissions_1 e1, exttab_permissions_1 e2;
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_1');
SET ROLE exttab_user2;
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_1');
SELECT gp_truncate_error_log('*');
SELECT gp_truncate_error_log('*.*');
SELECT gp_truncate_error_log('exttab_permissions_1');
SET ROLE exttab_user1;
-- Database owner can still not perform read / truncate on specific tables. This follows the same mechanism as TRUNCATE table.
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_1');
SELECT gp_truncate_error_log('exttab_permissions_1');
SELECT gp_truncate_error_log('*');
-- should fail
SELECT gp_truncate_error_log('*.*');
RESET ROLE;
SELECT * FROM gp_read_error_log('exttab_permissions_1');
\c regression
DROP ROLE IF EXISTS errlog_exttab_user3;
DROP ROLE IF EXISTS errlog_exttab_user4;
CREATE ROLE errlog_exttab_user3 WITH NOSUPERUSER LOGIN;
CREATE ROLE errlog_exttab_user4 WITH NOSUPERUSER LOGIN;
-- generate some error logs in this db
CREATE EXTERNAL TABLE exttab_permissions_3( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
SELECT COUNT(*) FROM exttab_permissions_3 e1, exttab_permissions_3 e2;
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_3');
ALTER EXTERNAL TABLE exttab_permissions_3 OWNER TO errlog_exttab_user3;
-- This should fail with non table owner
SET ROLE errlog_exttab_user4;
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_3');
SELECT gp_truncate_error_log('exttab_permissions_3');
-- should go through fine with table owner
SET ROLE errlog_exttab_user3;
SELECT gp_truncate_error_log('exttab_permissions_3');
RESET ROLE;
SELECT * FROM gp_read_error_log('exttab_permissions_3');

-- Grant TRUNCATE permission on table to a non table owner and make sure he is able to do gp_truncate_error_log
GRANT TRUNCATE on exttab_permissions_3 to errlog_exttab_user4;
SELECT COUNT(*) FROM exttab_permissions_3 e1, exttab_permissions_3 e2;
SELECT COUNT(*) FROM gp_read_error_log('exttab_permissions_3');
SET ROLE errlog_exttab_user4;
SELECT gp_truncate_error_log('exttab_permissions_3');
RESET ROLE;
SELECT * FROM gp_read_error_log('exttab_permissions_3');

-- Subqueries reaching segment reject limit
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_subq_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit, use the same err table
CREATE EXTERNAL TABLE exttab_subq_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
SELECT sum(distinct e1.i), sum(distinct e2.i), e1.j FROM
(SELECT i, j FROM exttab_subq_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_subq_2 WHERE i < 10) e2
group by e1.j;
SELECT COUNT(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_subq_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_subq_2')
) FOO;
SELECT gp_truncate_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_2');
SELECT sum(distinct e1.i), sum(distinct e2.i), e1.j FROM
(SELECT i, j FROM exttab_subq_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_subq_1 WHERE i < 10) e2
group by e1.j
HAVING sum(distinct e1.i) > (SELECT max(i) FROM exttab_subq_2);
SELECT COUNT(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_subq_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_subq_2')
) FOO;

-- Subqueries without reaching segment reject limit
SELECT gp_truncate_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_2');
SELECT sum(distinct e1.i), sum(distinct e2.i), e1.j FROM
(SELECT i, j FROM exttab_subq_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_subq_1 WHERE i < 10) e2
group by e1.j order by 3,2,1;
SELECT COUNT(*) > 0 FROM gp_read_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_2');
SELECT sum(distinct e1.i), sum(distinct e2.i), e1.j FROM
(SELECT i, j FROM exttab_subq_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_subq_1 WHERE i < 10) e2
group by e1.j
HAVING sum(distinct e1.i) > (SELECT max(i) FROM exttab_subq_1);
SELECT COUNT(*) > 0 FROM gp_read_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_2');
SELECT e1.i , e1.j FROM
exttab_subq_1 e1, exttab_subq_1 e2
WHERE e1.j = e2.j and 
e1.i + 1 IN ( SELECT i from exttab_subq_1 WHERE i <= e1.i);
SELECT COUNT(*) > 0 FROM gp_read_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_2');
SELECT ( SELECT i FROM exttab_subq_2 WHERE i <= e1.i) as i, e1.j
FROM exttab_subq_2 e1, exttab_subq_1 e2
WHERE e1.i = e2.i;
SELECT COUNT(*) > 0 FROM gp_read_error_log('exttab_subq_1');

-- CSQ
SELECT gp_truncate_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_2');
SELECT e1.i , e1.j FROM
exttab_subq_1 e1, exttab_subq_1 e2
WHERE e1.j = e2.j and 
e1.i + 1 IN ( SELECT i from exttab_subq_2 WHERE i <= e1.i);
SELECT COUNT(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_subq_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_subq_2')
) FOO;
SELECT gp_truncate_error_log('exttab_subq_1');
SELECT gp_truncate_error_log('exttab_subq_2');
SELECT ( SELECT i FROM exttab_subq_2 WHERE i <= e1.i) as i, e1.j
FROM exttab_subq_2 e1, exttab_subq_1 e2
WHERE e1.i = e2.i;
SELECT COUNT(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_subq_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_subq_2')
) FOO;

-- TRUNCATE / delete / write to error logs within subtransactions
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_subtxs_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit, use the same err table
CREATE EXTERNAL TABLE exttab_subtxs_2( i int, j text ) 
LOCATION ('file://@hostname@:@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
-- Populate error logs before transaction
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_subtxs_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_subtxs_1 WHERE i < 10) e2
WHERE e1.i = e2.i;
SELECT COUNT(*)
FROM (
SELECT * FROM gp_read_error_log('exttab_subtxs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_subtxs_2')
) FOO;
BEGIN;
savepoint s1;
SELECT gp_truncate_error_log('exttab_subtxs_1');
SELECT gp_truncate_error_log('exttab_subtxs_2');
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_subtxs_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_subtxs_1 WHERE i < 10) e2
WHERE e1.i = e2.i;
-- should have written rows into error log
SELECT count(*) FROM
(
SELECT * FROM gp_read_error_log('exttab_subtxs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_subtxs_2')
) FOO;
savepoint s2;
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_subtxs_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_subtxs_1 WHERE i < 10) e2
WHERE e1.i = e2.i;
savepoint s3;
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_subtxs_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_subtxs_1 WHERE i < 10) e2
WHERE e1.i = e2.i;
ROLLBACK TO s2;
-- rollback should not rollback the error rows written from within the transaction
SELECT count(*) FROM
(
SELECT * FROM gp_read_error_log('exttab_subtxs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_subtxs_2')
) FOO;
-- Make the tx fail, segment reject limit reaches here
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_subtxs_2 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_subtxs_2 WHERE i < 10) e2
WHERE e1.i = e2.i;
COMMIT;
-- Error logs should not have been rolled back. 
-- Check that number of errors is greater than 12 instead of checking for
-- actual number of errors, since the transaction might get aborted even before
-- rows are scanned on other segments if one of the segments hits the segment
-- reject limit. The 12 errors are from previous scans of the external table.
SELECT count(*) > 12 FROM
(
SELECT * FROM gp_read_error_log('exttab_subtxs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_subtxs_2')
) FOO;

-- TRUNCATE error logs within tx , abort transaction
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_txs_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit, use the same err table
CREATE EXTERNAL TABLE exttab_txs_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
-- Populate error log before transaction
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_txs_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_txs_1 WHERE i < 10) e2
WHERE e1.i = e2.i;
SELECT count(*) FROM
(
SELECT * FROM gp_read_error_log('exttab_txs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_txs_2')
) FOO;
BEGIN;
SELECT gp_truncate_error_log('exttab_txs_1');
SELECT gp_truncate_error_log('exttab_txs_2');
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_txs_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_txs_1 WHERE i < 10) e2
WHERE e1.i = e2.i;
ABORT;
-- Check that number of errors is greater than zero instead of checking the
-- actual number of errors since the transaction might get aborted even before
-- rows are scanned on other segments if one of the segments hits the segment
-- reject limit.
SELECT count(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_txs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_txs_2')
) FOO;

-- TRUNCATE error logs within txs , with segment reject limit reached
-- Populate error log before transaction
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_txs_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_txs_1 WHERE i < 10) e2
WHERE e1.i = e2.i;
SELECT count(*) FROM
(
SELECT * FROM gp_read_error_log('exttab_txs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_txs_2')
) FOO;
BEGIN;
SELECT gp_truncate_error_log('exttab_txs_1');
SELECT gp_truncate_error_log('exttab_txs_2');
-- This should abort the transaction
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_txs_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_txs_2 WHERE i < 10) e2
WHERE e1.i = e2.i;
COMMIT;
-- Additional error rows should have been inserted into the error logs even if the tx is aborted.
-- Truncate of error logs should not be rolled back even if the transaction is aborted. All operation on error logs are persisted. 
-- Check that number of errors is greater than zero instead of checking for
-- actual number of errors, since the transaction might get aborted even before
-- rows are scanned on other segments if one of the segments hits the segment
-- reject limit.
SELECT count(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_txs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_txs_2')
) FOO;

-- Creating external table with error log within txs with segment reject limits reached
SELECT gp_truncate_error_log('exttab_txs_1');
SELECT gp_truncate_error_log('exttab_txs_2');
DROP EXTERNAL TABLE IF EXISTS exttab_txs_3;
DROP EXTERNAL TABLE IF EXISTS exttab_txs_4;
BEGIN;
-- create an external table that will reach segment reject limit
-- reaches reject limit
CREATE EXTERNAL TABLE exttab_txs_3( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
-- new error log, within segment reject limit
CREATE EXTERNAL TABLE exttab_txs_4( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_txs_4 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_txs_4 WHERE i < 10) e2
WHERE e1.i = e2.i order by e1.i;
-- should be populated correctly
SELECT count(*) FROM gp_read_error_log('exttab_txs_4');
-- should error out and abort the transaction
SELECT e1.i, e2.j FROM
(SELECT i, j FROM exttab_txs_3 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_txs_4 WHERE i < 10) e2
WHERE e1.i = e2.i order by e1.i;
COMMIT;
-- Error logs should not exist for these tables that would have been rolled back 
SELECT count(*) FROM gp_read_error_log('exttab_txs_3');
SELECT count(*) FROM gp_read_error_log('exttab_txs_4');
-- external tables created within aborted transactions should not exist
SELECT count(*) FROM exttab_txs_3;
SELECT count(*) FROM exttab_txs_4;

-- UDFS with segment reject limit reached
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_udfs_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit, use the same err table
CREATE EXTERNAL TABLE exttab_udfs_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
CREATE OR REPLACE FUNCTION exttab_udfs_func1 ()
RETURNS boolean
AS $$
BEGIN
  EXECUTE 'SELECT sum(distinct e1.i) as sum_i, sum(distinct e2.i) as sum_j, e1.j as j FROM
	   (SELECT i, j FROM exttab_udfs_1 WHERE i < 5 ) e1,
	   (SELECT i, j FROM exttab_udfs_2 WHERE i < 10) e2
	   group by e1.j';
  RETURN 1;
END;
$$
LANGUAGE plpgsql volatile;
-- Should fail
SELECT * FROM exttab_udfs_func1();
-- Should be populated
SELECT COUNT(*) > 0 FROM 
(
SELECT * FROM gp_read_error_log('exttab_udfs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_udfs_2')
) FOO;

-- INSERT INTO from a udf
DROP TABLE IF EXISTS exttab_udfs_insert_1;
CREATE TABLE exttab_udfs_insert_1(a boolean);
SELECT gp_truncate_error_log('exttab_udfs_1');
SELECT gp_truncate_error_log('exttab_udfs_2');
-- Should fail
INSERT INTO exttab_udfs_insert_1 SELECT * FROM exttab_udfs_func1();
SELECT * FROM exttab_udfs_insert_1;
-- Error table should be populated correctly
SELECT COUNT(*) > 0 FROM 
(
SELECT * FROM gp_read_error_log('exttab_udfs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_udfs_2')
) FOO;

-- UDFs with INSERT INTO with segment reject limit reached
DROP TABLE IF EXISTS exttab_udfs_insert_2;
CREATE TABLE exttab_udfs_insert_2 (LIKE exttab_udfs_1);
CREATE OR REPLACE FUNCTION exttab_udfs_func2 ()
RETURNS boolean
AS $$
DECLARE
 r RECORD;
 cnt integer;
 result boolean;
BEGIN
  SELECT INTO result gp_truncate_error_log('exttab_udfs_1');
  SELECT INTO result gp_truncate_error_log('exttab_udfs_2');
  INSERT INTO exttab_udfs_insert_2
  SELECT i, j from exttab_udfs_1; 
  cnt := 0;
  FOR r in SELECT * FROM gp_read_error_log('exttab_udfs_1') LOOP
      -- just looping through the error log
      cnt := cnt + 1;
  END LOOP;
  IF cnt <= 0 THEN
      RAISE EXCEPTION 'Error log should not be empty';
  END IF;
  SELECT count(*) INTO cnt FROM exttab_udfs_insert_2;
  -- should be 8 
  IF cnt <> 8 THEN
      RAISE EXCEPTION 'Unexpected number of rows inserted';
  END IF;
  -- Now make insert into fail
  INSERT INTO exttab_udfs_insert_2
  SELECT i, j from exttab_udfs_2;
  -- Should not reach here
  cnt := 0;
  FOR r in SELECT * FROM gp_read_error_log('exttab_udfs_2') LOOP
      -- just looping through the error log
      cnt := cnt + 1;
  END LOOP;
  IF cnt <= 0 THEN
      RAISE EXCEPTION 'Error table should not be empty';
  END IF;
  RETURN 1;
END;
$$
LANGUAGE plpgsql volatile;
SELECT gp_truncate_error_log('exttab_udfs_1');
SELECT gp_truncate_error_log('exttab_udfs_2');
-- All this should fail, error logs should be populated even if the UDF gets aborted as we persist error rows written within aborted txs.
SELECT * FROM exttab_udfs_func2();
SELECT COUNT(*) > 0 FROM 
(
SELECT * FROM gp_read_error_log('exttab_udfs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_udfs_2')
) FOO;
SELECT gp_truncate_error_log('exttab_udfs_1');
SELECT gp_truncate_error_log('exttab_udfs_2');
SELECT exttab_udfs_func2();
SELECT COUNT(*) > 0 FROM 
(
SELECT * FROM gp_read_error_log('exttab_udfs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_udfs_2')
) FOO;
SELECT gp_truncate_error_log('exttab_udfs_1');
SELECT gp_truncate_error_log('exttab_udfs_2');
INSERT INTO exttab_udfs_insert_1 SELECT * FROM exttab_udfs_func2();
SELECT COUNT(*) > 0 FROM 
(
SELECT * FROM gp_read_error_log('exttab_udfs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_udfs_2')
) FOO;
SELECT gp_truncate_error_log('exttab_udfs_1');
SELECT gp_truncate_error_log('exttab_udfs_2');
CREATE TABLE exttab_udfs_ctas_2 AS SELECT * FROM exttab_udfs_func2();
SELECT COUNT(*) > 0 FROM 
(
SELECT * FROM gp_read_error_log('exttab_udfs_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_udfs_2')
) FOO;
-- No rows should be inserted into exttab_udfs_insert_2
SELECT * FROM exttab_udfs_insert_2;

-- Scans in union queries with seg reject limit reached
-- does not reach reject limit
DROP EXTERNAL TABLE IF EXISTS exttab_union_1;
DROP EXTERNAL TABLE IF EXISTS exttab_union_2;
CREATE EXTERNAL TABLE exttab_union_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit
CREATE EXTERNAL TABLE exttab_union_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;

-- Should error out as exttab_union_2 would reach it's reject limit
SELECT * FROM
(
SELECT * FROM exttab_union_1
UNION
SELECT * FROM exttab_union_2
) FOO
order by FOO.i;

-- Error table count
select count(*) > 0 from
(
SELECT * FROM gp_read_error_log('exttab_union_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_union_2')
) FOO;

-- Insert into another table, with and without segment reject limits being reached
DROP TABLE IF EXISTS exttab_union_insert_1;
CREATE TABLE exttab_union_insert_1 (LIKE exttab_union_1);
SELECT gp_truncate_error_log('exttab_union_1');
SELECT gp_truncate_error_log('exttab_union_2');
insert into exttab_union_insert_1
SELECT e1.i, e2.j from exttab_union_2 e1 INNER JOIN exttab_union_2 e2 ON e1.i = e2.i
UNION ALL
SELECT e1.i, e2.j from exttab_union_2 e1 INNER JOIN exttab_union_2 e2 ON e1.i = e2.i;
-- should return 0 rows
SELECT * from exttab_union_insert_1;
-- Error table count, should have more than 0 rows, the total number is non-deterministic
select count(*) > 0 from 
(
SELECT * FROM gp_read_error_log('exttab_union_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_union_2')
) FOO;
SELECT gp_truncate_error_log('exttab_union_1');
SELECT gp_truncate_error_log('exttab_union_2');
-- should not error out as exttab_union_1 will not reach segment reject limit
insert into exttab_union_insert_1
SELECT e1.i, e2.j from exttab_union_1 e1 INNER JOIN exttab_union_1 e2 ON e1.i = e2.i
UNION
SELECT e1.i, e2.j from exttab_union_1 e1 INNER JOIN exttab_union_1 e2 ON e1.i = e2.i;
-- should return the right result
SELECT * from exttab_union_insert_1 order by i;
-- Error table count
select count(*) > 0 FROM gp_read_error_log('exttab_union_1');

-- Views reaching segment reject limit
DROP EXTERNAL TABLE IF EXISTS exttab_views_1 CASCADE;
DROP EXTERNAL TABLE IF EXISTS exttab_views_2 CASCADE;
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_views_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit, use the same err table
CREATE EXTERNAL TABLE exttab_views_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
DROP VIEW IF EXISTS exttab_views_3;
CREATE VIEW exttab_views_3 as
SELECT sum(distinct e1.i) as sum_i, sum(distinct e2.i) as sum_j, e1.j as j FROM
(SELECT i, j FROM exttab_views_1 WHERE i < 5 ) e1,
(SELECT i, j FROM exttab_views_2 WHERE i < 10) e2
group by e1.j;
SELECT gp_truncate_error_log('exttab_views_1');
SELECT gp_truncate_error_log('exttab_views_2');
-- This should error out
SELECT * FROM exttab_views_3;
-- Error table should be populated
SELECT count(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_views_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_views_2')
) FOO;

-- INSERT INTO FROM a view
DROP TABLE IF EXISTS exttab_views_insert_1;
CREATE TABLE exttab_views_insert_1 (i int, j int, k text);
SELECT gp_truncate_error_log('exttab_views_1');
SELECT gp_truncate_error_log('exttab_views_2');
-- Should fail
INSERT INTO exttab_views_insert_1 SELECT * FROM exttab_views_3;
-- should not have any rows
SELECT * FROM exttab_views_insert_1; 
-- Error table should be populated
SELECT count(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_views_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_views_2')
) FOO;

-- CTAS from a view with segment reject limit reached
DROP TABLE IF EXISTS exttab_views_ctas_1;
SELECT gp_truncate_error_log('exttab_views_1');
SELECT gp_truncate_error_log('exttab_views_2');
-- Should fail 
CREATE TABLE exttab_views_ctas_1 AS SELECT * FROM exttab_views_3 where j::integer < 100;
SELECT * FROM exttab_views_ctas_1;
-- Error table should be populated
SELECT count(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_views_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_views_2')
) FOO;

-- CTAS FROM view with segment reject limits reached
DROP TABLE IF EXISTS exttab_views_ctas_1;
SELECT gp_truncate_error_log('exttab_views_1');
SELECT gp_truncate_error_log('exttab_views_2');
-- Should fail here 
CREATE TABLE exttab_views_ctas_1 AS SELECT * FROM exttab_views_3;
-- Relation should not exist
SELECT * FROM exttab_views_ctas_1;
-- Error table should be populated
SELECT count(*) > 0 FROM
(
SELECT * FROM gp_read_error_log('exttab_views_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_views_2')
) FOO;

-- Scans in window queries with and without seg reject limit reached
DROP EXTERNAL TABLE IF EXISTS exttab_windows_1;
DROP EXTERNAL TABLE IF EXISTS exttab_windows_2;
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_windows_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit
CREATE EXTERNAL TABLE exttab_windows_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;
-- without reaching segment reject limit
with cte1 as(
 select t1.i as i,
        sum(t2.i) sum_i,
        avg(sum(t2.i)) over
          (partition by t2.j)
          avg_j,
        rank() over
          (partition by t2.j order by t1.j) rnk_j
 from exttab_windows_1 t1, exttab_windows_1 t2
 where t1.i = t2.i
 group by t1.i,t2.j,t1.j
),
cte2 as
(
 select t1.i as i,
        sum(t2.i) sum_i,
        avg(sum(t2.i)) over
          (partition by t2.j)
          avg_j,
        rank() over
          (partition by t2.j order by t1.j) rnk_j
 from exttab_windows_1 t1, exttab_windows_1 t2
 where t1.i = t2.i
 group by t1.i, t2.j, t1.j
)
SELECT * FROM cte1 c1, cte2 c2
WHERE c1.i = c2.i
ORDER BY c1.i
limit 5;
SELECT relname,linenum,errmsg,rawdata FROM gp_read_error_log('exttab_windows_1') ORDER BY linenum LIMIT 2;

-- with reaching segment reject limit
SELECT gp_truncate_error_log('exttab_windows_1');
SELECT gp_truncate_error_log('exttab_windows_2');
with cte1 as(
 select t1.i as i,
        sum(t2.i) sum_i,
        avg(sum(t2.i)) over
          (partition by t2.j)
          avg_j,
        rank() over
          (partition by t2.j order by t1.j) rnk_j
 from exttab_windows_1 t1, exttab_windows_2 t2
 where t1.i = t2.i
 group by t1.i,t2.j,t1.j
),
cte2 as
(
 select t1.i as i,
        sum(t2.i) sum_i,
        avg(sum(t2.i)) over
          (partition by t2.j)
          avg_j,
        rank() over
          (partition by t2.j order by t1.j) rnk_j
 from exttab_windows_1 t1, exttab_windows_2 t2
 where t1.i = t2.i
 group by t1.i, t2.j, t1.j
)
SELECT * FROM cte1 c1, cte2 c2
WHERE c1.i = c2.i
ORDER BY c1.i
limit 5;
SELECT COUNT(*) > 0 
FROM
(
SELECT * FROM gp_read_error_log('exttab_windows_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_windows_2')
) FOO;

-- LIMIT queries without segment reject limit reached
DROP EXTERNAL TABLE IF EXISTS exttab_limit_1 cascade;
DROP EXTERNAL TABLE IF EXISTS exttab_limit_2 cascade;
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_limit_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit, use the same err table
CREATE EXTERNAL TABLE exttab_limit_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 2;

-- Note that even though we use exttab_limit_2 here , the LIMIT 3 will not throw a segment reject limit error
with cte1 as 
(
SELECT e1.i, e2.j FROM exttab_limit_1 e1, exttab_limit_1 e2
WHERE e1.i = e2.i LIMIT 5
)
SELECT * FROM cte1, exttab_limit_2 e3 where cte1.i = e3.i ORDER BY cte1.i LIMIT 3;
SELECT count(*) FROM gp_read_error_log('exttab_limit_1');
SELECT gp_truncate_error_log('exttab_limit_1');
SELECT gp_truncate_error_log('exttab_limit_2');
-- Note that even though we use exttab_limit_2 here , the LIMIT 3 will not throw a segment reject limit error
SELECT * FROM
(
(SELECT * FROM exttab_limit_1 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_1 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_1 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_1 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_1 LIMIT 5)
UNION
(SELECT * FROM exttab_limit_2 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_2 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_2 LIMIT 3)
) FOO
ORDER BY i LIMIT 5;
SELECT COUNT(*) > 0 FROM gp_read_error_log('exttab_limit_1');
SELECT COUNT(*) > 0 FROM gp_read_error_log('exttab_limit_2');

-- LIMIT queries with segment reject limit reached
SELECT gp_truncate_error_log('exttab_limit_1');
SELECT gp_truncate_error_log('exttab_limit_2');
with cte1 as 
(
SELECT e1.i, e2.j FROM exttab_limit_1 e1, exttab_limit_1 e2
WHERE e1.i = e2.i LIMIT 3
)
SELECT * FROM cte1, exttab_limit_2 e3 where cte1.i = e3.i ORDER BY cte1.i LIMIT 5;
SELECT count(*) > 0 FROM gp_read_error_log('exttab_limit_2');
SELECT gp_truncate_error_log('exttab_limit_1');
SELECT gp_truncate_error_log('exttab_limit_2');
SELECT * FROM
(
(SELECT * FROM exttab_limit_1 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_1 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_1 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_1 LIMIT 3)
UNION
(SELECT * FROM exttab_limit_2 LIMIT 5)
UNION
(SELECT * FROM exttab_limit_2 LIMIT 5)
UNION
(SELECT * FROM exttab_limit_2 LIMIT 5)
UNION
(SELECT * FROM exttab_limit_2 LIMIT 5)
) FOO
ORDER BY i LIMIT 5;
SELECT count(*) > 0 FROM 
(
SELECT * FROM gp_read_error_log('exttab_limit_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_limit_2')
) FOO;
-- This query will materialize exttab_limit_2 completely even if LIMIT is just 3 and hence will throw segment reject limit reached
SELECT gp_truncate_error_log('exttab_limit_1');
SELECT gp_truncate_error_log('exttab_limit_2');
SELECT * FROM exttab_limit_1 e1, exttab_limit_2 e2 where e1.i = e2.i LIMIT 3;
SELECT count(*) > 0 FROM 
(
SELECT * FROM gp_read_error_log('exttab_limit_1')
UNION ALL
SELECT * FROM gp_read_error_log('exttab_limit_2')
) FOO;

-- gp_initial_bad_row_limit guc test. This guc allows user to set the initial
-- number of rows which can contain errors before the database stops loading
-- the data. If there is a valid row within the first 'n' rows specified by
-- this guc, the database continues to load the data. 

-- default should be 1000
SHOW gp_initial_bad_row_limit;
DROP EXTERNAL TABLE IF EXISTS exttab_first_reject_limit_1 cascade;
CREATE EXTERNAL TABLE exttab_first_reject_limit_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_first_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 20000;
-- should fail with an appropriate error message
SELECT COUNT(*) FROM exttab_first_reject_limit_1;
SELECT COUNT(*) > 0 FROM gp_read_error_log('exttab_first_reject_limit_1');
-- should work now
SET gp_initial_bad_row_limit = 6000;
SELECT gp_truncate_error_log('exttab_first_reject_limit_1');
SELECT COUNT(*) FROM exttab_first_reject_limit_1;
SELECT COUNT(*) FROM gp_read_error_log('exttab_first_reject_limit_1');
-- first segment reject limit should be checked before segment reject limit
DROP EXTERNAL TABLE IF EXISTS exttab_first_reject_limit_2;
CREATE EXTERNAL TABLE exttab_first_reject_limit_2( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_first_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 500;
-- should report an error saying first rows were rejected
SET gp_initial_bad_row_limit = 2;
SELECT COUNT(*) FROM exttab_first_reject_limit_2;
SELECT COUNT(*) > 0 from gp_read_error_log('exttab_first_reject_limit_2');
-- should report an error saying segment reject limit reached
set gp_initial_bad_row_limit = 600;
SELECT gp_truncate_error_log('exttab_first_reject_limit_2');
SELECT COUNT(*) FROM exttab_first_reject_limit_2;
SELECT COUNT(*) > 0 from gp_read_error_log('exttab_first_reject_limit_2');
-- set unlimited first error rows, should fail only because of segment reject limits
set gp_initial_bad_row_limit = 0;
SELECT gp_truncate_error_log('exttab_first_reject_limit_2');
SELECT COUNT(*) FROM exttab_first_reject_limit_2;
SELECT COUNT(*) > 0 from gp_read_error_log('exttab_first_reject_limit_2');

DROP EXTERNAL TABLE IF EXISTS exttab_heap_join_1;
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_heap_join_1( i int, j text ) 
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|') 
LOG ERRORS SEGMENT REJECT LIMIT 10;
DROP TABLE IF EXISTS test_ext_heap_join;
CREATE TABLE test_ext_heap_join( i int, j text);
-- Turn off stats 
SET gp_autostats_mode = 'NONE';
INSERT INTO test_ext_heap_join SELECT i, i || '_number' FROM generate_series(1, 10) i;
SELECT COUNT(*) FROM test_ext_heap_join, exttab_heap_join_1;
SELECT COUNT(*) FROM gp_read_error_log('exttab_heap_join_1');

\! rm @abs_srcdir@/data/tableless.csv

-- Create external table with 'OPTIONS'
CREATE EXTERNAL TABLE exttab_with_option_empty( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|')
OPTIONS ();

CREATE EXTERNAL TABLE exttab_with_option_1( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|')
OPTIONS (hello 'world');

CREATE EXTERNAL TABLE exttab_with_options( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|')
OPTIONS (hello 'world', bonjour 'again', nihao 'again and again' );

\d exttab_with_options

\d exttab_with_option_empty

DROP EXTERNAL TABLE IF EXISTS exttab_with_option_empty;
DROP EXTERNAL TABLE IF EXISTS exttab_with_option_1;
DROP EXTERNAL TABLE IF EXISTS exttab_with_options;

-- start_ignore
-- drop temp external protocols
DROP PROTOCOL if exists demoprot;
DROP PROTOCOL if exists demoprot2;
-- end_ignore

-- create external protocol
CREATE OR REPLACE FUNCTION write_to_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_export' LANGUAGE C STABLE NO SQL;
CREATE OR REPLACE FUNCTION read_from_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_import' LANGUAGE C STABLE NO SQL;
CREATE TRUSTED PROTOCOL demoprot (readfunc = 'read_from_file', writefunc = 'write_to_file');

-- alter external protocol's name
ALTER PROTOCOL demoprot RENAME TO demoprot2;

-- type name is a fixed-length string padded by '\0', normal(wrong)
-- renaming will make cdbhash() getting different values, select
-- displaying more than oneline here
select distinct ptcname from (
        select ptcname AS ptcname from gp_dist_random('pg_extprotocol')
) all_segments where ptcname = 'demoprot2';

-- drop temp external protocols
DROP PROTOCOL if exists demoprot;
DROP PROTOCOL if exists demoprot2;

-- create external protocol with a serial type column
CREATE EXTERNAL TABLE SERIAL (a serial, x text)
LOCATION ('file://@hostname@@abs_srcdir@/data/no/such/place/serial.tbl')
FORMAT 'csv';

-- drop temp external table
DROP EXTERNAL TABLE IF EXISTS serial;