FDELIMITEDPARSER() BUG?

avi120

Hi Vertica Experts,

Is the following a bug? Is there a workaround for it?

STEP1
create file called a.csv in tmp folder with the following 3 lines:
c1,c2
2.8121163E8,1
200,2

STEP2
CREATE TABLE t1(c1 NUMERIC(18,2), c2 INT);

STEP 3
COPY t1 FROM '/tmp/a.csv' parser FDELIMITEDPARSER() DELIMITER ',' ABORT ON ERROR;

STEP 4
select * from t1;

vertica=> select * from t1;
c1 | c2
--------+----
| 1
200.00 | 2
(2 rows)

The above works if I use float instead of NUMERIC(18,2)

Bryan_H

We'll look into this. What version of Vertica is this?
The quickest workaround is not to specify a parser, as the default delimited parser works correctly here:
dbadmin=> CREATE TABLE forumt1(c1 NUMERIC(18,2), c2 INT);
CREATE TABLE
dbadmin=> copy forumt1 from local stdin delimiter ',' abort on error;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
2.1e8,123
123,45
\.
dbadmin=> select * from forumt1;
c1 | c2
--------------+-----
123.00 | 456
210000000.00 | 123
(2 rows)

marcothesane

No. This is not a bug.
2.8121163E8,1 is a Float literal. Mantissa and Exponent representation always is.

That format just calls for Vertica's default parser. Try this:

CREATE TABLE t1(
  c1 NUMERIC(18,2)
, c2 INT
);

COPY t1(
  c1_in FILLER FLOAT
, c1 AS c1_in::NUMERIC(18,2)
, c2
) FROM LOCAL 'a.csv'  DELIMITER ',' ;

SELECT * FROM t1;
-- out ROLLBACK 4213:  Object "t1" already exists
-- out TRUNCATE TABLE
-- out Time: First fetch (0 rows): 209.318 ms. All rows formatted: 209.625 ms
-- out  Rows Loaded 
-- out -------------
-- out            2
-- out (1 row)
-- out 
-- out Time: First fetch (1 row): 1712.051 ms. All rows formatted: 1712.862 ms
-- out       c1      | c2 
-- out --------------+----
-- out        200.00 |  2
-- out  281211630.00 |  1

mosheg

With the fdelimitedparser parser the parameter reject_on_materialized_type_error is false.
If you will change it to true the parser will reject any row value for a materialized column that the parser cannot coerce into a compatible data type.

As you mentioned the value 2.8121163E8 requires NUMERIC(19,2) instead of NUMERIC(18,2)
And if you will add ABORT ON ERROR at the end of the COPY statement, the parser will shout:
vsql:test.sql:8: ERROR 2035: COPY: Input record 1 has been rejected (Row [2] rejected due to materialized type error on column: [c1] with value: [2.8121163E8].)
See: https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/FlexTables/FDELIMITEDparser.htm

Here is an example:

cat a.csv
c1,c2
2.8121163E8,1
200,2
2.8121163E7,3


vsql -f test.sql
CREATE TABLE t1(c1 NUMERIC(18,2) , c2 INT);
COPY t1 (c1, c2)
FROM '/home/dbadmin/GILAT/a.csv'
parser fdelimitedparser (delimiter=',' , header=true , reject_on_materialized_type_error=true );
select * from t1;

     c1      | c2
-------------+----
 28121163.00 |  3
      200.00 |  2
(2 rows)