Template variable expansion default type changed in 2.0.2?

[banburybill]

I have multi-value template variables. With Grafana 7.1.0 and plugin 2.0.1, plain $var expands as a sqlstring, so as 'one', 'two', 'three'. With 2.0.2 this becomes ['one', 'two', 'three']. I'm using this in SELECT field WHERE field IN ($var), so ClickHouse becomes unhappy.

With 2.0.2, I can explicitly request sqlstring and replace $var with ${var:sqlstring} to restore previous behaviour. Unfortunately I need to work with Grafana 6.7.4 as well, and using 6.7.4 with 2.0.2 ${var:sqlstring} produces {one,two,three}.

(I just noticed 2.0.3 appeared while I was composing this - I haven't tested on that yet).

[Slach]

@banburybill
which version of ClickHouse Server do you use?
could you share the plugin generated SQL query (query inspector -> raw query) ?

before change
I tried

 SELECT 'one' IN (['one','two','three']);

and it was successful

┌─in('one', ['one', 'two', 'three'])─┐
│                                  1 │
└────────────────────────────────────┘

if you need multiple variable values as field names
so just try to use quick workaround ${var:csv}

[banburybill]

Sorry, I should have included that. Yes, it's a ClickHouse version issue. We're on a carefully selected 19.16 version, 19.16.4.12.

FWIW, here's logs of the actual queries. With 2.0.1:
executeQuery: (from xxx) SELECT region_name FROM wombat.node_text WHERE NOT bitTest(flags, 0) AND server_name IN ('IMRS','Sinodun') GROUP BY region_name FORMAT JSON

With 2.0.2:
executeQuery: Code: 53, e.displayText() = DB::Exception: Type mismatch in IN or VALUES section. Expected: String. Got: Array (version 19.16.4.12 (official build)) (from xxx) (in query: SELECT region_name FROM wombat.node_text WHERE NOT bitTest(flags, 0) AND server_name IN (['IMRS','Sinodun']) GROUP BY region_name FORMAT JSON

Trying your example on the latest 20.3LTS works. With our 19.16:

`:) select 'one' in (['one', 'two', 'three']);

SELECT 'one' IN (['one', 'two', 'three'])

Received exception from server (version 19.16.4):
Code: 53. DB::Exception: Received from localhost:9000. DB::Exception: Type mismatch in IN or VALUES section. Expected: String. Got: Array.`

I suspect 19.14LTS will do the same.

[banburybill]

Altinity's latest recommended stable ClickHouse is 20.3.12.112, a recommendation made less than a month ago. Previous Altinity recommended stable versions were both 19.16 releases. We'll think about updating, but there is a case for retaining ClickHouse v19 support.

[Slach]

oops ;(
look like we need to apply fix which continues to solve #169, but stay works for old ClickHouse versions

@banburybill thanks for open the issue

[Slach]

Hi @banburybill, could you check the latest master branch?

also please subscribe to grafana/grafana-plugin-repository#690 for notifying when this fix will available https://grafana.com/grafana/plugins/vertamedia-clickhouse-datasource

[banburybill]

Yes, latest master fixes my problem. Thanks!