You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2012/10/31 13:27:12 UTC
[jira] [Created] (DERBY-5972) Grammar doesn't accept OR operator
without parentheses
Knut Anders Hatlen created DERBY-5972:
-----------------------------------------
Summary: Grammar doesn't accept OR operator without parentheses
Key: DERBY-5972
URL: https://issues.apache.org/jira/browse/DERBY-5972
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.10.0.0
Reporter: Knut Anders Hatlen
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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Comment Edited] (DERBY-5972) Grammar doesn't accept OR
operator without parentheses
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13487713#comment-13487713 ]
Knut Anders Hatlen edited comment on DERBY-5972 at 11/5/12 3:22 PM:
--------------------------------------------------------------------
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.
was (Author: knutanders):
I think "select b1 or b2 from t".
Staring 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.
> Grammar doesn't accept OR operator without parentheses
> ------------------------------------------------------
>
> Key: DERBY-5972
> URL: https://issues.apache.org/jira/browse/DERBY-5972
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Knut Anders Hatlen
>
> 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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5972) Grammar doesn't accept OR operator
without parentheses
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13490690#comment-13490690 ]
Knut Anders Hatlen commented on DERBY-5972:
-------------------------------------------
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.
> Grammar doesn't accept OR operator without parentheses
> ------------------------------------------------------
>
> Key: DERBY-5972
> URL: https://issues.apache.org/jira/browse/DERBY-5972
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Knut Anders Hatlen
>
> 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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5972) Grammar doesn't accept OR operator
without parentheses
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5972?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5972:
--------------------------------------
Attachment: d5972-1a.diff
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.
> Grammar doesn't accept OR operator without parentheses
> ------------------------------------------------------
>
> Key: DERBY-5972
> URL: https://issues.apache.org/jira/browse/DERBY-5972
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Attachments: d5972-1a.diff
>
>
> 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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Assigned] (DERBY-5972) Grammar doesn't accept OR operator
without parentheses
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5972?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reassigned DERBY-5972:
-----------------------------------------
Assignee: Knut Anders Hatlen
> Grammar doesn't accept OR operator without parentheses
> ------------------------------------------------------
>
> Key: DERBY-5972
> URL: https://issues.apache.org/jira/browse/DERBY-5972
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
>
> 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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5972) Grammar doesn't accept OR operator
without parentheses
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13487713#comment-13487713 ]
Knut Anders Hatlen commented on DERBY-5972:
-------------------------------------------
I think "select b1 or b2 from t".
Staring 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.
> Grammar doesn't accept OR operator without parentheses
> ------------------------------------------------------
>
> Key: DERBY-5972
> URL: https://issues.apache.org/jira/browse/DERBY-5972
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Knut Anders Hatlen
>
> 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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Resolved] (DERBY-5972) Grammar doesn't accept OR operator
without parentheses
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5972?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen resolved DERBY-5972.
---------------------------------------
Resolution: Fixed
Fix Version/s: 10.10.0.0
Issue & fix info: (was: Patch Available)
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.
> Grammar doesn't accept OR operator without parentheses
> ------------------------------------------------------
>
> Key: DERBY-5972
> URL: https://issues.apache.org/jira/browse/DERBY-5972
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Fix For: 10.10.0.0
>
> Attachments: d5972-1a.diff
>
>
> 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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5972) Grammar doesn't accept OR operator
without parentheses
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5972?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5972:
--------------------------------------
Issue & fix info: Patch Available
All the regression tests passed with the patch.
> Grammar doesn't accept OR operator without parentheses
> ------------------------------------------------------
>
> Key: DERBY-5972
> URL: https://issues.apache.org/jira/browse/DERBY-5972
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Attachments: d5972-1a.diff
>
>
> 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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira