#23061: Oracle SQL compiler adding outer pagination selects causing ORA-00907:
missing right parenthesis when used with select_for_update.
-------------------------------------+-------------------------------------
Reporter: michael.miller@… | Owner: shai
Type: Bug | Status: assigned
Component: Database layer | Version: 1.7-rc-1
(models, ORM) | Resolution:
Severity: Release blocker | Triage Stage: Accepted
Keywords: oracle sql compiler | Needs documentation: 0
ORA-00907 | Patch needs improvement: 0
Has patch: 0 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by shai):
Hi all,
The issue is very hard to solve. The change that introduced this is
[da79ccca], a change to the `get()` function that made it return only up
to 20 records (rather than all of them) when the criteria are not specific
enough to select a single record. This was done, essentially, by adding
`[:20]` to the queryset being used for `get()`.
The issue is that, as far as I can tell, select-for-update and
limit/offset cannot be done together on Oracle without some deep changes
in the ORM; perhaps, generally, not even then.
Currently, On Oracle, the limit/offset is done by wrapping the original
query in an external query that selects all the original query fields and
adds a "row-number" fields, which can then be filtered on. `FOR UPDATE`
cannot be added in this setup -- current code adds it to the inner query,
which results in the reported error; I tried to put it on the external
one, and that results in "ORA-02014: cannot select FOR UPDATE from view
with DISTINCT, GROUP BY, etc."
Apparently, the ways to achieve limit/offset with select-for-update
involve either putting the row-number much deeper in the query (using a
windowing function, which requires a whole new treatment of the order-by
clause) or limiting the fetch rather than the select (which is generally
inappropriate, as the lock would still apply to the whole set, but is good
enough for the case of `get()`). See
http://stackoverflow.com/questions/6337126/oracle-for-update-select-
first-10-rows. Each of the answers for that question also has problems in
combination with other ORM features, like `select_related()`.
We could solve the current problem by just disabling the `[:20]` feature
on Oracle, at least when selecting for update (after all, this feature is
mostly useful in debugging, and so is secondary to making correct code
work). This will not solve the more general limit/offset issue, but may be
good enough for many users. I propose that we take this route for now, if
it is good enough for Michael.
PR forthcoming,
Shai.
--
Ticket URL: <https://code.djangoproject.com/ticket/23061#comment:7>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/084.7ea3b878267db32f0a46eb5618b69ace%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
