FAQ
Incorrect query with self joins
-------------------------------

Key: CAY-732
URL: https://issues.apache.org/cayenne/browse/CAY-732
Project: Cayenne
Issue Type: Bug
Components: Cayenne Core Library
Affects Versions: 2.0 [STABLE]
Reporter: Patric Lichtsteiner


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

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/cayenne/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdev @
categoriescayenne
postedJan 18, '07 at 8:45p
activeJan 18, '07 at 8:45p
posts1
users1
websitecayenne.apache.org

1 user in discussion

Patric Lichtsteiner (JIRA): 1 post

People

Translate

site design / logo © 2022 Grokbase