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
4186 "[boulder-oracle] Error using ""SELECT DISTINCT"" with TextFields" khoobks@… nobody "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]: []
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" closed Database layer (models, ORM) master wontfix oracle distinct NCLOB TextField Ian Kelly Matt Boersma Justin Accepted 0 0 0 0