Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 369854

Summary: Syntax eror in generated SQL from JPQL query
Product: z_Archived Reporter: Jérôme SALLES <jfp.salles>
Component: EclipselinkAssignee: Nobody - feel free to take it <nobody>
Status: NEW --- QA Contact:
Severity: normal    
Priority: P4 CC: jamesssss, jfp.salles, tom.ware
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Whiteboard:
Attachments:
Description Flags
The JPQL query
none
The TSQL query none

Description Jérôme SALLES CLA 2012-01-26 14:08:44 EST 
Build Identifier: 2.2.0

The joined jpql query is not properly translated in TSQL (the Microsoft version for SQL Server of the SQL language).

When the TSQL query is executed, the database server says : "Syntaxe incorrecte vers le mot clé 'IN'"  (french version). In english it would be something like "Incorrect syntax near token 'IN'" 

Both (JPQL and SQL generated) queries are joined to the issue.

The problem is that there is nothing before the 'IN' keyword in the query. It seems that a variable is not resolved when JPQL is translated.

Used EclipseLink is 2.2.0 FOR JPA 2.0.3 on Glassfish 3.1. The version of SQL Server is 2008 R2.

Any idea or workaround ?

Reproducible: Always
Comment 1 Jérôme SALLES CLA 2012-01-26 14:11:07 EST 
Created attachment 210146 [details]
The JPQL query
Comment 2 Jérôme SALLES CLA 2012-01-26 14:11:37 EST 
Created attachment 210147 [details]
The TSQL query
Comment 3 Jérôme SALLES CLA 2012-01-26 14:12:52 EST 
The only exception i have is when database server indicate that the query is syntaxically incorrect. There is no exception from Eclipselink at translation.
Comment 4 Jérôme SALLES CLA 2012-01-27 11:36:09 EST 
The documentation says that i was trying to do is not possible. In my JPQL, i check if an object (let say x) is returned from a subquery. I write it like this :

WHERE x IN (SELECT y FROM ...)

The documentation for the IN keyword in JPQL language reference (http://docs.oracle.com/cd/E11035_01/kodo41/full/html/ejb3_langref.html#ejb3_langref_in) says that IN keywork can test attributes only. That's why translation doesn't work.

However, Eclipselink should throw an exception when parsing this...
Comment 5 Tom Ware CLA 2012-02-17 10:44:58 EST 
Setting target and priority.  See the following page for the meanings of these fields:

http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines

Community: Please vote for this bug if it is important to you.  Votes are one of the main criteria we use to determine which bugs to fix next.
Comment 6 James Sutherland CLA 2012-02-29 13:13:18 EST 
I think this has been fixed in 2.4, please try on the latest nightly build.
Comment 7 Eclipse Webmaster CLA 2022-06-09 10:31:15 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink