User defined functions seem to be believing that NULL = NULL after being called multiple times.

I can reproduce this in all the builds of v16 I can find (including 2546), different windows versions, machines etc. The ANSINULL database option is ON (the default).

Run this script in isql:

CREATE TABLE Test1 (PK CHAR(20) NOT NULL, Col CHAR(12) NULL, PRIMARY KEY ( PK ASC ));

insert into Test1 (PK) values ('Blah1'); commit;

CREATE INDEX Test1_Col ON Test1 ( Col ASC );

create or replace FUNCTION TestFn (in val char(12)) returns char(20) NOT DETERMINISTIC begin declare rv char(20); select first PK into rv from Test1 where Col = val; return(rv); end;

If you run this query:

select TestFn(null);

the first 10 times it produces the correct result, "NULL", since NULL should not equate to NULL. After that it matches the first record it finds - in this case there's only one record so you get 'Blah1' (ie it matches NULL = NULL).

This is as stripped down as I can get the reproducible (the original was spotted in a far more complex situation). You have to use a function, running a straight SELECT in isql does not have issues. The index appears to be the key - no index and everything works fine.

Different queries seem to fail after 10 calls, the number of records in the table doesn't seem relevant either - the example above with 20000 records still fails on the eleventh go.

Data type doesn't seem relevant either, the above example works the same with Col as char, int or date.

asked 15 Nov, 09:31

Justin%20Willey's gravatar image

Justin Willey
7.0k115148219
accept rate: 21%

edited 15 Nov, 09:48

1

How do you call the function - within DBISQL? Does it differ if you "consume" the return value, say by storing it in a table or summing it up? I remember cases where DBISQL ignored/skipped repeated calls... Just very wild guessing...

(15 Nov, 10:05) Volker Barth
Replies hidden

I'm testing in isql but it happens if you call it in other ways. For example this function

create or replace function test1(@tests int default 20)
returns char(250)
BEGIN
declare @Result char(250);
declare @counter int default 1;
while @counter <= @tests loop
    set @Result = string(@Result,' : ',@Counter,'-',(select TestFn(null)));
    set @counter=@counter+1;
    message @Counter;
end loop;
return @Result;
end;

Then running select test1(20), gives:

 : 1- : 2- : 3- : 4- : 5- : 6- : 7- : 8- : 9- : 10- : 11-Blah1 : 12-Blah1 : 13-Blah1 : 14-Blah1 : 15-Blah1 : 16-Blah1 : 17-Blah1 : 18-Blah1 : 19-Blah1 : 20-Blah1
showing the result changes after the tenth run.

(15 Nov, 11:06) Justin Willey

Does (client) plan caching play a role here?

(15 Nov, 14:26) Volker Barth

Thanks for reporting this. I've confirmed that this is indeed a bug with plan caching, as Volker suggested. In v16, plans for statements in procedures are built for 10 training executions and a reusable plan built on the 11th execution. The reusable plan that is being built on the 11th iteration appears to be semantically incorrect for an equality predicate on a nullable column that uses an index---I'm currently investigating why. In v17 the semantic incorrectness bug still exists, but the logic for deciding when to cache plans has changed which makes the repro less deterministic.

As a workaround, you should disable plan caching by setting option max_plans_cached=0 at the connection, user, or database level.

permanent link

answered 15 Nov, 16:55

David%20DeHaan's gravatar image

David DeHaan
546611
accept rate: 50%

But is it expected behaviour that plans are cached for non-deterministic functions?

(15 Nov, 16:58) Volker Barth
Replies hidden

Thanks David - that's great to have that pinned down.

I think we can protect against the problem in most cases with "AND whatever IS NOT NULL" so long as we can identify where we are relying on NULLS not matching. If you find out when the issue first arose, that would be useful to know.

(15 Nov, 18:35) Justin Willey
Replies hidden
2

Yes and no.

When functions are marked as NOT DETERMINISTIC, the optimizer will not cache plans for the FUNCTION CALL---i.e. it impacts optimization of statements that REFERENCE expressions marked as non-deterministic.

In this case, the plan being cached is for a statement within the function definition itself. The execution of statements WITHIN the function are not impacted by the NOT DETERMINISTIC keyword. Granted, in this particular function it is the query itself that is the source of the non-determinism due to the FIRST keyword without an ORDER BY. Once we cache a particular plan, the query execution will be deterministic as long as we are running that cached plan. However, that is acceptable because the NOT DETERMINISTIC keyword is essentially a warning that execution MAY be non-deterministic---not a promise that it will be.

(16 Nov, 08:34) David DeHaan

Thanks for the detailed explanation, highly appreciated!

(16 Nov, 09:28) Volker Barth
2

The bug is specific to reusable plans built by the optimizer bypass, so another workaround is to use HINT(FORCE OPTIMIZATION) to disable the optimizer bypass (and plan caching) for specific statements.

From a few simple experiments, I've reproduced the bug in v11.0.1 GA (early 2009) but not in v10.0.1.4157 (although it's possible it was present in v10 but with different repro conditions).

(16 Nov, 11:56) David DeHaan

Thank you David, that's very helpful indeed. It means that the effect on us must be pretty limited or we would have seen it elsewhere in the last 10 years! We can protect against it in the couple of places we know are vulnerable and wait for the ebf.

(16 Nov, 15:17) Justin Willey
1

The fix for this bug has been submitted to the v16 (build 2606+) and v17 (build 4104+) codelines and should appear in future ebfs exceeding those build numbers.

(6 hours ago) David DeHaan
showing 3 of 7 show all flat view
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:

×209
×36
×19
×14

question asked: 15 Nov, 09:31

question was seen: 83 times

last updated: 6 hours ago