Fail to get type of "Expressions" when result is empty.

[n-arakawa]

Describe the bug I'm not sure this is a bug of this plugin or SQLite itself, I got 'number' type for "Expression" column even though its "Affinity" is "TEXT"

https://www.sqlite.org/datatype3.html#affinity_of_expressions

To Reproduce

With grafana explorer, type in query.

SELECT "a" WHERE 1=0
or SELECT CAST("a" as TEXT) WHERE 1=0

The type of the column is 'number'.

Without WHERE 1=0 , I got "string" type SELECT CAST("a" as TEXT)

Versions (please complete the following information):

• OS: Ubuntu20.04
• Run via docker?:yes
• Browser: Chrome
• Grafana :8.2.0
• Plugin Version 2.1.0
• Plugin installed via grafana-cli or manually?: grafana-cli

[fr-ser]

The problem might be that the plugin needs to determine the data type from the data itself. When there is no data this is not easily done and string is probably just the default.

Can you explain the issue this is causing?

[n-arakawa]

The problem might be that the plugin needs to determine the data type from the data itself.

Types of database column is correct even with empty result. Therefor I guess SQLite has a API to get type information of columns. I'm not sure whether there is corresponding API for "Expression" column.

Can you explain the issue this is causing?

My use case is quite a edge one,

• I use "Expression" column.
• I use "Merge" Transformation to make a singe table from multiple SQL's
• I use "text" type column.
• When the first SQL result is empty and the second SQL returns text data, type conflict occurs because the default type seems to be a "number".

I found work around for this. I use "UNION ALL" in SQL and remove "Merge" Transformation. Everything is fine now.

[fr-ser]

Yeah. I would also suggest to do this in SQL if possible. UNION ALL seems like the best way to go here.

If this is causing more issues, let's revisit 👌🏻