Confusing error message: Code: 47. DB::Exception: Unknown identifier

[jovezhong]

Describe what's wrong

This is reported by a community user.

To convert to our car_live_data data set, this works

SELECT *,current_rrdvalue,lag(current_rrdvalue) OVER(PARTITION BY cid) AS prev_rrdvalue
FROM(
SELECT window_start, cid, last_value(longitude) as current_rrdvalue
FROM tumble(car_live_data,10s) GROUP BY window_start, cid
)

But if we add a column to do some flat transformation current_rrdvalue-prev_rrdvalue AS gauge, it will fail with a confusing error message

SELECT *,current_rrdvalue,lag(current_rrdvalue) OVER(PARTITION BY cid) AS prev_rrdvalue,current_rrdvalue-prev_rrdvalue AS gauge 
FROM(
SELECT window_start, cid, last_value(longitude) as current_rrdvalue
FROM tumble(car_live_data,10s) GROUP BY window_start, cid
)

Code: 47. DB::Exception: Unknown identifier: lag(current_rrdvalue) OVER (PARTITION BY cid); there are columns: window_start, cid, current_rrdvalue. (UNKNOWN_IDENTIFIER) (version 1.3.31).

The workaround is to use another outerquery

SELECT *,current_rrdvalue-prev_rrdvalue AS gauge FROM(
SELECT *,current_rrdvalue,lag(current_rrdvalue) OVER(PARTITION BY cid) AS prev_rrdvalue 
FROM(
SELECT window_start, cid, last_value(longitude) as current_rrdvalue
FROM tumble(car_live_data,10s) GROUP BY window_start, cid
)
)

The concern is the error message Unknown identifier: lag(current_rrdvalue) OVER (PARTITION BY cid); there are columns: window_start, cid, current_rrdvalue is nothing to do with the extra column which led this issue current_rrdvalue-prev_rrdvalue AS gauge

How to reproduce

Error message and/or stacktrace

Additional context

[yokofly]

https://timepluscommunity.slack.com/archives/C05QRJ5RS5A/p1706037213601879 I cherry-picked this branch commit, but still did not work.

c1 :) CREATE STREAM default.car_live_data
      (
        `in_use` bool,
        `longitude` float32,
        `latitude` float32,
        `speed_kmh` uint32,
        `gas_percent` decimal(10, 2),
        `total_km` float32,
        `locked` bool,
        `cid` string,
        `time` datetime64(3),
        `_tp_time` datetime64(3) DEFAULT time,
        INDEX _tp_time_index _tp_time TYPE minmax GRANULARITY 2
      )
      ENGINE = Stream(1, 1, rand())
      PARTITION BY to_YYYYMM(_tp_time)
      PRIMARY KEY to_YYYYMMDD(_tp_time)
      ORDER BY to_YYYYMMDD(_tp_time)
      TTL to_datetime(_tp_time) + INTERVAL 30 DAY
      SETTINGS subtype = 'tabular', logstore_retention_bytes = '604800000', logstore_retention_ms = '1342177280', index_granularity = 8192;
c1 :) SELECT *,current_rrdvalue,lag(current_rrdvalue) OVER(PARTITION BY cid) AS prev_rrdvalue,current_rrdvalue-prev_rrdvalue AS gauge 
      FROM(
      SELECT window_start, cid, last_value(longitude) as current_rrdvalue
      FROM tumble(car_live_data,10s) GROUP BY window_start, cid
      )
      ;

SELECT
  *, current_rrdvalue, lag(current_rrdvalue) OVER (PARTITION BY cid) AS prev_rrdvalue, current_rrdvalue - prev_rrdvalue AS gauge
FROM
  (
    SELECT
      window_start, cid, last_value(longitude) AS current_rrdvalue
    FROM
      tumble(car_live_data, 10s)
    GROUP BY
      window_start, cid
  )

Query id: 0663fa23-7ef6-4990-8c24-11dfade2b28d

0 rows in set. Elapsed: 0.225 sec. 

Received exception from server (version 1.4.1):
Code: 47. DB::Exception: Received from localhost:8463. DB::Exception: Unknown identifier: lag(current_rrdvalue) OVER (PARTITION BY cid); there are columns: window_start, cid, current_rrdvalue. (UNKNOWN_IDENTIFIER)

c1 :)