COPY, CSV, NUMBER(1024,512) : Change in Behavior / Bug v11 -> v12 ?
mark_d_drake
Community Edition User ✭
Simple contrived test case.
Given the following data files
y:\stagingArea\db\vertica\t1.csv "1" y:\stagingArea\db\vertica\t2.csv "1.0" y:\stagingArea\db\vertica\t3.csv 1 C:\Development\YADAMU>
Running the following script:
select version();
--
drop schema "test";
--
drop table "reject_t1";
--
drop table "reject_t2";
--
drop table "reject_t3";
--
create schema "test";
--
create table "test"."t1" ("x" number(1024,512));
--
copy "test"."t1" ("x") from '/mnt/shared/stagingArea/db/vertica/t1.csv' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "reject_t1" NO COMMIT;
--
select * from "test"."t1";
--
select * from "reject_t1";
--
select count(*) from "test"."t1" where "x" is null;
--
copy "test"."t1" ("x") from '/mnt/shared/stagingArea/db/vertica/t2.csv' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "reject_t2" NO COMMIT;
--
select * from "test"."t1";
--
select * from "reject_t2";
--
select count(*) from "test"."t1" where "x" is null;
--
copy "test"."t1" ("x") from '/mnt/shared/stagingArea/db/vertica/t3.csv' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "reject_t3" NO COMMIT;
--
select * from "test"."t1";
--
select * from "reject_t3";
--
select count(*) from "test"."t1" where "x" is null;
--
-5
This discussion has been closed.
Answers
With Vertica 11 results in the following output:
VMART=> select version(); version ------------------------------------- Vertica Analytic Database v11.1.1-0 (1 row) VMART=> -- VMART=> drop schema "test"; DROP SCHEMA VMART=> -- VMART=> drop table "reject_t1"; ROLLBACK 4876: Table "reject_t1" does not exist VMART=> -- VMART=> drop table "reject_t2"; ROLLBACK 4876: Table "reject_t2" does not exist VMART=> -- VMART=> drop table "reject_t3"; ROLLBACK 4876: Table "reject_t3" does not exist VMART=> -- VMART=> create schema "test"; CREATE SCHEMA VMART=> -- VMART=> create table "test"."t1" ("x" number(1024,512)); CREATE TABLE VMART=> -- VMART=> copy "test"."t1" ("x") from '/mnt/shared/stagingArea/db/vertica/t1.csv' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "reject_t1" NO COMMIT; NOTICE 5795: COPY is not going to commit. Creating a TEMPORARY rejection table, which will be dropped at end-of-session Rows Loaded ------------- 1 (1 row) VMART=> -- VMART=> select * from "test"."t1"; x ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (1 row) VMART=> -- VMART=> select * from "reject_t1"; node_name | file_name | session_id | transaction_id | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length | rejected_reason -----------+-----------+------------+----------------+--------------+--------------+------------+---------------+---------------------------+----------------- (0 rows) VMART=> -- VMART=> select count(*) from "test"."t1" where "x" is null; count ------- 0 (1 row) VMART=> -- VMART=> copy "test"."t1" ("x") from '/mnt/shared/stagingArea/db/vertica/t2.csv' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "reject_t2" NO COMMIT; NOTICE 5795: COPY is not going to commit. Creating a TEMPORARY rejection table, which will be dropped at end-of-session Rows Loaded ------------- 1 (1 row) VMART=> -- VMART=> select * from "test"."t1"; x ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 1.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (2 rows) VMART=> -- VMART=> select * from "reject_t2"; node_name | file_name | session_id | transaction_id | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length | rejected_reason -----------+-----------+------------+----------------+--------------+--------------+------------+---------------+---------------------------+----------------- (0 rows) VMART=> -- VMART=> select count(*) from "test"."t1" where "x" is null; count ------- 0 (1 row) VMART=> -- VMART=> copy "test"."t1" ("x") from '/mnt/shared/stagingArea/db/vertica/t3.csv' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "reject_t3" NO COMMIT; NOTICE 5795: COPY is not going to commit. Creating a TEMPORARY rejection table, which will be dropped at end-of-session Rows Loaded ------------- 1 (1 row)VMART=> -- VMART=> select * from "test"."t1"; x ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 1.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 1.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (3 rows) VMART=> -- VMART=> select * from "reject_t3"; node_name | file_name | session_id | transaction_id | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length | rejected_reason -----------+-----------+------------+----------------+--------------+--------------+------------+---------------+---------------------------+----------------- (0 rows) VMART=> -- VMART=> select count(*) from "test"."t1" where "x" is null; count ------- 0 (1 row) VMART=>But in Vertica 12 it results in
VMART=> select version(); version ------------------------------------- Vertica Analytic Database v12.0.3-0 (1 row) VMART=> -- VMART=> drop schema "test"; NOTICE 4927: The Table t1 depends on Schema test ROLLBACK 3128: DROP failed due to dependencies DETAIL: Cannot drop Schema test because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too VMART=> -- VMART=> drop table "reject_t1"; ROLLBACK 4876: Table "reject_t1" does not exist VMART=> -- VMART=> drop table "reject_t2"; ROLLBACK 4876: Table "reject_t2" does not exist VMART=> -- VMART=> drop table "reject_t3"; ROLLBACK 4876: Table "reject_t3" does not exist VMART=> -- VMART=> create schema "test"; ROLLBACK 4213: Object "test" already exists VMART=> -- VMART=> create table "test"."t1" ("x" number(1024,512)); ROLLBACK 4213: Object "t1" already exists VMART=> -- VMART=> copy "test"."t1" ("x") from '/mnt/shared/stagingArea/db/vertica/t1.csv' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "reject_t1" NO COMMIT; NOTICE 5795: COPY is not going to commit. Creating a TEMPORARY rejection table, which will be dropped at end-of-session Rows Loaded ------------- 1 (1 row) VMART=> -- VMART=> select * from "test"."t1"; x --- (1 row) VMART=> -- VMART=> select * from "reject_t1"; node_name | file_name | session_id | transaction_id | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length | rejected_reason -----------+-----------+------------+----------------+--------------+--------------+------------+---------------+---------------------------+----------------- (0 rows) VMART=> -- VMART=> select count(*) from "test"."t1" where "x" is null; count ------- 1 (1 row) VMART=> -- VMART=> copy "test"."t1" ("x") from '/mnt/shared/stagingArea/db/vertica/t2.csv' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "reject_t2" NO COMMIT; NOTICE 5795: COPY is not going to commit. Creating a TEMPORARY rejection table, which will be dropped at end-of-session Rows Loaded ------------- 1 (1 row) VMART=> -- VMART=> select * from "test"."t1"; x --- (2 rows) VMART=> -- VMART=> select * from "reject_t2"; node_name | file_name | session_id | transaction_id | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length | rejected_reason -----------+-----------+------------+----------------+--------------+--------------+------------+---------------+---------------------------+----------------- (0 rows) VMART=> -- VMART=> select count(*) from "test"."t1" where "x" is null; count ------- 2 (1 row) VMART=> -- VMART=> copy "test"."t1" ("x") from '/mnt/shared/stagingArea/db/vertica/t3.csv' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "reject_t3" NO COMMIT; NOTICE 5795: COPY is not going to commit. Creating a TEMPORARY rejection table, which will be dropped at end-of-session Rows Loaded ------------- 1 (1 row) VMART=> -- VMART=> select * from "test"."t1"; x --- (3 rows) VMART=> -- VMART=> select * from "reject_t3"; node_name | file_name | session_id | transaction_id | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length | rejected_reason -----------+-----------+------------+----------------+--------------+--------------+------------+---------------+---------------------------+----------------- (0 rows) VMART=> -- VMART=> select count(*) from "test"."t1" where "x" is null; count ------- 3 (1 row) VMART=> -- VMART=>In version 11 the column contains data, in version 12 the column is NULL. IMHO the output from version 11 is correct.
Simple test case and workaround...
New testcase
select version(); -- drop schema "test" cascade; -- create schema "test"; -- create table "test"."t1" ("x" number(1024,512)); -- copy "test"."t1" ("x") from STDIN PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' NO COMMIT; "1.0" "2.0" \. -- select * from "test"."t1"; -- drop table "test"."t1"; -- create table "test"."t1" ("x" varchar(1024)); -- copy "test"."t1" ("x") from STDIN PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' NO COMMIT; "1.0" "2.0" \. -- select * from "test"."t1"; -- drop table "test"."t1"; -- create table "test"."t1" ("x" number(1024,512),"y" varchar(1024),"z" number(1024,512)); -- delete from "test"."t1"; -- copy "test"."t1" ("x","y",NUMBER_VARCHAR FILLER VARCHAR(1026), "z" as CAST(NUMBER_VARCHAR as number(1024,512))) from STDIN PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' NO COMMIT; "1.0","1.1","1.2" "2.0","2.1","2.2" \. -- select * from "test"."t1"; --Running in Vertica 10 I get the following
C:\Development\YADAMU>vsql -Udbadmin -hyadamu-db2 -dVMart -p5434 Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See vsql reference page "Notes for Windows users" for details. VMart=> select version(); version ------------------------------------- Vertica Analytic Database v10.1.1-0 (1 row) VMart=> -- VMart=> drop schema "test" cascade; DROP SCHEMA VMart=> -- VMart=> create schema "test"; CREATE SCHEMA VMart=> -- VMart=> create table "test"."t1" ("x" number(1024,512)); CREATE TABLE VMart=> -- VMart=> copy "test"."t1" ("x") from STDIN VMart-> PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' NO COMMIT; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. . >> "1.0" . >> "2.0" . >> \. VMart=> -- VMart=> select * from "test"."t1"; x ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 2.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (2 rows) VMart=> -- VMart=> drop table "test"."t1"; DROP TABLE VMart=> -- VMart=> create table "test"."t1" ("x" varchar(1024)); CREATE TABLE VMart=> -- VMart=> copy "test"."t1" ("x") from STDIN VMart-> PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' NO COMMIT; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. .>> "1.0" .>> "2.0" .>> \. VMart=> -- VMart=> select * from "test"."t1"; x ----- 1.0 2.0 (2 rows) VMart=> -- VMart=> drop table "test"."t1"; DROP TABLE VMart=> -- VMart=> create table "test"."t1" ("x" number(1024,512),"y" varchar(1024),"z" number(1024,512)); CREATE TABLE VMart=> -- VMart=> delete from "test"."t1"; OUTPUT -------- 0 (1 row) VMart=> -- VMart=> copy "test"."t1" ("x","y",NUMBER_VARCHAR FILLER VARCHAR(1026), "z" as CAST(NUMBER_VARCHAR as number(1024,512))) from STDIN VMart-> PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' NO COMMIT; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. .>> "1.0","1.1","1.2" .>> "2.0","2.1","2.2" .>> \. VMart=> -- VMart=> select * from "test"."t1"; x | y | z ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 1.1 | 1.20000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 2.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 2.1 | 2.20000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (2 rows) VMart=> -- VMart=>And with Vertica 12
```
C:\Development\YADAMU>vsql -Udbadmin -hyadamu-db2 -dVMart
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters may not work correctly. See vsql reference
page "Notes for Windows users" for details.
VMart=> select version();
version
Vertica Analytic Database v12.0.3-0
(1 row)
VMart=> --
VMart=> drop schema "test" cascade;
DROP SCHEMA
VMart=> --
VMart=> create schema "test";
CREATE SCHEMA
VMart=> --
VMart=> create table "test"."t1" ("x" number(1024,512));
CREATE TABLE
VMart=> --
VMart=> copy "test"."t1" ("x") from STDIN
VMart-> PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' NO COMMIT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
.>> "1.0"
.>> "2.0"
.>> .
VMart=> --
VMart=> select * from "test"."t1";
x
(2 rows)
VMart=> --
VMart=> drop table "test"."t1";
DROP TABLE
VMart=> --
VMart=> create table "test"."t1" ("x" varchar(1024));
CREATE TABLE
VMart=> --
VMart=> copy "test"."t1" ("x") from STDIN
VMart-> PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' NO COMMIT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
.>> "1.0"
.>> "2.0"
.>> .
VMart=> --
VMart=> select * from "test"."t1";
x
1.0
2.0
(2 rows)
VMart=> --
VMart=> drop table "test"."t1";
DROP TABLE
VMart=> --
VMart=> create table "test"."t1" ("x" number(1024,512),"y" varchar(1024),"z" number(1024,512));
CREATE TABLE
VMart=> --
VMart=> delete from "test"."t1";
OUTPUT
(1 row)
VMart=> --
VMart=> copy "test"."t1" ("x","y",NUMBER_VARCHAR FILLER VARCHAR(1026), "z" as CAST(NUMBER_VARCHAR as number(1024,512))) from STDIN
VMart-> PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' NO COMMIT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
.>> "1.0","1.1","1.2"
.>> "2.0","2.1","2.2"
.>> .
VMart=> --
VMart=> select * from "test"."t1";
x | y |
z
---+-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1.1 | 1.20000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
| 2.1 | 2.20000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(2 rows)
VMart=> --
VMart=> exit