Issue Details (XML | Word | Printable)

Key: NUCCORE-644
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Unassigned
Reporter: Fiaz Hossain
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Core

JPQL "NOT IN (subquery)" is not generically compiled correctly

Created: 14/Feb/11 08:01 AM   Updated: 07/Mar/11 05:34 PM   Resolved: 14/Feb/11 09:53 AM
Component/s: Queries
Affects Version/s: 2.2.2, 3.0.0.m1
Fix Version/s: 2.2.3, 3.0.0.m2

Severity: Development


 Description  « Hide
The following JPQL query -
select a from A where a.id NOT IN (select b.a_id from B b)
does not retain the NOT after parsing. The problem is in JPQLParser.processInExpression(boolean not) method. For the case of a subquery the NOT is not used at all. A possible fix would be -

Fix 1 -
@@ -836,6 +830,12 @@
             Node subqueryNode = stack.pop();
             inNode.appendChildNode(subqueryNode);
             stack.push(inNode);
+ if (not)
+ {
+ Node notNode = new Node(NodeType.OPERATOR, "!");
+ notNode.insertChildNode(stack.pop());
+ stack.push(notNode);
+ }
             return;
         }

However generating NOT IN SQL out of subsequent expression is a bit tricky. It would be much easier if the operator is set to "NOT IN" as in fix 2.

Fix 2 -
@@ -829,7 +823,7 @@
         if (!p.parseChar('('))
         {
             // Subquery
- Node inNode = new Node(NodeType.OPERATOR, "IN");
+ Node inNode = new Node(NodeType.OPERATOR, not ? "NOT IN" : "IN");
             inNode.appendChildNode(inputNode);
 
             processExpression(); // subquery variable

Fix 2 requires additional change in Expression.java
@@ -194,6 +194,8 @@
     public static final DyadicOperator OP_CAST = new DyadicOperator("CAST", 3, false);
     /** IN **/
     public static final DyadicOperator OP_IN = new DyadicOperator("IN", 3, false);
+ /** NOT IN **/
+ public static final DyadicOperator OP_NOTIN = new DyadicOperator("NOT IN", 3, false);
     /** ADD **/
     public static final DyadicOperator OP_ADD = new DyadicOperator("+", 4, true);
     /** SUB **/

And ExpressionCompiler.java
@@ -249,6 +243,12 @@
             Expression right = compileExpression(node.getNextChild());
             return new DyadicExpression(left, Expression.OP_IN, right);
         }
+ else if (isOperator(node, "NOT IN"))
+ {
+ Expression left = compileExpression(node.getFirstChild());
+ Expression right = compileExpression(node.getNextChild());
+ return new DyadicExpression(left, Expression.OP_NOTIN, right);
+ }
         return compileAdditiveMultiplicativeExpression(node);
     }

The second fix should require no change to existing SQL generation code in most instances.

Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 14/Feb/11 09:53 AM
SVN for 2.2 and 3.0 have this working, including for RDBMS.