ClickHouse / ClickHouse/ClickHouse

Lazy column loading does not work with `if` and CTEs

Open
#79,771 2 comments 0 reactions 0 assignees View on GitHub
comp-query-optimizer external unexpected behaviour
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
20h 33m
Merged PRs (30d)
501

Description

### Company or project name

_No response_

### Describe the unexpected behaviour

We came across an unexpected behaviour on a query that was working on v24.5 that is not working as expected on v25.3. We belive it is due to the lazy column loading behaviour recently introduced.

### How to reproduce

ClickHouse server version: 25.3

Original query:

```sql
:) create table a (id Int32) Engine=Memory;
:) create table b (id Int32, email Nullable(String)) Engine=Memory;
:) insert into a values (1), (2), (3);
:) insert into b values (2, 'two@example.com');
:) select a.id, if(b.email is null, false, true) as has_email from a left join b on a.id = b.id;

┌─id─┬─has_email─┐
1. │ 1 │ true │
2. │ 2 │ true │
3. │ 3 │ true │
└────┴───────────┘
```

Works as expected when explicitly projecting the column inside the `if` function.

```sql
:) select a.id, b.email, if(b.email is null, false, true) as has_email from a left join b on a.id = b.id;

┌─id─┬─email───────────┬─has_email─┐
1. │ 1 │ ᴺᵁᴸᴸ │ false │
2. │ 2 │ two@example.com │ true │
3. │ 3 │ ᴺᵁᴸᴸ │ false │
└────┴─────────────────┴───────────┘
```

Selecting inside CTEs fails to load the column as well:

```sql
:) with ds as (select a.id, b.email, if(b.email is null, false, true) as has_email from a left join b on a.id = b.id) select id, has_email from ds;

┌─id─┬─has_email─┐
1. │ 1 │ true │
2. │ 2 │ true │
3. │ 3 │ true │
└────┴───────────┘

:) with ds as (select a.id, b.email, if(b.email is null, false, true) as has_email from a left join b on a.id = b.id) select id, email, has_email from ds;

┌─id─┬─email───────────┬─has_email─┐
1. │ 1 │ ᴺᵁᴸᴸ │ false │
2. │ 2 │ two@example.com │ true │
3. │ 3 │ ᴺᵁᴸᴸ │ false │
└────┴─────────────────┴───────────┘
```

### Expected behavior

Columns inside funtion calls should be loaded. CTEs should be evaluated eagerly. Behaviour should at least show a warning.

### Error message and/or stacktrace

_No response_

### Additional context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.