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

10.2.2 windowing function returns incorrect result

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

          Ascending order - Click to sort in descending order
          kjoiner Kyle Joiner (Inactive) created issue -
          cvicentiu Vicențiu Ciorbaru made changes -
          Field Original Value New Value
          Assignee Vicentiu Ciorbaru [ cvicentiu ]
          psergei Sergei Petrunia made changes -
          Component/s Optimizer - Window functions [ 13502 ]
          Component/s OTHER [ 10125 ]
          psergei Sergei Petrunia made changes -
          Fix Version/s 10.2 [ 14601 ]
          cvicentiu Vicențiu Ciorbaru added a comment -

          This is due to our current limitation regarding SORT_BUFFER_SIZE.

          As a temporary fix, setting SORT_BUFFER_SIZE to a large enough value such that all values fit in memory will generate the correct results.

          set SORT_BUFFER_SIZE=1024*1024*1024

          Ought to fix it.

          cvicentiu Vicențiu Ciorbaru added a comment - This is due to our current limitation regarding SORT_BUFFER_SIZE. As a temporary fix, setting SORT_BUFFER_SIZE to a large enough value such that all values fit in memory will generate the correct results. set SORT_BUFFER_SIZE=1024*1024*1024 Ought to fix it.
          serg Sergei Golubchik made changes -
          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;           		{noformat}
          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)
          {noformat}
          0 <> 1
          {noformat}
          Test case :
          {code:sql}
          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;
          {code}
          serg Sergei Golubchik made changes -
          Description {noformat}
          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)
          {noformat}
          0 <> 1
          {noformat}
          Test case :
          {code:sql}
          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;
          {code}           		{noformat}
          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)
          {noformat}

          1000 <> 2120

          {noformat}
          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)
          {noformat}

          630 <> 976
          {noformat}
          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)
          {noformat}
          0 <> 1

          Test case :
          {code:sql}
          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;
          {code}
          serg Sergei Golubchik made changes -
          Issue Type Task [ 3 ] Bug [ 1 ]
          serg Sergei Golubchik made changes -
          Affects Version/s 10.2 [ 14601 ]
          elenst Elena Stepanova made changes -
          Labels 10.2-rc
          cvicentiu Vicențiu Ciorbaru added a comment -

          This needs rechecking at it should be fixed now.

          cvicentiu Vicențiu Ciorbaru added a comment - This needs rechecking at it should be fixed now.
          cvicentiu Vicențiu Ciorbaru added a comment -

          This is also fixed by MDEV-10092

          cvicentiu Vicențiu Ciorbaru added a comment - This is also fixed by MDEV-10092
          cvicentiu Vicențiu Ciorbaru made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          cvicentiu Vicențiu Ciorbaru added a comment -

          Test case added for bug to prevent regressions.
          https://github.com/mariadb/server/commit/eb54d86b584833fe3586467a65794c3e46f3ddb6

          cvicentiu Vicențiu Ciorbaru added a comment - Test case added for bug to prevent regressions. https://github.com/mariadb/server/commit/eb54d86b584833fe3586467a65794c3e46f3ddb6
          cvicentiu Vicențiu Ciorbaru made changes -
          Fix Version/s 10.2.4 [ 22116 ]
          Fix Version/s 10.2 [ 14601 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 76845 ] MariaDB v4 [ 150838 ]

          People

            cvicentiu Vicențiu Ciorbaru
            kjoiner Kyle Joiner (Inactive)
            Votes:
            1 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.