MySQL Bugs: #115587: MySQL fails to resolve order by a const field in a derived table

Bug #115587	MySQL fails to resolve order by a const field in a derived table
Submitted:	15 Jul 2024 9:32	Modified:	25 Jul 2024 1:24
Reporter:	Diancheng Wang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	ALL	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL fails to resolve order by field in a derived table, but it is ok if it is in  parent query block.

How to repeat:
create table t1(a int);
select subq_0.c0 from (select a as c0, 4 as c1 from t1 order by c1) as subq_0;

ERROR 1054 (42S22): Unknown column 'c1' in 'order clause'

but following queries are ok:
select a as c0, 4 as c1 from t1 order by c1;

select subq_0.c0 from (select a as c0, 4 as c1 from t1 order by c0) as subq_0;

Hi Mr. Wang,

Thank you for your bug report.

However, this is not a bug.

In the expression:

select a as c0, 4 as c1 from t1

"from t1" is necessary and is related ONLY to a as c0. 

It is not related to c1, which is just a constant !!!!!

Not a bug.

Hi Mr. Wang,

One more comment from us .....

Your report is not a bug for one more reason. Ordering on a constant value is of no use, so this report has no real impact.

Thank you.

But why this query is OK:

select a as c0, 4 as c1 from t1 order by c1;

I think it should report an error too just like it is in derived table if it is not a bug.

Hi,

We already explained why is FROM necessary .......

Also, ORDER BY will not return an error, but will simply ignore the constant for ordering.

Thanks for your reply.

> Also, ORDER BY will not return an error, but will simply ignore the constant for ordering.

This query is generated by my fuzzing test tool, This issue leads to higher error rate.

BTW: I tested it in postgresql, the query is OK, too.

postgres=# explain  select subq_0.c0 from (select a as c0, 4 as c1 from t1 order by c1) as subq_0;
                          QUERY PLAN                           
---------------------------------------------------------------
 Subquery Scan on subq_0  (cost=0.00..61.00 rows=2550 width=4)
   ->  Seq Scan on t1  (cost=0.00..35.50 rows=2550 width=8)
(2 rows)

Hi,

This is a forum only for MySQL, as released by Oracle Inc.

Hi,

I found this query is also OK:

select subq_0.c0 as c1 from (select ref_0.a as c0, 4 + 1 as c1 from  test.tt1 as ref_0 order by c1) as subq_0;

Note， I just changed "4" to "4 + 1".

HI,

Expressions are evaluated always. However, as this one results in a constant, then ORDER by will have no effect at all.

It is simply optimised away .......