[DERBY-4692] Unions between BOOLEAN and non-BOOLEAN datatypes should be rejected

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
• Fix Version/s: 10.7.1.1
• Component/s: SQL
• Issue & fix info: Patch Available, Repro attached
• Bug behavior facts: Deviation from standard
• Assignee: Richard N. Hillegas
• Reporter: Richard N. Hillegas
• Created: 04/Jun/10 15:09
• Updated: 29/Nov/10 14:22
• Resolved: 07/Jun/10 15:13

Description

DERBY-4684 fixed problems in implicit casts to BOOLEAN. However, the query which created the implicit casts should raise an error for other reasons:

select isindex from sys.sysconglomerates where conglomeratename = 'foo'
union
values ( 'true' )

This should fail because if either of the datatypes being UNIONed is BOOLEAN, then both should be BOOLEAN. Here is my reasoning, copied from the related discussion on DERBY:

1) The rules for determining whether two datatypes are union compatible are stated in the SQL Standard in part 2, section 7.3 (<query expression>), syntax rule 20.b.ii.

2) That, in turn, refers the reader to section 9.3 (Result of data type combinations).

3) Section 9.3, syntax rule 3.g says that if either of two values to be merged is BOOLEAN, then both must be BOOLEAN.

Issue Links

• is part of: DERBY-499 Expose BOOLEAN datatype to end users
• relates to: DERBY-4583 TRUE by itself is not accepted in WHERE
• relates to: DERBY-4684 Correct the implicit casting of other types to BOOLEAN