#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 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/069.c9dd3f247379b59f531c46b7ffdc9da6%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
