Nesting variables not working correctly

[mzealey]

What happened:

Basic issue is that when a datapoint is not specified I wish to have it listed as zero. Other drivers (such as postgres) do this in a somewhat magic way.

Using the other CH Grafana driver we were doing:

select  
  intDiv(toUnixTimestamp(Timestamp), $interval) * $interval * 1000 AS t
  , IF(SpanName = '', 'unknown', SpanName) AS d
  , count() / $interval AS data                                                                                                                                               
FROM otel.otel_traces
WHERE Timestamp BETWEEN $from AND $to
  AND ServiceName in ($service_names)
  AND ParentSpanId = ''
  AND toUInt16OrNull(SpanAttributes['http.status_code']) between 200 and 399
GROUP BY t, d
ORDER BY t
  with fill
    from intDiv($from, $interval) * $interval * 1000
    to intDiv($to, $interval) * $interval * 1000
    step $interval * 1000, d

Converting to the latest public version of this driver I have come up with the following:

select
  $__timeInterval(Timestamp) AS t
  , IF(SpanName = '', 'unknown', SpanName) AS d
  , count() / $__interval_s AS data
FROM otel.otel_traces
WHERE $__timeFilter(Timestamp)
  AND ServiceName in ($service_names)
  AND ParentSpanId = ''
  AND toUInt16OrNull(SpanAttributes['http.status_code']) between 200 and 399
GROUP BY t, d
ORDER BY t with fill from $__timeInterval($__fromTime) to $__timeInterval($__toTime) step $__interval_s
  , d

This seems to work 50% of the time and not the other 50%; looking in query inspector, the resultant sql seems bistable between having the outer $__timeInterval macros being interpolated and not

What you expected to happen:

The outer macros should always be interpolated.

Alternatively, some other way of specifying non-existent values as 0 could be provided.

Alternatively, new variables such as $__fromTimeTruncated and $__toTimeTruncated could be provided to avoid having to nest variables.

[aangelisc]

Hi @mzealey, can you show me the query inspector output please 😊

[mzealey]

select
  toStartOfInterval(toDateTime(Timestamp), INTERVAL 60 second) AS t
  , IF(SpanName = '', 'unknown', SpanName) AS d
  , count() / 60 AS data
FROM otel.otel_traces
WHERE Timestamp >= '1689834429' AND Timestamp <= '1689856029'
  AND ServiceName in ('xxx')
  AND ParentSpanId = ''
  AND toUInt16OrNull(SpanAttributes['http.status_code']) between 200 and 399
GROUP BY t, d
ORDER BY t with fill from $__timeInterval(toDateTime(intDiv(1689834429025,1000))) to $__timeInterval(toDateTime(intDiv(1689856029026,1000))) step 60
  , d

and then it's pretty much bi-table between the above and:

select
  toStartOfInterval(toDateTime(Timestamp), INTERVAL 60 second) AS t
  , IF(SpanName = '', 'unknown', SpanName) AS d
  , count() / 60 AS data
FROM otel.otel_traces
WHERE Timestamp >= '1689834452' AND Timestamp <= '1689856052'
  AND ServiceName in ('xxx')
  AND ParentSpanId = ''
  AND toUInt16OrNull(SpanAttributes['http.status_code']) between 200 and 399
GROUP BY t, d
ORDER BY t with fill from toStartOfInterval(toDateTime(toDateTime(intDiv(1689834452214,1000))), INTERVAL 60 second) to toStartOfInterval(toDateTime(toDateTime(intDiv(1689856052214,1000))), INTERVAL 60 second) step 60
  , d

actually feeling more like a race than a bi-stable when i hit refresh from the query inspector now

[mzealey]

nb this is grafana v9.5.1 (bc353e4b2d) and ch plugin 3.1.0

[bossinc]

This is a problem with SQLDS. I created an issue in that repo. grafana/sqlds#97
Once we fix the issue in SQLDS we will need to update the dependency here.