From 3c0a0a72015711989e06b16883a333a279bd095f Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 14 Apr 2021 16:14:36 -0400 Subject: don't base compilation off the int value of offset/limit part II Fixed an additional regression in the same area as that of :ticket:`6184`, where using a value of 0 for OFFSET in conjunction with LIMIT with SQL Server would create a statement using "TOP", as was the behavior in 1.3, however due to caching would then fail to respond accordingly to other values of OFFSET. If the "0" wasn't first, then it would be fine. For the fix, the "TOP" syntax is now only emitted if the OFFSET value is omitted entirely, that is, :meth:`_sql.Select.offset` is not used. Note that this change now requires that if the "with_ties" or "percent" modifiers are used, the statement can't specify an OFFSET of zero, it now needs to be omitted entirely. Fixes: #6265 Change-Id: If30596b8dcd9f2ce4221cd87c5407fa81f5f9a90 --- lib/sqlalchemy/testing/suite/test_select.py | 29 ++++++++++++++++++----------- 1 file changed, 18 insertions(+), 11 deletions(-) (limited to 'lib/sqlalchemy/testing/suite') diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py index 7318a4f33..7b35dc3fa 100644 --- a/lib/sqlalchemy/testing/suite/test_select.py +++ b/lib/sqlalchemy/testing/suite/test_select.py @@ -265,19 +265,26 @@ class FetchLimitOffsetTest(fixtures.TablesTest): [(4, 4, 5), (5, 4, 6)], ) + @testing.combinations( + ([(2, 0), (2, 1), (3, 2)]), + ([(2, 1), (2, 0), (3, 2)]), + ([(3, 1), (2, 1), (3, 1)]), + argnames="cases", + ) @testing.requires.offset - def test_simple_limit_offset(self, connection): + def test_simple_limit_offset(self, connection, cases): table = self.tables.some_table - self._assert_result( - connection, - select(table).order_by(table.c.id).limit(2).offset(1), - [(2, 2, 3), (3, 3, 4)], - ) - self._assert_result( - connection, - select(table).order_by(table.c.id).limit(3).offset(2), - [(3, 3, 4), (4, 4, 5), (5, 4, 6)], - ) + connection = connection.execution_options(compiled_cache={}) + + assert_data = [(1, 1, 2), (2, 2, 3), (3, 3, 4), (4, 4, 5), (5, 4, 6)] + + for limit, offset in cases: + expected = assert_data[offset : offset + limit] + self._assert_result( + connection, + select(table).order_by(table.c.id).limit(limit).offset(offset), + expected, + ) @testing.requires.fetch_first def test_simple_fetch_offset(self, connection): -- cgit v1.2.1