i think we could at least add a variable at optimizer to consider or not consider the order by inside sub query well i don't know if it's right, or it's ok, or anything else, just a user point, not a developer or sql standard point
2013/9/10 Roberto Spadim <[email protected]> > Hi Reindl Harald > Maybe we have a problem... i think it's not part of 4978, but maybe it's a > documentation problem? well i don't know, but please check this comment > from Sergei at MDEV-5007 > > > Sergei > Golubchik<https://mariadb.atlassian.net/secure/ViewProfile.jspa?name=serg> > added > a comment - 14 minutes ago > The explanation is in the knowledge base > https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ > > But here it is, again: your ORDER BY clause is ignored by the optimizer. > It is not a bug. > > A "table" (and subquery in the FROM clause too) is — according to the SQL > standard — an unordered set of rows. Rows in a table (or in a subquery in > the FROM clause) do not come in any specific order. That's why the > optimizer can ignore the ORDER BY clause that you have specified. In fact, > SQL standard does not even allow the ORDER BY clause to appear in this > subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, > the set of rows, not only their order). > > You need to treat the subquery in the FROM clause, as a set of rows in > some unspecified and undefined order, and put the ORDER BY on the top-level > SELECT. > > > 2013/9/10 Reindl Harald <[email protected]> > >> >> >> Am 10.09.2013 16:55, schrieb Roberto Spadim: >> > Hi guys! >> > I posted the MDEV-5007 and it's a old problem from MDEV-3795 (the same >> problem) >> > It (MDEV-3795) was closed as a not a bug, but i think it's a bug, but i >> didn't explained well at the first time >> > >> > I changed from a mysql server to a mariadb server and the result order >> by isn't executed, i tried >> > myisam,aria,innodb,memory engines and all of them return the same >> problem (order by not executed) >> > >> > On the wrong query i don't see the "using filesort" at explain, and at >> the right query (with right order by) it >> > show "using filesort" >> > >> > Could anyone help me? or at least EXPLAIN why the order by shouldn't be >> executed on the first query, and maybe a >> > workaround using sql_optimizer variable or something like it? >> > >> > it's a closed software and i need to contact developer to change the >> query, that's why i'm talking about the >> > sql_optimizer variable instead of rewrite the query >> >> this is pretty clear the following and will be fixed in 5.5.33 >> https://mariadb.atlassian.net/browse/MDEV-4978 >> >> > > > -- > Roberto Spadim > SPAEmpresarial > -- Roberto Spadim SPAEmpresarial
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
