MySQL Bugs: #101997: Didn't report ER_FIELD_IN_ORDER_NOT

Bug #101997	Didn't report ER_FIELD_IN_ORDER_NOT_SELECT in SELECT sub-queries.
Submitted:	15 Dec 2020 11:58	Modified:	16 Dec 2020 3:08
Reporter:	Yan Dong	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL didn't report error ER_FIELD_IN_ORDER_NOT_SELECT for subqueries in SELECT fields.

Example:

create table t (a int, b int);

mysql root@localhost:test> select distinct a from t where b=3 order by b
(3065, "Expression #1 of ORDER BY clause is not in SELECT list, references column 'test.t.b' which is not in SELECT list; this is incompatible with DISTINCT")

mysql root@localhost:test> select (select distinct a from t where b=3 order by b);
+-------------------------------------------------+
| (select distinct a from t where b=3 order by b) |
+-------------------------------------------------+
| <null>                                          |
+-------------------------------------------------+
1 row in set
Time: 0.008s

How to repeat:
drop table if exists t;
create table t (a int, b int);
select distinct a from t where b=3 order by b;
select (select distinct a from t where b=3 order by b);

Suggested fix:
Report error for "select (select distinct a from t where b=3 order by b);".

Hi Mr. Dong,

Thank you for your bug report.

I have managed to reproduce your test case.

I have got the following result:

---------------

ERROR 3065 (HY000) at line 3: Expression #1 of ORDER BY clause is not in SELECT list, references column 'test.t1.b' which is not in SELECT list; this is incompatible with DISTINCT
Level	Code	Message
Error	3065	Expression #1 of ORDER BY clause is not in SELECT list, references column 'test.t1.b' which is not in SELECT list; this is incompatible with DISTINCT
(select distinct a from t1 where b=3 order by b)
NULL

-------------------------------

And as far as I remember, this is a problem with nested queries. Simply, errors like these can not be propagated to the outer query. This is probably a documentation bug, but I am leaving it as a code bug, simply because the result returned from the last SELECT query is the same, even if you remove ORDER BY in the nested query.

Verified as reported.

Actually, this is not quite a bug.

I changed a script and added few other nested queries:

--------------------
drop table if exists t1;
create table t1 (a int, b int);
select distinct a from t1 where b=3 order by b;
show warnings;
select (select distinct a from t1 where b=3 order by b);
show warnings;
select 1 from (select distinct a from t1 where b=3 order by b);
show warnings;
select a from t1 where a in  (select distinct a from t1 where b=3 order by b);
show warnings;
drop table if exists t1;
--------------------

And I have got successfully errors in the other sub-selects:

-----------------------------
ERROR 3065 (HY000) at line 3: Expression #1 of ORDER BY clause is not in SELECT list, references column 'test.t1.b' which is not in SELECT list; this is incompatible with DISTINCT
Level	Code	Message
Error	3065	Expression #1 of ORDER BY clause is not in SELECT list, references column 'test.t1.b' which is not in SELECT list; this is incompatible with DISTINCT
(select distinct a from t1 where b=3 order by b)
NULL
ERROR 1248 (42000) at line 7: Every derived table must have its own alias
Level	Code	Message
Error	1248	Every derived table must have its own alias
-----------------------------

Hence, the only problem is with sub-select in the SELECT list. But, that sub-select can not return the error, by its standard definition.

However, there is no reason why the query would not return a warning, corresponding to the error 1248.

Hence, this is the only reason why this report is verified !!! To get the message with SHOW WARNINGS;

Posted by developer:
 
We don't consider this to be a bug.
When used as a scalar subquery, the result of the subquery will never have a cardinality greater than 1,
so ordering is useless and we remove the ORDER BY from the query.
When used as a table subquery, e.g with an IN predicate, ordering is irrelevant and the ORDER BY clause
is also removed. Thus, we never come to some of the deeper semantic checks, however execution is still
correct because the ordering is irrelevant.

Thanks for your efforts.

But this problem also occurs in FROM clause. 

create table t1 (a int, b int);
select c from (select distinct a as c from t1 order by b) t;
+---+
| c |
+---+

show warnings;
+-------+------+---------+
| Level | Code | Message |
+-------+------+---------+

The example you have tested reported "Every derived table must have its own alias" instead of "Incompatible with DISTINCT".

Hi Mr. Dong,

It is still irrelevant, since the ORDER BY is again optimised away ....

"Every derived table must have its own alias" is due to syntax error because of missing mandatory table alias for derived table. Syntax errors are checked before semantical errors.

"Expression #1 of ORDER BY clause is not in SELECT list" is still reported for the query with correct syntax.