Description
When the left join coercion is active on the instance (System Property 'glide.db.coerce_left_joins' is either not existing on the instance or existing and set to 'true'), the query fails to retrieve correct data on database views with left join.
Steps to Reproduce
- Create a Database View with the left join as follows:
- Name = u_db_view
- Add a view table to the database view:
- table= asmt_metric_result
- Order= 100
- Variable Prefix = metricres
- Where clause = (keep it empty)
- left join = false
- Add another view table to the database view:
- table=asmt_metric_definition
- Order= 200
- Variable Prefix = mdef
- Where clause = mdef_sys_id=metricres_metric_definition
- left join = true
- Go to the following URL and no records are fetched:
https://<instance-name>.service-now.com/u_db_view_list.do?sysparm_query=metricres_metric.nameSTARTSWITHworking
Workaround
Beginning in the Vancouver release, a new left join optimizer is available to opt-into and aims to solve many of the issues observed with the previous optimizer. This new strategy is now the default in the Washington release and beyond. To enable this new strategy in Vancouver:
- Verify that the System Property 'glide.db.coerce_left_joins' does not exist. If it does exist in your instance, set it to 'true'.
- Set the System Property 'glide.db.coerce_left_joins.version' to 'V2'. If it does not exist in the instance, you can create it to apply the workaround
- Name: glide.db.coerce_left_joins.version
- Type: String
- Value: V2
- Open the link and note that now records are returned:
https://<instance-name>.service-now.com/u_db_view_list.do?sysparm_query=metricres_metric.nameSTARTSWITHworking
Prior to Vancouver, the recommended workaround is to disable the optimizer:
- Set the System Property 'glide.db.coerce_left_joins' to 'false'. If it does not exist in the instance, you can create it to apply the workaround:
- Name: glide.db.coerce_left_joins
- Type: True|False
- Value: false
- Open the link and note that now records are returned:
https://<instance-name>.service-now.com/u_db_view_list.do?sysparm_query=metricres_metric.nameSTARTSWITHworking
Related Problem: PRB1326267