Scalar subquery filters not pushed down to TableScan #21324

Describe the bug

Example query (TPC-H Q22):

  SELECT ... FROM customer
  WHERE c_acctbal > (SELECT avg(c_acctbal) FROM customer WHERE ...)

It would be good to push the predicate down to the scan on customer.

The problem with doing this now is that filter_partitions calls create_physical_expr with a fresh ExecutionProps, so we don't have a way to pass the ScalarSubqueryResults to any ScalarSubqueryExpr nodes in the filter expression. This should be fixable in theory.

We'd also need to update extract_dml_filters to apply subquery filters.

This is distinct from using subquery filters for partition pruning, which would be more difficult (we'd need to evaluate the subquery at planning-time).

To Reproduce

No response

Expected behavior

No response

Additional context

No response