New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Allow implicit conversion between boolean and string [CORE5167] #5450
Comments
Commented by: @asfernandes Boolean is not implicit convertable to string, so it should not work with concatenation. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: Alex Bekhtin (afgm) But why? and SQL-2003: <character factor> ::= <character primary> [ <collate clause> ] <unsigned value specification> ::= <unsigned literal> | <general value specification> |
Commented by: @asfernandes So, you're saying if it's implicitly convertible, it would work, but I said it's not implicit convertible per the standard. I didn't found where this is said now. But feel free to point a place telling the contrary. |
Commented by: Sean Leyne (seanleyne) Boolean string literals were defined in the SQL:1999 standard (https://en.wikipedia.org/wiki/Boolean_data_type) -- the details align with details posted above Alex The Oracle semantics (http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/literal.htm) -- note they use "NULL" not "UNKNOWN" The PostgreSQL semantics (http://www.postgresql.org/docs/8.1/static/datatype-boolean.html) -- they also refer to "NULL" for "UNKNOWN" but the link doesn't provide full example to confirm IMO, this case should be re-opened, this is a real issue. |
Modified by: Sean Leyne (seanleyne)status: Closed [ 6 ] => Reopened [ 4 ] resolution: Won't Fix [ 2 ] => |
Commented by: @asfernandes Sean, and what Alex said and your link about SQL points we have a problem? |
Commented by: Sean Leyne (seanleyne) There is a string representation for Boolean datatypes ("TRUE", "FALSE" and "Unknown" or NULL), so "select '' || cast( TRUE as BOOLEAN ) ..." is valid. |
Commented by: @asfernandes Yes, and that is the explicit conversion. This ticket is about implicit conversion. I'm trying to find the SQL 2011 foundation PDF but no luck. But I do remember it disallowed implicit conversion of booleans to strings. |
Commented by: @dyemanov It's not as simple. SQL spec explicitly forbids many implicit conversions that we support since the very beginning. It has a special chapter "9.5 Result of data type combinations" which is applied for concatenation (among other operations). This chapter declares that, for example: If any of the data types in DTS is character string, then: It means that 'asd' || 123 must be prohibited. But it's perfectly valid in Firebird. The same for other datatypes. So, if we follow the SQL spec for booleans, then the current implementation is correct but it works opposite to all other datatypes in Firebird. If we follow our historical behaviour, then implicit conversions should be allowed but it would violate the standard. |
Commented by: @dyemanov SQL:2011 draft is here: http://www.wiscorp.com/sql20nn.zip |
Commented by: Sean Leyne (seanleyne) As I see it, given that our current implementation is non-conforming, we have the following choices. 1- keep the current implementation 2- provide implicit conversion for Boolean to maintain consistency with the non-conforming treatment of other data types 3- adopt conforming treatment for all "data type combinations" IMO, #2 seems to be the only reasonable path. #3 would create significant compatibility issues with 99.99% of existing applications, this option is not reasonable. #1 would create exceptional/inconsistent treatment. It is better to be consistent, even if non-conforming, then to be inconsistent -- developers want/can 'handle' consistent/predicable handling, they can't abide inconsistency. |
Commented by: @dyemanov I tend to agree with Sean. |
Commented by: @asfernandes The behaviour of implicit conversion with boolean was not logical for me in relation to what we had, but seems it what I found when looking for BOOLEAN things in the standard. It's surely inconsistent with Firebird handling of other types. It is, however, documented: Booleans are not implicitly convertible to any other datatype. But it's convertible to/from strings with CAST. And that may be relaxed without problems. So should we change it only for v4? |
Commented by: @dyemanov I believe this should be relaxed for v3 too. But I'm afraid this is too late for v3.0.0 (not only code must be changes but also docs), so maybe in v3.0.1? |
Modified by: @asfernandesissuetype: Bug [ 1 ] => Improvement [ 4 ] Fix Version: 4.0 Alpha 1 [ 10731 ] assignee: Adriano dos Santos Fernandes [ asfernandes ] description: select '' || cast( 1 as SMALLINT ) from rdb$database; select cast( TRUE as varchar(20) ) from rdb$database; -- works fine => Edited subject to say about the more general case - Adriano. select '' || cast( 1 as SMALLINT ) from rdb$database; select cast( TRUE as varchar(20) ) from rdb$database; -- works fine summary: Automatic boolean values convertation with string concatenation => Allow implicit conversion of boolean values to string |
Commented by: @asfernandes Should this continue to give an error? SQL> select false > 'true' from rdb$database; Or should not like this? select 1 > '1' from rdb$database; |
Commented by: @dyemanov It should work by converting 'true' to boolean before comparing the values. |
Commented by: @asfernandes But what about this? not 'true' 'true' or 'false' IMO, should give an error like '1' + '2' does. Agree? |
Commented by: Sean Leyne (seanleyne) Dmitry, What does the SQL standard say about case sensitivity of Boolean constants? PostgreSQL (http://www.postgresql.org/docs/8.1/static/datatype-boolean.html), to my reading, describes the only valid non-string/non-quoted representations for Booleans as TRUE and FALSE. So, while 'true' and 'false' are a valid Boolean string values, "false" or "true" are not valid non-string representations. |
Commented by: @dyemanov Adriano, given our legacy dialect-related issues, I woudn't take +-*/ as good examples, they have hard historical heritage. But speaking practically, I think it would be OK for your cases to throw an error. Sean, "something" is an identifier, so double quotes are out of question. |
Commented by: Sean Leyne (seanleyne) Dmitry, I was trying to use " to denote a non-quoted literal (not as an identifier), as in:
is valid (since FALSE and 'true' are valid Boolean values) whereas
would be invalid (since false is not a valid Boolean value) . |
Commented by: @dyemanov In your terms, both "FALSE" and "false" mean the same in our grammar (and this is OK from the standard POV), so I suppose this implies that both 'FALSE' and 'false' should be impicitly converted to boolean without errors. The standard is not absolutely clear about that, but its rules for CAST declare that if the string to be converted from matches <literal> element, it should be converted. <literal> for booleans define one of: TRUE, FALSE, UNKNOWN. But following the generic grammar, these tokens are case-insensitive. |
Commented by: Sean Leyne (seanleyne) Dmitry, Thanks for the clarification. Based on same, it seems that of Adriano's examples, these are valid:
Personally, I am not sure about: A strict reading of the PostgreSQL pages suggest that this is not valid, the correct syntax being IS NOT 'true' |
Modified by: @asfernandesComponent: Engine [ 10000 ] summary: Allow implicit conversion of boolean values to string => Allow implicit conversion between boolean and string |
Commented by: @pavel-zotov Can anyone clarify what's wrong here: select 'false' <> not false from rdb$database; -- output: <true>; expected But: select true = not 'false' from rdb$database; select true is not 'false' from rdb$database; PS. Checked on: WI-T4.0.0.119 |
Commented by: @pavel-zotov One more sample, without 'not': SQL> select true = 'true' from rdb$database; -- output: <true>; expected SQL> select true is 'true' from rdb$database; 'IS' does not like string literal in the right part of expression ? |
Commented by: @asfernandes Pavel, true is 'true' is invalid. The operator is IS [NOT] {TRUE | FALSE} not IS [NOT] <value> not 'false' as I asked, together with AND/OR, does not allow non boolean argument. |
Commented by: @pavel-zotov <boolean literal> ::= TRUE | FALSE | UNKNOWN What about literal UNKNOWN usage ? Should it be always treated as NULL ? SQL> select true is null from rdb$database; -- <false> -- OK, expected SQL> select 'true' is null from rdb$database; -- <false> -- OK, expected SQL> select true is unknown from rdb$database; -- <false> -- OK, expected SQL> select 'true' is unknown from rdb$database; SQL> select 'true' is true from rdb$database; -- <true> SQL> select 'true' is false from rdb$database; -- <false> PS. WI-T4.0.0.127 |
Commented by: @pavel-zotov Am I right in guess that 'UNKNOW' (enclosed in single quotes) can be used on when it's compared with UNKNOWN literal and _not_ with TRUE | FALSE ? select unknown in ('unknown', 'false', 'true') from rdb$database; -- stdOut: <null> select true in ('unknown', 'false', 'true') from rdb$database; -- stdErr: SQLSTATE = 22018 // conversion error from string "unknown" |
Commented by: @pavel-zotov It seems that BETWEEN also has some troubles with boolean expressions: SQL> select true >= not true and true <= not false from rdb$database; -- <true>; OK SQL> select true between (not true) and (not false) from rdb$database; -- <true>; OK SQL> select true between not true and not false from rdb$database; SQL> select true between not true and (not false) from rdb$database; SQL> select true between (not true) and not false from rdb$database; |
Commented by: @asfernandes About: My question is not why about the first, but how both should be, as they should be equivalent. Following the logic of AND/OR giving an error as they are exclusive boolean operators, I tend to think that the correct should be raise error for both. |
Commented by: @asfernandes IMO, cast 'UNKNOWN' to boolean should raise an error like cast 'NULL' to date/number does. May be not what the standard say, but give the fact about all differences between Firebird and the standard in relation to everything about this matter discussed here... |
Commented by: @asfernandes About BETWEEN, if we allow every crazy construct there, parser conflicts explodes. |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovstatus: Closed [ 6 ] => Closed [ 6 ] Test Details: Checked on WI-T4.0.0.141; WI-V3.0.0.32490 |
Modified by: @dyemanovFix Version: 3.0.1 [ 10730 ] |
Submitted by: Alex Bekhtin (afgm)
Edited subject to say about the more general case - Adriano.
select '' || cast( 1 as SMALLINT ) from rdb$database;
select '' || cast( 1 as INTEGER ) from rdb$database;
select '' || cast( 1 as BIGINT ) from rdb$database;
select '' || cast( TRUE as BOOLEAN ) from rdb$database; -- Overflow occurred during data type conversion.
-- conversion error from string "BOOLEAN".
select '' || cast( 1.1 as FLOAT ) from rdb$database;
select '' || cast( 1.1 as DOUBLE PRECISION ) from rdb$database;
select '' || cast( 1.1 as NUMERIC(3,3) ) from rdb$database;
select '' || cast( 1.1 as DECIMAL(3,3) ) from rdb$database;
select '' || cast( '2015-01-01' as DATE ) from rdb$database;
select '' || cast( '10:51:59' as TIME ) from rdb$database;
select '' || cast( '2015-01-01 10:51:59' as TIMESTAMP ) from rdb$database;
select '' || cast( 'char text' as CHAR(20) ) from rdb$database;
select '' || cast( 'varchar text' as VARCHAR(20) ) from rdb$database;
select '' || cast( 'blob text' as BLOB ) from rdb$database;
select cast( TRUE as varchar(20) ) from rdb$database; -- works fine
Commits: 1635a93 251ec1c d7eb6fe
====== Test Details ======
Checked on WI-T4.0.0.141; WI-V3.0.0.32490
The text was updated successfully, but these errors were encountered: