Table and Column Comments set up misbehaviour

[GavrG]

Describe the bug

Testing v1.7.7 Community PRs
exacly: allows to add a comment in table's or view's metadata by @dstsimokha in #228
We've found, that generated ALTER TABLE SQL "ignores" ON CLUSTER clause and all changes done only on connection node.
Clickhouse and DBT does not return any errors.

Steps to reproduce

1. Create a model: models\test_comment_model.sql

{{
  config
      (
        materialized = 'table',
        schema = 'dbt__core',
        order_by=('some_id'),
        engine='ReplicatedMergeTree',
        persist_docs={"relation": true, "columns": true}
      )
}}

Select
    1 AS some_id,
    '----' AS some_column_name,
    '++++' AS some_other_column_name

2. Create a description: models\models.yml

version: 2
models:
  - name: test_comment_model
    description: | 
      test_comment_model description
    columns:
      - name: some_id
        description: some_id description
      - name: some_column_name
        description: some_column_name description
      - name: some_other_column_name
        description: some_other_column_name description

3. Build model dbt build --select test_comment_model.sql
4. Checking SQL in dbt_logs

  alter table `dbt__core`.`test_comment_model` 
    ON CLUSTER "clust"  
  modify comment $dbt_comment_literal_block$test_comment_model description$dbt_comment_literal_block$, 
  comment column some_id  $dbt_comment_literal_block$some_id description$dbt_comment_literal_block$, 
  comment column some_column_name  $dbt_comment_literal_block$some_column_name description$dbt_comment_literal_block$, 
  comment column some_other_column_name $dbt_comment_literal_block$some_other_column_name description$dbt_comment_literal_block$

5. Check it up in Clickhouse
   5.1. columns comments

SELECT hostName() as hostName,`database`, name, `comment`, *
FROM clusterAllReplicas('{cluster}', system.columns)
where database = 'dbt__core'
and `table` = 'test_comment_model'
order by database,  name, hostName

hostName |database |name |comment
clickhouse-01|dbt__core|some_column_name |some_column_name description
clickhouse-02|dbt__core|some_column_name |
clickhouse-01|dbt__core|some_id |some_id description
clickhouse-02|dbt__core|some_id |
clickhouse-01|dbt__core|some_other_column_name |some_other_column_name description
clickhouse-02|dbt__core|some_other_column_name |

5.2. tables comments

SELECT hostName() as hostName,`database`, name, `comment`, *
FROM clusterAllReplicas('{cluster}', system.tables)
where database = 'dbt__core'
and name = 'test_comment_model'
order by database,  name, hostName

hostName |database |name |comment
-----------------+---------+------------------+-------------------------------+
clickhouse-01|dbt__core|test_comment_model |test_comment_model description
clickhouse-02|dbt__core|test_comment_model |

Expected behaviour

Comments should be on all nodes

Fix proposal

Split single command to two

• table comment

  alter table `dbt__core`.`test_comment_model` 
    ON CLUSTER "clust"  modify comment 
  $dbt_comment_literal_block$test_comment_model description$dbt_comment_literal_block$

• columns comments

  alter table `dbt__core`.`test_comment_model` 
    ON CLUSTER "clust"
  comment column some_id $dbt_comment_literal_block$some_id description$dbt_comment_literal_block$,  
  comment column some_column_name $dbt_comment_literal_block$some_column_name description$dbt_comment_literal_block$,  
  comment column some_other_column_name $dbt_comment_literal_block$some_other_column_namedescription$dbt_comment_literal_block$

that makes clickhouse behave as expected

dbt and/or ClickHouse server logs

Configuration

Environment

• dbt version:1.7.16
• dbt-clickhouse version:1.7.7
• Python version: 3.9.13
• Operating system: Windows-10-10.0.22621-SP0

ClickHouse server

• ClickHouse Server version: ClickHouse 24.2.1.2248
• ClickHouse Server non-default settings, if any:
  ** <allow_experimental_live_view>1</allow_experimental_live_view>
  ** <allow_experimental_nlp_functions>1</allow_experimental_nlp_functions>
  ** <distributed_ddl_task_timeout>180</distributed_ddl_task_timeout>
  ** <allow_nondeterministic_mutations>1</allow_nondeterministic_mutations>

[simpl1g]

The query looks correct, I believe it should be a bug created in ClickHouse itself, not in dbt adapter

[GavrG]

The query looks correct, I believe it should be a bug created in ClickHouse itself, not in dbt adapter

Agree, but two points:

• i've not found such a syntax in Clickhouse SQL documentation
• it's much easier to make changes in fresh dbt functionality, rather than in Clickhouse parser

[emirkmo]

Have you found or reported the bug to ClickHouse? It is the very active, good chance it is fixed. If not, please let me know as I want to report it.

[genzgd]

ClickHouse/ClickHouse#55720, although I would have though the fix would be in 24.2. Is this is ClickHouse Cloud instance?

[emirkmo]

Interesting. We are running an ever so slightly older version than when the bug appears. We see still see the issue that only the communicator node gets the comments and it is missing from all others. So no proper DDL. Hmm