LEAD/LAG functions

[Patrick Angeles]

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

[Timo Walther]

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*
>

[Jark Wu-3]

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 <[hidden email]> 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*
>

[Patrick Angeles]

Thanks, Jark.

On Mon, Feb 1, 2021 at 11:50 PM Jark Wu <[hidden email]> wrote:

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 <[hidden email]> 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*
>