clickhouse-client --queries-file broken for INSERT INTO statements on multiple lines

[alexvanolst]

When using clickhouse-client with the --queries-file parameter, valid INSERT INTO queries fail when there are newlines between values to insert

Fiddle link

https://fiddle.clickhouse.com/86e1674a-31b8-43fe-8588-4af5f07bf6aa

Does it reproduce on recent release?

Yes

How to reproduce

• Which ClickHouse server version to use: Latest (23.4.2.11)
• Which interface to use, if matters: clickhouse-client with --queries-fine (bug does not appear when using interactive queries)
• Non-default settings, if any: n/a
• CREATE TABLE statements for all tables involved:

CREATE TABLE users (uid Int16, name String, age Int16) ENGINE=Memory;

• Queries to run that lead to unexpected result

INSERT INTO users VALUES (1231, 'John', 33),
  (1232, 'John2', 33);

Expected behavior

Expect INSERT INTO to not fail when newlines are between values

Error message and/or stacktrace

Code: 62. DB::Exception: Syntax error: failed at position 2 ('1232'): 1232, 'John2', 33);
. Expected one of: SELECT query, possibly with UNION, list of union elements, SELECT query, subquery, possibly with UNION, SELECT subquery, SELECT query, WITH, FROM, SELECT, EXPLAIN. (SYNTAX_ERROR)

Additional context

We have some scripts setup to create tables and insert data into clickhouse table using clickhouse-client --queries-file. It is unmanageable to have all values on a single line and the scripts get broken by any SQL formatting tool.

This is also prevents us from automating some things as we have to exclude INSERT INTO statements and execute interactively instead (without --queries-file)

[evillique]

As a workaround, you can do inserts in batches:

INSERT INTO users VALUES (1231, 'John', 1), (1232, 'John2', 2), (1232, 'John3', 3);
INSERT INTO users VALUES (1231, 'John4', 4), (1232, 'John5', 5), (1232, 'John6', 6);

[alexey-milovidov]

Proposal: #58436