
Type: Bug

Status: Closed (View Workflow)

Priority: Major

Resolution: Fixed

Affects Version/s: 10.2

Fix Version/s: 10.2.4

Component/s: Optimizer  Window functions

Labels:
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; 