Derby
  1. Derby
  2. DERBY-4720

Allow boolean-valued expressions in select lists

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: 10.7.1.1
    • Component/s: SQL
    • Labels:
      None

      Description

      When the BOOLEAN datatype was disabled, the grammar was peppered with special checks to make sure that expressions like "a > 10" did not appear in the SELECT list. Perhaps this was done to reduce the number of cases in which BOOLEAN values could be materialized as columns in queries. That is, however, an unsatisfying reason because you could always materialize BOOLEAN columns by selecting from certain system tables. In any event, the SQL grammar allows these expressions in the SELECT list and we should re-enable them as part of exposing BOOLEAN as a full-fledged Derby datatype.

      I intend to re-enable these expressions per the SQL spec. If anyone knows of a reason why this should not be allowed, please speak up. Thanks.

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          Attaching derby-4720-01-ac-allowBooleanExpressions.diff. This patch makes it possible to use boolean-valued expressions in several situations required by the SQL Standard. The tests ran cleanly for me.

          The grammar was changed as follows:

          1) The inSelectClause arguments were rototilled out of productions which return ValueNode. Those arguments were added when the BOOLEAN datatype was disabled. Their purpose was to reject BOOLEAN-valued expressions in the situations listed in (2) below. This rototill accounts for most of the changes to the parser.

          2) Calls to additiveExpression() were replaced by calls to orExpression() in the following productions. This allows unparenthesized boolean expressions to appear in SELECT lists, as columns in VALUES constructors, as arguments to FUNCTIONs and PROCEDUREs, and as targets of SET clauses. The Derby productions do not map exactly to the productions in the SQL grammar. The orExpression() production lies above additiveExpression() in Derby's grammar. That is, it is more general. It is possible that an even more general production might bring us even closer to the SQL Standard. However, orExpression() seemed to me to be the minimal production required to allow unparenthesized boolean-valued expressions. Generalizing this further seems to me to be beyond the scope of this JIRA.

          i) derivedColumn() (SELECT list)

          ii) rowValueConstructor() (VALUES clause)

          iii) methodParameter() (routine arguments)

          iv) updateSource() (SET clauses)

          Touches the following files:

          ---------

          M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

          Grammar changes described above.

          ---------

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

          New test case. More extensive testing is provided by the revised tests below.

          ---------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/logop.sql
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/valuesclause.sql
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery2.sql
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql
          M java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
          M java/testing/org/apache/derbyTesting/functionTests/master/logop.out
          M java/testing/org/apache/derbyTesting/functionTests/master/valuesclause.out
          M java/testing/org/apache/derbyTesting/functionTests/master/subquery2.out
          M java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
          M java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out

          Changes to existing tests to account for the fact that boolean expressions can now appear in other situations required by the SQL Standard.

          Show
          Rick Hillegas added a comment - Attaching derby-4720-01-ac-allowBooleanExpressions.diff. This patch makes it possible to use boolean-valued expressions in several situations required by the SQL Standard. The tests ran cleanly for me. The grammar was changed as follows: 1) The inSelectClause arguments were rototilled out of productions which return ValueNode. Those arguments were added when the BOOLEAN datatype was disabled. Their purpose was to reject BOOLEAN-valued expressions in the situations listed in (2) below. This rototill accounts for most of the changes to the parser. 2) Calls to additiveExpression() were replaced by calls to orExpression() in the following productions. This allows unparenthesized boolean expressions to appear in SELECT lists, as columns in VALUES constructors, as arguments to FUNCTIONs and PROCEDUREs, and as targets of SET clauses. The Derby productions do not map exactly to the productions in the SQL grammar. The orExpression() production lies above additiveExpression() in Derby's grammar. That is, it is more general. It is possible that an even more general production might bring us even closer to the SQL Standard. However, orExpression() seemed to me to be the minimal production required to allow unparenthesized boolean-valued expressions. Generalizing this further seems to me to be beyond the scope of this JIRA. i) derivedColumn() (SELECT list) ii) rowValueConstructor() (VALUES clause) iii) methodParameter() (routine arguments) iv) updateSource() (SET clauses) Touches the following files: --------- M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Grammar changes described above. --------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java New test case. More extensive testing is provided by the revised tests below. --------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/logop.sql M java/testing/org/apache/derbyTesting/functionTests/tests/lang/valuesclause.sql M java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery2.sql M java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql M java/testing/org/apache/derbyTesting/functionTests/master/subquery.out M java/testing/org/apache/derbyTesting/functionTests/master/logop.out M java/testing/org/apache/derbyTesting/functionTests/master/valuesclause.out M java/testing/org/apache/derbyTesting/functionTests/master/subquery2.out M java/testing/org/apache/derbyTesting/functionTests/master/schemas.out M java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out Changes to existing tests to account for the fact that boolean expressions can now appear in other situations required by the SQL Standard.
          Hide
          Rick Hillegas added a comment -

          Committed patch at subversion revision 959991.

          Show
          Rick Hillegas added a comment - Committed patch at subversion revision 959991.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development