Strange behavior on NULL = NULL comparisons (SA11, SA12)

[former_SQLA_member1694875]

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.

[VolkerBarth]

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

[former_SQLA_member1694875]

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

[VolkerBarth]

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();

[former_SQLA_member1694875]

Yes, you are right.

[Former Member]

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

[former_SQLA_member1694875]

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

[Former Member]

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

[VolkerBarth]

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.

[former_SQLA_member1694875]

Yes, Volker, your understanding is absolutely correct.

[Former Member]

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

[former_SQLA_member1694875]

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

[VolkerBarth]

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