06.05.2014 12:35, Dimitry Sibiryakov wrote:
>> Decreasing the scale is allowed and should work fine, AFAIU.
>
> Shouldn't it be contrary?.. If in table is value 1.234 and scale is changed
> from 3 to
> 2, this value will be displayed as 1.23, but cannot be found by eq. Vice
> versa it works:
> value 1.23 will be able to be found even if scale changed from 2 to 3.
Nice catch. Increasing *only* the scale is prohibited, as it decreases
the integral part of the number and thus can potentially render the
table unusable. Increasing *both* the precision and the scale is allowed
and expected to work fine, you're absolutely correct here.
However, decreasing the scale shows us an expected surprise:
SQL> create table q (col numeric(7, 3));
SQL> create index iq on q (col);
SQL> insert into q values (1.234);
SQL> commit;
SQL> select * from q;
COL
============
1.234
SQL> select * from q where col = 1.234;
COL
============
1.234
SQL> alter table q alter col type numeric(7, 2);
SQL> select * from q;
COL
============
1.23
SQL> select * from q where col = 1.23;
SQL> select * from q where col = 1.234;
Oops. We allow such changes and the index lookup fails. But note that it
has nothing to do with int64, numeric(7, 2) is backed by a 32-bit
integer, so the index key is plain double.
It seems that decreasing the scale must be always prohibited. One may
argue that it should be so only for indexed columns. But look here:
SQL> alter table q alter col type numeric(10, 5);
SQL> select * from q; -- PLAN NATURAL
COL
============
1.23400
Isn't it funny that 1.23 suddenly became 1.234?
Maybe I'm missing something obvious?
Dmitry
------------------------------------------------------------------------------
Is your legacy SCM system holding you back? Join Perforce May 7 to find out:
• 3 signs your SCM is hindering your productivity
• Requirements for releasing software faster
• Expert tips and advice for migrating your SCM now
http://p.sf.net/sfu/perforce
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
