Multi-valued query variables when using repeat panel functionality

[hsuominen]

Great work on this datasource!

I am having some issues with the "Repeat panels" functionality (https://grafana.com/docs/grafana/latest/variables/repeat-panels-or-rows/). I think this might be related to the implementation of query variables as discussed briefly in #14.

Concretely I have a multi-valued query variable: var_name = {var1, var2, var3, .......}, and a panel query SELECT foo as value FROM actions WHERE var = '${var_name}' ORDER BY date

When using the inspector, each panel shows queries that look like: SELECT ... WHERE foo = '{var1, var2, var3, .......}' while I was expecting each panel query to be single valued.

[fr-ser]

I could actually reproduce the issue and comparing to the Postgres data source this does indeed not happen.

Sadly the documentation of Grafana in this regard seems to be pretty much non-existing. I might have to snoop around existing plugins (maybe the internal ones) to reverse-engineer how it is done :cry:

This might take a while unless you happen to stumble upon the documentation of how to implement it...

[fr-ser]

I just tried to reproduce the issue again, but this time it would as expected (after I updated the query variable once and then the panels also updated).

My query for the panel is

with data(time, temperature, city) AS (VALUES
(724125600, 10, 'London'), (724161600, 11, 'London'),
(724161600, 20, 'New York'), (724197600, 21, 'New York'),
(724125600, 25, 'Washington'), (724197600, 26, 'Washington')
) SELECT * from data
WHERE city in (${cities:singlequote})
order by time

And my query for the variable is

SELECT * FROM (VALUES ('London'), ('New York'), ('Washington'))

Whit this I got the below result

Could you maybe elaborate if you expected something else or if the issue is indeed solved?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[sharanchandran]

This issue still exists. Let me try to explain with an Example and comparing the behavior with MySQL datasource when "Repeat Option" is enabled:

Variable in Dashboard: $test_id : A, B, C, D, E, F [include all and multi value selection permitted in variable settings] Master Panel Query: SELECT time, value from TESTRESULT where testname IN (${test_id:singlequote}) "Repeat Option" for $test_id enabled

In Dashboard Suppose I select "A + B + C + D" [selected multiple test ids]

SQLite as Data Source Behavior: It will create 4 panels as expected (this is good). But if you inspect Each panel query it is translated to the same query across panels: SELECT time, value from TESTRESULT where testname IN ('A','B','C','D') So in effect, all panels are showing ALL the data instead of repeated individual query results based on variable.

MySQL as Data Source Behavior: It will create 4 panels as expected. But if you inspect Each panel query it is translated to individual query based on variable: Panel A: SELECT time, value from TESTRESULT where testname IN ('A') Panel B: SELECT time, value from TESTRESULT where testname IN ('B') Panel C: SELECT time, value from TESTRESULT where testname IN ('C') Panel D: SELECT time, value from TESTRESULT where testname IN ('D') So here, I got the expected behavior when the panels are repeated for each test id.

In this example, in the SQL query I am using "IN" as the selection clause just as a sample to make it simpler. The same behavior is shown if I use the operator "=" or "LIKE" instead of IN. And if that is the case, SQLLite will not show any result as the query cannot match the condition (it gets translated to an invalid query mostly).

[fr-ser]

Could you give me a query (preferably with a CTE for the data) and a JSON export of your dashboard? I would like to see the issue and reproduce it myself.

Since I could not reproduce the issue here I would be very glad about an example for me to test around: https://github.com/fr-ser/grafana-sqlite-datasource/issues/22#issuecomment-813332142

[sharanchandran]

Made a couple of observations and partly able to work:

1. My SQLite plugin version was old (0.1.5). I installed using grafana-cli plugins install command (why it is installing the older version, I am not sure). So I installed the new version manually (unzipped). The older version didn't support Query in variable. So I had a custom Comma separate list of values in that case.
2. After SQLite plugin upgrade manually (to v2.0.0), the Query in variable started to work. And with this, the Panel repeat is working fine when I select multiple values for variable [this solved most of my problem]
3. Even after upgrading the plugin, if I use Custom comma separated values in variable (instead of a query), If I enable repeating, each panel is getting repeated Twice (bug ! or is it because of a little older Grafana (v7.1.4) ? )
4. In the variable If I enable "Include ALL" and then select ALL instead of multiple values, the "Repeat option" does not work. Inspect Query is translated to : SELECT time, value from TESTRESULT where testname IN ('$__all') instead of individual values. So this could be another bug (or is it because of a little older Grafana (v7.1.4) ? ) .

[fr-ser]

Can you please export an example dashboard as JSON and also provide the queries with CTEs or some sample database to recreate your scenarios?