Bug 360720

Comment 1 Tom Ware 2011-12-02 10:27:07 EST

Correcting target milestone to next milestone that will include community-submitted bugs.

Comment 2 Andrei Ilitchev 2012-05-09 14:33:18 EDT

Created attachment 215356 [details]
Suggested patch.

The tested query:

Expression expression = builder.getAllowingNull("teamLeader").get("firstName").equal("Sarah");
expression = expression.or(builder.get("name").equal("TOPEmployee Management"));
query.setSelectionCriteria(expression);
query.setAsOfClause(getAsOfClause());

The expected result was 2 LargeProject objects:
one named "TOPEmployee Management", which has no teamLead;
and another one, with a teamLead Sarah.

But when run on MySQL 5.5.16 the test returned only one object - teamLead-less "TOPEmployee Management" was missing.

Here's the generated sql:

SELECT 
	t2.PROJ_ID, t2.PROJ_TYPE, t2.DESCRIP, t2.PROJ_NAME, t2.LEADER_ID, t2.VERSION, t3.PROJ_ID, t3.BUDGET, t3.MILESTONE 
FROM 
	PROJECT_HIST t2 LEFT OUTER JOIN (EMPLOYEE_HIST t0 JOIN SALARY_HIST t1 ON (t1.EMP_ID = t0.EMP_ID)) ON (t0.EMP_ID = t2.LEADER_ID), LPROJECT_HIST t3 
WHERE 
	((((t0.F_NAME = ?) OR (t2.PROJ_NAME = ?)) AND 
	((t3.PROJ_ID = t2.PROJ_ID) AND (t2.PROJ_TYPE = ?))) AND 
	((((t2.ROW_START <= ?) AND ((t2.ROW_END IS NULL) OR (t2.ROW_END > ?))) AND 
	  ((t3.ROW_START <= ?) AND ((t3.ROW_END IS NULL) OR (t3.ROW_END > ?)))) AND 
	((((t0.ROW_START IS NULL) OR (t0.ROW_START <= ?)) AND 
	  ((t0.ROW_END IS NULL) OR (t0.ROW_END > ?))) AND 
	 (((t1.ROW_START IS NULL) OR (t1.ROW_START <= ?)) AND 
	  ((t1.ROW_END IS NULL) OR (t1.ROW_END > ?))))))
	
bind => [
Sarah, TOPEmployee Management, 
L, 
2012-05-07 09:38:49.906, 2012-05-07 09:38:49.906, 
2012-05-07 09:38:49.906, 2012-05-07 09:38:49.906, 
2012-05-07 09:38:49.906, 2012-05-07 09:38:49.906, 
2012-05-07 09:38:49.906, 2012-05-07 09:38:49.906]


As was pointed by James, the problem is putting joining conditions in WHERE clause - those should be in ON clause instead.

Somehow the previous version of MySQL, in case t3.teamLead is null evaluated the joined expression
  ((t0.ROW_START IS NULL) OR (t0.ROW_START <= ?))
to "true", but 5.5.16 - to "false".

The patch moves historical joining conditions from WHERE to ON clause.
Patched Eclipselink generates the following sql:

SELECT 
	t2.PROJ_ID, t2.PROJ_TYPE, t2.DESCRIP, t2.PROJ_NAME, t2.LEADER_ID, t2.VERSION, t3.PROJ_ID, t3.BUDGET, t3.MILESTONE 
FROM 
	PROJECT_HIST t2 LEFT OUTER JOIN (EMPLOYEE_HIST t0 JOIN SALARY_HIST t1 
	ON ((t1.EMP_ID = t0.EMP_ID) AND ((t1.ROW_START <= ?) AND ((t1.ROW_END IS NULL) OR (t1.ROW_END > ?))))) 
	ON ((t0.EMP_ID = t2.LEADER_ID) AND ((t0.ROW_START <= ?) AND ((t0.ROW_END IS NULL) OR (t0.ROW_END > ?)))), LPROJECT_HIST t3 
WHERE 
	((((t0.F_NAME = ?) OR (t2.PROJ_NAME = ?)) AND ((t3.PROJ_ID = t2.PROJ_ID) AND (t2.PROJ_TYPE = ?))) AND 
	(((t2.ROW_START <= ?) AND ((t2.ROW_END IS NULL) OR (t2.ROW_END > ?))) AND 
	((t3.ROW_START <= ?) AND ((t3.ROW_END IS NULL) OR (t3.ROW_END > ?)))))

bind => [
2012-05-09 13:32:26.782, 2012-05-09 13:32:26.782, 
2012-05-09 13:32:26.782, 2012-05-09 13:32:26.782, 
Sarah, TOPEmployee Management, L, 
2012-05-09 13:32:26.782, 2012-05-09 13:32:26.782, 
2012-05-09 13:32:26.782, 2012-05-09 13:32:26.782]

This one correctly returns both objects, the test passes.

Comment 3 Andrei Ilitchev 2012-05-10 14:27:17 EDT

Checked in the fix into trunk (2.4). Reviewed by James.
The bug won't be fixed in 2.3.3 because support for on clause was introduced only in 2.4.

Comment 4 Eclipse Webmaster 2022-06-09 10:27:05 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink