Issue with clickhouse-client and cte in a file

[Skyhikeeper]

When trying to execute a simple query containing a cte I get the following issue

Query
ClickHouse client version 18.16.1.
Connecting to database jisDataProdWarehouse at loomix-clickhouse-headless.click:9000 as user default.
Connected to ClickHouse server version 25.3.2 revision 54476.

loomix-clickhouse-shard0-0.loomix-clickhouse-headless.click.svc.cluster.local :) WITH start_prev AS (SELECT toStartOfMonth(now() - INTERVAL 1 MONTH, 'Africa/Johannesburg') AS dt) select * from start_prev

Syntax error: failed at position 20:

WITH start_prev AS (SELECT toStartOfMonth(now() - INTERVAL 1 MONTH, 'Africa/Johannesburg') AS dt) select * from start_prev

Expected identifier

This query works 100% in my query tool (Dbeaver) but gives the following error on clickhouse-client

All my other "simple queries" work 100%

Advice will be appreciated

[charles-benedito]

hey, the clue is in the first two lines of ur output: client 18.16.1 talking to server 25.3.2. the WITH name AS (subquery) cte form only landed in 20.10 (pr #14771, listed in the changelog at

ClickHouse/docs/changelogs/archive/v20.10.1.4881-prestable.md

Line 28 in ecac83a

* Now we support `WITH <identifier> AS (subquery) ... ` to introduce named subqueries in the query context. This closes [#2416](https://github.com/ClickHouse/ClickHouse/issues/2416). This closes [#4967](https://github.com/ClickHouse/ClickHouse/issues/4967). [#14771](https://github.com/ClickHouse/ClickHouse/pull/14771) ([Amos Bird](https://github.com/amosbird)).

). before that WITH only took WITH expr AS name, which is why the parser stops right after AS ( and says expected identifier.

afaik clickhouse-client parses the query locally before it ever sends it, so a syntax error like this comes from the old client parser, not the server. dbeaver just ships the text over jdbc and the 25.3 server is fine with it. install a clickhouse-client that matches the server (any recent build) and the query should run. lmk if that works

[ChangkeunJ]

That error comes from the client, not the server. clickhouse-client parses every query locally before sending it, and 18.16.1 is the 2018 build Ubuntu's own repo still ships. Its grammar only knows WITH expr AS alias, so after start_prev AS it wants an identifier and trips on the parenthesis; a server-side error would start with Code: 62. DBeaver hands the text to your 25.3 server untouched, so it works there. Install the client from ClickHouse's own repo to match the server.

[teksufiyanuddin-stack]

Your issue is caused by a massive version mismatch between your client and server:

Client: 18.16.1 (~2018)
Server: 25.3.2 (~2025)

The syntax error is coming from clickhouse-client's local parser, not your ClickHouse server. The client parser rejects the query before it is ever sent across the wire.

Updating your clickhouse-client to a modern version (matching 25.x) will resolve this immediately.

Deep Dive: Why is this happening?

1. Client-Side AST Parsing

Unlike typical database CLI clients that simply push raw SQL strings over TCP or HTTP, clickhouse-client parses the query AST locally before sending it over the native protocol.

Notice the error format:
Syntax error: failed at position 20: ... Expected identifier

If the error had come from the server, it would have included a ClickHouse exception code, like:
Code: 62. DB::Exception: Syntax error: ...

The lack of an error code confirms the local CLI binary rejected the query before dispatch.

2. CTE Grammar Evolution

In ClickHouse 18.x, WITH only supported scalar expression aliases with the syntax:
WITH AS

Support for subquery CTEs (WITH AS (subquery)) was only implemented in ClickHouse 20.10 (PR #14771
).

When your 18.16.1 client reads WITH start_prev AS (SELECT ...), its parser expects an expression or identifier after AS according to the 2018 grammar. Encountering the opening parenthesis ( throws Expected identifier.

3. Why DBeaver works

DBeaver communicates via the JDBC / HTTP interface. It sends the raw query string straight to port 8123 (or HTTP endpoint), completely bypassing clickhouse-client. Because your server is running 25.3.2, the server's parser understands modern CTEs and executes the query without issue.

How to Fix It
Step 1: Replace the ancient client

You likely ended up with 18.16.1 by running apt install clickhouse-client from the default Ubuntu/Debian repositories, which still ship archaic builds.

Install the official client using the modern ClickHouse repository or single static binary:

Option A: Standalone static binary (quickest, zero dependencies)

curl https://clickhouse.com/ | sh
./clickhouse client --host loomix-clickhouse-headless.click --port 9000 ...

Option B: Official Debian/Ubuntu repository

sudo apt-get install -y apt-transport-https ca-certificates curl gnupg
curl -fsSL 'https://packages.clickhouse.com/packages/clickhouse.gpg' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-client