SQL Expression Input Conversion Notes

README.md

SQL Expressions and Input

• What do we want to happen to data when read by SQL Expressions?
  • Data from an individual query needs to appear as a single table
    • Raw table data maps directly to single table
    • The "Long" formats of the TimeSeries and Numeric kinds map directly to a table
    • The "Multi" and "wide" formats of the TimeSeries and Numeric kinds need conversion to the kind's "Long" format
    • For now I'm calling this "SQL Conversion Rules"
• What does SSE currently do for conversion?
  • To generalize, SSE reads TimeSeries and Numeric data in different formats, and converts them to the multi format for their kind. Things like alerting depend on this behavior.
  • This conversion behavior is near the inverse of the desirable behavior for SQL expressions
  • For now I'm calling this "[Current] SSE TSDB Style Conversions"
• How do we get the behavior for SQL expressions without breaking existing behaviors?
  • Consideration: These conversions (or not converting when in other cases it does) will likely be a bit confusing to users. In short, when they add a SQL expression and have it read data, the input may look different from what it was before (and this can happen in a way which would make the data look like it does from the datasource without any expressions). I don't see a way around this -- but we can try to make it clearer to the user with UI.
  • Some approaches:
    • Note: Before running a query, we generally don't know what the response type will be. We do know if it will be the input to a SQL expression or not.
    • Option A: We keep an unconverted (hasn't gone through SSE TSDB Style Conversion) copy of data from data source queries, and the SQL expression reads from that (and does its conversion as needed per "SQL Conversion Rules")
    • Option B: We perform "SQL conversion Rules" when the input goes to a SQL expression, otherwise the "SSE TSDB Style Conversions". This means a single query could not be the input to a SQL expression and a non-SQL expression at the same time.
    • Option C: Somehow give control of the conversion to the user via the UI and API. (A no-convert button might be generally useful, however if that is needed - like not a great UX)
• How might do Option B?
  • The expressions and data source queries form a graph of the dependencies. This graph is formed before execution, and conversion happens after execution. So building the graph pkg/expr/graph.go, if the node is a dependency (NeedsVars()), we could mark the node to skip normal SSE conversion (or do a different conversion), see the buildGraphEdges func