Expression math does not work with variable

Grafana MySQL
,
1
  • 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

2

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

image
image1797×1000 210 KB

2 Likes
3

Wow. it works. thank you.

4

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

5

Also getting this problem with a InfluxDB data source. Normal queries work fine, but expressions make everything disappear. Even a simple expression like $A with no math. Error log shows “expression request error”.

1 Like
6

I have same problem…

7

I was create new expression query with same expression as is not working query. Then i was delete old no work query and the new rename to old query name. Q is working again correctly.

8

My solution was to define the data source with http:// in the name. I had it as just ‘localhost:8086’ but apparently Expressions require the full URL, so ‘http://localhost:8086’ or even just ‘http://localhost’

1 Like
9

Same problem.

In my case a postgresql datasource, with $A*$B. All three graphs vanish.

10

For me, math expression dont work, too.

image
image1573×757 76.1 KB

What do I wrong? BR; Simon

11

Is it possible to perform the transform on two different data sources?

For example:
Quary A is sql
Query B is API

When we perform transform “add field from calculation” with mode “binary operation” on the queries above it doesn’t work.

If we perform the same transform on queries with the same data source it works.

Why it only works when I use the same data source but doesn’t work on two differen data sources?