Create 'main' test directory and move 't' and 'r' there

1 files changed, 2072 insertions, 0 deletions

diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
new file mode 100644
index 00000000000..c46aaecfbbf
--- /dev/null
+++ b/mysql-test/main/win.test
@@ -0,0 +1,2072 @@
+#
+#  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);
+
+--sorted_result
+select pk, a, rank() over (order by a) from t2;
+--sorted_result
+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,
+  sum(c) over (partition by c
+                 order by pk
+                 rows between 1 preceding
+                          and 2 preceding)
+                as sum
+from t1;
+
+select
+  pk, c,
+  sum(c) over (partition by c
+                 order by pk
+                 rows between 2 following
+                          and 1 following)
+                as sum
+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
+  a, b,
+  rank() over (order by a), rank() over (order by b),
+  rank() over (order by a) - rank() over (order by b) as diff
+from
+  t1;
+
+drop table t1;
+
+create table t1 (i int);
+insert into t1 values (1),(2);
+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;
+
+--echo #
+--echo # MDEV-10097: Assertion `count > 0' failed in Item_sum_sum::add_helper(bool)
+--echo #
+CREATE TABLE `orders` (
+        `o_orderkey` int(11) NOT NULL,
+        `o_custkey` int(11) DEFAULT NULL,
+        PRIMARY KEY (`o_orderkey`)
+        ) DEFAULT CHARSET=latin1;
+
+INSERT INTO `orders` VALUES (59908,242);
+INSERT INTO `orders` VALUES (59940,238);
+
+SELECT o_custkey, avg(o_custkey) OVER (PARTITION BY abs(o_custkey)
+                                       ORDER BY o_custkey
+                                       RANGE BETWEEN 15 FOLLOWING
+                                                 AND 15 FOLLOWING) from orders;
+DROP table orders;
+
+--echo #
+--echo # MDEV-10842: window functions with the same order column
+--echo #             but different directions
+--echo #
+
+create table t1 (
+  pk int primary key,
+  a int,
+  b int,
+  c char(10)
+);
+
+insert into t1 values
+( 1, 0, 1, 'one'),
+( 2, 0, 2, 'two'),
+( 3, 0, 3, 'three'),
+( 4, 1, 1, 'one'),
+( 5, 1, 1, 'two'),
+( 6, 1, 2, 'three'),
+( 7, 2, NULL, 'n_one'),
+( 8, 2, 1,    'n_two'),
+( 9, 2, 2,    'n_three'),
+(10, 2, 0,    'n_four'),
+(11, 2, 10,   NULL);
+
+select pk,
+      row_number() over (order by pk desc) as r_desc,
+      row_number() over (order by pk asc) as r_asc
+from t1;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-10874: two window functions with ccompatible sorting 
+--echo #
+
+create table t1 (
+pk int primary key,
+a int,
+b int,
+c char(10),
+d decimal(10, 3),
+e real
+);
+insert into t1 values
+( 1, 0, 1,    'one',    0.1,  0.001),
+( 2, 0, 2,    'two',    0.2,  0.002),
+( 3, 0, 3,    'three',  0.3,  0.003),
+( 4, 1, 2,    'three',  0.4,  0.004),
+( 5, 1, 1,    'two',    0.5,  0.005),
+( 6, 1, 1,    'one',    0.6,  0.006),
+( 7, 2, NULL, 'n_one',  0.5,  0.007),
+( 8, 2, 1,    'n_two',  NULL, 0.008),
+( 9, 2, 2,    NULL,     0.7,  0.009),
+(10, 2, 0,    'n_four', 0.8,  0.010),
+(11, 2, 10,   NULL,     0.9,  NULL);
+
+select pk, a, d,
+       sum(d) over (partition by a order by pk
+                    ROWS between 1 preceding and current row) as sum_1, 
+       sum(d) over (order by a 
+                    ROWS BETWEEN 1 preceding and 2 following) as sum_2
+from t1;
+explain format=json
+select pk, a, d,
+       sum(d) over (partition by a order by pk
+                    ROWS between 1 preceding and current row) as sum_1, 
+       sum(d) over (order by a 
+                    ROWS BETWEEN 1 preceding and 2 following) as sum_2
+from t1;
+
+select pk, a, d,
+       sum(d) over (partition by a order by pk desc
+                    ROWS between 1 preceding and current row) as sum_1, 
+       sum(d) over (order by a 
+                    ROWS BETWEEN 1 preceding and 2 following) as sum_2
+from t1;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-9941: two window functions with compatible partitions 
+--echo #
+
+create table t1 (
+  a int,
+  b int,
+  c int
+);
+ 
+insert into t1 values 
+ (10, 1, 1),
+ (10, 3, 10),
+ (10, 1, 10),
+ (10, 3, 100),
+ (10, 5, 1000),
+ (10, 1, 100);
+
+explain format=json
+select 
+  a,b,c,
+  row_number() over (partition by a),
+  row_number() over (partition by a, b)
+from t1;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-10815: Window Function Expressions Wrong Results
+--echo #
+create table t(a decimal(35,10), b int);
+insert into t(a,b) values(1,1);
+insert into t(a,b) values(2,1);
+insert into t(a,b) values(0,1);
+insert into t(a,b) values(1,  2);
+insert into t(a,b) values(1.5,2);
+insert into t(a,b) values(3,  2);
+insert into t(a,b) values(4.5,2);
+select a, b,
+       sum(t.a) over (partition by t.b order by a) as simple_sum,
+       sum(t.a) over (partition by t.b order by a) + 1 as sum_and_const,
+       sum(t.b) over (partition by t.b order by a) + sum(t.a) over (partition by t.b order by a) as sum_and_sum
+from t
+order by t.b, t.a;
+drop table t;
+
+--echo #
+--echo # MDEV-10669: Crash in SELECT with window function used
+--echo #
+create table t(a decimal(35,10), b int);
+insert into t(a,b) values(1,1);
+insert into t(a,b) values(2,1);
+insert into t(a,b) values(0,1);
+SELECT (CASE WHEN sum(t.a) over (partition by t.b)=0 THEN null ELSE null END) AS a FROM t;
+SELECT ifnull(((t.a) / CASE WHEN sum(t.a) over(partition by t.b) =0 then null else null end) ,0) from t;
+SELECT sum(t.a) over (partition by t.b order by a),
+       sqrt(ifnull((sum(t.a) over (partition by t.b order by a)), 0))
+from t;
+drop table t;
+
+--echo #
+--echo # MDEV-10868: view definitions with window functions
+--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;
+
+let $q=
+select pk, c, c/count(*) over (partition by c order by pk
+                               rows between 1 preceding and 2 following) as CNT
+from t1;
+
+eval $q;
+eval create view v1 as $q;
+show create view v1;
+select * from v1;
+
+let $q=
+select pk, c, c/count(*) over w1 as CNT from t1
+  window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
+
+eval $q;
+eval create view v2 as $q;
+show create view v2;
+select * from v2;
+
+let $q=
+select pk, c, c/count(*) over w1 as CNT from t1
+  window w1 as (partition by c order by pk rows unbounded preceding);
+
+eval $q;
+eval create view v3 as $q;
+show create view v3;
+select * from v3;
+
+let $q=
+select pk, c, c/count(*) over (partition by c order by pk
+                               range between 3 preceding and current row) as CNT
+from t1;
+
+eval $q;
+eval create view v4 as $q;
+show create view v4;
+select * from v4;
+
+drop view v1,v2,v3,v4;
+drop table t0,t1;
+
+--echo #
+--echo # MDEV-10875: window function in subquery
+--echo #
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (3),(1);
+
+CREATE TABLE t2 (c VARCHAR(8));
+INSERT INTO t2 VALUES ('foo'),('bar'),('foo');
+
+SELECT COUNT(*) OVER (PARTITION BY c) FROM t2;
+
+SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-9976: window function without PARTITION BY and ORDER BY
+--echo #
+
+CREATE TABLE t1  (id int, a int);
+INSERT INTO t1 VALUES
+ (1,1000), (2,1100), (3,1800), (4,1500), (5,1700), (6,1200),
+ (7,2000), (8,2100), (9,1600);
+
+--sorted_result
+SELECT id, sum(a) OVER (PARTITION BY id 
+                       ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+FROM t1;
+
+--sorted_result
+SELECT id, sum(a) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-11867: window function with aggregation
+--echo #             over the result of grouping
+--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, avg(sum(amount)) over (order by s desc)
+  from t1
+group by username;
+
+select username, sum(amount), avg(sum(amount)) over (order by sum(amount) desc)
+  from t1
+group by username;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-11594: window function over implicit grouping
+--echo #
+
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2);
+
+select sum(id) over (order by sum(id)) from t1;
+
+select sum(sum(id)) over (order by sum(id)) from t1;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-9923: integer constant in order by list 
+--echo #            of window specification
+--echo #
+
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2);
+
+select rank() over (order by 1) from t1;
+select rank() over (order by 2) from t1;
+select rank() over (partition by id order by 2) from t1;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-10660: view using a simple window function 
+--echo #
+
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2);
+
+create view v1(id,rnk) as
+  select id, rank() over (order by id) from t1;
+
+show create view v1;
+
+select id, rank() over (order by id) from t1;
+select * from v1;
+
+drop view v1;
+drop table t1;
+
+--echo #
+--echo # MDEV-11138: window function in the query without tables 
+--echo #
+
+select row_number() over ();
+select count(*) over ();
+select sum(5) over ();
+select row_number() over (), sum(5) over ();
+select row_number() over (order by 2);
+select row_number() over (partition by 2);
+select row_number() over (partition by 4 order by 1+2);
+
+--echo #
+--echo # MDEV-11999: execution of prepared statement for  
+--echo #             tableless query with window functions
+--echo #
+
+prepare stmt from
+"select row_number() over (partition by 4 order by 1+2)";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+--echo #
+--echo # MDEV-11745: window function with min/max 
+--echo #
+
+create table t1 (i int, b int);
+insert into t1 values
+  (1,1),(2,1),(3,1),(4,4),(5,4),(6,4),(7,8),(8,8),(9,8),(10,8);
+
+select b, min(i) over (partition by b) as f 
+  from t1 as tt
+order by i;
+
+select b, min(i) over (partition by b) as f 
+  from (select * from t1) as tt
+order by i;
+
+select b, min(i+10) over (partition by b) as f 
+  from t1 as tt
+order by i;
+
+select b, min(i) over (partition by b) as f 
+  from (select i+10 as i, b from t1) as tt
+order by i;
+
+select b, min(i+20) over (partition by b) as f 
+  from (select i+10 as i, b from t1) as tt
+order by i;
+
+select b, max(i) over (partition by b) as f 
+  from t1 as tt
+order by i;
+
+select b, max(i) over (partition by b) as f 
+  from (select * from t1) as tt
+order by i;
+
+select b, max(i+10) over (partition by b) as f 
+  from t1 as tt
+order by i;
+
+select b, max(i) over (partition by b) as f 
+  from (select i+10 as i, b from t1) as tt
+order by i;
+
+select b, max(i+20) over (partition by b) as f 
+  from (select i+10 as i, b from t1) as tt
+order by i;
+
+select max(i), max(i), sum(i), count(i)
+  from t1 as tt
+group by b;
+
+select max(i), min(sum(i)) over (partition by count(i)) f
+  from t1 as tt
+group by b;
+
+select max(i), min(sum(i)) over (partition by count(i)) f
+  from (select * from t1) as tt
+group by b;
+
+select max(i+10), min(sum(i)+10) over (partition by count(i)) f
+  from t1 as tt
+group by b;
+
+select max(i), max(i), sum(i), count(i)
+  from (select i+10 as i, b from t1) as tt
+group by b;
+
+select max(i), min(sum(i)) over (partition by count(i)) f
+  from (select i+10 as i, b from t1) as tt
+group by b;
+
+select max(i), min(i), min(max(i)-min(i)) over (partition by count(i)) f
+  from (select i+10 as i, b from t1) as tt
+group by b;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-12015: window function over select with WHERE  
+--echo #             that is always FALSE
+--echo #
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (3), (1), (2);
+
+SELECT i, ROW_NUMBER() OVER () FROM t1 WHERE 1 = 2;
+
+SELECT i, COUNT(*) OVER () FROM t1 WHERE 1 = 2;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-12051: window function in query with implicit grouping 
+--echo #             on always empty set
+--echo #
+
+create table t1 (a int, b varchar(8));
+insert into t1 values (1,'foo'),(2,'bar');
+
+select max(a), row_number() over () from t1 where a > 10;
+select max(a), sum(max(a)) over () from t1 where a > 10;
+select max(a), sum(max(a)) over (partition by max(a)) from t1 where a > 10;
+
+select max(a), row_number() over () from t1 where 1 = 2;
+select max(a), sum(max(a)) over () from t1 where 1 = 2;
+select max(a), sum(max(a)) over (partition by max(a)) from t1 where 1 = 2;
+
+select max(a), row_number() over () from t1 where 1 = 2
+  having max(a) is not null;
+select max(a), sum(max(a)) over () from t1 where 1 = 2
+  having max(a) is not null;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-10885: window function in query with implicit grouping 
+--echo #             with constant condition evaluated to false
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(8));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+
+CREATE TABLE t2 (c INT);
+INSERT INTO t2 VALUES (3),(4);
+ 
+CREATE TABLE t3 (d INT);
+INSERT INTO t3 VALUES (5),(6);
+
+SELECT MAX(a), ROW_NUMBER() OVER (PARTITION BY MAX(a)) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+
+SELECT MAX(a), COUNT(MAX(a)) OVER (PARTITION BY MAX(a)) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+
+SELECT MAX(a), SUM(MAX(a)) OVER (PARTITION BY MAX(a)) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+
+SELECT MAX(a), ROW_NUMBER() OVER (PARTITION BY MAX(a)) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) )
+HAVING MAX(a) IS NOT NULL;
+
+SELECT a, MAX(a), ROW_NUMBER() OVER (PARTITION BY b) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+
+SELECT a, COUNT(a), AVG(a) OVER (PARTITION BY b) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+
+SELECT a, MAX(a), AVG(a) OVER (PARTITION BY b) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-10859: Wrong result of aggregate window function in query
+--echo #                  with HAVING and no ORDER BY
+--echo #
+
+create table empsalary (depname varchar(32), empno smallint primary key, salary int);
+insert into empsalary values
+  ('develop', 1, 5000), ('develop', 2, 4000),('sales', 3, '6000'),('sales', 4, 5000);
+
+--sorted_result
+SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
+--sorted_result
+SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname;
+--echo #
+--echo # These last 2 should have the same row results, ignoring order.
+--echo #
+--sorted_result
+SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1;
+--sorted_result
+SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1 ORDER BY depname;
+
+drop table empsalary;
+
+--echo #
+--echo # MDEV-11868: min(distinct) over () returns wrong value
+--echo #
+
+create table TDEC (CDEC int, RNUM int);
+create view VDEC as select * from TDEC;
+insert into TDEC (CDEC) values (null),(-1),(0),(1),(0),(10);
+select TDEC.CDEC, min(TDEC.CDEC) over () from TDEC;
+select VDEC.CDEC, min(VDEC.CDEC) over () from VDEC;
+select TDEC.CDEC, max(TDEC.CDEC) over () from TDEC;
+select VDEC.CDEC, max(VDEC.CDEC) over () from VDEC;
+
+select TDEC.CDEC, min(distinct TDEC.CDEC) over () from TDEC;
+select VDEC.CDEC, min(distinct VDEC.CDEC) over () from VDEC;
+select TDEC.CDEC, max(distinct TDEC.CDEC) over () from TDEC;
+select VDEC.CDEC, max(distinct VDEC.CDEC) over () from VDEC;
+
+--echo #
+--echo # These should be removed once support for them is added.
+--echo #
+--error ER_NOT_SUPPORTED_YET
+select TDEC.CDEC, count(distinct TDEC.CDEC) over () from TDEC;
+--error ER_NOT_SUPPORTED_YET
+select VDEC.CDEC, count(distinct VDEC.CDEC) over () from VDEC;
+--error ER_NOT_SUPPORTED_YET
+select TDEC.CDEC, sum(distinct TDEC.CDEC) over () from TDEC;
+--error ER_NOT_SUPPORTED_YET
+select VDEC.CDEC, sum(distinct VDEC.CDEC) over () from VDEC;
+--error ER_NOT_SUPPORTED_YET
+select TDEC.CDEC, avg(distinct TDEC.CDEC) over () from TDEC;
+--error ER_NOT_SUPPORTED_YET
+select VDEC.CDEC, avg(distinct VDEC.CDEC) over () from VDEC;
+--error ER_NOT_SUPPORTED_YET
+select TDEC.CDEC, GROUP_CONCAT(TDEC.CDEC) over () from TDEC;
+--error ER_NOT_SUPPORTED_YET
+select VDEC.CDEC, GROUP_CONCAT(distinct VDEC.CDEC) over () from VDEC;
+
+drop table TDEC;
+drop view VDEC;
+
+--echo #
+--echo # MDEV-10700: 10.2.2 windowing function returns incorrect result
+--echo #
+create table t(a int,b int, c int , d int);
+insert into t(a,b,c,d) values(1, rand(10)*1000, rand(10)*1000, rand(10)*1000);
+insert into t(a,b,c,d) values(1, rand(10)*1000, rand(10)*1000, rand(10)*1000);
+replace into t(a,b,c,d) select 1, rand(10)*1000, rand(10)*1000, rand(10)*1000 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15, t t16, t t17;
+
+select count(distinct s) from (select sum(d) over(partition by a,b,c) as s from t) Z where s > 0;
+select count(distinct s) from (select sum(d) as s from t group by a,b,c) Z where s > 0;
+
+select count(distinct s) from (select sum(d) over(partition by a,b) as s from t) Z where s > 0;
+select count(distinct s) from (select sum(d) as s from t group by a,b) Z where s > 0;
+
+select count(distinct s) from (select sum(d) over(partition by a) as s from t) Z where s > 0;
+select count(distinct s) from (select sum(d) as s from t group by a) Z where s > 0;
+
+drop table t;
+
+--echo #
+--echo # MDEV-9924: window function in query with group by optimized away 
+--echo #
+
+create table t1 (i int);
+insert into t1 values (2),(3),(1);
+
+select row_number() over () from t1 group by 1+2;
+select max(i), row_number() over () from t1 group by 1+2;
+select rank() over (order by max(i)) from t1 group by 1+2;
+
+select i, row_number() over () from t1 group by 1+2;
+select i, rank() over (order by i) rnk from t1 group by 1+2;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-11907: window function as the second operand of division
+--echo #
+
+create table t1 (pk int, c int);
+insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2);
+
+set @sql_mode_save= @@sql_mode;
+set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
+
+select pk, c, c/count(*) over 
+           (partition by c order by pk
+            rows between 1 preceding and 2 following) as CNT
+from t1;
+show warnings;
+
+set sql_mode=@sql_mode_save;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-12336: several functions over a window function 
+--echo #
+  
+create table t1 (name varchar(10), cnt int);
+insert into t1 values ('Fred', 23), ('Fred', 35), ('Joe', 10);
+
+select q.name, q.row_cnt, 
+       round( 100 * ( q.row_cnt /
+                      sum(q.row_cnt) over
+                        ( 
+                          order by q.name 
+                          rows between
+                            unbounded preceding and
+                            unbounded following
+                        )
+                    ),2
+            ) pct_of_total
+from 
+( 
+  select name, count(*) row_cnt, sum(cnt) sum_cnt 
+  from t1 
+  group by 1
+) q;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-11990: window function over min/max aggregation
+--echo #
+
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2), (4), (2);
+
+select sum(max(id)) over (order by max(id)) from t1;
+explain
+select sum(max(id)) over (order by max(id)) from t1;
+
+create index idx on t1(id);
+select sum(max(id)) over (order by max(id)) from t1;
+explain
+select sum(max(id)) over (order by max(id)) from t1;
+select sum(max(id)) over (order by max(id)) from t1 where id < 3;
+select count(max(id)) over (order by max(id)) from t1 where id < 3;
+select max(id), rank() over (order by max(id)) from t1 where id < 3;
+
+drop table t1;
+
+--echo #
+--echo # main.win failure post MDEV-12336
+--echo #
+create table t(a decimal(35,10), b int);
+insert into t values (1, 10), (2, 20), (3, 30);
+
+prepare stmt from "SELECT (CASE WHEN sum(t.a) over (partition by t.b)=1 THEN 1000 ELSE 300 END) AS a FROM t";
+execute stmt;
+drop table t;
+
+--echo #
+--echo # MDEV-12851 case with window functions query crashes server
+--echo #
+
+create table t1(dt datetime);
+insert into t1 values ('2017-05-17'), ('2017-05-18');
+select dt,
+       case when (max(dt) over (order by dt rows between 1 following and 1 following) is null)
+       then '9999-12-31 12:00:00'
+       else max(dt) over (order by dt rows between 1 following and 1 following)
+       end x,
+       case when (max(dt) over (order by dt rows between 1 following and 1 following) is not null)
+       then '9999-12-31 12:00:00'
+       else max(dt) over (order by dt rows between 1 following and 1 following)
+       end x
+from t1;
+
+drop table t1;
+
+create table t1(i int);
+insert into t1 values (null),(1),(2);
+select max(i) over (order by i),
+       max(i) over (order by i) is null,
+       max(i) over (order by i) is not null
+from t1;
+drop table t1;
+
+--echo #
+--echo # MDEV-13189: Window functions crash when using INTERVAL function
+--echo #
+create table t1(i int);
+insert into t1 values (1),(2),(10),(20),(30);
+select sum(i) over (order by i), interval(sum(i) over (order by i), 10, 20)
+from t1;
+drop table t1;
+
+--echo #
+--echo # MDEV-13352: Server crashes in st_join_table::remove_duplicates
+--echo #
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT DISTINCT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
+SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-13344: Server crashes in in AGGR_OP::put_record on subquery 
+--echo #             with window function and constant table
+--echo # (Testcase only)
+--echo #
+CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM;
+INSERT IGNORE INTO t1 VALUES ('foo');
+SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1);
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-13351: Server crashes in st_select_lex::set_explain_type upon UNION with window function 
+--echo #
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT Nth_value(i,1) OVER() FROM t1 
+UNION ALL 
+( SELECT Nth_value(i,2) OVER() FROM t1 LIMIT 0 )
+;
+DROP TABLE t1;
+
+--echo #
+--echo # A regression after MDEV-13351:
+--echo # MDEV-13374 : Server crashes in first_linear_tab / st_select_lex::set_explain_type 
+--echo #              upon UNION with aggregate function	
+--echo #
+
+CREATE TABLE t1 (i INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+SELECT i AS fld FROM t1 UNION SELECT COUNT(*) AS fld FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-13240 Wrong warning with MAX(datetime_field) OVER (...)
+--echo #
+
+CREATE TABLE t1 (dt DATETIME);
+INSERT INTO t1 VALUES ('2017-05-17');
+SELECT MAX(dt) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-13358 FIRST_VALUE throws SQL Error (1292): Incorrect datetime value
+--echo #
+CREATE TABLE IF NOT EXISTS `fv_test` (
+        `SOME_DATE` datetime NOT NULL
+        );
+
+INSERT INTO `fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56');
+
+CREATE TABLE fv_result
+SELECT
+FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate
+FROM fv_test;
+
+SHOW CREATE TABLE fv_result;
+
+SELECT * FROM fv_result;
+
+DROP TABLE fv_test, fv_result;
+
+--echo #
+--echo # MDEV-13649: Server crashes in set_field_to_null_with_conversions or in Field::set_notnull
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0),(1),(2);
+SELECT LEAD(a) OVER (PARTITION BY a) as lead,
+       a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part
+FROM t1;
+
+SELECT a OR LEAD(a) OVER (ORDER BY a) AS a_or_lead_order
+FROM t1
+ORDER BY a;
+
+SELECT a AND LEAD(a) OVER (ORDER BY a) AS a_and_lead_order
+FROM t1
+ORDER BY a;
+
+SELECT a XOR LEAD(a) OVER (ORDER BY a) AS a_xor_lead_order
+FROM t1
+ORDER BY a;
+
+SELECT NOT LEAD(a) OVER (ORDER BY a) AS not_lead_order
+FROM t1
+ORDER BY a;
+
+SELECT LEAD(a) OVER (ORDER BY a) is not null AS is_not_null_lead_order
+FROM t1
+ORDER BY a;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-13354: Server crashes in find_field_in_tables upon PS with window function and subquery
+--echo #
+
+CREATE TABLE t1 (i INT, a char);
+INSERT INTO t1 VALUES (1, 'a'),(2, 'b');
+PREPARE stmt FROM "SELECT row_number() over (partition by i order by i), i FROM (SELECT * from t1) as sq";
+EXECUTE stmt;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-13384: "window" seems like a reserved column name but it's not listed as one
+--echo #
+--echo # Currently we allow window as an identifier, except for table aliases.
+--echo #
+
+CREATE TABLE door (id INT, window VARCHAR(10));
+
+--error ER_PARSE_ERROR
+SELECT id
+FROM door as window;
+
+SELECT id, window
+FROM door;
+
+--error ER_PARSE_ERROR
+SELECT id, window
+FROM door as window;
+
+DROP TABLE door;
+
+--echo #
+--echo # MDEV-13352: Server crashes in st_join_table::remove_duplicates
+--echo #
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT DISTINCT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
+SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
+DROP TABLE t1;
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #