Repository navigation
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" #24072
Description
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.
Activity
rusackas commentedon May 17, 2023
@sujiplr any insights here, perchance?
sfirke commentedon Aug 10, 2023
I ran into this today in 2.1.0. I ran SQL Server Profiler, there's no query being made against the DB -- the error message comes from SQL Alchemy.
sfirke commentedon Aug 11, 2023
This message originates from SQLAlchemy and has been patched in other projects. Resources for anyone who wants to tackle it:
gramener/gramex#626 (comment) has a list of links
mrjoes/flask-admin#8 has a fix description, as does
pallets-eco/flask-admin#582 (comment)
sfirke commentedon Aug 11, 2023
pinging @villebro as you have fixed similar MS SQL Server quirks like this in the past - would this be a quick fix for you or is there another contributor you think would be well-suited to handle it?
sujiplr commentedon Aug 11, 2023
Hi Guys, Sorry for the delayed response. I was out for few weeks and missed the notification. @dastein1 whether you able to get the SQL for this case. So that can check how currently it's being built before digging more on code level.
sfirke commentedon Aug 11, 2023
@sujiplr I tried to get the SQL call from SQL Server Profiler but it seems like SQL Alchemy rejects the query from Superset and sends back that error message, nothing makes it to the DB as far as I can tell. So I'm not sure how to get the SQL.
sujiplr commentedon Aug 11, 2023
@sfirke , let me debug something here
dastein1 commentedon Sep 11, 2023
Any news on that one?
edxe commentedon Oct 5, 2023
having this same issue. Any alternatives here?
nbauneau commentedon Oct 10, 2023
We have the same problem without any solution on "drill to detail" feature
[-]MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause[/-][+]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"[/+]18 remaining items
MikeDel001 commentedon Mar 18, 2025
Are we any closer to a resolution for this?
sfirke commentedon Mar 19, 2025
No, I haven't worked on this, sorry. Anyone is welcome to help. You could either change the code so that it doesn't alter the result of the end-to-end test, or figure out what the correct result should be for my failing test that has re-ordered the dataframe and then send the update to my PR (or your own fork).
rusackas commentedon Jun 10, 2025
Anyone is welcome to adopt the PR... I would if I had access to MSSQL 😅
This may be closed due to inactivity at some point, since it's been silent for a few months.
I also can't help but wonder if the SQLGlot migration has made any difference here (probably not) if they want to test this on
mastersfirke commentedon Jun 10, 2025
Agreed anyone is welcome to adopt this! I would ask that we keep it open but I won't get to it in the next month at least.
sfirke commentedon Jun 10, 2025
I saw Beto introduced a Superset SQL dialect for Firebolt and I wonder if that sort of dialect extension for MSSQL within Superset would be a way to handle this particularly for MSSQL, rather than modifying the main codebase.
sfirke commentedon Aug 6, 2025
I just submitted a PR that fixes this by editing the db_engine_spec for MSSQL: #34583
sfirke commentedon Aug 29, 2025
Finalllllllly fixed! Coming to a future release near you.
Mady1111 commentedon Nov 20, 2025
I was using Apache Superset version 4.0.2, and at that time I was able to see the Drill to Details option.
However, the second page of Drill-to-Detail for the MSSQL data source resulted in the following error:“requires an order_by when using an OFFSET or a non-simple LIMIT clause.”
Because of this, I upgraded to Apache Superset version 6, but now I am not able to see the Drill to Details option for any chart.
I also added the following settings in the superset_config.py file:
"DRILL_TO_DETAIL": True
"DRILL_BY": True
But the option still does not appear.
Can you please confirm whether this issue has been resolved in Superset version 6? If yes, where can it be enabled, or is there any additional configuration required? I would be helpful
sfirke commentedon Nov 20, 2025
hi @Mady1111 - Superset 6 has not been released yet. I wonder what version you updated to. I tested Superset 6.0.0rc2 and since I reported a bug related to drill to detail, I can confirm that feature hasn't been disabled. Maybe your user you're testing has the wrong permissions?
You could try deploying Superset 6.0.0rc3, the third release candidate, which was released last night for testing and voting.
sfirke commentedon Nov 24, 2025
Yep I just tried testing 6.0.0rc3 and this bug is patched there.
Mady1111 commentedon Nov 28, 2025
@sfirke - Now I'm able to see the drill to details in 6.0.0rc2 version, I just hit superset init in superset container for reset the permissions to give dashboard access for other user, after that drill down enables automatically.
Thanks.