Left-join coercion is an optimization applied to queries run against the back-end database that is used to convert certain left joins to less expensive inner joins. When the LEFTJOINCOERCION optimized query is active on the instance, it is possible for some database views that reports, exports, and scheduled exports are no longer generating data. Note that no errors are generated.
Steps to Reproduce
Create a database view that has a LEFT JOIN to a table but where the WHERE clause for that LEFT JOIN has the table being LEFT JOINED on the right and the parent table on the left.
Ensure that your database views have a left join explicitly selected, for example, create a database view automationerrormsg: LEFT JOIN err + svc WHERE svc.cidd = err.ciid.
Perform a search on Err.application = "xxxx".
The logs will show the following:
LEFTJOINCOERCION optimized query:
original: SELECT count() AS recordcount FROM (automationerrormsg err LEFT JOIN svcciassoc svc ON svc.ciid = err.ciid ) WHERE err.application = 'd5069fe9e70332001a310a6103f6a94b'
failing: SELECT ... FROM (automationerrormsg err INNER JOIN svcciassoc svc ON svc.ciid = err.ciid) WHERE err.application = 'd5069fe9e70332001a310a6103f6a94b'
Set the glide.db.coerce_left_joins system property to false.
This system property disables this specific optimization (left join). No other optimizations are modified.
Related Problem: PRB1253535