Code

Opened 6 years ago

Closed 6 years ago

#6404 closed (duplicate)

Wrong table alias in inner join of Oracle db backend

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: oracle
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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

Attachments (0)

Change History (1)

comment:1 Changed 6 years ago by ikelly

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to duplicate
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.