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

3 files changed, 170 insertions, 3 deletions

diff --git a/mysql-test/r/win_ntile.result b/mysql-test/r/win_ntile.result
index 41cb1a594bf..6af9fc358e5 100644
--- a/mysql-test/r/win_ntile.result
+++ b/mysql-test/r/win_ntile.result
@@ -433,3 +433,112 @@ 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, 'Ron', '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, 'Ron', 'Male', 5);
+# Correct usage of NTILE function with a constant argument NTILE(6).
+select c1, c2, c3, ntile(6) over (partition by c2 order by c3) from t1;
+c1	c2	c3	ntile(6) over (partition by c2 order by c3)
+Jodi	Female	6	1
+Jodi	Female	6	1
+Mary	Female	6	2
+Pam	Female	6	2
+Mary	Female	6	3
+Pam	Female	6	4
+Sara	Female	6	5
+Sara	Female	6	6
+Todd	Male	5	1
+John	Male	5	1
+Todd	Male	5	2
+John	Male	5	2
+Tom	Male	5	3
+Tom	Male	5	3
+Ron	Male	5	4
+Ben	Male	5	4
+Ron	Male	5	5
+Mark	Male	5	5
+Ben	Male	5	6
+Mark	Male	5	6
+# Correct usage of NTILE function with a constant argument per partition.
+select c1, c2, c3, ntile(c3) over (partition by c2 order by c3) from t1;
+c1	c2	c3	ntile(c3) over (partition by c2 order by c3)
+Jodi	Female	6	1
+Sara	Female	6	1
+Pam	Female	6	2
+Mary	Female	6	2
+Jodi	Female	6	3
+Sara	Female	6	4
+Pam	Female	6	5
+Mary	Female	6	6
+Tom	Male	5	1
+John	Male	5	1
+Todd	Male	5	1
+Mark	Male	5	2
+Ben	Male	5	2
+Ron	Male	5	2
+Tom	Male	5	3
+John	Male	5	3
+Todd	Male	5	4
+Mark	Male	5	4
+Ben	Male	5	5
+Ron	Male	5	5
+select c1, c2, c3, ntile(c3) over (partition by c1 order by c2) from t1;
+c1	c2	c3	ntile(c3) over (partition by c1 order by c2)
+Ben	Male	5	1
+Ben	Male	5	2
+Jodi	Female	6	1
+Jodi	Female	6	2
+John	Male	5	1
+John	Male	5	2
+Mark	Male	5	1
+Mark	Male	5	2
+Mary	Female	6	1
+Mary	Female	6	2
+Pam	Female	6	1
+Pam	Female	6	2
+Ron	Male	5	1
+Ron	Male	5	2
+Sara	Female	6	1
+Sara	Female	6	2
+Todd	Male	5	1
+Todd	Male	5	2
+Tom	Male	5	1
+Tom	Male	5	2
+update t1
+set c3= 1
+where pk <= 10;
+# We save NTILE argument(N) and compare it to the following N.
+# If N isn't equal with the old argument N - we stop with an error.
+# Currently used an old error:Argument of NTILE must be greater than 0
+select c1, c2, c3, ntile(c3) over (partition by c2 order by c3) from t1;
+ERROR HY000: Argument of NTILE must be greater than 0
+select c1, c2, c3, ntile(c3) over (partition by c1 order by c2) 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..f0203b01e50 100644
--- a/mysql-test/t/win_ntile.test
+++ b/mysql-test/t/win_ntile.test
@@ -167,5 +167,57 @@ select pk, a, b,
     ntile((select a from t1)) over (partition by b order by pk)
 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, 'Ron', '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, 'Ron', 'Male', 5);
+--echo # Correct usage of NTILE function with a constant argument NTILE(6).
+select c1, c2, c3, ntile(6) over (partition by c2 order by c3) from t1;
+--echo # Correct usage of NTILE function with a constant argument per partition.
+select c1, c2, c3, ntile(c3) over (partition by c2 order by c3) from t1;
+select c1, c2, c3, ntile(c3) over (partition by c1 order by c2) from t1;
+
+update t1
+  set c3= 1
+  where pk <= 10;
+
+--echo # We save NTILE argument(N) and compare it to the following N.
+--echo # If N isn't equal with the old argument N - we stop with an error.
+--echo # Currently used an old error:Argument of NTILE must be greater than 0
+--error ER_INVALID_NTILE_ARGUMENT
+select c1, c2, c3, ntile(c3) over (partition by c2 order by c3) from t1;
+
+--error ER_INVALID_NTILE_ARGUMENT
+select c1, c2, c3, ntile(c3) over (partition by c1 order by c2) from t1;
 
 drop table t1;
diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h
index b9df1b7482b..37d2786f800 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()
   {
@@ -632,10 +633,11 @@ class Item_sum_ntile : public Item_sum_window_with_row_count
       null_value= true;
       return 0;
     }
-
+    
     longlong num_quantiles= get_num_quantiles();
 
-    if (num_quantiles <= 0) {
+    if (num_quantiles <= 0 || (n_old_val_ > 0 && n_old_val_ != num_quantiles))
+    {
       my_error(ER_INVALID_NTILE_ARGUMENT, MYF(0));
       return true;
     }
@@ -644,6 +646,8 @@ class Item_sum_ntile : public Item_sum_window_with_row_count
     ulonglong quantile_size = get_row_count() / num_quantiles;
     ulonglong extra_rows = get_row_count() - quantile_size * num_quantiles;
 
+    n_old_val_= num_quantiles;
+
     if (current_row_count_ <= extra_rows * (quantile_size + 1))
       return (current_row_count_ - 1) / (quantile_size + 1) + 1;
 
@@ -665,6 +669,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 +688,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_;
+  longlong n_old_val_;
 };