FAQ
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

Search Discussions

  • Tad at Jul 19, 2011 at 9:07 pm
    I solved the issue by setting JOINT_PREFETCH_SEMANTICS on the
    prefetch. Why should this make a difference?

    --
    Thanks!
    Tad
  • Andrus Adamchik at Jul 20, 2011 at 6:27 am
    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
  • Tad at Jul 20, 2011 at 5:11 pm
    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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedJul 19, '11 at 8:16p
activeJul 20, '11 at 5:11p
posts4
users2
websitecayenne.apache.org

2 users in discussion

Tad: 3 posts Andrus Adamchik: 1 post

People

Translate

site design / logo © 2021 Grokbase