Unexpected ISQL behavior (calling a function and commiting)

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.

asked 16 Jan '14, 06:59

Arthoor
1.3k●35●52●66
accept rate: 11%

edited 20 Jan '14, 03:19

Volker Barth
39.3k●354●538●808

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?

(16 Jan '14, 09:19) Volker Barth

Replies hidden

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

(16 Jan '14, 09:29) Arthoor

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.

(16 Jan '14, 10:03) Mikel Rychliski

Replies hidden

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

(16 Jan '14, 10:18) Volker Barth

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

(16 Jan '14, 11:18) Mikel Rychliski

flat view

5 Answers:

active answers oldest answers newest answers popular answers

That seems to be an issue of materialization . . .

I'm betting you are running DBISQL configured to not return multiple result sets, and not to show each result set (if running as a batch). When DBISQL runs that way it

prepares the query using the function,
describes the statement
opens and closes a cursor
and drops the statement

it just fine! BUT Never fetches from it.

Without that fetch, the first row does not materialize and that can result in no call to the function. No call, no updates ... or other side-effects.

So if this is your case, you can configure Tools >> Options >> SQL Anywhere >> Results Processing to "Show results from each statment" and "Show all results" ... and then it should behave the way you are probably expecting.

Let us know if that changes the behaviour issue in your setting.

permanent link

answered 17 Jan '14, 18:28

Nick Elson S...
7.3k●35●107
accept rate: 32%

edited 17 Jan '14, 18:31

Yes, I'm running DBISQL with default (at least for our platforms) settings (Show results from the last statement and Show only the first result set). When I change them to the values you said, the behavior becomes as expected - different times are being showed (the function call DOES work).

(20 Jan '14, 01:26) Arthoor

Well, that's why I still prefer dbisqlc for such tests - DBISQL seems too smart sometimes, i.e. too different from normal database client applications:)

(20 Jan '14, 02:46) Volker Barth

flat view

Despite the fact that Mike (here) was able to recreate the behaviour, I too, like Breck, could not.

I've tried this both ways on 12.0.1 and 16 ... and get different times each time ... The latest script Arthoor provided returns this result (from my latest run): t_number,t_time 1, '2014-01-16 12:18:05.252' 2, '2014-01-16 12:18:10.322'

So that would only seem to leave some machine specific behaviour or some option setting.

permanent link

answered 16 Jan '14, 12:23

Nick Elson S...
7.3k●35●107
accept rate: 32%

Maybe it's CURRENT TIMESTAMP getting stuck?

I was gonna ask about snapshot isolation but that's just crazy, given all the test scripts.

Or... perhaps... Nick and I share the same malady :)

(16 Jan '14, 13:10) Breck Carter

Comment Text Removed

alt text

(16 Jan '14, 13:21) Breck Carter

Replies hidden

Just an issue of unfitting options - at least it doesn't fall into the CNR category:)

(20 Jan '14, 10:09) Volker Barth

flat view

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

permanent link

answered 16 Jan '14, 09:02

Breck Carter
32.5k●539●724●1050
accept rate: 20%

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.

(16 Jan '14, 10:06) Arthoor

Replies hidden

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.

(16 Jan '14, 12:14) Breck Carter

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

(17 Jan '14, 01:10) Arthoor

flat view

Actually. One can recreate this if you start with a newly created database using dbinit (not the create database wizard in SCJView).

It seems unlikely this will be experienced by many customers (given a number of observed factors) and you are likley going to get the server to behave correctly after restarting with the database after the creation of the UDF and table ... but it is still odd ... and we are looking into it more now ...

permanent link

answered 16 Jan '14, 15:00

Nick Elson S...
7.3k●35●107
accept rate: 32%

I used dbinit, and it did NOT occur.

(16 Jan '14, 15:10) Breck Carter

Replies hidden

Did you autostart the database file?

(16 Jan '14, 15:21) Mikel Rychliski

No .........

(16 Jan '14, 15:55) Breck Carter

I used Create Database Wizard in Sybase Central. I also restarted the service. And I still can reproduce that behavior (same times).

(17 Jan '14, 01:08) Arthoor

Replies hidden

Can you put a MESSAGE statement in the function to display what CURRENT TIMESTAMP displayed? (in case it is returning the same value, thus causing the symptom).

(17 Jan '14, 10:02) Breck Carter

The MESSAGE statement is NOT called as the whole function is NOT. But when I change DBISQL settings about Results processing, it starts to work as expected (see my comment below Nick's answer).

(20 Jan '14, 01:29) Arthoor

The moral of the story:

In case of unexpected behaviour with your stored function/procedure/trigger/event , use a MESSAGE statement within the body to make sure your code is called at all...

(20 Jan '14, 03:18) Volker Barth

showing 2 of 7 show all flat view

0	If dbisql is configured to display multiple result sets you won't see this behaviour (mostly for Breck's benefit) ... still investigating ... permanent link answered 17 Jan '14, 16:11 Nick Elson S... 7.3k●35●107 accept rate: 32% flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-12 ×438
sa-16 ×258
sa-11 ×143
dbisql ×102
option ×15

question asked: 16 Jan '14, 06:59

question was seen: 3,793 times

last updated: 20 Jan '14, 10:10

SAP SQL Anywhere

Unexpected ISQL behavior (calling a function and commiting)

Follow this question

Related questions