Handle empty variables

Grafana MySQL
,
1

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.

2 Likes
2

You can write CASE statement in your query .

3

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.

4

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

5

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}')

6

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.

7

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)
8

Howdy,

Issue:
When no options are selected from a variable list or a variable text box the $variable returns a blank statement as opposed to Null. This results in the user not being able to use try-catch blocks, if then, or case statements.

Workaround:
create a new variable using the original variable to search your database. This will generate a None value type if the db query returns none. You can then use the new variable in your query along with standard sql case, if-then, etc statements

Example variable query:
select * from TABLE if COLUMN in $variable

If your $variable is empty the result of the query will be None

9

the easiest way to do this would be to use a union and pull in a default value.

select data from table where condition of ${variable}

union select ‘no_data_found’ as ‘no_data’ ;

or something like that.

That way even if your variable query did not return and results at least the ‘no_data_found’ option would be available, and it could point to an empty data set or something to keep the dasbhoard working.