IIF -> FALSE produces space

[tastyshrimps]

When an IIF resolves to FALSE, and only then, an empty string concatenation resolves to a space:

SELECT
'X' ||
IIF(1=2, 'X', '')
|| 'X'
FROM RDB$DATABASE

=> X X

Firebird 5.0.1
Classic Server

[dyemanov]

By design. Resulting data type of IIF is computed at the prepare stage and becomes CHAR(1) because 'X' is CHAR(1) and '' is CHAR(0). Empty string as CHAR(1) becomes a pad character (which is space for non-binary data).

[AlexPeshkoff]

If you cast literals to varchar() you will get desired behavior:

SQL> SELECT 'X' || IIF(1=2, cast('X' as varchar(1)), cast('' as varchar(1))) || 'X' FROM RDB$DATABASE;

CONCATENATION

XX

SQL>