type coerce weirdness
phil2
✭
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 ?
0
Answers
According to the doc:
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:
Fyi...Typically Vertica can use implicit conversion when doing arithmetic between a VARCHAR and INT. But not always as expected.
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?
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.