Derive effective predicate for Values

[martint]

This allows Join inference to push the derived predicate
to the other side when one side is based on a Values node.

For a query like:

 SELECT 1
 FROM orders JOIN (values 'O', 'F') t(x) ON orders.orderstatus = t.x;

It changes the plan from:

  - Output[_col0]
    - Project[]
      - InnerJoin[("orderstatus" = "field")]
        - TableScan[tpch:orders:sf0.01]
            orderstatus := tpch:orderstatus
                 :: [[F], [O], [P]]
        - Values
             ('O')
             ('F')

to:

  - Output[_col0]
    - Project[]
      - InnerJoin[("orderstatus" = "field")]
        - ScanFilter[table = tpch:orders:sf0.01, filterPredicate = ("orderstatus" IN ('F', 'O'))]
             orderstatus := tpch:orderstatus
                  :: [[F], [O]]
        - Filter[filterPredicate = ("field" IN ('F', 'O'))]
          - Values
               ('O')
               ('F')

[sopel39]

Can we do something about excessive filter:

- Filter[filterPredicate = ("field" IN ('F', 'O'))]

I saw this problem before with table scans and partition columns.
When partition values from one side of join were pushed to the other side, then there is always filter above table scan with col IN partition_values.
I tried to tackle it in starburstdata/facebook-presto@7081f3e
but it didn't work unfortunately because inference cannot easily reduce

inheritedValue = 1 OR inheritedValue = 2
inheritedValue = tableScanValue
tableScanValue = 1 OR tableScanValue = 2

to simply true (after simplification) and TupleDomain doesn't have true contains method

[martint]

Unfortunately, there's not much we can do about it, and I haven't investigated it further. On the other hand, it's a filter over a ValuesNode, so it's inexpensive.

[sopel39]

It could be meaningful though if filter contains 100s of partition values, but it's out of scope of this PR

[sopel39]

maybe ValuesNode should only contain Literal as rows (I think it implicitly does)

[martint]

Eventuallly, we should probably have a physical vs logical values node or something else to represent that. Currently, a values node inside a correlated subquery could have a reference to the outer scope.

[sopel39]

Currently, a values node inside a correlated subquery could have a reference to the outer scope.

That's a problem on it's own. See: #473

[martint]

It’s not a “problem” per se. It’s legitimate for a query to reference variables from the enclosing scope. After all, filters, projections, subqueries, etc, are nothing but capturing lamba expressions (if you squint a little)

[sopel39]

After all, filters, projections, subqueries, etc, are nothing but capturing lamba expressions (if you squint a little)

Still, I don't think correlated symbols should appear out of nothing in any node that contains Expression, but should rather be injected into subquery via some kind of special ProjectCorrelations node. This way subquery support can be narrowed down in various places.

[martint]

That would be a “bind” operation, just like lambda(x) { ... } binds x.

The next generation of the IR that I’ve been prototyping for a while will likely unify both concepts. Anyway, topic for another time :)

[sopel39]

nit: you could still derive domains for other symbols

[martint]

No, the predicate needs to be an “upper bound”. If we had a single row, we could do that, but otherwise, there’s no way to formulate such a predicate.