CTE Syntax Error

[mcgrawia]

Hi, I am trying to run a query using a subquery CTE and am getting a syntax error. Is this supposed to work?

with cte as (select * from numbers(10))
select * from cte;

Error:

Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 41 ('FORMAT') (line 2, col 1): FORMAT TabSeparatedWithNamesAndTypes;. Expected one of: SELECT, Comma, token (version 21.1.2.15 (official build))

I notice when I move it on to one line, it works as expected:

with cte as (select * from numbers(10)) select * from cte;

Result:

0
1
2
3
4
5
6
7
8
9

I am using version 21.1.2.15.

[den-crane]

What API/tool do you use?

clickhouse-client -m
 :) with cte as (select * from numbers(10))
:-] select * from cte;

WITH cte AS
    (
        SELECT *
        FROM numbers(10)
    )
SELECT *
FROM cte

Query id: 7b835ee2-4c42-4edf-bab2-4bd9949a127a

┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘

JDBC also work.

[den-crane]

Use JDBC version 0.3+

[mcgrawia]

Thanks for the fast response @den-crane. I am using DataGrip. I originally was using the bundled JDBC driver 0.2.4 but just upgraded to 0.3.1 and am still getting the same error. In the console it looks like it's only sending the first line of the query to CH, so it definitely seems like a driver issue:

[2021-06-02 16:17:41] Connected
default> with cte as (select * from numbers(10))
[2021-06-02 16:17:41] Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 40 (end of query): . Expected one of: SELECT, Comma, token (version 21.1.2.15 (official build))

[den-crane]

DataGrip has own SQL parser and this parser is 2 years old. Open a bug against DataGrip

[mcgrawia]

if anyone else stumbles on this issue, it looks like there are already 2 open tickets for DataGrip:
https://youtrack.jetbrains.com/issue/DBE-12548
https://youtrack.jetbrains.com/issue/DBE-13280

[mcgrawia]

it also looks like if you wrap your query in parenthesis, it works:

(with cte as (select * from numbers(10))
select * from cte);

0
1
2
3
4
5
6
7
8
9