Commented by: @mrotteveel

"One need to know in advance that NULL w/o CAST() is considered as CHAR(1)", why do you think that is the case? As far as I know it shouldn't have any type in this situation. It looks to me like the behaviour in 2.5 is correct (where it cannot infer the datatype), and the behavior in 3.0 and 4.0 is wrong.

Instead, it looks to me that maybe the datatype is inferred as SQL_NULL, which shouldn't be usable in this context, which is then coerced to a string which is then not convertible to an integer.

Commented by: @pavel-zotov

> why do you think that is the case? // char(1)

Because when i explicitly cast NULL to required datatype (int) then all works fine:

set term ^;
execute block returns(o_rel_id int) as
begin
execute statement ('select iif(1 <= ?, ?, cast(null as int) ) from rdb$database') (2, 3) into o_rel_id;
suspend;
end
^

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 496 LONG Nullable scale: 0 subtype: 0 len: 4
: name: O_REL_ID alias: O_REL_ID
: table: owner:

O\_REL\_ID 

============
3

PS.
Maybe subject of this ticket is also confusing as null cast/missed cast... :-)

Once again:
1) execute statement ('select iif(1 <= ?, ?, null) from rdb$database') (2, 3) into o_rel_id; ==> Statement failed, SQLSTATE = 22018 / conversion error from string " "
2) execute statement ('select iif(1 <= ?, ?, cast(null as int) ) from rdb$database') (2, 3) into o_rel_id; ==> all OK.

Here we can see nothing about cast parameters ("?"); rather it's about cast NULL literal :-)

Commented by: @mrotteveel

As I said in my previous comment, it looks like the second parameter is inferred as type SQL_NULL, which isn't usable in this context. When the parameter is then assigned, it gets the value 'not null' instead of 3, that is then coerced to a string which is then not convertible to an integer. The behaviour of 2.5 should probably be retained in this case, or at least SQL_NULL should not be allowed in this context.

I have confirmed this by preparing the statement select iif(1 <= ?, ?, cast(null as int) ) from rdb$database using Jaybird, and checking the type of the second parameter, which is 32767, or a (nullable) SQL_NULL.

Commented by: @mrotteveel

The reason the cast works, is that changes the inference of the parameter to SQL_LONG instead of SQL_NULL (just as would have been if you used iif(1 <= ?, cast(? as int), null)), that is because in an expression like iif(..., ?, VAL), the type of the parameter is inferred from the type of VAL, and if VAL is NULL, then it can only infer SQL_NULL (where in 2.5 it couldn't derive a datatype, so it produced a datatype unknown error), unless you explicitly cast that null to an explicit type.

Repeating my check in Jaybird, the type of the second parameter is (nullable) SQL_LONG in both

select iif(1 <= ?, ?, cast(null as int)) from rdb$database

and

select iif(1 <= ?, cast(? as int), null) from rdb$database