Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#29630 closed Bug (fixed)

Oracle crash querying multiple tables with the same column name when limit/offset is used.

Reported by: zelfor5436 Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 2.1
Severity: Release blocker Keywords: oracle, admin, exception
Cc: Mariusz Felisiak Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by zelfor5436)

Attempting to log in to the admin interface causes a SQL exception to be raised when using an Oracle database. The exception throw by Oracle is ... ORA-00918: column ambiguously defined.

Database: Oracle 12.1
cx_Oracle version: 6.4.1

Steps to reproduce...

1) Start a project
2) Configure the use of Oracle as a database
3) Execute makemigrations
4) Execute migrate
5) Execute createsuperuser
6) Execute runserver
7) Open localhost:8000/admin in a browser
8) Enter admin username/password and click to log in

At this point, Django will respond with an exception page indicating that Oracle has raised a SQL exception. Rolling back to Django 2.0.7 eliminates the problem and the admin interface behaves as expected.

It looks like the cause is related to base.html used in the admin templates. I'm assuming the problem is either related to some kind of change, since 2.0.7, in how SQL is constructed, or changes/additions in what kind of statements are being emitted by the templates for the admin interface.

Interestingly enough, there's no problem when using a SQLite database.

I'll try to dig into this further on Monday when I return to work. Relatively new to Django, so I'm not as familiar as I'd like to be with what's going on under the hood and I'm not sure where to really begin digging in as this deals with things that come prepackaged with the framework.

Attachments (3)

id ora bug.jpg (140.6 KB ) - added by Rafael Zinezi Araújo 6 years ago.
screenshot of the query highlighting my "theory"
29630.diff (822 bytes ) - added by Mariusz Felisiak 6 years ago.
Test
29630_different_tables.diff (825 bytes ) - added by Mariusz Felisiak 6 years ago.

Download all attachments as: .zip

Change History (25)

comment:1 by zelfor5436, 6 years ago

Description: modified (diff)

comment:2 by zelfor5436, 6 years ago

Description: modified (diff)

comment:3 by Simon Charette, 6 years ago

Resolution: needsinfo
Status: newclosed

Hey there,

Without details about the query that's causing the crash it's almost impossible for volunteers to determine whether or not the issue is in with project or caused by Django 2.1 itself.

If the debugging page doesn't expose the problematic query you can turn query logging to the runserver console with the following LOGGING tweak

LOGGING['loggers']['django.db.backends'] = {
    'level': 'DEBUG',
    'handlers': ['console'],
}

Please re-open if you can provide details proving that Django is at fault.

Last edited 6 years ago by Simon Charette (previous) (diff)

comment:4 by Rafael Zinezi Araújo, 6 years ago

Resolution: needsinfo
Status: closednew

Crossed the same problem. full traceback debug @pastebin

Was able to extract the offending query from traceback and found that when base.html runs it, in this case, it has multiple columns named ID (from 3 tables: Django_Admin_Log, Auth_User and Django_Content_Type).
by parsing and running the query in a sql script the same error occurs.
by aliasing the columns it goes away.

SELECT 		DJANGO_ADMIN_LOG.ID logid, 
		DJANGO_ADMIN_LOG.ACTION_TIME, 
 		DJANGO_ADMIN_LOG.USER_ID, 
 		DJANGO_ADMIN_LOG.CONTENT_TYPE_ID,
 		DJANGO_ADMIN_LOG.OBJECT_ID, 
 		DJANGO_ADMIN_LOG.OBJECT_REPR, 
 		DJANGO_ADMIN_LOG.ACTION_FLAG, 
 		DJANGO_ADMIN_LOG.CHANGE_MESSAGE, 
 		AUTH_USER.ID authid, 
 		AUTH_USER.PASSWORD, 
 		AUTH_USER.LAST_LOGIN, 
 		AUTH_USER.IS_SUPERUSER, 
 		AUTH_USER.USERNAME, 
 		AUTH_USER.FIRST_NAME, 
 		AUTH_USER.LAST_NAME, 
 		AUTH_USER.EMAIL, 
 		AUTH_USER.IS_STAFF, 
 		AUTH_USER.IS_ACTIVE, 
 		AUTH_USER.DATE_JOINED, 
 		DJANGO_CONTENT_TYPE.ID contid, 
 		DJANGO_CONTENT_TYPE.APP_LABEL, 
 		DJANGO_CONTENT_TYPE.MODEL 
