Expression math does not work with variable

  • What Grafana version and what operating system are you using?

grafana 7.5.11
debian 9

  • What are you trying to achieve?

Use Expression math along with a query that uses $variable.

  • How are you trying to achieve it?

I created query “A” and query “B”, which use data from mysql. The sql queries use $variable.

Query “A”:

SELECT
  UNIX_TIMESTAMP(cast(mplayers.registration as date)) as time_sec,
  count(*) as value,
  'регистраций_новых_людей'
FROM players as mplayers
WHERE mplayers.registration BETWEEN cast($__timeFrom() as date) + interval 1 day AND cast($__timeTo() as date) + interval 1 day $filter
GROUP BY time_sec
ORDER BY time_sec ASC

Query “B”:

SELECT
  UNIX_TIMESTAMP(cast(mplayers.registration as date)) as time_sec,
  sum((`mplayers`.`last_online` > (`mplayers`.`registration` + interval 1 day))) as value,
  'зашел_через_24_часа_и_более'
FROM players as mplayers
WHERE mplayers.registration BETWEEN cast($__timeFrom() as date) + interval 1 day AND cast($__timeTo() as date) + interval 1 day $filter
GROUP BY time_sec
ORDER BY time_sec ASC

These sql queries use the grafana-variable $filter.
This variable is equal to the value “and multi = 0”.
Both queries work.

I also created an expression math “C” which equals “$A + $B”.

  • What happened?

Graph “C” is not displayed, and graphs “A” and “B” disappear.

  • What did you expect to happen?

I was expecting to see a C and A and B chart.

  • Can you copy/paste the configuration(s) that you are having problems with?
  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

In grafana 7.5.11 there are no errors in the GUI or in /var/log/grafana

But I tested grafana 8.2.5 and this problem is present there too. So, on version 8.2.5 in /var/log/grafana the error is displayed:

t=2021-11-21T03:50:50+0300 lvl=eror msg="expression request error" logger=context userId=1 orgId=1 uname=secret error="failed to execute query E: db query error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '$filter\r\nGROUP BY time_sec\r\nORDER BY time_sec ASC' at line 6" remote_addr=secret
t=2021-11-21T03:50:50+0300 lvl=eror msg="Request Completed" logger=context userId=1 orgId=1 uname=secret method=POST path=/api/ds/query status=500 remote_addr=secret time_ms=2 size=38 referer="http://secret:3000/d/ehJD-HZZk/registratsii?editPanel=25&orgId=1"
  • Did you follow any online instructions? If so, what is the URL?

No

Have you considered using a Transformation to add the results of the two queries, like this?

Wow. it works. thank you.

Great. Please mark the post above as Solution so people in the future may be able to find it.