Clickhouse: Inner Nested Fields cause and Error when trying to create a table

Created on 2 Oct 2020 · 2Comments · Source: ClickHouse/ClickHouse

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

Describe the bug
I have followed the following syntax to create a table that was located here https://github.com/ClickHouse/ClickHouse/issues/14196


CREATE TABLE t
(
    `x` Nested(    a Int8,     b Nested(    u String,     v Float32))
)
ENGINE = MergeTree()

And I get the following error in Console
Received exception from server (version 20.9.2): Code: 62. DB::Exception: Received from clickhouse-server:9000. DB::Exception: Syntax error (data type): failed at position 21 ('String'): String, v Float32))). Expected one of: LIKE, GLOBAL NOT IN, AS, IS, OR, QuestionMark, BETWEEN, NOT LIKE, AND, Comma, alias, IN, ILIKE, Dot, NOT ILIKE, NOT, Arrow, token, NOT IN, GLOBAL IN.
And here is my original sql where I have used JDBC to create table

CREATE TABLE AUDIT.MY_TEST1
(
    message_timestamp DateTime,
    correlation_id    String,
    some_nested Nested (
        MY_VALUE1 String,
        MY_VALUE2 Int32,
        MY_VALUE4 String,
        MY_TEST Nested (
           MY_VALUE1 String
          )
        ),
    last_value        String
)
    ENGINE = MergeTree()
        PARTITION BY toYYYYMMDD(message_timestamp)
        ORDER BY (message_timestamp)
        SETTINGS index_granularity = 8192

How to reproduce
Run the above query... Note I have found this syntax here I was expecting this to work https://github.com/ClickHouse/ClickHouse/issues/14196

Or Run any of the given samples in ClickHouse Console

Which ClickHouse server version to use version 20.9.2
Which interface to use, if matters clickhouse-client and clickhouse-jdbc
Non-default settings, if any
CREATE TABLE statements for all tables involved
Sample data for all these tables, use clickhouse-obfuscator if necessary
Queries to run that lead to unexpected result

Expected behavior
A clear and concise description of what you expected to happen.

Expect a table to be created

Error message and/or stacktrace
If applicable, add screenshots to help explain your problem.

For JDBC

[2020-10-02 00:32:24] Code: 62, e.displayText() = DB::Exception: Syntax error (data type): failed at position 29 ('String'): String))). Expected one of: LIKE, GLOBAL NOT IN, AS, IS, OR, QuestionMark, BETWEEN, NOT LIKE, AND, Comma, alias, IN, ILIKE, Dot, NOT ILIKE, NOT, Arrow, token, NOT IN, GLOBAL IN (version 20.9.2.20 (official build))

For clickhouse-client

Received exception from server (version 20.9.2):
Code: 62. DB::Exception: Received from clickhouse-server:9000. DB::Exception: Syntax error (data type): failed at position 21 ('String'): String, v Float32))). Expected one of: LIKE, GLOBAL NOT IN, AS, IS, OR, QuestionMark, BETWEEN, NOT LIKE, AND, Comma, alias, IN, ILIKE, Dot, NOT ILIKE, NOT, Arrow, token, NOT IN, GLOBAL IN.

0 rows in set. Elapsed: 0.003 sec.

Additional context
Add any other context about the problem here.
N/A

question question-answered

Source