Wrong table alias in inner join of Oracle db backend

[anonymous]

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

[Erin Kelly]

I'm closing this as a duplicate of #6148. The problem here is caused by setting db_table = '"dbobjects"."ha_housing_app"'. It really should just be an unquoted table name, as anything else is not understood by Django. Including a schema will work to an extent, but as you've found, it will fall apart as soon as you try to do anything that involves munging the table name. The current workaround for this in Oracle is to create private synonyms inside the Django schema that reference the desired tables from other schemas; then the db_table option is just the name of the synonym.

The second error pointed out in the description is not a real issue. Those values are actually passed to the database via bind parameters. They just substituted into the connection.queries SQL as a debugging convenience.