Merge branch '10.2' into 10.2-mdev9864

1 files changed, 1203 insertions, 0 deletions

diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
new file mode 100644
index 00000000000..09ddf41b4f0
--- /dev/null
+++ b/mysql-test/t/win.test
@@ -0,0 +1,1203 @@
+#
+#  Window Functions Tests
+#
+
+--disable_warnings
+drop table if exists t1,t2;
+drop view if exists v1;
+--enable_warnings
+
+--echo # ########################################################################
+--echo # # Parser tests
+--echo # ########################################################################
+--echo #
+--echo #  Check what happens when one attempts to use window function without OVER clause 
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2);
+
+--error ER_PARSE_ERROR
+select row_number() from t1;
+--error ER_PARSE_ERROR
+select rank() from t1;
+
+--echo # Attempt to use window function in the WHERE clause
+--error ER_WRONG_PLACEMENT_OF_WINDOW_FUNCTION 
+select * from t1 where 1=rank() over (order by a);
+--error ER_WRONG_PLACEMENT_OF_WINDOW_FUNCTION
+select * from t1 where 1>row_number() over (partition by b order by a);
+drop table t1;
+
+--echo # ########################################################################
+--echo # # Functionality tests
+--echo # ########################################################################
+--echo #
+--echo #  Check if ROW_NUMBER() works in basic cases
+create table t1(a int, b int, x char(32));
+insert into t1 values (2, 10, 'xx');
+insert into t1 values (2, 10, 'zz');
+insert into t1 values (2, 20, 'yy');
+insert into t1 values (3, 10, 'xxx');
+insert into t1 values (3, 20, 'vvv');
+
+--sorted_result
+select a, row_number() over (partition by a order by b) from t1;
+
+select a, b, x, row_number() over (partition by a order by x) from t1;
+
+drop table t1;
+
+create table t1 (pk int primary key, a int, b int);
+insert into t1 values 
+  (1, 10, 22),
+  (2, 11, 21),
+  (3, 12, 20),
+  (4, 13, 19),
+  (5, 14, 18);
+
+select 
+  pk, a, b, 
+  row_number() over (order by a), 
+  row_number() over (order by b)
+from t1;
+
+drop table t1;
+
+--echo #
+--echo # Try RANK() function
+--echo #
+create table t2 (
+  pk int primary key, 
+  a int
+);
+
+insert into t2 values
+( 1 , 0),
+( 2 , 0),
+( 3 , 1),
+( 4 , 1),
+( 8 , 2),
+( 5 , 2),
+( 6 , 2),
+( 7 , 2),
+( 9 , 4),
+(10 , 4);
+
+select pk, a, rank() over (order by a) from t2;
+select pk, a, rank() over (order by a desc) from t2;
+
+drop table t2;
+
+--echo #
+--echo # Try Aggregates as window functions. With frames.
+--echo #
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+
+select 
+  pk, c, 
+  count(*) over (partition by c order by pk 
+                 rows between 2 preceding and 2 following) as CNT
+from t1;
+
+select 
+  pk, c, 
+  count(*) over (partition by c order by pk 
+                 rows between 1 preceding and 2 following) as CNT
+from t1;
+
+select
+  pk, c,  
+  count(*) over (partition by c order by pk
+                 rows between 2 preceding and current row) as CNT 
+from t1;
+
+select 
+  pk,c, 
+  count(*) over (partition by c order by pk rows
+                 between 1 following and 2 following) as CNT
+from t1;
+
+select 
+  pk,c, 
+  count(*) over (partition by c order by pk rows
+                 between 2 preceding and 1 preceding) as CNT
+from t1;
+
+select
+  pk, c,  
+  count(*) over (partition by c order by pk
+                 rows between current row and 1 following) as CNT 
+from t1;
+
+--echo # Check ORDER BY DESC
+select 
+  pk, c, 
+  count(*) over (partition by c order by pk desc
+                 rows between 2 preceding and 2 following) as CNT
+from t1;
+
+drop table t0,t1;
+
+--echo #
+--echo # Resolution of window names
+--echo #
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+
+select 
+  pk, c,
+  count(*) over w1 as CNT
+from t1
+window w1 as (partition by c order by pk 
+              rows between 2 preceding and 2 following);
+
+select 
+  pk, c, 
+  count(*) over (w1 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c order by pk);
+
+select 
+  pk, c, 
+  count(*) over (w1 order by pk rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c);
+
+select 
+  pk, c, 
+  count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c), w2 as (w1 order by pk);
+
+select 
+  pk, c, 
+  count(*) over w3 as CNT
+from t1
+window
+  w1 as (partition by c), 
+  w2 as (w1 order by pk),
+  w3 as (w2 rows between 2 preceding and 2 following);
+
+--error ER_WRONG_WINDOW_SPEC_NAME
+select 
+  pk, c, 
+  count(*) over w as CNT
+from t1
+window w1 as (partition by c order by pk 
+              rows between 2 preceding and 2 following);
+
+--error ER_DUP_WINDOW_NAME
+select 
+  pk, c, 
+  count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c), w1 as (order by pk);
+
+--error ER_WRONG_WINDOW_SPEC_NAME
+select 
+  pk, c, 
+  count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c), w2 as (w partition by c order by pk);
+
+--error ER_PARTITION_LIST_IN_REFERENCING_WINDOW_SPEC
+select 
+  pk, c, 
+  count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c), w2 as (w1 partition by c order by pk);
+
+--error ER_ORDER_LIST_IN_REFERENCING_WINDOW_SPEC
+select 
+  pk, c, 
+  count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c order by pk), w2 as (w1 order by pk);
+
+--error ER_WINDOW_FRAME_IN_REFERENCED_WINDOW_SPEC
+select 
+  pk, c, 
+  count(*) over w3 as CNT
+from t1
+window
+  w1 as (partition by c), 
+  w2 as (w1 order by pk rows between 3 preceding and 2 following),
+  w3 as (w2 rows between 2 preceding and 2 following);
+
+--error ER_BAD_COMBINATION_OF_WINDOW_FRAME_BOUND_SPECS
+select 
+  pk, c, 
+  count(*) over w1 as CNT
+from t1
+window w1 as (partition by c order by pk 
+              rows between unbounded following and 2 following);
+
+--error ER_BAD_COMBINATION_OF_WINDOW_FRAME_BOUND_SPECS
+select 
+  pk, c, 
+  count(*) over (w1 rows between 2 preceding and unbounded preceding) as CNT
+from t1
+window w1 as (partition by c order by pk);
+
+--error ER_BAD_COMBINATION_OF_WINDOW_FRAME_BOUND_SPECS
+select 
+  pk, c, 
+  count(*) over (w1 order by pk rows between current row and 2 preceding) as CNT
+from t1
+window w1 as (partition by c);
+
+--error ER_BAD_COMBINATION_OF_WINDOW_FRAME_BOUND_SPECS
+select 
+  pk, c, 
+  count(*) over (w2 rows between 2 following and current row) as CNT
+from t1
+window w1 as (partition by c), w2 as (w1 order by pk);
+
+--error ER_WRONG_PLACEMENT_OF_WINDOW_FUNCTION 
+select 
+  pk, c 
+from t1 where rank() over w1 > 2
+window w1 as (partition by c order by pk);
+
+--error ER_WRONG_PLACEMENT_OF_WINDOW_FUNCTION 
+select
+  c, max(pk) as m
+from t1 
+  group by c + rank() over w1
+window w1 as (order by m);
+
+--error ER_WRONG_PLACEMENT_OF_WINDOW_FUNCTION 
+select
+  c, max(pk) as m, rank() over w1 as r 
+from t1 
+  group by c+r
+window w1 as (order by m);
+
+--error ER_WRONG_PLACEMENT_OF_WINDOW_FUNCTION 
+select
+  c, max(pk) as m, rank() over w1 as r
+from t1 
+  group by c having c+r > 3
+window w1 as (order by m);
+
+--error ER_WINDOW_FUNCTION_IN_WINDOW_SPEC 
+select
+  c, max(pk) as m, rank() over w1 as r, 
+    rank() over (partition by r+1 order by m) 
+from t1 
+  group by c
+window w1 as (order by m);
+
+--error ER_WINDOW_FUNCTION_IN_WINDOW_SPEC 
+select
+  c, max(pk) as m, rank() over w1 as r, 
+    rank() over (partition by m order by r) 
+from t1 
+  group by c
+window w1 as (order by m);
+
+--error ER_WINDOW_FUNCTION_IN_WINDOW_SPEC 
+select
+  c, max(pk) as m, rank() over w1 as r, dense_rank() over w2 as dr  
+from t1 
+  group by c
+window w1 as (order by m), w2 as (partition by r order by m);
+
+--error ER_NOT_ALLOWED_WINDOW_FRAME
+select 
+  pk, c, 
+  row_number() over (partition by c order by pk 
+                     range between unbounded preceding and current row) as r 
+from t1;
+
+--error ER_NOT_ALLOWED_WINDOW_FRAME
+select 
+  pk, c, 
+  rank() over w1 as r
+from t1
+window w1 as (partition by c order by pk 
+              rows between 2 preceding and 2 following);
+
+--error ER_NOT_ALLOWED_WINDOW_FRAME
+select 
+  pk, c, 
+  dense_rank() over (partition by c order by pk
+                    rows between 1 preceding and 1 following) as r
+from t1;
+
+--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
+select 
+  pk, c, 
+  rank() over w1 as r
+from t1
+window w1 as (partition by c);
+
+--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
+select 
+  pk, c, 
+  dense_rank() over (partition by c) as r
+from t1;
+
+drop table t0,t1;
+
+--echo #
+--echo # MDEV-9634: Window function produces incorrect value
+--echo #
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (part_id int, pk int, a int);
+insert into t2 select 
+  if(a<5, 0, 1), a, if(a<5, NULL, 1) from t0;
+select * from t2;
+
+select 
+  part_id, pk, a,
+  count(a) over (partition by part_id order by pk
+                 rows between 1 preceding and 1 following) as CNT
+from t2;
+
+drop table t0, t2;
+
+--echo #
+--echo #  RANGE-type bounds 
+--echo #
+
+create table t3 (
+  pk int, 
+  val int
+);
+
+insert into t3 values
+(0, 1),
+(1, 1),
+(2, 1),
+(3, 2),
+(4, 2),
+(5, 2),
+(6, 2);
+
+select 
+  pk, 
+  val,
+  count(val) over (order by val 
+                 range between current row and 
+                               current row) 
+  as CNT
+from t3;
+
+insert into t3 values 
+(7, 3),
+(8, 3);
+
+select 
+  pk, 
+  val,
+  count(val) over (order by val 
+                 range between current row and 
+                               current row) 
+  as CNT
+from t3;
+
+drop table t3;
+
+--echo # Now, check with PARTITION BY
+create table t4 (
+  part_id int,
+  pk int,
+  val int
+);
+
+insert into t4 values
+(1234, 100, 1),
+(1234, 101, 1),
+(1234, 102, 1),
+(1234, 103, 2),
+(1234, 104, 2),
+(1234, 105, 2),
+(1234, 106, 2),
+(1234, 107, 3),
+(1234, 108, 3),
+
+(5678, 200, 1),
+(5678, 201, 1),
+(5678, 202, 1),
+(5678, 203, 2),
+(5678, 204, 2),
+(5678, 205, 2),
+(5678, 206, 2),
+(5678, 207, 3),
+(5678, 208, 3);
+
+select 
+  part_id,
+  pk, 
+  val,
+  count(val) over (partition by part_id
+                   order by val 
+                   range between current row and 
+                               current row) 
+  as CNT
+from t4;
+
+--echo #
+--echo # Try RANGE UNBOUNDED PRECEDING | FOLLOWING
+--echo #
+select 
+  part_id,
+  pk, 
+  val,
+  count(val) over (partition by part_id
+                   order by val 
+                   range between unbounded preceding and 
+                               current row) 
+  as CNT
+from t4;
+
+select 
+  part_id,
+  pk, 
+  val,
+  count(val) over (partition by part_id
+                   order by val 
+                   range between current row and 
+                                 unbounded following) 
+  as CNT
+from t4;
+
+select 
+  part_id,
+  pk, 
+  val,
+  count(val) over (partition by part_id
+                   order by val 
+                   range between unbounded preceding and 
+                                 unbounded following) 
+  as CNT
+from t4;
+
+drop table t4;
+
+--echo #
+--echo # MDEV-9695: Wrong window frame when using RANGE BETWEEN N FOLLOWING AND PRECEDING
+--echo #
+create table t1 (pk int, a int, b int);
+insert into t1 values
+( 1 , 0, 1),
+( 2 , 0, 2),
+( 3 , 1, 4),
+( 4 , 1, 8),
+( 5 , 2, 32),
+( 6 , 2, 64),
+( 7 , 2, 128),
+( 8 , 2, 16);
+ 
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as bit_or
+from t1;
+
+--echo # Extra ROWS n PRECEDING tests 
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as bit_or
+from t1;
+drop table t1;
+
+
+create table t2 (
+  pk int, 
+  a int, 
+  b int
+);
+
+insert into t2 values
+( 1, 0, 1),
+( 2, 0, 2),
+( 3, 0, 4),
+( 4, 0, 8),
+( 5, 1, 16),
+( 6, 1, 32),
+( 7, 1, 64),
+( 8, 1, 128),
+( 9, 2, 256),
+(10, 2, 512),
+(11, 2, 1024),
+(12, 2, 2048);
+
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as bit_or
+from t2;
+
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) as bit_or
+from t2;
+
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) as bit_or
+from t2;
+
+--echo # Check CURRENT ROW 
+
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN CURRENT ROW AND CURRENT ROW) as bit_or
+from t2;
+
+drop table t2;
+
+--echo #
+--echo # Try RANGE PRECEDING|FOLLWING n
+--echo #
+create table t1 (
+  part_id int,
+  pk int,
+  a int
+);
+
+insert into t1 values 
+(10, 1, 1),
+(10, 2, 2),
+(10, 3, 4),
+(10, 4, 8),
+(10, 5,26), 
+(10, 6,27),
+(10, 7,40),
+(10, 8,71),
+(10, 9,72);
+
+select
+  pk, a,
+  count(a) over (ORDER BY a
+                 RANGE BETWEEN UNBOUNDED PRECEDING 
+                 AND 10 FOLLOWING) as cnt
+from t1;
+
+select
+  pk, a,
+  count(a) over (ORDER BY a DESC
+                 RANGE BETWEEN UNBOUNDED PRECEDING 
+                 AND 10 FOLLOWING) as cnt
+from t1;
+
+select
+  pk, a,
+  count(a) over (ORDER BY a
+                 RANGE BETWEEN UNBOUNDED PRECEDING 
+                 AND 1 FOLLOWING) as cnt
+from t1;
+
+select
+  pk, a,
+  count(a) over (ORDER BY a
+                 RANGE BETWEEN UNBOUNDED PRECEDING 
+                 AND 10 PRECEDING) as cnt
+from t1;
+
+select
+  pk, a,
+  count(a) over (ORDER BY a DESC
+                 RANGE BETWEEN UNBOUNDED PRECEDING 
+                 AND 10 PRECEDING) as cnt
+from t1;
+
+select
+  pk, a,
+  count(a) over (ORDER BY a
+                 RANGE BETWEEN UNBOUNDED PRECEDING 
+                 AND 1 PRECEDING) as cnt
+from t1;
+
+# Try bottom bound 
+select
+  pk, a,
+  count(a) over (ORDER BY a
+                 RANGE BETWEEN 1 PRECEDING 
+                 AND CURRENT ROW) as cnt
+from t1;
+
+select
+  pk, a,
+  count(a) over (ORDER BY a DESC
+                 RANGE BETWEEN 1 PRECEDING 
+                 AND CURRENT ROW) as cnt
+from t1;
+
+select
+  pk, a,
+  count(a) over (ORDER BY a
+                 RANGE BETWEEN 1 FOLLOWING 
+                 AND 3 FOLLOWING) as cnt
+from t1;
+
+--echo # Try CURRENT ROW with[out] DESC
+select
+  pk, a,
+  count(a) over (ORDER BY a
+                 RANGE BETWEEN CURRENT ROW
+                 AND 1 FOLLOWING) as cnt
+from t1;
+
+select
+  pk, a,
+  count(a) over (order by a desc
+                 range between current row
+                 and 1 following) as cnt
+from t1;
+
+
+# Try with partitions
+insert into t1 select 22, pk, a from t1;
+select
+  part_id, pk, a,
+  count(a) over (PARTITION BY part_id
+                 ORDER BY a
+                 RANGE BETWEEN UNBOUNDED PRECEDING 
+                 AND 10 FOLLOWING) as cnt
+from t1;
+
+select
+  pk, a,
+  count(a) over (PARTITION BY part_id
+                 ORDER BY a
+                 RANGE BETWEEN UNBOUNDED PRECEDING 
+                 AND 1 PRECEDING) as cnt
+from t1;
+
+drop table t1;
+
+--echo # Try a RANGE frame over non-integer datatype:
+
+create table t1 (
+  col1 int,
+  a decimal(5,3)
+);
+
+insert into t1 values (1, 0.45);
+insert into t1 values (1, 0.5);
+insert into t1 values (1, 0.55);
+insert into t1 values (1, 1.21);
+insert into t1 values (1, 1.22);
+insert into t1 values (1, 3.33);
+
+select
+  a, 
+  count(col1) over (order by a 
+                    range between 0.1 preceding 
+                                  and 0.1 following) 
+from t1;
+
+drop table t1;
+
+--echo # 
+--echo # RANGE-type frames and NULL values 
+--echo # 
+create table t1 (
+ pk int,
+ a int,
+ b int
+);
+
+insert into t1 values (1, NULL,1);
+insert into t1 values (2, NULL,1);
+insert into t1 values (3, NULL,1);
+insert into t1 values (4, 10 ,1);
+insert into t1 values (5, 11 ,1);
+insert into t1 values (6, 12 ,1);
+insert into t1 values (7, 13 ,1);
+insert into t1 values (8, 14 ,1);
+
+
+select 
+ pk, a, 
+ count(b) over (order by a 
+                range between 2 preceding 
+                          and 2 following) as CNT
+from t1;
+drop table t1;
+
+--echo # 
+--echo #  Try ranges that have bound1 > bound2.  The standard actually allows them
+--echo # 
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+
+select
+  pk, c,
+  count(*) over (partition by c
+                 order by pk
+                 rows between 1 preceding
+                          and 2 preceding) 
+                as cnt
+from t1;
+
+
+select
+  pk, c,
+  count(*) over (partition by c
+                 order by pk
+                 range between 1 preceding
+                           and 2 preceding) 
+                as cnt
+from t1;
+drop table t0, t1;
+
+--echo #
+--echo # Error checking for frame bounds
+--echo #
+
+create table t1 (a int, b int, c varchar(32));
+insert into t1 values (1,1,'foo');
+insert into t1 values (2,2,'bar');
+--error ER_RANGE_FRAME_NEEDS_SIMPLE_ORDERBY
+select 
+  count(*) over (order by a,b
+                 range between unbounded preceding and current row)
+from t1;
+
+--error ER_WRONG_TYPE_FOR_RANGE_FRAME
+select
+  count(*) over (order by c
+                 range between unbounded preceding and current row)
+from t1;
+
+--error ER_WRONG_TYPE_FOR_RANGE_FRAME
+select
+  count(*) over (order by a
+                 range between 'abcd' preceding and current row)
+from t1;
+
+--error ER_WRONG_TYPE_FOR_RANGE_FRAME
+select
+  count(*) over (order by a
+                 range between current row and 'foo' following)
+from t1;
+
+--echo # Try range frame with invalid bounds
+--error ER_WRONG_TYPE_FOR_ROWS_FRAME
+select
+  count(*) over (order by a
+                 rows between 0.5 preceding and current row)
+from t1;
+
+--error ER_WRONG_TYPE_FOR_ROWS_FRAME
+select
+  count(*) over (order by a
+                 rows between current row and 3.14 following)
+from t1;
+
+--echo #
+--echo # EXCLUDE clause is parsed but not supported 
+--echo #
+
+--error ER_FRAME_EXCLUSION_NOT_SUPPORTED
+select
+  count(*) over (order by a
+                 rows between 1 preceding and 1 following
+                 exclude current row)
+from t1;
+
+--error ER_FRAME_EXCLUSION_NOT_SUPPORTED
+select
+  count(*) over (order by a
+                 range between 1 preceding and 1 following
+                 exclude ties)
+from t1;
+
+--error ER_FRAME_EXCLUSION_NOT_SUPPORTED
+select
+  count(*) over (order by a
+                 range between 1 preceding and 1 following
+                 exclude group)
+from t1;
+
+# EXCLUDE NO OTHERS means 'don't exclude anything'
+select
+  count(*) over (order by a
+                 rows between 1 preceding and 1 following
+                 exclude no others)
+from t1;
+
+drop table t1;
+
+--echo # 
+--echo #  Window function in grouping query 
+--echo # 
+
+create table t1 (
+  username  varchar(32),
+  amount int
+);
+ 
+insert into t1 values
+('user1',1),
+('user1',5),
+('user1',3),
+('user2',10),
+('user2',20),
+('user2',30);
+ 
+select 
+  username,
+  sum(amount) as s, 
+  rank() over (order by s desc)
+from t1
+group by username;
+
+drop table t1;
+
+--echo # 
+--echo #  mdev-9719: Window function in prepared statement 
+--echo # 
+
+create table t1(a int, b int, x char(32));
+insert into t1 values (2, 10, 'xx');
+insert into t1 values (2, 10, 'zz');
+insert into t1 values (2, 20, 'yy');
+insert into t1 values (3, 10, 'xxx');
+insert into t1 values (3, 20, 'vvv');
+ 
+prepare stmt from 'select a, row_number() over (partition by a order by b) from t1';
+--sorted_result
+execute stmt;
+
+drop table t1;
+
+--echo # 
+--echo #  mdev-9754: Window name resolution in prepared statement 
+--echo # 
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+ 
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+ 
+prepare stmt from 
+'select 
+  pk, c, 
+  count(*) over w1 as CNT
+from t1
+window w1 as (partition by c order by pk 
+              rows between 2 preceding and 2 following)';
+execute stmt;
+
+drop table t0,t1;
+
+--echo #
+--echo # EXPLAIN FORMAT=JSON support for window functions
+--echo #
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+explain format=json select rank() over (order by a) from t0;
+
+create table t1 (a int, b int, c int);
+insert into t1 select a,a,a from t0;
+
+explain format=json
+select
+  a,
+  rank() over (order by sum(b))
+from t1
+group by a;
+
+explain format=json
+select
+  a,
+  rank() over (order by sum(b))
+from t1
+group by a
+order by null;
+
+--echo #
+--echo # Check how window function works together with GROUP BY and HAVING
+--echo #
+
+select b,max(a) as MX, rank() over (order by b) from t1 group by b having MX in (3,5,7);
+explain format=json
+select b,max(a) as MX, rank() over (order by b) from t1 group by b having MX in (3,5,7);
+
+drop table t1;
+drop table t0;
+
+--echo #
+--echo # Building ordering index for window functions
+--echo #
+
+create table t1 (
+  pk int primary key,
+  a int,
+  b int,
+  c  int
+);
+
+insert into t1 values
+(101 , 0, 10, 1),
+(102 , 0, 10, 2),
+(103 , 1, 10, 3),
+(104 , 1, 10, 4),
+(108 , 2, 10, 5),
+(105 , 2, 20, 6),
+(106 , 2, 20, 7),
+(107 , 2, 20, 8),
+(109 , 4, 20, 9),
+(110 , 4, 20, 10),
+(111 , 5, NULL, 11),
+(112 , 5, 1, 12),
+(113 , 5, NULL, 13),
+(114 , 5, NULL, 14),
+(115 , 5, NULL, 15),
+(116 , 6, 1, NULL),
+(117 , 6, 1, 10),
+(118 , 6, 1, 1),
+(119 , 6, 1, NULL),
+(120 , 6, 1, NULL),
+(121 , 6, 1, NULL),
+(122 , 6, 1, 2),
+(123 , 6, 1, 20),
+(124 , 6, 1, -10),
+(125 , 6, 1, NULL),
+(126 , 6, 1, NULL),
+(127 , 6, 1, NULL);
+
+--sorted_result
+select sum(b) over (partition by a order by b,pk
+                    rows between unbounded preceding  and current row) as c1,
+       avg(b) over (w1 rows between 1 preceding and 1 following) as c2,
+       sum(c) over (w2 rows between 1 preceding and 1 following) as c5,  
+       avg(b) over (w1 rows between 5 preceding and 5 following) as c3,
+       sum(b) over (w1 rows between 1 preceding and 1 following) as c4
+from t1 
+window w1 as (partition by a order by b,pk),
+       w2 as (partition by b order by c,pk);
+
+drop table t1;
+
+
+--echo #
+--echo # MDEV-9848: Window functions: reuse sorting and/or scanning
+--echo #
+
+create table t1 (a int, b int, c int);
+insert into t1 values 
+(1,3,1),
+(2,2,1),
+(3,1,1);
+
+--echo #  Check using counters
+flush status;
+select 
+  rank() over (partition by c order by a),
+  rank() over (partition by c order by b)
+from t1;
+show status like '%sort%';
+
+flush status;
+select 
+  rank() over (partition by c order by a),
+  rank() over (partition by c order by a)
+from t1;
+show status like '%sort%';
+
+# Check using EXPLAIN FORMAT=JSON 
+explain format=json
+select 
+  rank() over (partition by c order by a),
+  rank() over (partition by c order by a)
+from t1;
+
+explain format=json
+select 
+  rank() over (order by a),
+  row_number() over (order by a)
+from t1;
+
+explain format=json
+select 
+  rank() over (partition by c order by a),
+  count(*) over (partition by c)
+from t1;
+
+explain format=json
+select 
+  count(*) over (partition by c),
+  rank() over (partition by c order by a)
+from t1;
+
+drop table t1;
+
+
+--echo #
+--echo # MDEV-9847: Window functions: crash with big_tables=1
+--echo #
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+set @tmp=@@big_tables;
+set big_tables=1;
+select rank() over (order by a) from t1;
+set big_tables=@tmp;
+drop table t1;
+
+--echo #
+--echo # Check if "ORDER BY window_func" works
+--echo #
+
+create table t1 (s1 int, s2 char(5));
+insert into t1 values (1,'a');
+insert into t1 values (null,null);
+insert into t1 values (1,null);
+insert into t1 values (null,'a');
+insert into t1 values (2,'b');
+insert into t1 values (-1,'');
+
+explain format=json
+select *, row_number() over (order by s1, s2) as X from t1 order by X desc;
+select *, row_number() over (order by s1, s2) as X from t1 order by X desc;
+drop table t1;
+
+--echo #
+--echo # Try window functions that are not directly present in the select list
+--echo #
+create table t1 (a int, b int);
+insert into t1 values 
+  (1,3),
+  (2,2),
+  (3,1);
+
+select 
+  rank() over (order by a) -
+  rank() over (order by b)
+from
+  t1;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-9894: Assertion `0' failed in Window_func_runner::setup
+--echo #  return ER_NOT_SUPPORTED_YET for aggregates that are not yet supported
+--echo #  as window functions.
+--echo #
+create table t1 (i int);
+insert into t1 values (1),(2);
+--error ER_NOT_SUPPORTED_YET
+SELECT MAX(i) OVER (PARTITION BY (i)) FROM t1;
+drop table t1;
+
+--echo #
+--echo # Check the 0 in ROWS 0 PRECEDING
+--echo #
+
+create table t1 (
+  part_id int,
+  pk int,
+  a int
+);
+
+insert into t1 values (1, 1, 1);
+insert into t1 values (1, 2, 2);
+insert into t1 values (1, 3, 4);
+insert into t1 values (1, 4, 8);
+
+select 
+  pk, a,
+  sum(a) over (order by pk rows between 0 preceding and current row)
+from t1;
+
+select 
+  pk, a, 
+  sum(a) over (order by pk rows between 1 preceding and 0 preceding)
+from t1;
+
+insert into t1 values (200, 1, 1);
+insert into t1 values (200, 2, 2);
+insert into t1 values (200, 3, 4);
+insert into t1 values (200, 4, 8);
+select 
+  part_id, pk, a,
+  sum(a) over (partition by part_id order by pk rows between 0 preceding and current row)
+from t1;
+
+select 
+  part_id, pk, a, 
+  sum(a) over (partition by part_id order by pk rows between 1 preceding and 0 preceding)
+from t1;
+
+drop table t1;
+--echo #
+--echo # MDEV-9780, The "DISTINCT must not bet converted into GROUP BY when
+--echo #                 window functions are present" part 
+--echo #
+
+create table t1 (part_id int, a int);
+insert into t1 values
+(100, 1),
+(100, 2),
+(100, 2),
+(100, 3),
+(2000, 1),
+(2000, 2),
+(2000, 3),
+(2000, 3),
+(2000, 3);
+
+select          rank() over (partition by part_id order by a) from t1;
+select distinct rank() over (partition by part_id order by a) from t1;
+explain format=json
+select distinct rank() over (partition by part_id order by a) from t1;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-9893: Window functions with different ORDER BY lists, 
+--echo #            one of these lists containing an expression 
+--echo #
+
+create table t1 (s1 int, s2 char(5));
+insert into t1 values (1,'a');
+insert into t1 values (null,null);
+insert into t1 values (3,null);
+insert into t1 values (4,'a');
+insert into t1 values (2,'b');
+insert into t1 values (-1,'');
+ 
+select 
+  *,
+  ROW_NUMBER() OVER (order by s1),
+  CUME_DIST() OVER (order by -s1) 
+from t1;
+
+drop table t1;
+
+
+--echo #
+--echo # MDEV-9925: Wrong result with aggregate function as a window function
+--echo #
+create table t1 (i int);
+insert into t1 values (1),(2);
+select i, sum(i) over (partition by i) from t1;
+drop table t1;
+
+--echo #
+--echo # MDEV-9922: Assertion `!join->only_const_tables() && fsort' failed in int create_sort_index
+--echo #
+create view v1 as select 1 as i;
+select rank() over (order by i) from v1;
+drop view v1;
+