DBCacheTests.test_cull fails under Oracle

[Aymeric Augustin]

$ python runtests.py --settings=test_settings.oracle cache
Creating test database for alias 'default'...
Creating test user...
Creating test database for alias 'other'...
Creating test user...
...........................E................................................................................................................ssssssssssssssssssssssssssssssss.......
======================================================================
ERROR: test_cull (regressiontests.cache.tests.DBCacheTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/tests/regressiontests/cache/tests.py", line 750, in test_cull
    self.perform_cull_test(50, 29)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/tests/regressiontests/cache/tests.py", line 417, in perform_cull_test
    self.cache.set('cull%d' % i, 'value', 1000)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/core/cache/backends/db.py", line 65, in set
    self._base_set('set', key, value, timeout)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/core/cache/backends/db.py", line 84, in _base_set
    self._cull(db, cursor, now)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/core/cache/backends/db.py", line 138, in _cull
    cursor.execute("SELECT cache_key FROM %s ORDER BY cache_key LIMIT 1 OFFSET %%s" % table, [num / self._cull_frequency])
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/db/backends/oracle/base.py", line 638, in execute
    return self.cursor.execute(query, self._param_generator(params))
DatabaseError: ORA-00933: SQL command not properly ended


----------------------------------------------------------------------
Ran 179 tests in 26.809s

FAILED (errors=1, skipped=32)
Destroying test database for alias 'default'...
Destroying test user...
Destroying test database tables...
Destroying test database for alias 'other'...
Destroying test user...
Destroying test database tables...

[Aymeric Augustin]

We now have a CI server for Oracle, see ​http://ci.django-cms.org/job/Django/database=oracle,python=python2.7/lastCompletedBuild/testReport/regressiontests.cache.tests/DBCacheTests/test_cull/

[Aymeric Augustin]

Oracle doesn't support the combination of LIMIT and OFFSET. This is a very common source of question in forums.

I found this page which provides an analysis of this problem on several RDBMS: ​http://troels.arvin.dk/db/rdbms/#select-limit-offset

It says that the SQL we currently use, ORDER BY ... LIMIT ... OFFSET ..., isn't standard. But it's supported by PostgreSQL, MySQL and SQLite at least (the test passes on those engines). Also, it's the only solution supported by MySQL, and it's simple. As a consequence, I chose to add a special case for Oracle, and a comment to warn about the use of non-standard SQL.

Since the database cache backend isn't an industrial grade concept, I don't believe someone who has the resources to purchase an Oracle license will ever use it. This patch may be a bit hackish but it fixes the test suite with limited changes.

[Aymeric Augustin]

NB: the patch is tested with Oracle 10g. I don't have Oracle 11g available, but the CI server does.

[Ramiro Morales]

In [16635]:

Fixed #16481 -- Adapted one raw SQL query in cull immplementation of the database-based cache backend so it works with Oracle. Thanks Aymeric Augustin for the report and patch.

[Aymeric Augustin]

This was a duplicate of #15580.

[Aymeric Augustin]

In [17805]:

[1.3.x] Fixed #16481 -- Adapted one raw SQL query in cull implementation of the database-based cache backend so it works with Oracle. Backport of r16635 from trunk.