Predicate pushdown through exclude_columns

[takezoe]

Currently, predicates are not pushed down to table scan when we use exclude_columns.

SELECT * 
FROM TABLE(exclude_columns(
  input => TABLE(tpch.sf1.customer), 
  columns => DESCRIPTOR(c_comment)
))
WHERE c_custkey = 1

Of course, we can write as follows:

SELECT * 
FROM TABLE(exclude_columns(
  input => TABLE(SELECT * FROM tpch.sf1.customer WHERE c_custkey = 1), 
  columns => DESCRIPTOR(c_comment)
))

However, it would be great if predicates will be pushd down through exclude_columns even in the first case.

[findinpath]

@kasiafi pls triage.

[kasiafi]

If we wanted to implement predicate pushdown into table function on the engine level, we must consider that:

1. Table functions take two types of table arguments:

• arguments with row semantics -- the function result is computed on row basis
• arguments with set semantics -- the function result is computed on partition basis
  It is legal to push predicate into a row semantics source, but not into a set semantics source (unless the predicate is based on the partitioning columns and the source is PRUNE WHEN EMPTY).

2. Table functions output two types of columns

• proper columns -- produced by the table function
• pass-through columns -- passed directly from the input
  We can only push predicates based on the pass-through columns.

If we implemented a generic pushdown rule with the above limitations, sadly the exclude_columns function would not benefit. It has a row semantics source, but it is implemented in the way that all columns are proper, not pass-through.

To support pushdown for exclude_columns as well as for other table functions which cannot be handled in the generic way, we would have to extend the SPI so that the connector can decide about pushdown based on the semantics of the particular function. It is possible to do but requires discussion and careful design.

[takezoe]

Ugh... Sounds overkill for this particular purpose. I wonder if simply rewriting exclude_columns to projection could work.