[
https://issues.apache.org/jira/browse/DERBY-6584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14007165#comment-14007165
]
Rick Hillegas commented on DERBY-6584:
--------------------------------------
Thanks, Knut. I mis-read the table in section 6.13. I confused B with BO. You
are right. The SQL Standard allows you to cast between BOOLEAN and character
types. The limitations on the string value are described by section 6.13,
general rules 10e, 11e, and 20.
> Comparisons between BOOLEAN and other types sometimes succeed
> -------------------------------------------------------------
>
> Key: DERBY-6584
> URL: https://issues.apache.org/jira/browse/DERBY-6584
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.2.0
> Reporter: Knut Anders Hatlen
>
> I would have expected the following queries to fail because of type mismatch
> between CHAR and BOOLEAN:
> {noformat}
> ij> values 'abc' = true, true = 'xyz', false = 'yes';
> 1
> -----
> true
> true
> false
> 3 rows selected
> ij> values 'abc' in (true, false), true in ('abc', 'def');
> 1
> -----
> true
> true
> 2 rows selected
> {noformat}
> Similar queries using INTEGER values instead of CHAR values fail as expected:
> {noformat}
> ij> values 1 = true;
> ERROR 42818: Comparisons between 'INTEGER' and 'BOOLEAN' are not supported.
> Types must be comparable. String types must also have matching collation. If
> collation does not match, a possible solution is to cast operands to force
> them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE
> CAST(tablename AS VARCHAR(128)) = 'T1')
> ij> values true = 1;
> ERROR 42818: Comparisons between 'BOOLEAN' and 'INTEGER' are not supported.
> Types must be comparable. String types must also have matching collation. If
> collation does not match, a possible solution is to cast operands to force
> them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE
> CAST(tablename AS VARCHAR(128)) = 'T1')
> ij> values true in (1,2,3);
> ERROR 42818: Comparisons between 'BOOLEAN' and 'INTEGER' are not supported.
> Types must be comparable. String types must also have matching collation. If
> collation does not match, a possible solution is to cast operands to force
> them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE
> CAST(tablename AS VARCHAR(128)) = 'T1')
> ij> values 1 in (true, false);
> ERROR 42818: Comparisons between 'INTEGER' and 'BOOLEAN' are not supported.
> Types must be comparable. String types must also have matching collation. If
> collation does not match, a possible solution is to cast operands to force
> them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE
> CAST(tablename AS VARCHAR(128)) = 'T1')
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.2#6252)
