Hi, // 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. I think this is a bug. |
If you disable plan caching, I.e., max_query_cache=0, do you still see this issue? Yes, the problem is still here when I disable plan caching (max_plans_cached=0).
(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:
In my understanding, this is the first call after creating/recompiling the procedure.
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:
http://dcx.sybase.com/index.html#1201/en/dbreference/nulls.html 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
|
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):
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 easiercode shorter...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).
Well, the problem can even be shown when just calling the proc several times, i.e. by
Yes, you are right.