[
https://issues.apache.org/jira/browse/DERBY-6584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14007234#comment-14007234
]
Rick Hillegas commented on DERBY-6584:
--------------------------------------
Hi Knut,
As I read section 8.2 (comparison predicate), it seems to me that the detail is
described under the general rules. Comparisons are only defined in the
following situations:
o Both operands are numbers (general rule 2)
o Both operands are character strings (general rule 3)
o Both operands are binary strings (general rules 4 and 5)
o Both operands are date times (general rule 6)
o Both operands are intervals (general rule 7)
o Both operands are boolean (general rule 8)
Otherwise, the comparison is not defined. I don't see any rules for implicitly
casting operands between these types. I don't see any guidance about what
action should be taken if the comparison is not defined by general rules 2-8.
My gut feeling is that if the comparison is not defined by general rules 2-8,
then the statement is meaningless and an error should be thrown.
> 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)
