My MySQL doesn't seem to use " as field quotes in the ORDER BY clause?

If I run the folowing query in MySQL 8.0.28:

SELECT 2 as "A A"
UNION ALL
SELECT 1 

ORDER BY "A A"

I get

If I run

SELECT 2 as "A A"
UNION ALL
SELECT 1 

ORDER BY `A A`

I get

like I expect.

Does it mean MySQL doesn't use "double quoted fields" in the ORDER BY, and my ORDER BY "A A" is being interpreted as if it were ORDER BY 'A A' (i.e. order by constant string)? Can this behavior be altered by a setting?

Strings inside double quotes are treated as strings, not as a column alias.

Use backticks when you have special charcters (like spaces) in the column aliases.

In MySQL, double quotes are using to enclose string literals, whereas backticks are used to enclose identifiers such as column names ,table names.

Since the backticks provide an extra level of safety, they are used extensively in program-generated SQL statements, where the identifier names might not be known in advance.

Many other database systems use double quotation marks (") around such special names. For portability, you can enable ANSI_QUOTES mode in MySQL and use double quotation marks instead of backticks to qualify identifier names.

for more detail: backticks

This is an unfortunate ambiguity in MySQL. Column aliases can be defined either with identifier syntax or by string literal syntax. This is described in https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

By default in MySQL, double-quotes are the same as single-quotes by default. They are both string literal delimiters.

This is string literal syntax in MySQL:

SELECT 2 as "A A"
...

This is identifier syntax in MySQL:

SELECT 2 as `A A`
...

But when you reach the ORDER BY, you must use identifiers. If you order by a string literal, then it counts as a constant value. Sorting by a constant value results in a tie for all rows, so the order ends up being whatever order the rows were read in, not the order you wanted.

You can make double-quotes act as identifier delimiters by changing the SQL mode.

mysql> select "abc";
+-----+
| abc |
+-----+
| abc |
+-----+

mysql> set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');
Query OK, 0 rows affected (0.00 sec)

mysql> select "abc";
ERROR 1054 (42S22): Unknown column 'abc' in 'field list'

The error is expected, this is just to demonstrate that abc is treated as an identifier instead of a string after I changed the SQL mode.

You can of course make the change to SQL mode persistent by putting it in your my.cnf file (not the CONCAT() usage but spell out the whole SQL mode string with other appropriate SQL modes):

[mysqld]
sql_mode = ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION