Query with scalar subquery having mathematical function returns 0 rows

[pratyakshsharma]

In case when a query involves scalar subquery with mathematical function and the subquery returns no rows, the entire query returns no rows. This only happens in cases with mathematical functions present in the subquery.

Your Environment

• Presto version used: 0.290-SNAPSHOT (latest master)
• Storage (HDFS/S3/GCS..):
• Data source and connector used: hive connector
• Deployment (Cloud or On-prem): local machine
• Pastebin link to the complete debug logs:

Expected Behavior

The scalar subquery should return null and other selected columns should return proper values.

Current Behavior

When the scalar subquery returns no rows, the entire query returns 0 rows.

Possible Solution

The query plan in such cases is introducing an empty Values Node in case simplify_plan_with_empty_input is set to true (It is true by default). If it is false, PickTableLayoutForPredicate rule removes TableScan node for the columns not in the subquery. Also an additional FilterNode with false predicate gets introduced.

Steps to Reproduce

Run the below simple query -

select
  comment,
  (
    select
      name
    from
      tpch.tiny.nation
    where
      nationkey = 1
      and regionkey = 1
      and width_bucket(nationkey, 1, 100, 10) = 2
  )
from
  tpch.tiny.nation;

Screenshots (if appropriate)

Outputs

When simplify_plan_with_empty_input is true

When simplify_plan_with_empty_input is false

Context