Regression in 1.9.3: Unit Tests With Fixtures Failing With "Temporary tables cannot be inside a database"

[enqueue]

Describe the bug

When running a unit test with fixtures using dbt-clickhouse version 1.9.3, the following error occurs:

Code: 442. DB::Exception: Temporary tables cannot be inside a database.
You should not specify a database for a temporary table.
(BAD_DATABASE_FOR_TEMPORARY_TABLE)

This error does not occur using dbt-clickhouse version 1.9.2.

Query in 1.9.3:

create temporary table `dbname`.`testname__dbt_tmp` engine Memory -- end_of_sql
 as ( select * from ( with __dbt__cte__table_to_test as ...

Query in 1.9.2:

create temporary table `testname__dbt_tmp`  engine Memory  -- end_of_sql
as ( select * from (  with __dbt__cte__table_to_test as ...

Steps to reproduce

1. Create a unit test with a CSV fixture
2. Run dbt test

Expected behaviour

Test to be green ✅

Code examples, such as models or profile settings

I could perhaps create a reproducer if it is really required, but it would take me some time. Perhaps you can already pinpoint the error without having a complete example project.

dbt and/or ClickHouse server logs

ClickHouse error log:

<Error> executeQuery: Code: 442. DB::Exception: Temporary tables cannot be inside a database. You should not specify a database for a temporary table. (BAD_DATABASE_FOR_TEMPORARY_TABLE)

dbt log:

10:45:52    Runtime Error in unit_test_my_test (models/basic/my_test.yml)
  An error occurred during execution of unit test 'my_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    Code: 442.
    DB::Exception: Temporary tables cannot be inside a database. You should not specify a database for a temporary table.. Stack trace:

Configuration

Environment

• dbt version: 1.10.8
• dbt-clickhouse version: 1.9.3
• clickhouse-driver version: 0.2.9
• clickhouse-connect version: 0.8.17
• Python version: 3.13.7
• Operating system: Linux

ClickHouse server

• ClickHouse Server version: 25.8.1.5101
• ClickHouse Server non-default settings, if any: none
• CREATE TABLE statements for tables involved: n/a
• Sample data for these tables, use clickhouse-obfuscator if necessary: n/a

[koletzilla]

Hi @enqueue! Thanks for reporting it and for giving all these details.

I'm trying to replicate it locally but I'm not having problems to execute dbt test. Can you help me replicate it? Maybe it would be enough with the example of the kind of test that makes that fail or some details about the model it reads from.

I have the feeling that this may be related to 1fb1041 but I'm not 100% sure right now.

[enqueue]

Hi @koletzilla , I created reproducer here, based on jaffle-shop demo project.

Go through usual steps, .e.g.

1. python -m venv .venv
2. source .venv/bin/activate
3. python -m pip install --upgrade pip
4. python -m pip install -r requirements.txt
5. source .venv/bin/activate
6. dbt deps
7. Create schema raw
8. Create schema ch514
9. Set DBT_USER to ClickHouse user
10. Set DBT_PASSWORD to ClickHouse password
11. dbt seed --full-refresh --vars '{"load_source_data": true}'
12. dbt build -f

Failure 😢

Change dbt-clickhouse to 1.9.2 in requirements.in, run 'pip install -r requirements.in', and run 'dbt build -f' again.

Success 😄

[koletzilla]

Thanks for that. I was able to catch the problem: We are not correctly managing when a schema (CH database) needs to be used when dealing with creating/querying temporary tables, and 1fb1041 made this worse.

We are going to take a look at this and fix it for the next release, 1.9.4.

I'm sorry for the inconvenience!