White spaces when updating varchar field using iif in firebird

Question

I see strange result when executing this query

update sd_invodt set line_type=iif(is_promo=1, 'campaign', 'item')

The value in line_type field will be "item ". There are whitespaces in value. But when I execute this query

update sd_invodt set line_type='item'

I don't get white spaces.

Now I have to use trim as workaround

update sd_invodt set line_type=trim(iif(is_promo=1, 'campaign', 'item'))

I use latest firebird 2.5. Line_type is a varchar(15).

Is this bug in Firebird?

EDIT

I have tested using new database, and the problem persists.

Toiletry

Answer 1

The result type of the iif() depends on input and in case of string the result type seems to be char(x) where x is the length of the longest input string. Thus the "item" will be padded with 4 spaces to make it as long as the "campaign" is. I think this is by design but you may want to enter an ticket to the FB issuetracker.

So the iif() return "item " in case of first parameter being false and when you store spaces onto varchar field they are preserved.

Longwood

It is not considered a bug, see CORE-4147 – Luff