You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Tad <ta...@gmail.com> on 2011/07/19 22:16:04 UTC
Query prefetch missing rows
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
Re: Query prefetch missing rows
Posted by Tad <ta...@gmail.com>.
I can't reproduce it using disjoint prefetch semantics anymore. I
suspect my Eclipse/Tomcat instance was at fault.
--
Thanks,
Tad
On Tue, Jul 19, 2011 at 8:26 PM, Andrus Adamchik <an...@objectstyle.org> wrote:
> It really shouldn't make a difference.
>
> So if you fetch the data outside Cayenne using MySQL client, which row is missing if you fetch
>
> SELECT `t0`.*, `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` = ?)
>
> vs. just
>
> SELECT `t0`.* FROM `scheduler_position` `t0`
>
> ?
>
> Thanks,
> Andrus
>
> On Jul 20, 2011, at 12:06 AM, Tad wrote:
>
>> I solved the issue by setting JOINT_PREFETCH_SEMANTICS on the
>> prefetch. Why should this make a difference?
>>
>> --
>> Thanks!
>> Tad
>>
>
>
Re: Query prefetch missing rows
Posted by Andrus Adamchik <an...@objectstyle.org>.
It really shouldn't make a difference.
So if you fetch the data outside Cayenne using MySQL client, which row is missing if you fetch
SELECT `t0`.*, `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` = ?)
vs. just
SELECT `t0`.* FROM `scheduler_position` `t0`
?
Thanks,
Andrus
On Jul 20, 2011, at 12:06 AM, Tad wrote:
> I solved the issue by setting JOINT_PREFETCH_SEMANTICS on the
> prefetch. Why should this make a difference?
>
> --
> Thanks!
> Tad
>
Re: Query prefetch missing rows
Posted by Tad <ta...@gmail.com>.
I solved the issue by setting JOINT_PREFETCH_SEMANTICS on the
prefetch. Why should this make a difference?
--
Thanks!
Tad