Re: Self joins

From: Mike Kienenberger (mkienen..mail.com)
Date: Tue Jan 23 2007 - 11:12:47 EST

  • Next message: Malcolm Edgar: "Converting DataRow to DataObject"

    Probably when someone takes time to convert it to 2.0 and then 3.0.
    Officially, we don't want to add it to the 1.2 or 2.0 branch.

    I've been using the patches in a production application for several
    months now, and I've not encountered any issues.

    One thing you could do to speed up the process is to convert the patch
    to 2.0. It should be a matter of changing the package names and
    possibly dealing with changed license header comments.

    On 1/22/07, Patric Lichtsteiner <lists0..ichtsteiner.org> wrote:
    > Hi Mike
    >
    > Thanks a lot for testing this! Great news that your patch solves the
    > problem.
    > Any ideas when your patch will make it to an official release?
    >
    > Patric
    >
    > On 19.01.2007, at 17:58, Mike Kienenberger wrote:
    >
    > > I guess I should have mentioned that java 1.4.2 was preferred :-)
    > >
    > > I converted the project to Java 1.4.2 (commented out the annotations,
    > > typecast the List members).
    > >
    > > I then loaded your schema into my hsqldb test database and updated the
    > > jdbc url. I removed the script line trying to set indexes to the
    > > missing dabase data, and everything seems to work. The tests all
    > > pass. So it looks like the problem is fixed if you use the outer join
    > > patch. As you stated, the correct output should have been
    > > "t1.ParentPathElementId IS NULL"
    > >
    > > Here's the output:
    > >
    > > Created connection pool: jdbc:hsqldb:hsql://localhost:9200
    > > Driver class: org.hsqldb.jdbcDriver
    > > Min. connections in the pool: 1
    > > Max. connections in the pool: 1
    > > --- will run 1 query.
    > > Opening connection: jdbc:hsqldb:hsql://localhost:9200
    > > Login: sa
    > > Password: *******
    > > +++ Connecting: SUCCESS.
    > > --- transaction started.
    > > Detected and installed adapter:
    > > org.objectstyle.cayenne.dba.hsqldb.HSQLDBAdapter
    > > SELECT t0.Name, t0.ParentPathElementId, t0.ID FROM PathElement t0 -
    > > prepared in 93 ms.
    > > === returned 0 rows. - took 141 ms.
    > > +++ transaction committed.
    > > --- will run 2 queries.
    > > --- transaction started.
    > > SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = 'PathElement'
    > > === returned 1 row. - took 0 ms.
    > > UPDATE AUTO_PK_SUPPORT SET NEXT_ID = NEXT_ID + 20 WHERE TABLE_NAME =
    > > 'PathElement'
    > > === updated 1 row.
    > > --- will run 1 query.
    > > INSERT INTO PathElement (ID, Name, ParentPathElementId) VALUES
    > > (?, ?, ?)
    > > [bind: 221, 'root', NULL]
    > > === updated 1 row.
    > > [bind: 222, 'root.sub0', 221]
    > > === updated 1 row.
    > > [bind: 220, 'root.sub1', 221]
    > > === updated 1 row.
    > > +++ transaction committed.
    > > --- will run 1 query.
    > > --- transaction started.
    > > SELECT t0.Name, t0.ParentPathElementId, t0.ID FROM PathElement t0
    > > WHERE t0.ParentPathElementId IS NULL - prepared in 15 ms.
    > > === returned 1 row. - took 15 ms.
    > > +++ transaction committed.
    > > --- will run 1 query.
    > > --- transaction started.
    > > SELECT t0.Name, t0.ParentPathElementId, t0.ID FROM PathElement t0
    > > === returned 3 rows. - took 0 ms.
    > > +++ transaction committed.
    > > --- will run 1 query.
    > > --- transaction started.
    > > DELETE FROM PathElement WHERE ID = ?
    > > [bind: 222]
    > > === updated 1 row.
    > > [bind: 220]
    > > === updated 1 row.
    > > [bind: 221]
    > > === updated 1 row.
    > > +++ transaction committed.
    > > --- will run 1 query.
    > > --- transaction started.
    > > INSERT INTO PathElement (ID, Name, ParentPathElementId) VALUES
    > > (?, ?, ?)
    > > [bind: 224, 'root', NULL]
    > > === updated 1 row.
    > > [bind: 225, 'root.sub1', 224]
    > > === updated 1 row.
    > > [bind: 223, 'root.sub0', 224]
    > > === updated 1 row.
    > > +++ transaction committed.
    > > --- will run 2 queries.
    > > --- transaction started.
    > > SELECT t0.Name, t0.ParentPathElementId, t0.ID FROM PathElement t0
    > > WHERE t0.ParentPathElementId IS NULL
    > > === returned 1 row. - took 0 ms.
    > > SELECT t0.Name, t0.ParentPathElementId, t0.ID FROM PathElement t0
    > > INNER JOIN PathElement t1 ON t0.ParentPathElementId = t1.ID WHERE
    > > t1.ParentPathElementId IS NULL
    > > === returned 2 rows. - took 16 ms.
    > > +++ transaction committed.
    > >
    > >
    > >
    > > On 1/19/07, Patric Lichtsteiner <patri..ichtsteiner.org> wrote:
    > >> Hi Mike
    > >>
    > >> Here is my test project as an eclipse export. Everything is in a
    > >> JUnit
    > >> test, including the test data generation. The HSQLDB schema
    > >> generation
    > >> script is also attached.
    > >>
    > >> Please let me know if something is missing.
    > >
    > >
    > >
    > > On 1/18/07, Patric Lichtsteiner <lists0..ichtsteiner.org> wrote:
    > >> Sounds interesting!
    > >> I'll send you a 1.2 example tomorrow.
    > >>
    > >> Patric
    > >>
    > >> On 18.01.2007, at 21:52, Mike Kienenberger 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 : Tue Jan 23 2007 - 11:13:48 EST