[Django] #32693: case sensitive issue on subquery aliases generated
Django
-------------------------------------+-------------------------------------
Reporter: ecogels | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords: subquery
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
When generating aliases for subqueries, django uses 2 different cases:
'Col%d'
https://github.com/django/django/blob/187118203197801c6cb72dc8b06b714b23b6dd3d/django/db/models/sql/compiler.py#L557
Here is a concrete example, using --v 2 --debug-sql
queries.tests.Queries6Tests.test_distinct_ordered_sliced_subquery_aggregation.
SELECT COUNT(*) FROM (SELECT "subquery".**"col1"**, "subquery"."col2",
"subquery"."col3", "subquery"."col4" FROM (SELECT DISTINCT
"queries_tag"."id" AS **Col1**, "queries_tag"."name" AS Col2,
"queries_tag"."parent_id" AS Col3, "queries_tag"."category_id" AS Col4,
"queries_namedcategory"."name" AS Col5 FROM "queries_tag" LEFT OUTER JOIN
"queries_namedcategory" ON ("queries_tag"."category_id" =
"queries_namedcategory"."dumbcategory_ptr_id") ORDER BY
"queries_namedcategory"."name" ASC LIMIT 3) subquery) subquery;
This would fail on a database with case sensitive column names.
It will be trivial to fix if deemed necessary.
--
Ticket URL: <https://code.djangoproject.com/ticket/32693>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: ecogels | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: subquery | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Bug => Cleanup/optimization
* stage: Unreviewed => Accepted
Comment:
It's not an issue in practice because SQLite, MySQL, and PostgreSQL all
fold ambiguous aliases to lower case, try the following in their
respective shell
SQlite and Postgres
{{{#!sql
SELECT "subquery"."col" FROM (SELECT 1 AS Col) subquery;
}}}
MySQL
{{{#!sql
SELECT `subquery`.`col` FROM (SELECT 1 AS Col) subquery;
}}}
Oracle folds uppercase (as per the SQL standard) though but the
[https://github.com/django/django/blob/187118203197801c6cb72dc8b06b714b23b6dd3d/django/db/models/sql/compiler.py#L651-L655
quoted usage] happens to
[https://github.com/django/django/blob/187118203197801c6cb72dc8b06b714b23b6dd3d/django/db/backends/oracle/operations.py#L331-L342
uppercase the identifier] which transforms the query to
Oracle
{{{#!sql
SELECT "SUBQUERY"."COL" FROM (SELECT 1 AS Col) subquery;
}}}
Which also works.
I think we should still perform a cleanup here for the sake of consistency
with two adjustments:
1. Always generate `col` alias in lowercase
2. Make sure to always call `connection.ops.quote_name` on the generated
alias
Both of these changes should be made to
[https://github.com/django/django/blob/187118203197801c6cb72dc8b06b714b23b6dd3d/django/db/models/sql/compiler.py#L557
the first instance you pointed at]. Do you feel comfortable submitting a
Github PR that does that?
--
Ticket URL: <https://code.djangoproject.com/ticket/32693#comment:1>
Django
-------------------------------------+-------------------------------------
Type: | Ramezani
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: subquery | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Hasan Ramezani
* status: new => assigned
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/32693#comment:2>
Django
-------------------------------------+-------------------------------------
Type: | Ramezani
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: subquery | Triage Stage: Accepted
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/32693#comment:3>
Django
-------------------------------------+-------------------------------------
Type: | Ramezani
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: subquery | Triage Stage: Accepted
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by ecogels):
Thank you for the follow-up! Sorry I wasn't available earlier.
--
Ticket URL: <https://code.djangoproject.com/ticket/32693#comment:4>
Django
-------------------------------------+-------------------------------------
Type: | Ramezani
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: subquery | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_tests: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/32693#comment:5>
Django
-------------------------------------+-------------------------------------
Type: | Ramezani
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/32693#comment:6>
Django
-------------------------------------+-------------------------------------
Type: | Ramezani
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: subquery | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"8de4ca74ba49b3f97a252e2b9d385cb2e70c442c" 8de4ca74]:
{{{
#!CommitTicketReference repository=""
revision="8de4ca74ba49b3f97a252e2b9d385cb2e70c442c"
Fixed #32693 -- Quoted and lowercased generated column aliases.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32693#comment:7>