[OPENJPA-2251] EXISTS with subquery from superclass generates SQL with syntax error

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.2.0
• Assignee: Unassigned
• Reporter: Vermeulen
• Created: 16/Aug/12 09:42
• Updated: 18/Aug/12 08:51

Description

I have an abstract entity Booking with @Inheritance(strategy = InheritanceType.TABLE_PER_CLASS).
It has a ManyToOne relation with Event.

The following JPQL query generates SQL with a syntax error:

SELECT z FROM Event z WHERE EXISTS (
SELECT booking.id FROM Booking booking
WHERE booking.event = z AND booking.remarks = :REMARKS
)

org.apache.openjpa.lib.jdbc.ReportingSQLException: Incorrect syntax near the keyword 'FROM'.

{prepstmnt 17234903 SELECT t0.id, ...(many more columns)..., t0.USERCREATED_ID FROM booking_Event t0 LEFT OUTER JOIN booking_OccasionType t1 ON t0.OCCASIONTYPE_ID = t1.id WHERE (EXISTS (SELECT FROM WHERE ( AND )))}

[code=156, state=S0001]

When I replace Booking with the name of a concrete subclass the query executes fine.
When I execute only the subquery as a normal query it executes fine:
SELECT booking.id FROM Booking booking
WHERE booking.event.id = :eventId AND booking.remarks = :REMARKS

When I replace the parameter :REMARKS with a Collection-valued parameter like so "booking.remarks IN :REMARKS", I get a weird
ClassCastException:

Caused by: java.lang.ClassCastException: java.lang.String cannot be cast to [Ljava.lang.Object;
at org.apache.openjpa.jdbc.kernel.exps.InExpression.orContains(InExpression.java:178)
at org.apache.openjpa.jdbc.kernel.exps.InExpression.appendTo(InExpression.java:122),
....

Again when I replace Booking with the name of a concrete subclass the query executes fine.

I think this may be related to OPENJPA-681 and OPENJPA-658 which have already been resolved, but perhaps the problem still exists when querying on a superclass.

Issue Links

• is related to: OPENJPA-658 select count(o) in subquery or not exists subquery generates SQL with syntax error