Altinity / clickhouse-grafana

Altinity Grafana datasource plugin for ClickHouse
MIT License
704 stars 119 forks source link

$columns + subquery stop works after 3.1.1 #565

Closed Slach closed 1 month ago

Slach commented 1 month ago

$columns(concat('LID: ', toString(lid), ' | ', switch, ' ', mod, ' port ', toString(port), ' <=> ', r_switch, ' ', r_mod, ' port ', toString(r_port)) AS desc, median(lost_bw_pct) AS ratio) FROM $table WHERE $timeFilter AND dc = '$dc' AND port_type = 'spine' AND (lid, port) in (SELECT DISTINCT lid, port FROM $table WHERE $timeFilter AND host LIKE '%$host_type%' AND dc = '$dc' AND port_type = 'spine' GROUP BY lid,port ORDER BY max(lost_bw_pct) DESC LIMIT $top_count)

works only if added GROUP BY ALL

Slach commented 1 month ago

Reproduce only case when have $top_count which not exists in template variables and replaced $to as timestamp

need error message and steps to reproduce

Slach commented 1 month ago

more information for reproduce

source query

$columns(concat('LID: ', toString(lid), ' | ', switch, ' ', mod, ' port ', toString(port), ' = ', r_switch, ' ', r_mod, ' port ', toString(r_port)) AS desc, median(lost_bw_pct) AS ratio) FROM $table WHERE $timeFilter AND dc = 'fpip3' AND port_type = 'spine' AND (lid, port) in (SELECT DISTINCT lid, port FROM $table WHERE $timeFilter AND dc = 'fpip3' AND port_type = 'spine' GROUP BY lid,port ORDER BY max(lost_bw_pct) DESC LIMIT 10)

generated SQL

SELECT
    t,
    groupArray((desc, ratio)) AS groupArr
FROM
(
    SELECT
        (intDiv(toUInt32(_time), 1) * 1) * 1000 AS t,
        concat('LID: ', toString(lid), ' | ', switch, ' ', mod, ' port ', toString(port), ' = ', r_switch, ' ', r_mod, ' port ', toString(r_port)) AS desc,
        median(lost_bw_pct) AS ratio
    FROM default.ibstats_non_negative_rate
    WHERE (_time >= toDateTime(1718009046)) AND (_time <= toDateTime(1718009946)) AND (_time >= toDateTime(1718009046)) AND (_time <= toDateTime(1718009946)) AND (dc = 'fpip3') AND (port_type = 'spine') AND ((lid, port) IN (
        SELECT DISTINCT
            lid,
            port
        FROM default.ibstats_non_negative_rate
        WHERE (_time >= toDateTime(1718009046)) AND (_time <= toDateTime(1718009946)) AND (_time >= toDateTime(1718009046)) AND (_time <= toDateTime(1718009946)) AND (dc = 'fpip3') AND (port_type = 'spine')
        GROUP BY
            lid,
            port
        ORDER BY max(lost_bw_pct) DESC
        LIMIT 10
    ))
^^^^^^^ GROUP BY t, desc ^^^^^ missing here
)
GROUP BY t
ORDER BY t ASC