Skip to content
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

Open
firebird-automations opened this issue Jan 8, 2011 · 6 comments
Open

RDB$VALID_BLR unreliable [CORE3303] #3670

firebird-automations opened this issue Jan 8, 2011 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

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

       B

============
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

        1

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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:

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant