Hallelujah, Carlos! At MySQL, I argued for SQL compliance until I was blue in the face but without noticeable effect.
Database systems should have strings. Period. Not fixed strings, variable strings, or bounded strings. String comparisons should be blank extended. Nobody should ever have to worry about the semantic differences between char and vchar. Numbers should be numbers, too, but that's a different rant. Netfrastructure, NuoDB, and Amorphous all have just "string." Unbounded and simple. Falcon did too, but it couldn't be exposed. On 1/5/2016 9:08 AM, Carlos H. Cantu wrote: > 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 ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
