Interactive SQL (dbisql) and event bug on exception handling

[VolkerBarth]

I have this code with exception handling (three identical BEGIN-END blocks to reproduce the bug):

BEGIN
    DECLARE err_tablenotfound EXCEPTION FOR SQLSTATE '42W33';
    DECLARE err_invalidstatement EXCEPTION FOR SQLSTATE '07W02';
    DELETE FROM #tmp_foo;
    EXCEPTION
        WHEN err_tablenotfound THEN
            message 'Table not found error' to client;
        WHEN err_invalidstatement THEN
            message 'Invalid statement error' to client;
        WHEN OTHERS THEN
            message 'Another error' to client;
            RESIGNAL;
END;
BEGIN
    DECLARE err_tablenotfound EXCEPTION FOR SQLSTATE '42W33';
    DECLARE err_invalidstatement EXCEPTION FOR SQLSTATE '07W02';
    DELETE FROM #tmp_foo;
    EXCEPTION
        WHEN err_tablenotfound THEN
            message 'Table not found error' to client;
        WHEN err_invalidstatement THEN
            message 'Invalid statement error' to client;
        WHEN OTHERS THEN
            message 'Another error' to client;
            RESIGNAL;
END;
BEGIN
    DECLARE err_tablenotfound EXCEPTION FOR SQLSTATE '42W33';
    DECLARE err_invalidstatement EXCEPTION FOR SQLSTATE '07W02';
    DELETE FROM #tmp_foo;
    EXCEPTION
        WHEN err_tablenotfound THEN
            message 'Table not found error' to client;
        WHEN err_invalidstatement THEN
            message 'Invalid statement error' to client;
        WHEN OTHERS THEN
            message 'Another error' to client;
            RESIGNAL;
END;


I run it in dbisql. My results:
Table not found error
Execution time: 0.008 seconds
Table not found error
Execution time: 0.002 seconds
Invalid statement error
Execution time: 0.001 seconds


I. e. the 3rd block gives me unexpected results.
dbisqlc works as expected.
Can anyone repeat this behavior?
Tried all these client and server versions of SA: 11.0.1.3113, 12.0.1.4086, 16.0.0.1824.
Edited: similar problem exists in events, see my own answer below.

[VolkerBarth]

I've tried with 12.0.1.4085, and the results are similar:

The first and second block return "Table not found error", all other repetitions return "Invalid statement error". I.e. if I repeat all 3 three blocks, they always return "Invalid statement error".

It's reproducable: If I drop the connection and re-connect then the next two block repetitions return "Table not found error", and all following ones return the other error.

Don't know what that means:(

[Breck_Carter]

Try SET TEMPORARY OPTION ConsistentBehavior = 'On :)'

[VolkerBarth]

So that option does exist but has a different name:)

SET TEMPORARY OPTION max_client_statements_cached = 0

[Breck_Carter]

That is TOO funny... I thought you were being sarcastic until I read Mikel's answer! ...well done.

[VolkerBarth]

Hm, I guess I have been a little bit sarcastic (or at least ironic) here - but the implications are too huge, as you have already pointed out:(

[VolkerBarth]

Might be a DBISQL issue - a test with dbisqlc on 12.0.1.4085 returns the expected "Table not found" error for all repetitions.

Good ol' times...