maria-developers team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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 <roberto@xxxxxxxxxxxxx>

> 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 <h.reindl@xxxxxxxxxxxxx>
>
>>
>>
>> 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