Code

Opened 6 years ago

Closed 6 years ago

#7057 closed (fixed)

ORA-00918: column ambiguously defined error

Reported by: ikelly Owned by: nobody
Component: Database layer (models, ORM) Version: queryset-refactor
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Column aliases aren't being generated for columns with conflicting names. This causes problems in Oracle when doing pagination, which effectively wraps the query in "SELECT * FROM (%s)". Here's an example, where the test_species.id and test_genus.id columns conflict, as well as the test_species.name and test_genus.name columns:

In [5]: qs = Species.objects.all().select_related(depth=1)[:5]

In [6]: qs.query.as_sql()
Out[6]:
('SELECT * FROM (SELECT (ROW_NUMBER() OVER (ORDER BY "TEST_SPECIES"."ID" )) AS rn, "TEST_SPECIES"."ID", "TEST_SPECIES"."NAME", "TEST_SPECIES"."GENUS_ID", "TEST_GENUS"."ID", "TEST_GENUS"."NAME", "TEST_GENUS"."FAMILY_ID", "TEST_SPECIES"."ID", "TEST_SPECIES"."NAME", "TEST_SPECIES"."GENUS_ID", "TEST_GENUS"."ID", "TEST_GENUS"."NAME", "TEST_GENUS"."FAMILY_ID" FROM "TEST_SPECIES" INNER JOIN "TEST_GENUS" ON ("TEST_SPECIES"."GENUS_ID" = "TEST_GENUS"."ID")) WHERE rn > 0 AND rn <= 5',
 ())

In [7]: list(qs)

...

<class 'cx_Oracle.DatabaseError'>: ORA-00918: column ambiguously defined

Attachments (1)

7057.diff (1.1 KB) - added by ikelly 6 years ago.

Download all attachments as: .zip

Change History (8)

comment:1 Changed 6 years ago by mtredinnick

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

Aliasing the column names in the Oracle backend is one solution, but it means duplicating a lot of the get_columns() method to do so, or adding an extra do-nothing function call for the common case so that the Oracle backend can override it. Neither option immediately fills me with joy.

Is there anything particularly bad about explicitly writing all the table_name.column_name strings again in the outer select, rather than using select *? We have pretty easy access to those at the point the outer query is created, so it's probably only a couple of lines of code and even I should be able to manage to do that without screwing up.

Happy to be guided by the experts here.

comment:2 Changed 6 years ago by ikelly

Unfortunately, I don't think that will work. The table names are no longer accessible in the outer select list. If the column names are specified, then they have to be qualified using the name of the subquery instead. So there's no way at that level to distinguish between the columns that were originally called species.name and genus.name.

comment:3 Changed 6 years ago by mtredinnick

Aah, good point.

So Oracle just hates me, then. We need to allow column aliases in some cases. I'll do that today.

comment:4 Changed 6 years ago by mtredinnick

  • Keywords qs-rf removed

I've attempted to fix this in [7457] (the automatic commit message reference didn't come through to here, though). It looks like it's doing the right thing.

If it could be tested on a real Oracle server, it would be appreciated.

Changed 6 years ago by ikelly

comment:5 Changed 6 years ago by ikelly

It works with the patch I just uploaded. Without that, there are still some conflicts since get_default_columns() doesn't update the col_aliases set.

comment:6 Changed 6 years ago by mtredinnick

Thanks for the patch. Can't use it unmodified, since the partition() method on strings was only introduced in Python 2.5. But I can work around that fairly easily.

comment:7 Changed 6 years ago by mtredinnick

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

(In [7466]) queryset-refactor: Added a few modifications to the select column aliases from
[7457], based on a patch from Ian Kelly. Fixed #7057.

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.