[Firebird-devel] arithmetic exception, numeric overflow, or string truncation after change column datatype and select from older format view

[Alexandre B. S. <ib...@th...>]

Hi !

On previous versions of FB (2.1 and earlier) one could not change the 
column datatype if it has any dependencies (for example used on a view), 
you need to drop all the views and after that change the column datatype 
and recreate everything.

Fortunatelly, on FB 2.5 we could change the datatype. wich reduce a lot 
the needed work. But I found that it could lead to a bad surprise... I 
will not call it a bug, but, at least it's a misbehaviour, and the final 
result is worst than the greater ammount of work needed to be done on 
previous versions. I think it's a bad because you don't know that it 
would lead to an error until it's too late...

Take a look at the following ISQL session:


C:\bd>\Fb25\bin\isql.exe
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database "foo" user "sysdba" password "masterkey";
SQL> show database;
Database: foo
         Owner: SYSDBA
PAGE_SIZE 4096
Number of DB pages allocated = 196
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 1
Transaction - oldest active = 2
Transaction - oldest snapshot = 2
Transaction - Next = 5
ODS = 11.2
Default Character set: NONE
SQL>
SQL> create table t(a char(1));
SQL> commit;
SQL>
SQL> insert into t values ('a');
SQL> commit;
SQL>
SQL> select * from t;

A
======
a

SQL> commit;
SQL> create view v as select a from t;
SQL> commit;
SQL>
SQL> select * from v;

A
======
a

SQL>
SQL> alter table t alter column a type char(2);
SQL> commit;
SQL>
SQL> select * from t;

A
======
a

SQL> select * from v;

A
======
a

SQL>
SQL> update t set a = 'aa';
SQL> commit;
SQL>
SQL> select * from t;

A
======
aa

SQL>
SQL> select * from v;

A
======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL>


As you can see.. there is no error until the records are inserted or 
updated with a value that the length is greater than the previous 
size... In a way or another, the view stores the datatype (record format 
?), and if a string is greater than that, the error is trown.. IIRC the 
same occurs with computed columns (did not made a test case to check it out)

To make the things worse... The error is the one I consider most 
cryptic, since you don't know the column name, the value and wich record 
is the culprit.. a very annoying one to get on production...

The same error can be reproduced on FB 3.0.

What do you think about it ?

see you !