Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 217 insertions, 0 deletions

diff --git a/mysql-test/main/win_ntile.test b/mysql-test/main/win_ntile.test
new file mode 100644
index 00000000..c508b162
--- /dev/null
+++ b/mysql-test/main/win_ntile.test
@@ -0,0 +1,217 @@
+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);
+
+# TODO Try invalid queries too.
+
+--error ER_INVALID_NTILE_ARGUMENT
+select pk, a, b, ntile(-1) over (order by a)
+from t1;
+
+--error ER_INVALID_NTILE_ARGUMENT
+select pk, a, b,
+    ntile(0) over (order by a)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(1) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(2) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(3) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(4) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(5) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(6) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(7) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(8) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(9) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(10) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(11) over (order by pk)
+from t1;
+
+--sorted_result
+select pk, a, b,
+    ntile(20) over (order by pk)
+from t1;
+
+
+select pk, a, b,
+    ntile(1) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(2) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(3) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(4) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(5) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(6) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(7) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(8) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(9) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(10) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(11) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(20) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile(1 + 3) over (partition by b order by pk)
+from t1;
+
+select pk, a, b,
+    ntile((select 4)) over (partition by b order by pk)
+from t1;
+
+select t1.a from t1 where pk = 11;
+--error ER_INVALID_NTILE_ARGUMENT
+select pk, a, b,
+    ntile((select a from t1 where pk=11)) over (partition by b order by pk)
+from t1;
+
+select t1.a from t1 where pk = 13;
+#enable after fix MDEV-27871
+--disable_view_protocol
+select pk, a, b,
+    ntile((select a from t1 where pk=13)) over (partition by b order by pk)
+from t1;
+
+explain
+select pk, a, b,
+    ntile((select a from t1 where pk=13)) over (partition by b order by pk)
+from t1;
+--enable_view_protocol
+
+select a from t1;
+--error ER_SUBQUERY_NO_1_ROW
+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, '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;