[Django] #37365: models.E007 misses duplicate columns when one db_column is explicitly quoted

[Django]

#37365: models.E007 misses duplicate columns when one db_column is explicitly
quoted
-------------------------------------+-------------------------------------
Reporter: Matthew Schinckel | Type: Bug
Status: new | Component: Core
| (System checks)
Version: 6.1 | Severity: Normal
Keywords: db_column quoting | Triage Stage:
system-checks | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
The models.E007 system check can miss duplicate database columns when one
db_column is explicitly quoted and another is not. The strings differ, but
the configured backend can render them as the same SQL identifier.

This issue appears to be mostly the same across different backends.

== Reproduction ==

This test uses the configured backend's own identifier quoting. It does
not query the database.

{{{#!python
from django.db import connection, models
from django.test import SimpleTestCase
from django.test.utils import isolate_apps


class QuotedColumnTests(SimpleTestCase):
@isolate_apps()
def test_quoted_column_collision(self) -> None:
quoted_name = connection.ops.quote_name("id")

class Example(models.Model):
key = models.IntegerField(primary_key=True,
db_column=quoted_name)
score = models.IntegerField(db_column="id")

class Meta:
app_label = "example"

self.assertIn("models.E007", {error.id for error in
Example.check()})
}}}

Actual result: Example.check() returns [], so the assertion fails.

Expected result: models.E007, because both fields map to the same physical
column. Using the unquoted string "id" for both fields correctly produces
that error.

== Backend examples ==

The equivalent spellings depend on the backend:

* SQLite: {{{id}}} and {{{"id"}}} both render as {{{"id"}}}.
[https://github.com/django/django/blob/6.1.1/django/db/backends/sqlite3/operations.py
Source]
* MySQL: {{{id}}} and {{{`id`}}} both render as {{{`id`}}}. The quoted
spelling must use backticks; double quotes would represent a different
identifier.
[https://github.com/django/django/blob/6.1.1/django/db/backends/mysql/operations.py
Source]
* Oracle: {{{id}}} and {{{"id"}}} both render as {{{"ID"}}}, because
Django's Oracle quote_name() uppercases its result.
[https://github.com/django/django/blob/6.1.1/django/db/backends/oracle/operations.py
Source]
* PostgreSQL: {{{id}}} and {{{"id"}}} both render as {{{"id"}}}.
[https://github.com/django/django/blob/6.1.1/django/db/backends/postgresql/operations.py
Source]

== Cause ==

[https://github.com/django/django/blob/6.1.1/django/db/models/base.py
Model._check_column_name_clashes()] compares the original field.column
strings without accounting for backend identifier handling. It accepts
declarations that would generate duplicate column names.

The desired behaviour is to recognise equivalent identifiers and report
the collision, rather than permit duplicate columns. Any fix should
account for backend-specific rules rather than simply stripping double
quotes.

== Versions checked ==

Runtime reproduction: Django 5.2.17, Python 3.11.6, with SQLite and
PostgreSQL backend configurations. The checks do not query a database.

6.1.1 appears to have the same issue based on inspection of its released
source. The shared check and the four backends' quote_name()
implementations are unchanged from 5.2.17. MySQL and Oracle findings are
source verification only; no integration tests were run against those
databases.
--
Ticket URL: <https://code.djangoproject.com/ticket/37365>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#37365: models.E007 misses duplicate columns when one db_column is explicitly
quoted
-------------------------------------+-------------------------------------

Reporter: Matthew Schinckel | Owner: Md.
| Saikat Islam
Type: Bug | Status: assigned
Component: Core (System | Version: 6.1
checks) |
Severity: Normal | Resolution:
Keywords: db_column quoting | Triage Stage: Accepted
system-checks |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Md. Saikat Islam):

* owner: (none) => Md. Saikat Islam
* stage: Unreviewed => Accepted
* status: new => assigned

Comment:

Thanks for reporting this issue. I was able to reproduce it after
inspection.

