IN predicate with string-type elements is evaluated wrongly against a numeric field

[edsongregorio]

--TEST TABLE AND REGISTERS CREATE TABLE TABLEX ( ID INTEGER NOT NULL PRIMARY KEY, TEXT VARCHAR(30) );

INSERT INTO TABLEX (ID, TEXT) VALUES (1, 'REG 1'); INSERT INTO TABLEX (ID, TEXT) VALUES (2, 'REG 2'); INSERT INTO TABLEX (ID, TEXT) VALUES (3, 'REG 3'); INSERT INTO TABLEX (ID, TEXT) VALUES (11, 'REG 11'); INSERT INTO TABLEX (ID, TEXT) VALUES (12, 'REG 12'); INSERT INTO TABLEX (ID, TEXT) VALUES (13, 'REG 13');

--WORKS WELL select from TABLEX r where r.ID in ('1','12') select from TABLEX r where r.ID in (2,12) select * from TABLEX r where r.ID in ('02','12')

--WRONG RESULT SET select * from TABLEX r where r.ID in ('2','12')

[fperana]

I've also experienced this behaviour and found a sort of pattern.

Consider this table:

create table test (intfld integer);

with this data:

insert into test (intfld) values (50);
insert into test (intfld) values (200);
insert into test (intfld) values (600);

This query correctly returns the record with value 50:

select * from test where intfld in ('50');

While this one returns no rows:

select * from test where intfld in ('50','200');

If the shorter (less characters) number's first (non-zero) character is <= than the bigger's one, it works again:

select * from test where intfld in ('50','500');
select * from test where intfld in ('50','600'); (this returns 2 rows as expected)
select * from test where intfld in ('50','5000');
select * from test where intfld in ('50','4000'); -> NO! because 4 < 5

Left-padding the shorter number with one or more zeroes or spaces would make the query work again:

select * from test where intfld in ('050','200'); (2 rows)
or
select * from test where intfld in (' 50','200'); (2 rows)

And if also the longer number is padded with zeroes or spaces, the shorter number needs to have at least one padding char more than the longer number:

select * from test where intfld in ('0050','0200');
select * from test where intfld in ('00050','0200');
select * from test where intfld in ('050','0200'); -> NO! because both numbers are padded with one zero

This seems related to some issue with string comparison.

BTW, this makes me think that firebird is converting each row's numeric field values to string before comparing them to the given string values. Wouldn't it be faster to convert the given (fixed) values to numbers and then comparing them to each row's values, without converting them row by row?