type coerce weirdness
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 ?