Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Incorrect SQL syntax for a "JOIN FETCH" JPQL(Incorrect SQL syntax for a "JOIN FETCH" JPQL)
Incorrect SQL syntax for a "JOIN FETCH" JPQL [message #506963] Mon, 11 January 2010 09:50 Go to next message
Dan Siviter is currently offline Dan Siviter
Messages: 4
Registered: January 2010
Junior Member
Basically, when I attempt to perform a query that has a 'JOIN FETCH' hint associated does not appear correctly in the SQL which causes a minor performance headache.

For some reason the syntax is not correct for SQL Server as it's not using a 'LEFT JOIN' in the SQL.

Environment:
* Microsoft SQL Server 2005
* Microsoft sqljdbc 1.2 JDBC driver
* EclipseLink 2.0.0.RC1

Entities:
@Entity
public class Job {
	@Id
	@Column(name = "job_id")
	private long id;
	private String name;
	@ManyToOne
	@JoinColumn(name = "broker_id")
	private Broker broker;
}

@Entity
public class Broker {
	@Id
	@Column(name = "broker_id")
	private long id;
	private String name;
	@OneToMany(mappedBy = "broker")
	private List<Job> jobs;
}

Query:
SELECT j
FROM Job j
LEFT JOIN FETCH j.broker


Generated SQL:
SELECT t1.job_id, t1.broker_id, t0.broker_id, t0.name
FROM dbo.brokers t0, dbo.jobs t1
WHERE (((t1.start_time BETWEEN ? AND ?) AND (t0.broker_name = ?)) AND (t0.broker_id = t1.broker_id))


I would expect to perform the following:
SELECT t1.job_id, t1.broker_id, t0.broker_id, t0.name
FROM dbo.jobs t1
LEFT JOIN dbo.brokers t0 ON t0.broker_id = t1.broker_id
WHERE (t1.start_time BETWEEN ? AND ?) AND (t0.broker_name = ?)


Do I have a error in my associations? I've recently moved the codebase from Hibernate Annotations to EclipseLink and Hibernate was using a 'LEFT JOIN' .

Thanks
Dan
Re: Incorrect SQL syntax for a "JOIN FETCH" JPQL [message #506978 is a reply to message #506963] Mon, 11 January 2010 15:34 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You should get the outer join generated in the SQL.

Perhaps try, "LEFT OUTER JOIN FETCH j.broker"

Also what is the code for the real query, from the SQL it is more complex than the JPQL you gave. If you try the same JPQL as you gave, do it do the outer join?

You could also remove the JOIN FETCH from the JPQL, and instead use the EclipseLink query hint,
"eclipselink.left-join-fetch"="j.broker"


James : Wiki : Book : Blog : Twitter
Re: Incorrect SQL syntax for a "JOIN FETCH" JPQL [message #507001 is a reply to message #506978] Mon, 11 January 2010 16:33 Go to previous messageGo to next message
Dan Siviter is currently offline Dan Siviter
Messages: 4
Registered: January 2010
Junior Member
Thanks for the prompt reply! You're correct the query is more complex, but I removed a load of code for clarity. But this time I performed the queries exactly how I've written them below.

James wrote on Mon, 11 January 2010 10:34
You should get the outer join generated in the SQL.

Perhaps try, "LEFT OUTER JOIN FETCH j.broker"


Ok, so using:
SELECT j
FROM Job j
LEFT JOIN FETCH j.broker


I get:
SELECT t1.job_id, t1.start_time, t1.job_name, t1.broker_id, t0.broker_id, t0.broker_name
FROM dbo.jobs t1
LEFT OUTER JOIN dbo.brokers t0 ON (t0.broker_id = t1.broker_id)


So I thought I'd reintroduce the filter parameter:
SELECT j
FROM Job j 
LEFT JOIN FETCH j.broker 
WHERE j.broker.name = :brokerName


Gives:
SELECT t1.job_id, t1.job_name, t1.broker_id, t0.broker_id, t0.broker_name
FROM dbo.brokers t0, dbo.jobs t1
WHERE ((t0.broker_name = ?)
AND (t0.broker_id = t1.broker_id))


I thought I'd alias the broker to see if that would work:
SELECT j 
FROM Job j 
LEFT JOIN FETCH j.broker 
JOIN j.broker AS b 
WHERE b.name = :brokerName


Gives:
SELECT t1.job_id, t1.job_name, t1.broker_id, t0.broker_id, t0.broker_name
FROM dbo.brokers t0, dbo.jobs t1
WHERE ((t0.broker_name = ?) 
AND (t0.broker_id = t1.broker_id))


Finally, I thought I'd try the WHERE clause on something on Job:
SELECT j 
FROM Job j 
LEFT JOIN FETCH j.broker 
WHERE j.name = :brokerName


Gave:
SELECT t1.job_id, t1.job_name, t1.broker_id, t0.broker_id, t0.broker_name
FROM dbo.jobs t1
LEFT OUTER JOIN dbo.brokers t0 ON (t0.broker_id = t1.broker_id)
WHERE (t1.job_name = ?)


It seems when I attempt to do any WHERE clause on broker it prevents it from using 'LEFT OUTER JOIN'.

James wrote on Mon, 11 January 2010 10:34
You could also remove the JOIN FETCH from the JPQL, and instead use the EclipseLink query hint,
"eclipselink.left-join-fetch"="j.broker"


I tried that also to no avail.

Any other ideas?
Re: Incorrect SQL syntax for a "JOIN FETCH" JPQL [message #507530 is a reply to message #507001] Wed, 13 January 2010 18:51 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1021
Registered: July 2009
Senior Member
Hello,

Problem seems to be in the additional "JOIN j.broker AS b" that you are using - this causes the inner join to be used.

Try using "LEFT OUTER JOIN j.broker AS b" instead, which will allow you to use the b in your where clause.

Best Regards,
Chris
Previous Topic:Problems with JPA 2, an Embeddable with Map and multiple usages in Entities
Next Topic:2.0.0: application hangs in acquire connection
Goto Forum:
  


Current Time: Thu Sep 18 13:53:18 GMT 2014

Powered by FUDForum. Page generated in 0.05394 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software