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]: [<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"		closed	Database layer (models, ORM)	dev		wontfix	oracle distinct NCLOB TextField	Erin Kelly Matt Boersma Justin	Accepted	0	0	0	0	0	0