Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4738

AVG returns a wrong result

    XMLWordPrintable

    Details

    • Sprint:
      2021-8, 2021-9

      Description

      This problem is not repeatable with the develop-5 branch.

      This problem is repeatable with the develop branch on Fedora (5.5.9-200.fc31.x86_64), only with RelWithDebInfo build.
      Debug build returns expected results (work exactly in the same way with InnoDB).

      I run this script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, b CHAR(5))ENGINE=Columnstore;
      INSERT INTO t1 VALUES (NULL, ''),(1, 'aaa'),(2, 'aaa'),(3, 'ccc'),(4, 'ddd'),(5, 'aaa'),(6, 'ddd'),(7, 'eee');
      SELECT * FROM t1;
      

      +------+------+
      | a    | b    |
      +------+------+
      | NULL | NULL |
      |    1 | aaa  |
      |    2 | aaa  |
      |    3 | ccc  |
      |    4 | ddd  |
      |    5 | aaa  |
      |    6 | ddd  |
      |    7 | eee  |
      +------+------+
      

      Looks good so far.

      SELECT a, AVG(a) FROM t1 GROUP BY a ORDER BY a;
      

      +------+--------+
      | a    | AVG(a) |
      +------+--------+
      | NULL |   NULL |
      |    1 |   NULL |
      |    2 |   NULL |
      |    3 | 0.0000 |
      |    4 |   NULL |
      |    5 | 0.0000 |
      |    6 | 0.0000 |
      |    7 | 0.0000 |
      +------+--------+
      

      Looks wrong.

      SELECT AVG(DISTINCT a) FROM t1;
      

      +-----------------+
      | AVG(DISTINCT a) |
      +-----------------+
      |            NULL |
      +-----------------+
      

      Also looks wrong.

      If I change ENGINE to InnoDB, it returns expected results:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, b CHAR(5)) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (NULL, ''),(1, 'aaa'),(2, 'aaa'),(3, 'ccc'),(4, 'ddd'),(5, 'aaa'),(6, 'ddd'),(7, 'eee');
      

      SELECT a, AVG(a) FROM t1 GROUP BY a ORDER BY a;
      

      +------+--------+
      | a    | AVG(a) |
      +------+--------+
      | NULL |   NULL |
      |    1 | 1.0000 |
      |    2 | 2.0000 |
      |    3 | 3.0000 |
      |    4 | 4.0000 |
      |    5 | 5.0000 |
      |    6 | 6.0000 |
      |    7 | 7.0000 |
      +------+--------+
      

      SELECT AVG(DISTINCT a) FROM t1;
      

      +-----------------+
      | AVG(DISTINCT a) |
      +-----------------+
      |          4.0000 |
      +-----------------+
      

        Attachments

          Activity

            People

            Assignee:
            tntnatbry Gagan Goel
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.