| Incorrect SQL syntax for a "JOIN FETCH" JPQL [message #506963] |
Mon, 11 January 2010 04:50  |
Eclipse User |
|
|
|
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 #507001 is a reply to message #506978] |
Mon, 11 January 2010 11:33  |
| Eclipse User |
|
|
|
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?
|
|
|
|
Powered by
FUDForum. Page generated in 0.20230 seconds
Home