Possible JPQL bug

Support
#1

I have been struggling with this for like 6 hours.

I am trying to write a query to find which of my wafers is available to be shipped.

I have two entities, WaferLot and Wafer in a composition relationship. I have a datasource, waferLotDs, and a collectionDatasource, wafersDs. I have a table attached to the wafersDs datasource. A wafer lot can have one of two “ProbeTypes”- “Sample” or “100%”. If a wafer has not been shipped, a boolean value “Available” is marked as true, if it has been shipped, "Available is false. If a wafer lot has a probe type of “100%”, each wafer must have completed its probing first. If it is a probe type of “Sample”, it doesn’t matter if it is probed or not.

This is my current query:

select e from deiproductconfig2$Wafer e
WHERE 
(
(e.waferLot.id = :ds$waferLotDs and e.available = true and e.waferLot.probeType.name = '100%' and e.sampleProbe.probeComplete = true)
or
(e.waferLot.id = :ds$waferLotDs and e.available = true and e.waferLot.probeType.name = 'Sample' )
)

This query works fine for the “100%” wafer lots but for the “sample” wafer lots, it is only returning the wafers where the probing has been completed. Even if I remove the

e.waferLot.id = :ds$waferLotDs

clause it still doesn’t return the expected wafers. But If I run this same query, minus the above clause, of course, through the JMX console (see here: Test JPQL queries - CUBA.Platform) it returns the expected wafers.

Am I doing something wrong? Or did I find an actual bug?

#2

Hi,
Your “Sample” Wafers have empty sampleProbes. Right?
ORM transforms the query to SQL with INNER JOINs. So records with empty link to sampleProbe are not loaded.
Try to modify the query as follows and check results. Probably “Sample” Wafers will be there.

select e from deiproductconfig2$Wafer e left join e.sampleProbe sp
WHERE 
(
(e.waferLot.id = :ds$waferLotDs and e.available = true and e.waferLot.probeType.name = '100%' and sp.probeComplete = true)
or
(e.waferLot.id = :ds$waferLotDs and e.available = true and e.waferLot.probeType.name = 'Sample' )
)

Also, let me share the recipe of how to debug JPQL queries: In the running application, you can set eclipselink.sql log level to “debug”. And see which SQL is performed in app.log.
Open Administration -> Server log in menu.
public://attachments/e9a5568f2c10293adff1787343fdc460.jpg

e9a5568f2c10293adff1787343fdc460