Database Views with "left joins" fail to retrieve correct data - Known Error

Database Views with "left joins" fail to retrieve correct data 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:// .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:// .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:// .service-now.com/u_db_view_list.do?sysparm_query=metricres_metric.nameSTARTSWITHworking Related Problem: PRB1326267