Issue Details (XML | Word | Printable)

Key: CORE-3303
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Paul Vinkenoog
Votes: 0
Watchers: 4

If you were logged in you would be able to see more operations.
Firebird Core

RDB$VALID_BLR unreliable

Created: 08/Jan/11 01:51 PM   Updated: 03/Feb/11 12:51 PM
Component/s: Engine
Affects Version/s: 2.1.3, 2.5.0
Fix Version/s: None

 Description  « Hide
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);


SQL> alter domain dd type varchar(12);
SQL> commit;
SQL> select rdb$valid_blr from rdb$procedures where rdb$procedure_name = 'DDTEST';


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.)

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dimitry Sibiryakov added a comment - 08/Jan/11 02:12 PM
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.

Sean Leyne added a comment - 08/Jan/11 06:37 PM

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.

Dmitry Yemanov added a comment - 08/Jan/11 07:45 PM
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.

Dimitry Sibiryakov added a comment - 10/Jan/11 12:30 PM - edited
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 CORE-3305.

Oracle had no choice as they have non-transactional DDL. Firebird do have transactional DDL.

Dmitry Yemanov added a comment - 02/Feb/11 09:33 AM
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.

Paul Vinkenoog added a comment - 03/Feb/11 12:51 PM
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:

    alter procedure ddtest (a varchar(12)) returns (b varchar(12)) as begin b = a * a; end

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),
the BLR flag does become 0 and you can't execute the procedure anymore. So there's also a discrepancy between + and * here.