May DBISQL skip SELECT statements in a SQL batch? - Part II

[VolkerBarth]

Based on Breck's helpful suggestion, I have tried to build a reproducible for that other FAQ:

First, create a fresh v12.0.1 database (I've used 12.0.1.4278) with default settings and launch DBISQL (or dbisqlc):

MD MyTestDb
CD MyTestDb
"%SQLANY12%\\bin64\\dbinit" MyTest.db
"%SQLANY12%\\bin64\\dbisql" -c "DBF=.\\MyTest;UID=DBA;PWD=sql"

Here's a small code snippet for a stored function that makes dummy entries to a dummy table and uses WAITFOR in order to make it look like "hard word" (likewise, the parameters and the used savepoint logic are just attempts to mimic the real function's inner workings):

drop table if exists dbo.MyTestTable;
create table dbo.MyTestTable
(
   pk            int not null      default autoincrement primary key,
   lastChanged   datetime not null default timestamp
);

create or replace function dbo.MyFctn(nPkTest int, nSecondsToWait int) returns int
not deterministic
begin
    declare bSuccess int;
    declare nResult int = -1;
    savepoint strSavepoint;
    message '';
    message 'MyFctn with pk ' || nPkTest || ' waiting for ' || nSecondsToWait || ' seconds...';

    -- limit timespan to one minute
    set nSecondsToWait = greater(nSecondsToWait, 0);
    set nSecondsToWait = lesser(nSecondsToWait, 60);
    set bSuccess = if nSecondsToWait > 0 then 1 else 0 end if;
    waitfor time dateadd(ss, nSecondsToWait, current time);

    -- dummy DML to make the function really not-deterministic
    insert dbo.MyTestTable values (default, default);

    if bSuccess <> 0 then
        release savepoint strSavepoint;
        set nResult = nSecondsToWait;
    else
        rollback to savepoint strSavepoint;
    end if;

    message 'MyFctn: Result = ' || nResult;

    return nResult;
end;

-- inital test call
select dbo.MyFctn(10, 2);

Here's a second script that is used for a (fake) test: dump the output of the table, make some function calls (with non-identical arguments) and dump again and calculate the elapsed time.

-- Test script as a bunch of SQL statements (no code blocks):

create variable tsStart timestamp;
create variable nDurance bigint;
create variable varSaVersion varchar(4);
create variable nFunctionCallsBefore int;
create variable nFunctionCallsAfter int;
set varSaVersion = 'SA' || left(@@version, 2);

message 'MyFctn - Test starts' to log;

-- Store state before test calls and dump table contents
set tsStart = current timestamp;
set nFunctionCallsBefore = (select count(*) from dbo.MyTestTable);

select * from MyTestTable order by 1;
output to 'LogTableBefore.txt' format text quote ''; 

-- Make several calls with different arguments
select dbo.MyFctn(0, 5);
select dbo.MyFctn(0, 6);
select dbo.MyFctn(0, 7);
select dbo.MyFctn(0, 8);
select dbo.MyFctn(0, 9);
select dbo.MyFctn(0, 10);

-- Store state after test calls and dump table contents
set nFunctionCallsAfter = (select count(*) from dbo.MyTestTable);

select * from MyTestTable order by 1;
output to 'LogTableAfter.txt' format text quote ''; 

-- Calculate delay and message results
set nDurance = datediff(ms, tsStart, current timestamp);
message 'MyFctn with ' || varSaVersion || ' - Durance ' || nDurance || ' ms.' to log;
message 'MyFctn with ' || varSaVersion || ' - Additional number of calls ' || nFunctionCallsAfter - nFunctionCallsBefore || '.' to log;
message 'MyFctn - Test finished' to log;

drop variable tsStart;
drop variable nDurance;
drop variable varSaVersion;
drop variable nFunctionCallsBefore;
drop variable nFunctionCallsAfter;

-- Show results from log
select msg_text, msg_time from sa_server_messages()
where msg_text like 'MyFctn%' order by msg_id;

When running that script, DBISQL will only make the function calls when the following option (on the left) is set to "each statement":

That's what the result of the console log will look like:

In contrast, in case the left option is set to "Show results from the last statement", the functions (i.e. the statements "select dbo.MyFctn(0, 5);" and the following lines) do not get called, as the console output clearly shows:

I would certainly not expect that "not showing a result set" would mean "not calling that statement", particularly as v12 has deprecated the "CALL myFctn" syntax...

[VolkerBarth]

Just to add: Whereas DBISQL seems to behave on an "all or nothing" rule when calling these functions, dbisqlc seems somewhat non-deterministic:

When I replace the function calls by a longer list (say, with 20 calls) like in the following snippet, dbisqlc just issues the first n of them:

