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

Ordering by aggregate expression sometimes works incorrectly



    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2
    • Fix Version/s: 10.1, 10.2
    • Component/s: Optimizer
    • Labels:
    • Environment:


      http://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=cacfe3a36b241a9379faf72286bb0531 - basically, it has everything that's needed.

      Long story:

      When profiling a complex query on a copy of production database, I found out that the ORDER BY clause doesn't always work properly with an expression that contains aggregate functions.

      I used MariaDB version 10.1.28, on Nixos Linux. The sample query below exhibits the bug on MariaDB 10.1.28 and on dbfiddle.uk (which runs MariaDB 10.2.12). MySQL 5.6.38 (which I run on Gentoo Linux) doesn't have this bug (at least, the sample query doesn't exhibit it).

      Here is some sample data that demonstrate the issue:

      CREATE TABLE `t0` (
        `a` int(11) NOT NULL,
        `x` int(11) NOT NULL,
        `y` int(11) NOT NULL
      ) ENGINE=InnoDB;
      INSERT INTO `t0` VALUES (1,40,9),(2,3,12),(3,14,14),(4,1,2),(10,23,1),(20,15,10),(30,8,2),(40,100,12),(1,40,9),(2,3,12),(3,14,14),(4,1,2),(10,23,1),(20,15,10),(30,8,2),(40,100,12);
      CREATE TABLE `t1` (
        `a` int(11) NOT NULL,
        `b` int(11) NOT NULL
      ) ENGINE=InnoDB;
      INSERT INTO `t1` VALUES (10,5),(10,8),(20,5),(10,5),(10,7),(10,8),(20,5),(20,8),(30,9),(30,5),(40,9),(40,11),(40,8),(1,5),(1,7),(1,8),(2,5),(2,8),(3,9),(3,5),(4,9),(4,11),(4,8);

      The problematic query is as follows:

      select t0.a, sum(x + y * b)/count(distinct b) + x as mysum
      from t0 inner join t1 on t0.a=t1.a
      group by a order by (sum(x + y * b)/count(distinct b) + x) desc;

      It produces the following result:

      a	mysum
      40	524.0000
      20	240.0000
      3	238.0000
      1	240.0000
      2	165.0000
      10	121.6667
      30	52.0000
      4	40.3333

      So, despite the query being sorted by the expression that's used to calculate the "mysum" column, the rows aren't listed in correct order. If I replace the ORDER BY expression with "mysum", the order becomes correct.

      The ORDER BY expression in the original query (for production data) looks like this: "r.value1 + sum(ifnull(e.value2 * (e.value3 + e.value), 0.0))". That query also starts working properly once I replace the ORDER BY expression with an alias.

      Also, both queries seem to work fine if I remove the non-aggregate part ("x" in sample query and "r.value1" in production query).


          Issue Links



              cvicentiu Vicențiu Ciorbaru
              pvgoran Pavel Goran
              2 Vote for this issue
              4 Start watching this issue