Commented by: @paulvink

Several solutions are possible, but I'd like to suggest that SINGULAR's behaviour be modeled along the same lines as EXISTS:

- TRUE if the search condition is met exactly once;
- FALSE in all other cases;
- NULL is never returned;
- NOT SINGULAR always returns the opposite of SINGULAR.

IOW: SINGULAR( <query> ) <=> The result set of <query> contains exactly one row.

Commented by: @pcisar

Maybe I'm just tired today, but I can't figure out any reproducible test case for this issue. Can someone help me out? A simple SQL script would be great.

Commented by: @paulvink

Hi Pavel, the trick I used was to test on singular, not(singular), not singular and not(not singular). Otherwise you don't know if an empty set means false or null. So:

set autoddl on;
create database "test.fdb" user "sysdba" password "masterke";
create table t (a int);

// first testcase:
insert into t values (2);
insert into t values (null);
commit;
select 1 from rdb$database where singular( select * from t where a = 1);
select 1 from rdb$database where not (singular( select * from t where a = 1));
select 1 from rdb$database where not singular( select * from t where a = 1);
select 1 from rdb$database where not (not singular( select * from t where a = 1));
// the above queries return 4 empty sets, so both SING and NOT SING were null

// second testcase:
delete from t;
insert into t values (1);
insert into t values (null);
commit;
select 1 from rdb$database where singular( select * from t where a = 1);
// returns 1, so SING was true. No need for not(singular) here.
select 1 from rdb$database where not singular( select * from t where a = 1);
select 1 from rdb$database where not (not singular( select * from t where a = 1));
// 1.5.3: two empty sets, so NOT SING was null
// 2.0: first an empty set, then 1, so NOT SING was false

// likewise for 3rd and 4th testcases.

// note that all this has already been fixed for 2.1; don't know about 2.0.1 and 1.5.4

Commented by: @pcisar

Fix confirmed for 2.1 Alpha 1. Test added.

Commented by: @pcisar

Reopened to update ticket information.