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 2019/05/22 14:41:57 UTC
[hawq] branch master updated: HAWQ-1711. Add feature test for ORC
format
This is an automated email from the ASF dual-hosted git repository.
huor pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hawq.git
The following commit(s) were added to refs/heads/master by this push:
new 79869d6 HAWQ-1711. Add feature test for ORC format
79869d6 is described below
commit 79869d6a82a7be10221d5d4766ce7525a14d173b
Author: tuyu <tu...@oushu.io>
AuthorDate: Sun May 12 20:17:43 2019 +0800
HAWQ-1711. Add feature test for ORC format
---
.../ans/exttable_extorc_datatype_bool.ans.source | 91 ++
.../ans/exttable_extorc_datatype_char.ans.source | 50 +
.../exttable_extorc_datatype_datetime.ans.source | 188 +++
.../exttable_extorc_datatype_decimal.ans.source | 270 +++++
.../ans/exttable_extorc_datatype_double.ans.source | 42 +
.../exttable_extorc_datatype_integer.ans.source | 48 +
.../ans/exttable_extorc_datatype_money.ans.source | 27 +
.../exttable_extorc_datatype_varchar.ans.source | 42 +
.../ans/exttable_extorc_encoding.ans.source | 37 +
.../ans/exttable_extorc_negativepath.ans.source | 225 ++++
.../ans/exttable_extorc_normalpath.ans | 103 ++
.../ans/exttable_extorc_normalpath.ans.source | 103 ++
.../ans/exttable_extorc_same_name_test.ans.source | 69 ++
.../ans/exttable_extorc_testtruncate.ans.source | 22 +
.../sql/exttable_extorc_datatype_bool.sql.source | 27 +
.../sql/exttable_extorc_datatype_char.sql.source | 27 +
.../exttable_extorc_datatype_datetime.sql.source | 98 ++
.../exttable_extorc_datatype_decimal.sql.source | 92 ++
.../sql/exttable_extorc_datatype_double.sql.source | 17 +
.../exttable_extorc_datatype_integer.sql.source | 17 +
.../sql/exttable_extorc_datatype_money.sql.source | 14 +
.../exttable_extorc_datatype_varchar.sql.source | 18 +
.../sql/exttable_extorc_encoding.sql.source | 17 +
.../sql/exttable_extorc_negativepath.sql.source | 89 ++
.../sql/exttable_extorc_normalpath.sql | 49 +
.../sql/exttable_extorc_normalpath.sql.source | 49 +
.../sql/exttable_extorc_same_name_test.sql.source | 33 +
.../sql/exttable_extorc_testtruncate.sql.source | 5 +
.../feature/ExternalSource/test_extfmt_orc.cpp | 1253 ++++++++++++++++++++
src/test/feature/lib/compent_config.cpp | 327 +++++
src/test/feature/lib/compent_config.h | 139 +++
src/test/feature/lib/hdfs_config.cpp | 387 +++---
src/test/feature/lib/hdfs_config.h | 28 +-
33 files changed, 3838 insertions(+), 165 deletions(-)
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_bool.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_bool.ans.source
new file mode 100644
index 0000000..282ebe7
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_bool.ans.source
@@ -0,0 +1,91 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestDatatypeBool;
+SET
+-- end_ignore
+--start_ignore
+set new_executor=auto;
+SET
+--end_ignore
+create table tb (i int, b boolean);
+CREATE TABLE
+insert into tb values(1, TRUE);
+INSERT 0 1
+insert into tb values(2, 't');
+INSERT 0 1
+insert into tb values(3, 'true');
+INSERT 0 1
+insert into tb values(4, 'y');
+INSERT 0 1
+insert into tb values(5, 'yes');
+INSERT 0 1
+insert into tb values(6, '1');
+INSERT 0 1
+insert into tb values(7, FALSE);
+INSERT 0 1
+insert into tb values(8, 'f');
+INSERT 0 1
+insert into tb values(9, 'false');
+INSERT 0 1
+insert into tb values(10, 'n');
+INSERT 0 1
+insert into tb values(11, 'no');
+INSERT 0 1
+insert into tb values(12, '0');
+INSERT 0 1
+create writable external table twb_e (i int, b boolean) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_bool/twb_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trb_e (i int, b boolean) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_bool/twb_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into twb_e select * from tb;
+INSERT 0 12
+select * from tb order by i;
+ i | b
+----+---
+ 1 | t
+ 2 | t
+ 3 | t
+ 4 | t
+ 5 | t
+ 6 | t
+ 7 | f
+ 8 | f
+ 9 | f
+ 10 | f
+ 11 | f
+ 12 | f
+(12 rows)
+
+select * from twb_e order by i;
+ i | b
+----+---
+ 1 | t
+ 2 | t
+ 3 | t
+ 4 | t
+ 5 | t
+ 6 | t
+ 7 | f
+ 8 | f
+ 9 | f
+ 10 | f
+ 11 | f
+ 12 | f
+(12 rows)
+
+select * from trb_e order by i;
+ i | b
+----+---
+ 1 | t
+ 2 | t
+ 3 | t
+ 4 | t
+ 5 | t
+ 6 | t
+ 7 | f
+ 8 | f
+ 9 | f
+ 10 | f
+ 11 | f
+ 12 | f
+(12 rows)
+
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_char.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_char.ans.source
new file mode 100644
index 0000000..7ae1f05
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_char.ans.source
@@ -0,0 +1,50 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestDatatypeChar;
+SET
+-- end_ignore
+create table tc (i int, c character);
+CREATE TABLE
+create table tcn (i int, cn character(6));
+CREATE TABLE
+insert into tc values (1, 'a');
+INSERT 0 1
+insert into tc values (2, 'b');
+INSERT 0 1
+insert into tc values (3, 'c');
+INSERT 0 1
+insert into tcn values (1, 'a');
+INSERT 0 1
+insert into tcn values (2, 'bbb');
+INSERT 0 1
+insert into tcn values (3, 'cccccc');
+INSERT 0 1
+create writable external table twc_e (i int, c character) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_char/twc_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trc_e (i int, c character) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_char/twc_e') format 'orc';
+CREATE EXTERNAL TABLE
+create writable external table twcn_e (i int, cn character(6)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_char/twcn_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trcn_e (i int, cn character(6)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_char/twcn_e') format 'orc';
+CREATE EXTERNAL TABLE
+-- insert into twc_e select * from tc;
+-- insert into twcn_e select * from tcn;
+select * from tc order by i;
+ i | c
+---+---
+ 1 | a
+ 2 | b
+ 3 | c
+(3 rows)
+
+-- select * from twc_e order by i;
+-- select * from trc_e order by i;
+select * from tcn order by i;
+ i | cn
+---+--------
+ 1 | a
+ 2 | bbb
+ 3 | cccccc
+(3 rows)
+
+-- select * from twcn_e order by i;
+-- select * from trcn_e order by i;
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_datetime.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_datetime.ans.source
new file mode 100644
index 0000000..daf0a00
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_datetime.ans.source
@@ -0,0 +1,188 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestDatatypeDateTime;
+SET
+-- end_ignore
+--start_ignore
+set new_executor=auto;
+SET
+--end_ignore
+-- date
+create table td (i int, d date);
+CREATE TABLE
+insert into td values (1, '2017-07-01'::date);
+INSERT 0 1
+insert into td values (2, 'Jul-02-17'::date);
+INSERT 0 1
+insert into td values (2, '2017.300'::date);
+INSERT 0 1
+create writable external table twd_e (i int, d date) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twd_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trd_e (i int, d date) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twd_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into twd_e select * from td;
+INSERT 0 3
+select * from td order by i;
+ i | d
+---+------------
+ 1 | 07-01-2017
+ 2 | 07-02-2017
+ 2 | 10-27-2017
+(3 rows)
+
+-- select * from twd_e order by i;
+-- select * from trd_e order by i;
+-- time
+create table tt (i int, t time);
+CREATE TABLE
+create table ttntz(i int, t time without time zone);
+CREATE TABLE
+create table tttz(i int, t time with time zone);
+CREATE TABLE
+insert into tt values (1, '19:18:17'::time);
+INSERT 0 1
+insert into ttntz values (1, '19:18:17'::time without time zone);
+INSERT 0 1
+insert into tttz values (1, '19:18:17-08:00'::time with time zone);
+INSERT 0 1
+create writable external table twt_e (i int, t time) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twt_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trt_e (i int, t time) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twt_e') format 'orc';
+CREATE EXTERNAL TABLE
+create writable external table twtntz_e (i int, t time without time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtntz_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trtntz_e (i int, t time without time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtntz_e') format 'orc';
+CREATE EXTERNAL TABLE
+create writable external table twttz_e (i int, t time with time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twttz_e') format 'orc' (compresstype 'lz4');
+psql:/tmp/TestExtOrc_TestDatatypeDateTime.sql:38: ERROR: unsupported data types 1266 for columns of external ORC table is specified.
+create readable external table trttz_e (i int, t time with time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twttz_e') format 'orc';
+psql:/tmp/TestExtOrc_TestDatatypeDateTime.sql:39: ERROR: unsupported data types 1266 for columns of external ORC table is specified.
+-- insert into twt_e select * from tt;
+select * from tt order by i;
+ i | t
+---+----------
+ 1 | 19:18:17
+(1 row)
+
+-- select * from twt_e order by i;
+-- select * from trt_e order by i;
+-- insert into twtntz_e select * from ttntz;
+select * from ttntz order by i;
+ i | t
+---+----------
+ 1 | 19:18:17
+(1 row)
+
+-- select * from twtntz_e order by i;
+-- select * from trtntz_e order by i;
+-- insert into twttz_e select * from tttz;
+select * from tttz order by i;
+ i | t
+---+-------------
+ 1 | 19:18:17-08
+(1 row)
+
+-- select * from twttz_e order by i;
+-- select * from trttz_e order by i;
+-- timestamp
+create table tts (i int, t timestamp);
+CREATE TABLE
+create table ttsntz(i int, t timestamp without time zone);
+CREATE TABLE
+create table ttstz(i int, t timestamp with time zone);
+CREATE TABLE
+insert into tts values (1, '2017-07-01 19:18:17'::timestamp);
+INSERT 0 1
+insert into ttsntz values (1, '2017-07-01 19:18:17'::timestamp without time zone);
+INSERT 0 1
+insert into ttstz values (1, '2017-07-01 19:18:17-08:00'::timestamp with time zone);
+INSERT 0 1
+create writable external table twts_e (i int, t timestamp) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twts_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trts_e (i int, t timestamp) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twts_e') format 'orc';
+CREATE EXTERNAL TABLE
+create writable external table twtsntz_e (i int, t timestamp without time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtsntz_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trtsntz_e (i int, t timestamp without time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtsntz_e') format 'orc';
+CREATE EXTERNAL TABLE
+create writable external table twtstz_e (i int, t timestamp with time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtstz_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trtstz_e (i int, t timestamp with time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtstz_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into twts_e select * from tts;
+INSERT 0 1
+select * from tts order by i;
+ i | t
+---+--------------------------
+ 1 | Sat Jul 01 19:18:17 2017
+(1 row)
+
+select * from twts_e order by i;
+ i | t
+---+--------------------------
+ 1 | Sat Jul 01 19:18:17 2017
+(1 row)
+
+select * from trts_e order by i;
+ i | t
+---+--------------------------
+ 1 | Sat Jul 01 19:18:17 2017
+(1 row)
+
+insert into twtsntz_e select * from ttsntz;
+INSERT 0 1
+select * from ttsntz order by i;
+ i | t
+---+--------------------------
+ 1 | Sat Jul 01 19:18:17 2017
+(1 row)
+
+select * from twtsntz_e order by i;
+ i | t
+---+--------------------------
+ 1 | Sat Jul 01 19:18:17 2017
+(1 row)
+
+select * from trtsntz_e order by i;
+ i | t
+---+--------------------------
+ 1 | Sat Jul 01 19:18:17 2017
+(1 row)
+
+insert into twtstz_e select * from ttstz;
+INSERT 0 1
+select * from ttstz order by i;
+ i | t
+---+------------------------------
+ 1 | Sat Jul 01 20:18:17 2017 PDT
+(1 row)
+
+select * from twtstz_e order by i;
+ i | t
+---+------------------------------
+ 1 | Sat Jul 01 20:18:17 2017 PDT
+(1 row)
+
+select * from trtstz_e order by i;
+ i | t
+---+------------------------------
+ 1 | Sat Jul 01 20:18:17 2017 PDT
+(1 row)
+
+-- interval
+create table tit (i int, it interval);
+CREATE TABLE
+insert into tit values (1, '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval);
+INSERT 0 1
+create writable external table twit_e (i int, it interval) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twit_e') format 'orc' (compresstype 'lz4');
+psql:/tmp/TestExtOrc_TestDatatypeDateTime.sql:94: ERROR: unsupported data types 1186 for columns of external ORC table is specified.
+create readable external table trit_e (i int, it interval) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twit_e') format 'orc';
+psql:/tmp/TestExtOrc_TestDatatypeDateTime.sql:95: ERROR: unsupported data types 1186 for columns of external ORC table is specified.
+-- insert into twit_e select * from tit;
+select * from tit order by i;
+ i | it
+---+-------------------------------
+ 1 | 1 year 2 mons 3 days 04:05:06
+(1 row)
+
+-- select * from twit_e order by i;
+-- select * from trit_e order by i;
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_decimal.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_decimal.ans.source
new file mode 100644
index 0000000..2f42cd9
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_decimal.ans.source
@@ -0,0 +1,270 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestDatatypeDecimal;
+SET
+-- end_ignore
+-- start_ignore
+set new_executor=off;
+SET
+-- end_ignore
+create table td (i int, d decimal(5, 1));
+CREATE TABLE
+create table tn (i int, n numeric(7, 2));
+CREATE TABLE
+insert into td values(1, 1111.1);
+INSERT 0 1
+insert into td values(2, 2222.2);
+INSERT 0 1
+insert into td values(3, 3333.3);
+INSERT 0 1
+insert into tn values(1, 11111.11);
+INSERT 0 1
+insert into tn values(2, 22222.22);
+INSERT 0 1
+insert into tn values(3, 33333.33);
+INSERT 0 1
+create writable external table twd_e (i int, d decimal(5, 1)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_decimal/twd_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trd_e (i int, d decimal(5, 1)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_decimal/twd_e') format 'orc';
+CREATE EXTERNAL TABLE
+create writable external table twn_e (i int, n numeric(7, 2)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_decimal/twn_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trn_e (i int, n numeric(7, 2)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_decimal/twn_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into twd_e select * from td;
+INSERT 0 3
+insert into twn_e select * from td;
+INSERT 0 3
+select * from td order by i;
+ i | d
+---+--------
+ 1 | 1111.1
+ 2 | 2222.2
+ 3 | 3333.3
+(3 rows)
+
+select * from twd_e order by i;
+ i | d
+---+--------
+ 1 | 1111.1
+ 2 | 2222.2
+ 3 | 3333.3
+(3 rows)
+
+select * from trd_e order by i;
+ i | d
+---+--------
+ 1 | 1111.1
+ 2 | 2222.2
+ 3 | 3333.3
+(3 rows)
+
+select * from tn order by i;
+ i | n
+---+----------
+ 1 | 11111.11
+ 2 | 22222.22
+ 3 | 33333.33
+(3 rows)
+
+select * from twn_e order by i;
+ i | n
+---+---------
+ 1 | 1111.10
+ 2 | 2222.20
+ 3 | 3333.30
+(3 rows)
+
+select * from trn_e order by i;
+ i | n
+---+---------
+ 1 | 1111.10
+ 2 | 2222.20
+ 3 | 3333.30
+(3 rows)
+
+create table numeric_table0(id int, salary decimal(18,9)) format 'orc';
+CREATE TABLE
+-- create table numeric_table1(id int, salary decimal(100,50)) format 'orc';
+create table numeric_table2(id int, salary decimal(2000,3)) format 'orc';
+psql:/tmp/TestExtOrc_TestDatatypeDecimal.sql:37: ERROR: DECIMAL precision 2000 must be between 1 and 1000
+create table numeric_table3(id int, salary decimal(1000,3000)) format 'orc';
+psql:/tmp/TestExtOrc_TestDatatypeDecimal.sql:38: ERROR: DECIMAL scale 3000 must be between 0 and precision 1000
+insert into numeric_table0 values(1, 1212.54);
+INSERT 0 1
+insert into numeric_table0 values(2, 12123.557);
+INSERT 0 1
+insert into numeric_table0 values(3, 12121.54);
+INSERT 0 1
+insert into numeric_table0 values(4, -121224343.5846544);
+INSERT 0 1
+insert into numeric_table0 values(4, 324332.582342);
+INSERT 0 1
+insert into numeric_table0 values(5, 'nan');
+INSERT 0 1
+insert into numeric_table0 values(6, 0);
+INSERT 0 1
+-- insert into numeric_table1 values(0, 0);
+-- insert into numeric_table1 values(1, 1234343543535435345436.3243543543524654243524353);
+-- insert into numeric_table1 values(5, -324343435332434.4324315435524541);
+select * from numeric_table0;
+ id | salary
+----+----------------------
+ 1 | 1212.540000000
+ 2 | 12123.557000000
+ 3 | 12121.540000000
+ 4 | -121224343.584654400
+ 4 | 324332.582342000
+ 5 |
+ 6 | 0.000000000
+(7 rows)
+
+-- select * from numeric_table1;
+select * from numeric_table0 where id > 3 order by salary;
+ id | salary
+----+----------------------
+ 4 | -121224343.584654400
+ 6 | 0.000000000
+ 4 | 324332.582342000
+ 5 |
+(4 rows)
+
+-- select * from numeric_table1 where salary > 0;
+select salary + 1 from numeric_table0;
+ ?column?
+----------------------
+ 1213.540000000
+ 12124.557000000
+ 12122.540000000
+ -121224342.584654400
+ 324333.582342000
+
+ 1.000000000
+(7 rows)
+
+select salary - 2 from numeric_table0;
+ ?column?
+----------------------
+ 1210.540000000
+ 12121.557000000
+ 12119.540000000
+ -121224345.584654400
+ 324330.582342000
+
+ -2.000000000
+(7 rows)
+
+select salary * 12 from numeric_table0;
+ ?column?
+-----------------------
+ 14550.480000000
+ 145482.684000000
+ 145458.480000000
+ -1454692123.015852800
+ 3891990.988104000
+
+ 0.000000000
+(7 rows)
+
+select salary / 2 from numeric_table0;
+ ?column?
+------------------------
+ 606.2700000000000000
+ 6061.7785000000000000
+ 6060.7700000000000000
+ -60612171.792327200000
+ 162166.291171000000
+
+ 0.00000000000000000000
+(7 rows)
+
+select avg(salary) from numeric_table0;
+ avg
+------------------------
+ -20145758.894218733333
+(1 row)
+
+select max(salary) from numeric_table0;
+ max
+------------------
+ 324332.582342000
+(1 row)
+
+select min(salary) from numeric_table0;
+ min
+----------------------
+ -121224343.584654400
+(1 row)
+
+select round(salary) from numeric_table0;
+ round
+------------
+ 1213
+ 12124
+ 12122
+ -121224344
+ 324333
+
+ 0
+(7 rows)
+
+create table test_scale(d decimal(25,10)) format 'orc';
+CREATE TABLE
+insert into test_scale values
+(100000000000000),
+(10000000000000),
+(1000000000000),
+(100000000000),
+(10000000000),
+(1000000000),
+(100000000),
+(10000000),
+(1000000),
+(100000),
+(10000),
+(1000),
+(100),
+(10),
+(1),
+(0.1),
+(0.01),
+(0.001),
+(0.0001),
+(0.00001),
+(0.000001),
+(0.0000001),
+(0.00000001),
+(0.000000001),
+(0.0000000001),
+(0);
+INSERT 0 26
+select * from test_scale order by d;
+ d
+----------------------------
+ 0.0000000000
+ 0.0000000001
+ 0.0000000010
+ 0.0000000100
+ 0.0000001000
+ 0.0000010000
+ 0.0000100000
+ 0.0001000000
+ 0.0010000000
+ 0.0100000000
+ 0.1000000000
+ 1.0000000000
+ 10.0000000000
+ 100.0000000000
+ 1000.0000000000
+ 10000.0000000000
+ 100000.0000000000
+ 1000000.0000000000
+ 10000000.0000000000
+ 100000000.0000000000
+ 1000000000.0000000000
+ 10000000000.0000000000
+ 100000000000.0000000000
+ 1000000000000.0000000000
+ 10000000000000.0000000000
+ 100000000000000.0000000000
+(26 rows)
+
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_double.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_double.ans.source
new file mode 100644
index 0000000..9e0cafd
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_double.ans.source
@@ -0,0 +1,42 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestDatatypeDouble;
+SET
+-- end_ignore
+create table td (i int, f float, r real, dp double precision);
+CREATE TABLE
+insert into td values(1, 1.1, 1.11, 1.111);
+INSERT 0 1
+insert into td values(2, 2.2, 2.22, 2.222);
+INSERT 0 1
+insert into td values(3, 3.3, 3.33, 3.333);
+INSERT 0 1
+create writable external table twd_e (i int, f float, r real, dp double precision) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_double/twd_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trd_e (i int, f float, r real, dp double precision) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_double/twd_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into twd_e select * from td;
+INSERT 0 3
+select * from td order by i;
+ i | f | r | dp
+---+-----+------+-------
+ 1 | 1.1 | 1.11 | 1.111
+ 2 | 2.2 | 2.22 | 2.222
+ 3 | 3.3 | 3.33 | 3.333
+(3 rows)
+
+select * from twd_e order by i;
+ i | f | r | dp
+---+-----+------+-------
+ 1 | 1.1 | 1.11 | 1.111
+ 2 | 2.2 | 2.22 | 2.222
+ 3 | 3.3 | 3.33 | 3.333
+(3 rows)
+
+select * from trd_e order by i;
+ i | f | r | dp
+---+-----+------+-------
+ 1 | 1.1 | 1.11 | 1.111
+ 2 | 2.2 | 2.22 | 2.222
+ 3 | 3.3 | 3.33 | 3.333
+(3 rows)
+
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_integer.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_integer.ans.source
new file mode 100644
index 0000000..8e87895
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_integer.ans.source
@@ -0,0 +1,48 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestDatatypeInteger;
+SET
+-- end_ignore
+create table ti (i int, si smallint, ii int, bi bigint, s serial, bs bigserial);
+psql:/tmp/TestExtOrc_TestDatatypeInteger.sql:4: NOTICE: CREATE TABLE will create implicit sequence "ti_s_seq" for serial column "ti.s"
+psql:/tmp/TestExtOrc_TestDatatypeInteger.sql:4: NOTICE: CREATE TABLE will create implicit sequence "ti_bs_seq" for serial column "ti.bs"
+CREATE TABLE
+insert into ti values(1, -32768, -2147483648, -9223372036854775808);
+INSERT 0 1
+insert into ti values(2, 0, 0, 0);
+INSERT 0 1
+insert into ti values(3, 32767, 2147483647, 9223372036854775807);
+INSERT 0 1
+create writable external table twi_e (i int, si smallint, ii int, bi bigint, s serial, bs bigserial) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_integer/twi_e') format 'orc' (compresstype 'lz4');
+psql:/tmp/TestExtOrc_TestDatatypeInteger.sql:10: NOTICE: CREATE EXTERNAL TABLE will create implicit sequence "twi_e_s_seq" for serial column "twi_e.s"
+psql:/tmp/TestExtOrc_TestDatatypeInteger.sql:10: NOTICE: CREATE EXTERNAL TABLE will create implicit sequence "twi_e_bs_seq" for serial column "twi_e.bs"
+CREATE EXTERNAL TABLE
+create readable external table tri_e (i int, si smallint, ii int, bi bigint, s serial, bs bigserial) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_integer/twi_e') format 'orc';
+psql:/tmp/TestExtOrc_TestDatatypeInteger.sql:11: NOTICE: CREATE EXTERNAL TABLE will create implicit sequence "tri_e_s_seq" for serial column "tri_e.s"
+psql:/tmp/TestExtOrc_TestDatatypeInteger.sql:11: NOTICE: CREATE EXTERNAL TABLE will create implicit sequence "tri_e_bs_seq" for serial column "tri_e.bs"
+CREATE EXTERNAL TABLE
+insert into twi_e select * from ti;
+INSERT 0 3
+select * from ti order by i;
+ i | si | ii | bi | s | bs
+---+--------+-------------+----------------------+---+----
+ 1 | -32768 | -2147483648 | -9223372036854775808 | 1 | 1
+ 2 | 0 | 0 | 0 | 2 | 2
+ 3 | 32767 | 2147483647 | 9223372036854775807 | 3 | 3
+(3 rows)
+
+select * from twi_e order by i;
+ i | si | ii | bi | s | bs
+---+--------+-------------+----------------------+---+----
+ 1 | -32768 | -2147483648 | -9223372036854775808 | 1 | 1
+ 2 | 0 | 0 | 0 | 2 | 2
+ 3 | 32767 | 2147483647 | 9223372036854775807 | 3 | 3
+(3 rows)
+
+select * from tri_e order by i;
+ i | si | ii | bi | s | bs
+---+--------+-------------+----------------------+---+----
+ 1 | -32768 | -2147483648 | -9223372036854775808 | 1 | 1
+ 2 | 0 | 0 | 0 | 2 | 2
+ 3 | 32767 | 2147483647 | 9223372036854775807 | 3 | 3
+(3 rows)
+
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_money.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_money.ans.source
new file mode 100644
index 0000000..890c840
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_money.ans.source
@@ -0,0 +1,27 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestDatatypeMoney;
+SET
+-- end_ignore
+create table tm (i int, m money);
+CREATE TABLE
+insert into tm values(1, '1'::money);
+INSERT 0 1
+insert into tm values(2, '2.2'::money);
+INSERT 0 1
+insert into tm values(3, '$3,333.33');
+INSERT 0 1
+create writable external table twm_e (i int, m money) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_money/twm_e') format 'orc' (compresstype 'lz4');
+psql:/tmp/TestExtOrc_TestDatatypeMoney.sql:10: ERROR: unsupported data types 790 for columns of external ORC table is specified.
+create readable external table trm_e (i int, m money) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_money/twm_e') format 'orc';
+psql:/tmp/TestExtOrc_TestDatatypeMoney.sql:11: ERROR: unsupported data types 790 for columns of external ORC table is specified.
+-- insert into twm_e select * from tm;
+select * from tm order by i;
+ i | m
+---+-----------
+ 1 | $1.00
+ 2 | $2.20
+ 3 | $3,333.33
+(3 rows)
+
+-- select * from twm_e order by i;
+-- select * from trm_e order by i;
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_varchar.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_varchar.ans.source
new file mode 100644
index 0000000..592a37b
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_datatype_varchar.ans.source
@@ -0,0 +1,42 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestDatatypeVarchar;
+SET
+-- end_ignore
+create table tv (i int, cv char varying, cvn char varying(6), v varchar, vn varchar(8), t text);
+CREATE TABLE
+insert into tv values (1, 'aa', 'aaa', 'aaaa', 'aaaaa', 'aaaaaaaa');
+INSERT 0 1
+insert into tv values (2, 'bb', 'bbb', 'bbbb', 'bbbbb', 'bbbbbbbb');
+INSERT 0 1
+insert into tv values (3, 'cc', 'ccc', 'cccc', 'ccccc', 'cccccccc');
+INSERT 0 1
+create writable external table twv_e (i int, cv char varying, cvn char varying(6), v varchar, vn varchar(8), t text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_varchar/twv_e') format 'orc' (compresstype 'lz4');
+CREATE EXTERNAL TABLE
+create readable external table trv_e (i int, cv char varying, cvn char varying(6), v varchar, vn varchar(8), t text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_varchar/twv_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into twv_e select * from tv;
+INSERT 0 3
+select * from tv order by i;
+ i | cv | cvn | v | vn | t
+---+----+-----+------+-------+----------
+ 1 | aa | aaa | aaaa | aaaaa | aaaaaaaa
+ 2 | bb | bbb | bbbb | bbbbb | bbbbbbbb
+ 3 | cc | ccc | cccc | ccccc | cccccccc
+(3 rows)
+
+select * from twv_e order by i;
+ i | cv | cvn | v | vn | t
+---+----+-----+------+-------+----------
+ 1 | aa | aaa | aaaa | aaaaa | aaaaaaaa
+ 2 | bb | bbb | bbbb | bbbbb | bbbbbbbb
+ 3 | cc | ccc | cccc | ccccc | cccccccc
+(3 rows)
+
+select * from trv_e order by i;
+ i | cv | cvn | v | vn | t
+---+----+-----+------+-------+----------
+ 1 | aa | aaa | aaaa | aaaaa | aaaaaaaa
+ 2 | bb | bbb | bbbb | bbbbb | bbbbbbbb
+ 3 | cc | ccc | cccc | ccccc | cccccccc
+(3 rows)
+
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_encoding.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_encoding.ans.source
new file mode 100644
index 0000000..0a3ca9a
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_encoding.ans.source
@@ -0,0 +1,37 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestEncoding;
+SET
+-- end_ignore
+create table te (i int, t text);
+CREATE TABLE
+insert into te values(1, 'abc');
+INSERT 0 1
+insert into te values(2, E'中国');
+INSERT 0 1
+create writable external table twe_e (i int, t text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/encoding/twe_e') format 'orc' (compresstype 'lz4') encoding 'utf8';
+CREATE EXTERNAL TABLE
+create readable external table tre_e (i int, t text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/encoding/twe_e') format 'orc' encoding 'utf8';
+CREATE EXTERNAL TABLE
+insert into twe_e select * from te;
+INSERT 0 2
+select * from te order by i;
+ i | t
+---+------
+ 1 | abc
+ 2 | 中国
+(2 rows)
+
+select * from twe_e order by i;
+ i | t
+---+------
+ 1 | abc
+ 2 | 中国
+(2 rows)
+
+select * from tre_e order by i;
+ i | t
+---+------
+ 1 | abc
+ 2 | 中国
+(2 rows)
+
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_negativepath.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_negativepath.ans.source
new file mode 100644
index 0000000..7cbc24c
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_negativepath.ans.source
@@ -0,0 +1,225 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestNegativePath;
+SET
+-- end_ignore
+-- start_ignore
+drop table if exists neg_orc_table1, neg_orc_table2, neg_orc_table3, neg_orc_table4, neg_orc_table5, neg_orc_testdrop,
+ neg_orc_table6, neg_orc_table7, neg_orc_table8, neg_orc_table9, neg_orc_table10, neg_orc_table11, neg_orc_table12, neg_orc_table13, neg_orc_table14,
+ neg_orc_table15 ,neg_orc_table16 ,neg_orc_table17 ,neg_orc_table18, neg_orc_table19, neg_orc_table20;
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table1" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table2" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table3" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table4" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table5" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_testdrop" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table6" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table7" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table8" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table9" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table10" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table11" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table12" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table13" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table14" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table15" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table16" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table17" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table18" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table19" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:7: NOTICE: table "neg_orc_table20" does not exist, skipping
+DROP TABLE
+drop external table if exists neg_orc_table1_e, neg_orc_table2_e, neg_orc_table3_e, neg_orc_table4_e, neg_orc_table5_e, neg_orc_table6_e, neg_orc_table7_e,
+ neg_orc_table8_e, neg_orc_table9_e, neg_orc_table10_e, neg_orc_table11_e, neg_orc_table12_e, neg_orc_table13_e, neg_orc_table14_e, neg_orc_table15_e, neg_orc_table16_e,
+ neg_orc_table17_e, neg_orc_table18_e, neg_orc_table19_e, neg_orc_table20_e, neg_orc_table21_e, neg_orc_table22_e, neg_orc_table23_e, neg_orc_table24_e, neg_orc_table25_e,
+ neg_orc_testdrop_e, neg_orc_table26_e, neg_orc_table27_e, neg_orc_table28_e;
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table1_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table2_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table3_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table4_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table5_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table6_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table7_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table8_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table9_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table10_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table11_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table12_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table13_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table14_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table15_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table16_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table17_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table18_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table19_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table20_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table21_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table22_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table23_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table24_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table25_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_testdrop_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table26_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table27_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNegativePath.sql:11: NOTICE: table "neg_orc_table28_e" does not exist, skipping
+DROP EXTERNAL TABLE
+-- end_ignore
+-- internal table
+create writable table neg_orc_table1 (i int, j float) format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:14: ERROR: syntax error at or near "table"
+LINE 1: create writable table neg_orc_table1 (i int, j float) format...
+ ^
+create readable table neg_orc_table2 (i int, j float) format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:15: ERROR: syntax error at or near "table"
+LINE 1: create readable table neg_orc_table2 (i int, j float) format...
+ ^
+-- internal table can not location
+create table neg_orc_table3 (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table3') format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:18: ERROR: LOCATION or EXECUTE may not be used for native table
+HINT: Use CREATE TABLE FORMAT or CREATE EXTERNAL TABLE instead
+-- internal table can not execute
+create table neg_orc_table4(i int) execute() format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:21: ERROR: syntax error at or near "("
+LINE 1: create table neg_orc_table4(i int) execute() format 'orc';
+ ^
+-- test can not support create index statement
+create table neg_orc_table5(i int ,j float, primary key(i)) format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:24: ERROR: Cannot support create index statement yet
+create table neg_orc_table6(i int ,j float, check(i>5)) format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:25: ERROR: Do not support table constrait other than primary key
+create table neg_orc_table7(i int not null, f float) format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:26: ERROR: Do not support column constrait
+-- test compresstype
+create table neg_orc_table8(i int, j float) format 'orc' (compresstype lz4);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:30: ERROR: invalid value for option compresstype_lz4: "1"
+create table neg_orc_table9(i int, j float) format 'orc' (compresstype snappy);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:31: ERROR: invalid value for option compresstype_snappy: "1"
+create table neg_orc_table10(i int, j float) format 'orc' (compresstype none);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:32: ERROR: syntax error at or near ")"
+LINE 1: ...rc_table10(i int, j float) format 'orc' (compresstype none);
+ ^
+create table neg_orc_table11(i int, j float) format 'orc' (compresstype fndsngsjng);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:33: ERROR: invalid value for option compresstype_fndsngsjng: "1"
+create table neg_orc_table12(i int, j float) format 'orc' (compresstype 'fndsngsjng');
+psql:/tmp/TestExtOrc_TestNegativePath.sql:34: ERROR: invalid value for option compresstype: "fndsngsjng"
+create table neg_orc_table13(i int, j float) format 'orc' (rlecoder 'v');
+psql:/tmp/TestExtOrc_TestNegativePath.sql:35: ERROR: Option "rlecoder" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create table neg_orc_table14(i int, j float) format 'orc' (rlecoder v);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:36: ERROR: Option "rlecoder_v" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create table neg_orc_table15(i int, j float) format orc;
+psql:/tmp/TestExtOrc_TestNegativePath.sql:38: ERROR: syntax error at or near "orc"
+LINE 1: create table neg_orc_table15(i int, j float) format orc;
+ ^
+create table neg_orc_table16(i int, j float) format 'orc' encoding utf8;
+psql:/tmp/TestExtOrc_TestNegativePath.sql:39: ERROR: syntax error at or near "utf8"
+LINE 1: ... neg_orc_table16(i int, j float) format 'orc' encoding utf8;
+ ^
+-- test like
+create table neg_orc_table17(i int, j float) format 'orc';
+CREATE TABLE
+create table neg_orc_table18(i text, like neg_orc_table17) format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:43: ERROR: column "i" duplicated
+-- test distributed
+create table neg_orc_table19(i int, j float) format 'orc' distributed by i;
+psql:/tmp/TestExtOrc_TestNegativePath.sql:46: ERROR: syntax error at or near "i"
+LINE 1: ...g_orc_table19(i int, j float) format 'orc' distributed by i;
+ ^
+create table neg_orc_table20(i int, j float) format 'orc' (escape #);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:48: ERROR: syntax error at or near "#"
+LINE 1: ...ble neg_orc_table20(i int, j float) format 'orc' (escape #);
+ ^
+-- writable external orc table
+--external table can not be web
+create writable external web table neg_orc_table1_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table1_e') format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:53: ERROR: an EXTERNAL WEB TABLE may only use http URI's, problem in: 'hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table1_e'
+HINT: Use CREATE EXTERNAL TABLE instead.
+create writable external table neg_orc_table2_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table2_e');
+psql:/tmp/TestExtOrc_TestNegativePath.sql:54: ERROR: syntax error at or near ";"
+LINE 1: ...efault/exttable_extorc_test_negativepath/neg_orc_table2_e');
+ ^
+create writable external table neg_orc_table3_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table3_e') format 'parquet';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:55: ERROR: LOCATION using hdfs url 'hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table3_e' does not support 'parquet' format
+HINT: Use "FORMAT 'orc', 'text', or 'csv'" instead
+create writable external table neg_orc_table4_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table4_e') format 'orc' (compresstype 'zlib') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:56: ERROR: invalid value for option compresstype: "zlib"
+create writable external table neg_orc_table5_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table5_e') format 'orc' (rlecoder 'v') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:57: ERROR: Option "rlecoder" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create writable external table neg_orc_table6_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table6_e') format 'orc' (rlecoder 'v1') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:58: ERROR: Option "rlecoder" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create writable external table neg_orc_table7_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table7_e') format 'orc' (rlecoder 'v3') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:59: ERROR: Option "rlecoder" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create writable external table neg_orc_table8_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table8_e') format 'orc' encoding 'us-ascii';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:60: ERROR: "us-ascii" is not a valid encoding for ORC external table. Encoding for ORC external table must be UTF8.
+create writable external table neg_orc_table9_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table9_e') format 'orc' encoding 'gbk';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:61: ERROR: "gbk" is not a valid encoding for ORC external table. Encoding for ORC external table must be UTF8.
+create writable external table neg_orc_table10_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table10_e') format 'orc' (compresstype 'lz4' compresstype 'lz4') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:62: ERROR: conflicting or redundant options "compresstype"
+create writable external table neg_orc_table11_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table11_e') format 'orc' (compresstype 'lz4' escape '#') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:63: ERROR: Option "escape" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create writable external table neg_orc_table12_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table12_e') format 'orc' (compresstype 'lz4' delimiter ',') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:64: ERROR: Option "delimiter" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create writable external table neg_orc_table13_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table13_e') format 'orc' (compresstype 'lz4' escape '#') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:65: ERROR: Option "escape" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create writable external table neg_orc_table14_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table14_e') format 'magma';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:66: ERROR: LOCATION using hdfs url 'hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table14_e' does not support 'magma' format
+HINT: Use "FORMAT 'orc', 'text', or 'csv'" instead
+-- readable external orc table
+create readable external table neg_orc_table15_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn01_e');
+psql:/tmp/TestExtOrc_TestNegativePath.sql:69: ERROR: syntax error at or near ";"
+LINE 1: ...000@@path@@exttable_extorc_test_negativepath/rn01_e');
+ ^
+create readable external table neg_orc_table16_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn02_e') format 'parquet';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:70: ERROR: LOCATION using hdfs url 'hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn02_e' does not support 'parquet' format
+HINT: Use "FORMAT 'orc', 'text', or 'csv'" instead
+create readable external table neg_orc_table17_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn03_e') format 'orc' encoding 'gbk';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:71: ERROR: "gbk" is not a valid encoding for ORC external table. Encoding for ORC external table must be UTF8.
+create readable external table neg_orc_table18_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn04_e') format 'orc' (compresstype 'none') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:72: ERROR: redundant option compresstype
+create readable external table neg_orc_table19_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn05_e') format 'orc' (compresstype 'lz4') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:73: ERROR: redundant option compresstype
+create readable external table neg_orc_table20_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn06_e') format 'orc' (compresstype 'zlib') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:74: ERROR: redundant option compresstype
+create readable external table neg_orc_table21_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn07_e') format 'orc' (rlecoder 'v') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:75: ERROR: Option "rlecoder" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create readable external table neg_orc_table22_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn08_e') format 'orc' (rlecoder 'v1') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:76: ERROR: Option "rlecoder" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create readable external table neg_orc_table23_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn09_e') format 'orc' (rlecoder 'v3') encoding 'utf8';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:77: ERROR: Option "rlecoder" for ORC table is invalid
+HINT: Format options for ORC table must be either formatter, or compresstype
+create readable external table neg_orc_table24_e (i int) location ('magma://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table24_e') format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:78: ERROR: LOCATION using magma url 'magma://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table24_e' does not support 'orc' format
+HINT: Use "FORMAT 'magma'" instead
+create readable external web table neg_orc_table25_e(i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table25_e') format 'orc';
+psql:/tmp/TestExtOrc_TestNegativePath.sql:79: ERROR: an EXTERNAL WEB TABLE may only use http URI's, problem in: 'hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table25_e'
+HINT: Use CREATE EXTERNAL TABLE instead.
+create readable external table neg_orc_table26_e(i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table26_e') format 'orc' (compresstype lz4);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:80: ERROR: redundant option compresstype_lz4
+create readable external table neg_orc_table27_e(i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table27_e') format 'orc' (compresstype snappy);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:81: ERROR: redundant option compresstype_snappy
+create readable external table neg_orc_table28_e(i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table28_e') format 'orc' (compresstype none);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:82: ERROR: syntax error at or near ")"
+LINE 1: ...vepath/neg_orc_table28_e') format 'orc' (compresstype none);
+ ^
+-- test drop
+create table neg_orc_testdrop(i int) format 'orc';
+CREATE TABLE
+create readable external table neg_orc_testdrop_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test/normalpath/neg_orc_testdrop_e') format 'orc';
+CREATE EXTERNAL TABLE
+-- test can not insert for readable
+insert into neg_orc_testdrop_e values (1);
+psql:/tmp/TestExtOrc_TestNegativePath.sql:89: ERROR: cannot change a readable external table "neg_orc_testdrop_e"
+drop external table neg_orc_testdrop;
+psql:/tmp/TestExtOrc_TestNegativePath.sql:91: ERROR: "neg_orc_testdrop" is not an external table
+HINT: Use DROP TABLE to remove a base table
+drop table neg_orc_testdrop_e;
+psql:/tmp/TestExtOrc_TestNegativePath.sql:92: ERROR: "neg_orc_testdrop_e" is not a base table
+HINT: Use DROP EXTERNAL TABLE to remove an external table
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_normalpath.ans b/src/test/feature/ExternalSource/ans/exttable_extorc_normalpath.ans
new file mode 100644
index 0000000..2ced6cd
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_normalpath.ans
@@ -0,0 +1,103 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestNormalPath;
+SET
+-- end_ignore
+-- inter table
+-- start_ignore
+drop table if exists normal_orctable1, normal_orctable2, normal_orctable3, normal_orctable4, normal_orctable5;
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable1" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable2" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable3" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable4" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable5" does not exist, skipping
+DROP TABLE
+drop external table if exists normal_orctable1_e, normal_orctable2_e, normal_orctable3_e, normal_orctable4_e, normal_orctable5_e, normal_orctable6_e, normal_orctable7_e, normal_orctable8_e;
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable1_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable2_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable3_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable4_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable5_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable6_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable7_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable8_e" does not exist, skipping
+DROP EXTERNAL TABLE
+-- end_ignore
+-- create normal table
+create table normal_orctable1 (i int, j float) format 'orc';
+CREATE TABLE
+create table normal_orctable2 (i int, j float) format 'orc' encoding 'utf8';
+CREATE TABLE
+create table normal_orctable3 (i int, j float) format 'orc' (compresstype 'snappy') encoding 'utf8';
+CREATE TABLE
+create table normal_orctable4 (i int, j float) format 'orc' (compresstype 'none') encoding 'utf8';
+CREATE TABLE
+create table normal_orctable5 (i int, j float) format 'orc' (compresstype 'lz4') encoding 'utf8';
+CREATE TABLE
+create table normal_orctable6 (k text, like normal_orctable1) format 'orc';
+CREATE TABLE
+create table normal_orctable7 (i int, j float) format 'orc' distributed by (i);
+CREATE TABLE
+-- writable external orc table
+create writable external table normal_orctable1_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable1_e') format 'orc';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable2_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable2_e') format 'orc' encoding 'utf8';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable3_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable3_e') format 'orc' () encoding 'utf8';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable4_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable4_e') format 'orc' (compresstype 'snappy') encoding 'utf8';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable5_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable5_e') format 'orc' (compresstype 'none') encoding 'utf8';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable6_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable6_e') format 'orc' (compresstype 'lz4') encoding 'utf8';
+CREATE EXTERNAL TABLE
+-- readable external orc table
+create readable external table normal_orctable7_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable7_e') format 'orc';
+CREATE EXTERNAL TABLE
+create readable external table normal_orctable8_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable8_e') format 'orc' encoding 'utf8';
+CREATE EXTERNAL TABLE
+-- test insert
+insert into normal_orctable1 values (1,0.1);
+INSERT 0 1
+insert into normal_orctable1 values (2,0.1);
+INSERT 0 1
+insert into normal_orctable1 values (3,0.1);
+INSERT 0 1
+select * from normal_orctable1;
+ i | j
+---+-----
+ 1 | 0.1
+ 2 | 0.1
+ 3 | 0.1
+(3 rows)
+
+insert into normal_orctable1_e values (1,0.1);
+INSERT 0 1
+insert into normal_orctable1_e values (2,0.1);
+INSERT 0 1
+insert into normal_orctable1_e values (3,0.1);
+INSERT 0 1
+select * from normal_orctable1_e;
+ i | j
+---+-----
+ 1 | 0.1
+ 2 | 0.1
+ 3 | 0.1
+(3 rows)
+
+select c.relname, e.fmttype, e.fmtopts, e.command, e.rejectlimit, e.rejectlimittype, e.fmterrtbl, e.encoding, e.writable
+from pg_class c, pg_exttable e
+where c.oid=e.reloid and c.relname in ('normal_orctable1_e', 'normal_orctable2_e', 'normal_orctable3_e', 'normal_orctable4_e', 'normal_orctable5_e', 'normal_orctable6_e', 'normal_orctable7_e', 'normal_orctable8_e')
+order by c.relname;
+ relname | fmttype | fmtopts | command | rejectlimit | rejectlimittype | fmterrtbl | encoding | writable
+--------------------+---------+------------------------------------------------------------+---------+-------------+-----------------+-----------+----------+----------
+ normal_orctable1_e | b | formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable2_e | b | formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable3_e | b | formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable4_e | b | compresstype 'snappy' formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable5_e | b | compresstype 'none' formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable6_e | b | compresstype 'lz4' formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable7_e | b | formatter 'orc' category 'external' | | | | | 6 | f
+ normal_orctable8_e | b | formatter 'orc' category 'external' | | | | | 6 | f
+(8 rows)
+
+-- test drop
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_normalpath.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_normalpath.ans.source
new file mode 100644
index 0000000..5dc52f2
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_normalpath.ans.source
@@ -0,0 +1,103 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestNormalPath;
+SET
+-- end_ignore
+-- inter table
+-- start_ignore
+drop table if exists normal_orctable1, normal_orctable2, normal_orctable3, normal_orctable4, normal_orctable5;
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable1" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable2" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable3" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable4" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:6: NOTICE: table "normal_orctable5" does not exist, skipping
+DROP TABLE
+drop external table if exists normal_orctable1_e, normal_orctable2_e, normal_orctable3_e, normal_orctable4_e, normal_orctable5_e, normal_orctable6_e, normal_orctable7_e, normal_orctable8_e;
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable1_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable2_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable3_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable4_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable5_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable6_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable7_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestNormalPath.sql:7: NOTICE: table "normal_orctable8_e" does not exist, skipping
+DROP EXTERNAL TABLE
+-- end_ignore
+-- create normal table
+create table normal_orctable1 (i int, j float) format 'orc';
+CREATE TABLE
+create table normal_orctable2 (i int, j float) format 'orc' encoding 'utf8';
+CREATE TABLE
+create table normal_orctable3 (i int, j float) format 'orc' (compresstype 'snappy') encoding 'utf8';
+CREATE TABLE
+create table normal_orctable4 (i int, j float) format 'orc' (compresstype 'none') encoding 'utf8';
+CREATE TABLE
+create table normal_orctable5 (i int, j float) format 'orc' (compresstype 'lz4') encoding 'utf8';
+CREATE TABLE
+create table normal_orctable6 (k text, like normal_orctable1) format 'orc';
+CREATE TABLE
+create table normal_orctable7 (i int, j float) format 'orc' distributed by (i);
+CREATE TABLE
+-- writable external orc table
+create writable external table normal_orctable1_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable1_e') format 'orc';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable2_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable2_e') format 'orc' encoding 'utf8';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable3_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable3_e') format 'orc' () encoding 'utf8';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable4_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable4_e') format 'orc' (compresstype 'snappy') encoding 'utf8';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable5_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable5_e') format 'orc' (compresstype 'none') encoding 'utf8';
+CREATE EXTERNAL TABLE
+create writable external table normal_orctable6_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable6_e') format 'orc' (compresstype 'lz4') encoding 'utf8';
+CREATE EXTERNAL TABLE
+-- readable external orc table
+create readable external table normal_orctable7_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable7_e') format 'orc';
+CREATE EXTERNAL TABLE
+create readable external table normal_orctable8_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable8_e') format 'orc' encoding 'utf8';
+CREATE EXTERNAL TABLE
+-- test insert
+insert into normal_orctable1 values (1,0.1);
+INSERT 0 1
+insert into normal_orctable1 values (2,0.1);
+INSERT 0 1
+insert into normal_orctable1 values (3,0.1);
+INSERT 0 1
+select * from normal_orctable1;
+ i | j
+---+-----
+ 1 | 0.1
+ 2 | 0.1
+ 3 | 0.1
+(3 rows)
+
+insert into normal_orctable1_e values (1,0.1);
+INSERT 0 1
+insert into normal_orctable1_e values (2,0.1);
+INSERT 0 1
+insert into normal_orctable1_e values (3,0.1);
+INSERT 0 1
+select * from normal_orctable1_e;
+ i | j
+---+-----
+ 1 | 0.1
+ 2 | 0.1
+ 3 | 0.1
+(3 rows)
+
+select c.relname, e.fmttype, e.fmtopts, e.command, e.rejectlimit, e.rejectlimittype, e.fmterrtbl, e.encoding, e.writable
+from pg_class c, pg_exttable e
+where c.oid=e.reloid and c.relname in ('normal_orctable1_e', 'normal_orctable2_e', 'normal_orctable3_e', 'normal_orctable4_e', 'normal_orctable5_e', 'normal_orctable6_e', 'normal_orctable7_e', 'normal_orctable8_e')
+order by c.relname;
+ relname | fmttype | fmtopts | command | rejectlimit | rejectlimittype | fmterrtbl | encoding | writable
+--------------------+---------+------------------------------------------------------------+---------+-------------+-----------------+-----------+----------+----------
+ normal_orctable1_e | b | formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable2_e | b | formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable3_e | b | formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable4_e | b | compresstype 'snappy' formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable5_e | b | compresstype 'none' formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable6_e | b | compresstype 'lz4' formatter 'orc' category 'external' | | | | | 6 | t
+ normal_orctable7_e | b | formatter 'orc' category 'external' | | | | | 6 | f
+ normal_orctable8_e | b | formatter 'orc' category 'external' | | | | | 6 | f
+(8 rows)
+
+-- test drop
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_same_name_test.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_same_name_test.ans.source
new file mode 100644
index 0000000..e9abd27
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_same_name_test.ans.source
@@ -0,0 +1,69 @@
+-- start_ignore
+SET SEARCH_PATH=TestExtOrc_TestSameName;
+SET
+-- end_ignore
+-- start_ignore
+drop external table if exists test_same_name_orctable1_e, test_same_name_orctable2_e, test_same_name_orctable3_e, test_same_name_orctable4_e;
+psql:/tmp/TestExtOrc_TestSameName.sql:5: NOTICE: table "test_same_name_orctable1_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestSameName.sql:5: NOTICE: table "test_same_name_orctable2_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestSameName.sql:5: NOTICE: table "test_same_name_orctable3_e" does not exist, skipping
+psql:/tmp/TestExtOrc_TestSameName.sql:5: NOTICE: table "test_same_name_orctable4_e" does not exist, skipping
+DROP EXTERNAL TABLE
+drop table if exists test_same_orctable1_e;
+psql:/tmp/TestExtOrc_TestSameName.sql:6: NOTICE: table "test_same_orctable1_e" does not exist, skipping
+DROP TABLE
+-- end_ignore
+-- test different external table using same name but have different columns
+create writable external table test_same_name_orctable1_e (i int, j float, k text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/same_name_test/test_same_name_orctable1_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into test_same_name_orctable1_e values(1,0.1,'aaa');
+INSERT 0 1
+create writable external table test_same_name_orctable2_e(i int, j float) location('hdfs://@@host@@@@path@@exttable_extorc_test/same_name_test/test_same_name_orctable1_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into test_same_name_orctable2_e values(2,0.2);
+INSERT 0 1
+-- test different external table using same name and also same columns
+create writable external table test_same_name_orctable3_e(i int, j float) location('hdfs://@@host@@@@path@@exttable_extorc_test/same_name_test/test_same_name_orctable3_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into test_same_name_orctable3_e values(3,0.3);
+INSERT 0 1
+create writable external table test_same_name_orctable4_e(i int, j float) location('hdfs://@@host@@@@path@@exttable_extorc_test/same_name_test/test_same_name_orctable3_e') format 'orc';
+CREATE EXTERNAL TABLE
+insert into test_same_name_orctable4_e values(4,0.4);
+INSERT 0 1
+select * from test_same_name_orctable1_e;
+psql:/tmp/TestExtOrc_TestSameName.sql:26: ERROR: NewExecutorRun: failed to do MyExecutorRun. Invalid column selected 2 out of 2 (2600) (newExecutor.c:69) (seg0 127.0.0.1:40000 pid=80025)
+select * from test_same_name_orctable2_e;
+ i | j
+---+-----
+ 1 | 0.1
+ 2 | 0.2
+(2 rows)
+
+select * from test_same_name_orctable3_e;
+ i | j
+---+-----
+ 3 | 0.3
+ 4 | 0.4
+(2 rows)
+
+select * from test_same_name_orctable4_e;
+ i | j
+---+-----
+ 3 | 0.3
+ 4 | 0.4
+(2 rows)
+
+-- test the internal and external table using the same name
+create table test_same_name_orctable3_e(i int, j float) format 'orc';
+psql:/tmp/TestExtOrc_TestSameName.sql:32: ERROR: relation "test_same_name_orctable3_e" already exists
+insert into test_same_name_orctable3_e values(5,0.5);
+INSERT 0 1
+select * from test_same_name_orctable3_e;
+ i | j
+---+-----
+ 3 | 0.3
+ 4 | 0.4
+ 5 | 0.5
+(3 rows)
+
diff --git a/src/test/feature/ExternalSource/ans/exttable_extorc_testtruncate.ans.source b/src/test/feature/ExternalSource/ans/exttable_extorc_testtruncate.ans.source
new file mode 100644
index 0000000..7ad4245
--- /dev/null
+++ b/src/test/feature/ExternalSource/ans/exttable_extorc_testtruncate.ans.source
@@ -0,0 +1,22 @@
+-- start_ignore
+SET SEARCH_PATH=TETExtorc_TestTruncate;
+SET
+-- end_ignore
+create external table exttable_extorc_testtruncate (a int) location('hdfs://@@host@@@@path@@exttable_extorc_testtruncate') format'orc';
+CREATE EXTERNAL TABLE
+insert into exttable_extorc_testtruncate select generate_series(1,1000);
+INSERT 0 1000
+select count(*) from exttable_extorc_testtruncate;
+ count
+-------
+ 1000
+(1 row)
+
+truncate table exttable_extorc_testtruncate;
+TRUNCATE TABLE
+select count(*) from exttable_extorc_testtruncate;
+ count
+-------
+ 0
+(1 row)
+
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_bool.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_bool.sql.source
new file mode 100644
index 0000000..aa9cefc
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_bool.sql.source
@@ -0,0 +1,27 @@
+--start_ignore
+set new_executor=auto;
+--end_ignore
+
+create table tb (i int, b boolean);
+
+insert into tb values(1, TRUE);
+insert into tb values(2, 't');
+insert into tb values(3, 'true');
+insert into tb values(4, 'y');
+insert into tb values(5, 'yes');
+insert into tb values(6, '1');
+insert into tb values(7, FALSE);
+insert into tb values(8, 'f');
+insert into tb values(9, 'false');
+insert into tb values(10, 'n');
+insert into tb values(11, 'no');
+insert into tb values(12, '0');
+
+create writable external table twb_e (i int, b boolean) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_bool/twb_e') format 'orc' (compresstype 'lz4');
+create readable external table trb_e (i int, b boolean) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_bool/twb_e') format 'orc';
+
+insert into twb_e select * from tb;
+
+select * from tb order by i;
+select * from twb_e order by i;
+select * from trb_e order by i;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_char.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_char.sql.source
new file mode 100644
index 0000000..2c3a6a5
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_char.sql.source
@@ -0,0 +1,27 @@
+create table tc (i int, c character);
+create table tcn (i int, cn character(6));
+
+insert into tc values (1, 'a');
+insert into tc values (2, 'b');
+insert into tc values (3, 'c');
+
+insert into tcn values (1, 'a');
+insert into tcn values (2, 'bbb');
+insert into tcn values (3, 'cccccc');
+
+create writable external table twc_e (i int, c character) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_char/twc_e') format 'orc' (compresstype 'lz4');
+create readable external table trc_e (i int, c character) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_char/twc_e') format 'orc';
+
+create writable external table twcn_e (i int, cn character(6)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_char/twcn_e') format 'orc' (compresstype 'lz4');
+create readable external table trcn_e (i int, cn character(6)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_char/twcn_e') format 'orc';
+
+-- insert into twc_e select * from tc;
+-- insert into twcn_e select * from tcn;
+
+select * from tc order by i;
+-- select * from twc_e order by i;
+-- select * from trc_e order by i;
+
+select * from tcn order by i;
+-- select * from twcn_e order by i;
+-- select * from trcn_e order by i;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_datetime.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_datetime.sql.source
new file mode 100644
index 0000000..7bd29c3
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_datetime.sql.source
@@ -0,0 +1,98 @@
+--start_ignore
+set new_executor=auto;
+--end_ignore
+-- date
+create table td (i int, d date);
+
+insert into td values (1, '2017-07-01'::date);
+insert into td values (2, 'Jul-02-17'::date);
+insert into td values (2, '2017.300'::date);
+
+create writable external table twd_e (i int, d date) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twd_e') format 'orc' (compresstype 'lz4');
+create readable external table trd_e (i int, d date) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twd_e') format 'orc';
+
+insert into twd_e select * from td;
+
+select * from td order by i;
+-- select * from twd_e order by i;
+-- select * from trd_e order by i;
+
+-- time
+create table tt (i int, t time);
+create table ttntz(i int, t time without time zone);
+create table tttz(i int, t time with time zone);
+
+insert into tt values (1, '19:18:17'::time);
+insert into ttntz values (1, '19:18:17'::time without time zone);
+insert into tttz values (1, '19:18:17-08:00'::time with time zone);
+
+create writable external table twt_e (i int, t time) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twt_e') format 'orc' (compresstype 'lz4');
+create readable external table trt_e (i int, t time) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twt_e') format 'orc';
+
+create writable external table twtntz_e (i int, t time without time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtntz_e') format 'orc' (compresstype 'lz4');
+create readable external table trtntz_e (i int, t time without time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtntz_e') format 'orc';
+
+create writable external table twttz_e (i int, t time with time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twttz_e') format 'orc' (compresstype 'lz4');
+create readable external table trttz_e (i int, t time with time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twttz_e') format 'orc';
+
+-- insert into twt_e select * from tt;
+select * from tt order by i;
+-- select * from twt_e order by i;
+-- select * from trt_e order by i;
+
+-- insert into twtntz_e select * from ttntz;
+select * from ttntz order by i;
+-- select * from twtntz_e order by i;
+-- select * from trtntz_e order by i;
+
+-- insert into twttz_e select * from tttz;
+select * from tttz order by i;
+-- select * from twttz_e order by i;
+-- select * from trttz_e order by i;
+
+-- timestamp
+create table tts (i int, t timestamp);
+create table ttsntz(i int, t timestamp without time zone);
+create table ttstz(i int, t timestamp with time zone);
+
+insert into tts values (1, '2017-07-01 19:18:17'::timestamp);
+insert into ttsntz values (1, '2017-07-01 19:18:17'::timestamp without time zone);
+insert into ttstz values (1, '2017-07-01 19:18:17-08:00'::timestamp with time zone);
+
+create writable external table twts_e (i int, t timestamp) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twts_e') format 'orc' (compresstype 'lz4');
+create readable external table trts_e (i int, t timestamp) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twts_e') format 'orc';
+
+create writable external table twtsntz_e (i int, t timestamp without time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtsntz_e') format 'orc' (compresstype 'lz4');
+create readable external table trtsntz_e (i int, t timestamp without time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtsntz_e') format 'orc';
+
+create writable external table twtstz_e (i int, t timestamp with time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtstz_e') format 'orc' (compresstype 'lz4');
+create readable external table trtstz_e (i int, t timestamp with time zone) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twtstz_e') format 'orc';
+
+insert into twts_e select * from tts;
+select * from tts order by i;
+select * from twts_e order by i;
+select * from trts_e order by i;
+
+insert into twtsntz_e select * from ttsntz;
+select * from ttsntz order by i;
+select * from twtsntz_e order by i;
+select * from trtsntz_e order by i;
+
+insert into twtstz_e select * from ttstz;
+select * from ttstz order by i;
+select * from twtstz_e order by i;
+select * from trtstz_e order by i;
+
+-- interval
+create table tit (i int, it interval);
+
+insert into tit values (1, '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval);
+
+create writable external table twit_e (i int, it interval) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twit_e') format 'orc' (compresstype 'lz4');
+create readable external table trit_e (i int, it interval) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_datetime/twit_e') format 'orc';
+
+-- insert into twit_e select * from tit;
+
+select * from tit order by i;
+-- select * from twit_e order by i;
+-- select * from trit_e order by i;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_decimal.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_decimal.sql.source
new file mode 100644
index 0000000..bf56e35
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_decimal.sql.source
@@ -0,0 +1,92 @@
+-- start_ignore
+set new_executor=off;
+-- end_ignore
+create table td (i int, d decimal(5, 1));
+create table tn (i int, n numeric(7, 2));
+
+insert into td values(1, 1111.1);
+insert into td values(2, 2222.2);
+insert into td values(3, 3333.3);
+
+insert into tn values(1, 11111.11);
+insert into tn values(2, 22222.22);
+insert into tn values(3, 33333.33);
+
+create writable external table twd_e (i int, d decimal(5, 1)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_decimal/twd_e') format 'orc' (compresstype 'lz4');
+create readable external table trd_e (i int, d decimal(5, 1)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_decimal/twd_e') format 'orc';
+
+create writable external table twn_e (i int, n numeric(7, 2)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_decimal/twn_e') format 'orc' (compresstype 'lz4');
+create readable external table trn_e (i int, n numeric(7, 2)) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_decimal/twn_e') format 'orc';
+
+insert into twd_e select * from td;
+insert into twn_e select * from td;
+
+select * from td order by i;
+select * from twd_e order by i;
+select * from trd_e order by i;
+
+select * from tn order by i;
+select * from twn_e order by i;
+select * from trn_e order by i;
+
+create table numeric_table0(id int, salary decimal(18,9)) format 'orc';
+-- create table numeric_table1(id int, salary decimal(100,50)) format 'orc';
+create table numeric_table2(id int, salary decimal(2000,3)) format 'orc';
+create table numeric_table3(id int, salary decimal(1000,3000)) format 'orc';
+
+insert into numeric_table0 values(1, 1212.54);
+insert into numeric_table0 values(2, 12123.557);
+insert into numeric_table0 values(3, 12121.54);
+insert into numeric_table0 values(4, -121224343.5846544);
+insert into numeric_table0 values(4, 324332.582342);
+insert into numeric_table0 values(5, 'nan');
+insert into numeric_table0 values(6, 0);
+
+-- insert into numeric_table1 values(0, 0);
+-- insert into numeric_table1 values(1, 1234343543535435345436.3243543543524654243524353);
+-- insert into numeric_table1 values(5, -324343435332434.4324315435524541);
+
+select * from numeric_table0;
+-- select * from numeric_table1;
+
+select * from numeric_table0 where id > 3 order by salary;
+-- select * from numeric_table1 where salary > 0;
+
+select salary + 1 from numeric_table0;
+select salary - 2 from numeric_table0;
+select salary * 12 from numeric_table0;
+select salary / 2 from numeric_table0;
+select avg(salary) from numeric_table0;
+select max(salary) from numeric_table0;
+select min(salary) from numeric_table0;
+select round(salary) from numeric_table0;
+
+create table test_scale(d decimal(25,10)) format 'orc';
+insert into test_scale values
+(100000000000000),
+(10000000000000),
+(1000000000000),
+(100000000000),
+(10000000000),
+(1000000000),
+(100000000),
+(10000000),
+(1000000),
+(100000),
+(10000),
+(1000),
+(100),
+(10),
+(1),
+(0.1),
+(0.01),
+(0.001),
+(0.0001),
+(0.00001),
+(0.000001),
+(0.0000001),
+(0.00000001),
+(0.000000001),
+(0.0000000001),
+(0);
+select * from test_scale order by d;
\ No newline at end of file
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_double.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_double.sql.source
new file mode 100644
index 0000000..eb52f07
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_double.sql.source
@@ -0,0 +1,17 @@
+-- start_ignore
+set new_executor=off;
+-- end_ignore
+create table td (i int, f float, r real, dp double precision);
+
+insert into td values(1, 1.1, 1.11, 1.111);
+insert into td values(2, 2.2, 2.22, 2.222);
+insert into td values(3, 3.3, 3.33, 3.333);
+
+create writable external table twd_e (i int, f float, r real, dp double precision) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_double/twd_e') format 'orc' (compresstype 'lz4');
+create readable external table trd_e (i int, f float, r real, dp double precision) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_double/twd_e') format 'orc';
+
+insert into twd_e select * from td;
+
+select * from td order by i;
+select * from twd_e order by i;
+select * from trd_e order by i;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_integer.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_integer.sql.source
new file mode 100644
index 0000000..e47d9bb
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_integer.sql.source
@@ -0,0 +1,17 @@
+--start_ignore
+set new_executor=auto;
+--end_ignore
+create table ti (i int, si smallint, ii int, bi bigint, s serial, bs bigserial);
+
+insert into ti values(1, -32768, -2147483648, -9223372036854775808);
+insert into ti values(2, 0, 0, 0);
+insert into ti values(3, 32767, 2147483647, 9223372036854775807);
+
+create writable external table twi_e (i int, si smallint, ii int, bi bigint, s serial, bs bigserial) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_integer/twi_e') format 'orc' (compresstype 'lz4');
+create readable external table tri_e (i int, si smallint, ii int, bi bigint, s serial, bs bigserial) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_integer/twi_e') format 'orc';
+
+insert into twi_e select * from ti;
+
+select * from ti order by i;
+select * from twi_e order by i;
+select * from tri_e order by i;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_money.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_money.sql.source
new file mode 100644
index 0000000..79b066c
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_money.sql.source
@@ -0,0 +1,14 @@
+create table tm (i int, m money);
+
+insert into tm values(1, '1'::money);
+insert into tm values(2, '2.2'::money);
+insert into tm values(3, '$3,333.33');
+
+create writable external table twm_e (i int, m money) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_money/twm_e') format 'orc' (compresstype 'lz4');
+create readable external table trm_e (i int, m money) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_money/twm_e') format 'orc';
+
+-- insert into twm_e select * from tm;
+
+select * from tm order by i;
+-- select * from twm_e order by i;
+-- select * from trm_e order by i;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_varchar.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_varchar.sql.source
new file mode 100644
index 0000000..423aac5
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_datatype_varchar.sql.source
@@ -0,0 +1,18 @@
+--start_ignore
+set new_executor=auto;
+--end_ignore
+
+create table tv (i int, cv char varying, cvn char varying(6), v varchar, vn varchar(8), t text);
+
+insert into tv values (1, 'aa', 'aaa', 'aaaa', 'aaaaa', 'aaaaaaaa');
+insert into tv values (2, 'bb', 'bbb', 'bbbb', 'bbbbb', 'bbbbbbbb');
+insert into tv values (3, 'cc', 'ccc', 'cccc', 'ccccc', 'cccccccc');
+
+create writable external table twv_e (i int, cv char varying, cvn char varying(6), v varchar, vn varchar(8), t text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_varchar/twv_e') format 'orc' (compresstype 'lz4');
+create readable external table trv_e (i int, cv char varying, cvn char varying(6), v varchar, vn varchar(8), t text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/datatype_varchar/twv_e') format 'orc';
+
+insert into twv_e select * from tv;
+
+select * from tv order by i;
+select * from twv_e order by i;
+select * from trv_e order by i;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_encoding.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_encoding.sql.source
new file mode 100644
index 0000000..3597f17
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_encoding.sql.source
@@ -0,0 +1,17 @@
+--start_ignore
+set new_executor=auto;
+--end_ignore
+
+create table te (i int, t text);
+
+insert into te values(1, 'abc');
+insert into te values(2, E'中国');
+
+create writable external table twe_e (i int, t text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/encoding/twe_e') format 'orc' (compresstype 'lz4') encoding 'utf8';
+create readable external table tre_e (i int, t text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/encoding/twe_e') format 'orc' encoding 'utf8';
+
+insert into twe_e select * from te;
+
+select * from te order by i;
+select * from twe_e order by i;
+select * from tre_e order by i;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_negativepath.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_negativepath.sql.source
new file mode 100644
index 0000000..d0002cb
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_negativepath.sql.source
@@ -0,0 +1,89 @@
+-- start_ignore
+drop table if exists neg_orc_table1, neg_orc_table2, neg_orc_table3, neg_orc_table4, neg_orc_table5, neg_orc_testdrop,
+ neg_orc_table6, neg_orc_table7, neg_orc_table8, neg_orc_table9, neg_orc_table10, neg_orc_table11, neg_orc_table12, neg_orc_table13, neg_orc_table14,
+ neg_orc_table15 ,neg_orc_table16 ,neg_orc_table17 ,neg_orc_table18, neg_orc_table19, neg_orc_table20;
+drop external table if exists neg_orc_table1_e, neg_orc_table2_e, neg_orc_table3_e, neg_orc_table4_e, neg_orc_table5_e, neg_orc_table6_e, neg_orc_table7_e,
+ neg_orc_table8_e, neg_orc_table9_e, neg_orc_table10_e, neg_orc_table11_e, neg_orc_table12_e, neg_orc_table13_e, neg_orc_table14_e, neg_orc_table15_e, neg_orc_table16_e,
+ neg_orc_table17_e, neg_orc_table18_e, neg_orc_table19_e, neg_orc_table20_e, neg_orc_table21_e, neg_orc_table22_e, neg_orc_table23_e, neg_orc_table24_e, neg_orc_table25_e,
+ neg_orc_testdrop_e, neg_orc_table26_e, neg_orc_table27_e, neg_orc_table28_e;
+-- end_ignore
+-- internal table
+create writable table neg_orc_table1 (i int, j float) format 'orc';
+create readable table neg_orc_table2 (i int, j float) format 'orc';
+
+-- internal table can not location
+create table neg_orc_table3 (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table3') format 'orc';
+
+-- internal table can not execute
+create table neg_orc_table4(i int) execute() format 'orc';
+
+-- test can not support create index statement
+create table neg_orc_table5(i int ,j float, primary key(i)) format 'orc';
+create table neg_orc_table6(i int ,j float, check(i>5)) format 'orc';
+create table neg_orc_table7(i int not null, f float) format 'orc';
+
+
+-- test compresstype
+create table neg_orc_table8(i int, j float) format 'orc' (compresstype lz4);
+create table neg_orc_table9(i int, j float) format 'orc' (compresstype snappy);
+create table neg_orc_table10(i int, j float) format 'orc' (compresstype none);
+create table neg_orc_table11(i int, j float) format 'orc' (compresstype fndsngsjng);
+create table neg_orc_table12(i int, j float) format 'orc' (compresstype 'fndsngsjng');
+create table neg_orc_table13(i int, j float) format 'orc' (rlecoder 'v');
+create table neg_orc_table14(i int, j float) format 'orc' (rlecoder v);
+
+create table neg_orc_table15(i int, j float) format orc;
+create table neg_orc_table16(i int, j float) format 'orc' encoding utf8;
+
+-- test like
+create table neg_orc_table17(i int, j float) format 'orc';
+create table neg_orc_table18(i text, like neg_orc_table17) format 'orc';
+
+-- test distributed
+create table neg_orc_table19(i int, j float) format 'orc' distributed by i;
+
+create table neg_orc_table20(i int, j float) format 'orc' (escape #);
+
+
+-- writable external orc table
+--external table can not be web
+create writable external web table neg_orc_table1_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table1_e') format 'orc';
+create writable external table neg_orc_table2_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table2_e');
+create writable external table neg_orc_table3_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table3_e') format 'parquet';
+create writable external table neg_orc_table4_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table4_e') format 'orc' (compresstype 'zlib') encoding 'utf8';
+create writable external table neg_orc_table5_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table5_e') format 'orc' (rlecoder 'v') encoding 'utf8';
+create writable external table neg_orc_table6_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table6_e') format 'orc' (rlecoder 'v1') encoding 'utf8';
+create writable external table neg_orc_table7_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table7_e') format 'orc' (rlecoder 'v3') encoding 'utf8';
+create writable external table neg_orc_table8_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table8_e') format 'orc' encoding 'us-ascii';
+create writable external table neg_orc_table9_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table9_e') format 'orc' encoding 'gbk';
+create writable external table neg_orc_table10_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table10_e') format 'orc' (compresstype 'lz4' compresstype 'lz4') encoding 'utf8';
+create writable external table neg_orc_table11_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table11_e') format 'orc' (compresstype 'lz4' escape '#') encoding 'utf8';
+create writable external table neg_orc_table12_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table12_e') format 'orc' (compresstype 'lz4' delimiter ',') encoding 'utf8';
+create writable external table neg_orc_table13_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table13_e') format 'orc' (compresstype 'lz4' escape '#') encoding 'utf8';
+create writable external table neg_orc_table14_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table14_e') format 'magma';
+
+-- readable external orc table
+create readable external table neg_orc_table15_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn01_e');
+create readable external table neg_orc_table16_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn02_e') format 'parquet';
+create readable external table neg_orc_table17_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn03_e') format 'orc' encoding 'gbk';
+create readable external table neg_orc_table18_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn04_e') format 'orc' (compresstype 'none') encoding 'utf8';
+create readable external table neg_orc_table19_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn05_e') format 'orc' (compresstype 'lz4') encoding 'utf8';
+create readable external table neg_orc_table20_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn06_e') format 'orc' (compresstype 'zlib') encoding 'utf8';
+create readable external table neg_orc_table21_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn07_e') format 'orc' (rlecoder 'v') encoding 'utf8';
+create readable external table neg_orc_table22_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn08_e') format 'orc' (rlecoder 'v1') encoding 'utf8';
+create readable external table neg_orc_table23_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/rn09_e') format 'orc' (rlecoder 'v3') encoding 'utf8';
+create readable external table neg_orc_table24_e (i int) location ('magma://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table24_e') format 'orc';
+create readable external web table neg_orc_table25_e(i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table25_e') format 'orc';
+create readable external table neg_orc_table26_e(i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table26_e') format 'orc' (compresstype lz4);
+create readable external table neg_orc_table27_e(i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table27_e') format 'orc' (compresstype snappy);
+create readable external table neg_orc_table28_e(i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test_negativepath/neg_orc_table28_e') format 'orc' (compresstype none);
+
+-- test drop
+create table neg_orc_testdrop(i int) format 'orc';
+create readable external table neg_orc_testdrop_e (i int) location ('hdfs://@@host@@@@path@@exttable_extorc_test/normalpath/neg_orc_testdrop_e') format 'orc';
+
+-- test can not insert for readable
+insert into neg_orc_testdrop_e values (1);
+
+drop external table neg_orc_testdrop;
+drop table neg_orc_testdrop_e;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_normalpath.sql b/src/test/feature/ExternalSource/sql/exttable_extorc_normalpath.sql
new file mode 100644
index 0000000..8c5468c
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_normalpath.sql
@@ -0,0 +1,49 @@
+-- inter table
+-- start_ignore
+drop table if exists normal_orctable1, normal_orctable2, normal_orctable3, normal_orctable4, normal_orctable5;
+drop external table if exists normal_orctable1_e, normal_orctable2_e, normal_orctable3_e, normal_orctable4_e, normal_orctable5_e, normal_orctable6_e, normal_orctable7_e, normal_orctable8_e;
+-- end_ignore
+-- create normal table
+create table normal_orctable1 (i int, j float) format 'orc';
+create table normal_orctable2 (i int, j float) format 'orc' encoding 'utf8';
+create table normal_orctable3 (i int, j float) format 'orc' (compresstype 'snappy') encoding 'utf8';
+create table normal_orctable4 (i int, j float) format 'orc' (compresstype 'none') encoding 'utf8';
+create table normal_orctable5 (i int, j float) format 'orc' (compresstype 'lz4') encoding 'utf8';
+
+create table normal_orctable6 (k text, like normal_orctable1) format 'orc';
+create table normal_orctable7 (i int, j float) format 'orc' distributed by (i);
+
+
+-- writable external orc table
+create writable external table normal_orctable1_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable1_e') format 'orc';
+create writable external table normal_orctable2_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable2_e') format 'orc' encoding 'utf8';
+create writable external table normal_orctable3_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable3_e') format 'orc' () encoding 'utf8';
+create writable external table normal_orctable4_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable4_e') format 'orc' (compresstype 'snappy') encoding 'utf8';
+create writable external table normal_orctable5_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable5_e') format 'orc' (compresstype 'none') encoding 'utf8';
+create writable external table normal_orctable6_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable6_e') format 'orc' (compresstype 'lz4') encoding 'utf8';
+
+
+-- readable external orc table
+create readable external table normal_orctable7_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable7_e') format 'orc';
+create readable external table normal_orctable8_e (i int, j float) location ('hdfs://localhost:8020/hawq_default/exttable_extorc_test_normalpath/normal_orctable8_e') format 'orc' encoding 'utf8';
+
+
+-- test insert
+insert into normal_orctable1 values (1,0.1);
+insert into normal_orctable1 values (2,0.1);
+insert into normal_orctable1 values (3,0.1);
+select * from normal_orctable1;
+
+insert into normal_orctable1_e values (1,0.1);
+insert into normal_orctable1_e values (2,0.1);
+insert into normal_orctable1_e values (3,0.1);
+select * from normal_orctable1_e;
+
+select c.relname, e.fmttype, e.fmtopts, e.command, e.rejectlimit, e.rejectlimittype, e.fmterrtbl, e.encoding, e.writable
+from pg_class c, pg_exttable e
+where c.oid=e.reloid and c.relname in ('normal_orctable1_e', 'normal_orctable2_e', 'normal_orctable3_e', 'normal_orctable4_e', 'normal_orctable5_e', 'normal_orctable6_e', 'normal_orctable7_e', 'normal_orctable8_e')
+order by c.relname;
+
+
+
+-- test drop
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_normalpath.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_normalpath.sql.source
new file mode 100644
index 0000000..97e7269
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_normalpath.sql.source
@@ -0,0 +1,49 @@
+-- inter table
+-- start_ignore
+drop table if exists normal_orctable1, normal_orctable2, normal_orctable3, normal_orctable4, normal_orctable5;
+drop external table if exists normal_orctable1_e, normal_orctable2_e, normal_orctable3_e, normal_orctable4_e, normal_orctable5_e, normal_orctable6_e, normal_orctable7_e, normal_orctable8_e;
+-- end_ignore
+-- create normal table
+create table normal_orctable1 (i int, j float) format 'orc';
+create table normal_orctable2 (i int, j float) format 'orc' encoding 'utf8';
+create table normal_orctable3 (i int, j float) format 'orc' (compresstype 'snappy') encoding 'utf8';
+create table normal_orctable4 (i int, j float) format 'orc' (compresstype 'none') encoding 'utf8';
+create table normal_orctable5 (i int, j float) format 'orc' (compresstype 'lz4') encoding 'utf8';
+
+create table normal_orctable6 (k text, like normal_orctable1) format 'orc';
+create table normal_orctable7 (i int, j float) format 'orc' distributed by (i);
+
+
+-- writable external orc table
+create writable external table normal_orctable1_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable1_e') format 'orc';
+create writable external table normal_orctable2_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable2_e') format 'orc' encoding 'utf8';
+create writable external table normal_orctable3_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable3_e') format 'orc' () encoding 'utf8';
+create writable external table normal_orctable4_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable4_e') format 'orc' (compresstype 'snappy') encoding 'utf8';
+create writable external table normal_orctable5_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable5_e') format 'orc' (compresstype 'none') encoding 'utf8';
+create writable external table normal_orctable6_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable6_e') format 'orc' (compresstype 'lz4') encoding 'utf8';
+
+
+-- readable external orc table
+create readable external table normal_orctable7_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable7_e') format 'orc';
+create readable external table normal_orctable8_e (i int, j float) location ('hdfs://@@host@@@@path@@exttable_extorc_test_normalpath/normal_orctable8_e') format 'orc' encoding 'utf8';
+
+
+-- test insert
+insert into normal_orctable1 values (1,0.1);
+insert into normal_orctable1 values (2,0.1);
+insert into normal_orctable1 values (3,0.1);
+select * from normal_orctable1;
+
+insert into normal_orctable1_e values (1,0.1);
+insert into normal_orctable1_e values (2,0.1);
+insert into normal_orctable1_e values (3,0.1);
+select * from normal_orctable1_e;
+
+select c.relname, e.fmttype, e.fmtopts, e.command, e.rejectlimit, e.rejectlimittype, e.fmterrtbl, e.encoding, e.writable
+from pg_class c, pg_exttable e
+where c.oid=e.reloid and c.relname in ('normal_orctable1_e', 'normal_orctable2_e', 'normal_orctable3_e', 'normal_orctable4_e', 'normal_orctable5_e', 'normal_orctable6_e', 'normal_orctable7_e', 'normal_orctable8_e')
+order by c.relname;
+
+
+
+-- test drop
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_same_name_test.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_same_name_test.sql.source
new file mode 100644
index 0000000..c6a094f
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_same_name_test.sql.source
@@ -0,0 +1,33 @@
+-- start_ignore
+drop external table if exists test_same_name_orctable1_e, test_same_name_orctable2_e, test_same_name_orctable3_e, test_same_name_orctable4_e;
+drop table if exists test_same_orctable1_e;
+-- end_ignore
+
+-- test different external table using same name but have different columns
+create writable external table test_same_name_orctable1_e (i int, j float, k text) location ('hdfs://@@host@@@@path@@exttable_extorc_test/same_name_test/test_same_name_orctable1_e') format 'orc';
+insert into test_same_name_orctable1_e values(1,0.1,'aaa');
+
+create writable external table test_same_name_orctable2_e(i int, j float) location('hdfs://@@host@@@@path@@exttable_extorc_test/same_name_test/test_same_name_orctable1_e') format 'orc';
+
+insert into test_same_name_orctable2_e values(2,0.2);
+
+-- test different external table using same name and also same columns
+create writable external table test_same_name_orctable3_e(i int, j float) location('hdfs://@@host@@@@path@@exttable_extorc_test/same_name_test/test_same_name_orctable3_e') format 'orc';
+
+insert into test_same_name_orctable3_e values(3,0.3);
+
+create writable external table test_same_name_orctable4_e(i int, j float) location('hdfs://@@host@@@@path@@exttable_extorc_test/same_name_test/test_same_name_orctable3_e') format 'orc';
+
+insert into test_same_name_orctable4_e values(4,0.4);
+
+select * from test_same_name_orctable1_e;
+select * from test_same_name_orctable2_e;
+select * from test_same_name_orctable3_e;
+select * from test_same_name_orctable4_e;
+
+-- test the internal and external table using the same name
+create table test_same_name_orctable3_e(i int, j float) format 'orc';
+
+insert into test_same_name_orctable3_e values(5,0.5);
+
+select * from test_same_name_orctable3_e;
diff --git a/src/test/feature/ExternalSource/sql/exttable_extorc_testtruncate.sql.source b/src/test/feature/ExternalSource/sql/exttable_extorc_testtruncate.sql.source
new file mode 100644
index 0000000..62d35b3
--- /dev/null
+++ b/src/test/feature/ExternalSource/sql/exttable_extorc_testtruncate.sql.source
@@ -0,0 +1,5 @@
+create external table exttable_extorc_testtruncate (a int) location('hdfs://@@host@@@@path@@exttable_extorc_testtruncate') format'orc';
+insert into exttable_extorc_testtruncate select generate_series(1,1000);
+select count(*) from exttable_extorc_testtruncate;
+truncate table exttable_extorc_testtruncate;
+select count(*) from exttable_extorc_testtruncate;
diff --git a/src/test/feature/ExternalSource/test_extfmt_orc.cpp b/src/test/feature/ExternalSource/test_extfmt_orc.cpp
new file mode 100644
index 0000000..0805ab6
--- /dev/null
+++ b/src/test/feature/ExternalSource/test_extfmt_orc.cpp
@@ -0,0 +1,1253 @@
+#include "gtest/gtest.h"
+
+#include "lib/file_replace.h"
+#include "lib/hdfs_config.h"
+#include "lib/sql_util.h"
+#include "lib/string_util.h"
+
+using hawq::test::FileReplace;
+using hawq::test::HdfsConfig;
+using hawq::test::SQLUtility;
+
+class TestExtOrc : public ::testing::Test {
+ public:
+ const std::string initFile = "ExternalSource/sql/init_file";
+ TestExtOrc() {}
+ ~TestExtOrc() {}
+
+ public:
+ bool checkHDFSCommand() {
+ if (system("which hdfs > /dev/null 2>&1")) return false;
+ return true;
+ }
+
+ std::string generateUrl(SQLUtility &util, const std::string &dir) {
+ if (!checkHDFSCommand()) return "";
+ HdfsConfig hc;
+ std::string hdfsPrefix;
+ hc.getNamenodeHost(hdfsPrefix);
+ std::string url =
+ "hdfs://" + hdfsPrefix + TestExtOrc::getHAWQDefaultPath(util) + dir;
+ std::string cmd = "hdfs dfs -rm -R " + url;
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ std::string result;
+ hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ return url;
+ }
+
+ std::string getHAWQDefaultPath(SQLUtility &util) {
+ std::string url = util.getGUCValue("hawq_dfs_url");
+ std::size_t found = url.find("/");
+ std::string result = url.substr(found);
+ if (result[result.size() - 1] != '/') {
+ result += "/";
+ }
+ return result;
+ }
+};
+
+TEST_F(TestExtOrc, TestNormalPath) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::cout << basepath << "\n";
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test_normalpath";
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ if (status) std::cout << "delete the hdfs";
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_normalpath.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_normalpath.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_normalpath.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_normalpath.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_normalpath.sql",
+ "ExternalSource/ans/exttable_extorc_normalpath.ans");
+
+ std::string dbname = util.getDbName();
+ std::string schemaname = util.getSchemaName();
+ std::string testsql;
+ std::string tablespacename;
+ testsql =
+ "select pg_tablespace.spcname from pg_tablespace,pg_database "
+ "where pg_tablespace.oid=pg_database.dat2tablespace and "
+ "pg_database.datname='" +
+ dbname + "';";
+ tablespacename = util.getQueryResult(testsql);
+ cmd = "hdfs dfs -ls " + basepath + tablespacename + "/" + dbname + "/" +
+ schemaname + "/normal_orctable1";
+ hawq::test::toLower(cmd);
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ EXPECT_TRUE(status) << "Internal table can not find path \n";
+
+ util.execute("drop table normal_orctable1;");
+
+ cmd = "hdfs dfs -ls " + basepath + tablespacename + "/" + dbname + "/" +
+ schemaname + "/normal_orctable1";
+ hawq::test::toLower(cmd);
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ EXPECT_FALSE(status) << "Internal table still exists after dropped \n";
+
+ std::cout << hdfs_prefix << "\n";
+ std::cout << basepath << "\n";
+ cmd = "hdfs dfs -ls hdfs://" + hdfs_prefix + basepath +
+ "exttable_extorc_test_normalpath/normal_orctable1_e";
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ EXPECT_TRUE(status) << "External table can not find path \n";
+
+ util.execute("drop external table normal_orctable1_e;");
+
+ cmd = "hdfs dfs -ls hdfs://" + hdfs_prefix + basepath +
+ "exttable_extorc_test_normalpath/normal_orctable1_e";
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ EXPECT_TRUE(status) << "External table still exists after dropped \n";
+
+ cmd = "hdfs dfs -rm -R " + basepath + "exttable_extorc_test_normalpath";
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+}
+
+TEST_F(TestExtOrc, TestNegativePath) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test_negativepath";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::cout << hdfs_prefix << basepath << "\n";
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_negativepath.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_negativepath.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_negativepath.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_negativepath.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_negativepath.sql",
+ "ExternalSource/ans/exttable_extorc_negativepath.ans",
+ initFile);
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestSameName) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/same_name_test";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::cout << hdfs_prefix << basepath << "\n";
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_same_name_test.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_same_name_test.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_same_name_test.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_same_name_test.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_same_name_test.sql",
+ "ExternalSource/ans/exttable_extorc_same_name_test.ans",
+ initFile);
+
+ // cleanup in hdfs
+ cmd = "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/same_name_test";
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestEncoding) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/encoding";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_encoding.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_encoding.sql", test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_encoding.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_encoding.ans", test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // prepare in database
+ util.executeIgnore("drop table if exists te;");
+ util.executeIgnore("drop external table if exists twe_e, tre_e;");
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_encoding.sql",
+ "ExternalSource/ans/exttable_extorc_encoding.ans");
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestDatatypeInteger) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/datatype_integer";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_integer.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_integer.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_integer.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_integer.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // prepare in database
+ util.executeIgnore("drop table if exists ti;");
+ util.executeIgnore("drop external table if exists twi_e, tri_e;");
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_datatype_integer.sql",
+ "ExternalSource/ans/exttable_extorc_datatype_integer.ans");
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestDatatypeBool) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/datatype_bool";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_bool.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_bool.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_bool.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_bool.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // prepare in database
+ util.executeIgnore("drop table if exists tb;");
+ util.executeIgnore("drop external table if exists twb_e, trb_e;");
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_datatype_bool.sql",
+ "ExternalSource/ans/exttable_extorc_datatype_bool.ans",
+ initFile);
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestDatatypeDouble) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/datatype_double";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_double.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_double.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_double.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_double.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // prepare in database
+ util.executeIgnore("drop table if exists td;");
+ util.executeIgnore("drop external table if exists twd_e, trd_e;");
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_datatype_double.sql",
+ "ExternalSource/ans/exttable_extorc_datatype_double.ans");
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestDatatypeDecimal) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/datatype_decimal";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_decimal.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_decimal.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_decimal.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_decimal.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // prepare in database
+ util.executeIgnore("drop table if exists td, tn;");
+ util.executeIgnore(
+ "drop external table if exists twd_e, trd_e, twn_e, trn_e;");
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_datatype_decimal.sql",
+ "ExternalSource/ans/exttable_extorc_datatype_decimal.ans");
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestDatatypeVarchar) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/datatype_varchar";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_varchar.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_varchar.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_varchar.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_varchar.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // prepare in database
+ util.executeIgnore("drop table if exists tv;");
+ util.executeIgnore("drop external table if exists twv_e, trv_e;");
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_datatype_varchar.sql",
+ "ExternalSource/ans/exttable_extorc_datatype_varchar.ans");
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestDatatypeChar) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/datatype_char";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_char.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_char.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_char.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_char.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // prepare in database
+ util.executeIgnore("drop table if exists tc, tcn;");
+ util.executeIgnore(
+ "drop external table if exists twc_e, trc_e, twcn_e, trcn_e;");
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_datatype_char.sql",
+ "ExternalSource/ans/exttable_extorc_datatype_char.ans");
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestDatatypeDateTime) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/datatype_datetime";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_datetime.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_datetime.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_datetime.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_datetime.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // prepare in database
+ util.executeIgnore("drop table if exists td;");
+ util.executeIgnore("drop external table if exists twd_e, trd_e;");
+
+ util.executeIgnore("drop table if exists tt, ttntz, tttz;");
+ util.executeIgnore(
+ "drop external table if exists twt_e, trt_e, twtntz_e, trtntz_e, "
+ "twttz_e, trttz_e;");
+
+ util.executeIgnore("drop table if exists tts, ttsntz, ttstz;");
+ util.executeIgnore(
+ "drop external table if exists twts_e, trts_e, twtsntz_e, trtsntz_e, "
+ "twtstz_e, trtstz_e;");
+
+ util.executeIgnore("drop table if exists tit;");
+ util.executeIgnore("drop external table if exists twit_e, trit_e;");
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_datatype_datetime.sql",
+ "ExternalSource/ans/exttable_extorc_datatype_datetime.ans");
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestDatatypeMoney) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ bool status;
+
+ // Prepare hdfs
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_test/datatype_money";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+
+ // prepare testing sql and ans files
+ std::string hdfs_prefix;
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_money.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_datatype_money.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_money.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_datatype_money.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+
+ // prepare in database
+ util.executeIgnore("drop table if exists tm;");
+ util.executeIgnore("drop external table if exists twm_e, trm_e;");
+
+ // run test
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_datatype_money.sql",
+ "ExternalSource/ans/exttable_extorc_datatype_money.ans");
+
+ // cleanup in hdfs
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ ASSERT_TRUE(status);
+}
+
+TEST_F(TestExtOrc, TestTruncate) {
+ SQLUtility util;
+ HdfsConfig hc;
+ std::string result;
+ std::string sql, sqlout;
+ std::string hdfs_prefix;
+ bool status;
+ std::string basepath = util.getHdfsPath();
+ std::string cmd =
+ "hdfs dfs -rm -R " + basepath + "exttable_extorc_testtruncate";
+ std::cout << "prepare hdfs : " << cmd << std::endl;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ hc.getNamenodeHost(hdfs_prefix);
+ FileReplace frep;
+ std::string test_root(util.getTestRootPath());
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@@host@@"] = hdfs_prefix;
+ strs_src_dst["@@path@@"] = basepath;
+ std::string sql_src(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_testtruncate.sql.source",
+ test_root.c_str()));
+ std::string sql_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/sql/exttable_extorc_testtruncate.sql",
+ test_root.c_str()));
+ frep.replace(sql_src, sql_dst, strs_src_dst);
+
+ std::string ans_src(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_testtruncate.ans.source",
+ test_root.c_str()));
+ std::string ans_dst(hawq::test::stringFormat(
+ "%s/ExternalSource/ans/exttable_extorc_testtruncate.ans",
+ test_root.c_str()));
+ frep.replace(ans_src, ans_dst, strs_src_dst);
+ util.execSQLFile("ExternalSource/sql/exttable_extorc_testtruncate.sql",
+ "ExternalSource/ans/exttable_extorc_testtruncate.ans");
+ cmd = "hdfs dfs -ls " + basepath + "exttable_extorc_testtruncate";
+ std::cout << cmd << std::endl;
+ int num = 0;
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ size_t pos = 0;
+ while ((pos = result.find(basepath, pos)) != std::string::npos) {
+ ++num;
+ ++pos;
+ };
+ EXPECT_EQ(1, num);
+ sql = "insert into exttable_extorc_testtruncate values(1);";
+ sqlout = util.execute(sql, true);
+ sql = "drop external table exttable_extorc_testtruncate;";
+ sqlout = util.execute(sql, true);
+ status = hc.runCommand(cmd, hc.getHdfsUser(), result, HDFS_COMMAND);
+ pos = 0;
+ num = 0;
+ while ((pos = result.find(basepath, pos)) != std::string::npos) {
+ ++num;
+ ++pos;
+ };
+ EXPECT_EQ(2, num);
+}
+
+TEST_F(TestExtOrc, BoolTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_BoolTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t10");
+ util.execute(
+ "create writable external table t10 (p bool) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t10 values('yes'),('n'),(null),('0'),(true)");
+ util.query("set new_executor=off; select * from t10 where p='f' or p is true",
+ "t|\nf|\nf|\nt|\n");
+ util.query("set new_executor=off; select * from t10 where not p is unknown",
+ "t|\nf|\nf|\nt|\n");
+ util.query("set new_executor=off; select * from t10 where 't' > p",
+ "f|\nf|\n");
+ util.query("set new_executor=off; select * from t10 where p != 't'",
+ "f|\nf|\n");
+}
+
+TEST_F(TestExtOrc, DateTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_DateTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t11");
+ util.execute(
+ "create writable external table t11 (p date) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute(
+ "insert into t11 values('1999-01-08'),('January 8, "
+ "1999'),('01/02/"
+ "03'),('1999-Jan-08'),('Jan-08-1999'),('08-Jan-1999'),('"
+ "19990108'),('990108'),('1999.008'),('J2451187'),('0099-01-08 BC')");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, MDY'; select * from t11 "
+ "where p != '2001-1-1'",
+ "1999-01-08|\n1999-01-08|\n2003-01-02|\n1999-01-08|"
+ "\n1999-01-08|\n1999-01-08|\n1999-01-08|\n1999-01-08|\n1999-01-08|"
+ "\n1999-01-08|\n0099-01-08 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, MDY'; select * from t11 "
+ "where p < '2000-1-1'",
+ "1999-01-08|\n1999-01-08|\n1999-01-08|"
+ "\n1999-01-08|\n1999-01-08|\n1999-01-08|\n1999-01-08|\n1999-01-08|"
+ "\n1999-01-08|\n0099-01-08 BC|\n");
+ util.query(
+ "set new_executor=off; select p - '2002-01-02' from t11 where p > "
+ "'2000-1-1'",
+ "365|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, MDY'; select * from t11 "
+ "where p = '0099-1-8 BC'",
+ "0099-01-08 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, DMY'; select * from t11 "
+ "where p = '0099-1-8 BC'",
+ "0099-01-08 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, YMD'; select * from t11 "
+ "where p = '0099-1-8 BC'",
+ "0099-01-08 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'Postgres, MDY'; select * from "
+ "t11 where p = '0099-1-8 BC'",
+ "01-08-0099 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'Postgres, DMY'; select * from "
+ "t11 where p = '0099-1-8 BC'",
+ "08-01-0099 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'Postgres, YMD'; select * from "
+ "t11 where p = '0099-1-8 BC'",
+ "01-08-0099 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'SQL, MDY'; select * from t11 "
+ "where p = '0099-1-8 BC'",
+ "01/08/0099 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'SQL, DMY'; select * from t11 "
+ "where p = '0099-1-8 BC'",
+ "08/01/0099 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'SQL, YMD'; select * from t11 "
+ "where p = '0099-1-8 BC'",
+ "01/08/0099 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'German, MDY'; select * from t11 "
+ "where p = '0099-1-8 BC'",
+ "08.01.0099 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'German, DMY'; select * from t11 "
+ "where p = '0099-1-8 BC'",
+ "08.01.0099 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'German, YMD'; select * from t11 "
+ "where p = '0099-1-8 BC'",
+ "08.01.0099 BC|\n");
+}
+
+TEST_F(TestExtOrc, TimeTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_TimeTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t12");
+ util.execute(
+ "create writable external table t12 (p time(2)) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute(
+ "insert into t12 "
+ "values('04:05:06.789'),('04:05:06'),('04:05'),('040506'),('04:05 "
+ "AM'),('04:05 "
+ "PM'),('04:05:06.789-8'),('04:05:06-08:00'),('04:05-08:00'),('040506-08')"
+ ",('04:05:06 PST'),('2003-04-12 04:05:06 America/New_York')");
+ util.query(
+ "set new_executor=off; select * from t12 where p = '04:05:06'",
+ "04:05:06|\n04:05:06|\n04:05:06|\n04:05:06|\n04:05:06|\n04:05:06|\n");
+ util.query("set new_executor=off; select * from t12 where p = '04:05:06.79'",
+ "04:05:06.79|\n04:05:06.79|\n");
+ util.query("set new_executor=off; select * from t12 where p > '04:05:06'",
+ "04:05:06.79|\n16:05:00|\n04:05:06.79|\n");
+ util.query("set new_executor=off; select * from t12 where p < '04:05:06'",
+ "04:05:00|\n04:05:00|\n04:05:00|\n");
+}
+
+TEST_F(TestExtOrc, TimestampTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_TimestampTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t13");
+ util.execute(
+ "create writable external table t13 (p timestamp) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute(
+ "insert into t13 values('1999-01-08 04:05:06'), ('January 8 04:05:06 "
+ "1999 PST'), ('1999-01-08 04:05:06.01 BC')");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, YMD'; select * from t13 "
+ "where p > '1999-01-08 04:05:06 BC'",
+ "1999-01-08 04:05:06|\n1999-01-08 04:05:06|\n1999-01-08 04:05:06.01 "
+ "BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, YMD'; select * from t13 "
+ "where p < '1999-01-08 04:05:06'",
+ "1999-01-08 04:05:06.01 BC|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, MDY'; select * from t13 "
+ "where p = '1999-01-08 04:05:06'",
+ "1999-01-08 04:05:06|\n1999-01-08 04:05:06|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, DMY'; select * from t13 "
+ "where p = '1999-01-08 04:05:06'",
+ "1999-01-08 04:05:06|\n1999-01-08 04:05:06|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'ISO, YMD'; select * from t13 "
+ "where p = '1999-01-08 04:05:06'",
+ "1999-01-08 04:05:06|\n1999-01-08 04:05:06|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'Postgres, MDY'; select * from "
+ "t13 where p = '1999-01-08 04:05:06'",
+ "Fri Jan 08 04:05:06 1999|\nFri Jan 08 04:05:06 1999|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'Postgres, DMY'; select * from "
+ "t13 where p = '1999-01-08 04:05:06'",
+ "Fri 08 Jan 04:05:06 1999|\nFri 08 Jan 04:05:06 1999|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'Postgres, YMD'; select * from "
+ "t13 where p = '1999-01-08 04:05:06'",
+ "Fri Jan 08 04:05:06 1999|\nFri Jan 08 04:05:06 1999|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'SQL, MDY'; select * from t13 "
+ "where p = '1999-01-08 04:05:06'",
+ "01/08/1999 04:05:06|\n01/08/1999 04:05:06|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'SQL, DMY'; select * from t13 "
+ "where p = '1999-01-08 04:05:06'",
+ "08/01/1999 04:05:06|\n08/01/1999 04:05:06|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'SQL, YMD'; select * from t13 "
+ "where p = '1999-01-08 04:05:06'",
+ "01/08/1999 04:05:06|\n01/08/1999 04:05:06|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'German, MDY'; select * from t13 "
+ "where p = '1999-01-08 04:05:06'",
+ "08.01.1999 04:05:06|\n08.01.1999 04:05:06|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'German, DMY'; select * from t13 "
+ "where p = '1999-01-08 04:05:06'",
+ "08.01.1999 04:05:06|\n08.01.1999 04:05:06|\n");
+ util.query(
+ "set new_executor=off; set datestyle to 'German, YMD'; select * from t13 "
+ "where p = '1999-01-08 04:05:06'",
+ "08.01.1999 04:05:06|\n08.01.1999 04:05:06|\n");
+}
+
+TEST_F(TestExtOrc, BinaryTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_BinaryTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t14");
+ util.execute(
+ "create writable external table t14 (arr bytea) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t14 values('haha'), ('233'), ('666'), ('ok')");
+ util.query("set new_executor=off; select * from t14",
+ "haha|\n233|\n666|\nok|\n");
+ util.query("set new_executor=off; select octet_length(arr) from t14",
+ "4|\n3|\n3|\n2|\n");
+ util.query("set new_executor=off; select * from t14 where arr > '233'",
+ "haha|\n666|\nok|\n");
+ util.query("set new_executor=off; select * from t14 where arr = '233'",
+ "233|\n");
+ util.query("set new_executor=off; select * from t14 where arr < '666'",
+ "233|\n");
+}
+
+TEST_F(TestExtOrc, StringTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_StringTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t15");
+ util.execute(
+ "create writable external table "
+ "t15(b \"char\", c char, charn char(5), varchar varchar, varcharn "
+ "varchar(10), text text) "
+ "LOCATION (\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t15 values('0', 'a ', 'b ', 'c', 'd', 'e');");
+ util.query("set new_executor=off; select * from t15", "0|a|b |c|d|e|\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_SmallIntArrayTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_SmallIntArrayTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t16");
+ util.execute(
+ "create writable external table t16 (p int2[10]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t16 values('{1,2,3}'), ('{NULL, 2}'), (NULL);");
+ util.query("set new_executor=off; select * from t16",
+ "{1,2,3}|\n{NULL,2}|\n|\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_IntArrayTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_IntArrayTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t17");
+ util.execute(
+ "create writable external table t17 (p int4[10]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t17 values('{1,2,3}'), ('{NULL, 2}'), (NULL);");
+ util.query("set new_executor=off; select * from t17",
+ "{1,2,3}|\n{NULL,2}|\n|\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_BigIntArrayTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_BigIntArrayTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t18");
+ util.execute(
+ "create writable external table t18 (p int8[10]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t18 values('{1,2,3}'), ('{NULL, 2}'), (NULL);");
+ util.query("set new_executor=off; select * from t18",
+ "{1,2,3}|\n{NULL,2}|\n|\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_FloatArrayTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_FloatArrayTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t19");
+ util.execute(
+ "create writable external table t19 (p float4[10]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t19 values('{1,2,3}'), ('{NULL, 2}'), (NULL);");
+ util.query("set new_executor=off; select * from t19",
+ "{1,2,3}|\n{NULL,2}|\n|\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_DoubleArrayTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_DoubleArrayTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t20");
+ util.execute(
+ "create writable external table t20 (p float8[10]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t20 values('{1,2,3}'), ('{NULL, 2}'), (NULL);");
+ util.query("set new_executor=off; select * from t20",
+ "{1,2,3}|\n{NULL,2}|\n|\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_SmallIntArrayTypeWithSortedAnsTest) {
+ SQLUtility util;
+ std::string url =
+ generateUrl(util, "TestExtOrc_SmallIntArrayWithSortedAnsTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t22");
+ util.execute(
+ "create writable external table t22 (i int, p int2[]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t22 values(1), (2);");
+ util.execute(
+ "insert into t22 values(11, '{1,2,NULL,4,5,NULL}'),(NULL, NULL),(31, "
+ "'{1,2,3}');");
+ util.execute(
+ "insert into t22 values(12, NULL),(13, "
+ "'{NULL,1,2,3}');");
+ util.query("set new_executor=off; select * from t22 order by i",
+ "1||\n2||\n11|{1,2,NULL,4,5,NULL}|\n12||\n13|{NULL,1,2,3}|\n31|{1,"
+ "2,3}|\n||\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_IntArrayTypeWithSortedAnsTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_IntArrayWithSortedAnsTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t23");
+ util.execute(
+ "create writable external table t23 (i int, p int4[]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t23 values(1), (2);");
+ util.execute(
+ "insert into t23 values(11, '{1,2,NULL,4,5,NULL}'),(NULL, NULL),(31, "
+ "'{1,2,3}');");
+ util.execute(
+ "insert into t23 values(12, NULL),(13, "
+ "'{NULL,1,2,3}');");
+ util.query("set new_executor=off; select * from t23 order by i",
+ "1||\n2||\n11|{1,2,NULL,4,5,NULL}|\n12||\n13|{NULL,1,2,3}|\n31|{1,"
+ "2,3}|\n||\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_BigIntArrayTypeWithSortedAnsTest) {
+ SQLUtility util;
+ std::string url =
+ generateUrl(util, "TestExtOrc_BigIntArrayWithSortedAnsTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t24");
+ util.execute(
+ "create writable external table t24 (i int, p int8[]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t24 values(1), (2);");
+ util.execute(
+ "insert into t24 values(11, '{1,2,NULL,4,5,NULL}'),(NULL, NULL),(31, "
+ "'{1,2,3}');");
+ util.execute(
+ "insert into t24 values(12, NULL),(13, "
+ "'{NULL,1,2,3}');");
+ util.query("set new_executor=off; select * from t24 order by i",
+ "1||\n2||\n11|{1,2,NULL,4,5,NULL}|\n12||\n13|{NULL,1,2,3}|\n31|{1,"
+ "2,3}|\n||\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_FloatArrayTypeWithSortedAnsTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_FloatArrayWithSortedAnsTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t25");
+ util.execute(
+ "create writable external table t25 (i int, p float4[]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t25 values(1), (2);");
+ util.execute(
+ "insert into t25 values(11, '{1,2,NULL,4,5,NULL}'),(NULL, NULL),(31, "
+ "'{1,2,3}');");
+ util.execute(
+ "insert into t25 values(12, NULL),(13, "
+ "'{NULL,1,2,3}');");
+ util.query("set new_executor=off; select * from t25 order by i",
+ "1||\n2||\n11|{1,2,NULL,4,5,NULL}|\n12||\n13|{NULL,1,2,3}|\n31|{1,"
+ "2,3}|\n||\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_DoubleArrayTypeWithSortedAnsTest) {
+ SQLUtility util;
+ std::string url =
+ generateUrl(util, "TestExtOrc_DoubleArrayWithSortedAnsTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t26");
+ util.execute(
+ "create writable external table t26 (i int, p float8[]) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute("insert into t26 values(1), (2);");
+ util.execute(
+ "insert into t26 values(11, '{1,2,NULL,4,5,NULL}'),(NULL, NULL),(31, "
+ "'{1,2,3}');");
+ util.execute(
+ "insert into t26 values(12, NULL),(13, "
+ "'{NULL,1,2,3}');");
+ util.query("set new_executor=off; select * from t26 order by i",
+ "1||\n2||\n11|{1,2,NULL,4,5,NULL}|\n12||\n13|{NULL,1,2,3}|\n31|{1,"
+ "2,3}|\n||\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_MixedTypeWithArrayTypeTest) {
+ SQLUtility util;
+ std::string url = generateUrl(util, "TestExtOrc_MixedTypeWithArrayTypeTest");
+ ASSERT_FALSE(url.empty());
+ util.execute("drop external table if exists t27");
+ util.execute(
+ "create writable external table t27 (p float8[], i int, s text, t "
+ "timestamp) LOCATION "
+ "(\'" +
+ url + "\') FORMAT 'orc'");
+ util.execute(
+ "insert into t27 values('{1,2,NULL,4,5,NULL}', 11, 'abc', '2018-3-20 "
+ "11:51:14.233'),(NULL, NULL, NULL, NULL),('{1,2,3}', 31, '', '2018-3-20 "
+ "11:51:14');");
+ util.execute(
+ "insert into t27 values(NULL, 12, 'f', NULL),('{NULL,1,2,3}', 13, NULL, "
+ "'2018-3-20 11:51:15'), ('{}', NULL, 'eee', '2018-3-20 11:51:14.233');");
+ util.query("set new_executor=off; select * from t27 order by i, s",
+ "{1,2,NULL,4,5,NULL}|11|abc|Tue Mar 20 11:51:14.233 "
+ "2018|\n|12|f||\n{NULL,1,2,3}|13||Tue Mar 20 11:51:15 "
+ "2018|\n{1,2,3}|31||Tue Mar 20 11:51:14 2018|\n{}||eee|Tue Mar 20 "
+ "11:51:14.233 2018|\n||||\n");
+ util.query(
+ "set new_executor=off; select * from t27 where s is not null "
+ "order by t,i ",
+ "{1,2,3}|31||Tue Mar 20 11:51:14 2018|\n{1,2,NULL,4,5,NULL}|11|abc|Tue "
+ "Mar 20 11:51:14.233 2018|\n{}||eee|Tue Mar 20 11:51:14.233 "
+ "2018|\n|12|f||\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_EuclideanMetricFloat8Array) {
+ SQLUtility util;
+ util.execute("drop table if exists t28");
+ util.execute("create table t28 (p float8[]) FORMAT 'orc'");
+ util.execute("insert into t28 values('{1,2,3}'), ('{1,2}');");
+ util.query(
+ "set new_executor=off; select euclidean_metric_float8array(p, "
+ "array[1,2,3]) from t28;",
+ "0|\n3|\n");
+ util.query(
+ "set new_executor=off; select euclidean_metric_float8array(p, "
+ "array[1,2]) from t28;",
+ "3|\n0|\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_EuclideanMetricFloat4Array) {
+ SQLUtility util;
+ util.execute("drop table if exists t29");
+ util.execute("create table t29 (p float4[]) FORMAT 'orc'");
+ util.execute("insert into t29 values('{1,2,3}'), ('{1,2}');");
+ util.query(
+ "set new_executor=off; select euclidean_metric_float4array(p, "
+ "array[1,2,3]) from t29;",
+ "0|\n3|\n");
+ util.query(
+ "set new_executor=off; select euclidean_metric_float4array(p, "
+ "array[1,2]) from t29;",
+ "3|\n0|\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_CosineDistanceFloat8Array) {
+ SQLUtility util;
+ util.execute("drop table if exists t30");
+ util.execute("create table t30 (p float8[]) FORMAT 'orc'");
+ util.execute("insert into t30 values('{1,2,0}'), ('{1,2}');");
+ util.query(
+ "set new_executor=off; select cosine_distance_float8array(p, "
+ "array[1,2,0]) from t30;",
+ "1|\n1|\n");
+ util.query(
+ "set new_executor=off; select cosine_distance_float8array(p, "
+ "array[1,2]) from t30;",
+ "1|\n1|\n");
+}
+
+TEST_F(TestExtOrc, DISABLED_CosineDistanceFloat4Array) {
+ SQLUtility util;
+ util.execute("drop table if exists t31");
+ util.execute("create table t31 (p float4[]) FORMAT 'orc'");
+ util.execute("insert into t31 values('{1,2,0}'), ('{1,2}');");
+ util.query(
+ "set new_executor=off; select cosine_distance_float4array(p, "
+ "array[1,2,0]) from t31;",
+ "1|\n1|\n");
+ util.query(
+ "set new_executor=off; select cosine_distance_float4array(p, "
+ "array[1,2]) from t31;",
+ "1|\n1|\n");
+}
diff --git a/src/test/feature/lib/compent_config.cpp b/src/test/feature/lib/compent_config.cpp
new file mode 100644
index 0000000..b018ff6
--- /dev/null
+++ b/src/test/feature/lib/compent_config.cpp
@@ -0,0 +1,327 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+#include <pwd.h>
+#include <string>
+#include <vector>
+
+#include "command.h"
+#include "compent_config.h"
+#include "string_util.h"
+#include "xml_parser.h"
+
+using std::string;
+
+namespace hawq {
+namespace test {
+
+CompentConfig::CompentConfig() {
+ iscloud = getenv("CLOUD_CLUSTER_ENV") ? true : false;
+ slavesPhysicalHosts.clear();
+ masterPhysicalHosts.clear();
+ activeMasterPos = true;
+ isdemo = false;
+ compentPath = "";
+ conn.reset(new hawq::test::PSQL(HAWQ_DB, HAWQ_HOST, HAWQ_PORT,
+ this->getHawqUser(), HAWQ_PASSWORD));
+}
+
+bool CompentConfig::__fetchKubeCluster() {
+ if (!iscloud)
+ return false;
+
+ string cmd, result;
+ bool status;
+ if (masterPhysicalHosts.size() == 0) {
+ cmd = "kubectl get nodes | grep -v NAME | wc -l ";
+ status = this->runCommand(KUBENET_MASTER, cmd, "", result, OS_COMMAND);
+ if (!status)
+ return false;
+ int nodenum = std::atoi(result.c_str());
+ if (nodenum > 1) {
+ isdemo = false;
+ cmd = "kubectl get nodes -a --show-labels=true | grep hadoopmaster | cut "
+ "-d ' ' -f 1";
+ status = this->runCommand(KUBENET_MASTER, cmd, "", result, OS_COMMAND);
+ auto masterlines = hawq::test::split(result, '\n');
+ for (size_t i = 0; i < masterlines.size(); i++) {
+ masterPhysicalHosts.push_back(hawq::test::trim(masterlines[i]));
+ }
+
+ slavesPhysicalHosts.clear();
+ cmd = "kubectl get nodes -a --show-labels=true | grep hadoopslave | cut "
+ "-d ' ' -f 1";
+ status =
+ this->runCommand(masterPhysicalHosts[0], cmd, "", result, OS_COMMAND);
+ auto slavelines = hawq::test::split(result, '\n');
+ for (size_t i = 0; i < slavelines.size(); i++) {
+ slavesPhysicalHosts.push_back(hawq::test::trim(slavelines[i]));
+ }
+ } else {
+ return false;
+ }
+ }
+ return true;
+}
+
+void CompentConfig::__copyCluster(std::vector<string> &masterhosts,
+ std::vector<string> &slavehost) {
+ masterhosts.clear();
+ for (uint16_t i = 0; i < masterPhysicalHosts.size(); i++)
+ masterhosts.push_back(masterPhysicalHosts[i]);
+
+ slavehost.clear();
+ for (uint16_t i = 0; i < slavesPhysicalHosts.size(); i++)
+ slavehost.push_back(slavesPhysicalHosts[i]);
+}
+
+string CompentConfig::getHawqUser() {
+ string user = HAWQ_USER;
+ if (user.empty()) {
+ struct passwd *pw;
+ uid_t uid = geteuid();
+ pw = getpwuid(uid);
+ user.assign(pw->pw_name);
+ }
+ return user;
+}
+
+bool CompentConfig::runCommand(const std::string hostname,
+ const std::string &commandstr,
+ const std::string user, std::string &result,
+ CommandType cmdType) {
+ string cmd = "ssh -o StrictHostKeyChecking=no ";
+ cmd.append(hostname);
+ CommandType refcmdType = cmdType;
+
+ if ((refcmdType == YARN_COMMAND || refcmdType == HAWQ_OS_COMMAND) && !iscloud)
+ refcmdType = OS_COMMAND;
+
+ switch (refcmdType) {
+ case OS_COMMAND: {
+ cmd.append(" \"");
+ if (user.size() > 0)
+ cmd.append("sudo -u ").append(user).append(" env PATH=$PATH ");
+ cmd.append(commandstr).append(" \"");
+ break;
+ }
+ case HAWQ_COMMAND: {
+ if (iscloud) {
+ std::cout << "HAWQ command is not supported in cluod platform\n";
+ EXPECT_TRUE(false);
+ return false;
+ }
+ cmd.append(" \"source ");
+ cmd.append(this->compentPath).append("/greenplum_path.sh;");
+ cmd.append(commandstr).append(" \"");
+ break;
+ }
+ case HAWQ_OS_COMMAND: {
+ EXPECT_TRUE(iscloud == iscloud);
+ cmd.append(" \"sudo docker exec `sudo docker ps | grep k8s_hawq-master | "
+ "cut -d ' ' -f 1` sh -c '");
+ cmd.append(commandstr).append("'\"");
+ break;
+ }
+ case HDFS_COMMAND: {
+ if (iscloud) {
+ cmd.append(" \"sudo docker exec `sudo docker ps | grep k8s_hdfs-nn | cut "
+ "-d ' ' -f 1` sh -c '");
+ cmd.append(commandstr).append("'\"");
+ } else {
+ cmd.append(" \"");
+ if (user.size() > 0)
+ cmd.append("sudo -u ").append(user).append(" env PATH=$PATH ");
+ cmd.append(commandstr).append(" \"");
+ }
+
+ break;
+ }
+ case YARN_COMMAND: {
+ std::cout << "HAWQ command is not supported in cluod platform\n";
+ EXPECT_TRUE(false);
+ return false;
+ }
+ }
+ Command c(cmd);
+ std::cout << cmd << "\n";
+ result = c.run().getResultOutput();
+ int status = c.getResultStatus();
+ return (status == 0);
+}
+
+bool CompentConfig::runCommand(const std::string &commandstr,
+ const std::string user, std::string &result,
+ CommandType cmdType) {
+ return this->runCommand(masterPhysicalHosts[MASTERPOS], commandstr, user,
+ result, cmdType);
+}
+bool CompentConfig::runCommandAndFind(const std::string &command,
+ const std::string user,
+ const std::string &findstring,
+ CommandType cmdType) {
+ string result = "";
+ bool status = this->runCommand(command, user, result, cmdType);
+ if (!status)
+ return false;
+ auto lines = hawq::test::split(result, '\n');
+ for (size_t i = 0; i < lines.size(); i++) {
+ string valueLine = lines[i];
+ int find = valueLine.find(findstring);
+ if (find >= 0) {
+ return true;
+ }
+ }
+ return false;
+}
+
+void CompentConfig::runCommandAndGetNodesPorts(
+ const std::string &command, const std::string user,
+ std::vector<string> &datanodelist, std::vector<int> &port,
+ CommandType cmdType) {
+ datanodelist.clear();
+ port.clear();
+ string result = "";
+ bool status = this->runCommand(command, user, result, cmdType);
+ if (status) {
+ auto lines = hawq::test::split(result, '\n');
+ for (size_t i = 0; i < lines.size(); i++) {
+ string valueLine = lines[i];
+ if (valueLine.find("WARNING") != string::npos)
+ continue;
+ auto datanodeInfo = hawq::test::split(valueLine, ':');
+ if (datanodeInfo.size() == 3) {
+ int portStart = datanodeInfo[2].find_first_of('(');
+ int portEnd = datanodeInfo[2].find_first_of(')');
+ string datanodePort = datanodeInfo[2].substr(0, portStart);
+ string datanodeHost =
+ datanodeInfo[2].substr(portStart + 1, portEnd - portStart - 1);
+ datanodelist.push_back(hawq::test::trim(datanodeHost));
+ port.push_back(std::stoi(hawq::test::trim(datanodePort)));
+ }
+ }
+ }
+}
+
+bool CompentConfig::checkRemainedProcess() {
+ string postcheckcmd = "ps -ef | grep postgres | grep con | grep -v idle | "
+ "grep -v gpsyncagent | grep -v grep | wc -l";
+ string postshowcmd = "ps -ef | grep postgres | grep con | grep -v idle | "
+ "grep -v gpsyncagent | grep -v grep";
+ string result;
+ bool status;
+ uint16_t postnum;
+
+ for (uint16_t i = 0; i < masterPhysicalHosts.size(); i++) {
+ uint16_t j;
+ for (j = 0; j < MAX_RETRY_NUM; j++) {
+ status = this->runCommand(masterPhysicalHosts[i], postcheckcmd, "",
+ result, OS_COMMAND);
+ if (status) {
+ postnum = atoi(result.c_str());
+ if (postnum == 0)
+ break;
+ std::cout << "Checking remained postgres process and there is "
+ << postnum << " remaind\n";
+ status = this->runCommand(masterPhysicalHosts[i], postshowcmd, "",
+ result, OS_COMMAND);
+ std::cout << "remained process is:\n" << result << std::endl;
+ }
+ sleep(5);
+ }
+ if (j == MAX_RETRY_NUM) {
+ status = this->runCommand(masterPhysicalHosts[i], postcheckcmd, "",
+ result, OS_COMMAND);
+ std::cout << "Checking remained postgres process and there is "
+ << result;
+ return false;
+ }
+ }
+
+ for (uint16_t i = 0; i < slavesPhysicalHosts.size(); i++) {
+ uint16_t j;
+ for (j = 0; j < MAX_RETRY_NUM; j++) {
+ status = this->runCommand(slavesPhysicalHosts[i], postcheckcmd, "",
+ result, OS_COMMAND);
+ if (status) {
+ postnum = atoi(result.c_str());
+ if (postnum == 0)
+ break;
+ std::cout << "Checking remained postgres process and there is "
+ << postnum << " remained\n";
+ status = this->runCommand(slavesPhysicalHosts[i], postshowcmd, "",
+ result, OS_COMMAND);
+ std::cout << "remained process is:\n" << result << std::endl;
+ }
+ sleep(10);
+ }
+ if (j == MAX_RETRY_NUM) {
+ status = this->runCommand(slavesPhysicalHosts[i], postcheckcmd, "",
+ result, OS_COMMAND);
+ std::cout << "Checking remained postgres process and there is "
+ << result;
+ return false;
+ }
+ }
+ return true;
+}
+
+bool CompentConfig::killRemainedProcess(int32_t segmentNum) {
+ string hawqprocess = "ps -ef | grep postgres | grep con | grep -v idle | "
+ "grep -v grep | awk '{print \\$2}' ";
+ string cmdkillstr = hawqprocess + " | sudo xargs kill -9 ";
+ string cmdoutput;
+ bool status;
+
+ if (segmentNum == 0) {
+ for (uint16_t i = 0; i < masterPhysicalHosts.size(); i++) {
+ status = this->runCommand(masterPhysicalHosts[i], hawqprocess, "",
+ cmdoutput, OS_COMMAND);
+ if (cmdoutput.size() != 0) {
+ std::cout << "Kill process on " << masterPhysicalHosts[i]
+ << " and pid is " << cmdoutput;
+ status = this->runCommand(masterPhysicalHosts[i], cmdkillstr, "",
+ cmdoutput, OS_COMMAND);
+ if (!status)
+ std::cout << "Kill process on " << masterPhysicalHosts[i]
+ << " failed!\n"
+ << cmdoutput;
+ }
+ }
+ }
+ int32_t slavesize =
+ (segmentNum == 0) ? slavesPhysicalHosts.size() : segmentNum;
+ for (uint16_t i = 0; i < slavesize; i++) {
+ status = this->runCommand(slavesPhysicalHosts[i], hawqprocess, "",
+ cmdoutput, OS_COMMAND);
+ if (cmdoutput.size() != 0) {
+ std::cout << "Kill process on " << slavesPhysicalHosts[i]
+ << " and pid is " << cmdoutput;
+ status = this->runCommand(slavesPhysicalHosts[i], cmdkillstr, "",
+ cmdoutput, OS_COMMAND);
+ if (!status)
+ std::cout << "Kill process on " << masterPhysicalHosts[i]
+ << " failed!\n"
+ << cmdoutput;
+ }
+ }
+ return true;
+}
+
+} // namespace test
+} // namespace hawq
diff --git a/src/test/feature/lib/compent_config.h b/src/test/feature/lib/compent_config.h
new file mode 100644
index 0000000..3d8d39a
--- /dev/null
+++ b/src/test/feature/lib/compent_config.h
@@ -0,0 +1,139 @@
+/*
+[- * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+#ifndef HAWQ_SRC_TEST_FEATURE_LIB_COMPENT_CONFIG_H_
+#define HAWQ_SRC_TEST_FEATURE_LIB_COMPENT_CONFIG_H_
+
+#include <string>
+#include <vector>
+
+#include "sql_util.h"
+#include "xml_parser.h"
+
+enum CommandType {
+ OS_COMMAND,
+ HAWQ_COMMAND,
+ HDFS_COMMAND,
+ YARN_COMMAND,
+ HAWQ_OS_COMMAND
+};
+
+namespace hawq {
+namespace test {
+
+#define MASTERPOS 0
+#define STANDBYPOS 1
+#define MAX_RETRY_NUM 10
+
+/**
+ * CompentConfig common library.
+ * Get information of cloud cluster and run command to physical cluster and cloud cluster
+
+ */
+class CompentConfig {
+ public:
+ /**
+ * CompentConfig constructor
+ */
+ CompentConfig();
+
+ /**
+ * CompentConfig destructor
+ */
+ virtual ~CompentConfig() {}
+
+ //return hawq user
+ std::string getHawqUser();
+
+ //Run command on specific host
+ //@param hostname: hostname the host which comand should run
+ //@param command: the running command
+ //@param user: user the specific user
+ //@param result: the command output
+ //@param cmdType: the command type
+
+ bool runCommand(const std::string hostname,
+ const std::string &command,
+ const std::string user,
+ std::string &result,
+ CommandType cmdType = OS_COMMAND);
+
+ //Run command on specific host
+ //@param command: the running command
+ //@param user: user the specific user
+ //@param result: the command output
+ //@param cmdType: the command type
+ bool runCommand(const std::string &command,
+ const std::string user,
+ std::string &result,
+ CommandType cmdType = OS_COMMAND);
+
+ //Run command on specific host and find the specific string
+ //@param command: the running command
+ //@param user: user the specific user
+ //@param findstring: the find string
+ //@param cmdtype: the command type
+ bool runCommandAndFind(const std::string &command,
+ const std::string user,
+ const std::string &findstring,
+ CommandType cmdType = OS_COMMAND);
+
+ //Run command on specific host and generate the host and port
+ //@param command: the running command
+ //@param user: user the specific user
+ //@param datanodelist:
+ //@param port: the command type
+ void runCommandAndGetNodesPorts(const std::string &command,
+ const std::string user,
+ std::vector<std::string> &datanodelist,
+ std::vector<int> &port,
+ CommandType cmdType = OS_COMMAND);
+
+ // Return kubnet cluster information. It is only valid when iscloud is true
+ virtual bool getCluster() { return false;}
+
+ bool isCloudCluster() { return iscloud; }
+
+ bool checkRemainedProcess();
+ bool killRemainedProcess(int32_t segmentNum = 0 );
+
+
+ protected:
+ // Get kubenet cluster information. It is only valid when iscloud is true
+ bool __fetchKubeCluster();
+
+ // Get kubenet cluster information. It is only valid when iscloud is true
+ void __copyCluster(std::vector<std::string> & masterhosts, std::vector<std::string> &slavehost);
+
+ //whether it is single node cluster just for demo
+ bool isdemo;
+ protected:
+ std::vector<std::string> masterPhysicalHosts;
+ std::vector<std::string> slavesPhysicalHosts;
+ //inidicate whether it is cloud cluster or physical cluster
+ bool iscloud;
+ //indicate which active master
+ bool activeMasterPos;
+ std::string compentPath;
+ std::unique_ptr<hawq::test::PSQL> conn;
+};
+
+} // namespace test
+} // namespace hawq
+
+#endif /* HAWQ_SRC_TEST_FEATURE_LIB_COMPENT_CONFIG_H_ */
diff --git a/src/test/feature/lib/hdfs_config.cpp b/src/test/feature/lib/hdfs_config.cpp
index 43f30ea..24938bb 100644
--- a/src/test/feature/lib/hdfs_config.cpp
+++ b/src/test/feature/lib/hdfs_config.cpp
@@ -33,134 +33,142 @@ namespace hawq {
namespace test {
HdfsConfig::HdfsConfig() {
- std::string user = HAWQ_USER;
- if(user.empty()) {
- struct passwd *pw;
- uid_t uid = geteuid();
- pw = getpwuid(uid);
- user.assign(pw->pw_name);
- }
- conn.reset(new hawq::test::PSQL(HAWQ_DB, HAWQ_HOST, HAWQ_PORT, user, HAWQ_PASSWORD));
- isLoadFromHawqConfigFile = false;
- isLoadFromHdfsConfigFile = false;
-}
-
-void HdfsConfig::runCommand(const string &command,
- bool ishdfsuser,
- string &result) {
- string cmd = "";
- if (ishdfsuser) {
- cmd = "/usr/bin/sudo -Eu ";
- cmd.append(getHdfsUser());
- cmd.append(" env \"PATH=$PATH\" ");
- cmd.append(command);
- } else {
- cmd = command;
- }
- Command c(cmd);
- result = c.run().getResultOutput();
+ isLoadFromHawqConfigFile = false;
+ isLoadFromHdfsConfigFile = false;
+ isHACluster = this->__isHA();
+ this->getCluster();
}
-bool HdfsConfig::runCommandAndFind(const string &command,
- bool ishdfsuser,
- const string &findstring) {
- string result = "";
- runCommand(command, ishdfsuser, result);
- auto lines = hawq::test::split(result, '\n');
- for (size_t i=0; i<lines.size(); i++) {
- string valueLine = lines[i];
- int find = valueLine.find(findstring);
- if (find >= 0) {
- return true;
+bool HdfsConfig::__isHA() {
+ const hawq::test::PSQLQueryResult &result = conn->getQueryResult(
+ "SELECT substring(fselocation from length('hdfs:// ') for (position('/' in substring(fselocation from length('hdfs:// ')))-1)::int) "
+ "FROM pg_filespace pgfs, pg_filespace_entry pgfse "
+ "WHERE pgfs.fsname = 'dfs_system' AND pgfse.fsefsoid=pgfs.oid ;");
+ std::vector<std::vector<string>> table = result.getRows();
+ if (table.size() > 0) {
+ int find = table[0][0].find(":");
+ if (find < 0) {
+ return true;
+ } else {
+ return false;
+ }
}
- }
- return false;
+ return false;
}
-void HdfsConfig::runCommandAndGetNodesPorts(const string &command,
- std::vector<string> &datanodelist,
- std::vector<int> &port) {
- string result = "";
- runCommand(command, true, result);
- auto lines = hawq::test::split(result, '\n');
- for (size_t i = 0; i < lines.size(); i++) {
- string valueLine = lines[i];
- auto datanodeInfo = hawq::test::split(valueLine, ':');
- if (datanodeInfo.size() == 3) {
- int portStart = datanodeInfo[2].find_first_of('(');
- int portEnd = datanodeInfo[2].find_first_of(')');
- string datanodePort = datanodeInfo[2].substr(0, portStart);
- string datanodeHost = datanodeInfo[2].substr(portStart+1, portEnd-portStart-1);
- datanodelist.push_back(hawq::test::trim(datanodeHost));
- port.push_back(std::stoi(hawq::test::trim(datanodePort)));
+// Return kubenet cluster information. It is only valid when iscloud is true
+bool HdfsConfig::getCluster()
+{
+ bool status;
+ if (masterPhysicalHosts.size() == 0 )
+ {
+ if (iscloud)
+ {
+ status = this->__fetchKubeCluster();
+ if (!status)
+ return false;
+ this->getHadoopHome();
+ hdfsuser = this->getHdfsUser();
+ if (hdfsuser.size() == 0)
+ return false;
+ }
+ else
+ {
+ if (isHACluster)
+ {
+ string hostname = HAWQ_HOST;
+ masterPhysicalHosts.push_back(hostname);
+ this->getHadoopHome();
+ hdfsuser = this->getHdfsUser();
+ std::vector<string> masterhosts;
+ std::vector<int> namenodePort;
+ this->getNamenodes(masterhosts, namenodePort);
+ masterPhysicalHosts.clear();
+ for (uint16_t i =0; i < masterhosts.size(); i++)
+ masterPhysicalHosts.push_back(masterhosts[i]);
+ }
+ else
+ {
+ string nameportstring;
+ this->getNamenodeHost(nameportstring);
+ int pos = nameportstring.find(":");
+ masterPhysicalHosts.push_back(nameportstring.substr(0, pos));
+ this->getHadoopHome();
+ hdfsuser = this->getHdfsUser();
+ }
+ std::vector<int> datanodePort;
+ this->getDatanodelist(slavesPhysicalHosts, datanodePort);
+ }
}
- }
+ return true;
}
+
string HdfsConfig::getHdfsUser() {
- string cmd = "ps aux|grep hdfs.server|grep -v grep";
- Command c(cmd);
- string result = c.run().getResultOutput();
- auto lines = hawq::test::split(result, '\n');
- if (lines.size() >= 1) {
- return hawq::test::trim(hawq::test::split(lines[lines.size()-1], ' ')[0]);
- }
- return "hdfs";
+ if (this->hdfsuser.size() == 0 )
+ {
+ string command = "ps aux|grep hdfs.server|grep -v grep";
+ string result;
+ // NOTE: It should be HDFS_COMMAND since we need to login the hdfs container to get user
+ bool status = this->runCommand(masterPhysicalHosts[MASTERPOS], command,
+ "", result, HDFS_COMMAND);
+ if (!status)
+ return "";
+ auto lines = hawq::test::split(result, '\n');
+ if (lines.size() >= 1) {
+ return hawq::test::trim(hawq::test::split(lines[lines.size()-1], ' ')[0]);
+ }
+ }
+ return hdfsuser;
}
bool HdfsConfig::LoadFromHawqConfigFile() {
- if (isLoadFromHawqConfigFile) {
+ if (!isLoadFromHawqConfigFile)
+ {
+ string confPath = iscloud ? getenv("CLOUD_CLUSTER_ENV") : getenv("GPHOME") ;
+ if (confPath.empty() || isdemo)
+ return false;
+ if (iscloud)
+ {
+ confPath.append("/hawq/hdfs-client.xml");
+ }
+ else
+ {
+ confPath.append("/etc/hdfs-client.xml");
+ }
+
+ hawqxmlconf.reset(new XmlConfig(confPath));
+ if (!hawqxmlconf->parse())
+ return false;
+ isLoadFromHawqConfigFile = true;
+ }
return true;
- }
- const char *env = getenv("GPHOME");
- string confPath = env ? env : "";
- if (confPath != "") {
- confPath.append("/etc/hdfs-client.xml");
- } else {
- return false;
- }
-
- hawqxmlconf.reset(new XmlConfig(confPath));
- if (!hawqxmlconf->parse())
- return false;
-
- isLoadFromHawqConfigFile = true;
- return true;
}
bool HdfsConfig::LoadFromHdfsConfigFile() {
- if (isLoadFromHdfsConfigFile) {
+ if (!isLoadFromHdfsConfigFile)
+ {
+ string confPath = iscloud ? getenv("CLOUD_CLUSTER_ENV") : this->getHadoopHome() ;
+ if (confPath.empty() || isdemo)
+ return false;
+ if (iscloud)
+ {
+ confPath.append("/hadoop/demo/hdfs-site.xml");
+ }
+ else
+ {
+ confPath.append("/etc/hadoop/hdfs-site.xml");
+ }
+
+ hdfsxmlconf.reset(new XmlConfig(confPath));
+ if (!hdfsxmlconf->parse())
+ return false;
+
+ isLoadFromHdfsConfigFile = true;
+ }
return true;
- }
- string confPath=getHadoopHome();
- if (confPath == "") {
- return false;
- }
- confPath.append("/etc/hadoop/hdfs-site.xml");
- hdfsxmlconf.reset(new XmlConfig(confPath));
- if (!hdfsxmlconf->parse())
- return false;
-
- isLoadFromHdfsConfigFile = true;
- return true;
}
-int HdfsConfig::isHA() {
- const hawq::test::PSQLQueryResult &result = conn->getQueryResult(
- "SELECT substring(fselocation from length('hdfs:// ') for (position('/' in substring(fselocation from length('hdfs:// ')))-1)::int) "
- "FROM pg_filespace pgfs, pg_filespace_entry pgfse "
- "WHERE pgfs.fsname = 'dfs_system' AND pgfse.fsefsoid=pgfs.oid ;");
- std::vector<std::vector<string>> table = result.getRows();
- if (table.size() > 0) {
- int find = table[0][0].find(":");
- if (find < 0) {
- return 1;
- } else {
- return 0;
- }
- }
- return -1;
-}
int HdfsConfig::isConfigKerberos() {
bool ret = LoadFromHawqConfigFile();
@@ -176,7 +184,8 @@ int HdfsConfig::isConfigKerberos() {
}
int HdfsConfig::isTruncate() {
- if (runCommandAndFind("hadoop fs -truncate", false, "-truncate: Unknown command")) {
+ if (this->runCommandAndFind("hadoop fs -truncate",
+ hdfsuser, "-truncate: Unknown command", HDFS_COMMAND)) {
return 0;
} else {
return 1;
@@ -184,23 +193,27 @@ int HdfsConfig::isTruncate() {
}
string HdfsConfig::getHadoopHome() {
- string result = "";
- runCommand("ps -ef|grep hadoop", false, result);
- string hadoopHome = "";
- auto lines = hawq::test::split(result, '\n');
- for (size_t i=0; i<lines.size()-1; i++) {
- string valueLine = lines[i];
- string findstring = "-Dhadoop.home.dir=";
- int pos = valueLine.find(findstring);
- if (pos >=0 ) {
- string valueTmp = valueLine.substr(pos+findstring.size());
- int valueEnd = valueTmp.find_first_of(" ");
- string value = valueTmp.substr(0, valueEnd);
- hadoopHome = hawq::test::trim(value);
- return hadoopHome;
+ if (this->compentPath.size () == 0)
+ {
+ string result = "";
+ bool status = this->runCommand(masterPhysicalHosts[MASTERPOS], "ps -ef|grep hadoop",
+ "", result, OS_COMMAND );
+ if (!status)
+ return "";
+ auto lines = hawq::test::split(result, '\n');
+ for (size_t i=0; i<lines.size()-1; i++) {
+ string valueLine = lines[i];
+ string findstring = "-Dhadoop.home.dir=";
+ int pos = valueLine.find(findstring);
+ if (pos >=0 ) {
+ string valueTmp = valueLine.substr(pos+findstring.size());
+ int valueEnd = valueTmp.find_first_of(" ");
+ string value = valueTmp.substr(0, valueEnd);
+ compentPath = hawq::test::trim(value);
+ }
+ }
}
- }
- return hadoopHome;
+ return this->compentPath;
}
bool HdfsConfig::getNamenodeHost(string &namenodehost) {
@@ -227,6 +240,32 @@ bool HdfsConfig::getStandbyNamenode(string &standbynamenode,
return getHANamenode("standby", standbynamenode, port);
}
+bool HdfsConfig::checkNamenodesHealth() {
+ if (isHA() <= 0) {
+ return false;
+ }
+ string namenodeService = "";
+ string nameServiceValue = getParameterValue("dfs.nameservices");
+ string haNamenodesName = "dfs.ha.namenodes.";
+ haNamenodesName.append(hawq::test::trim(nameServiceValue));
+ string haNamenodesValue = getParameterValue(haNamenodesName);
+ auto haNamenodes = hawq::test::split(haNamenodesValue, ',');
+ for (size_t i = 0; i < haNamenodes.size(); i++) {
+ string haNamenode = hawq::test::trim(haNamenodes[i]);
+ string cmd = "hdfs haadmin -checkHealth ";
+ cmd.append(haNamenode);
+ string checkResult;
+ bool status = this->runCommand(cmd,
+ hdfsuser, checkResult, HDFS_COMMAND );
+ if (!status)
+ return false;
+ if (checkResult.size() > 0)
+ return false;
+ }
+
+ return true;
+}
+
bool HdfsConfig::getHANamenode(const string &namenodetype,
string &namenode,
int &port) {
@@ -234,23 +273,30 @@ bool HdfsConfig::getHANamenode(const string &namenodetype,
return false;
}
string namenodeService = "";
- string nameServiceValue = hawqxmlconf->getString("dfs.nameservices");
+ string nameServiceValue = getParameterValue("dfs.nameservices");
string haNamenodesName = "dfs.ha.namenodes.";
haNamenodesName.append(hawq::test::trim(nameServiceValue));
- string haNamenodesValue = hawqxmlconf->getString(haNamenodesName);
+ string haNamenodesValue = getParameterValue(haNamenodesName);
auto haNamenodes = hawq::test::split(haNamenodesValue, ',');
- for (size_t i = 0; i < haNamenodes.size(); i++) {
+ size_t i;
+ string result;
+ for ( i = 0; i < haNamenodes.size(); i++) {
string haNamenode = hawq::test::trim(haNamenodes[i]);
string cmd = "hdfs haadmin -getServiceState ";
cmd.append(haNamenode);
- if (runCommandAndFind(cmd, true, namenodetype)) {
+ bool status = this->runCommandAndFind(cmd, hdfsuser, namenodetype, HDFS_COMMAND );
+ if (status) {
namenodeService = haNamenode;
break;
}
}
- string rpcAddressName = "dfs.namenode.rpc-address.gphd-cluster.";
- rpcAddressName.append(namenodeService);
- string rpcAddressValue = hawqxmlconf->getString(rpcAddressName);
+
+ if (i == haNamenodes.size())
+ return false;
+
+ string rpcAddressName = "dfs.namenode.rpc-address.";
+ rpcAddressName.append(nameServiceValue).append(".").append(namenodeService);
+ string rpcAddressValue = getParameterValue(rpcAddressName);
auto namenodeInfo = hawq::test::split(rpcAddressValue, ':');
namenode = hawq::test::trim(namenodeInfo[0]);
port = std::stoi(hawq::test::trim(namenodeInfo[1]));
@@ -258,38 +304,73 @@ bool HdfsConfig::getHANamenode(const string &namenodetype,
}
void HdfsConfig::getNamenodes(std::vector<string> &namenodes,
- std::vector<int> &port) {
- string result = "";
- runCommand("hdfs getconf -nnRpcAddresses", true, result);
- auto lines = hawq::test::split(result, '\n');
- for (size_t i = 0; i < lines.size(); i++) {
- string valueLine = lines[i];
- auto namenodeInfo = hawq::test::split(valueLine, ':');
- if (namenodeInfo.size() == 2) {
- namenodes.push_back(hawq::test::trim(namenodeInfo[0]));
- port.push_back(std::stoi(hawq::test::trim(namenodeInfo[1])));
- }
+ std::vector<int> &port)
+{
+ namenodes.clear();
+ port.clear();
+ string result = "";
+ bool status = this->runCommand("hdfs getconf -nnRpcAddresses",
+ hdfsuser, result, HDFS_COMMAND );
+ if (!status)
+ return;
+ auto lines = hawq::test::split(result, '\n');
+ for (size_t i = 0; i < lines.size(); i++) {
+ string valueLine = lines[i];
+ auto namenodeInfo = hawq::test::split(valueLine, ':');
+ if (namenodeInfo.size() == 2) {
+ namenodes.push_back(hawq::test::trim(namenodeInfo[0]));
+ port.push_back(std::stoi(hawq::test::trim(namenodeInfo[1])));
+ }
}
}
void HdfsConfig::getDatanodelist(std::vector<string> &datanodelist,
std::vector<int> &port) {
- runCommandAndGetNodesPorts("hdfs dfsadmin -report | grep Name", datanodelist, port);
+ this->runCommandAndGetNodesPorts("hdfs dfsadmin -report | grep Name",
+ hdfsuser, datanodelist, port,
+ HDFS_COMMAND);
}
void HdfsConfig::getActiveDatanodes(std::vector<string> &activedatanodes,
std::vector<int> &port) {
- runCommandAndGetNodesPorts("hdfs dfsadmin -report -live | grep Name", activedatanodes, port);
+ this->runCommandAndGetNodesPorts("hdfs dfsadmin -report -live | grep Name",
+ hdfsuser, activedatanodes, port,
+ HDFS_COMMAND);
+}
+
+
+int HdfsConfig::getActiveDatanodesNum() {
+ string resultnum;
+ bool status = this->runCommand(
+ "hdfs dfsadmin -report -live | grep Name | wc -l",
+ hdfsuser, resultnum, HDFS_COMMAND );
+ if (!status)
+ return -1;
+ auto lines = hawq::test::split(resultnum, '\n');
+ for (size_t i = 0; i < lines.size(); i++) {
+
+ int pos = lines[i].find(" WARN ");
+ if (pos > 0)
+ continue;
+ else
+ return std::atoi(lines[i].c_str());
+ }
+ return -1;
+
}
int HdfsConfig::isSafemode() {
- if (runCommandAndFind("hadoop fs -mkdir /tmp_hawq_test", false, "Name node is in safe mode.")) {
- return 1;
- }
- string cmd = "hadoop fs -rm -r /tmp_hawq_test";
- Command c_teardown(cmd);
- string result = c_teardown.run().getResultOutput();
- return 0;
+ bool status = this->runCommandAndFind(
+ "hadoop fs -mkdir /tmp_hawq_test",
+ hdfsuser, "Name node is in safe mode.", HDFS_COMMAND );
+ if (status)
+ return 1;
+ string cmd = "hadoop fs -rm -r /tmp_hawq_test";
+ string result;
+ status = this->runCommand(cmd, hdfsuser, result, HDFS_COMMAND );
+ if (status)
+ return 0;
+ return -1;
}
string HdfsConfig::getParameterValue(const string ¶meterName) {
diff --git a/src/test/feature/lib/hdfs_config.h b/src/test/feature/lib/hdfs_config.h
index 2a7fabd..89e4860 100644
--- a/src/test/feature/lib/hdfs_config.h
+++ b/src/test/feature/lib/hdfs_config.h
@@ -25,6 +25,7 @@
#include "psql.h"
#include "sql_util.h"
#include "xml_parser.h"
+#include "lib/compent_config.h"
namespace hawq {
namespace test {
@@ -34,7 +35,7 @@ namespace test {
* including checking state of namenodes and datanodes, get parameter value
* @author Chunling Wang
*/
-class HdfsConfig {
+class HdfsConfig : public CompentConfig {
public:
/**
* HdfsConfig constructor
@@ -48,9 +49,9 @@ class HdfsConfig {
/**
* whether HDFS is in HA mode
- * @return 1 if HDFS is HA, 0 if HDFS is not HA, -1 if there is an error
+ * @return true if HDFS is HA, false if HDFS is not HA
*/
- int isHA();
+ bool isHA() { return isHACluster; }
/**
* whether HDFS is kerbos
@@ -70,6 +71,7 @@ class HdfsConfig {
*/
std::string getHadoopHome();
+ bool getCluster();
/**
* get HDFS namenode's host ('hostname:port' for non-HA, 'servicename' for HA)
* @ param namenodehost, namenode host reference which will be set
@@ -103,6 +105,7 @@ class HdfsConfig {
void getNamenodes(std::vector<std::string> &namenodes,
std::vector<int> &port);
+ bool checkNamenodesHealth();
/**
* get HDFS datanodes information
* @param datanodelist, datanodes' hostnames reference which will be set
@@ -157,18 +160,15 @@ class HdfsConfig {
*/
bool setParameterValue(const std::string ¶meterName, const std::string ¶meterValue);
- private:
- void runCommand(const std::string &command, bool ishdfsuser, std::string &result);
-
- bool runCommandAndFind(const std::string &command, bool ishdfsuser, const std::string &findstring);
-
- void runCommandAndGetNodesPorts(const std::string &command, std::vector<std::string> &datanodelist, std::vector<int> &port);
-
+ //return active data node number
+ int getActiveDatanodesNum();
+
/**
* @return hdfs user
*/
std::string getHdfsUser();
+ protected:
/**
* load key-value parameters in ./etc/hdfs-client.xml
* @return true if succeeded
@@ -190,12 +190,16 @@ class HdfsConfig {
*/
bool getHANamenode(const std::string &namenodetype, std::string &namenode, int &port);
+ bool isHACluster;
+
+ std::string hdfsuser;
+ private:
+ bool __isHA();
private:
std::unique_ptr<XmlConfig> hawqxmlconf;
- std::unique_ptr<XmlConfig> hdfsxmlconf;
bool isLoadFromHawqConfigFile;
+ std::unique_ptr<XmlConfig> hdfsxmlconf;
bool isLoadFromHdfsConfigFile;
- std::unique_ptr<hawq::test::PSQL> conn;
};
} // namespace test