You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Patric Lichtsteiner <li...@lichtsteiner.org> on 2007/01/18 09:14:15 UTC
Self joins
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
Re: Self joins
Posted by Mike Kienenberger <mk...@gmail.com>.
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 <li...@lichtsteiner.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 <pa...@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 <li...@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 <an...@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
> >> >> >
> >> >>
> >> >>
> >>
> >>
>
>
Re: Self joins
Posted by Patric Lichtsteiner <li...@lichtsteiner.org>.
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 <pa...@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 <li...@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 <an...@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
>> >> >
>> >>
>> >>
>>
>>
Re: Self joins
Posted by Mike Kienenberger <mk...@gmail.com>.
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 <pa...@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 <li...@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 <an...@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
> >> >
> >>
> >>
>
>
Re: Self joins
Posted by Patric Lichtsteiner <li...@lichtsteiner.org>.
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 <an...@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
>> >
>>
>>
Re: Self joins
Posted by Mike Kienenberger <mk...@gmail.com>.
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 <mk...@gmail.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 <an...@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
> > >
> >
> >
>
Re: Self joins
Posted by Mike Kienenberger <mk...@gmail.com>.
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 <an...@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
> >
>
>
Re: Self joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
>