ClickHouse / ClickHouse/dbt-clickhouse

Incremental update errors with `NUMBER_OF_COLUMNS_DOESNT_MATCH` with tuple unique_key

Open
#84 1 comment 8 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.