1create table t1 (
2  pk int primary key,
3  a int,
4  b int
5);
6
7
8insert into t1 values
9(11 , 0, 10),
10(12 , 0, 10),
11(13 , 1, 10),
12(14 , 1, 10),
13(18 , 2, 10),
14(15 , 2, 20),
15(16 , 2, 20),
16(17 , 2, 20),
17(19 , 4, 20),
18(20 , 4, 20);
19
20# TODO Try invalid queries too.
21
22--error ER_INVALID_NTILE_ARGUMENT
23select pk, a, b, ntile(-1) over (order by a)
24from t1;
25
26--error ER_INVALID_NTILE_ARGUMENT
27select pk, a, b,
28    ntile(0) over (order by a)
29from t1;
30
31--sorted_result
32select pk, a, b,
33    ntile(1) over (order by pk)
34from t1;
35
36--sorted_result
37select pk, a, b,
38    ntile(2) over (order by pk)
39from t1;
40
41--sorted_result
42select pk, a, b,
43    ntile(3) over (order by pk)
44from t1;
45
46--sorted_result
47select pk, a, b,
48    ntile(4) over (order by pk)
49from t1;
50
51--sorted_result
52select pk, a, b,
53    ntile(5) over (order by pk)
54from t1;
55
56--sorted_result
57select pk, a, b,
58    ntile(6) over (order by pk)
59from t1;
60
61--sorted_result
62select pk, a, b,
63    ntile(7) over (order by pk)
64from t1;
65
66--sorted_result
67select pk, a, b,
68    ntile(8) over (order by pk)
69from t1;
70
71--sorted_result
72select pk, a, b,
73    ntile(9) over (order by pk)
74from t1;
75
76--sorted_result
77select pk, a, b,
78    ntile(10) over (order by pk)
79from t1;
80
81--sorted_result
82select pk, a, b,
83    ntile(11) over (order by pk)
84from t1;
85
86--sorted_result
87select pk, a, b,
88    ntile(20) over (order by pk)
89from t1;
90
91
92select pk, a, b,
93    ntile(1) over (partition by b order by pk)
94from t1;
95
96select pk, a, b,
97    ntile(2) over (partition by b order by pk)
98from t1;
99
100select pk, a, b,
101    ntile(3) over (partition by b order by pk)
102from t1;
103
104select pk, a, b,
105    ntile(4) over (partition by b order by pk)
106from t1;
107
108select pk, a, b,
109    ntile(5) over (partition by b order by pk)
110from t1;
111
112select pk, a, b,
113    ntile(6) over (partition by b order by pk)
114from t1;
115
116select pk, a, b,
117    ntile(7) over (partition by b order by pk)
118from t1;
119
120select pk, a, b,
121    ntile(8) over (partition by b order by pk)
122from t1;
123
124select pk, a, b,
125    ntile(9) over (partition by b order by pk)
126from t1;
127
128select pk, a, b,
129    ntile(10) over (partition by b order by pk)
130from t1;
131
132select pk, a, b,
133    ntile(11) over (partition by b order by pk)
134from t1;
135
136select pk, a, b,
137    ntile(20) over (partition by b order by pk)
138from t1;
139
140select pk, a, b,
141    ntile(1 + 3) over (partition by b order by pk)
142from t1;
143
144select pk, a, b,
145    ntile((select 4)) over (partition by b order by pk)
146from t1;
147
148select t1.a from t1 where pk = 11;
149--error ER_INVALID_NTILE_ARGUMENT
150select pk, a, b,
151    ntile((select a from t1 where pk=11)) over (partition by b order by pk)
152from t1;
153
154select t1.a from t1 where pk = 13;
155select pk, a, b,
156    ntile((select a from t1 where pk=13)) over (partition by b order by pk)
157from t1;
158
159explain
160select pk, a, b,
161    ntile((select a from t1 where pk=13)) over (partition by b order by pk)
162from t1;
163
164select a from t1;
165--error ER_SUBQUERY_NO_1_ROW
166select pk, a, b,
167    ntile((select a from t1)) over (partition by b order by pk)
168from t1;
169
170
171drop table t1;
172
173--echo #
174--echo # MDEV-9911 NTILE must return an error when parameter is not stable
175--echo #
176
177create table t1 (
178  pk int primary key,
179  c1 nvarchar(10),
180  c2 nvarchar(10),
181  c3 int
182);
183
184insert into t1 values
185 (1, 'Mark', 'Male', 5),
186 (2, 'John', 'Male', 5),
187 (3, 'Pam', 'Female', 6),
188 (4, 'Sara', 'Female', 6),
189 (5, 'Todd', 'Male', 5),
190 (6, 'Mary', 'Female', 6),
191 (7, 'Ben', 'Male', 5),
192 (8, 'Jodi', 'Female', 6),
193 (9, 'Tom', 'Male', 5),
194 (10, 'Lucky', 'Male', 5),
195 (11, 'Mark', 'Male', 5),
196 (12, 'John', 'Male', 5),
197 (13, 'Pam', 'Female', 6),
198 (14, 'Sara', 'Female', 6),
199 (15, 'Todd', 'Male', 5),
200 (16, 'Mary', 'Female', 6),
201 (17, 'Ben', 'Male', 5),
202 (18, 'Jodi', 'Female', 6),
203 (19, 'Tom', 'Male', 5),
204 (20, 'Lucky', 'Male', 5);
205# Correct usage of NTILE with a fix argument NTILE(6).
206select c1, c2, c3, ntile(6) over (partition by c2 order by pk) from t1;
207# Correct usage - constant NTILE (argument) in each partition.
208select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1;
209
210update t1 set c3= 1 where pk = 1;
211--error ER_INVALID_NTILE_ARGUMENT
212select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1;
213
214drop table t1;
215