db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6584) Comparisons between BOOLEAN and other types sometimes succeed
Date Fri, 23 May 2014 12:06:01 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6584?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Rick Hillegas updated DERBY-6584:
---------------------------------

    Bug behavior facts: Deviation from standard

Marking this as a deviation from the Standard. A BOOLEAN can only be cast to a BOOLEAN and
no other built-in datatype can be cast to a BOOLEAN. This is according to the table in part
2 of the 2011 SQL Standard, section 6.13 (cast specification), syntax rule 6. If you can't
explicitly cast between two datatypes, then you can't compare them.

The Derby Reference Manual section on "Data type assignments and comparison, sorting, and
ordering" says that these comparisons should be illegal.

> 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)

Mime
View raw message