CODEWITHSUNDEEP
javajpaormjpa-2.0jpql
user2490565
user2490565

Reputation: 11

JPQL Exception for composite key

I am unable to know the solution for 2 JPQL Exception

  1. subquery must return only one column The field
  2. [PARENTENTITY.PARENTID2] in this expression has an invalid table in this context for following entity --
    class ParentEntity{
      @Id
      String parentId1;
      @Id
      String parentId2;
      @ManyToOne
      ChildEntity childEntityRef;
    }

    class ChildEntity {  
      @Id
      String childId1; 
      @Id
      String childId2;
    }

when i tried Query - SELECT me.childId1,me.childId2 FROM ChildEntity as me where me <> ALL(Select pe.childEntityRef From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 ) it gives following exception -

Caused by: Exception [EclipseLink-6069] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.QueryException
Exception Description: The field [PARENTENTITY.PARENTID2] in this expression has an invalid table in this context.
    at org.eclipse.persistence.exceptions.QueryException.invalidTableForFieldInExpression(QueryException.java:712)
    at org.eclipse.persistence.internal.expressions.FieldExpression.validateNode(FieldExpression.java:277)
    at org.eclipse.persistence.expressions.Expression.normalize(Expression.java:2978)
    at org.eclipse.persistence.internal.expressions.DataExpression.normalize(DataExpression.java:342)
    at org.eclipse.persistence.internal.expressions.FieldExpression.normalize(FieldExpression.java:205)
    at org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:218)

when i tried Query - SELECT me.childId1,me.childId2 FROM ChildEntity as me where me <> (Select pe.childEntityRef From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 ) it gives following exception -

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: subquery must return only one column
Error Code: 0
Call: SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE ( <> (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2 LEFT OUTER JOIN CHILDENTITY t1 ON ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1)) WHERE ((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?))))
    bind => [2 parameters bound]
Query: ReportQuery(referenceClass=ChildEntity sql="SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE ( <> (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2 LEFT OUTER JOIN CHILDENTITY t1 ON ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1)) WHERE ((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?))))")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
Caused by: org.postgresql.util.PSQLException: ERROR: subquery must return only one column
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)

Upvotes: 1

Views: 1946

Answers (2)

Gaurav Kumar
Gaurav Kumar

Reputation: 333

James , Please clarify about the exception defined below (In response to your query described) ... Using Eclipselink 2.5 release [eclipselink-2.5.0.v20130507-3faac2b]

JPA Query 1) SELECT me.childId1,me.childId2 FROM ChildEntity as me where not exist (Select pe.parentId1 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 and pe.childEntityRef = me)

Output-

    Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [SELECT me.childId1,me.childId2 FROM ChildEntity as me where not exist (Select pe.parentId1 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 and pe.childEntityRef = me)], line 1, column 70: unexpected token [(].
Internal Exception: NoViableAltException(81!=[652:1: simpleConditionalExpressionRemainder[Object left] returns [Object node] : (n= comparisonExpression[left] | (n1= NOT )? n= conditionWithNotExpression[(n1!=null), left] | IS (n2= NOT )? n= isExpression[(n2!=null), left] );])
    at org.eclipse.persistence.exceptions.JPQLException.unexpectedToken(JPQLException.java:372)
    at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.handleRecognitionException(JPQLParser.java:319)
    at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.addError(JPQLParser.java:245)

JPA Query 2) SELECT me.childId1,me.childId2 FROM ChildEntity as me where not exist (Select pe.parentId1 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 and pe.childEntityRef.childId1 = me.childId1 and pe.childEntityRef.childId2 = me.childId2)

Output-

 Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [SELECT me.childId1,me.childId2 FROM ChildEntity as me where not exist (Select pe.parentId1 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 and pe.childEntityRef.childId1 = me.childId1 and pe.childEntityRef.childId2 = me.childId2)], line 1, column 70: unexpected token [(].
Internal Exception: NoViableAltException(81!=[652:1: simpleConditionalExpressionRemainder[Object left] returns [Object node] : (n= comparisonExpression[left] | (n1= NOT )? n= conditionWithNotExpression[(n1!=null), left] | IS (n2= NOT )? n= isExpression[(n2!=null), left] );])
    at org.eclipse.persistence.exceptions.JPQLException.unexpectedToken(JPQLException.java:372)
    at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.handleRecognitionException(JPQLParser.java:319)
    at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.addError(JPQLParser.java:245)

JPA Query 3) SELECT me.childId1,me.childId2 FROM ChildEntity as me where (me.childId1,me.childId2) NOT IN (Select pe.childEntityRef.childId1, pe.childEntityRef.childId2 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 )

Output-

