Hi Daniel, If you are able to create a valid SQL for the plan using the CalciteSqlDialect then the fact that it is not possible to do so using BigQuerySqlDialect is likely a bug. Please log a JIRA if that's the case.
What happens when you parse some of the example SQL queries you mentioned above. What's the generated plan after SqlToRelConverter? Did you generate a similar plan when you directly used the RelBuilder API? Maybe you can take some inspiration on how to use the RelBuilder by looking into tests that introduce an uncollect operator in SqlToRelConverterTest [1]. Best, Stamatis [1] https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java#L1908 On Tue, Nov 16, 2021 at 3:18 PM Daniel (Spotify) Ståhl <[email protected]> wrote: > Hi, > Sorry for being unclear. We have tried to generate SQL similar to the three > examples using the RelBuilder and the Uncollect expression. Something like > this. > > relBuilder.scan("table"); > final Holder<RexCorrelVariable> correlVar = Holder.empty(); > relBuilder.variable(correlVar); > relBuilder.values(new String[] {"dummy"}, ""); > relBuilder.project( > relBuilder.alias( > relBuilder.dot(relBuilder.field(correlVar.get(), "history"), > "all_of_history"), > "unnest_all_of_history")); > relBuilder.uncollect(List.of("unnest_all_of_history"), false); > relBuilder.correlate(JoinRelType.INNER, correlVar.get().id); > RexNode userIdNode = relBuilder.alias(relBuilder.field("id"), "id"); > RexNode isActive = > relBuilder.alias( > relBuilder.dot(relBuilder.field("unnest_all_of_history"), > "is_active"), > "is_active"); > relBuilder.project(userIdNode, reportingRegion); > RelNode result = relBuilder.build(); > > We can generate SQL using the CalciteSqlDialect but we get an exception > when using the BigQuerySqlDialect. We wonder what the best way to work with > UNNEST in relational algebra using RelBuilder for BigQuery. > > Thanks. > > > > On Tue, Nov 16, 2021 at 2:49 PM Thomas Rebele <[email protected]> > wrote: > > > Hello, > > > > I don't understand what you want to do here. There's an UNNEST operator > in > > Calcite, see the comment at the bottom of this section: > > https://calcite.apache.org/docs/reference.html#collection-functions. > > > > Cordialement / Best Regards, > > *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com > > > > > > On Tue, Nov 16, 2021 at 2:30 PM Daniel (Spotify) Ståhl > > <[email protected]> wrote: > > > > > Hi > > > We are trying to generate relational algebra for SQL that uses UNNEST > of > > > arrays in BigQuery. Here are three examples of SQL (in Big Query > standard > > > query syntax) that we want to be able to handle and generate from > > > relational algebra. Any tips on how you would do that in Calcite? > > > > > > SELECT id, > > > (SELECT h.is_active FROM UNNEST(history.all_of_history) h > > > WHERE start_date <= "2021-06-01" AND (end_date >= "2021-06-01" OR > > > end_date IS NULL)) > > > FROM `table`; > > > > > > SELECT id, h.is_active > > > FROM `table` > > > CROSS JOIN UNNEST(history.all_of_history) h; > > > > > > SELECT id, count(h) as h > > > from `table` > > > CROSS JOIN UNNEST(history.all_of_history) h > > > GROUP BY id; > > > > > > The table "table" has two columns: "id" and "history". history is a > > record > > > and "history.all_of_history" > > > is a repeated record with three fields ("is_active", "start_date" and > > > "end_date"). > > > > > > Thanks, > > > Daniel Ståhl > > > > > >
