ClickHouse / ClickHouse/dbt-clickhouse
Incremental update errors with `NUMBER_OF_COLUMNS_DOESNT_MATCH` with tuple unique_key
- Dominant language
- Python
- Stars
- 362
- Forks
- 177
- Avg merge
- 2d 10h
- Merged PRs (30d)
- 8
Description
Hi, I can't create incremental table with tuple unique_key. I prepared two reproducible examples:
```
{{ config(order_by='(id1, id2)', engine='MergeTree()', materialized='incremental', unique_key='(id1, id2)') }}
select 1 as id1, 2 as id2
```
```
{{ config(order_by='(id1, id2)', engine='MergeTree()', materialized='incremental', unique_key='id1, id2') }}
select 1 as id1, 2 as id2
```
The error I get is
` Code: 20. DB::Exception: Number of columns in section IN doesn't match. 2 at left, 1 at right. (NUMBER_OF_COLUMNS_DOESNT_MATCH) (version 22.1.3.7 (official build))`
The reason seems to be excess brackets in generated query here:
```sql
where (id1, id2) not in (
select (id1, id2) -- '(' and ')' are breaking query here
from example_without_brackets__dbt_tmp
)
```
Also, after error tables `*__dbt_old`, `*__dbt_tmp` don't clean up from DB and next time I'm trying to --run I get error
`Cache inconsistency detected: in rename, new key _ReferenceKey(database=None, schema='default', identifier='example_with_brackets__dbt_old') already in cache:`
[dbt.log](https://github.com/ClickHouse/dbt-clickhouse/files/9159276/dbt.log)
clickhouse-version: `22.1.3.7`
```
dbt --version
Core:
- installed: 1.1.1
- latest: 1.1.1 - Up to date!
Plugins:
- clickhouse: 1.1.7 - Up to date!
```
Contributor guide
Research direction
Start with the two incremental model configurations in the issue and the linked dbt.log, then inspect the generated query containing the tuple in the NOT IN subquery. Reproduce on the stated dbt-clickhouse and ClickHouse versions, and trace the failure cleanup. Done means tuple unique_key updates run successfully and the __dbt_old and __dbt_tmp tables are removed after an error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- clickhouse, python
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100