Caused by: Exception [EclipseLink-8024] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [SELECT me.childId1,me.childId2 FROM ChildEntity as me where (me.childId1,me.childId2) NOT IN (Select pe.childEntityRef.childId1, pe.childEntityRef.childId2 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 )], line 1, column 72: syntax error at [,].
Internal Exception: MismatchedTokenException(79!=82)
    at org.eclipse.persistence.exceptions.JPQLException.syntaxErrorAt(JPQLException.java:362)
    at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.handleRecognitionException(JPQLParser.java:304)

But PostgreSQL query working prefectly:

SQL Query 1) 

SELECT me.childId1,me.childId2 FROM ChildEntity as me where NOT EXISTS (
Select pe.parentId1 From ParentEntity as pe where pe.parentId1=? and pe.parentId2=?
 and pe.childId1 = me.childId1 and pe.childId2 = me.childId2)

SQL Query 2) 

SELECT me.childId1,me.childId2 FROM ChildEntity as me where (me.childId1,me.childId2) NOT IN (
Select pe.childId1, pe.childId2  From ParentEntity as pe where pe.parentId1=? and pe.parentId2=?)

Updated :

For 3rd JPA Query ) SELECT me.childId1,me.childId2 FROM ChildEntity as me where (me.childId1,me.childId2) NOT IN (Select pe.childEntityRef.childId1, pe.childEntityRef.childId2 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 )

Output-

[EL Warning]: 2013-06-18 19:21:54.407--UnitOfWork(15545028)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "childentity"
Error Code: 0
Exception in thread "main" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Call: SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE (CHILDENTITY.CHILDID1, CHILDENTITY.CHILDID2) NOT IN (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2, CHILDENTITY t1 WHERE (((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?)) AND ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1))))
Internal Exception: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "childentity"
    bind => [2 parameters bound]
Error Code: 0
Query: ReportQuery(referenceClass=ChildEntity sql="SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE (CHILDENTITY.CHILDID1, CHILDENTITY.CHILDID2) NOT IN (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2, CHILDENTITY t1 WHERE (((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?)) AND ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1))))")
Call: SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE (CHILDENTITY.CHILDID1, CHILDENTITY.CHILDID2) NOT IN (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2, CHILDENTITY t1 WHERE (((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?)) AND ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1))))
    bind => [2 parameters bound]
Query: ReportQuery(referenceClass=ChildEntity sql="SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE (CHILDENTITY.CHILDID1, CHILDENTITY.CHILDID2) NOT IN (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2, CHILDENTITY t1 WHERE (((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?)) AND ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1))))")
    at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:377)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:468)
    at com.NewClass.main(NewClass.java:27)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "childentity"
Error Code: 0
Call: SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE (CHILDENTITY.CHILDID1, CHILDENTITY.CHILDID2) NOT IN (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2, CHILDENTITY t1 WHERE (((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?)) AND ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1))))
    bind => [2 parameters bound]
Query: ReportQuery(referenceClass=ChildEntity sql="SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE (CHILDENTITY.CHILDID1, CHILDENTITY.CHILDID2) NOT IN (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2, CHILDENTITY t1 WHERE (((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?)) AND ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1))))")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:679)
Caused by: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "childentity"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)

Postgresql Query ) SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE (CHILDENTITY.CHILDID1, CHILDENTITY.CHILDID2) NOT IN (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2, CHILDENTITY t1 WHERE (((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?)) AND ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1))))

Output-

ERROR:  invalid reference to FROM-clause entry for table "childentity"
LINE 3: ....CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE (CHILDENTIT...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "t0".

********** Error **********

ERROR: invalid reference to FROM-clause entry for table "childentity"
SQL state: 42P01
Hint: Perhaps you meant to reference the table alias "t0".
Character: 62

Updated Postgresql Query ) -- Alias CHILDENTITY replaced by t0 then working perfectly -

 SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE (t0.CHILDID1, t0.CHILDID2) NOT IN (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2, CHILDENTITY t1 WHERE (((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?)) AND ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1))))

Upvotes: 0

James
James

Reputation: 18379

Try using the 2.4 or 2.5 release, there have been a lot of JPQL enhancements and fixes. Although what you are trying doing my still not be possible the way you are trying to do it.

Try simplifying your query,

SELECT me.childId1,me.childId2 FROM ChildEntity as me where not exists (Select pe.parentId1 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 and pe.childEntityRef = me)

or,

SELECT me.childId1,me.childId2 FROM ChildEntity as me where not exists (Select pe.parentId1 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 and pe.childEntityRef.childId1 = me.childId1 and pe.childEntityRef.childId2 = me.childId2)

In 2.5 you should also be able to do,

SELECT me.childId1,me.childId2 FROM ChildEntity as me where (me.childId1, mew.childId2) NOT IN (Select pe.childEntityRef.childId1, pe.childEntityRef.childId2 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 )

Upvotes: 1

Related Questions