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