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

From: Andrus Adamchik (JIRA) ("Andrus)
Date: Tue May 06 2008 - 16:12:52 EDT

  • Next message: Andrus Adamchik (JIRA): "[jira] Closed: (CAY-560) Add support for outer joins"

         [ 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.
    



    This archive was generated by hypermail 2.0.0 : Tue May 06 2008 - 16:13:19 EDT