case sensitive issue on subquery aliases generated

[ecogels]

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.

[Simon Charette]

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?

[ecogels]

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

[Mariusz Felisiak <felisiak.mariusz@…>]

In 8de4ca74:

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