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
6404	Wrong table alias in inner join of Oracle db backend	anonymous	nobody	"I use SVN code rev 7020. I found that I can't query any non-primary key on filtering cross table data. The reason is oracle db backend generates wrong SQL statements. Here are my model definitions:
{{{
class HousingApp(models.Model):
    emplid=models.CharField(max_length=9)
    class Meta:
        db_table='""dbobjects"".""ha_housing_app""'

class HousingAssignment(models.Model):
    application=models.ForeignKey(HousingApp, db_column='id_hous_app')
    building_name=models.CharField(max_length=20, null=True, db_column='bldg')
    class Meta:
        db_table='""dbobjects"".""ha_housing_assgnment""'
}}}
When I execute the following line,
{{{
HousingApp.objects.filter(emplid='12345', housingassignment__building_name__exact='CCC')
}}}
Here is the raw SQL statement I got from 'connection'
{{{
>>> from django.db import connection
>>> connection.queries
}}}
The generated SQL query is 
{{{
SELECT ""DBOBJECTS"".""HA_HOUSING_APP"".""ID"", ""DBOBJECTS"".""HA_HOUSING_APP"".""EMPLID""\n 
FROM ""DBOBJECTS"".""HA_HOUSING_APP"" 
INNER JOIN ""DBOBJECTS"".""HA_HOUSING_ASSGNMENT"" ""DBOBJECTS"".""HA_HOUSING_APP""__HOUSINGASSIGNMENT 
ON ""DBOBJECTS"".""HA_HOUSING_APP"".""ID"" = ""DBOBJECTS"".""HA_HOUSING_APP""__HOUSINGASSIGNMENT.""ID_HOUS_APP"" 
WHERE (""DBOBJECTS"".""HA_HOUSING_APP"".""EMPLID"" = 12345 
AND ""DBOBJECTS"".""HA_HOUSING_APP""__HOUSINGASSIGNMENT.""BLDG"" = CCC)
}}}
There are two errors in the SQL statement above
 * the table alias in ''INNER JOIN ""DBOBJECTS"".""HA_HOUSING_ASSGNMENT"" ""DBOBJECTS"".""HA_HOUSING_APP""!__HOUSINGASSIGNMENT'' should be either no alias or '''""!__HOUSINGASSIGNMENT""'''
 * ""EMPLID"" and ""BLDG"" are both !CharField, and the data values should be singled quoted.

Although I observed the error, I don't know how to fix it. Could you please provide a fix on it? Thank you very much!

Gang

FYI,
The error message is 
{{{
Traceback (most recent call last):
  File ""<console>"", line 1, in ?
  File ""/usr/lib/python2.4/site-packages/django/db/models/query.py"", line 108, in __repr__
    return repr(self._get_data())
  File ""/usr/lib/python2.4/site-packages/django/db/models/query.py"", line 483, in _get_data
    self._result_cache = list(self.iterator())
  File ""/usr/lib/python2.4/site-packages/django/db/backends/oracle/base.py"", line 123, in iterator
    cursor.execute(full_query, params)
  File ""/usr/lib/python2.4/site-packages/django/db/backends/util.py"", line 18, in execute
    return self.cursor.execute(sql, params)
  File ""/usr/lib/python2.4/site-packages/django/db/backends/oracle/base.py"", line 499, in execute
    return Database.Cursor.execute(self, query, params)
DatabaseError: ORA-00905: missing keyword
}}}

"		closed	Database layer (models, ORM)	dev		duplicate	oracle		Unreviewed	0	0	0	0	0	0