$columns expression triggers Clickhouse syntax error in clickhouse-grafana 2.0.2

[pavel-odintsov]

Hello!

We've got some issues with columns expression on v7.1.1 (3039f9c3bd) and latest stable plugin version (2.0.2).

We have following query:

$columns(
    dstAsn c,
    sum(length) length)
FROM $table
WHERE dstAsn in (
    SELECT dstAsn
    FROM
(
        SELECT
            dstAsn,
            sum(length) as length
        FROM $table
        GROUP BY dstAsn
        ORDER BY length DESC
        LIMIT 20
))

It worked fine for years but we've noticed issues after upgrade to latest Grafana and plugin.

Clickhouse does not like generated code with following error:

status:400
statusText:"Bad Request"
data:"Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 248: ) WHERE packetDate >= toDate(1596633305) AND packetDateTime >= toDateTime(1596633305) GROUP BY t, c ORDER BY t, c) GROUP BY t ORDER BY t FORMAT JSON . Expected one of: SAMPLE, LIMIT, WHERE, INNER, AS, HAVING, GROUP BY, INTO OUTFILE, identifier, OFFSET, LEFT ARRAY JOIN, PREWHERE, JOIN, alias, ORDER BY, array join, SETTINGS, UNION ALL, ARRAY JOIN, FORMAT, WITH, FINAL, table, table function, subquery or list of joined tables (version 20.5.4.40 (official build)) "
message:"Query error: 400 Bad Request"

We can see following generated SQL:

SELECT t, groupArray((c, length)) as groupArr FROM ( SELECT (intDiv(toUInt32(packetDateTime), 21600) * 21600) * 1000 as t, dstAsn c, 
    sum(length) length FROM fastnetmon.traffic
WHERE packetDate >= toDate(1491839204) AND packetDateTime >= toDateTime(1491839204) AND  dstAsn in (
    SELECT dstAsn
    FROM
(
        SELECT
            dstAsn,
            sum(length) as length
        FROM fastnetmon.traffic
        GROUP BY dstAsn
        ORDER BY length DESC
        LIMIT 20
)) GROUP BY t, c  ORDER BY t, c) GROUP BY t ORDER BY t 

You can find original dashboard here https://grafana.com/dashboards/2104

I do not see any issues with generated SQL and it works fine when I do query using generated code via clickhouse-client.

I've tried updating to current master code manually but it does not help.

I've tried downgrading and it solved problem:
sudo grafana-cli plugins install vertamedia-clickhouse-datasource 2.0.1

Can I ask for help with this problem?

Thank you!

[Slach]

@pavel-odintsov thanks you for reporting
i try to resolve issue ASAP

[Slach]

@pavel-odintsov could you git clone the latest master branch plugin version and check your query?

[pavel-odintsov]

Thank you so much! Will do!