I'm having an issue where prefetching a flattened relationship seems
to skip rows or fetch them from a stale cache. In my test case, I can
verify via mysql that the join table has 5 rows, yet prefetching only
returns 4.
DB layout:
customer has-many positions
customer has-many employees
employees many-to-many positions (employee_positions join table)
Log with prefetching a flattened "positions" relationship:
Jul 19, 2011 10:08:51 AM org.apache.cayenne.access.QueryLogger
logBeginTransaction
INFO: --- transaction started.
Jul 19, 2011 10:08:51 AM org.apache.cayenne.access.QueryLogger logQuery
INFO: SELECT `t0`.`employee_id`, `t0`.`davison_employee_id`,
`t0`.`customer_id`, `t0`.`is_enabled`, `t0`.`davison_manager_id`,
`t0`.`effective_availability_set_id`,
`t0`.`proposed_availability_set_id`, `t0`.`is_manager` FROM
`scheduler_employee` `t0` WHERE (`t0`.`customer_id` = ?) AND
(`t0`.`is_enabled` = ?) [bind: 1->customer_id:1, 2->is_enabled:'true']
Jul 19, 2011 10:08:51 AM org.apache.cayenne.access.QueryLogger logSelectCount
INFO: === returned 4 rows. - took 4 ms.
Jul 19, 2011 10:08:51 AM org.apache.cayenne.access.QueryLogger logQuery
INFO: SELECT `t0`.`position_id`, `t0`.`description`, `t0`.`name`,
`t0`.`customer_id`, `t2`.`employee_id` FROM `scheduler_position` `t0`
JOIN `scheduler_employee_position` `t1` ON (`t0`.`position_id` =
`t1`.`position_id`) JOIN `scheduler_employee` `t2` ON
(`t1`.`employee_id` = `t2`.`employee_id`) WHERE (`t2`.`customer_id` =
?) AND (`t2`.`is_enabled` = ?) [bind: 1->customer_id:1,
2->is_enabled:'true']
Jul 19, 2011 10:08:51 AM org.apache.cayenne.access.QueryLogger logSelectCount
INFO: === returned 4 rows. - took 6 ms.
Jul 19, 2011 10:08:51 AM org.apache.cayenne.access.QueryLogger
logCommitTransaction
Log with no prefetch:
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logBeginTransaction
INFO: --- transaction started.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logQuery
INFO: SELECT `t0`.`employee_id`, `t0`.`davison_employee_id`,
`t0`.`customer_id`, `t0`.`is_enabled`, `t0`.`davison_manager_id`,
`t0`.`effective_availability_set_id`,
`t0`.`proposed_availability_set_id`, `t0`.`is_manager` FROM
`scheduler_employee` `t0` WHERE (`t0`.`customer_id` = ?) AND
(`t0`.`is_enabled` = ?) [bind: 1->customer_id:1, 2->is_enabled:'true']
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logSelectCount
INFO: === returned 4 rows. - took 6 ms.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logCommitTransaction
INFO: +++ transaction committed.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logQueryStart
INFO: --- will run 1 query.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logBeginTransaction
INFO: --- transaction started.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logQuery
INFO: SELECT `t0`.`position_id`, `t0`.`description`, `t0`.`name`,
`t0`.`customer_id` FROM `scheduler_position` `t0` JOIN
`scheduler_employee_position` `t1` ON (`t0`.`position_id` =
`t1`.`position_id`) WHERE `t1`.`employee_id` = ? [bind:
1->employee_id:620]
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logSelectCount
INFO: === returned 1 row. - took 5 ms.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logCommitTransaction
INFO: +++ transaction committed.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logQueryStart
INFO: --- will run 1 query.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logBeginTransaction
INFO: --- transaction started.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logQuery
INFO: SELECT `t0`.`position_id`, `t0`.`description`, `t0`.`name`,
`t0`.`customer_id` FROM `scheduler_position` `t0` JOIN
`scheduler_employee_position` `t1` ON (`t0`.`position_id` =
`t1`.`position_id`) WHERE `t1`.`employee_id` = ? [bind:
1->employee_id:621]
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logSelectCount
INFO: === returned 1 row. - took 2 ms.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logCommitTransaction
INFO: +++ transaction committed.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logQueryStart
INFO: --- will run 1 query.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logBeginTransaction
INFO: --- transaction started.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logQuery
INFO: SELECT `t0`.`position_id`, `t0`.`description`, `t0`.`name`,
`t0`.`customer_id` FROM `scheduler_position` `t0` JOIN
`scheduler_employee_position` `t1` ON (`t0`.`position_id` =
`t1`.`position_id`) WHERE `t1`.`employee_id` = ? [bind:
1->employee_id:622]
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logSelectCount
INFO: === returned 1 row. - took 2 ms.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logCommitTransaction
INFO: +++ transaction committed.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logQueryStart
INFO: --- will run 1 query.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logBeginTransaction
INFO: --- transaction started.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logQuery
INFO: SELECT `t0`.`position_id`, `t0`.`description`, `t0`.`name`,
`t0`.`customer_id` FROM `scheduler_position` `t0` JOIN
`scheduler_employee_position` `t1` ON (`t0`.`position_id` =
`t1`.`position_id`) WHERE `t1`.`employee_id` = ? [bind:
1->employee_id:623]
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger logSelectCount
INFO: === returned 2 rows. - took 2 ms.
Jul 19, 2011 10:10:18 AM org.apache.cayenne.access.QueryLogger
logCommitTransaction
INFO: +++ transaction committed.
Relevant code (in Customer.java extends _Customer):
List<Expression> qualifiers = new ArrayList<Expression>();
qualifiers.add(ExpressionFactory.matchExp("customer", this));
qualifiers.add(ExpressionFactory.matchExp("isEnabled", true));
Expression qualifier = ExpressionFactory.joinExp(Expression.AND,
qualifiers);
SelectQuery query = new SelectQuery(Employee.class, qualifier);
query.addPrefech("positions"); // Removing this line fixes the
issue for me, but is obviously not desirable
query.setCacheStrategy(QueryCacheStrategy.NO_CACHE);
return getObjectContext().performQuery(query);
What am I doing wrong here?
--
Thanks!
Tad
