Re: [Firebird-devel] Literals in CASE expression
My sarcasm detector is not so good. Mark - Reply message - Van: "Dmitry Yemanov" Aan: "For discussion among Firebird Developers" Onderwerp: [Firebird-devel] Literals in CASE expression Datum: wo, jan. 6, 2016 08:37 06.01.2016 10:28, Mark Rotteveel wrote: > No, please no. I believe Arno was writing with the sarcasm mode turned on ;-) Dmitry -- 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
Re: [Firebird-devel] Literals in CASE expression
06.01.2016 00:49, Leyne, Sean wrote: > >> 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. > > The issue is not the intermediate datatype but the final datatype. The final datatype of the CASE expression. And it can be altered without describing literals as VARCHARs, that was the point. > In Pavel's use case "color" is a VarChar as such any value/string/variable > which is assigned to it should be cast as a VarChar, regardless of the > intermediate datatype. > > The current outcome is wrong! The SQL committee respectfully disagrees. > P.S. The fact that a fix for this could introduce compatibility issues is not > a something we should care about when fixing invalid/wrong functionality. Suggestion to describe literals as VARCHARs may fix the CASE issue but break something else that used to work correctly. Do you care? Dmitry -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Literals in CASE expression
06.01.2016 10:28, Mark Rotteveel wrote: > No, please no. I believe Arno was writing with the sarcasm mode turned on ;-) Dmitry -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Literals in CASE expression
No, please no. I can find some logic in changing the concatenation behavior as described (as I think that the standard might offer some leeway there, although I need to study on it first), but changing the math that is clearly described in the standard to something else is plain wrong. Mark - Reply message - Van: "Arno Brinkman" Aan: "For discussion among Firebird Developers" Onderwerp: [Firebird-devel] Literals in CASE expression Datum: wo, jan. 6, 2016 01:03 > P.S. The fact that a fix for this could introduce compatibility issues is > not a something we should care about when fixing invalid/wrong > functionality. Compatibility issues are items which developers need to > resolve for themselves, nothing is forcing a developer to adopt v3. Well. this : SELECT 1 / 3 FROM RDB$DATABASE SELECT 1.0 / 3 FROM RDB$DATABASE should return 0.333, agree ? Exactly same, but then with numbers DIALECT 5 ? :-p Kind Regards, Arno Brinkman -- 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
[Firebird-devel] [FB-Tracker] Created: (CORE-5069) Improper call of "daemon" shell function in rc startup script leads to "dirname" error
Improper call of "daemon" shell function in rc startup script leads to "dirname" error -- Key: CORE-5069 URL: http://tracker.firebirdsql.org/browse/CORE-5069 Project: Firebird Core Issue Type: Bug Components: Scripts Affects Versions: 2.5.5 Environment: CentOS 6.7 Reporter: Steve Friedl Priority: Minor After installing FirebirdCS-2.5.5.26952-0.amd64.rpm on a CentOS 6.7 system and switching into SuperClassic mode, starting the service produces a script type error: # service firebird start Starting Firebird server [default] /usr/bin/dirname: extra operand `-daemon' Try `/usr/bin/dirname --help' for more information. [ OK ] This comes from /etc/rc.d/init.d/firebird here: case "$1" in start) echo -n "Starting $FULLNAME " daemon --user=$FBRunUser "export FIREBIRD LD_LIBRARY_PATH; $GUARDIAN -pidfile $pidfile -daemon -forever" The issue is that the daemon() shell function is trying to extract the actual executable name (which is $GUARDIAN) from the second parameter, but it's getting confused by the string, attempting to run dirname on te wrong thing. It's very confused. This causes - among other things - the pid file not to be created properly, and the "service" infrastructure doesn't know if it's running or not. The fix is to add a --check="$GUARDIAN" parameter to the daemon line (I put it first), which provides the executable name so the shell function doesn't have to guess. daemon --check="$GUARDIAN" --user=$FBRunUser "export FIREBIRD LD_LIBRARY_PATH; $GUARDIAN -pidfile $pidfile -daemon -forever" This /etc/rc.d/init.d/firebird script is pulled from the install bundle in /opt/firebird/misc/firebird.init.d.mandrake, and this is why I'm not submitting this as a patch. The fix I mention works great on CentOS 6.7, but I have no idea if it works or breaks on Mandrake, or on other versions of Linux; this requires a bit more installation study that I can't do - I've never used and don't have Mandrake. Looking in the svn logs, it appears that this behavior was changed to use the "daemon" shell function here: r43324 | alexpeshkoff | 2009-07-21 04:02:30 -0700 (Tue, 21 Jul 2009) | 2 lines Updated startup script from Philippe Makowski This is a suspiciously long time ago for this issue to appear to be unreported (I couldn't find it in the tracker), so I'm not sure what introduced the issue. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Literals in CASE expression
> P.S. The fact that a fix for this could introduce compatibility issues is > not a something we should care about when fixing invalid/wrong > functionality. Compatibility issues are items which developers need to > resolve for themselves, nothing is forcing a developer to adopt v3. Well. this : SELECT 1 / 3 FROM RDB$DATABASE SELECT 1.0 / 3 FROM RDB$DATABASE should return 0.333, agree ? Exactly same, but then with numbers DIALECT 5 ? :-p Kind Regards, Arno Brinkman -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5068) gbak with invalid parameter crashes FB
gbak with invalid parameter crashes FB -- Key: CORE-5068 URL: http://tracker.firebirdsql.org/browse/CORE-5068 Project: Firebird Core Issue Type: Bug Affects Versions: 2.5.5 Reporter: Carlos H. Cantu Typo in gbak's command line parameter causes Firebird process to crash, ie: gbak -c -v -se service_mgr -user_all_space d:\backup.gbk d:\bd.fdb gbak:unknown switch "USER_ALL_SPACE" gbak: ERROR:connection lost to database gbak:Exiting before completion due to errors -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5067) Blocking new connections as a consequence of the too long sweep security2.fdb
Blocking new connections as a consequence of the too long sweep security2.fdb -- Key: CORE-5067 URL: http://tracker.firebirdsql.org/browse/CORE-5067 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 2.5.5, 2.5.3 Environment: Windows x64, Firebird Classic Server x64 Reporter: Oleg Matveyev Symptoms: every time one process fb_inte_server.exe load 100% CPU one kernel after some easy queries. Current process ID is not present in SELECT FROM MON$ATTACHMENTS. After one hour of continuous working, no more connections allowed, and number fb_inet_server process increased from 40..50 to more than 250. firebird.log: __ WOODY Sun Jan 03 13:26:01 2016 Sweep is started by SWEEPER Database "C:\IB\SECURITY2.FDB" OIT 19821012, OAT 20, OST 20, Next 70004809 WOODY Sun Jan 03 13:34:03 2016 Shutting down the server with 1 active connection(s) to 1 database(s), 0 active service(s) WOODY Sun Jan 03 13:34:06 2016 Sweep is started by SWEEPER Database "C:\IB\SECURITY2.FDB" OIT 19821012, OAT 20, OST 20, Next 70005503 WOODY Sun Jan 03 13:36:23 2016 Shutting down the server with 1 active connection(s) to 1 database(s), 0 active service(s) WOODY Sun Jan 03 13:36:24 2016 Sweep is started by SWEEPER Database "C:\IB\SECURITY2.FDB" OIT 19821012, OAT 20, OST 20, Next 70005604 WOODY Sun Jan 03 13:46:27 2016 Sweep is started by SWEEPER Database "C:\IB\SECURITY2.FDB" OIT 19821012, OAT 20, OST 20, Next 70005805 WOODY Sun Jan 03 13:51:03 2016 Sweep is started by SWEEPER Database "C:\IB\SECURITY2.FDB" OIT 19821012, OAT 20, OST 20, Next 70006002 __ gstat -h Database "c:\ib\security2.fdb" Database header page information: Flags 0 Checksum12345 Generation 140017716 Page size 4096 ODS version 11.2 Oldest transaction 19821012 Oldest active 20 Oldest snapshot 20 Next transaction70008371 Bumped transaction 1 Sequence number 0 Next attachment ID 70008352 Implementation ID 16 Shadow count0 Page buffers0 Next header page0 Database dialect3 Creation date Mar 19, 2013 10:56:23 Attributes force write Variable header data: *END* __ -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Literals in CASE expression
Dmitry, > > 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. The issue is not the intermediate datatype but the final datatype. In Pavel's use case "color" is a VarChar as such any value/string/variable which is assigned to it should be cast as a VarChar, regardless of the intermediate datatype. The current outcome is wrong! Sean P.S. The fact that a fix for this could introduce compatibility issues is not a something we should care about when fixing invalid/wrong functionality. Compatibility issues are items which developers need to resolve for themselves, nothing is forcing a developer to adopt v3. -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5066) FBSVCMGR produces "Missing arg #1 - possibly status vector overflow" when its command includes >= 5 switches and all of them aren't allowed for non-p
FBSVCMGR produces "Missing arg #1 - possibly status vector overflow" when its command includes >= 5 switches and all of them aren't allowed for non-privileged user --- Key: CORE-5066 URL: http://tracker.firebirdsql.org/browse/CORE-5066 Project: Firebird Core Issue Type: Bug Components: SVCMGR Affects Versions: 3.0 RC1 Reporter: Pavel Zotov intro: === create user foo password 'bar'; commit; === Then: fbsvcmgr localhost/:service_mgr user foo password bar info_user_dbpath info_get_env info_get_env_lock info_get_env_msg info_svr_db_info Output: Service isc_info_svc_user_dbpath requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. (note that we have five "info_" switches and all of them are unavail. for non-privileged user 'foo'; fifth row in output is broken). Let's play with number of args: 1) remove 'info_user_dbpath' (i.e. 1st switch) from source command: fbsvcmgr localhost/:service_mgr user foo password bar info_get_env info_get_env_lock info_get_env_msg info_svr_db_info Output: Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_svr_db_info requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. 2) remove 'info_svr_db_info' (i.e. last switch) from source command: fbsvcmgr localhost/:service_mgr user foo password bar info_user_dbpath info_get_env info_get_env_lock info_get_env_msg Output: Service isc_info_svc_user_dbpath requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. 3) change places but not total number of switches: move fbsvcmgr localhost/:service_mgr user foo password bar info_user_dbpath info_get_env info_svr_db_info info_get_env_lock info_get_env_msg -- compare with origin: -- fbsvcmgr localhost/:service_mgr user foo password bar info_user_dbpath info_get_env info_get_env_lock info_get_env_msg info_svr_db_info Output: Service isc_info_svc_user_dbpath requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_svr_db_info requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service isc_info_svc_get_env requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -Service requires SYSDBA permissions. Reattach to the Service Manager using the SYSDBA account. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Literals in CASE expression
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 redbag
>
> 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
> PC> Syntax Rules
> PC> ...
> PC> 15) The declared type of a is fixed-length
> PC> character string. The length of a is the
> PC> number of s that it contains.
>
> PC> ...
>
> PC> 6 Scalar expressions
>
> PC> 6.11
> PC> Syntax Rules
> PC> ...
> PC> 7) The declared type of a is determined by applying
> PC> Subclause 9.3, ‘‘Data types of results of aggregations’’, to the declared
> PC> types of all s in the .
>
>
> 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 '222' 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 '222'
> PC> end, 'b') fro
Re: [Firebird-devel] Literals in CASE expression
On 05/01/16 14:37, Dmitry Yemanov wrote: > 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 of CHAR(4), because string literal>s are fixed-length character strings and the size of the > largest aggregated is 4 characters. But if you pass in a CHAR(3), surely you should get a CHAR(3) back? Otherwise, your literal isn't really a literal because its datatype has been changed? Okay, I know sanity often doesn't enter into it when defining specifications, but requiring all literals to be forced to the same datatype does seem to be a fairly insane example :-) (And as I read the spec, if CASE has a varchar in it somewhere, it can return a varchar, so why not always return a varchar. Especially if, again as I read it, if you mix char and varchar you will actually get the desired result.) So if we're changing the datatype anyway, we might as well change it to varchar. Cheers, Wol -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Literals in CASE expression
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, 'b') || '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, 'b') 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
Re: [Firebird-devel] Literals in CASE expression
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 of CHAR(4), because s are fixed-length character strings and the size of the largest aggregated 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
Re: [Firebird-devel] Literals in CASE expression
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 redbag
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
PC> Syntax Rules
PC> ...
PC> 15) The declared type of a is fixed-length
PC> character string. The length of a is the
PC> number of s that it contains.
PC> ...
PC> 6 Scalar expressions
PC> 6.11
PC> Syntax Rules
PC> ...
PC> 7) The declared type of a is determined by applying
PC> Subclause 9.3, ‘‘Data types of results of aggregations’’, to the declared
PC> types of all s in the .
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 '222' 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 '222'
PC> end, 'b') from tmp;
PC> | concat('a', case 1 when 1 then '1 ' when 2 then '222' 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 '222' 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 '222'
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 '222' end||'b'
PC> FROM world
PC> 1
PC> a1 b
PC> ...
PC> So Firebird stands
[Firebird-devel] Literals in CASE expression
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 redbag
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
Syntax Rules
...
15) The declared type of a is fixed-length
character string. The length of a is the
number of s that it contains.
...
6 Scalar expressions
6.11
Syntax Rules
...
7) The declared type of a is determined by applying
Subclause 9.3, ‘‘Data types of results of aggregations’’, to the declared
types of all s in the .
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 '222' 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 '222'
end, 'b') from tmp;
| concat('a', case 1 when 1 then '1 ' when 2 then '222' end, 'b') |
|---|
| a1 b |
oracle (not sure which version, provided by sqlZoo.net):
SELECT 'a'||case 1 when 1 then '1 ' when 2 then '222' 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 '222'
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 '222' 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 '222' 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
[Firebird-devel] [FB-Tracker] Created: (CORE-5065) Altering a procedure called by another procedure doesn't recompile parent
Altering a procedure called by another procedure doesn't recompile parent - Key: CORE-5065 URL: http://tracker.firebirdsql.org/browse/CORE-5065 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 2.5.4 Environment: Tested on both Windows and Linux servers Reporter: Jonathan Neve Create procedures using the following SQL: create procedure PR_TEST_CHILD returns ( RESULT varchar(20)) as begin result = 'TEST INIT'; suspend; end; create procedure PR_TEST_PARENT returns ( RESULT varchar(20)) as begin select result from pr_test_child into :result; suspend; end; If you select from PR_TEST_PARENT, you will get 'TEST INIT', as expected. Open a new transaction, select * from PR_TEST_PARENT, you get 'TEST INIT' again. Keep this second transaction open. Now open a new transaction, and change the child procedure: create or alter procedure PR_TEST_CHILD returns ( RESULT varchar(20)) as begin result = 'TEST CHANGED'; suspend; end; Commit this transaction, and then select again. You'll get 'TEST INIIT'. Commit all open transactions, close all connections, open a new connection/transaction and select again from parent proc, and you'll still get 'TEST INIT'. If you do the above with only one transaction open, it all works fine, so it seems to be caused by altering the child procedure on the fly while other transactions are open and/or while the parent proc is in use in another transaction. A clear error message would be a lot better than this highly confusing behaviour. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
