Well the good news here is that Postgres and Drill agree on how this should work (and we both implement it).
We also have a bunch of customers leveraging the flatten in select behavior. I land a little different than Julian on how hard learning something new is. (See xquery...) It seems to me that if this pattern is unambiguous and doesn't conflict that we should continue to expose it to our users. We need to come up with how to incorporate it in Calcite such that it doesn't disrupt or constrain those who don't want this behavior. Julian, have you spent time thinking about how to extend validator and sql2rel? If you're going to fight inclusion in core Calcite, we need to think through how to solve in a collaborative way. The unnest/flatten naming thing is a bit challenging. What should we call the select t.a.b.c.* functionality? That actually seems more like an UNNESTing. Ugh... -- Jacques Nadeau CTO and Co-Founder, Dremio On Mon, Oct 12, 2015 at 5:28 PM, Julian Hyde <[email protected]> wrote: > > > On Oct 12, 2015, at 3:42 PM, Jacques Nadeau <[email protected]> wrote: > > > > - we have shortcut for a lateral join combined with a table function used > > in the select clause > > It’s funny, Postgres has a short-cut that allows you to use UNNEST in the > SELECT clause[1]. James and I discussed it for Phoenix Unnest support[2], > and I’ll recap what I said there. > > The semantics of a table expression in the SELECT clause are weird, > because you get multiple rows out for each row in. It gets even weirder if > you have more than one table expression in the SELECT clause and some > non-table expressions too. Presumably it should return the cartesian > product. > > LINQ (and Spark) has “selectMany”, which is like “select” except that the > expression is a collection and one row is output for each member of the > collection. Bart de Smet claims that selectMany is powerful enough to > subsume the other relational operators (see [3] around 48 minutes). So, I’m > tempted to add “SELECT MANY” to Calcite SQL. But I think the way postgres > did it — changing the behavior of the SELECT clause if it happens to > contain an UNNEST function — is wrong. The workaround — learning how to use > UNNEST or indeed a table function such as FLATTEN in the FROM clause — is > not too hard. > > Julian > > [1] > http://stackoverflow.com/questions/23003601/sql-multiple-unnest-in-single-select-list > < > http://stackoverflow.com/questions/23003601/sql-multiple-unnest-in-single-select-list > > > > [2] https://issues.apache.org/jira/browse/PHOENIX-953 < > https://issues.apache.org/jira/browse/PHOENIX-953> > > [3] > https://channel9.msdn.com/Shows/Going+Deep/Bart-De-Smet-MinLINQ-The-Essence-of-LINQ > < > https://channel9.msdn.com/Shows/Going+Deep/Bart-De-Smet-MinLINQ-The-Essence-of-LINQ > >
