Why does sql query give more results than expected?

David_P_1

create table public.test (
id varchar(255),
card_id varchar(255)
);

insert into test values (1, '90000000021981051');
insert into test values (2, '90000000021981058');
insert into test values (3, '9000000002198105');

select card_id
from test
where card_id=90000000021981051;

90000000021981051
90000000021981058

select card_id
from test
where card_id='90000000021981051';

90000000021981051

Does this make sense?

Jim_Knicely

Hmm. I personally never rely on implicit data conversion. I'd recommend an explicit data conversion...

dbadmin=> select card_id
dbadmin-> from test
dbadmin-> where card_id::INT=90000000021981051;
      card_id
-------------------
 90000000021981051
(1 row)

dbadmin=> select card_id
dbadmin-> from test
dbadmin-> where card_id=90000000021981051::VARCHAR;
      card_id
-------------------
 90000000021981051
(1 row)

Take a look at the "Data Type Coercion Chart":

https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/DataTypes/DataTypeCoercionChart.htm

Looks like Vertica is converting to a FLOAT, an "approximate" numeric type!

dbadmin=> SELECT 90000000021981058=90000000021981051::NUMERIC "Not True";
 Not True
----------
 f
(1 row)

dbadmin=> SELECT 90000000021981058=90000000021981051::FLOAT "True";
 True
------
 t
(1 row)