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

10.2.2 windowing function returns incorrect result

    XMLWordPrintable

    Details

      Description

      MariaDB [dw]> select count(distinct s) from (select sum(d) over(partition by a,b,c) as s from t) Z where s > 0;
      +-------------------+
      | count(distinct s) |
      +-------------------+
      | 1000 |
      +-------------------+
      1 row in set (0.28 sec)
       
      MariaDB [dw]> select count(distinct s) from (select sum(d) as s from t group by a,b,c) Z where s > 0;
      +-------------------+
      | count(distinct s) |
      +-------------------+
      | 2120 |
      +-------------------+
      1 row in set (0.29 sec)
      

      1000 <> 2120

      MariaDB [dw]>
      MariaDB [dw]> select count(distinct s) from (select sum(d) over(partition by a,b) as s from t) Z where s > 0;
      +-------------------+
      | count(distinct s) |
      +-------------------+
      | 630 |
      +-------------------+
      1 row in set (0.23 sec)
       
      MariaDB [dw]> select count(distinct s) from (select sum(d) as s from t group by a,b) Z where s > 0;
      +-------------------+
      | count(distinct s) |
      +-------------------+
      | 976 |
      +-------------------+
      1 row in set (0.11 sec)
      

      630 <> 976

      MariaDB [dw]>
      MariaDB [dw]> select count(distinct s) from (select sum(d) over(partition by a) as s from t) Z where s > 0;
      +-------------------+
      | count(distinct s) |
      +-------------------+
      | 0 |
      +-------------------+
      1 row in set (0.15 sec)
       
      MariaDB [dw]> select count(distinct s) from (select sum(d) as s from t group by a) Z where s > 0;
      +-------------------+
      | count(distinct s) |
      +-------------------+
      | 1 |
      +-------------------+
      1 row in set (0.08 sec)
      

      0 <> 1

      Test case :

      drop table if exists t;
      create table t(a int,b int, c int , d int);
      insert into t(a,b,c,d) values(1, rand()*1000, rand()*1000, rand()*1000);
      insert into t(a,b,c,d) values(1, rand()*1000, rand()*1000, rand()*1000);
      replace into t(a,b,c,d) select 1, rand()*1000, rand()*1000, rand()*1000 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15, t t16, t t17;
       
      select count(distinct s) from (select sum(d) over(partition by a,b,c) as s from t) Z where s > 0;
      select count(distinct s) from (select sum(d) as s from t group by a,b,c) Z where s > 0;
       
      select count(distinct s) from (select sum(d) over(partition by a,b) as s from t) Z where s > 0;
      select count(distinct s) from (select sum(d) as s from t group by a,b) Z where s > 0;
       
      select count(distinct s) from (select sum(d) over(partition by a) as s from t) Z where s > 0;
      select count(distinct s) from (select sum(d) as s from t group by a) Z where s > 0;
      

        Attachments

          Activity

            People

            Assignee:
            cvicentiu Vicențiu Ciorbaru
            Reporter:
            kjoiner Kyle Joiner
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration