Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.4
-
None
-
None
-
None
Description
Hi guys, i reported this at MDEV-3795
but the mdev is closed without a solution and without explaining why...
the problem is...
select from (some select order by xxx) limit 0,100
isn't ordered by xxx, but it's ordered by the table order, i will attach a full dump of a table to better explain and test, with a query to show this bug
this occur for myisam / aria / innodb / memory engines, in other words i think it's a problem at temporary table creation or somehting like it...
WRONG RETURN: (this query was used in mysql without problems with the right order by result, but in mariadb it return wrong order by)
SELECT * FROM (
|
SELECT a.pessoa_tipo,a.pessoa_id,
|
(SELECT plano_conta_numero FROM ROTAS_96e78953de9a50fa9262124944c1366e WHERE plano_conta_id=a.rota_distribuicao_id AND plano_conta_id_red=a.rota_distribuicao_id_red) AS rota_numero,
|
a.rota_distribuicao_sequencia,IF(a.pessoa_tipo='f',(SELECT (pais_entrega || uf_entrega || municipio_entrega || bairro_entrega || endereco_entrega_rua || endereco_entrega_numero || endereco_entrega_complemento) AS tmp_end FROM MDEV_5007_pessoa_fisica WHERE id=a.pessoa_id),(SELECT (pais_entrega || uf_entrega || municipio_entrega || bairro_entrega || endereco_entrega_rua || endereco_entrega_numero || endereco_entrega_complemento) AS tmp_end FROM MDEV_5007_pessoa_juridica WHERE id=a.pessoa_id)) AS end_2,
|
IF(a.pessoa_tipo='f',(SELECT IF(habilitada!='a','Y','N') FROM MDEV_5007_pessoa_fisica WHERE id=a.pessoa_id),(SELECT IF(habilitada!='a','Y','N') FROM MDEV_5007_pessoa_juridica WHERE id=a.pessoa_id)) AS desabilitado,
|
IF(a.pessoa_tipo='f',(SELECT vendas_bloqueada FROM MDEV_5007_pessoa_fisica WHERE id=a.pessoa_id),(SELECT vendas_bloqueada FROM MDEV_5007_pessoa_juridica WHERE id=a.pessoa_id)) AS vendas_bloqueada
|
FROM MDEV_5007_pessoa_juridica_mov AS a WHERE id=1
|
ORDER BY desabilitado,vendas_bloqueada,rota_numero,rota_distribuicao_sequencia,end_2
|
) AS tmp_tbl222
|
LIMIT 30 OFFSET 0;
|
explain (HERE WE DON'T HAVE USING TEMPORARY / FILESORT!!!)
1 | SIMPLE | a | ALL | 792 | Using where | ||||
9 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_juridica | ALL | 1447 | Using where | ||||
8 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_fisica | ALL | 340 | Using where | ||||
7 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_juridica | ALL | 1447 | Using where | ||||
6 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_fisica | ALL | 340 | Using where | ||||
5 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_juridica | ALL | 1447 | Using where | ||||
4 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_fisica | ALL | 340 | Using where | ||||
3 | DEPENDENT SUBQUERY | ROTAS_96e78953de9a50fa9262124944c1366e | eq_ref | PRIMARY | PRIMARY | 16 | bossoni_cadastros.a.rota_distribuicao_id,bossoni_cadastros.a.rota_distribuicao_id_red | 1 |
NICE RETURN: (this query runs nice in mariadb and mysql)
SELECT * FROM (
|
SELECT a.pessoa_tipo,a.pessoa_id,
|
(SELECT plano_conta_numero FROM ROTAS_96e78953de9a50fa9262124944c1366e WHERE plano_conta_id=a.rota_distribuicao_id AND plano_conta_id_red=a.rota_distribuicao_id_red) AS rota_numero,
|
a.rota_distribuicao_sequencia,IF(a.pessoa_tipo='f',(SELECT (pais_entrega || uf_entrega || municipio_entrega || bairro_entrega || endereco_entrega_rua || endereco_entrega_numero || endereco_entrega_complemento) AS tmp_end FROM MDEV_5007_pessoa_fisica WHERE id=a.pessoa_id),(SELECT (pais_entrega || uf_entrega || municipio_entrega || bairro_entrega || endereco_entrega_rua || endereco_entrega_numero || endereco_entrega_complemento) AS tmp_end FROM MDEV_5007_pessoa_juridica WHERE id=a.pessoa_id)) AS end_2,
|
IF(a.pessoa_tipo='f',(SELECT IF(habilitada!='a','Y','N') FROM MDEV_5007_pessoa_fisica WHERE id=a.pessoa_id),(SELECT IF(habilitada!='a','Y','N') FROM MDEV_5007_pessoa_juridica WHERE id=a.pessoa_id)) AS desabilitado,
|
IF(a.pessoa_tipo='f',(SELECT vendas_bloqueada FROM MDEV_5007_pessoa_fisica WHERE id=a.pessoa_id),(SELECT vendas_bloqueada FROM MDEV_5007_pessoa_juridica WHERE id=a.pessoa_id)) AS vendas_bloqueada
|
FROM MDEV_5007_pessoa_juridica_mov AS a WHERE id=1
|
) AS tmp_tbl222
|
ORDER BY desabilitado,vendas_bloqueada,rota_numero,rota_distribuicao_sequencia,end_2
|
LIMIT 30 OFFSET 0;
|
explain (HERE WE HAVE USING TEMPORARY / FILESORT)
1 | SIMPLE | a | ALL | 792 | Using where; Using temporary; Using filesort | ||||
9 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_juridica | ALL | 1447 | Using where | ||||
8 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_fisica | ALL | 340 | Using where | ||||
7 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_juridica | ALL | 1447 | Using where | ||||
6 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_fisica | ALL | 340 | Using where | ||||
5 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_juridica | ALL | 1447 | Using where | ||||
4 | DEPENDENT SUBQUERY | MDEV_5007_pessoa_fisica | ALL | 340 | Using where | ||||
3 | DEPENDENT SUBQUERY | ROTAS_96e78953de9a50fa9262124944c1366e | eq_ref | PRIMARY | PRIMARY | 16 | bossoni_cadastros.a.rota_distribuicao_id,bossoni_cadastros.a.rota_distribuicao_id_red | 1 |
the only diference at SQL is the order by not inside the () AS tmp_tbl222 alias, maybe the materializations isn't done using the order by (checked by explain at first and second query)...
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.