Handle empty variables

I have a MYSQL query that is referenced to a template variable. When the variable returns null, the mysql query fails since the IN clause contains an empty list. How do we handle such a scenario.

1 Like

You can write CASE statement in your query .

Actually you cannot use CASE - Grafana does direct substitution on template variables. If no values are returned from variables, it ll be blank. So CASE or COALESCE can’t be used in this situation.

You can resolve this issue using the following
WHERE id IN (${id:sqlstring}+'');

Also ran into this issue when using Loki, When I wanted no results returned when nothing was selected
|~ "${id:regex}|EMPTYDEFAULTVALUE"

It would be nice to add a filter to provide a default value

1 Like

The WHERE id IN (${id:sqlstring}+''); solution above started to fail me in certain situations.
A much better solution is WHERE FIND_IN_SET(id, '${id:csv}')

1 Like

I faced the same issue and this is how I resolved it:
Lets say your template variable is A
Lets say the condition when your template variable returns nil is Cond1

Use:
WHERE NOT (cond1 AND parameter in $A)

This way, when cond1 is not true and A is going to be empty, the bracket of AND does not work.

If suppose you have multiple template variables A,B,C and they DONT return empty during conditions CondA,CondB and CondC respectively,
Eg. during CondA, $A is definitely not empty but if CondA is not satisfied, A may be empty.

This will fail: WHERE parameter in ($A,$B,$C)
This will work:

WHERE
(condA AND parameter in $A) OR
(condB AND parameter in $B) OR
(condC AND parameter in $C)