#23061: Oracle SQL compiler adding outer pagination selects causing ORA-00907:
missing right parenthesis when used with select_for_update.
-------------------------------------+-------------------------------------
Reporter: michael.miller@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7-rc-1
(models, ORM) | Keywords: oracle sql compiler
Severity: Normal | ORA-00907
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
The Oracle backend SQL compiler is adding outer pagination selects when
calling queryset.get causing parsing failures with select_for_update.
Using Django 1.7 rc 1 on Python 3.4.0 Windows 7. Can also reproduce with
Django 1.7 rc 1 on Python 3.4.1 Centos 6.5.
Easy to reproduce with a simple project and one model. The only change I
made was to add a print(query) to backend.oracle.base.py execute method.
{{{
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.oracle',
'NAME': 'TNSORACLEDB',
'USER': 'user',
'PASSWORD': 'pass',
'HOST': '',
'PORT': '',
},
}
class MyModel(models.Model):
field1 = models.CharField(max_length=100)
}}}
Example of simple get having additional outer selects for pagination.
There is one row in the table with a pk of 1.
{{{
>>> import django
>>> django.setup()
>>> from bar.models import MyModel
>>> y = MyModel.objects.get(pk=1)
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF' TIME_ZONE = 'UTC'
SELECT 1 FROM DUAL WHERE DUMMY LIKE TRANSLATE(:arg0 USING NCHAR_CS) ESCAPE
TRANSLATE('\' USING NCHAR_CS)
SELECT * FROM (SELECT ROWNUM AS "_RN", "_SUB".* FROM (SELECT
"BAR_MYMODEL"."ID", "BAR_MYMODEL"."FIELD1" FROM "BAR_MYMODEL" WHERE
"BAR_MYMODEL"."ID" = :arg0) "_SUB" WHERE ROWNUM <= 21) WHERE "_RN" > 0
}}}
I don't think the two outer selects should be here. These outer selects
create invalid SQL for Oracle when used with select_for_update.
{{{
>>> from django.db import transaction
>>> with transaction.atomic(using='default'):
... y = MyModel.objects.select_for_update(nowait=True).get(pk=1)
...
SELECT * FROM (SELECT ROWNUM AS "_RN", "_SUB".* FROM (SELECT
"BAR_MYMODEL"."ID", "BAR_MYMODEL"."FIELD1" FROM "BAR_MYMODEL" WHERE
"BAR_MYMODEL"."ID" = :arg0 FOR UPDATE NOWAIT) "_SUB" WHERE ROWNUM <= 21)
WHERE "_RN" > 0
Traceback (most recent call last):
File "<input>", line 2, in <module>
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\models\query.py", line 349, in get
num = len(clone)
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\models\query.py", line 122, in __len__
self._fetch_all()
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\models\query.py", line 964, in _fetch_all
self._result_cache = list(self.iterator())
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\models\query.py", line 265, in iterator
for row in compiler.results_iter():
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\models\sql\compiler.py", line 699, in results_iter
for rows in self.execute_sql(MULTI):
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\models\sql\compiler.py", line 785, in execute_sql
cursor.execute(sql, params)
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\backends\utils.py", line 81, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\backends\utils.py", line 65, in execute
return self.cursor.execute(sql, params)
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\utils.py", line 94, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\utils\six.py", line 549, in reraise
raise value.with_traceback(tb)
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\backends\utils.py", line 65, in execute
return self.cursor.execute(sql, params)
File "C:\Python-Environments\Test-Environment\lib\site-
packages\django\db\backends\oracle\base.py", line 898, in execute
return self.cursor.execute(query, self._param_generator(params))
django.db.utils.DatabaseError: ORA-00907: missing right parenthesis
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/23061>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/069.c9dd3f247379b59f531c46b7ffdc9da6%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
