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 Set the System Property ' glide.db.coerce_left_joins ' to ' false '. If it doesn't exists 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