You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Lukas Eder (Created) (JIRA)" <ji...@apache.org> on 2011/10/09 12:40:31 UTC
[jira] [Created] (DERBY-5451) Allow for casting numeric types to
BOOLEAN
Allow for casting numeric types to BOOLEAN
------------------------------------------
Key: DERBY-5451
URL: https://issues.apache.org/jira/browse/DERBY-5451
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.8.1.2
Reporter: Lukas Eder
Priority: Minor
According to the casting matrix:
http://db.apache.org/derby/docs/10.8/ref/rrefsqlj33562.html
it is currently not possible to write CAST(1 as BOOLEAN). At the same time, casting CHAR and VARCHAR as BOOLEAN is possible, e.g. CAST('1' as BOOLEAN), or CAST(CAST(1 as CHAR(1)) as BOOLEAN). This was recently implemented:
https://issues.apache.org/jira/browse/DERBY-4658
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5451) Allow for casting numeric types to
BOOLEAN
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5451?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-5451:
-----------------------------------
Labels: cast conversion derby_triage10_10 sql syntax type typesystem (was: cast conversion sql syntax type typesystem)
> Allow for casting numeric types to BOOLEAN
> ------------------------------------------
>
> Key: DERBY-5451
> URL: https://issues.apache.org/jira/browse/DERBY-5451
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.1.2
> Reporter: Lukas Eder
> Priority: Minor
> Labels: cast, conversion, derby_triage10_10, sql, syntax, type, typesystem
>
> According to the casting matrix:
> http://db.apache.org/derby/docs/10.8/ref/rrefsqlj33562.html
> it is currently not possible to write CAST(1 as BOOLEAN). At the same time, casting CHAR and VARCHAR as BOOLEAN is possible, e.g. CAST('1' as BOOLEAN), or CAST(CAST(1 as CHAR(1)) as BOOLEAN). This was recently implemented:
> https://issues.apache.org/jira/browse/DERBY-4658
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5451) Allow for casting numeric types to
BOOLEAN
Posted by "Lukas Eder (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5451?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13128191#comment-13128191 ]
Lukas Eder commented on DERBY-5451:
-----------------------------------
Also, most RDBMS also allow for treating '1' and '0' as booleans when casting:
CAST('1' as boolean) = CAST('true' as boolean)
CAST('0' as boolean) = CAST('false' as boolean)
Some databases go even further, e.g. Postgres:
CAST('y' as boolean) = CAST('true' as boolean)
CAST('yes' as boolean) = CAST('true' as boolean)
CAST('on' as boolean) = CAST('true' as boolean)
etc. See the documentation here: http://www.postgresql.org/docs/9.0/static/datatype-boolean.html
None of these values are currently supported in Derby
> Allow for casting numeric types to BOOLEAN
> ------------------------------------------
>
> Key: DERBY-5451
> URL: https://issues.apache.org/jira/browse/DERBY-5451
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.1.2
> Reporter: Lukas Eder
> Priority: Minor
> Labels: cast, conversion, sql, syntax, type, typesystem
>
> According to the casting matrix:
> http://db.apache.org/derby/docs/10.8/ref/rrefsqlj33562.html
> it is currently not possible to write CAST(1 as BOOLEAN). At the same time, casting CHAR and VARCHAR as BOOLEAN is possible, e.g. CAST('1' as BOOLEAN), or CAST(CAST(1 as CHAR(1)) as BOOLEAN). This was recently implemented:
> https://issues.apache.org/jira/browse/DERBY-4658
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5451) Allow for casting numeric types to
BOOLEAN
Posted by "Lukas Eder (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5451?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13128177#comment-13128177 ]
Lukas Eder commented on DERBY-5451:
-----------------------------------
Thanks for the explanation. I didn't realize the SQL standard was so restrictive.
With jOOQ (http://www.jooq.org), I am currently running integration tests against any of these 12 RDBMS:
- DB2
- Derby
- H2
- HSQLDB
- Ingres
- MySQL
- Oracle
- Postgres
- SQLite
- SQL Server
- Sybase ASE
- Sybase SQL Anywhere
Casting numeric types to boolean works in all RDBMS, except Derby and HSQLDB (and Oracle, which doesn't have a boolean SQL type). So a slight deviation from the standard might be worth thinking about.
> Allow for casting numeric types to BOOLEAN
> ------------------------------------------
>
> Key: DERBY-5451
> URL: https://issues.apache.org/jira/browse/DERBY-5451
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.1.2
> Reporter: Lukas Eder
> Priority: Minor
> Labels: cast, conversion, sql, syntax, type, typesystem
>
> According to the casting matrix:
> http://db.apache.org/derby/docs/10.8/ref/rrefsqlj33562.html
> it is currently not possible to write CAST(1 as BOOLEAN). At the same time, casting CHAR and VARCHAR as BOOLEAN is possible, e.g. CAST('1' as BOOLEAN), or CAST(CAST(1 as CHAR(1)) as BOOLEAN). This was recently implemented:
> https://issues.apache.org/jira/browse/DERBY-4658
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5451) Allow for casting numeric types to
BOOLEAN
Posted by "Rick Hillegas (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5451?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13124081#comment-13124081 ]
Rick Hillegas commented on DERBY-5451:
--------------------------------------
This seems like a useful improvement. However, note that numeric types can't be cast to BOOLEAN according to part 2 of the SQL Standard, section 6.12 (<cast specification>). According to the matrix there, only BOOLEANs, string types, UDTs, and Reference types can be cast to BOOLEAN.
> Allow for casting numeric types to BOOLEAN
> ------------------------------------------
>
> Key: DERBY-5451
> URL: https://issues.apache.org/jira/browse/DERBY-5451
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.1.2
> Reporter: Lukas Eder
> Priority: Minor
> Labels: cast, conversion, sql, syntax, type, typesystem
>
> According to the casting matrix:
> http://db.apache.org/derby/docs/10.8/ref/rrefsqlj33562.html
> it is currently not possible to write CAST(1 as BOOLEAN). At the same time, casting CHAR and VARCHAR as BOOLEAN is possible, e.g. CAST('1' as BOOLEAN), or CAST(CAST(1 as CHAR(1)) as BOOLEAN). This was recently implemented:
> https://issues.apache.org/jira/browse/DERBY-4658
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira