Question

COPY, CSV, NUMBER(1024,512) : Change in Behavior / Bug v11 -> v12 ?

Forum|Forum|3 years ago
March 8, 2023
5 replies
2 views

mark_d_drake
Participating Frequently

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;
--

mark_d_drake
Author
Participating Frequently
Forum|Forum|3 years ago
March 8, 2023

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)

mark_d_drake
Author
Participating Frequently
Forum|Forum|3 years ago
March 8, 2023

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.

mark_d_drake
Author
Participating Frequently
Forum|Forum|3 years ago
March 18, 2023

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";
--

mark_d_drake
Author
Participating Frequently
Forum|Forum|3 years ago
March 18, 2023

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=>

mark_d_drake
Author
Participating Frequently
Forum|Forum|3 years ago
March 18, 2023

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

version

x

x

OUTPUT

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register: