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

AVG() without group by on non matching row returns NULL

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Minor
        • Resolution: Not a Bug
        • Affects Version/s: None
        • Fix Version/s: N/A
        • Component/s: Optimizer
        • Labels:
          None

          Description

          Hi,

          I wonder if it's a bug or a feature, but I find this behaviour quite disturbing.
          If there's no join and if AVG() is used without GROUP BY and with a WHERE clause on a non matching row, it still returns a result.

          e.g. :

          MariaDB [(none)]> SELECT AVG(a), b FROM (SELECT 1 as a, 1 as b) a WHERE b=10;
+--------+---+
| AVG(a) | b |
+--------+---+
|   NULL | NULL |
+--------+---+

          However, if a GROUP BY is added, no rows are returned :

          SELECT AVG(a), b FROM (SELECT 1 as a, 1 as b) a WHERE b=10 GROUP BY a;

          Empty set (0.00 sec)

          Thanks and regards,
          Jocelyn Fournier

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                serg Sergei Golubchik added a comment -

                Quoting the standard (ISO/IEC 9075-2:2003, 4.15 Data analysis operations):

                If no row qualifies, then the result of COUNT is 0 (zero), and the result of any other aggregate function is the
                null value.

                That explains that NULL is correct. Even more:

                MariaDB [test]> SELECT AVG(a), MAX(a), COUNT(a), SUM(a) FROM (SELECT 1 as a, 1 as b) a WHERE b=10;
+--------+--------+----------+--------+
| AVG(a) | MAX(a) | COUNT(a) | SUM(a) |
+--------+--------+----------+--------+
|   NULL |   NULL |        0 |   NULL |
+--------+--------+----------+--------+
1 row in set (0.00 sec)

                But if you add GROUP BY you should get one row per group, as there are no groups you get no rows.

                Show
                serg Sergei Golubchik added a comment - Quoting the standard (ISO/IEC 9075-2:2003, 4.15 Data analysis operations): If no row qualifies, then the result of COUNT is 0 (zero), and the result of any other aggregate function is the null value. That explains that NULL is correct. Even more: MariaDB [test]> SELECT AVG(a), MAX(a), COUNT(a), SUM(a) FROM (SELECT 1 as a, 1 as b) a WHERE b=10; +--------+--------+----------+--------+ | AVG(a) | MAX(a) | COUNT(a) | SUM(a) | +--------+--------+----------+--------+ | NULL | NULL | 0 | NULL | +--------+--------+----------+--------+ 1 row in set (0.00 sec) But if you add GROUP BY you should get one row per group, as there are no groups you get no rows.

                  People

                  • Assignee:
                    serg Sergei Golubchik
                    Reporter:
                    jocel1 jocelyn fournier
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: