mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
114 lines
4.1 KiB
Text
114 lines
4.1 KiB
Text
create table t1 (i int);
|
|
insert into t1 values (5),(6),(0);
|
|
#
|
|
# Try out all set functions with window functions as arguments.
|
|
# Any such usage should return an error.
|
|
#
|
|
select MIN( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select MIN(DISTINCT SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select MAX( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select MAX(DISTINCT SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select SUM( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select SUM(DISTINCT SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select AVG( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select AVG(DISTINCT SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select COUNT( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select COUNT(DISTINCT SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select BIT_AND( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select BIT_OR( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select BIT_XOR( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select STD( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select STDDEV( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select STDDEV_POP( SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select STDDEV_SAMP(SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select VARIANCE(SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select VAR_POP(SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select VAR_SAMP(SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select GROUP_CONCAT(SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
select GROUP_CONCAT(DISTINCT SUM(i) OVER (order by i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
#
|
|
# Test that partition instead of order by in over doesn't change result.
|
|
#
|
|
select SUM( SUM(i) OVER (PARTITION BY i) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
#
|
|
# Test that no arguments in OVER() clause lead to crash in this case.
|
|
#
|
|
select SUM( SUM(i) OVER () )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
drop table t1;
|
|
#
|
|
# MDEV-13774: Server Crash on Execuate of SQL Statement
|
|
#
|
|
create table t1 (i int);
|
|
insert into t1 values (5),(6),(0);
|
|
select SUM(
|
|
IF( SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0
|
|
AND
|
|
SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,
|
|
1,
|
|
0) )
|
|
from t1;
|
|
ERROR HY000: Window functions can not be used as arguments to group functions.
|
|
#
|
|
# A way to get the aggregation result.
|
|
#
|
|
select i, IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col
|
|
from t1
|
|
order by i;
|
|
i if_col
|
|
0 0
|
|
5 1
|
|
6 1
|
|
select sum(if_col)
|
|
from (select IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col
|
|
from t1) tmp;
|
|
sum(if_col)
|
|
2
|
|
drop table t1;
|