multiple LEFT JOINs do not work as expected

Hi,

We have a hierarchical data set of organisations in the database, where each organisation stores a reference to it parent organisation. I'm trying to find all organisations 2 levels under a root organisation, e.g. where organisation.parentOrg is the root or organisation.parentOrg.parentOrg is the root. The query also contains a number of other predicates and it is built dynamically using Criteria API. It could be looking for other number of levels, 2 is just an example.

The test query looks like this:

SELECT DISTINCT $1
FROM Organisation $1
     JOIN $1.attributeList $2
     LEFT JOIN $1.parentOrg $3
     LEFT JOIN $3.parentOrg $4
WHERE ((($2.name='Attribute1') AND ($2.valueAsString='1')) AND
       (($3.organisationId='root') OR ($4.organisationId='root')))

As I understand, since I use LEFT Joins this query should return all organisation under the root organisation and all organisations under those first level orgs. It returns only organisations under the first level organisations, e.g. it does not return the organisations directly under root. The root organisation has null as it's parent. The log generated when I run this against the DB is attached.

Could you  please advise if this query is supposed to work like that? I'm running 2.3.4_05 version.

Kind Regards,

Natalia.

The following test case demonstrates a similar tree model with LEFT JOIN that works.

Please try to modify it to demonstrate a different query that doesn't work as expected:

import java.util.*;

import javax.persistence.*;


public final class T616 {

    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(
                "objectdb:test.tmp;drop");
        EntityManager em = emf.createEntityManager();

        em.getTransaction().begin();
        persistTree(em, null, 2);
        em.getTransaction().commit();
       
        Query query = em.createQuery(
            "SELECT DISTINCT n1 " +
            "FROM Node n1 LEFT JOIN n1.parent n2 LEFT JOIN n2.parent n3 " +
            "WHERE n1.depth = 0 AND n2.depth = 1 AND n3.depth = 2");
        List resultList = query.getResultList();
        System.out.println(resultList.size());

        em.close();
        emf.close();
    }
   
    private static Node persistTree(EntityManager em, Node parent, int depth) {
        Node node = new Node(parent, depth);
        em.persist(node);
        if (--depth >= 0) {
            persistTree(em, node, depth);
            persistTree(em, node, depth);
           
        }
        return node;
    }

    @Entity
    public static final class Node {
        @GeneratedValue @Id private long id;
        Node parent;
        int depth;
       
        Node() {
        }
       
        Node(Node parent, int depth) {
            this.parent = parent;
            this.depth = depth;
        }
    }
}

Please see the modified test case below. I expect to see 7 results in this test case and I get only 4.

public final class T616 {
 
    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(
                "objectdb:test.tmp;drop");
        EntityManager em = emf.createEntityManager();

        em.getTransaction().begin();
        persistTree(em, null, 2, "root");
        em.getTransaction().commit();

        Query query = em.createQuery(
            "SELECT DISTINCT n1 " +
            "FROM Node n1 LEFT JOIN n1.parent n2 LEFT JOIN n2.parent n3 " +
            "WHERE n1.nodeId = 'root' OR n2.nodeId = 'root' OR n3.nodeId = 'root'");
        List resultList = query.getResultList();
        System.out.println(resultList.size());

        em.close();
        emf.close();
    }

    private static Node persistTree(
            EntityManager em, Node parent, int depth, String nodeId) {
        Node node = new Node(parent, depth, nodeId);
        em.persist(node);
        System.out.println(nodeId);
        if (--depth >= 0) {
            persistTree(em, node, depth, nodeId+"0");
            persistTree(em, node, depth, nodeId+"1");

        }
        return node;
    }

    @Entity
    public static final class Node {
        @GeneratedValue @Id private long id;
        Node parent;
        int depth;
        String nodeId;

        Node() {
        }

        Node(Node parent, int depth, String nodeId) {
            this.parent = parent;
            this.depth = depth;
            this.nodeId = nodeId;
        }
    }
}

Regards,

Natalia.

Build 2.3.5_02 fixes the bug at least for this test case.

Hopefully it will also solve the problem in your project.

Thank you for this bug report.

Hi,

I've been away for a while and just got back to this. I've tried with version 2.3.6_04 and the test case still returns 4 results instead of 7. Please see the attached project.

Regards,

Natalia.

You are right. Please try build 2.3.6_05.