Yes. RANK/ROW_NUMBER is not allowed with ROW/RANGE over window, i.e. the "ROWS BETWEEN 1 PRECEDING AND CURRENT ROW" clause.
Best, Jark On Mon, 1 Feb 2021 at 22:06, Timo Walther <twal...@apache.org> wrote: > Hi Patrick, > > I could imagine that LEAD/LAG are translated into RANK/ROW_NUMBER > operations that are not supported in this context. > > But I will loop in @Jark who might know more about the limitaitons here. > > Regards, > Timo > > > On 29.01.21 17:37, Patrick Angeles wrote: > > Another (hopefully newbie) question. Trying to use LEAD/LAG over window > > functions. I get the following error. The exact same query works > > properly using FIRST_VALUE instead of LEAD. > > > > Thanks in advance... > > > > - Patrick > > > > Flink SQL> describe l1_min ; > > > > +-----------+------------------------+------+-----+--------+-----------+ > > > > |name | type | null | key | extras | watermark | > > > > +-----------+------------------------+------+-----+--------+-----------+ > > > > |symbol | STRING | true | || | > > > > | t_start | TIMESTAMP(3) *ROWTIME* | true | || | > > > > | ask_price | DOUBLE | true | || | > > > > | bid_price | DOUBLE | true | || | > > > > | mid_price | DOUBLE | true | || | > > > > +-----------+------------------------+------+-----+--------+-----------+ > > > > 5 rows in set > > > > > > Flink SQL> SELECT > > > >> symbol, > > > >> t_start, > > > >> ask_price, > > > >> bid_price, > > > >> mid_price, > > > >> LEAD (mid_price) OVER x AS prev_price > > > >> FROM l1_min > > > >> WINDOW x AS ( > > > >> PARTITION BY symbol > > > >> ORDER BY t_start > > > >> ROWS BETWEEN 1 PRECEDING AND CURRENT ROW > > > >> ) > > > >> ; > > > > *[ERROR] Could not execute SQL statement. Reason:* > > > > *org.apache.calcite.sql.validate.SqlValidatorException: ROW/RANGE not > > allowed with RANK, DENSE_RANK or ROW_NUMBER functions* > > > >
