Alerting: Failed to evaluate queries and expressions: input data must be a wide series but got type long (input refid)

[Boeller666]

I tryed to create an alert from a query that already works in explore, even as table or time series:

SELECT
  a.publishTime as TIMESTAMP,
  a.entityId,
  a.status,
  a.publishTime,
  a.operation,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), a.publishTime, MINUTE) as state_since
FROM `events` AS a
JOIN (
  SELECT
    entityId,
    MAX(publishTime) as maxPublishTime
  FROM `events`
  WHERE $__timeFilter(publishTime)
  GROUP BY entityId
) AS b ON a.entityId = b.entityId AND a.publishTime = b.maxPublishTime
WHERE a.status IN ("A", "B") AND a.operation = "transition"
AND $__timeFilter(a.publishTime)
order by TIMESTAMP ASC;

When using this as a table I got:

Failed to evaluate queries and expressions: input data must be a wide series but got type long (input refid)

When using this as a time series I got:

Failed to evaluate queries and expressions: no float64 value column found in frame A

Maybe related to https://github.com/grafana/grafana/issues/48072 ?

[Boeller666]

Is anybody able to check if this is a bug or if I'm doing something wrong?

[zoltanbedi]

Hey @Boeller666, Sure thing, let's try to make this simpler. Can you provide a test database or csv I can use to test this? Can you please tell us more about the data types you are using there?

[Boeller666]

These are the data types:

• operation STRING REQUIRED
• uniqueEventId STRING REQUIRED
• entityId STRING REQUIRED
• publishTime TIMESTAMP REQUIRED
• projectId STRING REQUIRED
• clipId STRING REQUIRED
• chunkId STRING REQUIRED
• stepName STRING REQUIRED
• workflowId STRING REQUIRED
• transition STRING NULLABLE
• status STRING NULLABLE
• statusOld STRING NULLABLE
• modifiedByUser STRING NULLABLE
• job STRING NULLABLE
• workflowName STRING NULLABLE
• workflowSection STRING NULLABLE
• isAutomatic BOOLEAN NULLABLE
• duration NUMERIC NULLABLE
• timestampInsertEvent TIMESTAMP NULLABLE

and a csv is attached example-data.csv

[zoltanbedi]

Hey @Boeller666, Thanks for providing me data to reproduce this. I was able to see the error you mentioned. I suspect you only see this when a query returns no data right?

We will fix this soon anyways. Thanks for reporting.

[Boeller666]

Hmm, have to check if it only occurs if there is no data returned. Will tell you later!

[Boeller666]

Yes, seems it will not fail if data is returned. Is there any other PR, branch or whatever to link here that solves this issue?

[zoltanbedi]

@Boeller666 nope, this is not picked up yet. We had something similar in the core sql data sources recently https://github.com/grafana/grafana/pull/67844/files#diff-fb30141c4ef5bef3c02a17014449038daac2b593e7cc4e066fb3c35243a4e7dcL317 the cause of the issue is that we return the column names with null values and alerting doesn't consider this an empty dataframe. The fix we did for the sql data sources is to remove the columns if they are empty. It looks like a proper fix would be not to return the column names from upstream. From here https://github.com/grafana/grafana-plugin-sdk-go/blob/main/data/sqlutil/sql.go

[gabor]

( as this relates to the no-results case, this may be similar to https://github.com/grafana/grafana/issues/82306)