can't get Clickhouse tables with: code: 47, message: Missing columns: 'data_path'

[Hackintosh-Unix]

version of clickhouse 19.15.3.6.

When i'm trying to create backup, got error:

can't get Clickhouse tables with: code: 47, message: Missing columns: 'data_path' while processing query: 'SELECT database, name, is_temporary, data_path, metadata_path FROM system.tables WHERE (data_path != '') AND (is_
temporary = 0) AND (engine LIKE '%MergeTree')', required columns: 'data_path' 'is_temporary' 'engine' 'database' 'name' 'metadata_path', source columns: 'primary_key' 'storage_policy' 'sorting_key' 'data_paths' 'partition_key' 'engine_full'
'is_temporary' 'database' 'sampling_key' 'create_table_query' 'engine' 'dependencies_table' 'name' 'metadata_path' 'metadata_modification_time' 'dependencies_database'

[AlexAkulov]

Thank you!
Please try with v0.4.2.
I've fixed this issue just now.

[Hackintosh-Unix]

It works! Thank you!

[nahwinrajan]

So what is the issue ?

I am currently encountering this whilst running clickhouse-client from the official docker image.
I've created some comparable Create Table instead my actual, but it have the some structure.

I am trying to make table where it is ordered by, fields in the nested field.:

CREATE TABLE SOME_RAW_TABLE (
  team Nested (
    teamID UInt32,
    employeeID UInt32,
    employeeName String
    ),
    task Nested(
     taskID String,
     employeeID UInt32,
     details Nested (
       taskDetailID UInt32,
       description String
     )
   )
) ENGINE = MergeTree()
ORDER BY ( team.teamID, team.employeeID, task.details.taskDetailID, task.taskID);

Output:

CREATE TABLE SOME_RAW_TABLE
(
    `team` Nested(teamID UInt32, employeeID UInt32, employeeName String),
    `task` Nested(taskID String, employeeID UInt32, details Nested(taskDetailID UInt32, description String))
)
ENGINE = MergeTree
ORDER BY (team.teamID, team.employeeID, task.details.taskDetailID, task.taskID)

Query id: 43d6b811-47a3-4a32-8783-e88bb099a88f


0 rows in set. Elapsed: 0.002 sec.

Received exception from server (version 23.6.2):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'task.details.taskDetailID' while processing query: 'team.teamID, team.employeeID, task.details.taskDetailID, task.taskID', required columns: 'team.teamID' 'task.details.taskDetailID' 'team.employeeID' 'task.taskID' 'team.teamID' 'task.details.taskDetailID' 'team.employeeID' 'task.taskID'. (UNKNOWN_IDENTIFIER)

[nahwinrajan]

Oh, this is weird.

If I change task.details.taskDetailID to task.details.1 it works!
If I exclude task.details.taskDetailID from Order By (Primary Key Index) it will also work.

[Slach]

@nahwinrajan
your issue is not related to clickhouse-backup
look to https://clickhouse.com/docs/en/sql-reference/data-types/nested-data-structures/nested
and https://clickhouse.com/docs/en/operations/settings/settings#flatten-nested
https://fiddle.clickhouse.com/55cd5988-bdc0-4610-9662-d8f51fed9970

Nested is "array of tuples"
looks like this you can't use named tuple element in primary key and it looks like clickhouse-server bug

[nahwinrajan]

So I need to set the SET flatten_nested to 0 first ?
that is global setting right ?

additional information: with the previous way even though it was successfully creted the table, each field of the nested fields become an array on its own. And am not successful to do insertion into it (even after making values of each field is and array and array of array).

Now back to start with SET flatten_nested = 0; as it return the same error as this issue mentioned.

Edit:

• The insert work by doing .IndexOfTheFileds and it start from 1 instead of 0 (not zero based index)
• Am aware of this Issue-43634 but I am trying to do insert with SQL command
• Interesting Resource: Clickhouse Online fiddle