Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

FDELIMITEDPARSER() BUG?

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)

Answers

  • Bryan_HBryan_H Administrator

    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)

  • marcothesanemarcothesane Administrator

    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
    
  • moshegmosheg Administrator

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.