COPY, CSV, NUMBER(1024,512) : Change in Behavior / Bug v11 -> v12 ?
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:
But in Vertica 12 it results in
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
Running in Vertica 10 I get the following
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