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