Impossible index-based retrievals after NUMERIC/DECIMAL column's scale is decreased

[dyemanov]

Initially published in firebird-devel. Test case:

create table t_scale (col1 numeric(5, 2), col2 numeric(5, 2));
create index t_scale_i1 on t_scale(col1);
create index t_scale_i2 on t_scale(col2);
commit;

insert into t_scale values (1.23, 1.23);
commit;

select * from t_scale;

        COL1         COL2
============ ============
        1.23         1.23

select * from t_scale where col1 = 1.23;

        COL1         COL2
============ ============
        1.23         1.23

select * from t_scale where col2 = 1.23;

        COL1         COL2
============ ============
        1.23         1.23

alter table t_scale alter col1 type numeric(6, 3);
alter table t_scale alter col2 type numeric(5, 1);
commit;

select * from t_scale;

        COL1         COL2
============ ============
       1.230          1.2

select * from t_scale where col1 = 1.23;

        COL1         COL2
============ ============
       1.230          1.2

select * from t_scale where col1 = 1.230;

        COL1         COL2
============ ============
       1.230          1.2

-- so far so good

select * from t_scale where col2 = 1.2;

-- NOTHING!

select * from t_scale where col2 = 1.23;

-- ALSO NOTHING!

We cannot access the existing value via the index anymore. This happens because (even after index rebuild) the stored key belongs to the old format, i.e. 1.23. It cannot be matched while searching for 1.2. It is matched while searching for 1.23, but then the record is read, its stored value 1.23 (old format) is converted into 1.2 (new format) and then re-compared with 1.23 thus being false again.

[AlexPeshkoff]

Pay attention - NUMERIC(5, 2) is backed not by BIGINT, it's just INT. select * from t_scale; ... 01: sqltype: 496 LONG Nullable scale: -2 subtype: 1 len: 4 ...

[dyemanov]

Yeah, sorry, fixed the title.