MySQL Bugs: #79226: Order by in where clause doesn't cause error

Bug #79226	Order by in where clause doesn't cause error
Submitted:	11 Nov 2015 10:08	Modified:	11 Nov 2015 15:12
Reporter:	Jeff Ulrich	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.21	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any
Tags:	order by, where clause

Description:
This query runs against by database:

select 
  i.id as invoice_id
, i.`index` as invoice_index
, w.amount as writeoff_amount
, i.amount as invoice_amount
, i.balance as invoice_balance
, w.note
, b.name
, w.isactive as writeoff_isactive
, i.isactive as invoice_isactive
, i.status as invoice_status
, from_unixtime(w.writeoff_date,'%Y-%m-%d') as writeoff_date
from tbl_writeoff w
left join tbl_invoice i on i.id=w.invoice_id
left join tbl_business b on b.id=i.business_id
where i.status =2 order by i.id
  and i.isactive=1
  and w.isactive=1
;

Note the order by clause after the first where clause statement.  There are two issues:
1.  The code runs and returns results
2.  The subsequent statements in the where clause are ignored

How to repeat:
Include an order by statement after the first where clause and have subsequent where statements after the order by.

Suggested fix:
I would expect this code to fail with an error on the line with the inline order by statement.

Hello Mr. Ulrich,

You have not been quite clear in what you request here, but I think I know what you are talking about. 

You are expecting that in the following ORDER clause:

 order by i.id  and i.isactive=1  and w.isactive=1

your get a syntax error after :  and i.isactive=1  and w.isactive=1

because it seems that WHERE clause is continuing in the ORDER clause. Am I right ???

Well, this is not a bug because, the entire ORDER clause, designed as above, is treated as one big expression. Hence, the results are ordered by a big clause. As a consequence , your results with not be ordered by i.id column, but by a much more narrow sorting criteria.

This is in accordance by current SQL standards.