[jira] Closed: (CAY-732) Incorrect query with self joins

["Andrus Adamchik (JIRA)" <de...@cayenne.apache.org>]

     [ https://issues.apache.org/cayenne/browse/CAY-732?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Andrus Adamchik closed CAY-732.
-------------------------------

       Resolution: Fixed
    Fix Version/s: 3.0

Running the example using Cayenne trunk build from today (after CAY-820 fix that involved deep refactoring on the column aliasing), and it works. SQL generated is this:

SELECT t0.ParentPathElementId, t0.ID, t0.Name FROM path_element t0 WHERE t0.ParentPathElementId IS NULL

SELECT t0.ParentPathElementId, t0.ID, t0.Name FROM path_element t0 JOIN path_element t1 ON (t0.ParentPathElementId = t1.ID) 
WHERE t1.ParentPathElementId IS NULL

> 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
>            Assignee: Andrus Adamchik
>             Fix For: 3.0
>
>         Attachments: selfjoin12.zip
>
>
> 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.
-
You can reply to this email to add a comment to the issue online.