[Maria-developers] MDEV 5007 - help me! it's a old mdev closed as not a bug but don't solve the problem
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 Thanks -- Roberto Spadim
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
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 Golubchikhttps://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
Hi guys! I posted the MDEV-5007 and it's a old problem from MDEV-3795 (the same
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
Am 10.09.2013 16:55, schrieb Roberto Spadim: problem) 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
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
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 Golubchikhttps://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
Hi guys! I posted the MDEV-5007 and it's a old problem from MDEV-3795 (the same
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
Am 10.09.2013 16:55, schrieb Roberto Spadim: problem) 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
participants (2)
-
Reindl Harald -
Roberto Spadim