Template variables expand to the text instead of the value

[LS80]

What happened:
Template variables expand to the text instead of the value.

What you expected to happen:
The template variable has the value of the template variable not the display text.

How to reproduce it (as minimally and precisely as possible):
Create a template variable var using an Athena data source as the following query

SELECT 'foo' AS __text, 'bar' AS __value

Then use $var in the dashboard. The value should be bar but it is foo.

When I do the same for a PostgreSQL data source it works as expected.

Screenshots

Anything else we need to know?:

Environment:

• Grafana version: v10.1.2
• Plugin version: v2.11.1
• OS Grafana is installed on: Linux
• User OS & Browser: Mac OS / Firefox
• Others:

[idastambuk]

Hi @LS80, thanks for submitting this. I managed to reproduce it and will add it to our backlog.

[idastambuk]

Possibly the same cause as with this one: grafana/iot-sitewise-datasource#233. I see that the variable value and text is the same in Athena like in Sitewise. They both have CustomVariableSupport for query variables (though it's been reverted in Sitewise)

[iwysiu]

Hi @LS80 ! I was investigating this, and it seems like if you set the names as text and value it should work (for example, the repro would be SELECT 'foo' AS text, 'bar' AS value instead)

Can you try that and tell me if it works?

[LS80]

it seems like if you set the names as text and value it should work

Yes that does indeed work. Is there any reason why it's not __text and __value as for the other datasources? Is it documented?

[iwysiu]

Ah @LS80 , sorry, I missed that in the documentation. That is a bug! It sounds like we have a workaround for now, but __text and __value are supposed to work, and I'll take another look at that.

[StephenKing]

Wait, this is not really working (at least not in the Redshift data source as filed in grafana/redshift-datasource#275):

While a dummy query such as

SELECT 'redshiftvalue' AS value, 'redshifttext' AS text

indeed reveals ${var}=redshiftvalue and ${var:text}=redshifttext, this doesn't work for any regular quer:

SELECT foo AS value, bar AS text FROM mytable

This always results in the text.

(EDIT: I'm posting this here, as this has more details than the redshift issue, and I guess they require the same change)

[sarahzinger]

Hey @StephenKing great catch! I can recreate this as well, looks like we have a bug on our end.

Some repro steps for anyone picking this up in the future:

I have a table of test data that looks like this:

I then create a variable that in theory I believe should create a list of catids as values, but where the visual text for it is the category names:
SELECT catname as text, select catid as value from category

Then I try to use that variable in my query:

and I get an error, we can see in the query inspector, the query is interpolating the text instead of the value: select * from category where catid = 'NHL'

This is again confirmed if I change my query to use the text instead of the value: select * from category where catname = '$catidvar' and it works.

I can recreate the same problem in athena. Will move this to the backlog for now, but will see if the team can look into prioritizing this.

[idastambuk]

Hi @StephenKing, @LS80 we're just updating our docs, which should clarify how variables work in both Athena and Redshift:

1. Instructions to add SELECT 'foo' AS __text, 'bar' AS __value was misleading - Grafana only supports text and value out of the box. We have updated the Grafana docs here
2. @StephenKing I think your problem might be caused by the column value being other than string, which is what Grafana supports when adding custom display values for variables. It should be fixed by casting the value column to string type, for example SELECT CAST(foo AS varchar) AS value, bar AS text FROM mytable. Can you confirm that this fixes the problem?
   We're working on updating Athena and Redshift docs to reflect this.

[StephenKing]

Thanks a lot @idastambuk, that helped!

[idastambuk]

@StephenKing Thanks for letting us know, will close this then :)