Interactive SQL (dbisql) and event bug on exception handling

[former_SQLA_member1694875]

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:(

[former_SQLA_member1694878]

Looks like this is a statement caching issue. I can reproduce it using JDBC, although it does require the garbage collector to run at the right time, and the server to decide to cache the statement.

If you need a workaround SET TEMPORARY OPTION max_client_statements_cached=0 should cause the (correct) "Table not found" error to occur every time.

[Breck_Carter]

If this is a server issue rather than a dbisql issue, the implications are huge: statement caching causes incorrect behavior, and should be turned off for all databases.

Your thoughts?

[VolkerBarth]

@Mikel: As Arthoor's tests have shown, the problem does appear for event code, too, so probably the same caching is done for events. Under what category of the following - quoted from here - do they fall?

The setting of this option applies to connections made using embedded SQL, ODBC, OLE DB, ADO.NET, and the SQL Anywhere JDBC driver. It does not apply to Sybase Open Client, jConnect, or HTTP connections.

(Possibly I'm just not used to thinking of events as "client code"...)

[VolkerBarth]

I'm even more puzzled that my tests with DBISQL and dbisqlc with the same user on the same (SA 12 test) database have revealed different results - I certainly have not changed that option in-between... - so if it's a server-side issue, why would the client matter, as long as the client API does make use of that caching? Here it would apply to both AFAIK (ESQL for dbisqlc, the SA JDBC driver for DBISQL)... Strange.

[former_SQLA_member1694878]

@Breck It should be noted that the sever will keep track of situations where statement caching causes behaviour changes and react appropriately. For example, while repeatedly executing DELETE FROM #temp_foo will cache the statement, if you create a temp table with that name (CREATE TABLE #temp_foo (c1 int)) the server will drop it's cached statement and re-prepare the DELETE FROM #temp_foo on next execution.

The resulting SQL code does change as a result of the statement caching, but it could be argued that the 'Invalid statement' is more accurate (we aren't actually checking if the temp table is there). I'm not sure in this case if the invalid statement is acceptable.

[former_SQLA_member1694878]

@Volker Probably has to do with the statements that dbisql executes in the background (for text auto-complete, etc.) changing the servers statement caching behaviour. The server will start or stop caching statements for a connection depending on the hit/miss rate. (Also FWIW, I'm able to get the 07W02 using ESQL)

[former_SQLA_member1694875]

@Mikel: max_client_statements_cached=0 does work in dbisql, but does NOT in events, i. e. I can't see any differences in my event tests.

[former_SQLA_member1694875]

Well, the deeper I test the more strange results I get. Now I encapsulated these blocks (five this time) into an event:

IF EXISTS(select 1 from sys.sysevent where event_name = 'ev_test_exceptions') THEN
    DROP EVENT "ev_test_exceptions"
END IF;

CREATE EVENT "ev_test_exceptions"
SCHEDULE "every_10_seconds" START TIME '00:00' EVERY 10 SECONDS
HANDLER
BEGIN
    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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                RESIGNAL;
    END;
END;


And these are the results in log file:
I. 05/23 09:06:50. Table not found error
I. 05/23 09:06:50. Table not found error
I. 05/23 09:06:50. Table not found error
I. 05/23 09:06:50. Table not found error
I. 05/23 09:06:50. Table not found error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
...


I. e. when the event runs first time it gives correct results for all blocks, and when it runs every next time it gives unexpected results. After event recreation it runs correctly again first time.
If I move all the code from event to procedure and only call that procedure in the event then it seems to work correctly.
This time it is not related with dbisql. Not sure if this case should be asked as separate question since these cases must be somehow related (for now I've just edited question header and body to include event)...

[VolkerBarth]

Hm, the obvious solution would be to make sure the desired table does exist🙂

[former_SQLA_member1694875]

Is there another way to check if temporary table exists? 🙂

The background:
The original problem was declared here. Then I've made a workaround - I've moved temporary table creation from lower level procedure to a one step higher level procedure. But that lower level procedure sometimes can be called directly so it must be checked if temporary table exists like in these code blocks above. So after my modifications this "Invalid statement" problem arised. The exact real problem is someway different (I could not reproduce it yet) but with the same symptom - it gives "Invalid statement" error at some point after running for a long time when calling DELETE statement.

[VolkerBarth]

Is the problem "just two different error codes for the same symptom"? - I.e. if you handle both errors the same way (probably by creating the missing tempoary table), will the code work? Or do you notice a different behaviour due to the different error codes?

Is there another way to check if temporary table exists?

Well, you could as well try to SELECT (instead DELETE) from that table... - but AFAIK you have to try to access a temporary table to know of its existence, by design the system catalog won't give any hints...

[former_SQLA_member1694875]

Is the problem "just two different error codes for the same symptom"?

No. The exact real problem in customer's production DB (which I still can't reproduce in my tests) is quite different. The DELETE statement fails with "Invalid statement" error when that temporary table actually does exist (or at least should exist). I hope that symptom in the event that I've written above is related with that real problem and somebody from SAP will give a hint how to workaround that and I'll not be forced to reproduce exactly the same problem. 🙂

you could as well try to SELECT (instead DELETE) from that table

The DELETE statement is needed in that case as I need to clean the table if it already exists. So additional SELECT statement before DELETE statement would be redundant.

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