Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Not a Bug
-
Affects Version/s: 5.5.25-galera
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:linux
Description
hi, i'm running two queries but they return diferent order by results when they should be the same i will attach files to create tables
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
and it returned with order by b? or by a? if it returned from by b, the parser didn't ignored the implicity order by of group by... in other words why a order by is ignored and a order by with group by isn't?
SELECT * FROM
( SELECT a, b FROM t1 GROUP BY a, b DESC ORDER BY b ASC
) t
LIMIT 0, 10
Result is ordered by "b ASC". So ORDER BY is not ignored.
now try this
alter table t1 order by a,b desc;
SELECT * FROM
( SELECT a, b FROM t1 ORDER BY b ASC
) t
LIMIT 0, 10
and test if it's ordered by a,b desc or b asc
It's ordered by a,b desc.
ORDER BY b ASC is ignored
that's the point
why it's ignored, if it's explicit? ok it's not explicit for the out select, but it's explicit for one select...
i think it's as undocumented, or a bug... i prefer think as a undocumented, and show a workaround to make it work... but for some guy that changed from mysql to mariadb, will consider it as a bug...
wrong query:
SELECT tmp_tbl22.* FROM (
SELECT a.pessoa_tipo,a.pessoa_id,a.spavendas_vendedor_tipo,a.spavendas_vendedor_id,a.spavendas_tbl_preco,a.cfop_vendas_id,a.cfop_vendas_id_red,a.spacompra_vendedor_tipo,a.spacompra_vendedor_id,a.cfop_compra_id,a.cfop_compra_id_red,(
select plano_conta_numero FROM ROTAS_ca49da4cb6a34b20343ffc942dd7e8ae WHERE plano_conta_id=a.rota_distribuicao_id AND plano_conta_id_red=a.rota_distribuicao_id_red) AS rota_numero,(
SELECT descricao FROM ROTAS_ca49da4cb6a34b20343ffc942dd7e8ae WHERE plano_conta_id=a.rota_distribuicao_id AND plano_conta_id_red=a.rota_distribuicao_id_red) AS rota_desc,a.rota_distribuicao_sequencia,'j' AS distribuidor_tipo,id AS distribuidor_id,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 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 pessoa_juridica WHERE id=a.pessoa_id)) AS end_2,IF(a.pessoa_tipo='f',(
SELECT IF(habilitada!='a','Y','N') FROM pessoa_fisica WHERE id=a.pessoa_id),(SELECT IF(habilitada!='a','Y','N') FROM pessoa_juridica WHERE id=a.pessoa_id)) AS desabilitado,IF(a.pessoa_tipo='f',(SELECT vendas_bloqueada FROM pessoa_fisica WHERE id=a.pessoa_id),(SELECT vendas_bloqueada FROM pessoa_juridica WHERE id=a.pessoa_id)) AS vendas_bloqueada,(SELECT plano_conta_numero FROM ROTAS_ca49da4cb6a34b20343ffc942dd7e8ae WHERE plano_conta_id=a.rota_comercial_id AND plano_conta_id_red=a.rota_comercial_id_red) AS rota_comercial_numero,(SELECT descricao FROM ROTAS_ca49da4cb6a34b20343ffc942dd7e8ae WHERE plano_conta_id=a.rota_comercial_id AND plano_conta_id_red=a.rota_comercial_id_red) AS rota_comercial_descricao,a.rota_comercial_sequencia,a.data_ultima_venda
FROM pessoa_juridica_mov AS a WHERE id=1 AND spavendas_vendedor_id=0
ORDER BY data_ultima_venda
) AS tmp_tbl22 LIMIT 30 OFFSET 0
right query:
SELECT tmp_tbl22.* FROM (
SELECT a.pessoa_tipo,a.pessoa_id,a.spavendas_vendedor_tipo,a.spavendas_vendedor_id,a.spavendas_tbl_preco,a.cfop_vendas_id,a.cfop_vendas_id_red,a.spacompra_vendedor_tipo,a.spacompra_vendedor_id,a.cfop_compra_id,a.cfop_compra_id_red,(
select plano_conta_numero FROM ROTAS_ca49da4cb6a34b20343ffc942dd7e8ae WHERE plano_conta_id=a.rota_distribuicao_id AND plano_conta_id_red=a.rota_distribuicao_id_red) AS rota_numero,(
SELECT descricao FROM ROTAS_ca49da4cb6a34b20343ffc942dd7e8ae WHERE plano_conta_id=a.rota_distribuicao_id AND plano_conta_id_red=a.rota_distribuicao_id_red) AS rota_desc,a.rota_distribuicao_sequencia,'j' AS distribuidor_tipo,id AS distribuidor_id,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 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 pessoa_juridica WHERE id=a.pessoa_id)) AS end_2,IF(a.pessoa_tipo='f',(
SELECT IF(habilitada!='a','Y','N') FROM pessoa_fisica WHERE id=a.pessoa_id),(SELECT IF(habilitada!='a','Y','N') FROM pessoa_juridica WHERE id=a.pessoa_id)) AS desabilitado,IF(a.pessoa_tipo='f',(SELECT vendas_bloqueada FROM pessoa_fisica WHERE id=a.pessoa_id),(SELECT vendas_bloqueada FROM pessoa_juridica WHERE id=a.pessoa_id)) AS vendas_bloqueada,(SELECT plano_conta_numero FROM ROTAS_ca49da4cb6a34b20343ffc942dd7e8ae WHERE plano_conta_id=a.rota_comercial_id AND plano_conta_id_red=a.rota_comercial_id_red) AS rota_comercial_numero,(SELECT descricao FROM ROTAS_ca49da4cb6a34b20343ffc942dd7e8ae WHERE plano_conta_id=a.rota_comercial_id AND plano_conta_id_red=a.rota_comercial_id_red) AS rota_comercial_descricao,a.rota_comercial_sequencia,a.data_ultima_venda
FROM pessoa_juridica_mov AS a WHERE id=1 AND spavendas_vendedor_id=0
) AS tmp_tbl22
ORDER BY data_ultima_venda
LIMIT 30 OFFSET 0