Unexpected ISQL behavior (calling a function and commiting)

[VolkerBarth]

Hello,

I've noticed strange behavior using Interactive SQL. I've made a sample script to reproduce the issue. Firstly, we have to create a table and a function that updates that table:

CREATE TABLE IF NOT EXISTS _tmp_t (
    t_id INTEGER PRIMARY KEY DEFAULT AUTOINCREMENT,
    t_time TIMESTAMP
);

CREATE OR REPLACE FUNCTION _sp_update_tmp_t()
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
    UPDATE _tmp_t SET t_time = CURRENT TIMESTAMP;
    RETURN 1;
END;

Then insert one record:

INSERT INTO _tmp_t (t_time) VALUES (CURRENT TIMESTAMP);
COMMIT;

Then check and remember the newly inserted value:

SELECT * FROM _tmp_t;

Next, try to update the value by calling the function that we created above:

SELECT _sp_update_tmp_t();
COMMIT;

Surprisingly (at least, to me), the value is still the same. But if I try to call the function without a COMMIT and then COMMIT separately, the value changes as expected. Also, the value changes if I remove the semicolon after the function call (before the COMMIT):

SELECT _sp_update_tmp_t()
COMMIT;

Can such behavior be explained somehow or is it a bug?

Server (and client): SA16 latest EBF (1691), the same behavior with SA12 and SA11 (not latest EBFs). Platform: Windows.

Thanks.

[VolkerBarth]

How do you check the table's contents? IMHO the "select * from _tmp_t" queries are missing in your test scenario...

You are using DBISQL, not dbisqlc, right?

[former_SQLA_member1694875]

I didn't write the same query again, but of course, I use the same simple query for checking table contents:
SELECT * FROM _tmp_t;
Yes, I'm using DBISQL (not DBISQLC).

[Former Member]

I can reproduce this. The lack of update appears to be a bug. It seems that removing the semi-colon causes the statements to be interpreted as a TSQL batch which exhibits the behaviour you're expecting.

[VolkerBarth]

May this be due to any DBISQL option with influence on the result set display (say, the auto_refetch option)?

[Former Member]

I've actually been able to reproduce the problem outside of Interactive SQL. I'll open a CR to get this fixed.

[Former Member]

If dbisql is configured to display multiple result sets you won't see this behaviour (mostly for Breck's benefit) ... still investigating ...