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.