DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
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.

Andy Jefferson made changes - 14/Feb/11 09:40 AM
Field Original Value New Value
Summary JPQL anti joins using NOT IN subquery are not processed correctly JPQL "NOT IN (subquery)" is not generically compiled correctly
Affects Version/s 3.0.0.m1 [ 11060 ]
Affects Version/s 2.2.2 [ 11130 ]
Affects Version/s 2.2.3 [ 11135 ]
Affects Version/s 3.0.0.m2 [ 11150 ]
Environment Ubuntu Linux
Andy Jefferson added a comment - 14/Feb/11 09:53 AM
SVN for 2.2 and 3.0 have this working, including for RDBMS.

Andy Jefferson made changes - 14/Feb/11 09:53 AM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 2.2.3 [ 11135 ]
Fix Version/s 3.0.0.m2 [ 11150 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 07/Mar/11 05:34 PM
Status Resolved [ 5 ] Closed [ 6 ]