SQL Expressions -- Errors, No Data and Dynamic Schema Query Responses

There are three general situations that SQL expressions does not handle gracefully that users should be aware of that are not bugs, but rather current limitations of the feature. Those are:

1. Error Responses
2. No Data Responses
3. Dynamic Schema responses (Ones where columns are labels change between executions of the query)

Why are there these limitations?

SQL Expression is based on a SQL engine, where the results of each query become appear as if they were a table in SQL.

In SQL expressions the schema (available columns) of those tables depends on the data returned -- which makes it dynamic (changing over time).

However, in SQL -- schema (generally speaking) doesn't change (much) between queries -- the schema and columns are static.

So in the case of something like No Data, or different set of labels keys being returned from say a prometheus query over time, or an error -- SQL expressions will see that as a columns not existing. So SQL layer will see that as a user trying to query a column that doesn't exist.

What can a user do?

Right now, two things:

1. For changing columns: Avoid things like Select * and select specific columns
2. If possible force it so when there is no data, that you still get columns

Prometheus No Data Example

If you wanted to join the results of the same Prometheus query against different instances of a prom data source, you can try something like the following (this needs more followup verification)

Prom Query:

sum by (cluster) (up{job=~".*zruler.*"})
or on (cluster) (
  (0/0) *
  (
    label_replace(vector(1), "cluster", "fake", "", "")
  )
)

SQL Query:

SELECT
    COALESCE(a.time, b.time) AS time,
    COALESCE(a.cluster, b.cluster) AS Cluster,
    COALESCE(a.up, 0) + COALESCE(b.up, 0) AS UnifiedUp
FROM (
    SELECT time, cluster, __value__ AS up
    FROM A
    WHERE cluster != "fake"
    ORDER BY time
    LIMIT 5
) a
FULL OUTER JOIN (
    SELECT time, cluster, __value__ as up
    FROM B
    WHERE cluster != "fake"
    ORDER BY time
    LIMIT 5
) b ON a.time = b.time;

Might this be improved in the future?

Yes, but an easy or clear path is not apparent yet. There are things like "Dynamic SQL", but that is not part of go-mysql-server, the engine we use. We can make a contribution to that - we can also extend GMS with custom functions that might help this.

But since tables and columns changing like this is not common for SQL (and not part of mySQL), presently this is a limitation.