Filtering on calculated properties using deep property path and MySQL view returns wrong results.
Description
I have an Entity 'Brand' with a calculated property and a MySQL view which is mapped using composite-id.
<property name="hasImage" generated="always"
formula="(SELECT EXISTS(SELECT 1 FROM images AS i WHERE i.refId = id AND mf.refType = 'ProductGroup' LIMIT 1))"/>
In MySQL the boolean is mapped to an tinyint {0,1}
.
<class name="entities.StockAndCategory" mutable="false">
<cache usage="read-only"/>
<composite-id>
<key-many-to-one name="stock" column="stock" class="entities.Stock" lazy="false"/>
<key-many-to-one name="category" class="entities.Category" lazy="false"/>
</composite-id>
</class>
This is just the minimum info to know about the bug. I never realized the bug before going into production since all our brands had images in development.
Following query results in wrong results. All brands are being retrieved independent of their `hasImage` flag being `true`. The sorting is working though.
SELECT DISTINCT
sc.stock.product.brand.name,
sc.stock.product.brand.hasImage
FROM
StockCategory AS sc
WHERE
sc.stock.product.brand IS NOT NULL -- usually obsolete check
AND sc.stock.product.brand.hasImage = true
ORDER BY
sc.stock.product.brand.hasImage DESC
Results
The filtering is ignored. But sorting works.
Brand
|
Has Image
|
Apple
|
true
|
Google
|
true
|
Microsoft
|
false
|
IBM
|
false
|
Observations
Performing a direct query on the brand table returns correct results. So the entity is definetly mapped correctly.
SELECT b.name, b.hasImage
FROM ProductGroup AS b
WHERE b.hasImage = true
|