[MDEV-5007] wrong order by moving from mysql to mariadb Created: 2013-09-10  Updated: 2013-09-10  Resolved: 2013-09-10

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: roberto spadim Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None

Attachments: File MDEV-5007.SQL    
Issue Links:
Relates
relates to MDEV-3795 WRONG ORDER BY Closed
relates to MDEV-3926 Wrong result with GROUP BY ... WITH R... Closed
relates to MDEV-4978 Server cursor is broken with blobs in... Closed

 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)...



 Comments   
Comment by Sergei Golubchik [ 2013-09-10 ]

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.

Comment by roberto spadim [ 2013-09-10 ]

wow! thanks sergey, now it's easier to understand, i will contact developer to rewrite this query

Comment by roberto spadim [ 2013-09-10 ]

it's easy to allow a order by inside the subquery?

Comment by roberto spadim [ 2013-09-10 ]

reading the https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/

the only 'easy' work around is add LIMIT 18446744073709551615 (64bits number-1) inside the subquery
this change the explain with "Using where; Using temporary; Using filesort"

thanks sergey about the link to knowledge!

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
LIMIT 18446744073709551615
) AS tmp_tbl222
LIMIT 30 OFFSET 0;

Generated at Thu Feb 08 07:00:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.