Derby
  1. Derby
  2. DERBY-5972

Grammar doesn't accept OR operator without parentheses

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.10.1.1
    • Fix Version/s: 10.8.3.3, 10.9.2.2, 10.10.1.1
    • Component/s: SQL
    • Labels:
      None

      Description

      The grammar doesn't accept the OR operator without parentheses in some constructs.

      For example, in a select list:

      ij> create table t(b1 boolean, b2 boolean);
      0 rows inserted/updated/deleted
      ij> select b1 or b2 from t;
      ERROR 42X01: Syntax error: Encountered "or" at line 1, column 11. (errorCode = 30000)
      Issue the 'help' command for general information on IJ command syntax.
      Any unrecognized commands are treated as potential SQL commands and executed directly.
      Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.

      However, it does not fail if OR is replaced by AND, or if parentheses are used around the OR expression:

      ij> select (b1 or b2) from t;
      1


      0 rows selected
      ij> select b1 and b2 from t;
      1


      0 rows selected

      Similar behaviour is seen in VALUES statements:

      ij> values true or false;
      ERROR 42X01: Syntax error: Encountered "or" at line 1, column 13. (errorCode = 30000)
      Issue the 'help' command for general information on IJ command syntax.
      Any unrecognized commands are treated as potential SQL commands and executed directly.
      Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
      ij> values (true or false);
      1


      true

      1 row selected
      ij> values true and false;
      1


      false

      1 row selected

      1. d5972-1a.diff
        6 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Knut Anders Hatlen created issue -
          Hide
          Knut Anders Hatlen added a comment - - edited

          I think "select b1 or b2 from t" is valid syntax.

          Starting with this rule in the SQL:2003 standard, part 2, section 7.12 <query specification>

          <select list> ::=
          <asterisk>

          <select sublist> [ { <comma> <select sublist> }

          ... ]

          and following the rules via <select sublist>, <derived column> and <value expression> brings you to

          6.34 <boolean value expression>

          <boolean value expression> ::=
          <boolean term>

          <boolean value expression> OR <boolean term>

          So it looks to me as if parentheses are not required by the standard for OR expressions in the select list.

          Show
          Knut Anders Hatlen added a comment - - edited I think "select b1 or b2 from t" is valid syntax. Starting with this rule in the SQL:2003 standard, part 2, section 7.12 <query specification> <select list> ::= <asterisk> <select sublist> [ { <comma> <select sublist> } ... ] and following the rules via <select sublist>, <derived column> and <value expression> brings you to 6.34 <boolean value expression> <boolean value expression> ::= <boolean term> <boolean value expression> OR <boolean term> So it looks to me as if parentheses are not required by the standard for OR expressions in the select list.
          Hide
          Knut Anders Hatlen added a comment -

          It looks to me as if sqlgrammar.jj uses orExpression() in some production rules where it should have used valueExpression(), for example in the derivedColumn() rule used by selectSublist(). Since orExpression() is not actually an expression involving OR, as one might expect from its name. It's the expression that represents one of the operands in an OR expression.

          Show
          Knut Anders Hatlen added a comment - It looks to me as if sqlgrammar.jj uses orExpression() in some production rules where it should have used valueExpression(), for example in the derivedColumn() rule used by selectSublist(). Since orExpression() is not actually an expression involving OR, as one might expect from its name. It's the expression that represents one of the operands in an OR expression.
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Assignee Knut Anders Hatlen [ knutanders ]
          Knut Anders Hatlen made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Hide
          Knut Anders Hatlen added a comment -

          The attached patch changes occurrences of orExpression(null) with valueExpression() in sqlgrammar.jj. Since the orExpression() rule only matches conjunctions of boolean primaries and valueExpression() matches disjunctions of orExpression(), this change makes it possible to both OR and AND expressions where only AND expressions could be used previously.

          The patch changes the following production rules:

          derivedColumn(): This rule is used in select lists. The change allows statements like "SELECT b1 OR b2 FROM t".

          methodParameter(): Used for parameters in function/procedure calls. Makes statements like "VALUES booleanFunction(TRUE OR FALSE)" work.

          updateSource(): Used in UPDATE statements and allows statements like: "UPDATE t SET b = TRUE OR FALSE"

          rowValueConstructorElement(): Used in VALUES statements and allows statements like: VALUES TRUE OR FALSE

          whenThenExpression(): This production rule actually worked before, because it had inlined valueExpression(). The patch changes it to call valueExpression() instead of duplicating the code. Added a test case for CASE WHEN ... THEN ... ELSE to verify that it still works.

          With these changes, only the valueExpression() rule uses the orExpression() rule directly. The patch also adds test cases for all the expressions mentioned above.

          I have only run BooleanValuesTest so far. Will run the full regression test suite.

          Show
          Knut Anders Hatlen added a comment - The attached patch changes occurrences of orExpression(null) with valueExpression() in sqlgrammar.jj. Since the orExpression() rule only matches conjunctions of boolean primaries and valueExpression() matches disjunctions of orExpression(), this change makes it possible to both OR and AND expressions where only AND expressions could be used previously. The patch changes the following production rules: derivedColumn(): This rule is used in select lists. The change allows statements like "SELECT b1 OR b2 FROM t". methodParameter(): Used for parameters in function/procedure calls. Makes statements like "VALUES booleanFunction(TRUE OR FALSE)" work. updateSource(): Used in UPDATE statements and allows statements like: "UPDATE t SET b = TRUE OR FALSE" rowValueConstructorElement(): Used in VALUES statements and allows statements like: VALUES TRUE OR FALSE whenThenExpression(): This production rule actually worked before, because it had inlined valueExpression(). The patch changes it to call valueExpression() instead of duplicating the code. Added a test case for CASE WHEN ... THEN ... ELSE to verify that it still works. With these changes, only the valueExpression() rule uses the orExpression() rule directly. The patch also adds test cases for all the expressions mentioned above. I have only run BooleanValuesTest so far. Will run the full regression test suite.
          Knut Anders Hatlen made changes -
          Attachment d5972-1a.diff [ 12552128 ]
          Hide
          Knut Anders Hatlen added a comment -

          All the regression tests passed with the patch.

          Show
          Knut Anders Hatlen added a comment - All the regression tests passed with the patch.
          Knut Anders Hatlen made changes -
          Issue & fix info Patch Available [ 10102 ]
          Hide
          Knut Anders Hatlen added a comment -

          Committed revision 1408136.

          I'm not planning to back-port the fix. It changes grammar rules used by most queries, so I'd prefer to keep it on trunk only for a while until we're confident that it doesn't inadvertently break anything. Resolving the issue for now.

          Show
          Knut Anders Hatlen added a comment - Committed revision 1408136. I'm not planning to back-port the fix. It changes grammar rules used by most queries, so I'd prefer to keep it on trunk only for a while until we're confident that it doesn't inadvertently break anything. Resolving the issue for now.
          Knut Anders Hatlen made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Issue & fix info Patch Available [ 10102 ]
          Fix Version/s 10.10.0.0 [ 12321550 ]
          Resolution Fixed [ 1 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 12732078 ] Default workflow, editable Closed status [ 12802894 ]
          Hide
          Kathey Marsden added a comment -

          Assign to myself temporarily for backport

          Show
          Kathey Marsden added a comment - Assign to myself temporarily for backport
          Kathey Marsden made changes -
          Assignee Knut Anders Hatlen [ knutanders ] Kathey Marsden [ kmarsden ]
          Hide
          ASF subversion and git services added a comment -

          Commit 1509207 from Kathey Marsden in branch 'code/branches/10.9'
          [ https://svn.apache.org/r1509207 ]

          (DERBY-5972) Grammar doesn't accept OR operator without parentheses

          backport 1408136 from trunk to 10.9
          Contributed by Knut Anders Hatlen

          Show
          ASF subversion and git services added a comment - Commit 1509207 from Kathey Marsden in branch 'code/branches/10.9' [ https://svn.apache.org/r1509207 ] ( DERBY-5972 ) Grammar doesn't accept OR operator without parentheses backport 1408136 from trunk to 10.9 Contributed by Knut Anders Hatlen
          Hide
          ASF subversion and git services added a comment -

          Commit 1509375 from Kathey Marsden in branch 'code/branches/10.8'
          [ https://svn.apache.org/r1509375 ]

          DERBY-5972 - Grammar doesn't accept OR operator without parentheses

          backport 1408136 from trunk to 10.8
          Contributed by Knut Anders Hatlen

          Show
          ASF subversion and git services added a comment - Commit 1509375 from Kathey Marsden in branch 'code/branches/10.8' [ https://svn.apache.org/r1509375 ] DERBY-5972 - Grammar doesn't accept OR operator without parentheses backport 1408136 from trunk to 10.8 Contributed by Knut Anders Hatlen
          Hide
          Kathey Marsden added a comment -

          backport to 10.8 complete reassigning to Knut Anders

          Show
          Kathey Marsden added a comment - backport to 10.8 complete reassigning to Knut Anders
          Kathey Marsden made changes -
          Assignee Kathey Marsden [ kmarsden ] Knut Anders Hatlen [ knutanders ]
          Fix Version/s 10.8.3.1 [ 12323475 ]
          Fix Version/s 10.9.2.2 [ 12323562 ]
          Kathey Marsden made changes -
          Link This issue relates to DERBY-6289 [ DERBY-6289 ]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-6423 [ DERBY-6423 ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open In Progress In Progress
          5d 4h 45m 1 Knut Anders Hatlen 05/Nov/12 17:11
          In Progress In Progress Resolved Resolved
          6d 5h 43m 1 Knut Anders Hatlen 11/Nov/12 22:55
          Resolved Resolved Closed Closed
          217d 10h 31m 1 Knut Anders Hatlen 17/Jun/13 10:27

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development