Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5007

wrong order by moving from mysql to mariadb

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • 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)...

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                rspadim roberto spadim
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: