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").