The query against the base users table completes successfully, while the view-based query fails with an INDEX_NOT_USED exception.
Does it reproduce on recent release?
Yes.
How to reproduce
I think the fiddle contains all steps necessary to reproduce. In case the fiddle is altered in the meantime, below is the same query text:
fiddle code reproduced here
```sql
CREATE TABLE users (
uid Int16,
name String,
department String,
started DateTime64()
) ENGINE=MergeTree
primary key (department);
create view users_with_previous as
SELECT
uid,
name,
department,
lagInFrame(uid, 1) over (partition by department order by started asc) as previous_user
from users;
INSERT INTO users VALUES (1231, 'John', 'Sales', '2023-06-08 12:00:00');
INSERT INTO users VALUES (6666, 'Ksenia', 'Engineering', '2023-06-09 12:00:00');
INSERT INTO users VALUES (8888, 'Alice', 'Engineering', '2023-06-10 12:00:00');
-- completes successfully
select
uid,
name,
department,
lagInFrame(uid, 1) over (partition by department order by started asc) as previous_user
from
users
where
department = 'Engineering'
settings force_primary_key=1;
-- does not complete successfully
select
*
from
users_with_previous
where
department = 'Engineering'
settings force_primary_key=1;
```
Expected behavior
I would expect both select queries in the code sample provided to return the same result.
Error message and/or stacktrace
Received exception from server (version 23.5.3):
Code: 277. DB::Exception: Received from localhost:9000. DB::Exception: Primary key (department) is not used and setting 'force_primary_key' is set. (INDEX_NOT_USED)
Describe what's wrong
Predicate pushdown does not seem to operate as expected when a view query contains a window function. I've included a link to a fiddle that contains:
users
tableusers
and each withforce_primary_key
enabled:users
table using the same query as the viewFiddle: https://fiddle.clickhouse.com/987e6c0e-e605-4625-a614-9e1f015fc668
The query against the base
users
table completes successfully, while the view-based query fails with anINDEX_NOT_USED
exception.Does it reproduce on recent release?
Yes.
How to reproduce I think the fiddle contains all steps necessary to reproduce. In case the fiddle is altered in the meantime, below is the same query text:
fiddle code reproduced here
```sql CREATE TABLE users ( uid Int16, name String, department String, started DateTime64() ) ENGINE=MergeTree primary key (department); create view users_with_previous as SELECT uid, name, department, lagInFrame(uid, 1) over (partition by department order by started asc) as previous_user from users; INSERT INTO users VALUES (1231, 'John', 'Sales', '2023-06-08 12:00:00'); INSERT INTO users VALUES (6666, 'Ksenia', 'Engineering', '2023-06-09 12:00:00'); INSERT INTO users VALUES (8888, 'Alice', 'Engineering', '2023-06-10 12:00:00'); -- completes successfully select uid, name, department, lagInFrame(uid, 1) over (partition by department order by started asc) as previous_user from users where department = 'Engineering' settings force_primary_key=1; -- does not complete successfully select * from users_with_previous where department = 'Engineering' settings force_primary_key=1; ```Expected behavior
I would expect both
select
queries in the code sample provided to return the same result.Error message and/or stacktrace