MySQL Bugs: #25758: quoted string in ORDER BY causes GROUP BY

Bug #25758	quoted string in ORDER BY causes GROUP BY
Submitted:	22 Jan 2007 18:01	Modified:	14 Feb 2007 19:09
Reporter:	Matthew Lord	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.25+, 4.1.19+	OS:	Any (all)
Assigned to:		CPU Architecture:	Any
Tags:	bfsm_2007_02_01, bfsm_2007_02_15, GROUP BY, order by

Description:
When using an alias that requires quoting such as "Column 2" in an ORDER BY it silently fails and also oddly seems to do a group by on a different column.

How to repeat:
mysqladmin create offline
mysql offline < ob.dmp.txt
mysql offline < sort_sql_calc.sql

Suggested fix:
We should either produce an error or fulfill the query.

A workaround is to use backticks, order by `Column 1`;

The actual query from sort_sql_calc.sql is using single quotes, not double quotes.
But neither single quotes nor double quotes can refer to identifiers - they always refer to string constants, in all contexts:

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 (a) values (3), (1), (2), (6), (4), (5);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select "a" from t1;
+---+
| a |
+---+
| a | 
| a | 
| a | 
| a | 
| a | 
| a | 
+---+
6 rows in set (0.00 sec)

mysql> select 'a' from t1;
+---+
| a |
+---+
| a | 
| a | 
| a | 
| a | 
| a | 
| a | 
+---+
6 rows in set (0.00 sec)

mysql> select `a` from t1;
+------+
| a    |
+------+
|    3 | 
|    1 | 
|    2 | 
|    6 | 
|    4 | 
|    5 | 
+------+
6 rows in set (0.00 sec)

In ORDER BY clause one can order by a column name, an alias (both have to be well formed identifiers), an expression, a numeric position from the select list. A quoted string is parsed as expression, and thus no order by is performed. This can be easily seen in EXPLAIN:
kostja@bodhi:~> mysql test -uroot < sort_sql_calc.sql 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	B	ALL	PRIMARY	NULL	NULL	NULL	10	Using where
1	SIMPLE	A	ref	processTypeDate	processTypeDate	1	test.B.type	1

(ORDER BY is optimized away).

See http://dev.mysql.com/doc/refman/5.0/en/select.html.
http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html

Oracle behaves similarly, except that there double quotes can be used for identifiers and trigger case-sensitive matching:

SQL> select a from t1 order by 1;

	 A
----------
	 1
	 2
	 3
	 4
	 5
	 6

6 rows selected.

Elapsed: 00:00:00.01
SQL> select a from t1 order by 'a';

	 A
----------
	 2
	 3
	 1
	 4
	 6
	 5

6 rows selected.

Elapsed: 00:00:00.01
SQL> select a from t1 order by 'A';    

	 A
----------
	 2
	 3
	 1
	 4
	 6
	 5

6 rows selected.

Elapsed: 00:00:00.01
SQL> select a from t1 order by "A";

	 A
----------
	 1
	 2
	 3
	 4
	 5
	 6

6 rows selected.

Elapsed: 00:00:00.01
SQL> 

Please feel free to request additional clarification of this behavior in the documentation.

Hi Konstantin,

This may just need to be very clearly documented but...

Unfortunately the query did work prior to 5.0.25, it was a change in a GA 
version which is not good.  Was the bug that it _did_ work before?

What is the result of this query in oracle and mysql (better fits this bug):

select a as "foo bar" from t1 order by "foo bar";

Best Regards

Another thing, look at the attached query.

Why does the order by "" cause the results to be grouped by description?

I would expect the order by to just fail and return the results as it would
w/o the order by.  Can this be explained?

Hi Konstantin,

The customer also noted that this was changed in 4.1 after 4.1.19 so both GA
versions had this change in  behavior.

Hi Matt,
Please ask the customer whether he was running in sql_mode='ANSI_QUOTES'
ANSI_QUOTES enables "" quotes as valid quotes for identifiers.
If this is not the case, what really happened needs to be investigated. I do not have 5.0.25 version at hand, how shall we proceed with this then?

I did not get your question about GROUP BY. If there is a GROUP BY, what columns are used for grouping?
What does explain show?
Will give you an answer in a sec about the example query.

ysql> select a as "foo bar" from t1 order by "foo bar";
+---------+
| foo bar |
+---------+
|       3 | 
|       1 | 
|       2 | 
|       6 | 
|       4 | 
|       5 | 
+---------+
6 rows in set (0.04 sec)
mysql> set sql_mode=ANSI_QUOTES;
mysql> select a as "foo bar" from t1 order by "foo bar";
+---------+
| foo bar |
+---------+
|       1 | 
|       2 | 
|       3 | 
|       4 | 
|       5 | 
|       6 | 
+---------+
6 rows in set (0.00 sec)

Oracle:
SQL> select a as "foo bar" from t1 order by "foo bar";

   foo bar
----------
	 1
	 2
	 3
	 4
	 5
	 6

6 rows selected.

Elapsed: 00:00:00.01
SQL> 

So, Oracle respects ANSI standard quotes for identifiers, MySQL doesn't do that in the default mode and treats strings in double quotes as string constants.

The change in behavior between two GA releases needs to be verified.

One can say that any bugfix is a "change in behaviour" - of course it used to crash, now it doesn't :) Still, we fix bugs in production releases too.

This particular change was a fix for a bug#14019