Search code examples
javasqljpaeclipselinkjpql

JPA/JPQL returns incomplete results with WHERE x.y IS NULL OR x.y.z = 1

I have the following SQL returning 13 results

SELECT *
FROM AAA a
JOIN BBB b ON b.id = a.b_id
LEFT JOIN CCC c ON c.id = b.c_id
LEFT JOIN DDD d ON d.id = c.d_id
WHERE b.c_id IS NULL
OR d.status = 1

And following JPQL returning 3 results

SELECT a
FROM AAA a
WHERE a.b.c IS NULL

And following JPQL returning 10 results

SELECT a
FROM AAA a
WHERE a.b.c.d.status = 1

But the following JPQL returns 10 results, missing the 3 null ones.

SELECT a
FROM AAA a
WHERE a.b.c IS NULL
OR a.b.c.d.status = 1

What am I missing? Where do I begin debugging this?

Solution

  • Where do I begin debugging this?

    If you're uncertain about your ORM, tune the log level so you could see the generated SQL queries or directly check database logs.

    What am I missing?

    Condition WHERE a.b.c.d.status = 1 forces jpa provider to create inner joins for all tables in the path (BBB, CCC, DDD). This neutralizes OR condition a.b.c IS NULL.