Skip to content

Exception Cannot find column if make select distinct from view and view contains window function and group by #62820

@handgunman

Description

@handgunman

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

Metadata

Metadata

Assignees

Labels

analyzerIssues and pull-requests related to new analyzerbugConfirmed user-visible misbehaviour in official release

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions