The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

Hi,
I've recently found a very strange behavior on SQL Anywhere versions 11.0.1.2867 and 12.0.1.3769.
Below is a sample script to prepare for reproduction of the situation.

// 1. Create table and procedure and fill table with values.
begin
    declare @id integer;

    if exists(select 1 from systable where table_name = '_test_nulls') then
        drop table _test_nulls
    end if;

    create table _test_nulls (
        id integer primary key default autoincrement,
        code varchar(60) null
    );

    create unique index idx_code ON _test_nulls(code);

    if exists(select 1 from sysprocedure where proc_name = '_test_nulls') then
        drop procedure _test_nulls
    end if;

    create procedure _test_nulls()
    result(id integer)
    begin
        declare @id integer;
        declare @code varchar(60);
        select min(id) into @id from _test_nulls where code = @code;
        select @id /* if this row is ommitted (and the row above is without 'into @id')
                      then we have different behavior but still incorrect */
    end;

    insert into _test_nulls (code) values (null);
    insert into _test_nulls (code) values (null);
    insert into _test_nulls (code) values (null);
    /* if this last row is ommitted
       then we have different behavior but still incorrect */
    commit;
end

Now we want to select data from procedure.

// 2. A few queries from the procedure that was created above.
begin
    declare @id integer;
    select id into @id from _test_nulls();
    message string('1. ', if @id is null then 'NULL' else 'NOT NULL' endif) to client;
    select id into @id from _test_nulls();
    message string('2. ', if @id is null then 'NULL' else 'NOT NULL' endif) to client;
    select id into @id from _test_nulls();
    message string('3. ', if @id is null then 'NULL' else 'NOT NULL' endif) to client;
    select id into @id from _test_nulls();
    message string('4. ', if @id is null then 'NULL' else 'NOT NULL' endif) to client;
end

Normally we should always get NULL. But that is only correct in the 1st query. All subsequent queries return NOT NULL.
If we recompile the procedure (alter procedure _test_nulls recompile) then again we get the 1st result NULL but all subsequent queries return NOT NULL.

I think this is a bug.

asked 19 Oct '12, 01:54

Arthoor's gravatar image

Arthoor
1.1k264056
accept rate: 0%

edited 19 Oct '12, 02:01

1

I can confirm this - really strange. Replacing "code = @code" with "code = NULL" does give correct results, as does the following "inlined" code (i.e. without the procedure):

begin
   declare @id integer;
   declare @code varchar(60) = null;
   select min(id) into @id from _test_nulls where code = @code;
   select @id;
   select min(id) into @id from _test_nulls where code = @code;
   select @id;
   select min(id) into @id from _test_nulls where code = @code;
   select @id;
end;

This returns three result sets with NULL as expected.

May this be a somewhat wrong kind of plan caching?


FWIW, for the versions you are testing with: "DROP TABLE x IF EXISTS" and "DROP PROCEDURE x IF EXISTS" make life easier code shorter...

(19 Oct '12, 03:41) Volker Barth
Replies hidden
1

My goal was to simplify that sample script as much as possible to reproduce the problem. So (I think) any other simplification gives correct results (except those cases in comments).

(19 Oct '12, 06:59) Arthoor
1

Well, the problem can even be shown when just calling the proc several times, i.e. by

call _test_nulls();
call _test_nulls();
call _test_nulls();
(19 Oct '12, 07:16) Volker Barth

Yes, you are right.

(22 Oct '12, 01:25) Arthoor

If you disable plan caching, I.e., max_query_cache=0, do you still see this issue?

permanent link

answered 19 Oct '12, 09:10

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

Yes, the problem is still here when I disable plan caching (max_plans_cached=0).
I think it can't be associated with plan caching because after disconnecting and connecting again the procedure returns NOT NULL even at the first call (except when it was recompiled before).

(22 Oct '12, 01:49) Arthoor

I am confused now: above you said the first call returns always the expected NULL.

"Normally we should always get NULL. But that is only correct in the 1st query. All subsequent queries return NOT NULL. If we recompile the procedure (alter procedure _test_nulls recompile) then again we get the 1st result NULL but all subsequent queries return NOT NULL."

(22 Oct '12, 13:16) Nica _SAP
Replies hidden

IMHO, that's no contradiction:

the first call returns always the expected NULL

In my understanding, this is the first call after creating/recompiling the procedure.

after disconnecting and connecting again the procedure returns NOT NULL even at the first call (except when it was recompiled before)

So that's another (i.e. not first) call without creating/recompiling.

(22 Oct '12, 15:19) Volker Barth

Yes, Volker, your understanding is absolutely correct.

(23 Oct '12, 01:06) Arthoor

I would expect the comparison where code = @code to return an unreliable response if you are specifically looking for a null. From the documentation:

The NULL value compared using any comparison operator with any value (including the NULL value) is "UNKNOWN." The only search condition that returns TRUE is the IS NULL predicate.

http://dcx.sybase.com/index.html#1201/en/dbreference/nulls.html

permanent link

answered 22 Oct '12, 17:29

Ron%20Emmert's gravatar image

Ron Emmert
33651118
accept rate: 12%

But shouldn't this query return something only when condition is TRUE (not UNKNOWN or FALSE)?

select min(id) into @id from _test_nulls where code = @code

(23 Oct '12, 01:15) Arthoor
Replies hidden
1

To clarify: MIN() and MAX() are documented to return NULL when applied on an empty group. You can chech this with

select min(dummy_col) from dummy where 1 = 2;
-- this will return NULL
select min(dummy_col) from dummy where 1 = null;
-- this will also return NULL

When applying MIN(), it doesn't play a role whether the underlying result set is empty because the search condition returns FALSE (as 1 = 2) or UNKNOWN (as code = @code in Arthoor' sample).

So the expected result is not "an unreliable response" but the documented behaviour of aggregate functions and three-valued logic...

(23 Oct '12, 03:12) Volker Barth
Your answer
toggle preview

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

Question tags:

×405
×137
×33
×18

question asked: 19 Oct '12, 01:54

question was seen: 1,242 times

last updated: 23 Oct '12, 03:18