Alerts trying to change integers to toDate() Using Clickhouse DB

Grafana
,
1

I recently upgraded my grafana from v8.5.6-1 to v11.0.0-1 and lost my alert rules.
I am now re-creating them and I am having some problems adding an alert rule, where I get the following error:

[sse.dataQueryError] failed to execute query [A]: Code: 46. DB::Exception: Unknown function toDATE. Maybe you meant: ['toDate','toDate32']: While processing SELECT (intDiv(toUInt32(record_datetime), 1800) * 1800) * 1000 AS t, avg(mos) FROM hepic_data.sip_transaction_call WHERE (record_datetime >= toDateTime(1717420888)) AND (record_datetime <= toDateTime(1718025688)) AND (captid IN (9174, 9174)) AND (mos > toDATE(1718025688)) AND (duration > toDATE(1718025688)) GROUP BY t ORDER BY t ASC LIMIT 5000. (UNKNOWN_FUNCTION) (version 21.9.4.35 (official build))

Here is the original query:

SELECT
    $timeSeries as t,
    avg(mos)
FROM $table

WHERE
    $timeFilter
    AND captid in (9174,9174) AND mos > 1 AND duration > 1
GROUP BY
    t
ORDER BY t

LIMIT 5000

It seems to be trying to convert certain integers to date using ‘toDATE()’
The Data source is a Clickhouse Database and I am using Grafana version 11.0.0

Here is a screen shot if it helps with context:

image
image1160×626 71.2 KB

I have confirmed the query works when the ‘mos’ and ‘duration’ WHERE clauses are integers:
image

Please let me know if any further information is needed.
Any assistance with this will be greatly appreciated.

2

what happens if you change things to

SELECT $timeSeries as time,
       avg(mos) as value
 FROM $table
WHERE $timeFilter
  AND captid in (9174,9174) 
  AND mos > 1 
  AND duration > 1
GROUP BY 1
ORDER BY 1

LIMIT 5000
3

Hi yosiasz, I am busy testing. It seems to not update correctly when I make changes.

4

I still seem to get the “Unknown function toDATE” error:

image
image1159×626 73.3 KB

5

look at the generated sql in that error.

intDiv(toUINt32 part for $timeSeries what is going on there? Can you copy and run that whole sql statement in ClickHouse and see what you get?

6

if I run the generated query ‘as is’ I get the same error:

image
image819×589 83.3 KB

But if I correct the ‘mos’ and ‘duration’ WHERE clauses it works fine:
image

7

So in your grafana query you do have it as

mos > 1 but somehow it is being converted to mos > toDate(1718028616)

1718028616 = Mon Jun 10 2024 14:10:16 GMT+0000

Why is that? What data type is mos ?

8

In the DB
mos=Int16
duration=Int32

Output of working query
mos=Float64 #this is due to avg()

9

are there functions to make mos > int16(1) otherwise as you can see it is converting that 1 to toDate() you need to fix that so that it does not convert it to date, that is your issue. could be a bug in the clickhouse plugin. you have not yet explained why it does that odd conversion

10

Yeah, I have no idea why it is trying to convert to toDate(). I will see if I can get some support on the Clickhouse plugin.