Database Views with "left joins" fail to retrieve correct data


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

  1. Create a Database View with the left join as follows:
    1. Name = u_db_view
    2. 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
    3. Add another view table to the database view:
      1. table=asmt_metric_definition
      2. Order= 200
      3. Variable Prefix = mdef
      4. Where clause = mdef_sys_id=metricres_metric_definition
      5. left join = true
  2. Go to the following URL and no records are fetched:


  1. 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
  2. Open the link and note that now records are returned:

Related Problem: PRB1326267