Prefetch SQL drops part of join

From: Neil Pierson (neil.pierso..mail.com)
Date: Mon Aug 22 2005 - 12:25:52 EDT

  • Next message: Dave Dombrosky: "CayenneModeler Exception"

    I'm having trouble with prefetches in Cayenne 1.1. I have a table --
    "MasterTable" in the log snippet below -- that has a to-one relationship to
    another table that I join to using an expression. The 'Status' column is a
    varchar2 in my Oracle 9 db. I need the status to be null, or not equal to a
    certain value. The SQL that gets generated looks fine for the query itself: .
    . . AND ((t0.Status IS NULL) OR (t0.Status <> ?))) . . . but when
    prefetching other relationships to the MasterTable causes part of the join
    to be dropped. Specifically, the (t0.Status <> ?) part of the join is used,
    but not the (t0.Status IS NULL) part. The behavior is the same for
    prefetching any other relationship, and it's the same if the relationship is
    to-one or to-many. The behavior is also the same if I create the query in
    the model and use query.andQualifier(some expression) or create the query
    right in my class.

    Any ideas of what I might be doing wrong or a possible workaround if it's
    not me?

    TIA,

    n.

    // The initial query:

    INFO [http-8080-Processor25 2005-22-08 11:56:36] QueryLogger: ---
    transaction started.
    INFO [http-8080-Processor25 2005-22-08 11:56:36] QueryLogger: SELECT t0.*
    FROM MySchema.MasterTable t0, MySchema.ToOneTable t1 WHERE t0.key1 =
    t1.key1AND ((
    t0.SomeColumn >= ?) AND (t0.SomeColumn <= ?) AND (t1.AnotherColumn= ?) AND
    ((t0.Status IS NULL) OR (t0.Status <> ?))) [bind: SomeColumnValue ,
    SomeColumnValue , AnotherColumnValue, 'SOME_STATUS']
    INFO [http-8080-Processor25 2005-22-08 11:56:36] QueryLogger: === returned
    29 rows. - took 203 ms.

    // a Prefetch:

    INFO [http-8080-Processor25 2005-22-08 11:56:36] QueryLogger: SELECT
    DISTINCT t0.* FROM MySchema.ToManyTable t0, MySchema.ToOneTable t1,
    MySchema.MasterTable t2 WHERE t0.KeyValue
    = t1.KeyValue AND t1.KeyValue = t2.KeyValue AND ((t2.SomeColumn >= ?) AND
    (t2.SomeColumn <= ?) AND
    (t1.AnotherColumn = ?) AND (t2.Status <> ?)) [bind: SomeColumnValue ,
    SomeColumnValue
    , AnotherColumn, 'SOME_STATUS']

    statscontract INFO [http-8080-Processor25 2005-22-08 11:56:37] QueryLogger:
    === returned 0 rows. - took 156 ms.

    // more logs of similar behavior for other prefetches omitted.

    This archive was generated by hypermail 2.0.0 : Mon Aug 22 2005 - 12:25:53 EDT