Expression Math: failed to query data: type assertion to string failed

[WMP]

What happened: When i use Expression Math i have error: "failed to query data: type assertion to string failed" Logs:

Aug 07 22:52:27 ha grafana-server[2868947]: t=2021-08-07T22:52:27+0200 lvl=eror msg="expression request error" logger=context userId=1 orgId=1 uname=admin error="failed to query data: type assertion to string failed" remote_addr=192.168.123.102
Aug 07 22:52:27 ha grafana-server[2868947]: t=2021-08-07T22:52:27+0200 lvl=eror msg="Request Completed" logger=context userId=1 orgId=1 uname=admin method=POST path=/api/ds/query status=500 remote_addr=192.168.123.102 time_ms=6 size=38 referer="http://ha.lan:3000/d/cNygq-G7k/new-dashboard-copy?orgId=1&editPanel=2"

What you expected to happen: I want to get result form simple math

How to reproduce it (as minimally and precisely as possible): My influxDB data:

> SHOW FIELD KEYS FROM mqtt_consumer;
name: mqtt_consumer
fieldKey fieldType
-------- ---------
value    float
> select mean("value") FROM "mqtt_consumer" WHERE ("topic" = 'supla/devices/zamel-mew-01-3a23c/channels/0/state/total_reverse_active_energy_balanced') limit 3;
name: mqtt_consumer
time mean
---- ----
0    208.79858308646934
>

Anything else we need to know?:

Environment:

• Grafana version:8.1.0

• Data source type & version:influxdb 1.8.1-1

• OS Grafana is installed on: Ubuntu 20.04

• User OS & Browser: Opera 75 on Ubuntu 20.04

• Grafana plugins: agenty-flowcharting-panel @ 0.9.1 blackmirror1-singlestat-math-panel @ 1.1.8 fetzerch-sunandmoon-datasource @ 0.2.1 yesoreyeram-boomtable-panel @ 1.4.1

• Others:

[gabor]

hi @WMP , i am trying to reproduce the problem, but i am not able to do so yet. it works fine for me.

if you remove the expression, so that you only have the query, does it work correctly in that case?

if it works, could you tell us the response-data? (you can get it by clicking the [query inspector] button, click [refresh] inside, then [expand all], and [copy to clipboard], and then you have it in your clipboard. please make sure to remove any private/secret info from the data.)

[WMP]

Hello, thanks for reply. Yes, works very good without expression math. When i do literally what you told, button copy to cliboard copy me Collapse data, so i copy it by mouse:

