[CAY-2521] Expression without Object ID disjoint issue

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0, 4.0.1, 4.1.B1
• Fix Version/s: 4.0.2
• Labels: pull-request-available
• Assignee: Arseni Bulatski
• Reporter: Vital Zanko
• Created: 05/Feb/19 15:07
• Updated: 12/Apr/19 12:22
• Resolved: 12/Apr/19 12:22

Description

As an example, there are three tables: issue, team, location. There are some relationships among them:

• issue.home_team_id = team.id
• issue.location_id = location.id
• location.team_id = team.id
• team.home_location_id = location.id

Team team = Cayenne.objectForPK(localContext, Team.class, 1);
		
SelectQuery<Issue> select = new SelectQuery<>(Issue.class, ExpressionFactory.exp("homeTeam = 1"));

select.addPrefetch(Issue.HOME_TEAM.disjoint());
select.addPrefetch(Issue.LOCATION.disjoint());
select.addPrefetch(Issue.HOME_TEAM.dot(Team.HOME_LOCATION).disjoint());

List<Issue> issues = localContext.select(select);

This causes following sql query for prefetched team's home location objects which doesn't correlate with the expression (it shouldn't be applied on): 

[05/Feb/2019:09:36:37,102] bootique-http-41 u=user1 INFO  o.a.c.l.JdbcEventLogger: SELECT t0.name, t0.id, t0.team_id FROM mydb.location t0 WHERE t0.id = ? [bind: 1->id:1]
[05/Feb/2019:09:36:37,104] bootique-http-41 u=user1 INFO  o.a.c.l.JdbcEventLogger: === returned 0 rows. - took 3 ms.

 

The issue will be resolved when Object Id is being used as a parameter in the expression:

SelectQuery<Issue> select = new SelectQuery<>(Issue.class, ExpressionFactory.exp("homeTeam = $id" , (Object) team.getObjectId()));
// some custom code
select.addPrefetch(Issue.HOME_TEAM.dot(Team.HOME_LOCATION).disjoint());

List<Issue> issues = localContext.select(select);

[05/Feb/2019:09:56:25,087] bootique-http-34 u=user1 INFO  o.a.c.l.JdbcEventLogger: SELECT t0.name, t0.id, t0.team_id FROM mydb.location t0 JOIN mydb.team t1 ON (t0.id = t1.home_location_id) WHERE t1.id = ? [bind: 1->id:1]
[05/Feb/2019:09:56:25,100] bootique-http-34 u=user1 INFO  o.a.c.l.JdbcEventLogger: === returned 1 row. - took 13 ms.

 

Or the issue will be also resolved in case of expression without object id when java code declares disjoint by id

SelectQuery<Issue> select = new SelectQuery<>(Issue.class, ExpressionFactory.exp("homeTeam = 1"));
// some custom code
select.addPrefetch(Issue.HOME_TEAM.dot(Team.HOME_LOCATION).disjointById());

List<Issue> issues = localContext.select(select);

[05/Feb/2019:09:59:23,605] bootique-http-36 u=user1 INFO  o.a.c.l.JdbcEventLogger: --- transaction started.
[05/Feb/2019:09:59:23,606] bootique-http-36 u=user1 INFO  o.a.c.l.JdbcEventLogger: SELECT t0.name, t0.id, t0.team_id FROM mydb.location t0 WHERE t0.id = ? [bind: 1->id:71]
[05/Feb/2019:09:59:23,609] bootique-http-36 u=user1 INFO  o.a.c.l.JdbcEventLogger: === returned 1 row. - took 4 ms.

Issue Links

• links to: GitHub Pull Request #366