22 views

Description

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'

 

Workaround

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.

Intended Fix Version

Kingston Patch 5
London

Safe Harbor Statement

This "Intended Fix Version" information is meant to outline ServiceNow's general product direction and should not be relied upon in making a purchasing decision. The information provided here is for information purposes only and may not be incorporated into any contract. It is not a commitment, promise, or legal obligation to deliver any material, code, or functionality. The development, release, and timing of any features or functionality described for our products remains at ServiceNow's sole discretion.

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-04-26 00:13:52
Published:2018-04-17