Object
request:Object
url:"api/ds/query"
method:"POST"
data:Object
queries:Array[2]
0:Object
hide:false
query:"SELECT mean("value") FROM "mqtt_consumer" WHERE ("topic" = 'supla/devices/zamel-mew-01-3a23c/channels/0/state/total_reverse_active_energy_balanced')"
rawQuery:true
refId:"A"
datasource:"InfluxDB"
datasourceId:1
intervalMs:15000
maxDataPoints:1446
1:Object
datasource:"__expr__"
expression:"$A - 1"
hide:false
refId:"B"
type:"math"
datasourceId:-100
range:Object
from:k
_isAMomentObject:true
_isUTC:false
_pf:Object
empty:false
unusedTokens:Array[0]
unusedInput:Array[0]
overflow:-2
charsLeftOver:0
nullInput:false
invalidEra:null
invalidMonth:null
invalidFormat:false
userInvalidated:false
iso:false
parsedDateParts:Array[0]
era:null
meridiem:null
rfc2822:false
weekdayMismatch:false
_locale:j
_calendar:Object
sameDay:"[Today at] LT"
nextDay:"[Tomorrow at] LT"
nextWeek:"dddd [at] LT"
lastDay:"[Yesterday at] LT"
lastWeek:"[Last] dddd [at] LT"
sameElse:"L"
_longDateFormat:Object
LTS:"HH:mm:ss"
LT:"HH:mm"
L:"DD/MM/YYYY"
LL:"D MMMM YYYY"
LLL:"D MMMM YYYY HH:mm"
LLLL:"dddd, D MMMM YYYY HH:mm"
_invalidDate:"Invalid date"
ordinal:function(e){…}
_dayOfMonthOrdinalParse:RegExp
_relativeTime:Object
future:"in %s"
past:"%s ago"
s:"a few seconds"
ss:"%d seconds"
m:"a minute"
mm:"%d minutes"
h:"an hour"
hh:"%d hours"
d:"a day"
dd:"%d days"
w:"a week"
ww:"%d weeks"
M:"a month"
MM:"%d months"
y:"a year"
yy:"%d years"
_months:Array[12]
0:"January"
1:"February"
2:"March"
3:"April"
4:"May"
5:"June"
6:"July"
7:"August"
8:"September"
9:"October"
10:"November"
11:"December"
_monthsShort:Array[12]
0:"Jan"
1:"Feb"
2:"Mar"
3:"Apr"
4:"May"
5:"Jun"
6:"Jul"
7:"Aug"
8:"Sep"
9:"Oct"
10:"Nov"
11:"Dec"
_week:Object
dow:1
doy:4
_weekdays:Array[7]
0:"Sunday"
1:"Monday"
2:"Tuesday"
3:"Wednesday"
4:"Thursday"
5:"Friday"
6:"Saturday"
_weekdaysMin:Array[7]
0:"Su"
1:"Mo"
2:"Tu"
3:"We"
4:"Th"
5:"Fr"
6:"Sa"
_weekdaysShort:Array[7]
0:"Sun"
1:"Mon"
2:"Tue"
3:"Wed"
4:"Thu"
5:"Fri"
6:"Sat"
_meridiemParse:RegExp
_abbr:"en-gb"
_config:Object
calendar:Object
sameDay:"[Today at] LT"
nextDay:"[Tomorrow at] LT"
nextWeek:"dddd [at] LT"
lastDay:"[Yesterday at] LT"
lastWeek:"[Last] dddd [at] LT"
sameElse:"L"
longDateFormat:Object
LTS:"HH:mm:ss"
LT:"HH:mm"
L:"DD/MM/YYYY"
LL:"D MMMM YYYY"
LLL:"D MMMM YYYY HH:mm"
LLLL:"dddd, D MMMM YYYY HH:mm"
invalidDate:"Invalid date"
ordinal:function(e){…}
dayOfMonthOrdinalParse:RegExp
relativeTime:Object
future:"in %s"
past:"%s ago"
s:"a few seconds"
ss:"%d seconds"
m:"a minute"
mm:"%d minutes"
h:"an hour"
hh:"%d hours"
d:"a day"
dd:"%d days"
w:"a week"
ww:"%d weeks"
M:"a month"
MM:"%d months"
y:"a year"
yy:"%d years"
months:Array[12]
0:"January"
1:"February"
2:"March"
3:"April"
4:"May"
5:"June"
6:"July"
7:"August"
8:"September"
9:"October"
10:"November"
11:"December"
monthsShort:Array[12]
0:"Jan"
1:"Feb"
2:"Mar"
3:"Apr"
4:"May"
5:"Jun"
6:"Jul"
7:"Aug"
8:"Sep"
9:"Oct"
10:"Nov"
11:"Dec"
week:Object
dow:1
doy:4
weekdays:Array[7]
0:"Sunday"
1:"Monday"
2:"Tuesday"
3:"Wednesday"
4:"Thursday"
5:"Friday"
6:"Saturday"
weekdaysMin:Array[7]
0:"Su"
1:"Mo"
2:"Tu"
3:"We"
4:"Th"
5:"Fr"
6:"Sa"
weekdaysShort:Array[7]
0:"Sun"
1:"Mon"
2:"Tue"
3:"Wed"
4:"Thu"
5:"Fri"
6:"Sat"
meridiemParse:RegExp
abbr:"en-gb"
_dayOfMonthOrdinalParseLenient:RegExp
_d:Date
_isValid:true
_z:null
to:k
_isAMomentObject:true
_isUTC:false
_pf:Object
empty:false
unusedTokens:Array[0]
unusedInput:Array[0]
overflow:-2
charsLeftOver:0
nullInput:false
invalidEra:null
invalidMonth:null
invalidFormat:false
userInvalidated:false
iso:false
parsedDateParts:Array[0]
era:null
meridiem:null
rfc2822:false
weekdayMismatch:false
_locale:j
_calendar:Object
sameDay:"[Today at] LT"
nextDay:"[Tomorrow at] LT"
nextWeek:"dddd [at] LT"
lastDay:"[Yesterday at] LT"
lastWeek:"[Last] dddd [at] LT"
sameElse:"L"
_longDateFormat:Object
LTS:"HH:mm:ss"
LT:"HH:mm"
L:"DD/MM/YYYY"
LL:"D MMMM YYYY"
LLL:"D MMMM YYYY HH:mm"
LLLL:"dddd, D MMMM YYYY HH:mm"
_invalidDate:"Invalid date"
ordinal:function(e){…}
_dayOfMonthOrdinalParse:RegExp
_relativeTime:Object
future:"in %s"
past:"%s ago"
s:"a few seconds"
ss:"%d seconds"
m:"a minute"
mm:"%d minutes"
h:"an hour"
hh:"%d hours"
d:"a day"
dd:"%d days"
w:"a week"
ww:"%d weeks"
M:"a month"
MM:"%d months"
y:"a year"
yy:"%d years"
_months:Array[12]
0:"January"
1:"February"
2:"March"
3:"April"
4:"May"
5:"June"
6:"July"
7:"August"
8:"September"
9:"October"
10:"November"
11:"December"
_monthsShort:Array[12]
0:"Jan"
1:"Feb"
2:"Mar"
3:"Apr"
4:"May"
5:"Jun"
6:"Jul"
7:"Aug"
8:"Sep"
9:"Oct"
10:"Nov"
11:"Dec"
_week:Object
dow:1
doy:4
_weekdays:Array[7]
0:"Sunday"
1:"Monday"
2:"Tuesday"
3:"Wednesday"
4:"Thursday"
5:"Friday"
6:"Saturday"
_weekdaysMin:Array[7]
0:"Su"
1:"Mo"
2:"Tu"
3:"We"
4:"Th"
5:"Fr"
6:"Sa"
_weekdaysShort:Array[7]
0:"Sun"
1:"Mon"
2:"Tue"
3:"Wed"
4:"Thu"
5:"Fri"
6:"Sat"
_meridiemParse:RegExp
_abbr:"en-gb"
_config:Object
calendar:Object
sameDay:"[Today at] LT"
nextDay:"[Tomorrow at] LT"
nextWeek:"dddd [at] LT"
lastDay:"[Yesterday at] LT"
lastWeek:"[Last] dddd [at] LT"
sameElse:"L"
longDateFormat:Object
LTS:"HH:mm:ss"
LT:"HH:mm"
L:"DD/MM/YYYY"
LL:"D MMMM YYYY"
LLL:"D MMMM YYYY HH:mm"
LLLL:"dddd, D MMMM YYYY HH:mm"
invalidDate:"Invalid date"
ordinal:function(e){…}
dayOfMonthOrdinalParse:RegExp
relativeTime:Object
future:"in %s"
past:"%s ago"
s:"a few seconds"
ss:"%d seconds"
m:"a minute"
mm:"%d minutes"
h:"an hour"
hh:"%d hours"
d:"a day"
dd:"%d days"
w:"a week"
ww:"%d weeks"
M:"a month"
MM:"%d months"
y:"a year"
yy:"%d years"
months:Array[12]
0:"January"
1:"February"
2:"March"
3:"April"
4:"May"
5:"June"
6:"July"
7:"August"
8:"September"
9:"October"
10:"November"
11:"December"
monthsShort:Array[12]
0:"Jan"
1:"Feb"
2:"Mar"
3:"Apr"
4:"May"
5:"Jun"
6:"Jul"
7:"Aug"
8:"Sep"
9:"Oct"
10:"Nov"
11:"Dec"
week:Object
dow:1
doy:4
weekdays:Array[7]
0:"Sunday"
1:"Monday"
2:"Tuesday"
3:"Wednesday"
4:"Thursday"
5:"Friday"
6:"Saturday"
weekdaysMin:Array[7]
0:"Su"
1:"Mo"
2:"Tu"
3:"We"
4:"Th"
5:"Fr"
6:"Sa"
weekdaysShort:Array[7]
0:"Sun"
1:"Mon"
2:"Tue"
3:"Wed"
4:"Thu"
5:"Fri"
6:"Sat"
meridiemParse:RegExp
abbr:"en-gb"
_dayOfMonthOrdinalParseLenient:RegExp
_d:Date
_isValid:true
_z:null
raw:Object
from:"now-6h"
to:"now"
from:"1628564797289"
to:"1628586397294"
hideFromInspector:false
response:Object
message:"expression request error"

