Macro expansion in parameterized views

[ethack]

What happened:

The $__fromTime and $__toTime macros aren't expanded (properly) in a parameterized view's parameters. I'm not sure if any others work.

What you expected to happen:

I'd expect $__fromTime and $__toTime and other macros to work in a view's parameters like they would in a normal WHERE clause.

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

Create a parameterized view:

CREATE VIEW dummy AS
SELECT toDate('2023-01-01') + number AS date FROM numbers(365)
WHERE date > {fromTime:DateTime} AND date <= {toTime:DateTime};

Queries go into Explore with the time picker set to sometime in 2023 (I had last 2 days set).

This works when I manually specify the parameters but use the macros in the WHERE:

-- this works
SELECT * 
FROM dummy(fromTime='2023-09-01', toTime='2023-09-30')
WHERE date > $__fromTime AND date <= $__toTime

Query inspector shows this as the final query:

This doesn't work when I use the macros directly in the view parameters:

-- this fails
SELECT * 
FROM dummy(fromTime=$__fromTime, toTime=$__toTime)

I took the expanded values from the earlier query and inserted directly into the parameterized view and this doesn't work either.

-- this fails
SELECT * 
FROM dummy(fromTime=toDateTime(intDiv(1695000381376,1000)), toTime=toDateTime(intDiv(1695173181376,1000)))

It seems that no functions work in this context.

-- this fails
SELECT * 
FROM dummy(fromTime=toDateTime(1695000381), toTime=toDateTime(1695173181))

Nor math (though I think this would normally get converted to the divide function anyway):

-- this fails
SELECT * 
FROM dummy(fromTime=1695000381376/1000, toTime=1695173181376/1000)

But literals do:

-- this works
SELECT * 
FROM dummy(fromTime=1695000381, toTime=1695173181)

Maybe this is a shortcoming of parameterized views in ClickHouse and deserves a feature request there. But in the meantime, any thoughts on a workaround for the Grafana data source?

Anything else we need to know?:

Environment:

• Grafana version: 10.1.2
• Plugin version: Unsure, but I just rebuilt the docker image with the latest plugin so probably v3.3.0
• OS Grafana is installed on: Ubuntu 22.04 (but using the official grafana docker image)
• User OS & Browser: PopOS 22.04 & Chrome
• Others: ClickHouse version: 23.8.2.7

[SpencerTorres]

Thank you for the detailed info, I was able to reproduce all of these findings.

I ran the debugger in the plugin and it appears that the macros ARE being interpolated correctly, in fact the string looks the same as when you manually entered the functions from the working query:

-- Debugger value for line https://github.com/grafana/sqlds/blob/main/macros.go#L262
SELECT *
FROM dummy(fromTime=toDateTime(intDiv(1695237644505,1000)), toTime=toDateTime(intDiv(1695410444506,1000)))

This never gets sent back to the query inspector since the query failed, so you wouldn't be able to see this without debugging the plugin backend or checking the query_log table.

Maybe this is a shortcoming of parameterized views in ClickHouse and deserves a feature request there.

I also tried running this directly in the ClickHouse client, and it gives the same error. It seems like this is something that should be possible to do, but for now it doesn't work. I would recommend opening an issue in the ClickHouse repository (make sure it's not a duplicate)

But in the meantime, any thoughts on a workaround for the Grafana data source?

Given the issue with the parameter values, I don't think there is a workaround currently. Is it possible for you to use the original query used to create the parameterized view?

[aangelisc]

Given what you've said @SpencerTorres it sounds like this isn't a datasource issue, are we fine to close this?

[dbug-river]

Related to this issue, would it be possible to have a macro that outputs plain dates which are specified in the alert/dashboard parameters ?

SELECT * 
FROM dummy(fromTime=$__fromStartTime, toTime=$__toEndTime)

and the values are replaced as the following:

SELECT * 
FROM dummy(fromTime='2024-04-10 01:00:00', toTime='2024-04-10 03:00:00')