Opened 3 years ago

Closed 3 years ago

#32693 closed Cleanup/optimization (fixed)

case sensitive issue on subquery aliases generated

Reported by: ecogels Owned by: Hasan Ramezani
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords: subquery
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: yes UI/UX: no

Description

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

and 'col%d' https://github.com/django/django/blob/187118203197801c6cb72dc8b06b714b23b6dd3d/django/db/models/sql/compiler.py#L651

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.

Change History (7)

comment:1 by Simon Charette, 3 years ago

Triage Stage: UnreviewedAccepted
Type: BugCleanup/optimization

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

SELECT "subquery"."col" FROM (SELECT 1 AS Col) subquery;

MySQL

SELECT `subquery`.`col` FROM (SELECT 1 AS Col) subquery;

Oracle folds uppercase (as per the SQL standard) though but the quoted usage happens to uppercase the identifier which transforms the query to

Oracle

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 the first instance you pointed at. Do you feel comfortable submitting a Github PR that does that?

comment:2 by Hasan Ramezani, 3 years ago

Has patch: set
Owner: changed from nobody to Hasan Ramezani
Status: newassigned
Last edited 3 years ago by Mariusz Felisiak (previous) (diff)

comment:3 by Hasan Ramezani, 3 years ago

Needs tests: set

comment:4 by ecogels, 3 years ago

Thank you for the follow-up! Sorry I wasn't available earlier.

comment:5 by Hasan Ramezani, 3 years ago

Needs tests: unset

comment:6 by Mariusz Felisiak, 3 years ago

Triage Stage: AcceptedReady for checkin

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

Resolution: fixed
Status: assignedclosed

In 8de4ca74:

Fixed #32693 -- Quoted and lowercased generated column aliases.

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