If I'm understanding correctly, this query is taken from an existing test [1], the only modification is the addition of "WHERE purchases > 3".
This seems like a bug -- I'm guessing the planner/matching logic doesn't handle filters properly. To really figure out what's going on, you'd have to compare the plans for the same query with and without the filter. [1] https://github.com/apache/beam/blob/9dbc7b7ceed7b744d90bbf771a9059ea91965353/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamAnalyticFunctionsTest.java#L89-L95 On Tue, May 11, 2021 at 3:08 PM Burkay Gur <[email protected]> wrote: > Hi folks, > > When we try to run the following query on BeamSQL: > > SELECT item, purchases, category, sum(purchases) over (PARTITION BY > category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT > ROW) as total_purchases FROM PCOLLECTION WHERE purchases > 3 > > We are getting the following error: > > Unable to convert query > org.apache.beam.sdk.extensions.sql.impl.SqlConversionException: Unable to > convert query SELECT item, purchases, category, sum(purchases) over > (PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING > AND CURRENT ROW) as total_purchases FROM PCOLLECTION WHERE purchases > 3 > at > org.apache.beam.sdk.extensions.sql.impl.CalciteQueryPlanner.convertToBeamRel(CalciteQueryPlanner.java:212) > at > org.apache.beam.sdk.extensions.sql.impl.BeamSqlEnv.parseQuery(BeamSqlEnv.java:111) > at > org.apache.beam.sdk.extensions.sql.SqlTransform.expand(SqlTransform.java:171) > at > org.apache.beam.sdk.extensions.sql.SqlTransform.expand(SqlTransform.java:109) > at org.apache.beam.sdk.Pipeline.applyInternal(Pipeline.java:548) at > org.apache.beam.sdk.Pipeline.applyTransform(Pipeline.java:499) at > org.apache.beam.sdk.values.PCollection.apply(PCollection.java:370) at > > We traced the issue back to this PR: > https://github.com/apache/beam/pull/11975 specifically this line: > https://github.com/apache/beam/pull/11975/files#diff-919be1e4bcc11c17b725cbf04168b583886ffe16286f9291893247954128ad81R43 > > What are the plans on a wider support for analytical functions? If I want > to contribute, what are the best resources to learn more about how BeamSQL > / Calcite integration is set up? > > Best, > Burkay > >
