Annotation queries aren't working

[ralston]

Bug Report

I've found a few different issues with annotation queries:

1. They don't appear to expand macros -- for the life of me I can't get a query to succeed when using $__timeFilter().

2. Even when the query succeeds, no annotations show up in the dashboard.

Expected Behavior

• Annotations loaded from bigquery show up in graph panels.
• Annotations queries can use macros

Actual Behavior

Annotations don't show up. It looks like text column might not be deserialized properly

Steps to Reproduce the Problem

Use this trivial annotation query:

SELECT
  UNIXTIME(DATETIME("2019-10-08 08:40:00", "America/Los_Angeles")) as time,
  "Some Text" as text

Set up a dashboard over the relevant timespan (and for fun, add a table that shows annotations):

Specifications

• Version: doitintl-bigquery-datasource @ 1.0.2
• Platform: linux (GCE)
• Grafana Version: v6.3.6

[avivl]

Can you share the full query?

[ralston]

That is the full query (I made a simple select to demonstrate the problem - you should be able to reproduce by running exactly that select (with an appropriate date value))

[junjieli1]

Same issue happened to me. I have this query used for annotation:

SELECT
  end_time AS time,
  name as text,
  type as tags
FROM
  <some table name>
WHERE
  $__timeFilter(end_time)

And I looked the query history from BigQuery console and saw:

SELECT
  end_time AS time,
  name as text,
  type as tags
FROM
  <some table name>
WHERE
  $__timeFilter(end_time)

Looks like the micro was not expanded.

[junjieli1]

Do you guys have an example showing how using bigquery for annotation works?

[junjieli1]

OP's second issue applies to my case as well. After I removed the timeFilter macro, the query succeeded as I can see from BigQuery console. But there is no annotation showing up on my dashboard.

[avivl]

@junjieli1
Since BQ can't handle aliases in the where clause you will need to use the having clause:

SELECT
  end_time AS time,
  name as text,
  type as tags
FROM
  <some table name> group by 1,2,3
HAVING
  $__timeFilter(time)

[junjieli1]

Hi @avivl, I tried your suggestion but the error is:

Syntax error: Illegal input character "$" at [8:3]

The query I used is:

SELECT
  end_time AS time,
  name as text,
  type as tags
FROM
  `<table_name>` group by 1,2,3
HAVING
  $__timeFilter(time)

[avivl]

@junjieli1 did you used the latest version from the master branch?

[junjieli1]

@avivl no we use v1.0.1

[avivl]

@junjieli1 please try with the latest from master 1.0.3

[junjieli1]

Hi @avivl, so I've just upgraded to 1.0.5. Now with the following query, I can see the annotation dotted line on the dashboard. However, when I hove over the dotted line, it doesn't show any name or tag. Do you know what could have gone wrong? Thanks!
SELECT start_time AS time, name AS text, tags AS tags FROMmydb.mytableWHERE type='mytype' and $__timeFilter(time)

[avivl]

@junjieli1 the latest version is 1.0.6 please use it

[junjieli1]

Reporting back, 1.0.6 works for me! Thanks @avivl !

[avivl]

Reporting back, 1.0.6 works for me! Thanks @avivl !

👍