[boulder-oracle] Error using "SELECT DISTINCT" with TextFields

[khoobks@…]

Hi,

I believe I have stumbled across a bug with the boulder-oracle-sprint branch. The issue is related to the use of the "DISTINCT" keyword and use of django TextFields. Behind the scenes the TextField is mapped to the NCLOB data type. The following example will cause the error to occur.

Using the following model,

from django.db import models
from django.contrib.auth.models import *

# Create your models here.
class MyUser(models.Model):
    basenode = models.OneToOneField(User)
    life_story = models.TextField(blank=True)

Enter the following into the shell

In [1]: from django.contrib.auth.models import *

In [2]: from testapp.models import *

In [3]:

In [3]: user = User.objects.create_user(username='Ben',email='ben@isp.com',password='password')

In [4]: user.save()

In [5]:

In [5]: my_user = MyUser(basenode=user)

In [6]: my_user.save()

In [7]:

In [7]: MyUser.objects.filter(basenode__username='Ben')
Out[7]: [<MyUser: MyUser object>]

In [8]: MyUser.objects.filter(basenode__username='Ben').distinct()

The last line will produce a long exception ending with this error

DatabaseError: ORA-00932: inconsistent datatypes: expected - got NCLOB

Further digging into this has shown that the problem can be replicated my entering the query manually into an Oracle console.

The following query will produce the same error

SELECT DISTINCT "TESTAPP_MYUSER"."BASENODE_ID", "TESTAPP_MYUSER"."LIFE_STORY" 
FROM "TESTAPP_MYUSER" INNER JOIN "AUTH_USER" "TESTAPP_MYUSER__BASENODE" ON "TESTAPP_MYUSER"."BASENODE_ID" = "TESTAPP_MYUSER__BASENODE"."ID" 
WHERE ("TESTAPP_MYUSER__BASENODE"."USERNAME" = Ben);

Removing "DISTINCT" or "TESTAPP_MYUSER.LIFE_STORY" from the query will allow the query to execute successfully.

My suspicion is that there is some restriction regarding the use of NCLOB datatypes and the DISTINCT keyword.

Regards
Ben Khoo

[Matt Boersma]

This is a limitation of Oracle. I added some documentation to the OracleBranch page describing the relevant limitations that Oracle places on LOB columns.

[wolf@…]

I am getting this same exact Oracle error whenever I try to view the history of an object on the administration site. This makes the administration site much less useful.

Traceback (most recent call last):
File "/usr/lib/python2.5/site-packages/django/template/init.py" in render_node

741. result = node.render(context)

File "/usr/lib/python2.5/site-packages/django/template/defaulttags.py" in render

211. if (value and not ifnot) or (ifnot and not value):

File "/usr/lib/python2.5/site-packages/django/db/models/query.py" in len

106. return len(self._get_data())

File "/usr/lib/python2.5/site-packages/django/db/models/query.py" in _get_data

476. self._result_cache = list(self.iterator())

File "/usr/lib/python2.5/site-packages/django/db/backends/oracle/base.py" in iterator

303. cursor.execute(full_query, params)

File "/usr/lib/python2.5/site-packages/django/db/backends/util.py" in execute

18. return self.cursor.execute(sql, params)

File "/usr/lib/python2.5/site-packages/django/db/backends/oracle/base.py" in execute

108. return Database.Cursor.execute(self, query, params)

DatabaseError at /admin/polls/poll/1/history/
ORA-00932: inconsistent datatypes: expected - got NCLOB

[wolf@…]

The error I am getting is probably because the poll model has a TextField field.

"LOB columns may not be used in a "SELECT DISTINCT" list. This means that attempting to use the QuerySet.distinct method on a model that includes TextField columns will result in an error. A workaround to this is to keep TextFields out of any models that you foresee performing .distinct queries on, and to include the TextFields in a related model instead."

​http://code.djangoproject.com/wiki/OracleBranch

Is there no possible way to resolve this? We must separate our models into unnatural formats?

[ian.g.kelly@…]

I've submitted a patch for the latter bug (using a TextField in a WHERE clause) in #5087. I still don't think there's any real solution for the original bug (using SELECT DISTINCT with a TextField).

[Erin Kelly]

This ticket is long overdue to be closed wontfix, so I'm doing that now. There seems to be no way to fix the core bug. Moreover, this error is easy to work around in Django 1.1+ by using the QuerySet.defer method to keep the TextField out of the select distinct list.

[Ramiro Morales]

In [16546]:

Fixed #16480 -- Modified test added in r16522 so it doesn't use a query not supported under Oracle (GROUP BY a NCLOB field). Thanks Aymeric for the report. Refs #4186