FROM DJANGO_ADMIN_LOG 
 	INNER JOIN AUTH_USER 
		ON (DJANGO_ADMIN_LOG.USER_ID = AUTH_USER.ID) 
	LEFT OUTER JOIN DJANGO_CONTENT_TYPE 
		ON (DJANGO_ADMIN_LOG.CONTENT_TYPE_ID = DJANGO_CONTENT_TYPE.ID)
ORDER BY DJANGO_ADMIN_LOG.ACTION_TIME DESC 
FETCH FIRST 10 ROWS ONLY

by Rafael Zinezi Araújo, 6 years ago

Attachment: id ora bug.jpg added

screenshot of the query highlighting my "theory"

comment:5 by Rafael Zinezi Araújo, 6 years ago

Component: Uncategorizedcontrib.admin
Easy pickings: set

in reply to:  5 comment:6 by Rafael Zinezi Araújo, 6 years ago

Replying to rafaelzinezi:
I imagine this should be easy pickings because the 2nd and 3rd columns in question are not really necessary, being duplicates of django_admin_log.user_id and django_admin_log.content_type_id

comment:7 by Simon Charette, 6 years ago

Cc: felixx added
Component: contrib.adminDatabase layer (models, ORM)
Easy pickings: unset
Summary: Admin interface causes ORA-00918: column ambiguously definedOracle crash querying multiple tables with the same column name "ORA-00918: column ambiguously defined"
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

The easy picking flag is used for tickets that would require small, easy, patches. For example a typo in an error message or something that be fixed with a search and replace through the code base.

Any ORM bug does't fit into this category. The fact it only manifests itself on Oracle most of current contributors have limited knowledge of also makes it harder to resolve.

I've switched the component to the database layer because while it manifests itself in the admin in your case such an ambigously defined query can be trivially constructed by using select_related('fk')[0:1] or values('id', 'fk__id')[0:1].

@felixx are you aware of any changes that might caused such breakage? At this point I suspect some obscure flag Oracle flag might be interfering with native limit/offset support added in #28670 since the test suite passes against Oracle 12 on CI.

Last edited 6 years ago by Simon Charette (previous) (diff)

comment:8 by Simon Charette, 6 years ago

Summary: Oracle crash querying multiple tables with the same column name "ORA-00918: column ambiguously defined"Oracle crash querying multiple tables with the same column name when limit/offset is used.

comment:9 by Simon Charette, 6 years ago

Severity: NormalRelease blocker

From https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6

Restrictions on the row_limiting_clause

This clause is subject to the following restrictions:

If the select list contains columns with identical names and you specify the row_limiting_clause, then an ORA-00918 error occurs. This error occurs whether the identically named columns are in the same table or in different tables. You can work around this issue by specifying unique column aliases for the identically named columns.

It looks like we'll have to either alias these columns as the reporter did or use the old limiting mechanism on column name collision. This is something that should be backported to 2.1 so I'll turn it into a release blocker.

comment:10 by Mariusz Felisiak, 6 years ago

Cc: Mariusz Felisiak added; felixx removed

I missed this restriction 😞 Specifying unique aliases sounds better to me, I will try to fix it in this week. Thanks for investigating this issue.

by Mariusz Felisiak, 6 years ago

Attachment: 29630.diff added

Test

comment:11 by Mariusz Felisiak, 6 years ago

I cannot reproduce this issue on Oracle with attached tests 🤔. Am I missing sth?

by Mariusz Felisiak, 6 years ago

