type coerce weirdness

phil2phil2
edited August 2019 in General Discussion

Hello!

I have accidently joined two tables with type mismatch on join key column (varchar in a first table and int in a second table). And vertica did not throw any error. But the result data set was incorrect because of type overflow during type cast. Vertica decided to cast both to float and join on float values. And because it lost few significant digits on the way the result is incorrect.

So I came up with a minimal example:

create table tmp_type_coerce 
(
    id identity, 
    ext varchar(64)
) 
order by ext 
segmented by hash(ext) all nodes;

insert into tmp_type_coerce(ext) values('2019012675695746446');
insert into tmp_type_coerce(ext) values('2019012675695746581');

select * from tmp_type_coerce where ext = 2019012675695746581;
6500002|2019012675695746581|
6500001|2019012675695746446|

Looks like a bug.
Is there an option to avoid implicit type cast from varchar to float ?

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited August 2019

    According to the doc:

    Implicit casts from INTEGER, FLOAT, and NUMERIC to VARCHAR are not supported. If you need that functionality, write an explicit cast

    See:
    https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/DataTypeCoercion.htm

    It's best practice to use explicit type casting when the data types do not match (i.e. are not in the same family).

    I would do this:

     dbadmin=> select * from tmp_type_coerce where ext = 2019012675695746581::VARCHAR(64);
      id |         ext
      ----+---------------------
       2 | 2019012675695746581
     (1 row)
    
    dbadmin=> select * from tmp_type_coerce where ext::INT = 2019012675695746581;
     id |         ext
    ----+---------------------
      2 | 2019012675695746581
    (1 row)
    

    Fyi...Typically Vertica can use implicit conversion when doing arithmetic between a VARCHAR and INT. But not always as expected.

    dbadmin=> SELECT '2019012675695746581' + 2019012675695746581 "Implicit conversion works as expected here...";
     Implicit conversion works as expected here...
    -----------------------------------------------
                               4038025351391493162
    (1 row)
    
    dbadmin=> CREATE VIEW test1_vw AS SELECT '2019012675695746581' + 2019012675695746581 "Implicit conversion works as expcted here...";SELECT '2019012675695746581' + 2019012675695746581 "Implicit conversion works as expected here...";
    CREATE VIEW
     Implicit conversion works as expected here...
    -----------------------------------------------
                               4038025351391493162
    (1 row)
    
    dbadmin=> \dv test1_vw
                                   List of View Fields
     Schema |   View   |                     Column                     | Type | Size
    --------+----------+------------------------------------------------+------+------
     public | test1_vw | "Implicit conversion works as expcted here..." | int  |    8
    (1 row)
    
    dbadmin=> SELECT 2019012675695746581::VARCHAR(64) + 2019012675695746581 "But not here...";
       But not here...
    ----------------------
     4.03802535139149e+18
    (1 row)
    
    dbadmin=> CREATE OR REPLACE VIEW test1_vw AS SELECT 2019012675695746581::VARCHAR(64) + 2019012675695746581 "But not here...";
    CREATE VIEW
    
    dbadmin=> \dv test1_vw
    
                     List of View Fields
     Schema |   View   |      Column       | Type  | Size
    --------+----------+-------------------+-------+------
     public | test1_vw | "But not here..." | float |    8
    (1 row)
    
  • Hello, Jim
    You suggested to use explicit cast to avoid glitches. I got it.
    But you cant always get what you want. You can mismatch or overlook type casting. Like I did today. May be there is a way to globally turn off this feature of implicit varchar to float casting?

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited August 2019

    I just found a JIRA that indicates that this may in fact be a bug!!! The title of the JIRA is "Implicit cast from varchar to numeric types lacks precision (wrong results)" and there is an example like yours.

    I will update the JIRA with a link to this thread.

Leave a Comment

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