Daniel Vidić Daniel Vidić - 4 months ago 11
SQL Question

SQL with nested CASE and joining strings gives trailing spaces

I am using Firebird 2.5. When I run

SELECT
CASE BILANCA
WHEN 1 THEN
CASE BILANCA WHEN 0 THEN 'SMALLINT' WHEN 1 THEN 'NUME' END
ELSE 'A'||'B' END
AS BILANCA1,
CASE BILANCA
WHEN 1 THEN
CASE BILANCA WHEN 0 THEN 'SMALLINT' WHEN 1 THEN 'NUME' END
ELSE 'AB' END
AS BILANCA2
FROM GS01BILANCA


field BILANCA2 is
'NUME '
(there are 4 spaces after NUME) and BILANCA1 is
'NUME'
. Number of added spaces is equal to length of longest string in CASE.
The only difference is in joining string in BILANCA1:

ELSE 'A'||'B' END


DDL is

CREATE TABLE GS01BILANCA
(BILANCA INTEGER DEFAULT 0 NOT NULL,
CONSTRAINT PK_GS01BILANCA PRIMARY KEY (BILANCA));


Table has 2 records:


  • 1

  • 2



Result is


  • 'NUME '
    (there are 4 spaces after NUME),
    'NUME'

  • 'AB'
    ,
    'AB'



Question: Is this expected and why?

Answer Source

String literals in Firebird are of type CHAR, the datatype of a case expression will have the longest length of the different branch. So with

CASE BILANCA WHEN 0 THEN 'SMALLINT' WHEN 1 THEN 'NUME' END

The datatype will be CHAR(8). A CHAR value will always be right-padded with the spaces to the declared length.

However when one of the branches of a case expression is a VARCHAR, then all branches are coerced to a VARCHAR. And concatenation in Firebird will always produce a VARCHAR, even if both operands are CHAR

This means that in the case of BILANCA1:

CASE BILANCA 
WHEN 1 THEN
    CASE BILANCA WHEN 0 THEN 'SMALLINT' WHEN 1 THEN 'NUME' END
ELSE 'A'||'B' END

that 'A'||'B' is a VARCHAR(2), this causes the entire case to be coerced to VARCHAR, which then causes 'SMALLINT' to become a VARCHAR(8) and 'NUME' a VARCHAR(4), which makes the entire expression becomes a VARCHAR(8).

Note that this type coercion is pushed down, so the literal will be a varchar from the start and not be blanked padded* by intermediate evaluation of the innermost case statement.

That is not the case for BILANCA2, as all (nested) branches are literals, therefor it will be a CHAR(8).

If you want both to behave the same you can do:

  1. Cast the entire expression to the desired type (this would work both for bilanca1 and bilanca2)
  2. Cast part of the expression (eg one of the literals or the nested case) to a varchar; this only works to force bilanca2 to a varchar
  3. Cast the string concatenation to char; this only works to force bilanca1 to a char
  4. Concatenate an empty string to one of the literals; this only works to force bilanca2 to a varchar
  5. Use trim on the entire expression (this also coerces to varchar)

*) This in contrast to for example cast(cast('nume' as char(8)) as varchar(8)) which will produce 'nume ' and not just 'nume' as in this case the intermediate result is preserved, and existing spaces are not thrown away in a varchar.