Multi-line time series with subquery/CTE

[stas-sl]

Describe the bug
I'm not sure if this bug or just not implemented, but when I'm trying to use a bit more complicated queries that include subqueries or CTEs, the results fail to be recognised as multi time-series data.

Versions
Grafana: 9.1.5
Plugin: 2.0.1

Query
For example this query works:

select $__timeInterval(time) as time, device_id, avg(temp) as temp
from measurements
where $__timeFilter(time)
group by device_id, time
order by time

However the following modifications do not:

select * from (select $__timeInterval(time) as time, device_id, avg(temp) as temp
from measurements
where $__timeFilter(time)
group by device_id, time
order by time)

or

with cte as (select $__timeInterval(time) as time, device_id, avg(temp) as temp
from measurements
where $__timeFilter(time)
group by device_id, time
order by time)
select * from cte

[gingerwizard]

You device_id to be a string

[stas-sl]

Well, despite it contains numbers, it is a String indeed (LowCardinality(String) to be precise) - you can see it from column header icon "A". And if it was a number, it would not work with a simple query as well.

[gingerwizard]

so you can use a transform to wide series to fix this but I don't understand why this isn't working - ideas @bossinc ?

I can't reproduce with my own sub selects - I'll try CTEs as well.

[stas-sl]

Thanks for looking into this! So you're saying it works for you? That's strange...

I've tried to use transform to wide series, but for some reason there was no effect:

However transforming to multi-frame worked:

If you confirm that it works for you - both subqueries and CTEs, then I'll be trying to figure out what I'm doing wrong. Maybe will reinstall the plugin.

[stas-sl]

Well, plugin reinstallation didn't help.

My CH version is 22.3.3.44, if it makes any difference.

[stas-sl]

I don't know why, but when I view results in query inspector device_id column is formatted as temperature using ℃. Although, as you see it has "A" in column header, and I tried to explicitly convert it to string - toString(device_id). It happens only when I wrap the query in CTE/subquery, otherwise it works well.

And response types look correct to me:

{
  "response": {
    "results": {
      "A": {
        "frames": [
          {
            "schema": {
              "name": "A",
              "refId": "A",
              "meta": {
                "preferredVisualisationType": "table",
                "executedQueryString": "..."
              },
              "fields": [
                {
                  "name": "time",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time"
                  }
                },
                {
                  "name": "device_id",
                  "type": "string",
                  "typeInfo": {
                    "frame": "string"
                  }
                },
                {
                  "name": "temp",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float64"
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  1663588800000,
                  1663588800000,
                  1663589400000,
                  ....
                ],
                [
                  "1231111",
                  "5449773",
                  "5449773",
                 ....
                ],
                [
                  11.505,
                  14.626666666666667,
                  15.0475,
                   ...
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

[gingerwizard]

Apologies i meant multi-frame not wide. I'll see if I can reproduce tmr.

[aangelisc]

@gingerwizard did you reproduce this issue?

[mrsndmn]

I have the same problem with

Versions
Grafana: 10.3.1
Plugin: 4.0.1

Query

SELECT time as time, s as metric, sum(a) FROM VALUES(
    'time DateTime, s String, a UInt64',
    (toDate(now()), 'one', 1),
    (toDate(now()), 'two', 2),
    (toDate(now()), 'three', 3),
    (toDate(now() - interval 1 day), 'one', 1),
    (toDate(now() - interval 1 day), 'two', 2),
    (toDate(now() - interval 1 day), 'three', 3)
    )
group by time, s
order by time

Multi-frame transform

Multi-frame transform fixed a problem. Thank you!

[aangelisc]

Hi @mrsndmn, if the multi-frame transformation solved this problem for you please let us know. If you're still having problems please create a new issue.

@stas-sl, @gingerwizard I'm going to close this issue as there's been awhile without any activity or response. If this is still an issue please re-open 😊

[iyuroch]

@aangelisc I'm having the same issue, multiframe seems to help, however as per my understanding it should be working without transformation, do I miss something?