Re: Self joins

From: Mike Kienenberger (mkienen..mail.com)
Date: Thu Jan 18 2007 - 16:19:20 EST

  • Next message: Malcolm Edgar: "Re: [ANN] Cayenne 2.0.2. and 1.2.2 released"

    Ok. I'll probably be using HSQLDB to test it. You can either send me
    the *.script file from an hsqldb database or a dbunit xml flatfile so
    I can load the test data. I suppose you can send me some raw inserts
    and that will probably work too. Or have your example code create
    the test data.

    On 1/18/07, Mike Kienenberger <mkienen..mail.com> wrote:
    > This is pure speculation, but maybe it has something to do with path
    > splitting. In that case, using the outer join patches I posted will
    > probably fix it. You'd have to convert the patch from the 1.2
    > packages to the 2.0 packages, though.
    >
    > Or if you created a simple 1.2 example demonstrating the problem, I
    > could check the output under 1.2 with the outer join patch installed.
    >
    > I vaguely seem to recall hitting and fixing self-join issues when I
    > was working on the outer join patch.
    >
    > On 1/18/07, Andrus Adamchik <andru..bjectstyle.org> wrote:
    > > Hmm... looks like a bug in the prefetch code. Could you possible open
    > > a bug report, somebody will take a look.
    > >
    > > http://issues.apache.org/cayenne
    > >
    > > Thanks
    > > Andrus
    > >
    > >
    > > On Jan 18, 2007, at 11:14 AM, Patric Lichtsteiner wrote:
    > >
    > > >
    > > > Hi
    > > >
    > > > I'm using Cayenne 2.0.1 and I'm working with a model with a self join
    > > > like this:
    > > >
    > > > <db-entity name="PathElement">
    > > > <db-attribute name="ID" type="INTEGER" isPrimaryKey="true"
    > > > isMandatory="true"/>
    > > > <db-attribute name="Name" type="VARCHAR" isMandatory="true"
    > > > length="100"/>
    > > > <db-attribute name="ParentPathElementId" type="INTEGER"/>
    > > > </db-entity>
    > > >
    > > > <db-relationship name="parentPathElement" source="PathElement"
    > > > target="PathElement" toMany="false">
    > > > <db-attribute-pair source="ParentPathElementId" target="ID"/>
    > > > </db-relationship>
    > > > <db-relationship name="subPathElements" source="PathElement"
    > > > target="PathElement" toMany="true">
    > > > <db-attribute-pair source="ID" target="ParentPathElementId"/>
    > > > </db-relationship>
    > > >
    > > > <obj-entity name="PathElement"
    > > > className="com.encodo.test.cayenne.PathElement"
    > > > dbEntityName="PathElement">
    > > > <obj-attribute name="name" type="java.lang.String"
    > > > db-attribute-path="Name"/>
    > > > </obj-entity>
    > > >
    > > > <obj-relationship name="parentPathElement" source="PathElement"
    > > > target="PathElement" db-relationship-path="parentPathElement"/>
    > > > <obj-relationship name="subPathElements" source="PathElement"
    > > > target="PathElement" db-relationship-path="subPathElements"/>
    > > >
    > > > The database scheme and the classes are correctly generated with this
    > > > definition. Inserting data with java code also works without problems,
    > > > but executing a query does not work as I would expect it (the database
    > > > contains a root PathElement and 2 sub PathElements):
    > > > SelectQuery pathElementQuery = new SelectQuery(PathElement.class,
    > > > Expression.fromString("parentPathElement = null"));
    > > > pathElementQuery.addPrefetch("subPathElements");
    > > > List<PathElement> rootPathElements =
    > > > _context.performQuery(pathElementQuery);
    > > > //Root element is found
    > > > List<PathElement> subPathElements =
    > > > rootPathElements.get(0).getSubPathElements();
    > > > //subPathElements.size() is 0, that's wrong!
    > > >
    > > > The following queries are executed by Cayenne:
    > > > SELECT t0.Name, t0.ParentPathElementId, t0.ID FROM PathElement t0
    > > > WHERE
    > > > t0.ParentPathElementId IS NULL
    > > > SELECT t0.Name, t0.ParentPathElementId, t0.ID FROM PathElement t0,
    > > > PathElement t1 WHERE t0.ParentPathElementId = t1.ID AND
    > > > (t0.ParentPathElementId IS NULL)
    > > >
    > > > IMHO, this query is wrong. The condition should be:
    > > > t1.ParentPathElementId IS NULL
    > > >
    > > > What is going wrong here? Has anybody an idea?
    > > >
    > > > Thanks a lot
    > > > Patric
    > > >
    > >
    > >
    >



    This archive was generated by hypermail 2.0.0 : Thu Jan 18 2007 - 16:19:49 EST