Knowledge Document: Vertica numerical column casting issues

Environment

Vertica Analytics Database: v9.2

Situation

Certain numerical records fail the native filter expression. Casting the numerical field to varchar causes the filter expression to succeed.

Example:

SELECT count(1)
FROM table_Schema.table_name 
WHERE integer_column = 883339; -- it returns 1

SELECT count(1)
FROM table_Schema.table_name 
WHERE integer_column::varchar = '883339'; -- it returns 2

integer_column column is of integer data type storing integer data. Multiple values in this column would fail the numerical filter expression consistently. After checking the data integrity, no junk is found in this column. This issue cannot be reproduced to a different table if the data is copied.

Find complete article on support portal