Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Not a Bug
-
Affects Version/s: 5.5.28a-galera
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:ubuntu 12.04LTS
Description
my web application will generate a sql:
select * from (select * .... order by time desc) limit 0,30
the result is not orderd
offical mysql or percona return orderd result
it seems like subquery optimizations problem
if you think it is not a bug, please tell me how to disable this "feature", I really dont need this
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
there is no optimizer_switch that tells the server to respect ORDER BY in subqueries.
Your application is broken, it relies on the behavior, that is undefined by the SQL standard, and it will soon start failing in MySQL too, as MySQL will get new optimizations for subqueries. There is no other way but to fix the web application. Still with MySQL or Percona Server you will need to do this fix later, with MariaDB you need to do it now.
I find link https://kb.askmonty.org/en/why-is-order-by-in-a-from-subquery-ignored/
it is not a bug, according to the SQL standard
two choices:
1 can I set optimizer_switch to disable this
2 dont use mariadb (I like subquery optimizations, because of in() with subquery, but to my web application, ignore order is a "error", I cannot change the web application code)