Derby
  1. Derby
  2. DERBY-4692

Unions between BOOLEAN and non-BOOLEAN datatypes should be rejected

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major 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
    • Labels:
      None
    • Issue & fix info:
      Patch Available, Repro attached
    • Bug behavior facts:
      Deviation from standard

      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

          Activity

          Rick Hillegas created issue -
          Rick Hillegas made changes -
          Field Original Value New Value
          Link This issue relates to DERBY-4583 [ DERBY-4583 ]
          Rick Hillegas made changes -
          Link This issue relates to DERBY-4684 [ DERBY-4684 ]
          Rick Hillegas made changes -
          Link This issue is part of DERBY-499 [ DERBY-499 ]
          Hide
          Rick Hillegas added a comment -

          Attaching derby-4692-01-aa-badUnions.diff. This patch prevents you from UNIONing BOOLEAN and non-BOOLEAN types. Regression tests passed cleanly for me after applying this patch.

          I am not sure that Derby is enforcing the Standard rules for UNION compatibility. Derby may be enforcing a weaker set of rules. Bringing Derby into full compliance with the Standard could break many existing applications so I do not recommend fixing this logic. However, as we re-enable BOOLEAN, it makes sense to me that the rules for BOOLEAN should be correct.

          Touches the following files:

          ---------

          M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java

          Make sure that if either side of a UNION is BOOLEAN, then both sides are BOOLEAN.

          ---------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java

          Change this regression test to reject the UNION queries which allowed mixing of BOOLEAN and non-BOOLEAN values.

          Show
          Rick Hillegas added a comment - Attaching derby-4692-01-aa-badUnions.diff. This patch prevents you from UNIONing BOOLEAN and non-BOOLEAN types. Regression tests passed cleanly for me after applying this patch. I am not sure that Derby is enforcing the Standard rules for UNION compatibility. Derby may be enforcing a weaker set of rules. Bringing Derby into full compliance with the Standard could break many existing applications so I do not recommend fixing this logic. However, as we re-enable BOOLEAN, it makes sense to me that the rules for BOOLEAN should be correct. Touches the following files: --------- M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Make sure that if either side of a UNION is BOOLEAN, then both sides are BOOLEAN. --------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java Change this regression test to reject the UNION queries which allowed mixing of BOOLEAN and non-BOOLEAN values.
          Rick Hillegas made changes -
          Attachment derby-4692-01-aa-badUnions.diff [ 12446357 ]
          Rick Hillegas made changes -
          Issue & fix info [Repro attached] [Patch Available, Repro attached]
          Hide
          Rick Hillegas added a comment -

          Committed patch at subversion revision 952263.

          Show
          Rick Hillegas added a comment - Committed patch at subversion revision 952263.
          Rick Hillegas made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Dag H. Wanvik made changes -
          Assignee Rick Hillegas [ rhillegas ]
          Rick Hillegas made changes -
          Fix Version/s 10.7.0.0 [ 12314971 ]
          Rick Hillegas made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Rick Hillegas made changes -
          Fix Version/s 10.7.1.1 [ 12315564 ]
          Fix Version/s 10.7.1.0 [ 12314971 ]
          Gavin made changes -
          Workflow jira [ 12512555 ] Default workflow, editable Closed status [ 12800274 ]

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development