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

wrong order by moving from mysql to mariadb

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

            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.

            serg Sergei Golubchik added a comment - 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.

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

            rspadim roberto spadim added a comment - wow! thanks sergey, now it's easier to understand, i will contact developer to rewrite this query

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

            rspadim roberto spadim added a comment - it's easy to allow a order by inside the subquery?

            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;

            rspadim roberto spadim added a comment - 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;

            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.