Why does ReadRel model Filter inside and what is your thought on modeling table functions like explode?

[zhangyt26]

1. Why does ReadRel model Filter inside?
   There is a FilterRel, and you can easily model the Filter inside ReadRel by chaining a relation through FilterRel -> ReadRel. What is the rationale for including filter expression inside ReadRel?

2. What is your opinion for operators like explode or unnest in other systems?
   I see pos_explode and explode as not implemented in the table function section. are you viewing table function as something different than a relation operator like a join or a group by?
   fwiw, I think there are lots of syntax floating out there that I don't know how to view them. for example, duckdb unnest is just an expression against a list column. druid is in the from clause in a really weird way like trino.

[westonpace]

1. In some cases it is possible to filter on a column without loading that column into memory. For example, if there is an index of some kind on that column and the filter is very basic. So a filter on a read relation doesn't have to be the same as a read followed by a filter. That being said, this is something of a physical optimization and so it could have been saved for a lower level plan representation. I think support for a filter in the read operation is mixed. I believe it's also been argued as well that the read relations themselves are already a pretty physical / lower level notion (with named table sources being the more typical higher level logical concept).

2. I'm not familiar with explode. Is it similar to expand? If you're willing to do the work to thoroughly document how it behaves I think it would be a fine addition. I do know unnest and think it should be an operator (and not a special expression element). In general I think we should probably prefer adding more operators over adding more complexity to the expression language. Also, I believe that unnest ends up as an operator in duckdb's physical plan.

I see pos_explode and explode as not implemented in the table function section. are you viewing table function as something different than a relation operator like a join or a group by?

Table functions have not really been defined and I don't think all engines have quite the same interpretation of table functions. However, my understanding is that a table function and a relation operator are two different concepts. I think there will be a relation operator to execute table functions. For example, in duckdb a table function is a sort of "parameterized source relation". It can't take in two inputs and join them together like a join relation so you wouldn't be able to replace a join relation with a table function.

fwiw, I think there are lots of syntax floating out there that I don't know how to view them. for example, duckdb unnest is just an expression against a list column. druid is in the from clause in a really weird way like trino.

Yes, once you start getting into the more detailed concepts of SQL I think the nuances between engines grows. One of the goals of Substrait is to remove this nuance by very clearly defining the various concepts and, in some cases, having multiple definitions or flags controlling behaviors (we aren't trying to define the "right way" as much as we are trying to define "all common ways").

[zhangyt26]

Thanks for the reply!

#1
Idk, I really appreciate the attempt of substrait wanting to unify the IR a little bit. Knowing it is inspired by calcite, I come in thinking we are staying mostly at logical level. However I guess because various projects like velox and data fusion, the use case is now mostly driven by physical plan? That disconnect is a bit annoying and a bit disappointing as well.

#2
Yeah I think I saw unnest as a logical operator in duckDB. I think on the substait documentation, explode and pos_explode was listed as a table function (designed, but not implemented), but they are just relation operators, yeah?
I see what you mean by table functions now. If I replace table function with user defined table function, will that be it?

[drin]

IMO, substrait is not strictly logical or physical.

Whether something goes in a logical or physical plan appears to me to be system-dependent anyways so I think it will be more beneficial for you to be more flexible in how you view a plan represented in substrait.