summaryrefslogtreecommitdiff
path: root/mysql-test/main/win.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/win.result')
-rw-r--r--mysql-test/main/win.result401
1 files changed, 218 insertions, 183 deletions
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index 5e908e234c2..2f757756117 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -38,10 +38,10 @@ a row_number() over (partition by a order by b)
select a, b, x, row_number() over (partition by a order by x) from t1;
a b x row_number() over (partition by a order by x)
2 10 xx 1
-2 10 zz 3
2 20 yy 2
-3 10 xxx 2
+2 10 zz 3
3 20 vvv 1
+3 10 xxx 2
drop table t1;
create table t1 (pk int primary key, a int, b int);
insert into t1 values
@@ -54,13 +54,14 @@ select
pk, a, b,
row_number() over (order by a),
row_number() over (order by b)
-from t1;
+from t1
+order by b;
pk a b row_number() over (order by a) row_number() over (order by b)
-1 10 22 1 5
-2 11 21 2 4
-3 12 20 3 3
-4 13 19 4 2
5 14 18 5 1
+4 13 19 4 2
+3 12 20 3 3
+2 11 21 2 4
+1 10 22 1 5
drop table t1;
#
# Try RANK() function
@@ -228,16 +229,16 @@ count(*) over (partition by c order by pk desc
rows between 2 preceding and 2 following) as CNT
from t1;
pk c CNT
-1 1 3
-2 1 4
-3 1 4
4 1 3
-5 2 3
-6 2 4
-7 2 5
-8 2 5
-9 2 4
+3 1 4
+2 1 4
+1 1 3
10 2 3
+9 2 4
+8 2 5
+7 2 5
+6 2 4
+5 2 3
drop table t0,t1;
#
# Resolution of window names
@@ -535,42 +536,40 @@ insert into t3 values
(5, 2),
(6, 2);
select
-pk,
val,
count(val) over (order by val
range between current row and
current row)
as CNT
from t3;
-pk val CNT
-0 1 3
-1 1 3
-2 1 3
-3 2 4
-4 2 4
-5 2 4
-6 2 4
+val CNT
+1 3
+1 3
+1 3
+2 4
+2 4
+2 4
+2 4
insert into t3 values
(7, 3),
(8, 3);
select
-pk,
val,
count(val) over (order by val
range between current row and
current row)
as CNT
from t3;
-pk val CNT
-0 1 3
-1 1 3
-2 1 3
-3 2 4
-4 2 4
-5 2 4
-6 2 4
-7 3 2
-8 3 2
+val CNT
+1 3
+1 3
+1 3
+2 4
+2 4
+2 4
+2 4
+3 2
+3 2
drop table t3;
# Now, check with PARTITION BY
create table t4 (
@@ -599,7 +598,6 @@ insert into t4 values
(5678, 208, 3);
select
part_id,
-pk,
val,
count(val) over (partition by part_id
order by val
@@ -607,31 +605,30 @@ range between current row and
current row)
as CNT
from t4;
-part_id pk val CNT
-1234 100 1 3
-1234 101 1 3
-1234 102 1 3
-1234 103 2 4
-1234 104 2 4
-1234 105 2 4
-1234 106 2 4
-1234 107 3 2
-1234 108 3 2
-5678 200 1 3
-5678 201 1 3
-5678 202 1 3
-5678 203 2 4
-5678 204 2 4
-5678 205 2 4
-5678 206 2 4
-5678 207 3 2
-5678 208 3 2
+part_id val CNT
+1234 1 3
+1234 1 3
+1234 1 3
+1234 2 4
+1234 2 4
+1234 2 4
+1234 2 4
+1234 3 2
+1234 3 2
+5678 1 3
+5678 1 3
+5678 1 3
+5678 2 4
+5678 2 4
+5678 2 4
+5678 2 4
+5678 3 2
+5678 3 2
#
# Try RANGE UNBOUNDED PRECEDING | FOLLOWING
#
select
part_id,
-pk,
val,
count(val) over (partition by part_id
order by val
@@ -639,28 +636,27 @@ range between unbounded preceding and
current row)
as CNT
from t4;
-part_id pk val CNT
-1234 100 1 3
-1234 101 1 3
-1234 102 1 3
-1234 103 2 7
-1234 104 2 7
-1234 105 2 7
-1234 106 2 7
-1234 107 3 9
-1234 108 3 9
-5678 200 1 3
-5678 201 1 3
-5678 202 1 3
-5678 203 2 7
-5678 204 2 7
-5678 205 2 7
-5678 206 2 7
-5678 207 3 9
-5678 208 3 9
+part_id val CNT
+1234 1 3
+1234 1 3
+1234 1 3
+1234 2 7
+1234 2 7
+1234 2 7
+1234 2 7
+1234 3 9
+1234 3 9
+5678 1 3
+5678 1 3
+5678 1 3
+5678 2 7
+5678 2 7
+5678 2 7
+5678 2 7
+5678 3 9
+5678 3 9
select
part_id,
-pk,
val,
count(val) over (partition by part_id
order by val
@@ -668,28 +664,27 @@ range between current row and
unbounded following)
as CNT
from t4;
-part_id pk val CNT
-1234 100 1 9
-1234 101 1 9
-1234 102 1 9
-1234 103 2 6
-1234 104 2 6
-1234 105 2 6
-1234 106 2 6
-1234 107 3 2
-1234 108 3 2
-5678 200 1 9
-5678 201 1 9
-5678 202 1 9
-5678 203 2 6
-5678 204 2 6
-5678 205 2 6
-5678 206 2 6
-5678 207 3 2
-5678 208 3 2
+part_id val CNT
+1234 1 9
+1234 1 9
+1234 1 9
+1234 2 6
+1234 2 6
+1234 2 6
+1234 2 6
+1234 3 2
+1234 3 2
+5678 1 9
+5678 1 9
+5678 1 9
+5678 2 6
+5678 2 6
+5678 2 6
+5678 2 6
+5678 3 2
+5678 3 2
select
part_id,
-pk,
val,
count(val) over (partition by part_id
order by val
@@ -697,25 +692,25 @@ range between unbounded preceding and
unbounded following)
as CNT
from t4;
-part_id pk val CNT
-1234 100 1 9
-1234 101 1 9
-1234 102 1 9
-1234 103 2 9
-1234 104 2 9
-1234 105 2 9
-1234 106 2 9
-1234 107 3 9
-1234 108 3 9
-5678 200 1 9
-5678 201 1 9
-5678 202 1 9
-5678 203 2 9
-5678 204 2 9
-5678 205 2 9
-5678 206 2 9
-5678 207 3 9
-5678 208 3 9
+part_id val CNT
+1234 1 9
+1234 1 9
+1234 1 9
+1234 2 9
+1234 2 9
+1234 2 9
+1234 2 9
+1234 3 9
+1234 3 9
+5678 1 9
+5678 1 9
+5678 1 9
+5678 2 9
+5678 2 9
+5678 2 9
+5678 2 9
+5678 3 9
+5678 3 9
drop table t4;
#
# MDEV-9695: Wrong window frame when using RANGE BETWEEN N FOLLOWING AND PRECEDING
@@ -881,15 +876,15 @@ RANGE BETWEEN UNBOUNDED PRECEDING
AND 10 FOLLOWING) as cnt
from t1;
pk a cnt
-1 1 9
-2 2 9
-3 4 9
-4 8 9
-5 26 5
-6 27 5
-7 40 3
-8 71 2
9 72 2
+8 71 2
+7 40 3
+6 27 5
+5 26 5
+4 8 9
+3 4 9
+2 2 9
+1 1 9
select
pk, a,
count(a) over (ORDER BY a
@@ -929,15 +924,15 @@ RANGE BETWEEN UNBOUNDED PRECEDING
AND 10 PRECEDING) as cnt
from t1;
pk a cnt
-1 1 5
-2 2 5
-3 4 5
-4 8 5
-5 26 3
-6 27 3
-7 40 2
-8 71 0
9 72 0
+8 71 0
+7 40 2
+6 27 3
+5 26 3
+4 8 5
+3 4 5
+2 2 5
+1 1 5
select
pk, a,
count(a) over (ORDER BY a
@@ -977,15 +972,15 @@ RANGE BETWEEN 1 PRECEDING
AND CURRENT ROW) as cnt
from t1;
pk a cnt
-1 1 2
-2 2 1
-3 4 1
-4 8 1
-5 26 2
-6 27 1
-7 40 1
-8 71 2
9 72 1
+8 71 2
+7 40 1
+6 27 1
+5 26 2
+4 8 1
+3 4 1
+2 2 1
+1 1 2
select
pk, a,
count(a) over (ORDER BY a
@@ -1026,15 +1021,15 @@ range between current row
and 1 following) as cnt
from t1;
pk a cnt
-1 1 1
-2 2 2
-3 4 1
-4 8 1
-5 26 1
-6 27 2
-7 40 1
-8 71 1
9 72 2
+8 71 1
+7 40 1
+6 27 2
+5 26 1
+4 8 1
+3 4 1
+2 2 2
+1 1 1
insert into t1 select 22, pk, a from t1;
select
part_id, pk, a,
@@ -1137,7 +1132,8 @@ pk, a,
count(b) over (order by a
range between 2 preceding
and 2 following) as CNT
-from t1;
+from t1
+order by a, pk;
pk a CNT
1 NULL 3
2 NULL 3
@@ -1969,12 +1965,12 @@ ROW_NUMBER() OVER (order by s1),
CUME_DIST() OVER (order by -s1)
from t1;
s1 s2 ROW_NUMBER() OVER (order by s1) CUME_DIST() OVER (order by -s1)
+-1 2 1.0000000000
1 a 3 0.8333333333
-NULL NULL 1 0.1666666667
+2 b 4 0.6666666667
3 NULL 5 0.5000000000
4 a 6 0.3333333333
-2 b 4 0.6666666667
--1 2 1.0000000000
+NULL NULL 1 0.1666666667
drop table t1;
#
# MDEV-9925: Wrong result with aggregate function as a window function
@@ -2012,8 +2008,8 @@ o_custkey avg(o_custkey) OVER (PARTITION BY abs(o_custkey)
ORDER BY o_custkey
RANGE BETWEEN 15 FOLLOWING
AND 15 FOLLOWING)
-242 NULL
238 NULL
+242 NULL
DROP table orders;
#
# MDEV-10842: window functions with the same order column
@@ -2042,20 +2038,20 @@ row_number() over (order by pk desc) as r_desc,
row_number() over (order by pk asc) as r_asc
from t1;
pk r_desc r_asc
-1 11 1
-2 10 2
-3 9 3
-4 8 4
-5 7 5
-6 6 6
-7 5 7
-8 4 8
-9 3 9
-10 2 10
11 1 11
+10 2 10
+9 3 9
+8 4 8
+7 5 7
+6 6 6
+5 7 5
+4 8 4
+3 9 3
+2 10 2
+1 11 1
drop table t1;
#
-# MDEV-10874: two window functions with ccompatible sorting
+# MDEV-10874: two window functions with compatible sorting
#
create table t1 (
pk int primary key,
@@ -2130,17 +2126,17 @@ sum(d) over (order by a
ROWS BETWEEN 1 preceding and 2 following) as sum_2
from t1;
pk a d sum_1 sum_2
-1 0 0.100 0.300 1.400
-2 0 0.200 0.500 1.200
3 0 0.300 0.300 0.600
-4 1 0.400 0.900 2.600
-5 1 0.500 1.100 2.400
+2 0 0.200 0.500 1.200
+1 0 0.100 0.300 1.400
6 1 0.600 0.600 1.600
-7 2 0.500 0.500 0.500
-8 2 NULL 0.700 1.200
-9 2 0.700 1.500 2.000
-10 2 0.800 1.700 2.400
+5 1 0.500 1.100 2.400
+4 1 0.400 0.900 2.600
11 2 0.900 0.900 2.800
+10 2 0.800 1.700 2.400
+9 2 0.700 1.500 2.000
+8 2 NULL 0.700 1.200
+7 2 0.500 0.500 0.500
drop table t1;
#
# MDEV-9941: two window functions with compatible partitions
@@ -2232,9 +2228,9 @@ SELECT sum(t.a) over (partition by t.b order by a),
sqrt(ifnull((sum(t.a) over (partition by t.b order by a)), 0))
from t;
sum(t.a) over (partition by t.b order by a) sqrt(ifnull((sum(t.a) over (partition by t.b order by a)), 0))
+0.0000000000 0
1.0000000000 1
3.0000000000 1.7320508075688772
-0.0000000000 0
drop table t;
#
# MDEV-10868: view definitions with window functions
@@ -2391,9 +2387,9 @@ CREATE TABLE t2 (c VARCHAR(8));
INSERT INTO t2 VALUES ('foo'),('bar'),('foo');
SELECT COUNT(*) OVER (PARTITION BY c) FROM t2;
COUNT(*) OVER (PARTITION BY c)
-2
1
2
+2
SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
i
1
@@ -2511,14 +2507,14 @@ select id, rank() over (order by id) from t1;
id rank() over (order by id)
1 1
2 2
-3 4
2 2
+3 4
select * from v1;
id rnk
1 1
2 2
-3 4
2 2
+3 4
drop view v1;
drop table t1;
#
@@ -2879,64 +2875,64 @@ CDEC min(TDEC.CDEC) over ()
NULL -1
-1 -1
0 -1
-1 -1
0 -1
+1 -1
10 -1
select VDEC.CDEC, min(VDEC.CDEC) over () from VDEC;
CDEC min(VDEC.CDEC) over ()
NULL -1
-1 -1
0 -1
-1 -1
0 -1
+1 -1
10 -1
select TDEC.CDEC, max(TDEC.CDEC) over () from TDEC;
CDEC max(TDEC.CDEC) over ()
NULL 10
-1 10
0 10
-1 10
0 10
+1 10
10 10
select VDEC.CDEC, max(VDEC.CDEC) over () from VDEC;
CDEC max(VDEC.CDEC) over ()
NULL 10
-1 10
0 10
-1 10
0 10
+1 10
10 10
select TDEC.CDEC, min(distinct TDEC.CDEC) over () from TDEC;
CDEC min(distinct TDEC.CDEC) over ()
NULL -1
-1 -1
0 -1
-1 -1
0 -1
+1 -1
10 -1
select VDEC.CDEC, min(distinct VDEC.CDEC) over () from VDEC;
CDEC min(distinct VDEC.CDEC) over ()
NULL -1
-1 -1
0 -1
-1 -1
0 -1
+1 -1
10 -1
select TDEC.CDEC, max(distinct TDEC.CDEC) over () from TDEC;
CDEC max(distinct TDEC.CDEC) over ()
NULL 10
-1 10
0 10
-1 10
0 10
+1 10
10 10
select VDEC.CDEC, max(distinct VDEC.CDEC) over () from VDEC;
CDEC max(distinct VDEC.CDEC) over ()
NULL 10
-1 10
0 10
-1 10
0 10
+1 10
10 10
#
# These should be removed once support for them is added.
@@ -3327,6 +3323,45 @@ BIT_AND(0) OVER () MAX(1)
0 1
drop table t1;
#
+# MDEV-17525: Window functions not working in ONLY_FULL_GROUP_BY mode
+#
+CREATE TABLE t1 (name CHAR(10), test CHAR(10), score TINYINT);
+INSERT INTO t1 VALUES
+('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
+('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
+('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
+('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
+SET @save_sql_mode= @@sql_mode;
+SET sql_mode = 'ONLY_FULL_GROUP_BY';
+SELECT name, test, score,
+AVG(score) OVER (PARTITION BY test) AS average_by_test
+FROM t1
+ORDER BY test, name;
+name test score average_by_test
+Chun SQL 75 65.2500
+Esben SQL 43 65.2500
+Kaolin SQL 56 65.2500
+Tatiana SQL 87 65.2500
+Chun Tuning 73 68.7500
+Esben Tuning 31 68.7500
+Kaolin Tuning 88 68.7500
+Tatiana Tuning 83 68.7500
+set @@sql_mode= @save_sql_mode;
+SELECT name, test, score,
+AVG(score) OVER (PARTITION BY test) AS average_by_test
+FROM t1
+ORDER BY test, name;
+name test score average_by_test
+Chun SQL 75 65.2500
+Esben SQL 43 65.2500
+Kaolin SQL 56 65.2500
+Tatiana SQL 87 65.2500
+Chun Tuning 73 68.7500
+Esben Tuning 31 68.7500
+Kaolin Tuning 88 68.7500
+Tatiana Tuning 83 68.7500
+drop table t1;
+#
# Start of 10.3 tests
#
#
View Lorry Controller Status