Hello, I'm toying since a week with a slight simplification of core-4959 in FB 2.5:
isql -user sysdba -pass masterkey create database 'coalesce.fdb'; set list on; create view v3(f1, f2) as select coalesce(rdb$relation_id, 111), coalesce(null, rdb$relation_id) from rdb$database; select * from v3; /* F1 130 F2 130 /* create view v4(f1, f2) as select coalesce(rdb$relation_id, 111), coalesce(5, 5) from rdb$database; select * from v4; /* F1 130 F2 5 */ So far so good. Now: select v3.* from rdb$database left join v3 on 1=0; /* F1 111 ????????? F2 <null> */ select v4.* from rdb$database left join v4 on 1=0; /* F1 111 ????????? F2 <null> */ The only wrong case that can be detected is A left join B with coalesce(B.field, constant) when B doesn't have a matching record. It means for that rpb: record->rec_format == NULL && record->rec_fmt_bk != NULL The "problem" here is case nod_missing inside EVL_boolean because it clears the null indicator. When B.field is evaluated to null, nod_missing does request->req_flags &= ~req_null; and the rest is history. Because the second param of coalesce is a constant, the null flag remains inactive and we see 111 in the output instead of null. But it also can happens with expressions: create view v5(f1, f2) as select coalesce(null, current_time), coalesce(rdb$relation_id, current_time - current_time) from rdb$database; select * from v5; /* F1 04:01:38.0000 F2 134.0000 */ select v5.* from rdb$database left join v5 on 1=0; /* F1 <null> F2 0.0000 ????????? */ My wish for FB v4 is that we stop using nod_value_if for coalesce (and for the CASE statement) and instead we implement a new verb. Among other things, it will avoid reevaluating expressions and will give us more freedom to fix things. C. --- Claudio Valderrama C. Consultant, SW developer. ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
