Implement ntile window function.

The current implementation does not allow for a dynamic expression
within the sum function's parameter.

1 files changed, 360 insertions, 0 deletions

diff --git a/mysql-test/r/win_ntile.result b/mysql-test/r/win_ntile.result
new file mode 100644
index 00000000000..c403e2937e6
--- /dev/null
+++ b/mysql-test/r/win_ntile.result
@@ -0,0 +1,360 @@
+create table t1 (
+pk int primary key,
+a int,
+b int
+);
+insert into t1 values
+(11 , 0, 10),
+(12 , 0, 10),
+(13 , 1, 10),
+(14 , 1, 10),
+(18 , 2, 10),
+(15 , 2, 20),
+(16 , 2, 20),
+(17 , 2, 20),
+(19 , 4, 20),
+(20 , 4, 20);
+select pk, a, b, ntile(-1) over (order by a)
+from t1;
+ERROR HY000: Argument of NTILE must be greater than 0
+select pk, a, b,
+ntile(0) over (order by a)
+from t1;
+ERROR HY000: Argument of NTILE must be greater than 0
+select pk, a, b,
+ntile(1) over (order by pk)
+from t1;
+pk	a	b	ntile(1) over (order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	1
+14	1	10	1
+15	2	20	1
+16	2	20	1
+17	2	20	1
+18	2	10	1
+19	4	20	1
+20	4	20	1
+select pk, a, b,
+ntile(2) over (order by pk)
+from t1;
+pk	a	b	ntile(2) over (order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	1
+14	1	10	1
+15	2	20	1
+16	2	20	2
+17	2	20	2
+18	2	10	2
+19	4	20	2
+20	4	20	2
+select pk, a, b,
+ntile(3) over (order by pk)
+from t1;
+pk	a	b	ntile(3) over (order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	1
+14	1	10	1
+15	2	20	2
+16	2	20	2
+17	2	20	2
+18	2	10	3
+19	4	20	3
+20	4	20	3
+select pk, a, b,
+ntile(4) over (order by pk)
+from t1;
+pk	a	b	ntile(4) over (order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	1
+14	1	10	2
+15	2	20	2
+16	2	20	2
+17	2	20	3
+18	2	10	3
+19	4	20	4
+20	4	20	4
+select pk, a, b,
+ntile(5) over (order by pk)
+from t1;
+pk	a	b	ntile(5) over (order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	2
+14	1	10	2
+15	2	20	3
+16	2	20	3
+17	2	20	4
+18	2	10	4
+19	4	20	5
+20	4	20	5
+select pk, a, b,
+ntile(6) over (order by pk)
+from t1;
+pk	a	b	ntile(6) over (order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	2
+14	1	10	2
+15	2	20	3
+16	2	20	3
+17	2	20	4
+18	2	10	4
+19	4	20	5
+20	4	20	6
+select pk, a, b,
+ntile(7) over (order by pk)
+from t1;
+pk	a	b	ntile(7) over (order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	2
+14	1	10	2
+15	2	20	3
+16	2	20	3
+17	2	20	4
+18	2	10	5
+19	4	20	6
+20	4	20	7
+select pk, a, b,
+ntile(8) over (order by pk)
+from t1;
+pk	a	b	ntile(8) over (order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	2
+14	1	10	2
+15	2	20	3
+16	2	20	4
+17	2	20	5
+18	2	10	6
+19	4	20	7
+20	4	20	8
+select pk, a, b,
+ntile(9) over (order by pk)
+from t1;
+pk	a	b	ntile(9) over (order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	2
+14	1	10	3
+15	2	20	4
+16	2	20	5
+17	2	20	6
+18	2	10	7
+19	4	20	8
+20	4	20	9
+select pk, a, b,
+ntile(10) over (order by pk)
+from t1;
+pk	a	b	ntile(10) over (order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+15	2	20	5
+16	2	20	6
+17	2	20	7
+18	2	10	8
+19	4	20	9
+20	4	20	10
+select pk, a, b,
+ntile(11) over (order by pk)
+from t1;
+pk	a	b	ntile(11) over (order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+15	2	20	5
+16	2	20	6
+17	2	20	7
+18	2	10	8
+19	4	20	9
+20	4	20	10
+select pk, a, b,
+ntile(20) over (order by pk)
+from t1;
+pk	a	b	ntile(20) over (order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+15	2	20	5
+16	2	20	6
+17	2	20	7
+18	2	10	8
+19	4	20	9
+20	4	20	10
+select pk, a, b,
+ntile(1) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(1) over (partition by b order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	1
+14	1	10	1
+18	2	10	1
+15	2	20	1
+16	2	20	1
+17	2	20	1
+19	4	20	1
+20	4	20	1
+select pk, a, b,
+ntile(2) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(2) over (partition by b order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	1
+14	1	10	2
+18	2	10	2
+15	2	20	1
+16	2	20	1
+17	2	20	1
+19	4	20	2
+20	4	20	2
+select pk, a, b,
+ntile(3) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(3) over (partition by b order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	2
+14	1	10	2
+18	2	10	3
+15	2	20	1
+16	2	20	1
+17	2	20	2
+19	4	20	2
+20	4	20	3
+select pk, a, b,
+ntile(4) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(4) over (partition by b order by pk)
+11	0	10	1
+12	0	10	1
+13	1	10	2
+14	1	10	3
+18	2	10	4
+15	2	20	1
+16	2	20	1
+17	2	20	2
+19	4	20	3
+20	4	20	4
+select pk, a, b,
+ntile(5) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(5) over (partition by b order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+18	2	10	5
+15	2	20	1
+16	2	20	2
+17	2	20	3
+19	4	20	4
+20	4	20	5
+select pk, a, b,
+ntile(6) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(6) over (partition by b order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+18	2	10	5
+15	2	20	1
+16	2	20	2
+17	2	20	3
+19	4	20	4
+20	4	20	5
+select pk, a, b,
+ntile(7) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(7) over (partition by b order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+18	2	10	5
+15	2	20	1
+16	2	20	2
+17	2	20	3
+19	4	20	4
+20	4	20	5
+select pk, a, b,
+ntile(8) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(8) over (partition by b order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+18	2	10	5
+15	2	20	1
+16	2	20	2
+17	2	20	3
+19	4	20	4
+20	4	20	5
+select pk, a, b,
+ntile(9) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(9) over (partition by b order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+18	2	10	5
+15	2	20	1
+16	2	20	2
+17	2	20	3
+19	4	20	4
+20	4	20	5
+select pk, a, b,
+ntile(10) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(10) over (partition by b order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+18	2	10	5
+15	2	20	1
+16	2	20	2
+17	2	20	3
+19	4	20	4
+20	4	20	5
+select pk, a, b,
+ntile(11) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(11) over (partition by b order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+18	2	10	5
+15	2	20	1
+16	2	20	2
+17	2	20	3
+19	4	20	4
+20	4	20	5
+select pk, a, b,
+ntile(20) over (partition by b order by pk)
+from t1;
+pk	a	b	ntile(20) over (partition by b order by pk)
+11	0	10	1
+12	0	10	2
+13	1	10	3
+14	1	10	4
+18	2	10	5
+15	2	20	1
+16	2	20	2
+17	2	20	3
+19	4	20	4
+20	4	20	5
+drop table t1;