-- Make several calls with different arguments
select dbo.MyFctn(0, 1);
select dbo.MyFctn(0, 2);
select dbo.MyFctn(0, 3);
select dbo.MyFctn(0, 4);
select dbo.MyFctn(0, 5);
select dbo.MyFctn(0, 6);
select dbo.MyFctn(0, 7);
select dbo.MyFctn(0, 8);
select dbo.MyFctn(0, 9);
select dbo.MyFctn(0, 10);
select dbo.MyFctn(0, 11);
select dbo.MyFctn(0, 12);
select dbo.MyFctn(0, 13);
select dbo.MyFctn(0, 14);
select dbo.MyFctn(0, 15);
select dbo.MyFctn(0, 16);
select dbo.MyFctn(0, 17);
select dbo.MyFctn(0, 18);
select dbo.MyFctn(0, 19);
select dbo.MyFctn(0, 20);

Here, from the 20 calls, just the first 12 are issued, for whatever reasons. When I repeat the test, only 5 or 10 calls are made, as if there were some random influence...(FWIW, I don't know whether dbisqlc makes use of DBISQL's "Show results of last/each statement" option.)

[VolkerBarth]

FWIW, the obvious solution to that problem is to replace the "SELECT function-call" statements with "SET MyVar = function-call" statements that do not return a result set.

Nevertheless, the current behaviour seems irritating to me. With an older 12.0.1.3726 server, dbisqlc behaves as expected, i.e. without skipping SELECT statements...

[former_SQLA_member1694940]

I think I can shed some light on this.

When DBISQL 12 runs a statement, it executes it (sends it to the server for compilation and execution), and then it reads the results back (if any). When you execute a batch of statements, DBISQL executes every statement. Whether it reads the results or not is controlled by the option "Show results from the last statement" / "Show results from each statement". If you select the former (which is the default), the results from the last statement are read, but results from the other statements are not read.

For some SELECT statements, the database defers some of the computation until the client actually asks for the results. The rationale is that if the client don't ask for the results, they must not matter, and the server can save itself some work.

In the posted code above, the client is invoking the function MyFctn with a SELECT statement. Suppose DBISQL is configured to show results from only the last statement in a batch. The first SELECT statement will be executed, but its results will not be fetched. The server has determined that it can defer the work for that SELECT statement until the first row is fetched, but DBISQL never fetches that row, so the body of the function is not executed. If you configure DBISQL to show results from every statement, fetching the results will cause the function to be executed.

If the function had been executed using a CALL statement, then it is executed unconditionally.

Consider this code:

create or replace function test( value int ) returns int
begin
    message 'test( ' || value || ' )';
    return 123;
end;

message '-------------';
select test(1);
select test(2);
select test(3);

call test(4);
call test(5);
call test(6);

If you set DBISQL to show only the last result set, you will see the following messages in the database server window:

-------------
test(4)
test(5)
test(6)

Because DBISQL did not fetch the results, the SELECT statements are essentially noops. The three messages were from the CALL statements.

If you set DBISQL to show results from all statements, you will see this:

-------------
test(1)
test(2)
test(3)
test(4)
test(5)
test(6)

This behavior has been simplified in DBISQL 17. It fetches all results from all statements. I think that gives you the results you had initially expected.

[VolkerBarth]

@Chris, thanks for the explanation (and the much simpler sample!) - that really helps to understand the background.

For further clarification: So that "I do not need to execute the function's body until the results are requested" optimization is a feature of the database engine and not of DBISQL? (But why would dbisqlc behave differently here: Does it initially read the results and then decides itself to skip the following ones - as the engine apparently executes some initial function calls?)

---

Nevertheless, I still feel the option in DBISQL is not correctly worded in connection with the server's optimization here - from a user's point, "not executing a function's body" and "skipping the statement altogether" are not that different.

Aside: Starting with v12, the CALL syntax is deprecated for stored functions, so the preferred workaround would be to use "SET myVar = test();". That's what I have now done to make the script run independent of DBISQL's settings.

[former_SQLA_member1694868]

I have found that when I have multiple select statements that call functions that return results, I have to use a begin / end and select the results into a variable to get them all to execute.

Your "SET myVar = test();" becomes my:

BEGIN
declare myVar long varchar;
select test(1) into myVar where Column1 = 1;
select test(2) into myVar where Column2 = 2;
select test(3) into myVar where Column1 = 3;
END

I had always suspected it was because DBISQL didn't have a way to handle the multiple result sets, but I never dug into it. Once I had this as the answer I used it and moved on.

Note that I'm not storing myVar and I don't care to return its results. For my case I just needed it to be sure each procedure call executed.