fix(mssql): support top syntax for limiting queries

[sujiplr]

MSSQL is not supporting LIMIT syntax in SQLs. For limiting the rows, MSSQL having a different keyword TOP. Added fixes for handling the TOP and LIMIT clauses based on the database engines. Made this fix as generalised one for handling different database engines.

SUMMARY

This fix will help to start supporting TOP clause in SQLs. And also along with CTE queries.

BEFORE SCREENSHOTS

AFTER SCREENSHOTS

TESTING INSTRUCTIONS

You can test the SQLs as per the above screenshots.

ADDITIONAL INFORMATION

• Has associated issue: closes Common Table Expressions (CTE's) do not work for Microsoft SQL Server database #8074
• Required feature flags:
• Changes UI
• Includes DB Migration (follow approval process in SIP-59)
  • Migration is atomic, supports rollback & is backwards-compatible
  • Confirm DB migration upgrade and downgrade tested
  • Runtime estimates and downtime expectations provided
• Introduces new feature or API
• Removes existing feature or API

[codecov]

Codecov Report

Merging #18746 (ee37d94) into master (ea9a904) will increase coverage by 0.11%.
The diff coverage is 95.18%.

@@            Coverage Diff             @@
##           master   #18746      +/-   ##
==========================================
+ Coverage   66.31%   66.43%   +0.11%     
==========================================
  Files        1620     1617       -3     
  Lines       63088    62847     -241     
  Branches     6372     6320      -52     
==========================================
- Hits        41839    41750      -89     
+ Misses      19592    19445     -147     
+ Partials     1657     1652       -5     

Flag	Coverage Δ
hive	52.28% <20.48%> (+0.05%)	⬆️
mysql	81.55% <95.18%> (+0.12%)	⬆️
postgres	81.60% <95.18%> (+0.12%)	⬆️
presto	52.12% <20.48%> (+0.05%)	⬆️
python	82.03% <95.18%> (+0.12%)	⬆️
sqlite	81.29% <95.18%> (+0.12%)	⬆️

Flags with carried forward coverage won't be shown. Click here to find out more.

Impacted Files	Coverage Δ
superset/sql_lab.py	81.64% <ø> (ø)
superset/models/core.py	88.91% <66.66%> (-0.19%)	⬇️
superset/sql_parse.py	98.61% <92.85%> (-0.87%)	⬇️
superset/db_engine_specs/base.py	89.48% <97.91%> (+0.73%)	⬆️
superset/db_engine_specs/mssql.py	96.00% <100.00%> (+0.08%)	⬆️
superset/db_engine_specs/teradata.py	92.30% <100.00%> (+29.54%)	⬆️
superset-frontend/src/views/CRUD/utils.tsx	58.58% <0.00%> (-5.14%)	⬇️
...d/src/explore/components/PropertiesModal/index.tsx	68.33% <0.00%> (-1.67%)	⬇️
...rset-frontend/src/explore/exploreUtils/formData.ts	81.81% <0.00%> (-1.52%)	⬇️
...dashboard/components/menu/ShareMenuItems/index.tsx	66.66% <0.00%> (-1.20%)	⬇️
... and 52 more

---

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update ea9a904...ee37d94. Read the comment docs.

[villebro]

First pass comments. Let's also ping the original authors of the Teradata TOP functionality when this is ready 👍

[villebro]

Second pass comments

[villebro]

Some last nit comments, but I think this is pretty much ready for a final test pass + a few added unit tests to the MSSQL test suite. Also, I suggest checking failing workflows on CI, as there appears to be simple linting issues that need to be addressed.

[villebro]

@dmcnulla this PR generalizes the work you did in #18240 to make it work for MSSQL, too. Would you be able to review this PR, and better yet test it on Teradata to ensure it still works as intended? Also notice the updated Teradata unit tests (I pushed a few new ones to this PR as I noticed the SAMPLE keyword wasn't covered by the existing tests).

[villebro]

Two minor nits

[villebro]

Last nit, after this I believe this is good to go

[villebro]

LGTM! Thanks for all the hard work here; this is a major step forward in ensuring Superset works properly on MSSQL, Synapse and other Azure db products! ❤️

[sfirke]

I am noodling on #21027 (comment) and found this PR via blame view on the relevant code. @sujiplr do you have thoughts on that issue - modifying the codebase to support use of DISTINCT in SQL Lab for SQL Server? The current code injects TOP 1000 in the wrong place for MSSQL so it's SELECT TOP 1000 DISTINCT when it needs to be SELECT DISTINCT TOP 1000.

UPDATE @villebro has a PR to address this, #23751.