cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Kienenberger" <mkien...@gmail.com>
Subject Re: Self joins
Date Fri, 19 Jan 2007 16:58:51 GMT
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 <patric@lichtsteiner.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 <lists01@lichtsteiner.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 <andrus@objectstyle.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
> >> >
> >>
> >>
>
>

Mime
View raw message