---- create required databases with different encoding for compatibility test.
CREATE DATABASE DB_ASCII_BULKLOAD_COMPATIBILITY_TEST WITH ENCODING 'SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;
CREATE DATABASE DB_LATIN1_BULKLOAD_COMPATIBILITY_TEST WITH ENCODING 'Latin1' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;
CREATE DATABASE DB_GBK_BULKLOAD_COMPATIBILITY_TEST WITH ENCODING 'GBK' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;
CREATE DATABASE DB_EUCJIS2004_BULKLOAD_COMPATIBILITY_TEST WITH ENCODING 'EUCJIS2004' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;
CREATE DATABASE TD_FORMAT_DB DBCOMPATIBILITY 'TD' template template0 lc_ctype 'C' lc_collate 'C';
----
---- load with bulkload compatible_illegal_chars
----
----smartcare case
CREATE TABLE SC_INVALID_ENCODING_TEST (
batchno numeric(12,0) null,
starttime numeric(18,0) null,
rat numeric(1,0) null,
apn varchar(64) null,
prot_category numeric(5,0) null,
prot_type numeric(5,0) null,
host varchar(64) null,
l4_ul_throughput numeric(20,0) null,
l4_dw_throughput numeric(20,0) null,
l4_ul_packets numeric(18,0) null,
l4_dw_packets numeric(18,0) null,
tcpconncount numeric(10,0) null,
tcpconnsucccount numeric(10,0) null,
carrier_id numeric(5,0) null,
tcp_rtt numeric(20,0) null,
app_id numeric(18,0) null
)
with(orientation=column, compression=low)
distribute by hash(carrier_id,app_id);
CREATE FOREIGN TABLE EXT_SC_INVALID_ENCODING_TEST (
batchno numeric(12,0) null,
starttime numeric(18,0) null,
rat numeric(1,0) null,
apn varchar(64) null,
prot_category numeric(5,0) null,
prot_type numeric(5,0) null,
host varchar(64) null,
l4_ul_throughput numeric(20,0) null,
l4_dw_throughput numeric(20,0) null,
l4_ul_packets numeric(18,0) null,
l4_dw_packets numeric(18,0) null,
tcpconncount numeric(10,0) null,
tcpconnsucccount numeric(10,0) null,
carrier_id numeric(5,0) null,
tcp_rtt numeric(20,0) null,
app_id numeric(18,0) null
)SERVER gsmpp_server OPTIONS(format 'text', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/smartcare_test.data', encoding 'sql_ascii', delimiter '|', mode 'normal', compatible_illegal_chars 'true', ignore_extra_data 'true');
INSERT INTO SC_INVALID_ENCODING_TEST SELECT * FROM EXT_SC_INVALID_ENCODING_TEST;
SELECT * FROM SC_INVALID_ENCODING_TEST;
DROP TABLE SC_INVALID_ENCODING_TEST;
DROP FOREIGN TABLE EXT_SC_INVALID_ENCODING_TEST;
----invalid chars for server_encoding=UTF8
----(1)local2utf8 test
----(1.1)only char type test
CREATE TABLE COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE (
COL1_CHAR CHAR(100) NOT NULL,
COL2_VARCHAR VARCHAR(100) NOT NULL,
COL3_VARCHAR2 VARCHAR2(100) NOT NULL,
COL4_NVARCHAR2 NVARCHAR2(100) NOT NULL,
COL5_CLOB CLOB NOT NULL,
COL6_TEXT TEXT NOT NULL
);
----(1.1.1)for text format
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE (
COL1_CHAR CHAR(100) NOT NULL,
COL2_VARCHAR VARCHAR(100) NOT NULL,
COL3_VARCHAR2 VARCHAR2(100) NOT NULL,
COL4_NVARCHAR2 NVARCHAR2(100) NOT NULL,
COL5_CLOB CLOB NOT NULL,
COL6_TEXT TEXT NOT NULL
)SERVER gsmpp_server OPTIONS(format 'text', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_text_format.data', encoding 'utf8', delimiter '|', mode 'normal', compatible_illegal_chars 'true');
INSERT INTO COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
SELECT TRIM(COL1_CHAR), TRIM(COL2_VARCHAR), TRIM(COL3_VARCHAR2), TRIM(COL4_NVARCHAR2), COL5_CLOB, COL6_TEXT FROM COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE ORDER BY TRIM(COL1_CHAR);
ALTER FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE OPTIONS (SET compatible_illegal_chars 'false');
SELECT TRIM(COL1_CHAR), TRIM(COL2_VARCHAR), TRIM(COL3_VARCHAR2), TRIM(COL4_NVARCHAR2), COL5_CLOB, COL6_TEXT FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE ORDER BY TRIM(COL1_CHAR);
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
TRUNCATE COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
----(1.1.2)for csv format
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE (
COL1_CHAR CHAR(100) NOT NULL,
COL2_VARCHAR VARCHAR(100) NOT NULL,
COL3_VARCHAR2 VARCHAR2(100) NOT NULL,
COL4_NVARCHAR2 NVARCHAR2(100) NOT NULL,
COL5_CLOB CLOB NOT NULL,
COL6_TEXT TEXT NOT NULL
)SERVER gsmpp_server OPTIONS(format 'csv', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_csv_format.data', encoding 'utf8', delimiter '|', mode 'normal', compatible_illegal_chars 'true');
INSERT INTO COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
SELECT TRIM(COL1_CHAR), TRIM(COL2_VARCHAR), TRIM(COL3_VARCHAR2), TRIM(COL4_NVARCHAR2), COL5_CLOB, COL6_TEXT FROM COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE ORDER BY TRIM(COL1_CHAR);
ALTER FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE OPTIONS (SET compatible_illegal_chars 'false');
SELECT TRIM(COL1_CHAR), TRIM(COL2_VARCHAR), TRIM(COL3_VARCHAR2), TRIM(COL4_NVARCHAR2), COL5_CLOB, COL6_TEXT FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE ORDER BY TRIM(COL1_CHAR);
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
TRUNCATE COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
----(1.1.3)for fixed format
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE (
COL1_CHAR CHAR(20) POSITION(0, 20) NOT NULL,
COL2_VARCHAR VARCHAR(20) POSITION(20, 20) NOT NULL,
COL3_VARCHAR2 VARCHAR2(20) POSITION(40, 20) NOT NULL,
COL4_NVARCHAR2 NVARCHAR2(20) POSITION(60, 20) NOT NULL,
COL5_CLOB CLOB POSITION(80, 20) NOT NULL,
COL6_TEXT TEXT POSITION(100, 20) NOT NULL
)SERVER gsmpp_server OPTIONS(format 'fixed', fix '120', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_fixed_format.data', encoding 'utf8', mode 'normal', compatible_illegal_chars 'true');
INSERT INTO COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
SELECT TRIM(COL1_CHAR), TRIM(COL2_VARCHAR), TRIM(COL3_VARCHAR2), TRIM(COL4_NVARCHAR2), COL5_CLOB, COL6_TEXT FROM COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE ORDER BY TRIM(COL1_CHAR);
ALTER FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE OPTIONS (SET compatible_illegal_chars 'false');
SELECT TRIM(COL1_CHAR), TRIM(COL2_VARCHAR), TRIM(COL3_VARCHAR2), TRIM(COL4_NVARCHAR2), COL5_CLOB, COL6_TEXT FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE ORDER BY TRIM(COL1_CHAR);
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE
(
C_CHAR CHAR(50) POSITION(0,30),
C_NULL BIGINT POSITION(30,0),
C_VARCHAR VARCHAR(50) POSITION(30,30),
C_NUMERIC NUMERIC(20,5) POSITION(60,10)
)SERVER gsmpp_server OPTIONS (LOCATION 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_fixed_format_TESTTABLE.data',FORMAT 'FIXED',MODE 'Normal',COMPATIBLE_ILLEGAL_CHARS 'TRUE',ENCODING 'UTF8');
SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
DROP TABLE COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
CREATE TABLE COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE
(
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_NUMERIC NUMERIC(20,5),
C_DP DOUBLE PRECISION
) DISTRIBUTE BY HASH(C_BIGINT);
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE
(
C_BIGINT BIGINT POSITION(0,5),
C_CHAR_1 CHAR(100) POSITION(5,10),
C_CHAR_2 CHAR(100) POSITION(15,29),
C_VARCHAR VARCHAR(1024) POSITION(44,29),
C_TEXT TEXT POSITION(73,29),
C_CLOB CLOB POSITION(102,29),
C_NUMERIC NUMERIC(20,5) POSITION(131,14),
C_DP DOUBLE PRECISION POSITION(145,14)
)SERVER gsmpp_server OPTIONS (LOCATION 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_fixed_format_TESTTABLE_all.data',FORMAT 'FIXED',MODE 'Normal',COMPATIBLE_ILLEGAL_CHARS 'TRUE',ENCODING 'UTF8');
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
INSERT INTO COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
SELECT COUNT(*) FROM COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
DROP TABLE COMPATIBLE_ILLEGAL_CHARS_TEST_JUST_CHAR_TYPE;
----(1.2)mixed type test
CREATE TABLE COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE (
COL1_INT BIGINT NOT NULL,
COL2_CHAR CHAR(100) NOT NULL,
COL3_VARCHAR VARCHAR(100) NOT NULL,
COL4_VARCHAR2 VARCHAR2(100) NOT NULL,
COL5_RAW RAW NOT NULL,
COL6_NVARCHAR2 NVARCHAR2(100) NOT NULL,
COL7_CLOB CLOB NOT NULL,
COL8_TEXT TEXT NOT NULL,
COL9_TIMESTAMP TIMESTAMP NOT NULL
);
----(1.2.1)for text format
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE (
COL1_INT BIGINT NOT NULL,
COL2_CHAR CHAR(100) NOT NULL,
COL3_VARCHAR VARCHAR(100) NOT NULL,
COL4_VARCHAR2 VARCHAR2(100) NOT NULL,
COL5_RAW RAW NOT NULL,
COL6_NVARCHAR2 NVARCHAR2(100) NOT NULL,
COL7_CLOB CLOB NOT NULL,
COL8_TEXT TEXT NOT NULL,
COL9_TIMESTAMP TIMESTAMP NOT NULL
)SERVER gsmpp_server OPTIONS(format 'text', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_mixed_type_text_format.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true', reject_limit 'unlimited')with err_cic_test;
SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
INSERT INTO COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
SELECT COUNT(*) FROM err_cic_test;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
----(1.2.1.1)gbk2utf8
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE (
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_DP1 DOUBLE PRECISION,
C_DP2 DOUBLE PRECISION
)SERVER gsmpp_server OPTIONS(format 'text', encoding 'gbk', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_mixed_type_text_format_gbk.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true', reject_limit 'unlimited')with err_cic_test;
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
ALTER FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE OPTIONS(SET compatible_illegal_chars 'false');
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
CREATE TABLE gbk2utf8_test(a varchar(4));
COPY gbk2utf8_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_gbk2utf8_1.data' (format 'text', encoding 'GBK', delimiter '|', compatible_illegal_chars 'false');
COPY gbk2utf8_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_gbk2utf8_1.data' (format 'text', encoding 'GBK', delimiter '|', compatible_illegal_chars 'true');
COPY gbk2utf8_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_gbk2utf8_2.data' (format 'text', encoding 'GBK', delimiter '|', compatible_illegal_chars 'false');
COPY gbk2utf8_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_gbk2utf8_2.data' (format 'text', encoding 'GBK', delimiter '|', compatible_illegal_chars 'true');
SELECT * FROM gbk2utf8_test ORDER BY a;
DROP TABLE gbk2utf8_test;
----(1.2.1.2)utf82gbk
\c DB_GBK_BULKLOAD_COMPATIBILITY_TEST;
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE (
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_DP1 DOUBLE PRECISION,
C_DP2 DOUBLE PRECISION
)SERVER gsmpp_server OPTIONS(format 'text', encoding 'utf8', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_mixed_type_text_format_gbk.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true', reject_limit 'unlimited')with err_cic_test;
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
ALTER FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE OPTIONS(SET compatible_illegal_chars 'false');
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
CREATE TABLE utf82gbk_test(a varchar(4));
COPY utf82gbk_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82gbk_1.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'false');
COPY utf82gbk_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82gbk_1.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'true');
COPY utf82gbk_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82gbk_2.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'false');
COPY utf82gbk_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82gbk_2.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'true');
SELECT * FROM utf82gbk_test ORDER BY a;
DROP TABLE utf82gbk_test;
\c regression;
----(1.2.1.3)latin12utf8
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE (
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_DP1 DOUBLE PRECISION,
C_DP2 DOUBLE PRECISION
)SERVER gsmpp_server OPTIONS(format 'text', encoding 'latin1', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_mixed_type_text_format_latin1.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true', reject_limit 'unlimited')with err_cic_test;
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
ALTER FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE OPTIONS(SET compatible_illegal_chars 'false');
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
----(1.2.1.4)utf82latin1
\c DB_LATIN1_BULKLOAD_COMPATIBILITY_TEST;
SHOW server_encoding;
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE (
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_DP1 DOUBLE PRECISION,
C_DP2 DOUBLE PRECISION
)SERVER gsmpp_server OPTIONS(format 'text', encoding 'utf8', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_mixed_type_text_format_latin1.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true', reject_limit 'unlimited')with err_cic_test;
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
ALTER FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE OPTIONS(SET compatible_illegal_chars 'false');
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
CREATE TABLE utf82latin1_test(a varchar(4));
COPY utf82latin1_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82latin1_1.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'false');
COPY utf82latin1_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82latin1_1.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'true');
COPY utf82latin1_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82latin1_2.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'false');
COPY utf82latin1_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82latin1_2.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'true');
COPY utf82latin1_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82latin1_3.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'false');
COPY utf82latin1_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82latin1_3.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'true');
SELECT * FROM utf82latin1_test ORDER BY a;
DROP TABLE utf82latin1_test;
----(1.2.1.5)mic2latin1
SHOW server_encoding;
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE (
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_DP1 DOUBLE PRECISION,
C_DP2 DOUBLE PRECISION
)SERVER gsmpp_server OPTIONS(format 'text', encoding 'MULE_INTERNAL', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_mixed_type_text_format_mic.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true', reject_limit 'unlimited')with err_cic_test;
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
ALTER FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE OPTIONS(SET compatible_illegal_chars 'false');
SELECT COUNT(*) FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
CREATE TABLE mic2latin_test(a varchar(4));
COPY mic2latin_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_mic2latin_1.data' (format 'text', encoding 'MULE_INTERNAL', delimiter '|', compatible_illegal_chars 'false');
COPY mic2latin_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_mic2latin_1.data' (format 'text', encoding 'MULE_INTERNAL', delimiter '|', compatible_illegal_chars 'true');
COPY mic2latin_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_mic2latin_2.data' (format 'text', encoding 'MULE_INTERNAL', delimiter '|', compatible_illegal_chars 'false');
COPY mic2latin_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_mic2latin_2.data' (format 'text', encoding 'MULE_INTERNAL', delimiter '|', compatible_illegal_chars 'true');
COPY mic2latin_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_mic2latin_3.data' (format 'text', encoding 'MULE_INTERNAL', delimiter '|', compatible_illegal_chars 'false');
COPY mic2latin_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_mic2latin_3.data' (format 'text', encoding 'MULE_INTERNAL', delimiter '|', compatible_illegal_chars 'true');
SELECT * FROM mic2latin_test ORDER BY a;
DROP TABLE mic2latin_test;
----(1.2.1.6)latin12latin1
SHOW server_encoding;
CREATE TABLE LAIN1_TO_LATIN1
(
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_NUMERIC NUMERIC(20,5),
C_DP DOUBLE PRECISION
);
CREATE FOREIGN TABLE FT_LAIN1_TO_LATIN1
(
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_NUMERIC NUMERIC(20,5),
C_DP DOUBLE PRECISION
) SERVER gsmpp_server OPTIONS(format 'text', encoding 'latin1', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_single_byte_encoding.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true');
SELECT * FROM FT_LAIN1_TO_LATIN1;
ALTER FOREIGN TABLE FT_LAIN1_TO_LATIN1 OPTIONS(SET compatible_illegal_chars 'false');
SELECT * FROM FT_LAIN1_TO_LATIN1;
COPY LAIN1_TO_LATIN1 FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_single_byte_encoding.data' (format 'text', encoding 'latin1', delimiter '|', compatible_illegal_chars 'true');
SELECT * FROM LAIN1_TO_LATIN1;
TRUNCATE LAIN1_TO_LATIN1;
COPY LAIN1_TO_LATIN1 FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_single_byte_encoding.data' (format 'text', encoding 'latin1', delimiter '|', compatible_illegal_chars 'true');
DROP TABLE LAIN1_TO_LATIN1;
DROP FOREIGN TABLE FT_LAIN1_TO_LATIN1;
----(1.2.1.7)ascii2ascii
\c DB_ASCII_BULKLOAD_COMPATIBILITY_TEST;
SHOW server_encoding;
CREATE TABLE ASCII_TO_ASCII
(
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_NUMERIC NUMERIC(20,5),
C_DP DOUBLE PRECISION
);
CREATE FOREIGN TABLE FT_ASCII_TO_ASCII
(
C_BIGINT BIGINT,
C_CHAR_1 CHAR(100),
C_CHAR_2 CHAR(100),
C_VARCHAR VARCHAR(1024),
C_TEXT TEXT,
C_CLOB CLOB,
C_NUMERIC NUMERIC(20,5),
C_DP DOUBLE PRECISION
) SERVER gsmpp_server OPTIONS(format 'text', encoding 'SQL_ASCII', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_single_byte_encoding.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true');
SELECT * FROM FT_ASCII_TO_ASCII;
ALTER FOREIGN TABLE FT_ASCII_TO_ASCII OPTIONS(SET compatible_illegal_chars 'false');
SELECT * FROM FT_ASCII_TO_ASCII;
COPY ASCII_TO_ASCII FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_single_byte_encoding.data' (format 'text', encoding 'latin1', delimiter '|', compatible_illegal_chars 'true');
SELECT * FROM ASCII_TO_ASCII;
TRUNCATE ASCII_TO_ASCII;
COPY ASCII_TO_ASCII FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_single_byte_encoding.data' (format 'text', encoding 'latin1', delimiter '|', compatible_illegal_chars 'false');
DROP TABLE ASCII_TO_ASCII;
DROP FOREIGN TABLE FT_ASCII_TO_ASCII;
----(1.2.1.8)utf82eucjis2004
\c DB_EUCJIS2004_BULKLOAD_COMPATIBILITY_TEST;
SHOW server_encoding;
CREATE TABLE utf82eucjis2004_test(a varchar(8));
COPY utf82eucjis2004_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82eucjis2004_1.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'false');
COPY utf82eucjis2004_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82eucjis2004_1.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'true');
COPY utf82eucjis2004_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82eucjis2004_2.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'false');
COPY utf82eucjis2004_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82eucjis2004_2.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'true');
COPY utf82eucjis2004_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82eucjis2004_3.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'false');
COPY utf82eucjis2004_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82eucjis2004_3.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'true');
COPY utf82eucjis2004_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82eucjis2004_4.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'false');
COPY utf82eucjis2004_test FROM '@abs_srcdir@/data/datanode1/bulkload_compatible_illegal_chars_test_data/invalid_char_for_just_char_type_utf82eucjis2004_4.data' (format 'text', encoding 'utf8', delimiter '|', compatible_illegal_chars 'true');
SELECT COUNT(*) FROM utf82eucjis2004_test;
DROP TABLE utf82eucjis2004_test;
\c regression;
----(1.2.2)for csv format
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE (
COL1_INT BIGINT NOT NULL,
COL2_CHAR CHAR(100) NOT NULL,
COL3_VARCHAR VARCHAR(100) NOT NULL,
COL4_VARCHAR2 VARCHAR2(100) NOT NULL,
COL5_RAW RAW NOT NULL,
COL6_NVARCHAR2 NVARCHAR2(100) NOT NULL,
COL7_CLOB CLOB NOT NULL,
COL8_TEXT TEXT NOT NULL,
COL9_TIMESTAMP TIMESTAMP NOT NULL
)SERVER gsmpp_server OPTIONS(format 'csv', location 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_mixed_type_csv_format.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true', reject_limit 'unlimited')with err_cic_test;
SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
INSERT INTO COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
SELECT COUNT(*) FROM err_cic_test;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_MIXED_TYPE;
----(1.2.3)for fixed format
----(2)exception test
CREATE TABLE COMPATIBLE_ILLEGAL_CHARS_TEST_EXCEPTION
(
A VARCHAR(100),
B VARCHAR(100)
);
CREATE FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_EXCEPTION
(
A VARCHAR(100),
B VARCHAR(100)
)SERVER gsmpp_server OPTIONS (LOCATION 'gsfs://127.0.0.1:8900/bulkload_compatible_illegal_chars_test_data/invalid_char_for_execption.data',FORMAT 'CSV',MODE 'Normal',COMPATIBLE_ILLEGAL_CHARS 'TRUE',ENCODING 'UTF8')with err_cic_test PER NODE REJECT LIMIT '1000';
INSERT INTO COMPATIBLE_ILLEGAL_CHARS_TEST_EXCEPTION SELECT * FROM EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_EXCEPTION;
SELECT COUNT(*) FROM err_cic_test;
SELECT COUNT(*) FROM COMPATIBLE_ILLEGAL_CHARS_TEST_EXCEPTION;
DROP FOREIGN TABLE EXT_COMPATIBLE_ILLEGAL_CHARS_TEST_EXCEPTION;
DROP TABLE COMPATIBLE_ILLEGAL_CHARS_TEST_EXCEPTION;
---
--- bulkload datetime format test
---
CREATE TABLE DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
);
--- (1)invalid datetime format test
--- (1.1)invalid date format test
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid.data', format 'text', mode 'normal', date_format 'YEAR-MM-DD', time_format 'HH24:MI:SS', timestamp_format 'YYYY-MM-DD HH24:MI:SS', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS');
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_1.data' (format 'text', delimiter '|', time_format 'HH24:MI:SS', timestamp_format 'YYYY-MM-DD HH24:MI:SS', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS');
--- (1.2)invalid time format test
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid.data', format 'text', mode 'normal', date_format 'YYYY-MM-DD', time_format 'HH23:MI:SS', timestamp_format 'YYYY-MM-DD HH24:MI:SS', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS');
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_1.data' (format 'text', delimiter '|', date_format 'YYYY-MM-DD', time_format 'HH23:MI:SS', timestamp_format 'YYYY-MM-DD HH24:MI:SS', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS');
--- (1.3)invalid timestamp format test
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid.data', format 'text', mode 'normal', date_format 'YYYY-MM-DD', time_format 'HH24:MI:SS', timestamp_format 'YYYY-MM-DD HH24:MIN:SS', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS');
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_1.data' (format 'text', delimiter '|', date_format 'YYYY-MM-DD', time_format 'HH24:MI:SS', timestamp_format 'YYYY-MM-DD HH24:MIN:SS', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS');
--- (1.4)invalid smalldatetime format test
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid.data', format 'text', mode 'normal', date_format 'YYYY-MM-DD', time_format 'HH24:MI:SS', timestamp_format 'YYYY-MM-DD HH24:MI:SS', smalldatetime_format 'YYYY-MM-DD HH24:MI:SEC');
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_1.data' (format 'text', delimiter '|', date_format 'YYYY-MM-DD', time_format 'HH24:MI:SS', timestamp_format 'YYYY-MM-DD HH24:MI:SS', smalldatetime_format 'YYYY-MM-DD HH24:MI:SEC');
--- (1.5)datetime format isn't allowed for exporting test
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid.data', format 'text', mode 'normal', date_format 'YYYY-MM-DD')WRITE ONLY;
COPY DATETIME_TEST TO '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_out.data' (format 'text', delimiter '|', date_format 'YYYY-MM-DD');
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid.data', format 'text', mode 'normal', time_format 'HH24:MI:SS')WRITE ONLY;
COPY DATETIME_TEST TO '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_out.data' (format 'text', delimiter '|', time_format 'HH24:MI:SS');
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid.data', format 'text', mode 'normal', timestamp_format 'YYYY-MM-DD HH24:MI:SS')WRITE ONLY;
COPY DATETIME_TEST TO '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_out.data' (format 'text', delimiter '|', timestamp_format 'YYYY-MM-DD HH24:MI:SS');
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid.data', format 'text', mode 'normal', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS')WRITE ONLY;
COPY DATETIME_TEST TO '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_out.data' (format 'text', delimiter '|', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS');
--- (2)valid datetime format test
SHOW NLS_TIMESTAMP_FORMAT;
SHOW DATESTYLE;
--- (2.1)for sql_compatibility = A_FORMAT
--- (2.1.1)no bulkload datetime format specified
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid_format_1.data', format 'text', delimiter '|', mode 'normal')WITH err_dt_format_table;
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_1.data' (format 'text', delimiter '|');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
--- (2.1.2)bulkload datetime format 1 test
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(ADD date_format 'YYYY-MM-DD', time_format 'HH24:MI:SS', ignore_extra_data 'true', timestamp_format 'YYYY-MM-DD HH24:MI:SS', noescaping 'true', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS', compatible_illegal_chars 'false');
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_1.data' (format 'text', delimiter '|', date_format 'YYYY-MM-DD', time_format 'HH24:MI:SS', ignore_extra_data 'true', timestamp_format 'YYYY-MM-DD HH24:MI:SS', noescaping 'true', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS', compatible_illegal_chars 'false');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
--- (2.1.3)bulkload datetime format 2 test
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(SET location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid_format_2.data');
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(SET date_format 'YYYYMMDD', SET time_format 'HHMISS', SET timestamp_format 'YYYYMMDDHHMISS', SET smalldatetime_format 'YYYYMMDDHH24MISS');
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(DROP date_format, DROP time_format, DROP timestamp_format, DROP smalldatetime_format);
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_2.data' (format 'text', delimiter '|', date_format 'YYYYMMDD', time_format 'HHMISS', ignore_extra_data 'true', timestamp_format 'YYYYMMDDHHMISS', noescaping 'true', smalldatetime_format 'YYYYMMDDHH24MISS', compatible_illegal_chars 'false');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
TRUNCATE DATETIME_TEST;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_2.data' (format 'text', delimiter '|');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
--- (2.1.4)bulkload datetime format mixed with TO_DATE()/TO_TIMESTAMP() test
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT TO_DATE(COL_DATE, 'YYYYMMDD'), COL_TIME, TO_TIMESTAMP(COL_TIMESTAMP, 'YYYYMMDDHHMISS'), COL_SMALLDATETIME FROM FT_DATETIME_TEST;
--- (2.1.5)bulkload datetime format 3 test
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(SET location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid_format_3.data');
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(ADD date_format 'DD/MM/YYYY', time_format 'HHMISS', timestamp_format 'DD/MM/YYYYHHMISS', smalldatetime_format 'DD/MM/YYYYHHMISS');
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(DROP date_format, DROP time_format, DROP timestamp_format, DROP smalldatetime_format);
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_3.data' (format 'text', delimiter '|', date_format 'DD/MM/YYYY', time_format 'HHMISS', ignore_extra_data 'true', timestamp_format 'DD/MM/YYYYHHMISS', noescaping 'true', smalldatetime_format 'DD/MM/YYYYHH24MISS', compatible_illegal_chars 'false');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
TRUNCATE DATETIME_TEST;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_3.data' (format 'text', delimiter '|');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
--- (2.1.6)bulkload datetime format 4 test
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(SET location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid_format_4.data');
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(ADD date_format 'MONTH DD, YYYY AD', time_format 'HHMISS', timestamp_format 'MONTH DD, YYYY ADHHMISS', smalldatetime_format 'MONTH DD, YYYY ADHHMISS');
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(DROP date_format, DROP time_format, DROP timestamp_format, DROP smalldatetime_format);
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
DROP FOREIGN TABLE FT_DATETIME_TEST;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_4.data' (format 'text', delimiter '|', date_format 'FXMONTH DD, YYYY AD', time_format 'HHMISS', ignore_extra_data 'true', timestamp_format 'FXMONTH DD, YYYY ADHHMISS', noescaping 'true', smalldatetime_format 'FXMONTH DD, YYYY ADHH24MISS', compatible_illegal_chars 'false');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
TRUNCATE DATETIME_TEST;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_4.data' (format 'text', delimiter '|');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
DROP TABLE DATETIME_TEST;
--- (2.2)for sql_compatibility = C_FORMAT
--- (2.2.1)no bulkload datetime format specified
\c TD_FORMAT_DB;
CREATE TABLE DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
);
CREATE FOREIGN TABLE FT_DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
)
server gsmpp_server options
(location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid_format_1.data', format 'text', delimiter '|', mode 'normal')WITH err_dt_format_table;
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_1.data' (format 'text', delimiter '|');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
--- (2.2.2)bulkload datetime format 1 test
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(ADD date_format 'YYYY-MM-DD', time_format 'HH24:MI:SS', timestamp_format 'YYYY-MM-DD HH24:MI:SS', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS');
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_1.data' (format 'text', delimiter '|', date_format 'YYYY-MM-DD', time_format 'HH24:MI:SS', ignore_extra_data 'true', timestamp_format 'YYYY-MM-DD HH24:MI:SS', noescaping 'true', smalldatetime_format 'YYYY-MM-DD HH24:MI:SS', compatible_illegal_chars 'false');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
--- (2.2.3)bulkload datetime format 2 test
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(SET location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid_format_2.data');
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(SET date_format 'YYYYMMDD', SET time_format 'HHMISS', SET timestamp_format 'YYYYMMDDHHMISS', SET smalldatetime_format 'YYYYMMDDHH24MISS');
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(DROP date_format, DROP time_format, DROP timestamp_format, DROP smalldatetime_format);
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_2.data' (format 'text', delimiter '|', date_format 'YYYYMMDD', time_format 'HHMISS', ignore_extra_data 'true', timestamp_format 'YYYYMMDDHHMISS', noescaping 'true', smalldatetime_format 'YYYYMMDDHH24MISS', compatible_illegal_chars 'false');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
TRUNCATE DATETIME_TEST;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_2.data' (format 'text', delimiter '|');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
--- (2.2.4)bulkload datetime format mixed with TO_DATE()/TO_TIMESTAMP() test
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT TO_DATE(COL_DATE, 'YYYYMMDD'), COL_TIME, TO_TIMESTAMP(COL_TIMESTAMP, 'YYYYMMDDHHMISS'), COL_SMALLDATETIME FROM FT_DATETIME_TEST;
--- (2.2.5)bulkload datetime format 3 test
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(SET location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid_format_3.data');
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(ADD date_format 'DD/MM/YYYY', time_format 'HHMISS', timestamp_format 'DD/MM/YYYYHHMISS', smalldatetime_format 'DD/MM/YYYYHH24MISS');
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(DROP date_format, DROP time_format, DROP timestamp_format, DROP smalldatetime_format);
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_3.data' (format 'text', delimiter '|', date_format 'DD/MM/YYYY', time_format 'HHMISS', ignore_extra_data 'true', timestamp_format 'DD/MM/YYYYHHMISS', noescaping 'true', smalldatetime_format 'DD/MM/YYYYHH24MISS', compatible_illegal_chars 'false');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
TRUNCATE DATETIME_TEST;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_3.data' (format 'text', delimiter '|');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
--- (2.2.6)bulkload datetime format 4 test
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(SET location 'gsfs://127.0.0.1:8900/bulkload_datetime_format_test_data/datetime_valid_format_4.data');
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(ADD date_format 'FXMONTH DD, YYYY AD', time_format 'HHMISS', timestamp_format 'FXMONTH DD, YYYY ADHHMISS', smalldatetime_format 'FXMONTH DD, YYYY ADHH24MISS');
TRUNCATE DATETIME_TEST;
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
ALTER FOREIGN TABLE FT_DATETIME_TEST OPTIONS(DROP date_format, DROP time_format, DROP timestamp_format, DROP smalldatetime_format);
INSERT INTO DATETIME_TEST SELECT * FROM FT_DATETIME_TEST;
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
SELECT COUNT(*) FROM err_dt_format_table;
TRUNCATE DATETIME_TEST;
TRUNCATE err_dt_format_table;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_4.data' (format 'text', delimiter '|', date_format 'MONTH DD, YYYY AD', time_format 'HHMISS', ignore_extra_data 'true', timestamp_format 'MONTH DD, YYYY ADHHMISS', noescaping 'true', smalldatetime_format 'MONTH DD, YYYY ADHH24MISS', compatible_illegal_chars 'false');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
TRUNCATE DATETIME_TEST;
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_4.data' (format 'text', delimiter '|');
SELECT * FROM DATETIME_TEST ORDER BY COL_DATE;
TRUNCATE DATETIME_TEST;
DROP TABLE DATETIME_TEST;
DROP FOREIGN TABLE FT_DATETIME_TEST;
\c regression;
---
-- bulkload compatible illegal chars test
---
--- create foreign table
CREATE FOREIGN TABLE EXT_LINEITEM_COMPATIBLE_ILLEGAL_CHARS_NORMAL (
L_ORDERKEY BIGINT NOT NULL,
L_PARTKEY BIGINT NOT NULL,
L_SUPPKEY BIGINT NOT NULL,
L_LINENUMBER BIGINT NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL
)SERVER gsmpp_server OPTIONS(format 'text', location 'gsfs://127.0.0.1:8900/lineitem.data', delimiter '|', mode 'normal', compatible_illegal_chars 'false', compatible_illegal_chars 'true');
CREATE FOREIGN TABLE EXT_LINEITEM_COMPATIBLE_ILLEGAL_CHARS_NORMAL (
L_ORDERKEY BIGINT NOT NULL,
L_PARTKEY BIGINT NOT NULL,
L_SUPPKEY BIGINT NOT NULL,
L_LINENUMBER BIGINT NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL
)SERVER gsmpp_server OPTIONS(format 'text', location 'gsfs://127.0.0.1:8900/lineitem.data', delimiter '|', mode 'normal', compatible_illegal_chars 'true');
SELECT ftoptions FROM pg_foreign_table WHERE ftrelid = (select oid from pg_class where relname = 'ext_lineitem_compatible_illegal_chars_normal');
--- alter foreign table
ALTER FOREIGN TABLE EXT_LINEITEM_COMPATIBLE_ILLEGAL_CHARS_NORMAL OPTIONS(DROP compatible_illegal_chars);
SELECT ftoptions FROM pg_foreign_table WHERE ftrelid = (select oid from pg_class where relname = 'ext_lineitem_compatible_illegal_chars_normal');
ALTER FOREIGN TABLE EXT_LINEITEM_COMPATIBLE_ILLEGAL_CHARS_NORMAL OPTIONS(ADD compatible_illegal_chars 'false', compatible_illegal_chars 'true');
ALTER FOREIGN TABLE EXT_LINEITEM_COMPATIBLE_ILLEGAL_CHARS_NORMAL OPTIONS(ADD compatible_illegal_chars 'false');
SELECT ftoptions FROM pg_foreign_table WHERE ftrelid = (select oid from pg_class where relname = 'ext_lineitem_compatible_illegal_chars_normal');
ALTER FOREIGN TABLE EXT_LINEITEM_COMPATIBLE_ILLEGAL_CHARS_NORMAL OPTIONS(SET compatible_illegal_chars 'true');
SELECT ftoptions FROM pg_foreign_table WHERE ftrelid = (select oid from pg_class where relname = 'ext_lineitem_compatible_illegal_chars_normal');
--- drop foreign tables
DROP FOREIGN TABLE EXT_LINEITEM_COMPATIBLE_ILLEGAL_CHARS_NORMAL;
--- foreign table options conflict
CREATE FOREIGN TABLE EXT_TABLE_CONFLICT (
A INT
)SERVER gsmpp_server OPTIONS(format 'text', location 'gsfs://127.0.0.1:8900/conflict.data', null ' ', mode 'normal', compatible_illegal_chars 'true');
CREATE FOREIGN TABLE EXT_TABLE_CONFLICT (
A INT
)SERVER gsmpp_server OPTIONS(format 'text', location 'gsfs://127.0.0.1:8900/conflict.data', null '?', mode 'normal', compatible_illegal_chars 'true');
CREATE FOREIGN TABLE EXT_TABLE_CONFLICT (
A INT
)SERVER gsmpp_server OPTIONS(format 'text', location 'gsfs://127.0.0.1:8900/conflict.data', delimiter ' ', mode 'normal', compatible_illegal_chars 'true');
CREATE FOREIGN TABLE EXT_TABLE_CONFLICT (
A INT
)SERVER gsmpp_server OPTIONS(format 'text', location 'gsfs://127.0.0.1:8900/conflict.data', delimiter '?', mode 'normal', compatible_illegal_chars 'true');
CREATE FOREIGN TABLE EXT_TABLE_CONFLICT (
A INT
)SERVER gsmpp_server OPTIONS(format 'csv', location 'gsfs://127.0.0.1:8900/conflict.data', quote ' ', mode 'normal', compatible_illegal_chars 'true');
CREATE FOREIGN TABLE EXT_TABLE_CONFLICT (
A INT
)SERVER gsmpp_server OPTIONS(format 'csv', location 'gsfs://127.0.0.1:8900/conflict.data', quote '?', mode 'normal', compatible_illegal_chars 'true');
CREATE FOREIGN TABLE EXT_TABLE_CONFLICT (
A INT
)SERVER gsmpp_server OPTIONS(format 'csv', location 'gsfs://127.0.0.1:8900/conflict.data', escape ' ', mode 'normal', compatible_illegal_chars 'true');
CREATE FOREIGN TABLE EXT_TABLE_CONFLICT (
A INT
)SERVER gsmpp_server OPTIONS(format 'csv', location 'gsfs://127.0.0.1:8900/conflict.data', escape '?', mode 'normal', compatible_illegal_chars 'true');
---
--- bulkload compatibility options incompatible in binary mode
---
CREATE TABLE DATETIME_TEST(
COL_DATE DATE,
COL_TIME TIME,
COL_TIMESTAMP TIMESTAMP,
COL_SMALLDATETIME SMALLDATETIME
);
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_3.data' (format 'binary', compatible_illegal_chars 'true');
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_3.data' (format 'binary', date_format 'DD/MM/YYYY');
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_3.data' (format 'binary', time_format 'HHMISS');
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_3.data' (format 'binary', timestamp_format 'DD/MM/YYYYHHMISS');
COPY DATETIME_TEST FROM '@abs_srcdir@/data/datanode1/bulkload_datetime_format_test_data/datetime_valid_format_3.data' (format 'binary', smalldatetime_format 'DD/MM/YYYYHH24MISS');