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.

[Breck_Carter]

Please doublecheck your testing method... I can't repeat your results with 16.0.0.1691 or 12.0.1.3298

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;
INSERT INTO _tmp_t (t_time) VALUES (CURRENT TIMESTAMP);
COMMIT;
SELECT 'first', * FROM _tmp_t;

WAITFOR DELAY '00:00:01';

SELECT _sp_update_tmp_t();
COMMIT;

SELECT 'second', * FROM _tmp_t;

'first'        t_id t_time                  
------- ----------- ----------------------- 
first             1 2014-01-16 09:06:18.803

_sp_update_tmp_t() 
------------------ 
                 1

'second'        t_id t_time                  
-------- ----------- ----------------------- 
second             1 2014-01-16 09:06:19.896 

[former_SQLA_member1694875]

Testing in Your way, my result is different than yours. The code:

DROP TABLE IF EXISTS _tmp_t;
CREATE TABLE _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;

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

DROP TABLE IF EXISTS #tmp;
DECLARE LOCAL TEMPORARY TABLE #tmp (t_number INTEGER, t_time TIMESTAMP) NOT TRANSACTIONAL;

INSERT INTO #tmp (t_number, t_time) SELECT 1, t_time FROM _tmp_t;

WAITFOR DELAY '00:00:05';

SELECT _sp_update_tmp_t();
COMMIT;

INSERT INTO #tmp (t_number, t_time) SELECT 2, t_time FROM _tmp_t;

SELECT * from #tmp ORDER BY t_number

The result:
t_number,t_time
1,'2014.01.16 17:09:40.546'
2,'2014.01.16 17:09:40.546'

Tried in newly created 16.0.0.1691 database.

[Breck_Carter]

I give up... also in a fresh 16.0.0.1691, your exact code ends with this:

t_number,t_time
1,'2014-01-16 12:21:14.613'
2,'2014-01-16 12:21:19.680'

What OS are you running on? I am running on Windoze 7.

[former_SQLA_member1694875]

Windows XP x64 SP2 (SA16 and SA12), Windows 8 (SA11).