Very bad formatting...

If you want, i can make you influxDB dump and send private.

[WMP]

And this same dashboard without Math:

[gabor]

@WMP thanks for the info! one more thing: could you get me the query inspector data for the case without Math? it is enough to get the part starting at > response

[WMP]

{
  "request": {
    "method": "GET",
    "url": "api/datasources/proxy/1/query",
    "params": {
      "db": "telegraf",
      "q": "SELECT mean(\"value\") FROM \"mqtt_consumer\" WHERE (\"topic\" = 'supla/devices/zamel-mew-01-3a23c/channels/0/state/total_reverse_active_energy_balanced')",
      "epoch": "ms"
    },
    "data": null,
    "precision": "ms",
    "hideFromInspector": false
  },
  "response": {
    "results": [
      {
        "statement_id": 0,
        "series": [
          {
            "name": "mqtt_consumer",
            "columns": [
              "time",
              "mean"
            ],
            "values": [
              [
                0,
                234.97671644676873
              ]
            ]
          }
        ]
      }
    ],
    "executedQueryString": "SELECT mean(\"value\") FROM \"mqtt_consumer\" WHERE (\"topic\" = 'supla/devices/zamel-mew-01-3a23c/channels/0/state/total_reverse_active_energy_balanced')"
  }
}```

[WMP]

I have this same on InfluxDB 2.0.8

[gabor]

hi @WMP , i tried to look into this problem more, but still haven't been able to find the problem. i have a couple recommendations:

• could you try a newer grafana version? you mentioned you are using 8.1.0, could you try upgrading to the newest in the 8.1.x series? or, even better, to the newest version (8.2.x currently)? there were some fixes after 8.1.0 in parts that are touched by this issue
• when you configured the influxdb datasource, are you setting up anything special? http authentication? TLS? custom http headers? in other words:
  • in the first HTTP section:
    • you must have URL filled out, that is ok.
    • is "Access" on the default value ? (it is "Server")
    • are any other fields there used?
  • in the next "Auth" section, do you use anything?
  • in the next InfluxDB Details section, which fields do you have set?
• if the expression does not work for you, alerting should also fail.but maybe we get a better error message there. here is how to try it out:
  • remove the expression, only keep the query
  • switch the visualization to timeSeries.
  • now you should have a tab named 'Alert' next to Query and Transform
  • go to the Alert tab, click Create Alert
  • in the Conditions section there is an empty text-field, put there any number you want
  • go to the bottom of the screen, and press [Test rule]
  • you should get an output where it will probably say that it failed, but maybe there is some useful info there. could you paste the result here? (remove anything private please)
  • make sure to [delete] the alert at the end
• maybe enabling debug-logs could show more info:
  • see here how to enable it https://grafana.com/docs/grafana/latest/troubleshooting/#troubleshoot-with-logs
  • do the failing query+expression thing, and watch the debug logs, do you see anything interesting here? could you also send us all the lines that appear when you run the query. (remove anything private pleasea)

[grafanabot]

We've closed this issue since it needs more information and hasn't had any activity recently. We can re-open it after you you add more information. To avoid having your issue closed in the future, please read our CONTRIBUTING guidelines.

Happy graphing!