[MDEV-5007] wrong order by moving from mysql to mariadb - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.0.4
Fix Version/s: None
Component/s: None
Labels:
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)...

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

MDEV-5007.SQL
182 kB
2013-09-10 17:29

Issue Links

relates to

MDEV-3795 WRONG ORDER BY

Closed

MDEV-4978 Server cursor is broken with blobs in the select list, ORDER BY does not work

Closed

MDEV-3926 Wrong result with GROUP BY ... WITH ROLLUP

Closed

links to

NOT A BUG!

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Sergei Golubchik added a comment - 2013-09-10 17:57

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.

Show

Sergei Golubchik added a comment - 2013-09-10 17:57 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.

Hide

Permalink

roberto spadim added a comment - 2013-09-10 18:08

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

Show

roberto spadim added a comment - 2013-09-10 18:08 wow! thanks sergey, now it's easier to understand, i will contact developer to rewrite this query

Hide

Permalink

roberto spadim added a comment - 2013-09-10 18:08

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

Show

roberto spadim added a comment - 2013-09-10 18:08 it's easy to allow a order by inside the subquery?

Hide

Permalink

roberto spadim added a comment - 2013-09-10 19:53

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;

Show

roberto spadim added a comment - 2013-09-10 19:53 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;

wrong order by moving from mysql to mariadb

Details

Description

Gliffy Diagrams

Attachments

Attachments

Issue Links

Activity

People

Dates

Agile