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

wrong order by moving from mysql to mariadb

    XMLWordPrintable

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

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.