Opened 10 years ago

Closed 6 years ago

Last modified 5 years ago

#4186 closed (wontfix)

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

Reported by: khoobks@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: oracle distinct NCLOB TextField
Cc: Ian Kelly, Matt Boersma, Justin Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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

Change History (13)

comment:1 Changed 10 years ago by Simon G. <dev@…>

Summary: Error using "SELECT DISTINCT" with TextFields[boulder-oracle] Error using "SELECT DISTINCT" with TextFields
Triage Stage: UnreviewedAccepted

comment:2 Changed 10 years ago by 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.

comment:3 Changed 10 years ago by 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

  1. result = node.render(context)

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

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

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

  1. return len(self._get_data())

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

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

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

  1. cursor.execute(full_query, params)

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

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

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

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

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

comment:4 Changed 10 years ago by 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?

comment:5 Changed 9 years ago by 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).

comment:6 Changed 9 years ago by Matt Boersma

Version: other branchSVN

comment:7 Changed 9 years ago by Ian Kelly

Cc: Ian Kelly added

comment:8 Changed 8 years ago by sunrise

Owner: changed from nobody to sunrise
Status: newassigned

comment:9 Changed 8 years ago by Karen Tracey

Owner: changed from sunrise to nobody
Status: assignednew

comment:10 Changed 8 years ago by Matt Boersma

Cc: Matt Boersma added

comment:11 Changed 7 years ago by Justin <jluzier@…>

Cc: Justin added

comment:12 Changed 6 years ago by Ian Kelly

Resolution: wontfix
Status: newclosed

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.

comment:13 Changed 5 years ago by 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

Note: See TracTickets for help on using tickets.
Back to Top