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

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

    XMLWordPrintable

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

              igor Igor Babaev
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.