[DERBY-6584] Comparisons between BOOLEAN and other types sometimes succeed

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.10.2.0
• Component/s: SQL
• Issue & fix info: Release Note Needed
• Bug behavior facts: Deviation from standard
• Assignee: Unassigned
• Reporter: Knut Anders Hatlen
• Created: 23/May/14 11:49
• Updated: 26/May/14 15:36

Description

I would have expected the following queries to fail because of type mismatch between CHAR and BOOLEAN:

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

Similar queries using INTEGER values instead of CHAR values fail as expected:

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

Issue Links

• relates to: DERBY-4684 Correct the implicit casting of other types to BOOLEAN
• relates to: DERBY-4658 Allow explicit casts of string values to BOOLEAN