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

Bogus 'Division by 0' warnings upon using COUNT as a window function

    Details

      Description

      drop table if exists t1;
      create table t1 (pk int, c int);
      insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2);
      set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
      select pk, c, c/count(*) over (partition by c order by pk
      rows between 1 preceding and 2 following) as CNT
      from t1;
      show warnings;
      drop table t1;
      

      MariaDB [test]> select pk, c, c/count(*) over (partition by c order by pk
          -> rows between 1 preceding and 2 following) as CNT
          -> from t1;
      +------+------+--------+
      | pk   | c    | CNT    |
      +------+------+--------+
      |    1 |    1 | 0.3333 |
      |    2 |    1 | 0.2500 |
      |    3 |    1 | 0.3333 |
      |    4 |    1 | 0.5000 |
      |    5 |    2 | 2.0000 |
      +------+------+--------+
      5 rows in set, 5 warnings (0.00 sec)
      

      MariaDB [test]> show warnings;
      +---------+------+---------------+
      | Level   | Code | Message       |
      +---------+------+---------------+
      | Warning | 1365 | Division by 0 |
      | Warning | 1365 | Division by 0 |
      | Warning | 1365 | Division by 0 |
      | Warning | 1365 | Division by 0 |
      | Warning | 1365 | Division by 0 |
      +---------+------+---------------+
      5 rows in set (0.00 sec)
      

      It becomes unexpectedly important since we are going to enable ERROR_FOR_DIVISION_BY_ZERO in 10.2 by default.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: