diff options
| author | Mike Bayer <mike_mp@zzzcomputing.com> | 2022-07-07 12:07:39 -0400 |
|---|---|---|
| committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2022-07-20 11:02:24 -0400 |
| commit | 5fc46b192b5005fa6962110a683abf1d296786d8 (patch) | |
| tree | e0988fe61c6d4b79c71dc84b265885a13df6f74f /test/requirements.py | |
| parent | d5e31d130808c94f09e51e9afb222c4efa63875c (diff) | |
| download | sqlalchemy-5fc46b192b5005fa6962110a683abf1d296786d8.tar.gz | |
Use FETCH FIRST N ROWS / OFFSET for Oracle LIMIT/OFFSET
Oracle will now use FETCH FIRST N ROWS / OFFSET syntax for limit/offset
support by default for Oracle 12c and above. This syntax was already
available when :meth:`_sql.Select.fetch` were used directly, it's now
implied for :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` as
well.
I'm currently setting this up so that the new syntax renders
in Oracle using POSTCOMPILE binds. I really have no indication
if Oracle's SQL optimizer would be better with params
here, so that it can cache the SQL plan, or if it expects
hardcoded numbers for these. Since we had reports that the previous
ROWNUM thing really needed hardcoded ints, let's guess
for now that hardcoded ints would be preferable. it can be turned
off with a single boolean if users report that they'd prefer
real bound values.
Fixes: #8221
Change-Id: I812ec24ffc947199866947b666d6ec6e6a690f22
Diffstat (limited to 'test/requirements.py')
| -rw-r--r-- | test/requirements.py | 16 |
1 files changed, 14 insertions, 2 deletions
diff --git a/test/requirements.py b/test/requirements.py index 17c11fc5d..5838cf824 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -704,10 +704,22 @@ class DefaultRequirements(SuiteRequirements): E.g. (SELECT id, ...) UNION (SELECT id, ...) ORDER BY id - Fails on SQL Server + Fails on SQL Server and oracle. + + Previously on Oracle, prior to #8221, the ROW_NUMBER subquerying + applied to queries allowed the test at + suite/test_select.py -> + CompoundSelectTest.test_limit_offset_selectable_in_unions + to pass, because of the implicit subquerying thus creating a query + that was more in line with the syntax + illustrated at + https://stackoverflow.com/a/6036814/34549. However, Oracle doesn't + support the above (SELECT ..) UNION (SELECT ..) ORDER BY syntax + at all. So those tests are now not supported w/ Oracle as of + #8221. """ - return fails_if("mssql") + return fails_if(["mssql", "oracle>=12"]) @property def parens_in_union_contained_select_w_limit_offset(self): |