SYSTEM SYNC REPLICA has wrong order of arguments

[jesperbagge]

Describe the bug

When materializing ReplicatedMergeTree tables on dbt-clickhouse=1.4.1, I get the following error:

Code: 62. DB::Exception: Syntax error: failed at position 185 ('ON') (line 5, col 5): ON CLUSTER "bi_dwh" . Expected one of: STRICT, LIGHTWEIGHT, PULL, end of query.

When inspecting the dbt.log file i see that the error happens when the following statement is run:

SYSTEM SYNC REPLICA <model_name> ON CLUSTER "cluster_name"

However, according to the documentation, this SQL statement should have the ON CLUSTER clause before the table name - which should probably also contain the database name.

Steps to reproduce

1. Install dbt-clickhouse==1.4.1
2. Have cluster_mode: true
3. Materialize a ReplicatedMergeTree table

Expected behaviour

The materialization works.

Code examples, such as models or profile settings

Demo model

{{
    config(
        materialized="table",
        engine="ReplicatedMergeTree",
        order_by="key,"
    )
}}

select
    rand() as key,
    randomPrintableASCII(8) as text
from numbers(200)

dbt and/or ClickHouse server logs

�[0m19:40:44.595946 [debug] [Thread-1 (]: dbt_clickhouse adapter: On model.standalone.source_data: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "clickhouse", "target_name": "dev", "node_id": "model.standalone.source_data"} */

    SYSTEM SYNC REPLICA source_data
  
    ON CLUSTER "bi_dwh"
  ...
�[0m19:40:44.679181 [debug] [Thread-1 (]: dbt_clickhouse adapter: Error running SQL: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "clickhouse", "target_name": "dev", "node_id": "model.standalone.source_data"} */

    SYSTEM SYNC REPLICA source_data
  
    ON CLUSTER "bi_dwh"
  
�[0m19:40:44.679620 [debug] [Thread-1 (]: Timing info for model.standalone.source_data (execute): 2023-05-24 19:40:43.896009 => 2023-05-24 19:40:44.679551
�[0m19:40:44.683652 [debug] [Thread-1 (]: Database Error in model source_data (models/example/source_data.sql)
  Code: 62.
  DB::Exception: Syntax error: failed at position 185 ('ON') (line 5, col 5): ON CLUSTER "bi_dwh"
    . Expected one of: STRICT, LIGHTWEIGHT, PULL, end of query. Stack trace:
  
  0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe3bb175 in /usr/bin/clickhouse
  1. ? @ 0x8edff0d in /usr/bin/clickhouse
  2. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, String const&, bool, unsigned long, unsigned long) @ 0x153f4fdf in /usr/bin/clickhouse
  3. ? @ 0x13efe7a9 in /usr/bin/clickhouse
  4. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum) @ 0x13efe16d in /usr/bin/clickhouse
  5. DB::TCPHandler::runImpl() @ 0x14cd4991 in /usr/bin/clickhouse
  6. DB::TCPHandler::run() @ 0x14cea499 in /usr/bin/clickhouse
  7. Poco::Net::TCPServerConnection::start() @ 0x17c5cab4 in /usr/bin/clickhouse
  8. Poco::Net::TCPServerDispatcher::run() @ 0x17c5dcdb in /usr/bin/clickhouse
  9. Poco::PooledThread::run() @ 0x17ddb087 in /usr/bin/clickhouse
  10. Poco::ThreadImpl::runnableEntry(void*) @ 0x17dd8abd in /usr/bin/clickhouse
  11. ? @ 0x7f96ae1edb43 in ?
  12. ? @ 0x7f96ae27fa00 in ?
  compiled Code at target/run/standalone/models/example/source_data.sql

Configuration

Environment

• dbt version: 1.4.6
• dbt-clickhouse version: 1.4.1
• clickhouse-driver version (if using native): 0.2.6
• clickhouse-connect version (if using http): 0.5.25
• Python version: 3.10.7
• Operating system: macOS-13.3.1-x86_64-i386-64bit

ClickHouse server

• ClickHouse Server version: 23.4.2 revision 54462
• ClickHouse Server non-default settings, if any:
• CREATE TABLE statements for tables involved:
• Sample data for these tables, use clickhouse-obfuscator if necessary

[simpl1g]

I guess it should be fixed in next release, pull request already created
#156

[genzgd]

That PR has been merged and released in v1.4.2