Hi all,
We have an annoying little problem. The visible manifestation is that
literals in CASE expressions could be padded with spaces.
Here is simplified example:
set term ^;
create procedure tmp_sp(pParam integer)
returns (selectionIf varchar(40), selectionCase varchar(40))
as
declare variable color varchar(10);
begin
if (pParam=1) then color='red';
else if (pParam=2) then color='yellow';
selectionIf='You have selected '||:color||' bag';
color=case :pParam when 1 then 'red' when 2 then 'yellow' end;
selectionCase='You have selected '||:color||' bag';
suspend;
end
^
set term ;^
select * from tmp_sp(1);
SELECTIONIF SELECTIONCASE
=========================== ============================
You have selected red bag You have selected red bag
drop procedure tmp_sp;
commit;
---
Padding with spaces is not a bug! Spaces are there because string
literals are CHARs, NOT VARCHARs. This is required by SQL standard.
Relevant except from SQL standard:
5 Lexical elements
5.3 <literal>
Syntax Rules
...
15) The declared type of a <character string literal> is fixed-length
character string. The length of a <character string literal> is the
number of <character representation>s that it contains.
...
6 Scalar expressions
6.11 <case expression>
Syntax Rules
...
7) The declared type of a <case specification> is determined by applying
Subclause 9.3, ‘‘Data types of results of aggregations’’, to the declared
types of all <result expression>s in the <case specification>.
9 Additional common rules
9.3 Data types of results of aggregations
Syntax Rules
...
3) Case:
a) If any of the data types in DTS is character string, then:
...
iii) Case:
1) If any of the data types in DTS is character large object string,
then the result data type is character large object string with
maximum length in characters equal to the maximum of the lengths in
characters and maximum lengths in characters of the data types in DTS.
2) If any of the data types in DTS is variable-length character string,
then the result data type is variable-length character string with
maximum length in characters equal to the maximum of the lengths in
characters and maximum lengths in characters of the data types in DTS.
3) Otherwise, the result data type is fixed-length character string with
length in characters equal to the maximum of the lengths in characters
of the data types in DTS.
----
To sum it up, standard dictates that literals are CHARs, aggregated
values has length of longest one and because CHARs are padded with
spaces to declared length, we have such stupid output in CASE. Sure, it
could be easily "fixed" with CAST or TRIM, but it's extremely annoying
to do so. And if there is any real world case when CHAR is the right
type for literals and VARCHAR the wrong one, I can't see it and would be
glad to be enlightened by someone else.
You may ask why I'm raising this issue here when Firebird's policy is to
follow SQL standard whenever possible (even with stupid requirements),
so annoying or not, we have to live with it. BUT... other databases are
not so strict here, break the stupid standard requirement and use
VARCHAR instead CHAR, for example:
MS SQLServer 2012:
SELECT 'a'+case 1 when 1 then '1 ' when 2 then '22222222222' end+'b'
FROM [SCM].[dbo].[SERVERID]
----
a1 b
(1 row(s) affected)
mySQL 5.6:
select concat('a', case 1 when 1 then '1 ' when 2 then '22222222222'
end, 'b') from tmp;
| concat('a', case 1 when 1 then '1 ' when 2 then '22222222222' end, 'b') |
|-------------------------------------------------------------------|
| a1 b |
oracle (not sure which version, provided by sqlZoo.net):
SELECT 'a'||case 1 when 1 then '1 ' when 2 then '22222222222' end||'b'
FROM world
'A'||CASE1WHE..
a1 b
...
PostgreSQL (not sure which version, provided by sqlZoo.net):
select concat('a', case 1 when 1 then '1 ' when 2 then '22222222222'
end, 'b') from world
concat
a1 b
...
db2 (not sure which version, provided by sqlZoo.net):
SELECT 'a'||case 1 when 1 then '1 ' when 2 then '22222222222' end||'b'
FROM world
1
a1 b
...
So Firebird stands out from the flock here with:
SELECT 'a'||case 1 when 1 then '1 ' when 2 then '22222222222' end||'b'
FROM rdb$database;
CONCATENATION
=============
a1 b
---
So Firebird behavior is even more annoying when you (have to or was used
to) work with other databases.
My question is: Could we sacrifice the standard compliance a little bit
by using VARCHAR instead CHAR (length rules remain the same!) as others
do? Because following the standard here really doesn't work in our favor
in this particular case.
My personal vote is for relaxing the rules and use VARCHAR. What is your
opinion?
best regards
Pavel Cisar
IBPhoenix
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
