[SOLVED] Clickhouse leadInFrame inside COALESCE return Unknown identifier

Clickhouse leadInFrame inside COALESCE return Unknown identifier

I'm following Altinity's examples on how to implement Lag/Lead functions https://kb.altinity.com/altinity-kb-queries-and-syntax/lag-lead/ But I can't find a way to replace NULLs with other values.

Using that example and adding toNullable(a) you can see that many values are going to be NULL.

SELECT
    g,
    a,
    lagInFrame(toNullable(a)) OVER (PARTITION BY g ORDER BY a ASC Rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev,
    leadInFrame(a) OVER (PARTITION BY g ORDER BY a ASC Rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next
FROM llexample
ORDER BY
    g ASC,
    a ASC

Query id: 65c75108-520f-4115-8996-328e8e62aa25

┌─g─┬──────────a─┬───────prev─┬───────next─┐
│ 0 │ 2020-01-01 │       ᴺᵁᴸᴸ │ 2020-01-04 │
│ 0 │ 2020-01-04 │ 2020-01-01 │ 2020-01-07 │
│ 0 │ 2020-01-07 │ 2020-01-04 │ 2020-01-10 │
│ 0 │ 2020-01-10 │ 2020-01-07 │ 1970-01-01 │
│ 1 │ 2020-01-02 │       ᴺᵁᴸᴸ │ 2020-01-05 │
│ 1 │ 2020-01-05 │ 2020-01-02 │ 2020-01-08 │
│ 1 │ 2020-01-08 │ 2020-01-05 │ 1970-01-01 │
│ 2 │ 2020-01-03 │       ᴺᵁᴸᴸ │ 2020-01-06 │
│ 2 │ 2020-01-06 │ 2020-01-03 │ 2020-01-09 │
│ 2 │ 2020-01-09 │ 2020-01-06 │ 1970-01-01 │
└───┴────────────┴────────────┴────────────┘

I tried to add leadInFrame inside a COALESCE. But when I try to do that I get the error:

SELECT
    g,
    a,
    COALESCE(
      lagInFrame(toNullable(a)) OVER (PARTITION BY g ORDER BY a ASC Rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
      today()
    ) AS prev,
    leadInFrame(a) OVER (PARTITION BY g ORDER BY a ASC Rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next
FROM llexample
ORDER BY
    g ASC,
    a ASC

Query id: 9685b822-7f31-45d3-9103-89f06b373876


0 rows in set. Elapsed: 0.002 sec.

Received exception from server (version 22.1.2):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Unknown identifier: lagInFrame(toNullable(a)) OVER (PARTITION BY g ORDER BY a ASC Rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING); there are columns: g, a, toNullable(a): While processing g, a, coalesce(lagInFrame(toNullable(a)) OVER (PARTITION BY g ORDER BY a ASC Rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), today()) AS prev, leadInFrame(a) OVER (PARTITION BY g ORDER BY a ASC Rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next. (UNKNOWN_IDENTIFIER)

I also tried other conditionals and got the same error.

Best

Solution

https://github.com/ClickHouse/ClickHouse/issues/19857 You simply need to use subquery, because window functions are not fully functional.

select 
  g,
  a,
  COALESCE( prev,  today()) prev,
  next
from (  
    SELECT
        g,
        a,
        lagInFrame(toNullable(a)) OVER (PARTITION BY g ORDER BY a ASC Rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) prev,
        leadInFrame(a) OVER (PARTITION BY g ORDER BY a ASC Rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next
    FROM llexample
    ORDER BY
        g ASC,
        a ASC
)

Update: it was fixed in June 2022

v22.7.1.2484-stable.md:* Support expressions with window functions. Closes #19857. #37848 (Dmitry Novik).