[MDEV-4030] sort "order by" is not working Created: 2013-01-14  Updated: 2014-06-20  Resolved: 2013-01-14

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.28a-galera
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Zhou Chao Assignee: Unassigned
Resolution: Not a Bug Votes: 0
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

 Comments   
Comment by Zhou Chao [ 2013-01-14 ]

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)

Comment by Sergei Golubchik [ 2013-01-14 ]

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.

Generated at Thu Feb 08 06:53:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.