After digging into the code I found the reason. When we normally assign a
string value as db_column, django saves it as it is (`"id"`), but when we
use `connections.quoate_name` djangos makes it quoated `'"id"'`. As a
result in the `_check_column_name_clashes` (`django/db/models/base.py`)
method when we compare using
{{{
...
column_name = f.column

# Ensure the column name is not already in use.
if column_name and column_name in used_column_names:
errors.append(...)
....
}}}
This could be solved using `column_name =
connection.ops.quote_name(f.column)` so that we can treat `"id"` and
`'"id"'` as same in duplicacy check.

but this is not good when querying data using sql if we assign a db_column
name like "multi word column name". This should be quoated.
So another solution i found is to update the field db_column name in
__init__ as quated.
in `django/db/models/fields/__init__.py`
{{{
self.db_column = db_column if not db_column else
connection.ops.quote_name(db_column)
}}}
since `quote_name` follows quoting once rule. So this should be good to
go. I will be following second solution since it will update the
db_column name.
--
Ticket URL: <https://code.djangoproject.com/ticket/37365#comment:1>

[Django]

#37365: models.E007 misses duplicate columns when one db_column is explicitly
quoted
-------------------------------------+-------------------------------------
Reporter: Matthew Schinckel | Owner: Md.
| Saikat Islam
Type: Bug | Status: assigned
Component: Core (System | Version: 6.1
checks) |
Severity: Normal | Resolution:
Keywords: db_column quoting | Triage Stage: Accepted
system-checks |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Md. Saikat Islam):

On a second thought.

db_column = "id" --------------------------------------------------->
gives you database column name `id`
db_column = connection.ops.quote_name("id") ---> gives you database
column name `"id"`

So they are not duplicate and should not raise duplicate column name
error.

I need suggestion here.
--
Ticket URL: <https://code.djangoproject.com/ticket/37365#comment:2>

[Django]

#37365: models.E007 misses duplicate columns when one db_column is explicitly
quoted
-------------------------------------+-------------------------------------
Reporter: Matthew Schinckel | Owner: Md.
| Saikat Islam
Type: Bug | Status: assigned
Component: Core (System | Version: 6.1
checks) |
Severity: Normal | Resolution:
Keywords: db_column quoting | Triage Stage: Accepted
system-checks |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Matthew Schinckel):

Yeah, I was going to work through a solution but ran out of day.
--
Ticket URL: <https://code.djangoproject.com/ticket/37365#comment:3>

[Django]

#37365: models.E007 misses duplicate columns when one db_column is explicitly
quoted
-------------------------------------+-------------------------------------
Reporter: Matthew Schinckel | Owner: Md.
| Saikat Islam
Type: Bug | Status: assigned
Component: Core (System | Version: 6.1
checks) |
Severity: Normal | Resolution:
Keywords: db_column quoting | Triage Stage: Accepted
system-checks |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Md. Saikat Islam):

I am already done with the solution. I was going to open a PR. I’d
appreciate it if you could share your thoughts on my reasoning.
--
Ticket URL: <https://code.djangoproject.com/ticket/37365#comment:4>

[Django]

#37365: models.E007 misses duplicate columns when one db_column is explicitly
quoted
-------------------------------------+-------------------------------------
Reporter: Matthew Schinckel | Owner: Md.
| Saikat Islam
Type: Bug | Status: assigned
Component: Core (System | Version: 6.1
checks) |
Severity: Normal | Resolution:
Keywords: db_column quoting | Triage Stage: Accepted
system-checks |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Md. Saikat Islam):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/37365#comment:5>

[Django]

#37365: models.E007 misses duplicate columns when one db_column is explicitly
quoted
-------------------------------------+-------------------------------------
Reporter: Matthew Schinckel | Owner: Md.
| Saikat Islam
Type: Bug | Status: assigned
Component: Core (System | Version: 6.1
checks) |
Severity: Normal | Resolution:
Keywords: db_column quoting | Triage Stage: Accepted
system-checks |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Md. Saikat Islam):

https://github.com/django/django/pull/22015
--
Ticket URL: <https://code.djangoproject.com/ticket/37365#comment:6>