Comment 1 James Lee 2023-04-09 07:48:24 EDT

The

Comment 2 James Lee 2023-04-09 07:54:33 EDT

I have hierarchical data of person -> department -> company.  There is another table which records interactions between people, I call this "payment" as if person one was paying person two.  I need a list of all payments either to or from anyone that belong to a company, that is either end is the company.  The catch is some people do not have a department, hence no company however the other end of the transaction can belong to a company and these are to are included in the search results.

Comment 3 James Lee 2023-04-09 08:54:31 EDT

(Sorry this reporting system does not let me edit nor save before posting)

This is the query:


criteriaQuery.where(
    criteriaBuilder.or(
        criteriaBuilder.and(
	    criteriaBuilder.isNotNull(root.get("one").get("department")),
            criteriaBuilder.equal(root.get("one").get("department").get("company"), company)
                           ),
        criteriaBuilder.and(
            criteriaBuilder.isNotNull(root.get("two").get("department")),
            criteriaBuilder.equal(root.get("two").get("department").get("company"), company)
                            )
                       )
                   );

Comment 4 James Lee 2023-04-09 09:17:48 EDT

The problem is it does not "or" the two parts.  If I do the two parts separately and combine I get the correct results.   I have written a small example that demonstrates this, it is reduced and simplified from the original code and designed to highlight the problem.  I know I can get the desired list other ways.

The SQL it produces is (? -> 1):

SELECT t1.ID, t1.ONE_ID, t1.TWO_ID FROM DEPARTMENT t4, PERSON t3, DEPARTMENT t2, PAYMENT t1, PERSON t0 WHERE (((NOT ((t0.DEPARTMENT_ID IS NULL)) AND (t2.COMPANY_ID = 1)) OR (NOT ((t3.DEPARTMENT_ID IS NULL)) AND (t4.COMPANY_ID = 1))) AND ((((t0.ID = t1.ONE_ID) AND (t2.ID = t0.DEPARTMENT_ID)) AND (t3.ID = t1.TWO_ID)) AND (t4.ID = t3.DEPARTMENT_ID)));

A correct SQL query is:

SELECT DISTINCT pay.ID, pay.ONE_ID, pay.TWO_ID FROM DEPARTMENT d1, PERSON p1, DEPARTMENT d2, PERSON p2, PAYMENT pay
WHERE
(
	pay.one_id = p1.id
	AND
	(p1.department_id IS NOT NULL AND d1.company_id = 1 AND p1.department_id = d1.id)
)
OR
(
	pay.two_id = p2.id
	AND
	(p2.department_id IS NOT NULL AND d2.company_id = 1 AND p2.department_id = d2.id)
);



When run the example produces:
select12 test
one-by-one result
Payment112  Person111  Person211
Payment251  Person532  Person111
Payment336  Person321  Person642
Payment517  Person111  Person7__

JPA result
Payment112  Person111  Person211
Payment251  Person532  Person111
Payment336  Person321  Person642

JPA in 2 halves
Payment112  Person111  Person211
Payment251  Person532  Person111
Payment336  Person321  Person642
Payment517  Person111  Person7__


The second uses the criteriaQuery above and lacks the Persons without Department.  The first list finds the result using code, the last does the query in two parts and combines discarding duplicates.

See NOTES for how it works - else ask!

Comment 5 James Lee 2023-04-09 09:50:02 EDT

Using:
glassfish 7.0.3
postgreaql 15.2
postgresqljdbc 42.6.0
maven 3.9.1
OmniOS r151038

Comment 6 James Lee 2023-04-09 09:51:30 EDT

Created attachment 289024 [details]
example showing the problem