15.02.2022 14:41, Dimitry Sibiryakov wrote:
Is "stored keys independent of the declared scale" a really useful
and used feature?
That's a separate but also good question. Double precision keys allow
independence of both precision (within its range) and scale. AFAIU, the
idea was to allow changing column data types between
FLOAT/DOUBLE/SMALLINT/INT and between different NUMERIC/DECIMAL
precisions/scales without rebuilding the index (i.e. keys in the old and
new formats may co-exist).
But then surprises start to appear.
First of all, it does not work this way. We rebuild an index every time
a column's data type is changed, regardless of the old/new precision and
scale. Even if the field is altered to the same data type. And AFAIK,
it's not something we changed in Firebird, the original IB6 code already
worked this way.
Second, co-existence of keys in the old and new formats is also buggy
now, see below.
recreate 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! WTF?
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.
I can think of two ways to proceed:
1) Keep rebuilding the index every time the type is changed. Cast all
column values into the new format before storing keys into the index.
Forget about being scale-independent, pick the scale from the latest
table format (and store BIGINTs or all exact numerics as integers
without scale, as you suggest).
2) Prohibit decreasing the scale. Avoid rebuilding the indices unless
really necessary (idx_itype is changed). Consider it a good thing (tm).
Maybe there may be other options, I didn't think deeply about this.
Dmitry
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
