Why does sql query give more results than expected?

David_P_1David_P_1 Registered User

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?

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file