Opened 2 years ago

Last modified 6 months ago

#20487 assigned Bug

oracle: case mixup causes ORA-00918: column ambiguously defined error

Reported by: Charlie.Tang@… Owned by: shai
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords: oracle ORA-00918
Cc: shai@…, carsten.fuchs@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

a model's fields, which have a common related model, led to the issue.

Here is an example:

class Institution(models.Model):
    name = models.CharField('full name', max_length=50)

class Personel(models.Model):
    institution = models.ForeignKey(Institution)

class Protocol(models.Model):
    Institution = models.ForeignKey('Institution', blank=True, null=True)

class StudyAssignment(models.Model):
    personel = models.ForeignKey(Personel)
    protocol = models.ForeignKey(Protocol)

which generated sql

SELECT * 
FROM 
(   SELECT ROWNUM AS "_RN", "_SUB".* 
    FROM 
    (   SELECT "STUDY_ASSIGNMENT"."ID", "STUDY_ASSIGNMENT"."PERSONEL_ID", "STUDY_ASSIGNMENT"."PROTOCOL_ID", "PERSONEL"."ID" AS Col3
              , "PERSONEL"."INSTITUTION_ID"
              , "INSTITUTION"."ID" AS Col5, "INSTITUTION"."NAME", "PROTOCOL"."ID" AS Col7
              , "PROTOCOL"."INSTITUTION_ID"
              , T5."ID" AS Col9, T5."NAME" AS Col10 
        FROM "STUDY_ASSIGNMENT" 
              INNER JOIN "PERSONEL" ON ("STUDY_ASSIGNMENT"."PERSONEL_ID" = "PERSONEL"."ID") 
              INNER JOIN "INSTITUTION" ON ("PERSONEL"."INSTITUTION_ID" = "INSTITUTION"."ID") 
              INNER JOIN "PROTOCOL" ON ("STUDY_ASSIGNMENT"."PROTOCOL_ID" = "PROTOCOL"."ID") 
            INNER JOIN "INSTITUTION" T5 ON ("PROTOCOL"."INSTITUTION_ID" = T5."ID") 
        ORDER BY "STUDY_ASSIGNMENT"."ID" DESC
    ) "_SUB"
    WHERE ROWNUM <= 21
)
WHERE "_RN" > 0;

To call model StudyAssignment through django admin. Both "PERSONEL"."INSTITUTION_ID" and "PROTOCOL"."INSTITUTION_ID" led to the issue.

Change History (5)

comment:1 Changed 2 years ago by Charlie.Tang@…

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

Fixed the issues through changing the field name Institution of class Protocol to lower case

comment:2 Changed 2 years ago by anonymous

  • Resolution set to fixed
  • Status changed from new to closed

comment:3 Changed 2 years ago by shai

  • Resolution fixed deleted
  • Status changed from closed to new

comment:4 Changed 2 years ago by shai

  • Cc shai@… added
  • Keywords oracle added
  • Owner changed from nobody to shai
  • Status changed from new to assigned
  • Summary changed from ORA-00918: column ambiguously defined error to oracle: case mixup causes ORA-00918: column ambiguously defined error
  • Triage Stage changed from Unreviewed to Accepted

comment:5 Changed 6 months ago by CarstenF

  • Cc carsten.fuchs@… added
Note: See TracTickets for help on using tickets.
Back to Top