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


  1. 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.

  2. 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.

  3. 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.

NAME: glide.db.coerce_left_joins
TYPE: True|False
VALUE: false

This system property disables this specific optimization (left join). No other optimizations are modified.

Related Problem: PRB1253535

Seen In

There is no data to report.

Fixed In

Kingston Patch 5

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2019-05-21 11:39:28