From 4ef180e25b53966cf001926c67b4474a6ab0d80f Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 13 Feb 2020 15:41:04 -0500 Subject: Document new LIMIT/OFFSET support; support subquery ORDER BY An adjustment to the original commit for the fix to #5084 in ab1799a2a1951fe8f188b6395fde04a233a3ac0d, correctly rendering ORDER BY for subqueries with the new syntax. Fixes: #5084 Change-Id: I5ab5c1887c5a10f0a5eed1e9aae1f5994c28d88e --- lib/sqlalchemy/dialects/mssql/base.py | 29 +++++++++++++++++++++-------- 1 file changed, 21 insertions(+), 8 deletions(-) (limited to 'lib/sqlalchemy') diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 6f3e8fb44..f900441a2 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -245,9 +245,16 @@ CREATE TABLE statement for this column will yield:: LIMIT/OFFSET Support -------------------- -MSSQL has no support for the LIMIT or OFFSET keywords. LIMIT is -supported directly through the ``TOP`` Transact SQL keyword. A statement -such as:: +MSSQL has added support for LIMIT / OFFSET as of SQL Server 2012, via the +"OFFSET n ROWS" and "FETCH NEXT n ROWS" clauses. SQLAlchemy supports these +syntaxes automatically if SQL Server 2012 or greater is detected. + +.. versionchanged:: 1.4 support added for SQL Server "OFFSET n ROWS" and + "FETCH NEXT n ROWS" syntax. + +For statements that specify only LIMIT and no OFFSET, all versions of SQL +Server support the TOP keyword. This syntax is used for all SQL Server +versions when no OFFSET clause is present. A statement such as:: select([some_table]).limit(5) @@ -255,8 +262,9 @@ will render similarly to:: SELECT TOP 5 col1, col2.. FROM table -LIMIT with OFFSET support is implemented using the using the ``ROW_NUMBER()`` -window function. A statement such as:: +For versions of SQL Server prior to SQL Server 2012, a statement that uses +LIMIT and OFFSET, or just OFFSET alone, will be rendered using the +``ROW_NUMBER()`` window function. A statement such as:: select([some_table]).order_by(some_table.c.col3).limit(5).offset(10) @@ -267,8 +275,9 @@ will render similarly to:: mssql_rn FROM table WHERE t.x = :x_1) AS anon_1 WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1 -Note that when using LIMIT and OFFSET together, the statement must have -an ORDER BY as well. +Note that when using LIMIT and/or OFFSET, whether using the older +or newer SQL Server syntaxes, the statement must have an ORDER BY as well, +else a :class:`.CompileError` is raised. .. _mssql_isolation_level: @@ -1884,7 +1893,11 @@ class MSSQLCompiler(compiler.SQLCompiler): def order_by_clause(self, select, **kw): # MSSQL only allows ORDER BY in subqueries if there is a LIMIT - if self.is_subquery() and not select._limit: + if ( + self.is_subquery() + and not select._limit + and (not select._offset or not self.dialect._supports_offset_fetch) + ): # avoid processing the order by clause if we won't end up # using it, because we don't want all the bind params tacked # onto the positional list if that is what the dbapi requires -- cgit v1.2.1