New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
RDB$VALID_BLR unreliable [CORE3303] #3670
Comments
Commented by: @aafemt And even if RDB$VALID_BLR is reset to 0, I wonder why Firebird allows transactions that bring database into invalid state?.. IMHO, all invalid objects must become valid on commit or commit must fail. |
Commented by: Sean Leyne (seanleyne) Dimitry, IIRC, the idea is that instead of rejecting the change, dependent objects would be placed into an "invalid/unknown" state so that they could be re-validated at a later time. That way a change could be made to an object without having to drop/delete all of the dependent objects, first. This would significantly reduce the "pain" of schema changes. Again, IIRC, Oracle uses this approach for schema changes. |
Commented by: @dyemanov It's worth testing this against FB 3.0. I remember fixing a few anomalies related to the RDB$VALID_BLR handling and I'm not sure there were backported. |
Commented by: @aafemt I believe that "later time" should be commit time. Oracle behavior is weird as it put off error discovery to execution time. If somebody has made invalid procedure that is used once a year, it will take much time to find out what is wrong and who is responsible. Another example - if someone has made invalid trigger on often used table, work of all users will be sabotaged at once. Exactly what we see in CORE3305. Oracle had no choice as they have non-transactional DDL. Firebird do have transactional DDL. |
Commented by: @dyemanov Well, I have to admit this issue is far from being trivial ;-) RDB$VALID_BLR is quite correctly remains TRUE, because the procedure is still valid. Try reconnect after altering the domain and you'll see both queries working. It looks like the procedure request was adjusted to the new domain definition but the DSQL cache still has no clue about the change. As a result, computation succeeds and the resulting value is VARCHAR, but the message to return the value to the client side is still described as INT, so the data type conversion happens. If you're wondering why we have an exception here, this query explains the situation, as it throws the same error: select cast('64.00000000' as int) from rdb$database because the value is internally to be converted to both an unscaled INT and its corresponding scale, and 6400000000 overflows the 32-bit signed integer limits. If your test case would be changed to use VARCHAR(11) instead of VARCHAR(12), everything would work. So in fact we have two different issues here: (1) some mis-synchronization of the metadata cache regarding the DDL changes, and (2) somewhat weird string->int conversion rules. But none of them makes the procedure invalid per se. |
Commented by: @paulvink You're right, without reconnecting I still get an INT when executing the procedure. With argument 1 it even works, with 2 or higher it raises the error. After reconnecting, the procedure works "fully", i.e. it works as long as you enter numerical arguments or string arguments that represent numerals. But this is not OK. After all, changing the domain to varchar(12) is the same (or should be the same) as altering the procedure like this:
which gives you an "expression evaluation not supported" error. And there's another thing: if you define the procedure with "b = a + a" instead of "b = a * a", and then change the domain to varchar(n), |
Submitted by: @paulvink
RDB$VALID_BLR sometimes stays 1 after a change that makes the code invalid.
Test case:
SQL> set auto on;
SQL> create domain dd int;
SQL> set term #;
SQL> create procedure ddtest (a dd) returns (b dd) as begin b = a * a; end#
SQL> set term ;#
SQL> execute procedure ddtest (8);
============
64
SQL> alter domain dd type varchar(12);
SQL> commit;
SQL> select rdb$valid_blr from rdb$procedures where rdb$procedure_name = 'DDTEST';
RDB$VALID_BLR
SQL> execute procedure ddtest (8);
Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range
SQL> execute procedure ddtest ('8');
Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range
(Funny thing: if I execute the last two statements in FlameRobin, both return a string(12) with value '64.00000000'. No error is raised.)
The text was updated successfully, but these errors were encountered: