Opened 8 years ago

Closed 5 years ago

Last modified 4 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: ikelly, mboersma, jluzier 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 8 years ago by Simon G. <dev@…>

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Summary changed from Error using "SELECT DISTINCT" with TextFields to [boulder-oracle] Error using "SELECT DISTINCT" with TextFields
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 8 years ago by bouldersprinters

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 8 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 8 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 8 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 8 years ago by mboersma

  • Version changed from other branch to SVN

comment:7 Changed 7 years ago by ikelly

  • Cc ikelly added

comment:8 Changed 7 years ago by sunrise

  • Owner changed from nobody to sunrise
  • Status changed from new to assigned

comment:9 Changed 7 years ago by kmtracey

  • Owner changed from sunrise to nobody
  • Status changed from assigned to new

comment:10 Changed 6 years ago by mboersma

  • Cc mboersma added

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

  • Cc jluzier added

comment:12 Changed 5 years ago by ikelly

  • Resolution set to wontfix
  • Status changed from new to closed

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 4 years ago by ramiro

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