superset: The second page of drill-to-detail with MSSQL data source errors"requires an order_by when using an OFFSET or a non-simple LIMIT clause"
A clear and concise description of what the bug is.
How to reproduce the bug
-
Enable Feature DRILL_TO_DETAIL
-
Connect to MSSQL db and create a table-chart with some group by or a big number chart.
-
Add the chart to some dashboard and click on ‘Drill to Detail’
-
Upon navigating to the second page see the error:
Error: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause
Expected results
Ideally there would be a way to utilize the drill to detail feature with ms sql and be able to browse through the data.
Actual results
Upon navigating to the second page see the error:
Error: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause
Screenshots
Environment
- browser type and version:
Version 0.103.0 - superset version: validated in 3.1.1, originally reported in 2.1.0
- python version:
Python 3.8.16 - node.js version: -
- any feature flags active:
DRILL_TO_DETAIL
Checklist
Make sure to follow these steps before submitting your issue - thank you!
- I have checked the superset logs for python stacktraces and included it here as text if there are any.
- I have reproduced the issue with at least the latest released version of superset.
- I have checked the issue tracker for the same issue and I haven’t found one similar.
Additional context
Add any other context about the problem here.
About this issue
- Original URL
- State: open
- Created a year ago
- Comments: 25 (16 by maintainers)
order by 1 is sorting by the 1st column.
I have changed _def get_drill_detail in /opt/superset/lib/python3.11/site-packages/superset/common/query_actions.py as follows:
query_obj.columns = qry_obj_cols query_obj.orderby = [(query_obj.columns[0], True)] <---- insert return _get_full(query_context, query_obj, force_cached)
@sfirke , let me debug something here
I believe Yongjie wrote most of that code, what do you think @zhaoyongjie ? We would delete the earlier line
query_obj.orderby = []and add the later line suggested above in bold. If you think this won’t have negative effects elsewhere, I’d be happy to test the fix and submit a PR.I think it is just a issue for MSSQL . We can use order by 1 as default order for MSSQL.
Example for MSSQL: SELECT * FROM tbl ORDER BY 1 OFFSET x ROWS FETCH NEXT y ROWS ONLY
This is still present in 3.0.0.
having this same issue. Any alternatives here?
Any news on that one?