Attachment: 29630_different_tables.diff added

comment:12 by Mariusz Felisiak, 6 years ago

It seems that is not easy to reproduce this issue (see AskTom). It works in both cases i.e. when identically named columns are in the same table or in different tables, e.g.

SELECT
    "QUERIES_TAG"."NAME",
    "QUERIES_NAMEDCATEGORY"."NAME"
FROM
     "QUERIES_TAG"
LEFT OUTER JOIN "QUERIES_NAMEDCATEGORY" ON
     ("QUERIES_TAG"."CATEGORY_ID" = "QUERIES_NAMEDCATEGORY"."DUMBCATEGORY_PTR_ID")
ORDER BY "QUERIES_TAG"."NAME" ASC
FETCH FIRST 2 ROWS ONLY

in reply to:  12 comment:13 by Rafael Zinezi Araújo, 6 years ago

Replying to felixxm:
I am @ Oracle 12.1.0.2.0

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE	12.1.0.2.0	Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Did as the linked post and got same ORA-00918 error.

SELECT t.id, u.id FROM t, u FETCH FIRST 10 ROWS ONLY;

It seems that is not easy to reproduce this issue (see AskTom). It works in both cases i.e. when identically named columns are in the same table or in different tables

Last edited 6 years ago by Rafael Zinezi Araújo (previous) (diff)

comment:14 by Jani Tiainen, 6 years ago

I could reproduce issue with Oracle 12.1.0.2-se2

I couldn't reproduce issue with Oracle 12.2.0.1-se2

It seems that issue has been fixed in Oracle 12.2

Last edited 6 years ago by Jani Tiainen (previous) (diff)

comment:15 by Simon Charette, 6 years ago

Since that would require quite invasive changes fixing do you believe it's worth fixing for 12.1? Maybe we could simply disable the feature on 12.1?

comment:16 by Jani Tiainen, 6 years ago

According to Oracle docs 12.1 (se2) premier support ended on July 2018, and extended one ends on july 2021.

To ensure database security and minimize Oracle support costs, organizations should plan to upgrade 11.2.0.4 and 12.1.0.2 databases in 2018 and move to 12.2 at that time. All new databases should be 12.1.0.2 and look to begin production use of 12.2 in late 2017 or with the release of 12.2.0.2 in early 2018.

One option could be to use old rownum based OFFSET..LIMIT for Oracle 12.1 if that wouldn't require much of the changes to backend rather than trying to fix unique column names.

comment:17 by Jani Tiainen, 6 years ago

Owner: changed from nobody to Jani Tiainen
Status: newassigned

comment:18 by Tim Graham, 6 years ago

Based on SupportedDatabaseVersions, I think we would drop Oracle 12.1 support following Django 2.2 so there's still time for that fix.

comment:19 by Mariusz Felisiak, 6 years ago

Has patch: set
Owner: changed from Jani Tiainen to Mariusz Felisiak

comment:20 by Mariusz Felisiak, 6 years ago

@zelfor5436 Can you confirm that this patch fixed the reported issue?

comment:21 by GitHub <noreply@…>, 6 years ago

Resolution: fixed
Status: assignedclosed

In 024abe5:

Fixed #29630 -- Fixed crash of sliced queries with multiple columns with the same name on Oracle 12.1.

Regression in 0899d583bdb140910698d00d17f5f1abc8774b07.

Thanks Tim Graham for the review and Jani Tiainen for help.

comment:22 by Mariusz Felisiak <felisiak.mariusz@…>, 6 years ago

In 5aeced6d:

[2.1.x] Fixed #29630 -- Fixed crash of sliced queries with multiple columns with the same name on Oracle 12.1.

Regression in 0899d583bdb140910698d00d17f5f1abc8774b07.

Thanks Tim Graham for the review and Jani Tiainen for help.
Backport of 024abe5b82d95ee60cb18a77ebf841ad715467fa from master

Note: See TracTickets for help on using tickets.
Back to Top