On 05/01/2016 11:31, Pavel Cisar wrote: > Hi all, > > We have an annoying little problem. The visible manifestation is that > literals in CASE expressions could be padded with spaces. > > We also have DECODE. Let's see it:
SQL> select 'a' || decode(1, 1, 'a', 2, 'bbbbb') || 'c' from rdb$database; CONCATENATION ============= aac It doesn't follow the CASE way, although at least in the README, it's not documented. But let's see it details: SQL> set sqlda_display on; SQL> select decode(1, 1, 'a', 2, 'bbbbb') from rdb$database; INPUT message field count: 0 OUTPUT message field count: 1 01: sqltype: 452 TEXT Nullable scale: 0 subtype: 0 len: 5 charset: 0 NONE : name: DECODE alias: DECODE : table: owner: DECODE ====== a What? It returns CHAR! The difference between the DECODE and CASE is that CASE adds an explicit CAST to CHAR, while DECODE doesn't. So DECODE returns the original expressions and when you concatenate it with something, the expression is not padded. That's weird. I'd say we need to change DECODE to be described as VARCHAR in this case. DECODE doesn't fulfill all CASE types, but it's easy to use and may maintain the "expected" behavior. PS: After all this, something me say that my original intention has to do DECODE different than CASE but people didn't agreed and want DECODE as CASE. So I tested with 2.5 too (above is 3.0) and DECODE works as CASE. So we have some work to do in a way or another. :) Adriano ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel