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

[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.

[Simon Charette]

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.

[Rafael Zinezi Araújo]

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

[Rafael Zinezi Araújo]

screenshot of the query highlighting my "theory"

[Rafael Zinezi Araújo]

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

[Simon Charette]

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.

[Simon Charette]

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.

[Mariusz Felisiak]

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.

[Mariusz Felisiak]

Test

[Mariusz Felisiak]

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

[Mariusz Felisiak]

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

[Rafael Zinezi Araújo]

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, 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

[Jani Tiainen]

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

[Simon Charette]

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?

[Jani Tiainen]

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.

[Tim Graham]

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

[Mariusz Felisiak]

​PR

[Mariusz Felisiak]

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

[GitHub <noreply@…>]

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

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