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 https://github.com/ClickHouse/dbt-clickhouse/pull/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|dbtcore|test_comment_model |test_comment_model description clickhouse-02|dbtcore|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: 1 1 180 1

[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]

https://github.com/ClickHouse/ClickHouse/issues/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