id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
23061	Oracle SQL compiler adding outer pagination selects causing ORA-00907: missing right parenthesis when used with select_for_update.	michael.miller@…	Shai Berger	"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
}}}"	Bug	closed	Database layer (models, ORM)	1.7-rc-1	Release blocker	fixed	oracle sql compiler ORA-00907		Ready for checkin	1	0	0	0	0	0