MySQL Bugs: #105699: SQL syntax error is not checked in ORDER BY

Bug #105699	SQL syntax error is not checked in ORDER BY
Submitted:	24 Nov 2021 12:51	Modified:	24 Nov 2021 15:35
Reporter:	Yui Song	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
The SQL Statement works in MySQL 8.0.27: 
mysql> select id from t1 where name='ccc' order by id and id>=4;
+------+
| id   |
+------+
|    3 |
|    4 |
+------+

while the following statement would get an error.

mysql> select id from t1 where name='ccc' order by id desc and id>=4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and id>=4' at line 1

I think both of them should get a SQL syntax error, but the first statement just ignore the predicate 'id>=4' and return the result of statement "select id from t1 where name='ccc' order by id"  

How to repeat:
create table t1 (id int, name char(10));
insert into t1 (id, name) values(1, 'aaa');
insert into t1 (id, name) values(2, 'bbb');
insert into t1 (id, name) values(3, 'ccc');
insert into t1 (id, name) values(4, 'ccc');
select id from t1 where name='ccc' order by id and id>=4;

mysql> select id from t1 where name='ccc' order by id and id>=4;
+------+
| id   |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)

mysql> select id from t1 where name='ccc' order by id desc and id>=4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and id>=4' at line 1

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

Suggested fix:
Both of the two statements should get a SQL syntax error.

Hi Mr. Song,

Thank you for your bug report.

However, this is not a bug.

Let us explain to you as to why. When you write a query like this one:

select id from t1 where name='ccc' order by id and id>=4;

That means that you are ordering the query by the Boolean result. That means that you will get 1 whenever the id is greater than 0 AND id is greater or equal to 4. Hence, sorting will be done only by TRUE or FALSE and not by values of the column `id`.

AND is a keyword that produces a Boolean result, so sorting is done by one criteria only.

On the other hand in a query like this:

select id from t1 where name='ccc' order by id desc;

DESC here denotes the end of the ORDER BY clause. The only token that can follow it is a token, like this one:

select id from t1 where name='ccc' order by id desc, name asc;

Hence, your second query is unacceptable to SQL, since it can not produce BOOLEAN on DESC keyword.

This is basic SQL.

Not a bug.

Well. I wonder what dose the first "id" in the following statement stand for?
"select id from t1 where id and id>=-1;"

create table t1 (id int, name char(10));
insert into t1 (id, name) values(1, 'aaa');
insert into t1 (id, name) values(2, 'bbb');
insert into t1 (id, name) values(3, 'ccc');
insert into t1 (id, name) values(4, 'ccc');
insert into t1 (id, name) values(5, 'ccc');
insert into t1 (id, name) values(-1,'ccc');
insert into t1 (id, name) values(0,'ccc');

mysql> select id from t1 where id and id>=-1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|   -1 |
+------+
6 rows in set (0.00 sec)

BTW, do you have any documents about these boolean things?

And in your explanation that 'That means that you will get 1 whenever the id is greater than 0 AND id is greater or equal to 4.', which's true for the following case.

create table t1 (id int, name char(10));
insert into t1 (id, name) values(1, 'aaa');
insert into t1 (id, name) values(2, 'bbb');
insert into t1 (id, name) values(3, 'ccc');
insert into t1 (id, name) values(4, 'ccc');
insert into t1 (id, name) values(5, 'ccc');
insert into t1 (id, name) values(-1,'ccc');
insert into t1 (id, name) values(0,'ccc'); 
mysql> select id from t1 where name='ccc' order by id and id>=-1;
+------+
| id   |
+------+
|    0 |
|    3 |
|    4 |
|    5 |
|   -1 |
+------+
5 rows in set (0.00 sec)

Sorry for the typo!
And your explanation 'That means that you will get 1 whenever the id is greater than 0 AND id is greater or equal to 4.' is NOT true for the case.

MySQL has a special feature that apply to simple expressions used as predicates in conditions (such as in the WHERE clause). The condition

  where id and id>=-1

is rewritten internally as

  where id <> 0 and id >= -1

We would like to add another comment to the clarification made by our colleagues.

ORDER BY is not filtering rows, since it is only sorting rows.

That means that any Boolean expression, like , for example:

id > 4

can return only TRUE(1) or FALSE(0).

Hence, these conditions are VERY rarely useful in the ORDER BY clause. Their place is either in WHERE or HAVING clause, as our esteeemed colleague has already mentioned.

Not a bug.