type coerce weirdness

phil2 · August 2019

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 ?

Jim_Knicely · 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)

phil2 · August 2019

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_Knicely · 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.

type coerce weirdness

Answers

Leave a Comment