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