-
Notifications
You must be signed in to change notification settings - Fork 8.3k
Exception Cannot find column if make select distinct from view and view contains window function and group by #62820
Description
If the view contains a grouping and a windowing function, querying with distinct results in an error.
CREATE TABLE default.t
(
id Int32,
val Nullable(Float64),
dt Nullable(DateTime64(6)),
type Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY id
CREATE VIEW default.t_v
AS SELECT
t1.type AS type,
sum(t1.val) AS sval,
toStartOfDay(t1.dt) AS sday,
anyLast(sum(t1.val)) OVER w
FROM default.t AS t1
GROUP BY
type,
t1.dt
WINDOW w AS (PARTITION BY type ORDER BY dt ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
SELECT DISTINCT type
FROM t_v
Received exception from server (version 24.3.2):
Code: 47. DB::Exception: Received from 84.201.141.233:9000. DB::Exception: Cannot find column anyLast(sum(__table1.val)) OVER (PARTITION BY __table1.type ORDER BY __table1.dt ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1_UInt8 PRECEDING) in ActionsDAG result. (UNKNOWN_IDENTIFIER)
Example in fiddle
https://fiddle.clickhouse.com/f3f2dac4-183d-4748-85ae-9b25452684c9
The problem is also reproduced on new installations and in 24.1.8.22