TRUNCATE TABLE of a distributed table has no effect

[mtoma]

(you don't have to strictly follow this form)

Describe the unexpected behaviour
The TRUNCATE TABLE of a distributed table returns no error but has no effect

How to reproduce

• Which ClickHouse server version to use
  23.1.3.5

• Which interface to use, if matter
  clickhouse-client

• Non-default settings, if any
  A cluster with two shards and 3 replicas per shard:
  select shard_num,replica_num, host_name from system.clusters where cluster='default' order by host_name;

┌─shard_num─┬─replica_num─┬─host_name────────────────────────────────────────────────────────────────────────┐
│         1 │           1 │ clickhouse-stage-shard0-0.clickhouse-stage-headless.clickhouse.svc.cluster.local │
│         1 │           2 │ clickhouse-stage-shard0-1.clickhouse-stage-headless.clickhouse.svc.cluster.local │
│         1 │           3 │ clickhouse-stage-shard0-2.clickhouse-stage-headless.clickhouse.svc.cluster.local │
│         2 │           1 │ clickhouse-stage-shard1-0.clickhouse-stage-headless.clickhouse.svc.cluster.local │
│         2 │           2 │ clickhouse-stage-shard1-1.clickhouse-stage-headless.clickhouse.svc.cluster.local │
│         2 │           3 │ clickhouse-stage-shard1-2.clickhouse-stage-headless.clickhouse.svc.cluster.local │
└───────────┴─────────────┴──────────────────────────────────────────────────────────────────────────────────┘

• CREATE TABLE statements for all tables involved

CREATE TABLE raw_stage_v2.nrj_monthdataelecbills (`_id` String, `bill_amount_ttc` Nullable(Float64), `bill_id` String, ...OTHER_COLUMNS...) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}') ORDER BY (_id, bill_id) SETTINGS index_granularity = 8192

CREATE TABLE raw_stage_v2.distributed_nrj_monthdataelecbills (`_id` String, `bill_amount_ttc` Nullable(Float64), `bill_id` String, ...OTHER_COLUMNS...) ENGINE = Distributed('default', 'raw_stage_v2', 'nrj_monthdataelecbills', murmurHash3_64(_id, bill_id))

• Queries to run that lead to unexpected result

select count(*) from raw_stage_v2.distributed_nrj_monthdataelecbills ;

1290619

TRUNCATE TABLE raw_stage_v2.distributed_nrj_monthdataelecbills

Query id: d623b90c-09aa-4e69-904a-1f98843ec04a

Ok.

0 rows in set. Elapsed: 0.057 sec.

select count(*) from raw_stage_v2.distributed_nrj_monthdataelecbills ;

1290619

Expected behavior
Truncate all underlying tables of the distributed table and return 0

Error message and/or stacktrace
No errors thrown

Additional context
our internal_replication is set to it's default "false" value, not sure if that matters but in any case a TRUNCATE TABLE with no effect at all is very strange.

[cangyin]

Doing TRUNCATE TABLE against a distributed table, according to code comment, only drops local temporary data (data waiting to be sent to other nodes):

ClickHouse/src/Storages/StorageDistributed.h

Lines 125 to 126 in f1eba08

	/// Removes temporary data in local filesystem.
	void truncate(const ASTPtr &, const StorageMetadataPtr &, ContextPtr, TableExclusiveLockHolder &) override;

To truncate data of local tables, do:

TRUNCATE TABLE  db.local_table_name ON CLUSTER cluster_name

[mtoma]

Maybe this should be mentioned in the documentation of the TRUNCATE TABLE feature for distributed tables?

[CheSema]

Just added a test that fixes current behaviour.
#54802

The documentation says nothing about how DDL affect pointed remote table. It just because it doesn't affect.