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 !
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
