MDEV-9911: NTILE must return an error when parameter is not stablebb-10.2-anel-ntile

3 files changed, 129 insertions, 3 deletions

diff --git a/mysql-test/r/win_ntile.result b/mysql-test/r/win_ntile.result
index 41cb1a594bf..4d02a230e13 100644
--- a/mysql-test/r/win_ntile.result
+++ b/mysql-test/r/win_ntile.result
@@ -433,3 +433,81 @@ ntile((select a from t1)) over (partition by b order by pk)
 from t1;
 ERROR 21000: Subquery returns more than 1 row
 drop table t1;
+#
+# MDEV-9911 NTILE must return an error when parameter is not stable
+#
+create table t1 (
+pk int primary key,
+c1 nvarchar(10),
+c2 nvarchar(10),
+c3 int
+);
+insert into t1 values
+(1, 'Mark', 'Male', 5),
+(2, 'John', 'Male', 5),
+(3, 'Pam', 'Female', 6),
+(4, 'Sara', 'Female', 6),
+(5, 'Todd', 'Male', 5),
+(6, 'Mary', 'Female', 6),
+(7, 'Ben', 'Male', 5),
+(8, 'Jodi', 'Female', 6),
+(9, 'Tom', 'Male', 5),
+(10, 'Lucky', 'Male', 5),
+(11, 'Mark', 'Male', 5),
+(12, 'John', 'Male', 5),
+(13, 'Pam', 'Female', 6),
+(14, 'Sara', 'Female', 6),
+(15, 'Todd', 'Male', 5),
+(16, 'Mary', 'Female', 6),
+(17, 'Ben', 'Male', 5),
+(18, 'Jodi', 'Female', 6),
+(19, 'Tom', 'Male', 5),
+(20, 'Lucky', 'Male', 5);
+select c1, c2, c3, ntile(6) over (partition by c2 order by pk) from t1;
+c1	c2	c3	ntile(6) over (partition by c2 order by pk)
+Pam	Female	6	1
+Sara	Female	6	1
+Mary	Female	6	2
+Jodi	Female	6	2
+Pam	Female	6	3
+Sara	Female	6	4
+Mary	Female	6	5
+Jodi	Female	6	6
+Mark	Male	5	1
+John	Male	5	1
+Todd	Male	5	2
+Ben	Male	5	2
+Tom	Male	5	3
+Lucky	Male	5	3
+Mark	Male	5	4
+John	Male	5	4
+Todd	Male	5	5
+Ben	Male	5	5
+Tom	Male	5	6
+Lucky	Male	5	6
+select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1;
+c1	c2	c3	ntile(c3) over (partition by c2 order by pk)
+Pam	Female	6	1
+Sara	Female	6	1
+Mary	Female	6	2
+Jodi	Female	6	2
+Pam	Female	6	3
+Sara	Female	6	4
+Mary	Female	6	5
+Jodi	Female	6	6
+Mark	Male	5	1
+John	Male	5	1
+Todd	Male	5	1
+Ben	Male	5	2
+Tom	Male	5	2
+Lucky	Male	5	2
+Mark	Male	5	3
+John	Male	5	3
+Todd	Male	5	4
+Ben	Male	5	4
+Tom	Male	5	5
+Lucky	Male	5	5
+update t1 set c3= 1 where pk = 1;
+select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1;
+ERROR HY000: Argument of NTILE must be greater than 0
+drop table t1;
diff --git a/mysql-test/t/win_ntile.test b/mysql-test/t/win_ntile.test
index 6f12e1f4005..b4d60c11749 100644
--- a/mysql-test/t/win_ntile.test
+++ b/mysql-test/t/win_ntile.test
@@ -169,3 +169,46 @@ from t1;
 
 
 drop table t1;
+
+--echo #
+--echo # MDEV-9911 NTILE must return an error when parameter is not stable
+--echo #
+
+create table t1 (
+  pk int primary key,
+  c1 nvarchar(10),
+  c2 nvarchar(10),
+  c3 int
+);
+
+insert into t1 values
+ (1, 'Mark', 'Male', 5),
+ (2, 'John', 'Male', 5),
+ (3, 'Pam', 'Female', 6),
+ (4, 'Sara', 'Female', 6),
+ (5, 'Todd', 'Male', 5),
+ (6, 'Mary', 'Female', 6),
+ (7, 'Ben', 'Male', 5),
+ (8, 'Jodi', 'Female', 6),
+ (9, 'Tom', 'Male', 5),
+ (10, 'Lucky', 'Male', 5),
+ (11, 'Mark', 'Male', 5),
+ (12, 'John', 'Male', 5),
+ (13, 'Pam', 'Female', 6),
+ (14, 'Sara', 'Female', 6),
+ (15, 'Todd', 'Male', 5),
+ (16, 'Mary', 'Female', 6),
+ (17, 'Ben', 'Male', 5),
+ (18, 'Jodi', 'Female', 6),
+ (19, 'Tom', 'Male', 5),
+ (20, 'Lucky', 'Male', 5);
+
+# Correct usage of NTILE with a fix argument NTILE(6).
+select c1, c2, c3, ntile(6) over (partition by c2 order by pk) from t1;
+# Correct usage - constant NTILE (argument) in each partition.
+select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1;
+update t1 set c3= 1 where pk = 1;
+--error ER_INVALID_NTILE_ARGUMENT
+select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1;
+
+drop table t1;
diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h
index 85957949053..e0574d073ee 100644
--- a/sql/item_windowfunc.h
+++ b/sql/item_windowfunc.h
@@ -618,7 +618,8 @@ class Item_sum_ntile : public Item_sum_window_with_row_count
  public:
   Item_sum_ntile(THD* thd, Item* num_quantiles_expr) :
     Item_sum_window_with_row_count(thd, num_quantiles_expr),
-    current_row_count_(0) {};
+    current_row_count_(0),
+    n_old_val_(0) {};
 
   double val_real()
   {
@@ -635,11 +636,13 @@ class Item_sum_ntile : public Item_sum_window_with_row_count
 
     longlong num_quantiles= get_num_quantiles();
 
-    if (num_quantiles <= 0) {
+    if (num_quantiles <= 0 ||
+        (static_cast<ulonglong>(num_quantiles) != n_old_val_ && n_old_val_ > 0))
+    {
       my_error(ER_INVALID_NTILE_ARGUMENT, MYF(0));
       return true;
     }
-
+    n_old_val_= static_cast<ulonglong>(num_quantiles);
     null_value= false;
     ulonglong quantile_size = get_row_count() / num_quantiles;
     ulonglong extra_rows = get_row_count() - quantile_size * num_quantiles;
@@ -665,6 +668,7 @@ class Item_sum_ntile : public Item_sum_window_with_row_count
   {
     current_row_count_= 0;
     set_row_count(0);
+    n_old_val_= 0;
   }
 
   const char*func_name() const
@@ -683,6 +687,7 @@ class Item_sum_ntile : public Item_sum_window_with_row_count
  private:
   longlong get_num_quantiles() { return args[0]->val_int(); }
   ulong current_row_count_;
+  ulonglong n_old_val_;
 };