05.01.2016 16:31, Pavel Cisar wrote: > > We have an annoying little problem.
We have it for the past decade, so I agree with describing this problem as a "little" one ;-) > 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. First of all, let's distinguish between two things: (1) how string literals are described and (2) how CASE evaluates the resulting datatype. Changing any of them may lead to the desired result. > 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. We already have some differences against the standard, e.g. we always evaluate concatenation as VARCHAR, even if both arguments are CHARs. We can have more if we consider it feasible. > MS SQLServer 2012: > mySQL 5.6: > oracle (not sure which version, provided by sqlZoo.net): > PostgreSQL (not sure which version, provided by sqlZoo.net): > db2 (not sure which version, provided by sqlZoo.net): These examples say nothing about the approach used there: either literals are VARCHARs, or CASE derives the datatype differenly, or both. And I suppose Firebird is not really alone, see for MariaDB: CASE ... THEN 'a' ... THEN 'abcd' ... ELSE 'abc' END will return a result with a <data type> of CHAR(4), because <character string literal>s are fixed-length character strings and the size of the largest aggregated <literal> is 4 characters. > 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. I agree that such a CASE result is unexpected and may be considered stupid. But I'm not convinced (yet) that CHAR->VARCHAR change for string literals is a proper solution. It may introduce compatibility issues, so we cannot touch it before v4. As a first attempt, I'd rather find some way to return the expected result from CASE, e.g. implicitly cast all CHAR arguments to VARCHAR before processing and thus return VARCHAR. Dmitry ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
