dagster
dagster copied to clipboard
dagster-io
sql error querying run schedules
Dagster version
0.15.6
What's the issue?
I am working on upgrading the mssql fork of the mssql package i developed some time ago. I figured out that i receive errors in the UI when viewing the sensor runs. The generated query from SqlRunStorage._runs_query is wrong and not following standard SQL.Due to the filters.tags being applied in the graphql query from the UI, we need an aggregation on all non grouped columns.
deployment-dagster-1 | sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'runs.status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120) (SQLExecDirectW)") deployment-dagster-1 | [SQL: SELECT TOP 1 runs.id, runs.run_body, runs.status, runs.create_timestamp, runs.update_timestamp, runs.start_time, runs.end_time deployment-dagster-1 | FROM runs JOIN run_tags ON runs.run_id = run_tags.run_id deployment-dagster-1 | WHERE run_tags.[key] = ? AND run_tags.value = ? GROUP BY runs.run_body, runs.id deployment-dagster-1 | HAVING count(runs.run_id) = ? ORDER BY runs.id DESC] deployment-dagster-1 | [parameters: ('dagster/sensor_name', 'my_directory_sensor_cursor', 1)]
What did you expect to happen?
A correct query being generated.
How to reproduce?
If running on a SQLRunStorage, you need to route to instance/sensors
Deployment type
No response
Deployment details
No response
Additional information
I know that mssql is not officially supported, but as per the SQL standards you need to provide aggregation functions for all non-grouped columns. I cannot believe this issue would not occur in postgres or mysql.
Message from the maintainers
Impacted by this issue? Give it a 👍! We factor engagement into prioritization.
Internally, I got this fixed via this line of code Would you mind checking if that is what you expected this query to look like?
Hi @nico525 sorry about the late response. I think you are right - this seems to be a bug and should be fixed. Besides, we'd definitely welcome a contribution if this is something you'd like to open a PR for.
I think this has been replaced with more standard joins/intersections starting in 1.0.10.
