Materialize/test/sqllogictest/window_funcs.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

# This test may seem simple, but it is surprisingly good at verifying that
# logical timestamp handling for internal inputs is sane.

mode cockroach

statement ok
CREATE TABLE t(x string);

statement ok
INSERT INTO t VALUES ('a'), ('b'), ('c');

statement error db error: ERROR: window function pg_catalog\.row_number requires an OVER clause
SELECT row_number()
FROM t;

statement error db error: ERROR: window function pg_catalog\.row_number requires an OVER clause
SELECT *
FROM t
ORDER BY row_number();

statement error db error: ERROR: window function pg_catalog\.row_number requires an OVER clause
SELECT *
FROM t
QUALIFY row_number();

statement error db error: ERROR: OVER clause not allowed on pg_catalog\.int8range\. The OVER clause can only be used with window functions \(including aggregations\)\.
SELECT int8range(1, 1, '') OVER (PARTITION BY 1 ORDER BY 1);

query IT
SELECT row_number() OVER (ORDER BY x), x FROM t
ORDER BY row_number
----
1  a
2  b
3  c

query IT
SELECT row_number() OVER (ORDER BY x DESC), x FROM t
ORDER BY row_number
----
1  c
2  b
3  a

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string, y int);

statement ok
INSERT INTO t VALUES ('a', 98), ('b', 99), ('c', 98);

query IT
SELECT row_number() OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY row_number, x
----
1  a
1  b
2  c

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string, y int);

statement ok
INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1);

query IT
SELECT row_number() OVER (PARTITION BY y ORDER BY x DESC), x FROM t
ORDER BY row_number, x
----
1  b
1  c
2  a

query IT
SELECT row_number() OVER (PARTITION BY x ORDER BY x), x FROM t
ORDER BY row_number, x
----
1  a
1  b
1  c

query IT
SELECT row_number() OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x
FROM t AS a1, t AS a2
ORDER BY q DESC
----
9  c
8  c
7  c
6  b
5  b
4  b
3  a
2  a
1  a

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string);

statement ok
INSERT INTO t VALUES ('a');

# Make sure a non-column expression following the window function is correctly
# handled.
query ITT
SELECT row_number() OVER (PARTITION BY NULL) AS q, x, 'b'
FROM t
----
1 a b

# Regression test for database-issues#2730
query II
SELECT row_number() OVER (), row_number() OVER ()
----
1 1

statement ok
INSERT INTO t VALUES ('b');

query II
SELECT row_number() OVER (), row_number() OVER () from t
----
1 1
2 2

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string, y int);

statement ok
INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1);

query T multiline
EXPLAIN RAW PLAN FOR
SELECT row_number() OVER (PARTITION BY x ORDER BY x), x FROM t
ORDER BY row_number, x
----
Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0, #1]
  Project (#2, #0)
    Map (row_number() over (partition by [#0{x}] order by [#0{x} asc nulls_last]))
      Get materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN DECORRELATED PLAN WITH(arity) FOR
SELECT row_number() OVER (PARTITION BY x ORDER BY x), x FROM t
ORDER BY row_number, x
----
Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0, #1]
  Project (#3, #0) // { arity: 2 }
    Map (#2) // { arity: 4 }
      Project (#3..=#5) // { arity: 3 }
        Map (record_get[0](record_get[1](#2)), record_get[1](record_get[1](#2)), record_get[0](#2)) // { arity: 6 }
          FlatMap unnest_list(#1) // { arity: 3 }
            Reduce group_by=[#0] aggregates=[row_number[order_by=[#0 asc nulls_last]](row(list[row(#0, #1)], #0{x}))] // { arity: 2 }
              CrossJoin // { arity: 2 }
                Constant // { arity: 0 }
                  - ()
                Get materialize.public.t // { arity: 2 }

Target cluster: quickstart

EOF

#
# Regression test for database-issues#2760
#

statement ok
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);
----

statement ok
----
INSERT INTO t1 VALUES (1, 1), (2, 2), (4, 4);

statement ok
CREATE TABLE t2 (f1 INTEGER, f2 INTEGER);
----

statement ok
INSERT INTO t2 VALUES (1, 1), (1, 2), (2, 2);
----

query I
SELECT f1 FROM t1
WHERE f1 IN (SELECT ROW_NUMBER() OVER () FROM t2);
----
1
2

query T multiline
EXPLAIN DECORRELATED PLAN WITH(arity) FOR SELECT f1 FROM t1
WHERE f1 IN (SELECT ROW_NUMBER() OVER () FROM t2);
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.public.t1 // { arity: 2 }
  cte l1 =
    Distinct project=[#0] // { arity: 1 }
      Get l0 // { arity: 2 }
  cte l2 =
    Map (true) // { arity: 2 }
      Distinct project=[#0] // { arity: 1 }
        Filter (integer_to_bigint(#0{f1}) = #1{right_col0_0}) // { arity: 2 }
          Project (#0, #4) // { arity: 2 }
            Map (#3) // { arity: 5 }
              Project (#3..=#6) // { arity: 4 }
                Map (record_get[0](record_get[1](#2)), record_get[1](record_get[1](#2)), record_get[2](record_get[1](#2)), record_get[0](#2)) // { arity: 7 }
                  FlatMap unnest_list(#1) // { arity: 3 }
                    Reduce group_by=[#0] aggregates=[row_number[order_by=[]](row(list[row(#0, #1, #2)]))] // { arity: 2 }
                      CrossJoin // { arity: 3 }
                        Get l1 // { arity: 1 }
                        Get materialize.public.t2 // { arity: 2 }
Return // { arity: 1 }
  Project (#0) // { arity: 1 }
    Filter #2 // { arity: 3 }
      Project (#0, #1, #3) // { arity: 3 }
        Join on=(#0 = #2) // { arity: 4 }
          Get l0 // { arity: 2 }
          Union // { arity: 2 }
            Get l2 // { arity: 2 }
            CrossJoin // { arity: 2 }
              Project (#0) // { arity: 1 }
                Join on=(#0 = #1) // { arity: 2 }
                  Union // { arity: 1 }
                    Negate // { arity: 1 }
                      Distinct project=[#0] // { arity: 1 }
                        Get l2 // { arity: 2 }
                    Distinct project=[#0] // { arity: 1 }
                      Get l1 // { arity: 1 }
                  Get l1 // { arity: 1 }
              Constant // { arity: 1 }
                - (false)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT f1 FROM t1
WHERE f1 IN (SELECT ROW_NUMBER() OVER () FROM t2);
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}) // { arity: 1 }
        ReadStorage materialize.public.t1 // { arity: 2 }
  Return // { arity: 1 }
    Project (#0{f1}) // { arity: 1 }
      Join on=(#0{f1} = #1) type=differential // { arity: 2 }
        implementation
          %1[#0]UKA » %0:l0[#0]K
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          Get l0 // { arity: 1 }
        ArrangeBy keys=[[#0]] // { arity: 1 }
          Distinct project=[record_get[0](record_get[1](#0))] // { arity: 1 }
            Project (#1) // { arity: 1 }
              Filter (integer_to_bigint(record_get[0](record_get[1](#1))) = record_get[0](#1)) // { arity: 2 }
                FlatMap unnest_list(#0{row_number}) // { arity: 2 }
                  Project (#1{row_number}) // { arity: 1 }
                    Reduce group_by=[#0{f1}] aggregates=[row_number[order_by=[]](row(list[row(#0{f1}, #1{f1}, #2{f2})]))] // { arity: 2 }
                      CrossJoin type=differential // { arity: 3 }
                        implementation
                          %0[×] » %1:t2[×]
                        ArrangeBy keys=[[]] // { arity: 1 }
                          Distinct project=[#0{f1}] // { arity: 1 }
                            Get l0 // { arity: 1 }
                        ArrangeBy keys=[[]] // { arity: 2 }
                          ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t1
Source materialize.public.t2

Target cluster: quickstart

EOF

query IIIII
SELECT * FROM t1, LATERAL(SELECT t2.*, ROW_NUMBER() OVER() FROM t2 WHERE t1.f1 = t2.f1) AS foo;
----
1  1  1  1  1
1  1  1  2  2
2  2  2  2  1

query IIIII
SELECT * FROM t1, LATERAL(SELECT t2.*, ROW_NUMBER() OVER() FROM t2 WHERE t1.f1 = t2.f1 AND t1.f2 = t2.f2) AS foo;
----
1  1  1  1  1
2  2  2  2  1

query IIIII
SELECT * FROM t1, LATERAL(SELECT t2.*, ROW_NUMBER() OVER() FROM t2 WHERE t1.f2 = t2.f2) AS foo;
----
1  1  1  1  1
2  2  1  2  1
2  2  2  2  2

query IIIII rowsort
SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER() FROM t1 WHERE t1.f1 = t2.f1) AS foo;
----
1  1  1  1  1
1  2  1  1  1
2  2  2  2  1

query IIIII
SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER() FROM t1 WHERE t1.f1 = t2.f1 AND t1.f2 = t2.f2) AS foo;
----
1  1  1  1  1
2  2  2  2  1

query IIIII rowsort
SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER() FROM t1 WHERE t1.f2 = t2.f2) AS foo;
----
1  1  1  1  1
1  2  2  2  1
2  2  2  2  1

# Check that the partition key comes after the outer columns in the grouping key

query T multiline
EXPLAIN DECORRELATED PLAN WITH(arity) FOR SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER() FROM t1 WHERE t1.f2 = t2.f2) AS foo;
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.public.t2 // { arity: 2 }
Return // { arity: 5 }
  Project (#0, #1, #3..=#5) // { arity: 5 }
    Join on=(#1 = #2) // { arity: 6 }
      Get l0 // { arity: 2 }
      Project (#0..=#2, #4) // { arity: 4 }
        Map (#3) // { arity: 5 }
          Project (#3..=#6) // { arity: 4 }
            Map (record_get[0](record_get[1](#2)), record_get[1](record_get[1](#2)), record_get[2](record_get[1](#2)), record_get[0](#2)) // { arity: 7 }
              FlatMap unnest_list(#1) // { arity: 3 }
                Reduce group_by=[#0] aggregates=[row_number[order_by=[]](row(list[row(#0, #1, #2)]))] // { arity: 2 }
                  Filter (#2{f2} = #0{f2}) // { arity: 3 }
                    CrossJoin // { arity: 3 }
                      Distinct project=[#1] // { arity: 1 }
                        Get l0 // { arity: 2 }
                      Get materialize.public.t1 // { arity: 2 }

Target cluster: quickstart

EOF

query T multiline
EXPLAIN DECORRELATED PLAN WITH(arity) FOR SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER(PARTITION BY f1) FROM t1 WHERE t1.f2 = t2.f2) AS foo;
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.public.t2 // { arity: 2 }
Return // { arity: 5 }
  Project (#0, #1, #3..=#5) // { arity: 5 }
    Join on=(#1 = #2) // { arity: 6 }
      Get l0 // { arity: 2 }
      Project (#0..=#2, #4) // { arity: 4 }
        Map (#3) // { arity: 5 }
          Project (#4..=#7) // { arity: 4 }
            Map (record_get[0](record_get[1](#3)), record_get[1](record_get[1](#3)), record_get[2](record_get[1](#3)), record_get[0](#3)) // { arity: 8 }
              FlatMap unnest_list(#2) // { arity: 4 }
                Reduce group_by=[#0, #1] aggregates=[row_number[order_by=[]](row(list[row(#0, #1, #2)]))] // { arity: 3 }
                  Filter (#2{f2} = #0{f2}) // { arity: 3 }
                    CrossJoin // { arity: 3 }
                      Distinct project=[#1] // { arity: 1 }
                        Get l0 // { arity: 2 }
                      Get materialize.public.t1 // { arity: 2 }

Target cluster: quickstart

EOF

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string);

statement ok
INSERT INTO t VALUES ('a'), ('b'), ('c');

# Regression for database-issues#2962
query error window functions are not allowed in ON
SELECT * FROM t AS v JOIN t ON row_number() over () > 1;

query error window functions are not allowed in WHERE
SELECT * FROM t
WHERE row_number() over () > 1;

statement ok
CREATE TABLE not_allowed_tests (
  v INT,
  w INT,
  k INT
);

query error window functions are not allowed in GROUP BY
SELECT * FROM not_allowed_tests GROUP BY v, count(w) OVER ();

query error window functions are not allowed in GROUP BY
SELECT count(w) OVER () FROM not_allowed_tests GROUP BY 1;

query error window functions are not allowed in RETURNING
INSERT INTO not_allowed_tests (k, v) VALUES (99, 100) RETURNING sum(v) OVER ();

query error window functions are not allowed in LIMIT
SELECT sum(v) FROM not_allowed_tests GROUP BY k LIMIT sum(v) OVER ();

query error db error: ERROR: window functions are not allowed in OFFSET \(function pg_catalog\.sum\)
SELECT sum(v) FROM not_allowed_tests GROUP BY k LIMIT 1 OFFSET sum(v) OVER ();

query error window functions are not allowed in VALUES
INSERT INTO not_allowed_tests (k, v) VALUES (99, count(1) OVER ());

query error window functions are not allowed in WHERE
SELECT k FROM not_allowed_tests WHERE avg(k) OVER () > 1;

query error window functions are not allowed in HAVING
SELECT 1 FROM not_allowed_tests GROUP BY 1 HAVING sum(1) OVER (PARTITION BY 1) > 1;

query T
SELECT DISTINCT ON (row_number() OVER ()) *
FROM t
ORDER BY row_number() OVER ()
----
a
b
c

# rank and dense_rank

query error db error: ERROR: function rank has 0 parameters, but was called with 1
SELECT rank(x) OVER (ORDER BY x), dense_rank() OVER (ORDER BY x), x FROM t
ORDER BY dense_rank;

query IT
SELECT dense_rank() OVER (ORDER BY x), x FROM t
ORDER BY dense_rank
----
1  a
2  b
3  c

statement ok
INSERT INTO t VALUES ('b');

query IT
SELECT dense_rank() OVER (ORDER BY x), x FROM t
ORDER BY dense_rank
----
1  a
2  b
2  b
3  c

statement ok
INSERT INTO t VALUES ('c');

query IT
SELECT dense_rank() OVER (ORDER BY x), x FROM t
ORDER BY dense_rank
----
1  a
2  b
2  b
3  c
3  c

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string);

statement ok
INSERT INTO t VALUES ('a'), ('b'), ('c');

query IT
SELECT dense_rank() OVER (ORDER BY x DESC), x FROM t
ORDER BY dense_rank
----
1  c
2  b
3  a

query IIT rowsort
SELECT rank() OVER (ORDER BY x), dense_rank() OVER (ORDER BY x), x FROM t
----
1  1  a
2  2  b
3  3  c

query IIT
SELECT rank() OVER (ORDER BY x DESC), dense_rank() OVER (ORDER BY x DESC), x FROM t
ORDER BY dense_rank
----
1  1  c
2  2  b
3  3  a

statement ok
INSERT INTO t VALUES ('b');

query IT
SELECT dense_rank() OVER (ORDER BY x DESC), x FROM t
ORDER BY dense_rank
----
1  c
2  b
2  b
3  a

query IIT
SELECT rank() OVER (ORDER BY x), dense_rank() OVER (ORDER BY x), x FROM t
----
1  1  a
2  2  b
2  2  b
4  3  c

query IIT
SELECT rank() OVER (ORDER BY x DESC), dense_rank() OVER (ORDER BY x DESC), x FROM t
ORDER BY dense_rank
----
1  1  c
2  2  b
2  2  b
4  3  a

statement ok
INSERT INTO t VALUES ('c');

query IT
SELECT dense_rank() OVER (ORDER BY x DESC), x FROM t
ORDER BY dense_rank
----
1  c
1  c
2  b
2  b
3  a

query IIT
SELECT rank() OVER (ORDER BY x DESC), dense_rank() OVER (ORDER BY x DESC), x FROM t
ORDER BY dense_rank
----
1  1  c
1  1  c
3  2  b
3  2  b
5  3  a

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string, y int);

statement ok
INSERT INTO t VALUES ('a', 98), ('b', 99), ('c', 98);

query IIT rowsort
SELECT rank() OVER (PARTITION BY y ORDER BY x), dense_rank() OVER (PARTITION BY y ORDER BY x), x FROM t;
----
1  1  a
1  1  b
2  2  c

query IT
SELECT dense_rank() OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY dense_rank, x;
----
1  a
1  b
2  c

statement ok
INSERT INTO t VALUES ('a', 98), ('a', 99);

query IIT
SELECT rank() OVER (PARTITION BY y ORDER BY x), dense_rank() OVER (PARTITION BY y ORDER BY x), x FROM t;
----
1  1  a
1  1  a
1  1  a
2  2  b
3  2  c

query IT
SELECT dense_rank() OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY dense_rank, x;
----
1  a
1  a
1  a
2  b
2  c

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string, y int);

statement ok
INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1);

query IIT
SELECT rank() OVER (PARTITION BY y ORDER BY x DESC), dense_rank() OVER (PARTITION BY y ORDER BY x DESC), x FROM t;
----
1  1  b
1  1  c
2  2  a

query IT
SELECT dense_rank() OVER (PARTITION BY y ORDER BY x DESC), x FROM t
ORDER BY dense_rank, x;
----
1  b
1  c
2  a

query IIT rowsort
SELECT rank() OVER (PARTITION BY x ORDER BY x), dense_rank() OVER (PARTITION BY x ORDER BY x), x FROM t;
----
1  1  a
1  1  b
1  1  c

query IT
SELECT dense_rank() OVER (PARTITION BY x ORDER BY x), x FROM t
ORDER BY dense_rank, x;
----
1  a
1  b
1  c

query IIT rowsort
SELECT rank() OVER (PARTITION BY NULL ORDER BY 10000), dense_rank() OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x
FROM t AS a1, t AS a2;
----
1  1  a
1  1  a
1  1  a
1  1  b
1  1  b
1  1  b
1  1  c
1  1  c
1  1  c

query IT
SELECT dense_rank() OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x
FROM t AS a1, t AS a2
ORDER BY q DESC, a1.x DESC;
----
1  c
1  c
1  c
1  b
1  b
1  b
1  a
1  a
1  a

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string);

statement ok
INSERT INTO t VALUES ('a');

# Make sure a non-column expression following the window function is correctly
# handled.
query IITT
SELECT rank() OVER (PARTITION BY NULL), dense_rank() OVER (PARTITION BY NULL) AS q, x, 'b'
FROM t;
----
1  1  a  b

query ITT
SELECT dense_rank() OVER (PARTITION BY NULL) AS q, x, 'b'
FROM t;
----
1  a  b

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x int, y string, z numeric);

statement ok
INSERT INTO t VALUES (1, 'a', 1.0), (2, 'a', 1.0), (2, 'a', 1.0), (3, 'a', 1.0), (4, 'b', 0), (4, 'b', 1), (1, 'c', 'NaN'), (2, 'c', 'NaN'), (3, 'c', 1.0);

query IIITT
SELECT rank() OVER (PARTITION BY y ORDER BY x DESC, z), dense_rank() OVER (PARTITION BY y ORDER BY x DESC, z), x, y, z
FROM t;
----
1  1  3  a  1
1  1  3  c  1
1  1  4  b  0
2  2  2  a  1
2  2  2  a  1
2  2  2  c  NaN
2  2  4  b  1
3  3  1  c  NaN
4  3  1  a  1

query IITT
SELECT dense_rank() OVER (PARTITION BY y ORDER BY x DESC, z), x, y, z
FROM t
ORDER BY y, x DESC, z;
----
1  3  a  1
2  2  a  1
2  2  a  1
3  1  a  1
1  4  b  0
2  4  b  1
1  3  c  1
2  2  c  NaN
3  1  c  NaN


# NaNs have the same rank
query IIITT
WITH t (x, y, z) AS (VALUES (1, 'a', 1.0), (2, 'a', 1.0), (2, 'a', 1.0), (3, 'a', 1.0), (4, 'b', 0), (4, 'b', 1), (1, 'c', 'NaN'), (2, 'c', 'NaN'), (3, 'c', 1.0))
SELECT rank() OVER (PARTITION BY y ORDER BY z DESC), dense_rank() OVER (PARTITION BY y ORDER BY z DESC), x, y, z
FROM t;
----
1  1  1  a  1
1  1  1  c  NaN
1  1  2  a  1
1  1  2  a  1
1  1  2  c  NaN
1  1  3  a  1
1  1  4  b  1
2  2  4  b  0
3  2  3  c  1

query IITT
SELECT dense_rank() OVER (PARTITION BY y ORDER BY z DESC), x, y, z
FROM t
ORDER BY y, z DESC, x;
----
1  1  a  1
1  2  a  1
1  2  a  1
1  3  a  1
1  4  b  1
2  4  b  0
1  1  c  NaN
1  2  c  NaN
2  3  c  1

## lag

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string);

statement ok
INSERT INTO t VALUES ('a'), ('b'), ('c');

# Simple cases
query TT
SELECT lag(x) OVER (ORDER BY x), x FROM t
ORDER BY x, lag
----
NULL  a
a     b
b     c

statement ok
INSERT INTO t VALUES ('b');

query TT
SELECT lag(x) OVER (ORDER BY x), x FROM t
ORDER BY x, lag
----
NULL  a
a     b
b     b
b     c

statement ok
INSERT INTO t VALUES ('c');

query TT
SELECT lag(x) OVER (ORDER BY x), x FROM t
ORDER BY x, lag
----
NULL  a
a     b
b     b
b     c
c     c

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string);

statement ok
INSERT INTO t VALUES ('a'), ('b'), ('c');

query TT
SELECT lag(x) OVER (ORDER BY x DESC), x FROM t
ORDER BY x, lag
----
b     a
c     b
NULL  c

statement ok
INSERT INTO t VALUES ('b');

query TT
SELECT lag(x) OVER (ORDER BY x DESC), x FROM t
ORDER BY x, lag
----
b     a
b     b
c     b
NULL  c

statement ok
INSERT INTO t VALUES ('c');

query TT
SELECT lag(x) OVER (ORDER BY x DESC), x FROM t
ORDER BY x, lag
----
b     a
b     b
c     b
c     c
NULL  c

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string, y int);

statement ok
INSERT INTO t VALUES ('a', 98), ('b', 99), ('c', 98);

query TT
SELECT lag(x) OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY x, lag
----
NULL  a
NULL  b
a     c

statement ok
INSERT INTO t VALUES ('a', 98), ('a', 99);

query TT
SELECT lag(x) OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY x, lag
----
a     a
NULL  a
NULL  a
a     b
a     c

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string, y int);

statement ok
INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1);

query TT
SELECT lag(x) OVER (PARTITION BY y ORDER BY x DESC), x FROM t
ORDER BY x, lag
----
c     a
NULL  b
NULL  c

query TT
SELECT lag(x) OVER (PARTITION BY x ORDER BY x), x FROM t
ORDER BY x, lag
----
NULL  a
NULL  b
NULL  c

query TT
SELECT lag(a1.x) OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x
FROM t AS a1, t AS a2
ORDER BY q DESC, a1.x DESC
----
NULL  a
c     c
c     c
b     c
b     b
b     b
a     b
a     a
a     a

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(f1 int, f2 string, f3 numeric);

statement ok
INSERT INTO t VALUES (1, 'a', 1.0), (2, 'a', 1.0), (2, 'a', 1.0), (3, 'a', 1.0), (4, 'b', 0), (4, 'b', 1), (1, 'c', 'NaN'), (2, 'c', 'NaN'), (3, 'c', 1.0), (7, 'd', -5.0);

query ITTT
SELECT f1, f2, f3, lag(f1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  1
3  c  1  2
4  b  1  NULL
4  b  0  4
1  a  1  NULL
2  a  1  1
2  a  1  2
3  a  1  2

query ITTT
SELECT f1, f2, f3, lag(f1, 1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  1
3  c  1  2
4  b  1  NULL
4  b  0  4
1  a  1  NULL
2  a  1  1
2  a  1  2
3  a  1  2

query ITTT
SELECT f1, f2, f3, lag(f1, 1, NULL) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  1
3  c  1  2
4  b  1  NULL
4  b  0  4
1  a  1  NULL
2  a  1  1
2  a  1  2
3  a  1  2


# With default value
query ITTT
SELECT f1, f2, f3, lag(f1, 1, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  -1
1  c  NaN  -1
2  c  NaN  1
3  c  1  2
4  b  1  -1
4  b  0  4
1  a  1  -1
2  a  1  1
2  a  1  2
3  a  1  2

# Complex expressions
query ITTT
SELECT f1, f2, f3, lag(f1 + coalesce(nullif(f3, 'NaN'), -10)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  -9
3  c  1  -8
4  b  1  NULL
4  b  0  5
1  a  1  NULL
2  a  1  2
2  a  1  3
3  a  1  3

# Nulls in the first argument
query ITTT
SELECT f1, f2, f3, lag(NULL::int) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lag(nullif(f1, 4)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  1
3  c  1  2
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  1
2  a  1  2
3  a  1  2

# Nulls in the first argument with a default value in the third argument
query ITTT
SELECT f1, f2, f3, lag(NULL::int, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL


# Zero offset
query ITTT
SELECT f1, f2, f3, lag(f1, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  7
1  c  NaN  1
2  c  NaN  2
3  c  1  3
4  b  1  4
4  b  0  4
1  a  1  1
2  a  1  2
2  a  1  2
3  a  1  3

query ITTT
SELECT f1, f2, f3, lag(f1 + f3, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  2
1  c  NaN  NaN
2  c  NaN  NaN
3  c  1  4
4  b  1  5
4  b  0  4
1  a  1  2
2  a  1  3
2  a  1  3
3  a  1  4

# Positive offsets
query ITTT
SELECT f1, f2, f3, lag(f1, 2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  1
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  1
2  a  1  NULL
3  a  1  2

query ITTT
SELECT f1, f2, f3, lag(f1 + f3, 2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NaN
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  2
2  a  1  NULL
3  a  1  3

# Out of range positive offsets

query ITTT
SELECT f1, f2, f3, lag(f1, 10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lag(f1 + f3, 10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lag(f1 + f3, 10, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  0
1  c  NaN  0
2  c  NaN  0
3  c  1  0
4  b  1  0
4  b  0  0
1  a  1  0
2  a  1  0
2  a  1  0
3  a  1  0

# Negative offsets
query ITTT
SELECT f1, f2, f3, lag(f1, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  2
2  c  NaN  3
3  c  1  NULL
4  b  1  4
4  b  0  NULL
1  a  1  2
2  a  1  2
2  a  1  3
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lag(f1 + f3, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NaN
2  c  NaN  4
3  c  1  NULL
4  b  1  4
4  b  0  NULL
1  a  1  3
2  a  1  3
2  a  1  4
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lag(f1 + f3, -2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  4
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  3
2  a  1  4
2  a  1  NULL
3  a  1  NULL

# Out of range negative offsets
query ITTT
SELECT f1, f2, f3, lag(f1, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lag(f1 + f3, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lag(f1, -10, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  0
1  c  NaN  0
2  c  NaN  0
3  c  1  0
4  b  1  0
4  b  0  0
1  a  1  0
2  a  1  0
2  a  1  0
3  a  1  0

# Variable per row offsets
query ITTT
SELECT f1, f2, f3, lag(f1, f1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  1
2  a  1  NULL
3  a  1  1

query ITTT
SELECT f1, f2, f3, lag(f1, f1 - 1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  1
2  c  NaN  1
3  c  1  1
4  b  1  NULL
4  b  0  NULL
1  a  1  1
2  a  1  1
2  a  1  2
3  a  1  2


# Null offsets
query ITTT
SELECT f1, f2, f3, lag(f1, NULL) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lag(f1, nullif(f1, 1)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  1
2  a  1  NULL
3  a  1  1

# Null offset with default value
query ITTT
SELECT f1, f2, f3, lag(f1, NULL, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

# Variable per row default value
query ITTT
SELECT f1, f2, f3, lag(f1, 1, f3) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lag
----
7  d  -5  -5
1  c  NaN  NaN
2  c  NaN  1
3  c  1  2
4  b  1  1
4  b  0  4
1  a  1  1
2  a  1  1
2  a  1  2
3  a  1  2

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(f1 int, f2 int);

statement ok
INSERT INTO t VALUES (1, 2), (1, 2), (3, 4);

# reduce_elision code path
# Default offset
query II
SELECT f1, lag(f1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  NULL
3  NULL

query II
SELECT f1, lag(f1, 1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  NULL
3  NULL

# Zero offset
query II
SELECT f1, lag(f1, 0) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  1
3  3

# Negative offset
query II
SELECT f1, lag(f1, -1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  NULL
3  NULL

# Default value with offset 1
query II
SELECT f1, lag(f1, 1, 10) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  10
3  10

# Default value with offset 0
query II
SELECT f1, lag(f1, 0, 10) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  1
3  3

# Complex expression
query II
SELECT f1, lag(f1 * f2, 0, 10) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  2
3  12

query II
SELECT f1, lag(f1 * f2, 1, f1 * f2 + 1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  3
3  13

# Complex offset
query II
SELECT f1, lag(f1 * f2, f1 - f1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  2
3  12

query II rowsort
SELECT f1, lag(f1 * f2, f1 - 1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
3  NULL
1  2

query II
SELECT f1, lag(f1 * f2, f2 - 2 * f1, f2) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  2
3  4

# Complex default value
query II
SELECT f1, lag(f1, 0, f1 * f2) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  1
3  3

query II
SELECT f1, lag(f1, 1, f1 * f2) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  2
3  12

# Null value in input relation
# This was caused by a bug in the reduce elision logic
statement ok
CREATE TABLE t3 (f1 INTEGER)
----

statement ok
INSERT INTO t3 VALUES (NULL)
----

query II
SELECT f1, lag(0, f1 , 0) OVER (PARTITION BY f1 ORDER BY f1) FROM t3 GROUP BY f1 ORDER BY 1
----
NULL NULL

## lead

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string);

statement ok
INSERT INTO t VALUES ('a'), ('b'), ('c');

# Simple cases
query TT
SELECT lead(x) OVER (ORDER BY x), x FROM t
ORDER BY x, lead
----
b     a
c     b
NULL  c

statement ok
INSERT INTO t VALUES ('b');

query TT
SELECT lead(x) OVER (ORDER BY x), x FROM t
ORDER BY x, lead
----
b     a
b     b
c     b
NULL  c

statement ok
INSERT INTO t VALUES ('c');

query TT
SELECT lead(x) OVER (ORDER BY x), x FROM t
ORDER BY x, lead
----
b     a
b     b
c     b
c     c
NULL  c

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string);

statement ok
INSERT INTO t VALUES ('a'), ('b'), ('c');

query TT
SELECT lead(x) OVER (ORDER BY x DESC), x FROM t
ORDER BY x, lead
----
NULL  a
a     b
b     c

statement ok
INSERT INTO t VALUES ('b');

query TT
SELECT lead(x) OVER (ORDER BY x DESC), x FROM t
ORDER BY x, lead
----
NULL  a
a     b
b     b
b     c

statement ok
INSERT INTO t VALUES ('c');

query TT
SELECT lead(x) OVER (ORDER BY x DESC), x FROM t
ORDER BY x, lead
----
NULL  a
a     b
b     b
b     c
c     c

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string, y int);

statement ok
INSERT INTO t VALUES ('a', 98), ('b', 99), ('c', 98);

query TT
SELECT lead(x) OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY x, lead
----
c     a
NULL  b
NULL  c

statement ok
INSERT INTO t VALUES ('a', 98), ('a', 99);

query TT
SELECT lead(x) OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY x, lead
----
a  a
b  a
c  a
NULL  b
NULL  c

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(x string, y int);

statement ok
INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1);

query TT
SELECT lead(x) OVER (PARTITION BY y ORDER BY x DESC), x FROM t
ORDER BY x, lead
----
NULL  a
NULL  b
a     c

query TT
SELECT lead(x) OVER (PARTITION BY x ORDER BY x), x FROM t
ORDER BY x, lead
----
NULL  a
NULL  b
NULL  c

query TT
SELECT lead(a1.x) OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x
FROM t AS a1, t AS a2
ORDER BY q DESC, a1.x DESC
----
NULL  c
c     c
c     c
c     b
b     b
b     b
b     a
a     a
a     a

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(f1 int, f2 string, f3 numeric);

statement ok
INSERT INTO t VALUES (1, 'a', 1.0), (2, 'a', 1.0), (2, 'a', 1.0), (3, 'a', 1.0), (4, 'b', 0), (4, 'b', 1), (1, 'c', 'NaN'), (2, 'c', 'NaN'), (3, 'c', 1.0), (7, 'd', -5.0);

query ITTT
SELECT f1, f2, f3, lead(f1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   NULL
1  c  NaN  2
2  c  NaN  3
3  c  1    NULL
4  b  1    4
4  b  0    NULL
1  a  1    2
2  a  1    2
2  a  1    3
3  a  1    NULL

query ITTT
SELECT f1, f2, f3, lead(f1, 1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   NULL
1  c  NaN  2
2  c  NaN  3
3  c  1    NULL
4  b  1    4
4  b  0    NULL
1  a  1    2
2  a  1    2
2  a  1    3
3  a  1    NULL

query ITTT
SELECT f1, f2, f3, lead(f1, 1, NULL) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   NULL
1  c  NaN  2
2  c  NaN  3
3  c  1    NULL
4  b  1    4
4  b  0    NULL
1  a  1    2
2  a  1    2
2  a  1    3
3  a  1    NULL


# With default value
query ITTT
SELECT f1, f2, f3, lead(f1, 1, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  -1
1  c  NaN  2
2  c  NaN  3
3  c  1   -1
4  b  1    4
4  b  0   -1
1  a  1    2
2  a  1    2
2  a  1    3
3  a  1   -1

# Complex expressions
query ITTT
SELECT f1, f2, f3, lead(f1 + coalesce(nullif(f3, 'NaN'), -10)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5    NULL
1  c  NaN  -8
2  c  NaN   4
3  c  1     NULL
4  b  1     4
4  b  0     NULL
1  a  1     3
2  a  1     3
2  a  1     4
3  a  1     NULL

# Nulls in the first argument
query ITTT
SELECT f1, f2, f3, lead(NULL::int) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1    NULL
4  b  1    NULL
4  b  0    NULL
1  a  1    NULL
2  a  1    NULL
2  a  1    NULL
3  a  1    NULL

query ITTT
SELECT f1, f2, f3, lead(nullif(f1, 4)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5    NULL
1  c  NaN  2
2  c  NaN  3
3  c  1    NULL
4  b  1    NULL
4  b  0    NULL
1  a  1    2
2  a  1    2
2  a  1    3
3  a  1    NULL

# Nulls in the first argument with a default value in the third argument
query ITTT
SELECT f1, f2, f3, lead(NULL::int, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1    NULL
4  b  1    NULL
4  b  0    NULL
1  a  1    NULL
2  a  1    NULL
2  a  1    NULL
3  a  1    NULL


# Zero offset
query ITTT
SELECT f1, f2, f3, lead(f1, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   7
1  c  NaN  1
2  c  NaN  2
3  c  1    3
4  b  1    4
4  b  0    4
1  a  1    1
2  a  1    2
2  a  1    2
3  a  1    3

query ITTT
SELECT f1, f2, f3, lead(f1 + f3, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   2
1  c  NaN  NaN
2  c  NaN  NaN
3  c  1    4
4  b  1    5
4  b  0    4
1  a  1    2
2  a  1    3
2  a  1    3
3  a  1    4

# Positive offsets
query ITTT
SELECT f1, f2, f3, lead(f1, 2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   NULL
1  c  NaN  3
2  c  NaN  NULL
3  c  1    NULL
4  b  1    NULL
4  b  0    NULL
1  a  1    2
2  a  1    3
2  a  1    NULL
3  a  1    NULL

query ITTT
SELECT f1, f2, f3, lead(f1 + f3, 2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   NULL
1  c  NaN  4
2  c  NaN  NULL
3  c  1    NULL
4  b  1    NULL
4  b  0    NULL
1  a  1    3
2  a  1    4
2  a  1    NULL
3  a  1    NULL

# Out of range positive offsets

query ITTT
SELECT f1, f2, f3, lead(f1, 10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1    NULL
4  b  1    NULL
4  b  0    NULL
1  a  1    NULL
2  a  1    NULL
2  a  1    NULL
3  a  1    NULL

query ITTT
SELECT f1, f2, f3, lead(f1 + f3, 10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1    NULL
4  b  1    NULL
4  b  0    NULL
1  a  1    NULL
2  a  1    NULL
2  a  1    NULL
3  a  1    NULL

query ITTT
SELECT f1, f2, f3, lead(f1 + f3, 10, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5   0
1  c  NaN  0
2  c  NaN  0
3  c  1    0
4  b  1    0
4  b  0    0
1  a  1    0
2  a  1    0
2  a  1    0
3  a  1    0

# Negative offsets
query ITTT
SELECT f1, f2, f3, lead(f1, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  1
3  c  1  2
4  b  1  NULL
4  b  0  4
1  a  1  NULL
2  a  1  1
2  a  1  2
3  a  1  2

query ITTT
SELECT f1, f2, f3, lead(f1 + f3, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NaN
3  c  1  NaN
4  b  1  NULL
4  b  0  5
1  a  1  NULL
2  a  1  2
2  a  1  3
3  a  1  3

query ITTT
SELECT f1, f2, f3, lead(f1 + f3, -2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NaN
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  2
2  a  1  NULL
3  a  1  3

# Out of range negative offsets
query ITTT
SELECT f1, f2, f3, lead(f1, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lead(f1 + f3, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lead(f1, -10, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  0
1  c  NaN  0
2  c  NaN  0
3  c  1  0
4  b  1  0
4  b  0  0
1  a  1  0
2  a  1  0
2  a  1  0
3  a  1  0

# Variable per row offsets
query ITTT
SELECT f1, f2, f3, lead(f1, f1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  2
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  2
2  a  1  3
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lead(f1, f1 - 1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  1
2  c  NaN  3
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  1
2  a  1  2
2  a  1  3
3  a  1  NULL


# Null offsets
query ITTT
SELECT f1, f2, f3, lead(f1, NULL) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

query ITTT
SELECT f1, f2, f3, lead(f1, nullif(f1, 1)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  3
2  a  1  NULL
3  a  1  NULL

# Null offset with default value
query ITTT
SELECT f1, f2, f3, lead(f1, NULL, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  NULL
1  c  NaN  NULL
2  c  NaN  NULL
3  c  1  NULL
4  b  1  NULL
4  b  0  NULL
1  a  1  NULL
2  a  1  NULL
2  a  1  NULL
3  a  1  NULL

# Variable per row default value
query ITTT
SELECT f1, f2, f3, lead(f1, 1, f3) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
ORDER BY f2 DESC, f3 DESC, f1, lead
----
7  d  -5  -5
1  c  NaN  2
2  c  NaN  3
3  c  1  1
4  b  1  4
4  b  0  0
1  a  1  2
2  a  1  2
2  a  1  3
3  a  1  1

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(f1 int, f2 int);

statement ok
INSERT INTO t VALUES (1, 2), (1, 2), (3, 4);

# reduce_elision code path
# Default offset
query II
SELECT f1, lead(f1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  NULL
3  NULL

query II
SELECT f1, lead(f1, 1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  NULL
3  NULL

# Zero offset
query II
SELECT f1, lead(f1, 0) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  1
3  3

# Negative offset
query II
SELECT f1, lead(f1, -1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  NULL
3  NULL

# Default value with offset 1
query II
SELECT f1, lead(f1, 1, 10) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  10
3  10

# Default value with offset 0
query II
SELECT f1, lead(f1, 0, 10) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  1
3  3

# Complex expression
query II
SELECT f1, lead(f1 * f2, 0, 10) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  2
3  12

query II
SELECT f1, lead(f1 * f2, 1, f1 * f2 + 1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  3
3  13

# Complex offset
query II
SELECT f1, lead(f1 * f2, f1 - f1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  2
3  12

query II
SELECT f1, lead(f1 * f2, f1 - 1) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  2
3  NULL

query II
SELECT f1, lead(f1 * f2, f2 - 2 * f1, f2) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  2
3  4

# Complex default value
query II
SELECT f1, lead(f1, 0, f1 * f2) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  1
3  3

query II
SELECT f1, lead(f1, 1, f1 * f2) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
ORDER BY 1, 2
----
1  2
3  12

# Null value in input relation
# This was caused by a bug in the reduce elision logic
statement ok
CREATE TABLE t4 (f1 INTEGER)
----

statement ok
INSERT INTO t4 VALUES (NULL)
----

query II
SELECT f1, lead(0, f1 , 0) OVER (PARTITION BY f1 ORDER BY f1) FROM t4 GROUP BY f1 ORDER BY 1
----
NULL NULL

## lag/lead ignore nulls

statement ok
create table t6(x int, y int);

statement ok
insert into t6 values (1,2), (3,null), (5,6), (7,8), (9, null), (11, null), (13, 14), (15, 16), (17, 18);

query IIIIIIIIIIIIIIIIIIII
select
  x,
  y,
  lag(y) over (order by x) as lag1,
  lag(y) respect nulls over (order by x) as lag1_resp,
  lag(y) ignore nulls over (order by x) as lag1_ign,
  lead(y) over (order by x) as lead1,
  lead(y) ignore nulls over (order by x) as lead1_ign,
  lag(y, 2) over (order by x) as lag2,
  lag(y, 2) ignore nulls over (order by x) as lag2_ign,
  lead(y, 2) over (order by x) as lead2,
  lead(y, 2) ignore nulls over (order by x) as lead2_ign,
  lag(y, 2, -1) ignore nulls over (order by x) as lag2_ign_def,
  lead(y, 2, -1) ignore nulls over (order by x) as lead2_ign_def,
  lag(y, 2, 16) ignore nulls over (order by x) as lag2_ign_def16,
  lead(y, 2, 16) ignore nulls over (order by x) as lead2_ign_def16,
  lag(y, -1, 100) ignore nulls over (order by x) as lag_neg_offset,
  lead(y, -2, 100) ignore nulls over (order by x) as lead_neg_offset,
  lag(y, y/5+1) ignore nulls over (order by x) as lag_dynamic_offset,
  lead(y, y/5+1) ignore nulls over (order by x) as lead_dynamic_offset,
  lag(y, null) ignore nulls over (order by x) as lag_literal_null_offset
from t6
order by x;
----
1  2  NULL  NULL  NULL  NULL  6  NULL  NULL  6  8  -1  8  16  8  6  100  NULL  6  NULL
3  NULL  2  2  2  6  6  NULL  NULL  8  8  -1  8  16  8  6  100  NULL  NULL  NULL
5  6  NULL  NULL  2  8  8  2  NULL  NULL  14  -1  14  16  14  8  100  NULL  14  NULL
7  8  6  6  6  NULL  14  NULL  2  NULL  16  2  16  2  16  14  2  2  16  NULL
9  NULL  8  8  8  NULL  14  6  6  14  16  6  16  6  16  14  6  NULL  NULL  NULL
11  NULL  NULL  NULL  8  14  14  8  6  16  16  6  16  6  16  14  6  NULL  NULL  NULL
13  14  NULL  NULL  8  16  16  NULL  6  18  18  6  18  6  18  16  6  2  NULL  NULL
15  16  14  14  14  18  18  NULL  8  NULL  NULL  8  -1  8  16  18  8  2  NULL  NULL
17  18  16  16  16  NULL  NULL  14  14  NULL  NULL  14  -1  14  16  100  14  6  NULL  NULL

# Same as above, but with a `partition by`

query IIIIIIIIIIIIIIIIIIII
select
  x,
  y,
  lag(y) over (partition by x%4 order by x) as lag1,
  lag(y) respect nulls over (partition by x%4 order by x) as lag1_resp,
  lag(y) ignore nulls over (partition by x%4 order by x) as lag1_ign,
  lead(y) over (partition by x%4 order by x) as lead1,
  lead(y) ignore nulls over (partition by x%4 order by x) as lead1_ign,
  lag(y, 2) over (partition by x%4 order by x) as lag2,
  lag(y, 2) ignore nulls over (partition by x%4 order by x) as lag2_ign,
  lead(y, 2) over (partition by x%4 order by x) as lead2,
  lead(y, 2) ignore nulls over (partition by x%4 order by x) as lead2_ign,
  lag(y, 2, -1) ignore nulls over (partition by x%4 order by x) as lag2_ign_def,
  lead(y, 2, -1) ignore nulls over (partition by x%4 order by x) as lead2_ign_def,
  lag(y, 2, 16) ignore nulls over (partition by x%4 order by x) as lag2_ign_def16,
  lead(y, 2, 16) ignore nulls over (partition by x%4 order by x) as lead2_ign_def16,
  lag(y, -1, 100) ignore nulls over (partition by x%4 order by x) as lag_neg_offset,
  lead(y, -2, 100) ignore nulls over (partition by x%4 order by x) as lead_neg_offset,
  lag(y, y/5+1) ignore nulls over (partition by x%4 order by x) as lag_dynamic_offset,
  lead(y, y/5+1) ignore nulls over (partition by x%4 order by x) as lead_dynamic_offset,
  lag(y, null) ignore nulls over (partition by x%4 order by x) as lag_literal_null_offset
from t6
order by x%4, x;
----
1  2  NULL  NULL  NULL  6  6  NULL  NULL  NULL  14  -1  14  16  14  6  100  NULL  6  NULL
5  6  2  2  2  NULL  14  NULL  NULL  14  18  -1  18  16  18  14  100  NULL  18  NULL
9  NULL  6  6  6  14  14  2  2  18  18  2  18  2  18  14  2  NULL  NULL  NULL
13  14  NULL  NULL  6  18  18  6  2  NULL  NULL  2  -1  2  16  18  2  NULL  NULL  NULL
17  18  14  14  14  NULL  NULL  NULL  6  NULL  NULL  6  -1  6  16  100  6  NULL  NULL  NULL
3  NULL  NULL  NULL  NULL  8  8  NULL  NULL  NULL  16  -1  16  16  16  8  100  NULL  NULL  NULL
7  8  NULL  NULL  NULL  NULL  16  NULL  NULL  16  NULL  -1  -1  16  16  16  100  NULL  NULL  NULL
11  NULL  8  8  8  16  16  NULL  NULL  NULL  NULL  -1  -1  16  16  16  100  NULL  NULL  NULL
15  16  NULL  NULL  8  NULL  NULL  8  NULL  NULL  NULL  -1  -1  16  16  100  100  NULL  NULL  NULL

statement ok
CREATE VIEW t6_more_nulls AS
SELECT
  x,
  CASE WHEN y < 15 AND y > 7 THEN null ELSE y END AS y
FROM t6;

query IIIIIIII
select
  x,
  y,
  lag(y) ignore nulls over (order by x) as lag1,
  lag(y,2) ignore nulls over (order by x) as lag2,
  lag(y,3) ignore nulls over (order by x) as lag3,
  lead(y) ignore nulls over (order by x) as lead1,
  lead(y,2) ignore nulls over (order by x) as lead2,
  lead(y,3) ignore nulls over (order by x) as lead3
from t6_more_nulls
order by x;
----
1  2  NULL  NULL  NULL  6  16  18
3  NULL  2  NULL  NULL  6  16  18
5  6  2  NULL  NULL  16  18  NULL
7  NULL  6  2  NULL  16  18  NULL
9  NULL  6  2  NULL  16  18  NULL
11  NULL  6  2  NULL  16  18  NULL
13  NULL  6  2  NULL  16  18  NULL
15  16  6  2  NULL  18  NULL  NULL
17  18  16  6  2  NULL  NULL  NULL

# Test the `offset = 0` code path. This currently doesn't work with nulls if IGNORE NULLS is specified,
# so we just filter out nulls for now.
# See https://materializeinc.slack.com/archives/C063H5S7NKE/p1724962369706729
# Also tests negative dynamic offsets.
statement ok
CREATE VIEW t6_no_nulls AS
SELECT
  x,
  COALESCE(y,-11) AS y
FROM t6;

query IIIIIII
SELECT
  x,
  y,
  y/10,
  lag(y, y/10) OVER (ORDER BY x),
  lag(y, y/10) IGNORE NULLS OVER (ORDER BY x),
  lead(y, y/10) OVER (ORDER BY x),
  lead(y, y/10) IGNORE NULLS OVER (ORDER BY x)
FROM t6_no_nulls
ORDER BY x;
----
1  2  0  2  2  2  2
3  -11  -1  6  6  2  2
5  6  0  6  6  6  6
7  8  0  8  8  8  8
9  -11  -1  -11  -11  8  8
11  -11  -1  14  14  -11  -11
13  14  1  -11  -11  16  16
15  16  1  14  14  18  18
17  18  1  16  16  NULL  NULL

query error db error: ERROR: IGNORE NULLS and RESPECT NULLS options for functions other than LAG and LEAD not yet supported
select first_value(x) ignore nulls over() from t6;

query error db error: ERROR: IGNORE NULLS and RESPECT NULLS options for functions other than LAG and LEAD not yet supported
select row_number() ignore nulls over();

query error db error: ERROR: Both IGNORE NULLS and RESPECT NULLS were given\.
select row_number() ignore nulls respect nulls over();

## window frames

# Invalid frame start
query error frame start cannot be UNBOUNDED FOLLOWING
SELECT row_number() OVER (ROWS UNBOUNDED FOLLOWING)

# Invalid frame end
query error frame end cannot be UNBOUNDED PRECEDING
SELECT row_number() OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING)

# End frame can't be of a type that comes before the start bound
query error frame starting from current row cannot have preceding rows
SELECT row_number() OVER (ROWS BETWEEN CURRENT ROW AND 1 PRECEDING)

query error frame starting from following row cannot have preceding rows
SELECT row_number() OVER (ROWS BETWEEN 1 FOLLOWING AND 1 PRECEDING)

query error frame starting from following row cannot have preceding rows
SELECT row_number() OVER (ROWS BETWEEN 1 FOLLOWING AND CURRENT ROW)

# But end offsets can come before start offsets
query I
SELECT row_number() OVER (ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING)
----
1

query I
SELECT row_number() OVER (ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING)
----
1

# Negative offsets are not allowed
# Our error message is different from Postgres, so it's not checked here
query error
SELECT row_number() OVER (ROWS -1 PRECEDING)

query error
SELECT row_number() OVER (ROWS -1 FOLLOWING)

# Current implementation restrictions
# RANGE is not supported outside of the default frame
query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN 1 FOLLOWING AND 1 FOLLOWING)

query error RANGE in non-default window frames not yet supported
SELECT row_number() OVER (RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)

# Default window frame works fine
query I
SELECT row_number() OVER ()
----
1

query I
SELECT row_number() OVER (RANGE UNBOUNDED PRECEDING)
----
1

query I
SELECT row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
----
1

# GROUPS is not supported at all
query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN 1 PRECEDING AND 1 PRECEDING)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN CURRENT ROW AND CURRENT ROW)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)

query error GROUPS in window frames not yet supported
SELECT row_number() OVER (GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)

## first_value

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t(f1 int, f2 string, f3 int);

statement ok
INSERT INTO t VALUES (1, 'a', 1), (2, 'a', 2), (2, 'a', 3), (3, 'a', 4), (4, 'b', 5), (4, 'b', 6), (1, 'c', 7), (2, 'c', 8), (3, 'c', 9), (7, 'd', 10);

# Default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN UNBOUNDED PRECEDING AND x PRECEDING
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  NULL
4  b  6  4
1  c  7  NULL
2  c  8  1
3  c  9  1
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  1
3  a  4  1
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  1
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 10 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN UNBOUNDED PRECEDING AND x FOLLOWING
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN 0 PRECEDING AND x PRECEDING
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 2 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 10 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN x PRECEDING AND 0 PRECEDING
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN x PRECEDING AND y PRECEDING, where x < y
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN x PRECEDING AND y PRECEDING, where x > y
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  NULL
4  b  6  4
1  c  7  NULL
2  c  8  1
3  c  9  1
7  d  10  NULL

# ROWS BETWEEN x PRECEDING AND y PRECEDING, where x == y
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  NULL
4  b  6  4
1  c  7  NULL
2  c  8  1
3  c  9  2
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  1
3  a  4  2
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  1
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND 10 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN x PRECEDING AND CURRENT ROW
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN x PRECEDING AND x FOLLOWING
# Equivalent to ROWS BETWEEN x PRECEDING AND CURRENT ROW for first_value
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN x PRECEDING AND UNBOUNDED FOLLOWING
# Equivalent to ROWS BETWEEN x PRECEDING AND CURRENT ROW for first_value
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN CURRENT ROW AND CURRENT ROW
# Always returns current row
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

# ROWS BETWEEN CURRENT ROW AND x FOLLOWING
# Always returns current row
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

# ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
# Always returns current row
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

# ROWS BETWEEN 0 FOLLOWING AND x FOLLOWING
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 10 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

# ROWS BETWEEN x FOLLOWING AND 0 FOLLOWING
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN x FOLLOWING AND y FOLLOWING, where x < y
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  2
2  a  2  2
2  a  3  3
3  a  4  NULL
4  b  5  4
4  b  6  NULL
1  c  7  2
2  c  8  3
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN x FOLLOWING AND y FOLLOWING, where x > y
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN x FOLLOWING AND y FOLLOWING, where x == y
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  2
2  a  2  2
2  a  3  3
3  a  4  NULL
4  b  5  4
4  b  6  NULL
1  c  7  2
2  c  8  3
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  2
2  a  2  3
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  3
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 FOLLOWING AND 10 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN x FOLLOWING AND UNBOUNDED FOLLOWING
query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  2
2  a  2  2
2  a  3  3
3  a  4  NULL
4  b  5  4
4  b  6  NULL
1  c  7  2
2  c  8  3
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  2
2  a  2  3
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  3
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# Test offset limits
query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 100 FOLLOWING AND 1000001 FOLLOWING)

# Test near-overflow behavior on offsets
# u64::MAX FOLLOWING
query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 18446744073709551615 FOLLOWING AND 18446744073709551615 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, first_value

query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1000001 PRECEDING AND 100 FOLLOWING)

query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 18446744073709551614 FOLLOWING AND 18446744073709551615 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value

query error Expected literal unsigned integer, found operator "\-"
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND -1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value

# u64::MAX PRECEDING
query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 18446744073709551615 PRECEDING AND 18446744073709551615 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value

query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 18446744073709551615 PRECEDING AND 18446744073709551614 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1000 PRECEDING AND 1000 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1000 PRECEDING AND 999 PRECEDING)
FROM t
ORDER BY f2, f3, f1, first_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# reduce_elision code path
statement ok
CREATE TABLE t5 (f1 INTEGER)
----

statement ok
INSERT INTO t5 VALUES (1)
----

# Default frame, includes current row
query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1)
FROM t5
GROUP BY f1
----
1 1

# Frame starting at UNBOUNDED PRECEDING
query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

# Frame starting at 1 PRECEDING
query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

# Frame starting at 0 PRECEDING
query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND CURRENT ROW)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

# Frame starting at CURRENT ROW
query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

# Frame starting at 0 FOLLOWING
query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

# Frame starting at 1 FOLLOWING
query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 NULL

## last_value

# Default frame (RANGE BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW)
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

# Default frame with large peer group
# Note: there are multiple valid results of this query (because the default frame is up through the current row's last
# ORDER BY peer, and the ordering among ORDER BY peers is unspecified), but we make the result stable by internally
# always adding all remaining columns into our orderings.
query ITII
SELECT f1, f2, f3, last_value(f3) OVER (PARTITION BY f2 ORDER BY f1)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  3
2  a  3  3
3  a  4  4
4  b  5  6
4  b  6  6
1  c  7  7
2  c  8  8
3  c  9  9
7  d  10  10

# ROWS BETWEEN x FOLLOWING AND UNBOUNDED FOLLOWING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  NULL
4  b  6  4
1  c  7  NULL
2  c  8  1
3  c  9  1
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  1
3  a  4  1
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  1
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN x PRECEDING AND UNBOUNDED FOLLOWING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN x FOLLOWING AND 0 FOLLOWING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN 0 FOLLOWING AND x FOLLOWING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 10 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN y FOLLOWING AND x FOLLOWING, where x < y
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN y FOLLOWING AND x FOLLOWING, where x > y
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  NULL
4  b  6  4
1  c  7  NULL
2  c  8  1
3  c  9  1
7  d  10  NULL

# ROWS BETWEEN x FOLLOWING AND y FOLLOWING, where x == y
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  NULL
4  b  6  4
1  c  7  NULL
2  c  8  1
3  c  9  2
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  1
3  a  4  2
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  1
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 FOLLOWING AND 10 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN CURRENT ROW AND x FOLLOWING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN x PRECEDING AND x FOLLOWING
# Equivalent to ROWS BETWEEN CURRENT ROW AND x FOLLOWING for last_value
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 10 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN UNBOUNDED PRECEDING AND x FOLLOWING
# Equivalent to ROWS BETWEEN CURRENT ROW AND x FOLLOWING for last_value
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  2
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  2
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  2
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  1
2  a  3  1
3  a  4  1
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  1
3  c  9  1
7  d  10  7

# ROWS BETWEEN CURRENT ROW AND CURRENT ROW
# Always returns current row
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

# ROWS BETWEEN x PRECEDING AND CURRENT ROW
# Always returns current row
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

# ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
# Always returns current row
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

# ROWS BETWEEN x PRECEDING AND 0 PRECEDING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

# ROWS BETWEEN 0 PRECEDING AND x PRECEDING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 2 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 10 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN y PRECEDING AND x PRECEDING, where x < y
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  2
2  a  2  2
2  a  3  3
3  a  4  NULL
4  b  5  4
4  b  6  NULL
1  c  7  2
2  c  8  3
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN y PRECEDING AND x PRECEDING, where x > y
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN y PRECEDING AND x PRECEDING, where x == y
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  2
2  a  2  2
2  a  3  3
3  a  4  NULL
4  b  5  4
4  b  6  NULL
1  c  7  2
2  c  8  3
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  2
2  a  2  3
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  3
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND 10 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# ROWS BETWEEN UNBOUNDED PRECEDING AND x PRECEDING
query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  1
2  a  2  2
2  a  3  2
3  a  4  3
4  b  5  4
4  b  6  4
1  c  7  1
2  c  8  2
3  c  9  3
7  d  10  7

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  2
2  a  2  2
2  a  3  3
3  a  4  NULL
4  b  5  4
4  b  6  NULL
1  c  7  2
2  c  8  3
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  2
2  a  2  3
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  3
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 10 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# Test near-overflow behavior on offsets
# u64::MAX FOLLOWING

# u64::MAX PRECEDING
query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 18446744073709551615 FOLLOWING AND 18446744073709551615 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value

query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 18446744073709551614 FOLLOWING AND 18446744073709551615 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1000 FOLLOWING AND 1000 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

query ITII
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1000 FOLLOWING AND 1001 FOLLOWING)
FROM t
ORDER BY f2, f3, f1, last_value
----
1  a  1  NULL
2  a  2  NULL
2  a  3  NULL
3  a  4  NULL
4  b  5  NULL
4  b  6  NULL
1  c  7  NULL
2  c  8  NULL
3  c  9  NULL
7  d  10  NULL

# reduce_elision code path
# Using the same table as first_value

# Default frame, includes current row
query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1)
FROM t5
GROUP BY f1
----
1 1

# Frame ending at UNBOUNDED FOLLOWING
query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

# Frame ending at 1 FOLLOWING
query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

# Frame ending at 0 FOLLOWING
query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING)
FROM t5
GROUP BY f1
----
1 1

# Frame ending at CURRENT ROW
query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND CURRENT ROW)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t5
GROUP BY f1
----
1 1

# Frame ending at 0 PRECEDING
query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING)
FROM t5
GROUP BY f1
----
1 1

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)
FROM t5
GROUP BY f1
----
1 1

# Frame ending at 1 PRECEDING
query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
FROM t5
GROUP BY f1
----
1 NULL

query II
SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM t5
GROUP BY f1
----
1 NULL

# Check some HIR plans to verify that the lifting of window functions to the top of Maps is actually happening.

statement ok
CREATE TABLE foo (
    a int,
    b text
);

statement ok
INSERT INTO foo (a, b) VALUES (0, 'zero'), (1, 'one'), (2, 'two'), (3, 'three');

query T multiline
EXPLAIN RAW PLAN FOR
SELECT 3 + lag(a) OVER (ORDER BY a) + 5 + 27
FROM foo;
----
Project (#3)
  Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (((3 + #2{?column?}) + 5) + 27))
    Get materialize.public.foo

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN FOR
SELECT -lag(10*right_a+3) OVER (ORDER BY right_a NULLS FIRST), -sum(foo.a), b
FROM foo LEFT JOIN (SELECT a AS right_a FROM foo WHERE a<2) ON foo.a = right_a
GROUP BY b, right_a;
----
Project (#4, #5, #0)
  Map (lag(row(((10 * #1{right_a}) + 3), 1, null)) over (order by [#1{right_a} asc nulls_first]), -(#3{?column?}), -(#2{?column?}))
    Reduce group_by=[#3, #4] aggregates=[sum(#0{a})]
      Map (#1{b}, #2{right_a})
        LeftOuterJoin (#0{a} = #2{right_a})
          Get materialize.public.foo
          Project (#0)
            Filter (#0{a} < 2)
              Get materialize.public.foo

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN FOR
SELECT 3 + lag(a) OVER (ORDER BY a) AS o
FROM foo
ORDER BY o;
----
Finish order_by=[#0 asc nulls_last] output=[#0]
  Project (#3)
    Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (3 + #2{?column?}))
      Get materialize.public.foo

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN FOR
SELECT
  a,
  3 + lag(a) OVER (ORDER BY a),
  3 + lag(a) OVER (ORDER BY a),
  lag(a) OVER (ORDER BY a),
  lead(a) OVER (ORDER BY a),
  5 + lag(a) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
Finish order_by=[#0 asc nulls_last] output=[#0..=#5]
  Project (#0, #4, #4, #2, #3, #5)
    Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), lead(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (3 + #2{?column?}), (5 + #2{?column?}))
      Get materialize.public.foo

Target cluster: quickstart

EOF

query IIIIII nosort
SELECT
  a,
  3 + lag(a) OVER (ORDER BY a),
  3 + lag(a) OVER (ORDER BY a),
  lag(a) OVER (ORDER BY a),
  lead(a) OVER (ORDER BY a),
  5 + lag(a) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  NULL  NULL  NULL  1  NULL
1  3  3  0  2  5
2  4  4  1  3  6
3  5  5  2  NULL  7

# Two window function calls in the same HirScalarExpr, but both calls are the same

query T multiline
EXPLAIN RAW PLAN FOR
SELECT a, b, lag(a) OVER (ORDER BY a) + lag(a) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
Finish order_by=[#0 asc nulls_last] output=[#0..=#2]
  Project (#0, #1, #3)
    Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (#2{?column?} + #2{?column?}))
      Get materialize.public.foo

Target cluster: quickstart

EOF

query ITI nosort
SELECT a, b, lag(a) OVER (ORDER BY a) + lag(a) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  NULL
1  one  0
2  two  2
3  three  4

# Two window function calls in the same HirScalarExpr, and they are different

query T multiline
EXPLAIN RAW PLAN FOR
SELECT a, b, lag(a) OVER (ORDER BY a) + lead(a) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
Finish order_by=[#0 asc nulls_last] output=[#0..=#2]
  Project (#0, #1, #4)
    Map (lead(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (#3{?column?} + #2{?column?}))
      Get materialize.public.foo

Target cluster: quickstart

EOF

query ITI nosort
SELECT a, b, lag(a) OVER (ORDER BY a) + lead(a) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  NULL
1  one  2
2  two  4
3  three  NULL

# Window function in DISTINCT ON

query T multiline
EXPLAIN RAW PLAN FOR
SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) *
FROM foo;
----
Project (#0, #1)
  TopK group_by=[#3] limit=1
    Map (lag(row(#0{a}, 1, 0)) over (order by [#0{a} asc nulls_last]), (1 + #2{?column?}))
      Get materialize.public.foo

Target cluster: quickstart

EOF

query IIT
SELECT 1 + lag(a, 1, 0) OVER (ORDER BY a), *
FROM foo
ORDER BY a;
----
1  0  zero
1  1  one
2  2  two
3  3  three

query IT
SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) *
FROM foo
ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a);
----
0  zero
2  two
3  three

query IIT nosort
SELECT 1 + lag(a, 1, 0) OVER (ORDER BY a), *
FROM foo
ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a), a ASC;
----
1  0  zero
1  1  one
2  2  two
3  3  three

query IT nosort
SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) *
FROM foo
ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a), a ASC;
----
0  zero
2  two
3  three

query error db error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) *
FROM foo
ORDER BY lag(a, 1, 0) OVER (ORDER BY a), a DESC;

query IIIT nosort
SELECT 1 + lag(a, 1, 0) OVER (ORDER BY a), lead(a) OVER (ORDER BY b) AS o, a, b
FROM foo
ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a), o DESC;
----
1  NULL  0  zero
1  3  1  one
2  0  2  two
3  2  3  three

query IIT nosort
SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) lead(a) OVER (ORDER BY b) AS o, a, b
FROM foo
ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a), o DESC;
----
NULL  0  zero
0  2  two
2  3  three

query T multiline
EXPLAIN RAW PLAN FOR
SELECT DISTINCT ON(5 + lag(a) OVER (ORDER BY a)) lead(a) OVER (ORDER BY b) AS o
FROM foo
ORDER BY 5 + lag(a) OVER (ORDER BY a), o;
----
Finish order_by=[#4 asc nulls_last, #3 asc nulls_last] output=[#3]
  TopK group_by=[#4] order_by=[#3 asc nulls_last] limit=1
    Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), lead(row(#0{a}, 1, null)) over (order by [#1{b} asc nulls_last]), (5 + #2{?column?}))
      Get materialize.public.foo

Target cluster: quickstart

EOF

# Nested window function call in the argument of a window function call (Postgres doesn't allow this, but we do)

query ITI nosort
SELECT a, b, lag(lag(a) OVER (ORDER BY a)) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  NULL
1  one  NULL
2  two  0
3  three  1

query ITI nosort
SELECT a, b, lag(lag(a) OVER (PARTITION BY length(b) ORDER BY a)) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  NULL
1  one  NULL
2  two  NULL
3  three  1

# The inner window function call also appears outside

query ITII nosort
SELECT a, b, lag(lag(a) OVER (ORDER BY a)) OVER (ORDER BY a), lag(a) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  NULL  NULL
1  one  NULL  0
2  two  0  1
3  three  1  2

query ITII nosort
SELECT a, b, lag(a) OVER (ORDER BY a), lag(lag(a) OVER (ORDER BY a)) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  NULL  NULL
1  one  0  NULL
2  two  1  0
3  three  2  1

# Nested window function call in the PARTITION BY of a window function call (Postgres doesn't allow this, but we do)

query ITII nosort
SELECT a, b, lag(a) OVER (PARTITION BY lag(length(b)) OVER (ORDER BY a) ORDER BY a), lag(length(b)) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  NULL  NULL
1  one  NULL  4
2  two  NULL  3
3  three  2  3

query ITI nosort
SELECT a, b, lag(a) OVER (PARTITION BY lag(length(b)) OVER (ORDER BY a) ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  NULL
1  one  NULL
2  two  NULL
3  three  2

query ITII nosort
SELECT a, b, row_number() OVER (PARTITION BY lag(length(b)) OVER (ORDER BY a) ORDER BY a), lag(length(b)) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  1  NULL
1  one  1  4
2  two  1  3
3  three  2  3

# Nested window function calls in both the argument and the PARTITION BY of a window function call (Postgres doesn't
# allow this, but we do)

query ITI nosort
SELECT a, b, lead(lag(a) OVER (PARTITION BY lag(length(b)) OVER (ORDER BY a) ORDER BY a), 1, -5) OVER (ORDER BY a)
FROM foo
ORDER BY a;
----
0  zero  NULL
1  one  NULL
2  two  2
3  three  -5

# Nested window function calls in the ORDER BY of a window function call (Postgres doesn't allow this, but we do)

query ITII nosort
SELECT a, b, lag(a) OVER (ORDER BY lag(a) OVER (ORDER BY a)), lag(a) OVER (ORDER BY a)
FROM foo
ORDER BY lag(a) OVER (ORDER BY a);
----
1  one  NULL  0
2  two  1  1
3  three  2  2
0  zero  3  NULL

query ITI
SELECT a, b, lag(a) OVER (ORDER BY lag(a) OVER (ORDER BY a))
FROM foo
ORDER BY a;
----
0  zero  3
1  one  NULL
2  two  1
3  three  2

# Window function inside CASE WHEN
# Regression test for https://github.com/MaterializeInc/database-issues/issues/6250

statement ok
CREATE TABLE bools(cond bool, x int);

statement ok
INSERT INTO bools VALUES (true, 0), (false, 1), (false, 2);

# The window function call should be lifted outside of the If
query T multiline
EXPLAIN RAW PLAN FOR
SELECT
  cond,
  x,
  CASE
    WHEN cond THEN
      first_value(x) OVER (ORDER BY x DESC)
    ELSE
      42
    END
FROM bools;
----
Project (#0, #1, #3)
  Map (first_value(#1{x}) over (order by [#1{x} desc nulls_first]), case when #0{cond} then #2{?column?} else 42 end)
    Get materialize.public.bools

Target cluster: quickstart

EOF

query TII
SELECT
  cond,
  x,
  CASE
    WHEN cond THEN
      first_value(x) OVER (ORDER BY x DESC)
    ELSE
      42
    END
FROM bools
ORDER BY x;
----
true  0  2
false  1  42
false  2  42

statement ok
CREATE TABLE tcm(
  chat_id int,
  chat_message_id int,
  message_tstamp date,
  xxx_id int,
  first_xxx_id int,
  user_id int,
  boolean_flag bool
);

statement ok
INSERT INTO tcm VALUES
  (
    5814889,
    78687406,
    '2023-08-04'::DATE,
    11111111,
    null,
    22222221,
    true
  ),
  (
    5814889,
    78707836,
    '2023-08-05'::DATE,
    11111112,
    923095,
    22222222,
    false
  ),
  (
    5814889,
    78708445,
    '2023-08-06'::DATE,
    11111113,
    743482,
    22222223,
    false
  ),
  (
    581488900,
    78687406,
    '2023-08-04'::DATE,
    11111111,
    null,
    22222221,
    true
  ),
  (
    581488900,
    78707836,
    '2023-08-05'::DATE,
    11111112,
    923095,
    22222222,
    false
  ),
  (
    581488900,
    78708445,
    '2023-08-06'::DATE,
    11111113,
    743482,
    22222223,
    true
  );

query IITIIIIT nosort
SELECT
  tcm.chat_id AS chat_id,
  tcm.chat_message_id AS chat_message_id,
  tcm.message_tstamp AS message_tstamp,
  tcm.first_xxx_id AS first_xxx_id,
  lead(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp) AS _lead,
  lag(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp) AS _lag,
  CASE
    WHEN tcm.boolean_flag THEN
      coalesce(
          lead(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp),
          lag(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp),
          tcm.xxx_id
      )
    ELSE tcm.user_id end AS xxx_id,
  tcm.boolean_flag AS boolean_flag
FROM tcm
ORDER BY chat_id, message_tstamp;
----
5814889  78687406  2023-08-04  NULL  923095  NULL  923095  true
5814889  78707836  2023-08-05  923095  743482  NULL  22222222  false
5814889  78708445  2023-08-06  743482  NULL  923095  22222223  false
581488900  78687406  2023-08-04  NULL  923095  NULL  923095  true
581488900  78707836  2023-08-05  923095  743482  NULL  22222222  false
581488900  78708445  2023-08-06  743482  NULL  923095  923095  true

query IITIIT nosort
SELECT
  tcm.chat_id AS chat_id,
  tcm.chat_message_id AS chat_message_id,
  tcm.message_tstamp AS message_tstamp,
  tcm.first_xxx_id AS first_xxx_id,
  CASE
    WHEN tcm.boolean_flag THEN
      coalesce(
          lead(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp),
          lag(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp),
          tcm.xxx_id
      )
    ELSE tcm.user_id end AS xxx_id,
  tcm.boolean_flag AS boolean_flag
FROM tcm
ORDER BY chat_id, message_tstamp;
----
5814889  78687406  2023-08-04  NULL  923095  true
5814889  78707836  2023-08-05  923095  22222222  false
5814889  78708445  2023-08-06  743482  22222223  false
581488900  78687406  2023-08-04  NULL  923095  true
581488900  78707836  2023-08-05  923095  22222222  false
581488900  78708445  2023-08-06  743482  923095  true

# Window func with (non-windowed) aggregations

query error db error: ERROR: window functions are not allowed in aggregate function \(function pg_catalog\.lag\)
EXPLAIN RAW PLAN FOR
SELECT sum(lag(a) OVER ())
FROM foo;

query III nosort
SELECT length(b), sum(a), lag(sum(a)) OVER (ORDER BY sum(a))
FROM foo
GROUP BY length(b)
ORDER BY sum(a), length(b);
----
4  0  NULL
3  3  0
5  3  3

query III nosort
SELECT length(b), sum(a), lag(sum(a)) OVER (ORDER BY length(b))
FROM foo
GROUP BY length(b)
ORDER BY length(b);
----
3  3  NULL
4  0  3
5  3  0

query III
SELECT * FROM
((SELECT length(b), sum(a), lag(sum(a)) OVER (ORDER BY sum(a))
 FROM foo
 GROUP BY length(b))
UNION
(SELECT length(b), sum(a), lag(sum(a)) OVER (ORDER BY length(b))
 FROM foo
 GROUP BY length(b))) r(x, y, z)
ORDER BY x, y, z;
----
3  3  0
3  3  NULL
4  0  3
4  0  NULL
5  3  0
5  3  3

query IIII nosort
SELECT *, row_number() OVER (PARTITION BY len ORDER BY sum DESC, lag NULLS FIRST) as row_num FROM (
    (SELECT length(b) AS len, sum(a) AS sum, lag(sum(a)) OVER (ORDER BY sum(a)) AS lag
     FROM foo
     GROUP BY length(b))
    UNION
    (SELECT length(b), -1 + sum(a) + row_number() OVER (ORDER BY length(b)), lag(sum(a)) OVER (ORDER BY length(b))
     FROM foo
     GROUP BY length(b))
) AS sq
ORDER BY len, row_num;
----
3  3  NULL  1
3  3  0  2
4  1  3  1
4  0  NULL  2
5  5  0  1
5  3  3  2

## Subqueries

# Correlated scalar subquery in the 2nd argument of lag
query ITI nosort
SELECT *,
    lag(
        outer_a,
        (SELECT count(*) FROM foo WHERE length(b) = length(outer_b))::integer
    ) OVER (ORDER BY outer_a)
FROM (
    SELECT a AS outer_a, b AS outer_b
    FROM foo
) as fsq
ORDER BY outer_a;
----
0  zero  NULL
1  one  NULL
2  two  0
3  three  2

# Add a correlated IN subquery at the 3rd argument of lag
query ITI nosort
SELECT *,
    lag(
        outer_a,
        (SELECT count(*) FROM foo WHERE length(b) = length(outer_b))::integer,
        CASE WHEN outer_a - 1 IN (SELECT a FrOM foo) THEN 100 ELSE 500 END
    ) OVER (ORDER BY outer_a)
FROM (
    SELECT a AS outer_a, b AS outer_b
    FROM foo
) as fsq
ORDER BY outer_a;
----
0  zero  500
1  one  100
2  two  0
3  three  2

# Correlated subquery in the PARTITION BY.
# The same subquery is also present in the SELECT to make the output more human-friendly.
query IITI nosort
SELECT (SELECT count(*) FROM foo WHERE length(b) = length(outer_b)) as part, *,
    lag(outer_a) OVER (
        PARTITION BY (SELECT count(*) FROM foo WHERE length(b) = length(outer_b))
        ORDER BY outer_a
    )
FROM (
    SELECT a AS outer_a, b AS outer_b
    FROM foo
) as fsq
ORDER BY part, outer_a;
----
1  0  zero  NULL
1  3  three  0
2  1  one  NULL
2  2  two  1

# Same query as the previous, but the subquery is not present in the SELECT
query ITI nosort
SELECT *,
    lag(outer_a) OVER (
        PARTITION BY (SELECT count(*) FROM foo WHERE length(b) = length(outer_b))
        ORDER BY outer_a
    )
FROM (
    SELECT a AS outer_a, b AS outer_b
    FROM foo
) as fsq
ORDER BY outer_a;
----
0  zero  NULL
1  one  NULL
2  two  1
3  three  0

# Correlated subquery in the ORDER BY of a window function.
# The same subquery is also present in the SELECT to make the output more human-friendly.
query IITI nosort
SELECT (SELECT count(*) FROM foo WHERE length(outer_b) > a + 2) as ord, *,
    lag(outer_a) OVER (
        ORDER BY (SELECT count(*) FROM foo WHERE length(outer_b) > a + 2), outer_a
    )
FROM (
    SELECT a AS outer_a, b AS outer_b
    FROM foo
) as fsq
ORDER BY ord, outer_a;
----
1  1  one  NULL
1  2  two  1
2  0  zero  2
3  3  three  0

# Same query as the previous, but the subquery is not present in the SELECT
query ITI nosort
SELECT *,
    lag(outer_a) OVER (
        ORDER BY (SELECT count(*) FROM foo WHERE length(outer_b) > a + 2), outer_a
    )
FROM (
    SELECT a AS outer_a, b AS outer_b
    FROM foo
) as fsq
ORDER BY outer_a;
----
0  zero  2
1  one  NULL
2  two  1
3  three  0

# Window func in a correlated subquery, but the correlating column reference is outside the window function
query ITI nosort
SELECT *,
    (
        SELECT sum(lead_a)
        FROM (
            SELECT lead(a) OVER (ORDER BY a) AS lead_a
            FROM foo
            WHERE a <= outer_a AND a < 3
        ) as fsq2
    )
FROM (
    SELECT a AS outer_a, b AS outer_b
    FROM foo
) as fsq
ORDER BY outer_a;
----
0  zero  NULL
1  one  1
2  two  3
3  three  3

# Similar to the previous one, but more window functions and aggregations
query ITI nosort
SELECT *,
    (
        SELECT sum(2 * w) + max(w)
        FROM (
            SELECT lead(a) OVER (ORDER BY a) + row_number() OVER (ORDER BY a) AS w
            FROM foo
            WHERE a <= outer_a AND a < 3
        ) as fsq2
    )
FROM (
    SELECT a AS outer_a, b AS outer_b
    FROM foo
) as fsq
ORDER BY outer_a;
----
0  zero  NULL
1  one  6
2  two  16
3  three  16

# Window func in a correlated subquery, and the window function argument refers to the outer query
query ITI nosort
SELECT *,
    (
        SELECT sum(lead_a)
        FROM (
            SELECT lead(outer_a + a) OVER (ORDER BY a) AS lead_a
            FROM foo
        ) as fsq2
    )
FROM (
    SELECT a AS outer_a, b AS outer_b
    FROM foo
) as fsq
ORDER BY outer_a;
----
0  zero  6
1  one  9
2  two  12
3  three  15

# Window func in a correlated subquery, and the window function's PARTITION BY refers to the outer query
query ITI
SELECT *,
    (
        SELECT sum(lead_a)
        FROM (
            SELECT 3 + lead(a) OVER (PARTITION BY 4 * a / (outer_a + length(outer_b)) ORDER BY a) AS lead_a
            FROM foo
        ) as fsq2
    )
FROM (
    (SELECT a AS outer_a, b AS outer_b
     FROM foo)
    UNION
    (SELECT a + 2 AS outer_a, b AS outer_b
     FROM foo)
    UNION
    (SELECT a + 5 AS outer_a, b AS outer_b
     FROM foo)
) as fsq
ORDER BY outer_a, outer_b;
----
0  zero  NULL
1  one  NULL
2  two  4
2  zero  4
3  one  4
3  three  10
4  two  10
5  three  9
5  zero  9
6  one  9
7  two  9
8  three  15

# Let's check the HIR plan for the above query to see if the window function is lifted out from behind the `3 +`
query T multiline
EXPLAIN RAW PLAN FOR
SELECT *,
    (
        SELECT sum(lead_a)
        FROM (
            SELECT 3 + lead(a) OVER (PARTITION BY 4 * a / (outer_a + length(outer_b)) ORDER BY a) AS lead_a
            FROM foo
        ) as fsq2
    )
FROM (
    (SELECT a AS outer_a, b AS outer_b
     FROM foo)
    UNION
    (SELECT a + 2 AS outer_a, b AS outer_b
     FROM foo)
    UNION
    (SELECT a + 5 AS outer_a, b AS outer_b
     FROM foo)
) as fsq
ORDER BY outer_a, outer_b;
----
Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#2]
  With
    cte [l1 as subquery-1] =
      Reduce aggregates=[sum(#0{lead_a})]
        Project (#3)
          Map (lead(row(#0{a}, 1, null)) over (partition by [((4 * #0{a}) / (#^0{outer_a} + char_length(#^1{outer_b})))] order by [#0{a} asc nulls_last]), (3 + #2{?column?}))
            Get materialize.public.foo
  Return
    Map (select(Get l1))
      Distinct
        Union
          Distinct
            Union
              Get materialize.public.foo
              Project (#2, #1)
                Map ((#0{a} + 2))
                  Get materialize.public.foo
          Project (#2, #1)
            Map ((#0{a} + 5))
              Get materialize.public.foo

Target cluster: quickstart

EOF

##################################
## Window aggregations
##################################

statement ok
CREATE TABLE t7(x INT, y INT);

# Plans
query T multiline
EXPLAIN DECORRELATED PLAN FOR
SELECT
  sum(y-3) OVER (PARTITION BY 2*x, 3*y ORDER BY x+1, y+2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t7
ORDER BY x;
----
Finish order_by=[#0 asc nulls_last] output=[#2]
  Project (#0, #1, #3)
    Map (#2)
      Project (#4..=#6)
        Map (record_get[0](record_get[1](#3)), record_get[1](record_get[1](#3)), record_get[0](#3))
          FlatMap unnest_list(#2)
            Reduce group_by=[#2, #3] aggregates=[window_agg[sum order_by=[#0 asc nulls_last, #1 asc nulls_last] rows between unbounded preceding and current row](row(row(row(#0, #1), (#1{y} - 3)), (#0{x} + 1), (#1{y} + 2)))]
              Map ((2 * #0{x}), (3 * #1{y}))
                CrossJoin
                  Constant
                    - ()
                  Get materialize.public.t7

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT
  sum(y-3) OVER (PARTITION BY 2*x, 3*y ORDER BY x+1, y+2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t7
ORDER BY x;
----
Explained Query:
  Finish order_by=[#0 asc nulls_last] output=[#2]
    Project (#3..=#5)
      Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[0](#1))
        FlatMap unnest_list(#0{window_agg})
          Project (#2{window_agg})
            Reduce group_by=[(2 * #0{x}), (3 * #1{y})] aggregates=[window_agg[sum order_by=[#0{x} asc nulls_last, #1{y} asc nulls_last] rows between unbounded preceding and current row](row(row(row(#0{x}, #1{y}), (#1{y} - 3)), (#0{x} + 1), (#1{y} + 2)))]
              ReadStorage materialize.public.t7

Source materialize.public.t7

Target cluster: quickstart

EOF

# Empty aggregations

query IIIIIT
SELECT
  x,
  y,
  sum(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  max(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  min(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  array_agg(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t7
ORDER BY x;
----

query IIIIIT
SELECT
  x,
  y,
  sum(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
  max(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
  min(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
  jsonb_agg(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM t7
ORDER BY x;
----

query IIIIIT
SELECT
  x,
  y,
  sum(y) OVER (ORDER BY x ROWS BETWEEN 2 FOLLOWING AND 5 FOLLOWING),
  max(y) OVER (ORDER BY x ROWS BETWEEN 2 FOLLOWING AND 5 FOLLOWING),
  min(y) OVER (ORDER BY x ROWS BETWEEN 2 FOLLOWING AND 5 FOLLOWING),
  list_agg(y) OVER (ORDER BY x ROWS BETWEEN 2 FOLLOWING AND 5 FOLLOWING)[1]
FROM t7
ORDER BY x;
----

statement ok
INSERT INTO t7 VALUES (1,2), (3,null), (5,6), (7,8), (9, null), (11, null), (13, 14), (15, 16), (17, 18), (10, -40), (10, -50);

# Test various nestings

# Postgres also doesn't support this.
query error db error: ERROR: window functions are not allowed in aggregate function \(function pg_catalog\.sum\)
SELECT sum(sum(x) OVER ()) FROM t7;

# Other way around: grouped aggregate inside a window aggregate.
# What this query is asking us to do is to
# 1. First, perform the inner sum (with its GROUP BY), and then
# 2. the outer sum (with its OVER clause).
# (Postgres handles this the same.)
query I
SELECT sum(sum(x)) OVER (ORDER BY y) FROM t7 GROUP BY y;
----
10
20
21
26
33
46
61
78
101

# Window aggregation inside a window aggregation. We support this, and queries like this might actually make sense in
# some cases. (I think we already had a customer with some other window functions nested inside each other.)
# (Postgres doesn't support this.)
query I
SELECT sum(sum(x) OVER ()) OVER () FROM t7;
----
1111
1111
1111
1111
1111
1111
1111
1111
1111
1111
1111

query III
SELECT
  x,
  sum(x) OVER (ORDER BY x),
  sum(sum(x) OVER (ORDER BY x)) OVER (ORDER BY x)
FROM (SELECT DISTINCT ON(x) * FROM t7);
----
1  1  1
3  4  5
5  9  14
7  16  30
9  25  55
10  35  90
11  46  136
13  59  195
15  74  269
17  91  360

# Lots of different aggregation functions. This covers
# - basic, hierarchical, accumulable aggregations
# - count's null handling
# - count(*)
# - FILTER clause
# - Aggregation functions that are transformed away by `transform_ast.rs`: bool_and, bool_or.
# These are all supported by Postgres, and the output should be identical.
query IIIIIITIIIITT
SELECT
  x-y,
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  max(x*y+1) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  min(x*y+1) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  array_agg(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  count(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  count(CASE WHEN x%3 != 0 THEN x ELSE null END) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  count(*) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  sum(x) FILTER (WHERE x%3 != 0) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  bool_and(x%3 != 0) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  bool_or(x%3 = 0) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t7
ORDER BY x-y, x;
----
-1  1  2  116  307  3  {2,10,14,26,30,34}  6  5  6  43  false  true
-1  5  6  114  307  31  {10,14,26,30,34}  5  4  5  42  false  true
-1  7  8  104  307  57  {14,26,30,34}  4  3  4  37  false  true
-1  13  14  90  307  183  {26,30,34}  3  2  3  30  false  true
-1  15  16  64  307  241  {30,34}  2  1  2  17  false  true
-1  17  18  34  307  307  {34}  1  1  1  17  true  false
50  10  -40  20  -399  -399  {20}  1  1  1  10  true  false
60  10  -50  20  -499  -499  {20}  1  1  1  10  true  false
NULL  3  NULL  46  NULL  NULL  {6,18,22}  3  1  3  11  false  true
NULL  9  NULL  40  NULL  NULL  {18,22}  2  1  2  11  false  true
NULL  11  NULL  22  NULL  NULL  {22}  1  1  1  11  true  false

# Also supported by Postgres, but output text formatting is a bit different, so putting this in a separate test.
# avg, variance, var_pop, stddev, stddev_pop are transformed away by `transform_ast.rs`.
query IIITRRRRR
SELECT
  x-y,
  x,
  y,
  jsonb_agg(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  avg(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  variance(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  var_pop(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  stddev(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  stddev_pop(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t7
ORDER BY x-y, x;
----
-1  1  2  [2,10,14,26,30,34]  20.3333333333333333333333333333333333333  157.866666666666666666666666666666666666  131.555555555555555555555555555555555555  12.5645002553490628940078294328743895394  11.469767022723502362305964713399618176
-1  5  6  [10,14,26,30,34]  23.8  107.2  85.76  10.3537432844358276565421773235938072887  9.26066952223218037884464808956388302968
-1  7  8  [14,26,30,34]  27  74.6666666666666666666666666666666666667  56  8.64098759787714697462128991478399554361  7.48331477354788277116749746463309860351
-1  13  14  [26,30,34]  31  16  10.6666666666666666666666666666666666667  4  3.26598632371090413092971209960785518929
-1  15  16  [30,34]  33  8  4  2.82842712474619009760337744841939615714  2
-1  17  18  [34]  35  NULL  0  NULL  0
50  10  -40  [20]  -30  NULL  0  NULL  0
60  10  -50  [20]  -40  NULL  0  NULL  0
NULL  3  NULL  [6,18,22]  NULL  NULL  NULL  NULL  NULL
NULL  9  NULL  [18,22]  NULL  NULL  NULL  NULL  NULL
NULL  11  NULL  [22]  NULL  NULL  NULL  NULL  NULL

# These are not supported by Postgres.
# - ORDER BY in aggr
# - list indexing after the OVER clause
query IIITTI
SELECT
  x-y,
  x,
  y,
  array_agg(x ORDER BY CASE WHEN y IS NOT NULL THEN y ELSE x%5 END DESC) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  array_agg(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  list_agg(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)[2]
FROM t7
ORDER BY x-y, x;
----
-1  1  2  {17,15,13,7,5,1}  {2,10,14,26,30,34}  10
-1  5  6  {17,15,13,7,5}  {10,14,26,30,34}  14
-1  7  8  {17,15,13,7}  {14,26,30,34}  26
-1  13  14  {17,15,13}  {26,30,34}  30
-1  15  16  {17,15}  {30,34}  34
-1  17  18  {17}  {34}  NULL
50  10  -40  {10}  {20}  NULL
60  10  -50  {10}  {20}  NULL
NULL  3  NULL  {9,3,11}  {6,18,22}  18
NULL  9  NULL  {9,11}  {18,22}  22
NULL  11  NULL  {11}  {22}  NULL

# https://github.com/MaterializeInc/database-issues/issues/6626
query error db error: ERROR: DISTINCT in window aggregates not yet supported
SELECT
  x-y,
  x,
  y,
  array_agg(x/10) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  count(DISTINCT x/10) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t7
ORDER BY x-y, x;

# No ORDER BY in OVER clause.
query IIIIITTTT
SELECT
  x,
  y,
  sum(y) OVER (),
  max(y) OVER (),
  min(y) OVER (),
  array_agg(y ORDER BY y) OVER (),
  array_agg(y ORDER BY y NULLS LAST) OVER (),
  array_agg(y ORDER BY y NULLS FIRST) OVER (),
  bool_and(x%3 != 0) OVER ()
FROM t7
ORDER BY x;
----
1  2  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
3  NULL  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
5  6  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
7  8  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
9  NULL  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
10  -50  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
10  -40  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
11  NULL  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
13  14  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
15  16  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false
17  18  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}  {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18}  false

query IIIIIIT
SELECT
  x-y+x/10,
  x,
  y,
  sum(y) OVER (PARTITION BY x-y+x/10),
  max(y) OVER (PARTITION BY x-y+x/10),
  min(y) OVER (PARTITION BY x-y+x/10),
  array_agg(y) OVER (PARTITION BY x-y+x/10)
FROM t7
ORDER BY x-y+x/10;
----
-1  1  2  16  8  2  {2,6,8}
-1  5  6  16  8  2  {2,6,8}
-1  7  8  16  8  2  {2,6,8}
0  13  14  48  18  14  {14,16,18}
0  15  16  48  18  14  {14,16,18}
0  17  18  48  18  14  {14,16,18}
51  10  -40  -40  -40  -40  {-40}
61  10  -50  -50  -50  -50  {-50}
NULL  3  NULL  NULL  NULL  NULL  {NULL,NULL,NULL}
NULL  9  NULL  NULL  NULL  NULL  {NULL,NULL,NULL}
NULL  11  NULL  NULL  NULL  NULL  {NULL,NULL,NULL}

# Test the situation when we don't have an ORDER BY, but we have a frame that doesn't include the current row.
# This verifies that the `order_by.is_empty()` if condition also includes `window_frame.includes_current_row()`.
query IIIIITR
SELECT
  x-y+x/10,
  x,
  y,
  sum(y) OVER (PARTITION BY x-y+x/10 ROWS BETWEEN 200 PRECEDING AND 100 PRECEDING),
  count(y) OVER (PARTITION BY x-y+x/10 ROWS BETWEEN 200 PRECEDING AND 100 PRECEDING),
  array_agg(y) OVER (PARTITION BY x-y+x/10 ROWS BETWEEN 200 PRECEDING AND 100 PRECEDING),
  avg(y) OVER (PARTITION BY x-y+x/10 ROWS BETWEEN 200 PRECEDING AND 100 PRECEDING)
FROM t7
ORDER BY x, y;
----
-1  1  2  NULL  0  NULL  NULL
NULL  3  NULL  NULL  0  NULL  NULL
-1  5  6  NULL  0  NULL  NULL
-1  7  8  NULL  0  NULL  NULL
NULL  9  NULL  NULL  0  NULL  NULL
61  10  -50  NULL  0  NULL  NULL
51  10  -40  NULL  0  NULL  NULL
NULL  11  NULL  NULL  0  NULL  NULL
0  13  14  NULL  0  NULL  NULL
0  15  16  NULL  0  NULL  NULL
0  17  18  NULL  0  NULL  NULL

# ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

query IIIIIT
SELECT
  x,
  y,
  sum(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  max(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  min(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  array_agg(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t7
ORDER BY x;
----
1  2  2  2  2  {2}
3  NULL  2  2  2  {2,NULL}
5  6  8  6  2  {2,6,NULL}
7  8  16  8  2  {2,6,8,NULL}
9  NULL  16  8  2  {2,6,8,NULL,NULL}
10  -50  -34  8  -50  {-50,2,6,8,NULL,NULL}
10  -40  -74  8  -50  {-50,-40,2,6,8,NULL,NULL}
11  NULL  -74  8  -50  {-50,-40,2,6,8,NULL,NULL,NULL}
13  14  -60  14  -50  {-50,-40,2,6,8,14,NULL,NULL,NULL}
15  16  -44  16  -50  {-50,-40,2,6,8,14,16,NULL,NULL,NULL}
17  18  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}

statement ok
CREATE TABLE t8(o1 INT, o2 INT, v INT);

statement ok
INSERT INTO t8 VALUES (1,1,-2), (1,1,-3), (1,2,-4), (1,3,-7), (1,3,-8), (2,1,-10), (2,2,-1000), (2,2,-1000), (2,2,-1000), (3,0,-100);

# Default frame, i.e. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

query IIIIIIT
SELECT
  o1,
  o2,
  v,
  sum(v) OVER (ORDER BY o1, o2),
  max(v) OVER (ORDER BY o1, o2),
  min(v) OVER (ORDER BY o1, o2),
  array_agg(v) OVER (ORDER BY o1, o2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t8
ORDER BY o1, o2;
----
1  1  -3  -5  -2  -3  {-3,-2}
1  1  -2  -5  -2  -3  {-3,-2}
1  2  -4  -9  -2  -4  {-4,-3,-2}
1  3  -8  -24  -2  -8  {-8,-7,-4,-3,-2}
1  3  -7  -24  -2  -8  {-8,-7,-4,-3,-2}
2  1  -10  -34  -2  -10  {-10,-8,-7,-4,-3,-2}
2  2  -1000  -3034  -2  -1000  {-1000,-1000,-1000,-10,-8,-7,-4,-3,-2}
2  2  -1000  -3034  -2  -1000  {-1000,-1000,-1000,-10,-8,-7,-4,-3,-2}
2  2  -1000  -3034  -2  -1000  {-1000,-1000,-1000,-10,-8,-7,-4,-3,-2}
3  0  -100  -3134  -2  -1000  {-1000,-1000,-1000,-100,-10,-8,-7,-4,-3,-2}

query IIIIIIT
SELECT
  o1,
  o2,
  v,
  sum(v) OVER (PARTITION BY o1 ORDER BY o2),
  max(v) OVER (PARTITION BY o1 ORDER BY o2),
  min(v) OVER (PARTITION BY o1 ORDER BY o2),
  array_agg(v) OVER (PARTITION BY o1 ORDER BY o2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t8
ORDER BY o1, o2;
----
1  1  -3  -5  -2  -3  {-3,-2}
1  1  -2  -5  -2  -3  {-3,-2}
1  2  -4  -9  -2  -4  {-4,-3,-2}
1  3  -8  -24  -2  -8  {-8,-7,-4,-3,-2}
1  3  -7  -24  -2  -8  {-8,-7,-4,-3,-2}
2  1  -10  -10  -10  -10  {-10}
2  2  -1000  -3010  -10  -1000  {-1000,-1000,-1000,-10}
2  2  -1000  -3010  -10  -1000  {-1000,-1000,-1000,-10}
2  2  -1000  -3010  -10  -1000  {-1000,-1000,-1000,-10}
3  0  -100  -100  -100  -100  {-100}

# ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
# Note that x=10 occurs twice. This means that the ordering of these rows is undefined, so it can change between
# Materialize versions.
query IIIIIT
SELECT
  x,
  y,
  sum(y) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  max(y) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  min(y) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  array_agg(y) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t7
ORDER BY x;
----
1  2  -26  18  -50  {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL}
3  NULL  -28  18  -50  {-50,-40,6,8,14,16,18,NULL,NULL,NULL}
5  6  -28  18  -50  {-50,-40,6,8,14,16,18,NULL,NULL}
7  8  -34  18  -50  {-50,-40,8,14,16,18,NULL,NULL}
9  NULL  -42  18  -50  {-50,-40,14,16,18,NULL,NULL}
10  -40  8  18  -40  {-40,14,16,18,NULL}
10  -50  -42  18  -50  {-50,-40,14,16,18,NULL}
11  NULL  48  18  14  {14,16,18,NULL}
13  14  48  18  14  {14,16,18}
15  16  34  18  16  {16,18}
17  18  18  18  18  {18}

# RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
# TODO: Actually, we already have the rendering code for this. So, we just need to either hack planning a bit to add an
# exception for window aggregations, or add rendering support also for other window functions.
query error RANGE in non-default window frames not yet supported
SELECT
  x,
  y,
  sum(y) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  max(y) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  min(y) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  array_agg(y) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t7
ORDER BY x;

# PARTITION BY
# ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
# Here, x=10 occurring twice does NOT mean that the result for these rows would be undefined.
query IIIIIIT
SELECT
  x-y,
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  max(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  min(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  array_agg(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t7
ORDER BY x-y, x;
----
-1  1  2  116  34  2  {2,10,14,26,30,34}
-1  5  6  114  34  10  {10,14,26,30,34}
-1  7  8  104  34  14  {14,26,30,34}
-1  13  14  90  34  26  {26,30,34}
-1  15  16  64  34  30  {30,34}
-1  17  18  34  34  34  {34}
50  10  -40  20  20  20  {20}
60  10  -50  20  20  20  {20}
NULL  3  NULL  46  22  6  {6,18,22}
NULL  9  NULL  40  22  18  {18,22}
NULL  11  NULL  22  22  22  {22}

query IIIIIT
SELECT
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  max(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  min(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  array_agg(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t7
ORDER BY x-y, x;
----
1  2  116  34  2  {2,10,14,26,30,34}
5  6  114  34  10  {10,14,26,30,34}
7  8  104  34  14  {14,26,30,34}
13  14  90  34  26  {26,30,34}
15  16  64  34  30  {30,34}
17  18  34  34  34  {34}
10  -40  20  20  20  {20}
10  -50  20  20  20  {20}
3  NULL  46  22  6  {6,18,22}
9  NULL  40  22  18  {18,22}
11  NULL  22  22  22  {22}

# PARTITION BY
# RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
query error RANGE in non-default window frames not yet supported
SELECT
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-y ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  max(x+x) OVER (PARTITION BY x-y ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  min(x+x) OVER (PARTITION BY x-y ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  array_agg(x+x) OVER (PARTITION BY x-y ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM t7
ORDER BY x-y, x;

# ROWS BETWEEN offset PRECEDING AND offset FOLLOWING
query IIIIIT
SELECT
  x,
  y,
  sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING),
  max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING),
  min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING),
  array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
FROM t7
ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END;
----
10  -50  20  10  10  {10,10}
10  -40  21  10  1  {1,10,10}
1  2  24  10  1  {1,3,10,10}
3  NULL  23  10  1  {1,3,9,10}
9  NULL  24  11  1  {1,3,9,11}
11  NULL  28  11  3  {3,5,9,11}
5  6  32  11  5  {5,7,9,11}
7  8  36  13  5  {5,7,11,13}
13  14  40  15  5  {5,7,13,15}
15  16  52  17  7  {7,13,15,17}
17  18  45  17  13  {13,15,17}

query IIIIIT
SELECT
  x,
  y,
  sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING),
  max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING),
  min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING),
  array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING)
FROM t7
ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END;
----
10  -50  20  10  10  {10,10}
10  -40  11  10  1  {1,10}
1  2  4  3  1  {1,3}
3  NULL  12  9  3  {3,9}
9  NULL  20  11  9  {9,11}
11  NULL  16  11  5  {5,11}
5  6  12  7  5  {5,7}
7  8  20  13  7  {7,13}
13  14  28  15  13  {13,15}
15  16  32  17  15  {15,17}
17  18  17  17  17  {17}

query IIIIIT
SELECT
  x,
  y,
  sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING),
  max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING),
  min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING),
  array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING)
FROM t7
ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END;
----
10  -50  10  10  10  {10}
10  -40  10  10  10  {10}
1  2  1  1  1  {1}
3  NULL  3  3  3  {3}
9  NULL  9  9  9  {9}
11  NULL  11  11  11  {11}
5  6  5  5  5  {5}
7  8  7  7  7  {7}
13  14  13  13  13  {13}
15  16  15  15  15  {15}
17  18  17  17  17  {17}

# mixed UNBOUNDED - OFFSET frames
query error db error: ERROR: mixed unbounded \- offset frames not yet supported
SELECT
  sum(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING)
FROM t7;

query error db error: ERROR: mixed unbounded \- offset frames not yet supported
SELECT
  sum(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
FROM t7;

query error db error: ERROR: mixed unbounded \- offset frames not yet supported
SELECT
  sum(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING)
FROM t7;

query error db error: ERROR: mixed unbounded \- offset frames not yet supported
SELECT
  sum(y) OVER (ORDER BY x ROWS BETWEEN 3 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM t7;

# And a parse error as a bonus
query error Expected PRECEDING or FOLLOWING, found UNBOUNDED
SELECT
  sum(y) OVER (ORDER BY x ROWS BETWEEN 3 FOLLOWING AND 2 UNBOUNDED FOLLOWING)
FROM t7;

# ROWS BETWEEN offset PRECEDING AND offset PRECEDING
query IIIIIT
SELECT
  x,
  y,
  sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING),
  max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING),
  min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING),
  array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
FROM t7
ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END;
----
10  -50  NULL  NULL  NULL  NULL
10  -40  10  10  10  {10}
1  2  20  10  10  {10,10}
3  NULL  21  10  1  {1,10,10}
9  NULL  14  10  1  {1,3,10}
11  NULL  13  9  1  {1,3,9}
5  6  23  11  3  {3,9,11}
7  8  25  11  5  {5,9,11}
13  14  23  11  5  {5,7,11}
15  16  25  13  5  {5,7,13}
17  18  35  15  7  {7,13,15}

# ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING
query IIIIIT
SELECT
  x,
  y,
  sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING),
  max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING),
  min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING),
  array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING)
FROM t7
ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END;
----
10  -50  13  9  1  {1,3,9}
10  -40  23  11  3  {3,9,11}
1  2  25  11  5  {5,9,11}
3  NULL  23  11  5  {5,7,11}
9  NULL  25  13  5  {5,7,13}
11  NULL  35  15  7  {7,13,15}
5  6  45  17  13  {13,15,17}
7  8  32  17  15  {15,17}
13  14  17  17  17  {17}
15  16  NULL  NULL  NULL  NULL
17  18  NULL  NULL  NULL  NULL

# Frame end is later than frame start. All frames are empty.
query IIIIIT
SELECT
  x,
  y,
  sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 30 FOLLOWING AND 1 FOLLOWING),
  max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 30 FOLLOWING AND 1 FOLLOWING),
  min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 30 FOLLOWING AND 1 FOLLOWING),
  array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 30 FOLLOWING AND 1 FOLLOWING)
FROM t7
ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END;
----
10  -50  NULL  NULL  NULL  NULL
10  -40  NULL  NULL  NULL  NULL
1  2  NULL  NULL  NULL  NULL
3  NULL  NULL  NULL  NULL  NULL
9  NULL  NULL  NULL  NULL  NULL
11  NULL  NULL  NULL  NULL  NULL
5  6  NULL  NULL  NULL  NULL
7  8  NULL  NULL  NULL  NULL
13  14  NULL  NULL  NULL  NULL
15  16  NULL  NULL  NULL  NULL
17  18  NULL  NULL  NULL  NULL

query IIIIIT
SELECT
  x,
  y,
  sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING),
  max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING),
  min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING),
  array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING)
FROM t7
ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END;
----
10  -50  NULL  NULL  NULL  NULL
10  -40  NULL  NULL  NULL  NULL
1  2  NULL  NULL  NULL  NULL
3  NULL  NULL  NULL  NULL  NULL
9  NULL  NULL  NULL  NULL  NULL
11  NULL  NULL  NULL  NULL  NULL
5  6  NULL  NULL  NULL  NULL
7  8  NULL  NULL  NULL  NULL
13  14  NULL  NULL  NULL  NULL
15  16  NULL  NULL  NULL  NULL
17  18  NULL  NULL  NULL  NULL

# ROWS BETWEEN offset PRECEDING AND offset FOLLOWING
query IIIIIIT
SELECT
  x-y+x/10,
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM t7
ORDER BY x-y+x/10, x;
----
-1  1  2  12  10  2  {2,10}
-1  5  6  26  14  2  {2,10,14}
-1  7  8  24  14  10  {10,14}
0  13  14  56  30  26  {26,30}
0  15  16  90  34  26  {26,30,34}
0  17  18  64  34  30  {30,34}
51  10  -40  20  20  20  {20}
61  10  -50  20  20  20  {20}
NULL  3  NULL  24  18  6  {6,18}
NULL  9  NULL  46  22  6  {6,18,22}
NULL  11  NULL  40  22  18  {18,22}

query IIIIIIT
SELECT
  x-y+x/10,
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1000000 PRECEDING AND 1000000 FOLLOWING),
  max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1000000 PRECEDING AND 1000000 FOLLOWING),
  min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1000000 PRECEDING AND 1000000 FOLLOWING),
  array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1000000 PRECEDING AND 1000000 FOLLOWING)
FROM t7
ORDER BY x-y+x/10, x;
----
-1  1  2  26  14  2  {2,10,14}
-1  5  6  26  14  2  {2,10,14}
-1  7  8  26  14  2  {2,10,14}
0  13  14  90  34  26  {26,30,34}
0  15  16  90  34  26  {26,30,34}
0  17  18  90  34  26  {26,30,34}
51  10  -40  20  20  20  {20}
61  10  -50  20  20  20  {20}
NULL  3  NULL  46  22  6  {6,18,22}
NULL  9  NULL  46  22  6  {6,18,22}
NULL  11  NULL  46  22  6  {6,18,22}

query IIIIIIT
SELECT
  x-row_number() OVER (ORDER BY x+y),
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  max(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  min(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  array_agg(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM t7
ORDER BY x-row_number() OVER (ORDER BY x+y), x;
----
-6  3  NULL  6  6  6  {6}
-2  1  2  2  2  2  {2}
-1  9  NULL  18  18  18  {18}
0  11  NULL  22  22  22  {22}
1  5  6  10  10  10  {10}
2  7  8  14  14  14  {14}
7  13  14  26  26  26  {26}
8  10  -40  50  30  20  {20,30}
8  15  16  50  30  20  {20,30}
9  10  -50  54  34  20  {20,34}
9  17  18  54  34  20  {20,34}

# Offset has to be a literal
query error Expected literal unsigned integer, found identifier "row_number"
SELECT
  x-row_number() OVER (ORDER BY x+y),
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN row_number() OVER (ORDER BY x+y) PRECEDING AND 0 FOLLOWING),
  max(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN row_number() OVER (ORDER BY x+y) PRECEDING AND 0 FOLLOWING),
  min(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN row_number() OVER (ORDER BY x+y) PRECEDING AND 0 FOLLOWING),
  array_agg(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN row_number() OVER (ORDER BY x+y) PRECEDING AND 0 FOLLOWING)
FROM t7
ORDER BY x-row_number() OVER (ORDER BY x+y), x;

# ROWS BETWEEN offset PRECEDING AND CURRENT ROW
query IIIIIIT
SELECT
  x-y+x/10,
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
  max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
  min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
  array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM t7
ORDER BY x-y+x/10, x;
----
-1  1  2  2  2  2  {2}
-1  5  6  12  10  2  {2,10}
-1  7  8  26  14  2  {2,10,14}
0  13  14  26  26  26  {26}
0  15  16  56  30  26  {26,30}
0  17  18  90  34  26  {26,30,34}
51  10  -40  20  20  20  {20}
61  10  -50  20  20  20  {20}
NULL  3  NULL  6  6  6  {6}
NULL  9  NULL  24  18  6  {6,18}
NULL  11  NULL  46  22  6  {6,18,22}

# ROWS BETWEEN CURRENT ROW AND offset FOLLOWING
query IIIIIIT
SELECT
  x-y+x/10,
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),
  max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),
  min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),
  array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM t7
ORDER BY x-y+x/10, x;
----
-1  1  2  12  10  2  {2,10}
-1  5  6  24  14  10  {10,14}
-1  7  8  14  14  14  {14}
0  13  14  56  30  26  {26,30}
0  15  16  64  34  30  {30,34}
0  17  18  34  34  34  {34}
51  10  -40  20  20  20  {20}
61  10  -50  20  20  20  {20}
NULL  3  NULL  24  18  6  {6,18}
NULL  9  NULL  40  22  18  {18,22}
NULL  11  NULL  22  22  22  {22}

# ROWS BETWEEN CURRENT ROW AND CURRENT ROW
query IIIIIIT
SELECT
  x-y+x/10,
  x,
  y,
  sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW),
  max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW),
  min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW),
  array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
FROM t7
ORDER BY x-y+x/10, x;
----
-1  1  2  2  2  2  {2}
-1  5  6  10  10  10  {10}
-1  7  8  14  14  14  {14}
0  13  14  26  26  26  {26}
0  15  16  30  30  30  {30}
0  17  18  34  34  34  {34}
51  10  -40  20  20  20  {20}
61  10  -50  20  20  20  {20}
NULL  3  NULL  6  6  6  {6}
NULL  9  NULL  18  18  18  {18}
NULL  11  NULL  22  22  22  {22}

## Tests for `AggregateExpr::on_unique`

# In the next several tests, the global aggregates make the input column to the window aggregate a key, so
# `ReduceElision` invokes `on_unique`.
#
# First, let's see that `ReduceElision` indeed kicks in, by observing that the Reduce of the window aggregation is not
# present in the plan. (There is 1 Reduce present from the global aggregation.)
#
# Note that in these tests that rely on the window function's PARTITION BY being a key, we unfortunately can't test more
# than one window function call in one test, because we currently forget the key information after a window function
# call (even when `ReduceElision` simplifies the window function call).
# TODO: Add an optimization that eliminates a Map-FlatMap pair where the Map is just creating a 1-element list on which
# the FlatMap is immediately calling `unnest_list`.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(keys, humanized expressions) AS VERBOSE TEXT FOR
SELECT
  sum(sum(x)) OVER ()
FROM t7;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum(#0{x})] // { keys: "([])" }
        Project (#0{x}) // { keys: "()" }
          ReadStorage materialize.public.t7 // { keys: "()" }
  Return // { keys: "()" }
    Project (#2) // { keys: "()" }
      Map (record_get[0](#1)) // { keys: "()" }
        FlatMap unnest_list(#0) // { keys: "()" }
          Project (#1) // { keys: "([])" }
            Map (list[row(bigint_to_numeric(#0{sum_x}), row(#0{sum_x}))]) // { keys: "([])" }
              Union // { keys: "([])" }
                Get l0 // { keys: "([])" }
                Map (null) // { keys: "()" }
                  Union // { keys: "()" }
                    Negate // { keys: "()" }
                      Project () // { keys: "([])" }
                        Get l0 // { keys: "([])" }
                    Constant // { keys: "([])" }
                      - ()

Source materialize.public.t7

Target cluster: quickstart

EOF

query I
SELECT
  sum(sum(x)) OVER ()
FROM t7;
----
101

query I
SELECT
  min(count(x)) OVER ()
FROM t7;
----
11

query I
SELECT
  count(min(x)) OVER ()
FROM t7;
----
1

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

statement ok
CREATE TABLE t9(x INT UNIQUE, y INT);

statement ok
INSERT INTO t9 VALUES (1,2), (3,null), (5,6), (7,8), (9, null), (11, null), (13, 14), (15, 16), (17, 18);

# Still testing `AggregateExpr::on_unique`. Here, the uniqueness constraint on the table makes `ReduceElision` call
# `on_unique`. There should be 0 Reduce operations in the plan.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(keys, humanized expressions) AS VERBOSE TEXT FOR
SELECT
  sum(y) OVER (PARTITION BY x)
FROM t9
ORDER BY x;
----
Explained Query:
  Finish order_by=[#0 asc nulls_last] output=[#2]
    Project (#3..=#5) // { keys: "()" }
      Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[0](#1)) // { keys: "()" }
        FlatMap unnest_list(#0) // { keys: "()" }
          Project (#2) // { keys: "()" }
            Map (list[row(integer_to_bigint(#1{y}), row(#0{x}, #1{y}))]) // { keys: "([0])" }
              ReadStorage materialize.public.t9 // { keys: "([0])" }

Source materialize.public.t9

Target cluster: quickstart

EOF

query I
SELECT
  sum(y) OVER (PARTITION BY x)
FROM t9
ORDER BY x;
----
2
NULL
6
8
NULL
NULL
14
16
18

query I
SELECT
  min(y) OVER (PARTITION BY x)
FROM t9
ORDER BY x;
----
2
NULL
6
8
NULL
NULL
14
16
18

query I
SELECT
  count(y) OVER (PARTITION BY x)
FROM t9
ORDER BY x;
----
1
0
1
1
0
0
1
1
1

query I
SELECT
  count(*) OVER (PARTITION BY x)
FROM t9
ORDER BY x;
----
1
1
1
1
1
1
1
1
1

query T
SELECT
  array_agg(y) OVER (PARTITION BY x)
FROM t9
ORDER BY x;
----
{2}
{NULL}
{6}
{8}
{NULL}
{NULL}
{14}
{16}
{18}

query R
SELECT
  avg(y+y) OVER (PARTITION BY x)
FROM t9
ORDER BY x;
----
4
NULL
12
16
NULL
NULL
28
32
36

# Test that, for window aggregations, `on_unique` is properly calling `wrapped_aggregate.default()` when the frame is
# empty (and not just returns null as the other window function cases in `on_unique`).
query I
SELECT
  count(y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
FROM t9
ORDER BY x;
----
0
0
0
0
0
0
0
0
0

query I
SELECT
  sum(y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
FROM t9
ORDER BY x;
----
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL

query II
SELECT
  count(y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING),
  sum(y)   OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
FROM t9
ORDER BY x;
----
0 NULL
0 NULL
0 NULL
0 NULL
0 NULL
0 NULL
0 NULL
0 NULL
0 NULL

query T
SELECT
  array_agg(y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
FROM t9
ORDER BY x;
----
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL

query R
SELECT
  avg(y+y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
FROM t9
ORDER BY x;
----
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL

# Test `on_unique` for lag/lead
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT
  lag(x,y,100) OVER (PARTITION BY x ORDER BY y+y)
FROM t9;
----
Explained Query:
  Project (#2)
    Map (record_get[0](#1))
      FlatMap unnest_list(#0)
        Project (#2)
          Map (list[row(case when (#1{y}) IS NULL then null else case when (#1{y} = 0) then #0{x} else 100 end end, row(#0{x}, #1{y}))])
            ReadStorage materialize.public.t9

Source materialize.public.t9

Target cluster: quickstart

EOF

query I rowsort
SELECT
  lag(x,y,100) OVER (PARTITION BY x ORDER BY y+y)
FROM t9;
----
NULL
NULL
NULL
100
100
100
100
100
100

query I rowsort
SELECT
  lead(x,y-2,120) OVER (PARTITION BY x ORDER BY x)
FROM t9;
----
NULL
NULL
NULL
1
120
120
120
120
120

# Test `on_unique` for `FusedValueWindowFunc`. There should be NO window function call in the following plan.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT
  x,
  y,
  lag(x,y,100)    OVER (PARTITION BY x ORDER BY y+y),
  lag(x,y,110)    OVER (PARTITION BY x ORDER BY y+y),
  lag(x,y,100)    OVER (PARTITION BY x ORDER BY y+y),
  first_value(y)  OVER (PARTITION BY x ORDER BY y+y),
  last_value(y)   OVER (PARTITION BY x ORDER BY y+y),
  lead(x,y-2,120) OVER (PARTITION BY x ORDER BY y+y)
FROM t9
ORDER BY x;
----
Explained Query:
  Finish order_by=[#0 asc nulls_last] output=[#0..=#7]
    Project (#3, #4, #9, #10, #9, #8, #7, #6)
      Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[0](#1), record_get[0](#5), record_get[1](#5), record_get[2](#5), record_get[3](#5), record_get[4](#5))
        FlatMap unnest_list(#0)
          Project (#3)
            Map ((#1{y}) IS NULL, list[row(row(case when #2 then null else case when (0 = (#1{y} - 2)) then #0{x} else 120 end end, #1{y}, #1{y}, case when #2 then null else case when (#1{y} = 0) then #0{x} else 100 end end, case when #2 then null else case when (#1{y} = 0) then #0{x} else 110 end end), row(#0{x}, #1{y}))])
              ReadStorage materialize.public.t9

Source materialize.public.t9

Target cluster: quickstart

EOF

query IIIIIIII
SELECT
  x,
  y,
  lag(x,y,100)    OVER (PARTITION BY x ORDER BY y+y),
  lag(x,y,110)    OVER (PARTITION BY x ORDER BY y+y),
  lag(x,y,100)    OVER (PARTITION BY x ORDER BY y+y),
  first_value(y)  OVER (PARTITION BY x ORDER BY y+y),
  last_value(y)   OVER (PARTITION BY x ORDER BY y+y),
  lead(x,y-2,120) OVER (PARTITION BY x ORDER BY y+y)
FROM t9
ORDER BY x;
----
1  2  100  110  100  2  2  1
3  NULL  NULL  NULL  NULL  NULL  NULL  NULL
5  6  100  110  100  6  6  120
7  8  100  110  100  8  8  120
9  NULL  NULL  NULL  NULL  NULL  NULL  NULL
11  NULL  NULL  NULL  NULL  NULL  NULL  NULL
13  14  100  110  100  14  14  120
15  16  100  110  100  16  16  120
17  18  100  110  100  18  18  120


## Complex tests with array_agg and then unnesting the array

query R
SELECT avg(u)
FROM (
  SELECT x, unnest(l) AS u
  FROM (
    SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l
    FROM t7
  ) AS ff1
) AS ff2
GROUP BY x%5
ORDER BY x%5;
----
-17
2
13
14
NULL

query IBRR
WITH
  array_agg_unnest_grouped_agg AS (
    SELECT x%5 AS k1, avg(u) AS avg1
    FROM (
      SELECT x, unnest(l) AS u
      FROM (
        SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l
        FROM t7
      ) AS ff1
    ) AS ff2
    GROUP BY x%5
  ),
  grouped_agg AS (
    SELECT x%5 AS k2, avg(y) AS avg2
    FROM t7
    GROUP BY x%5
  )
SELECT k1, avg1 = avg2, avg1, avg2
FROM array_agg_unnest_grouped_agg, grouped_agg
WHERE k1 = k2 AND avg1 IS NOT NULL
ORDER BY k1;
----
0  true  -17  -17
1  true  2  2
2  true  13  13
3  true  14  14

query I
WITH
  array_agg AS (
    SELECT x%5, array_agg(y) OVER (PARTITION BY x%5) AS l
    FROM t7
  )
SELECT s
FROM
  array_agg,
  LATERAL (
    SELECT sum(ul) AS s
    FROM (SELECT unnest(l) AS ul FROM array_agg) AS sq1
  ) AS sq2;
----
-188
-188
-188
-188
-188
-188
-188
-188
-188
-188
-188

query IRRB
WITH
  simple AS (
    SELECT
      x%5 AS x5_simple,
      avg(y) OVER (PARTITION BY x%5) AS avg_simple
    FROM t7
  ),
  complicated AS ( -- array_agg, then do an unnest and global agg in a subquery in a SELECT
    WITH
      array_agg AS (
        SELECT
          x%5 AS x5,
          array_agg(y) OVER (PARTITION BY x%5) AS l
        FROM t7
      )
    SELECT
      x5 AS x5_complicated,
      (
        SELECT avg(uy)
        FROM unnest(l) AS uy
      ) AS avg_complicated
    FROM array_agg
  )
SELECT DISTINCT
  x5_simple,
  avg_simple,
  avg_complicated,
  avg_simple = avg_complicated
FROM simple, complicated
WHERE x5_simple = x5_complicated
ORDER BY x5_simple;
----
0  -17  -17  true
1  2  2  true
2  13  13  true
3  14  14  true
4  NULL  NULL  NULL

# Regression test for https://github.com/MaterializeInc/materialize/pull/22270#issuecomment-1761432124
statement ok
SET cluster_replica = r1

statement error db error: ERROR: Evaluation error: "4294967295 \* 2" uint4 out of range
select max(1) over (partition by 1 order by 1, 1)
from
  (select
        subq_2."c9" as c0
      from
        (select
              subq_1."c0" as c0,
              subq_1."c2" as c1,
              subq_1."c4" as c2,
              subq_1."c2" as c3,
              subq_1."c1" as c4,
              subq_1."c0" as c5,
              subq_1."c5" as c6,
              subq_1."c0" as c7,
              subq_1."c5" as c8,
              subq_1."c1" as c9,
              subq_1."c2" as c10,
              87 as c11,
              subq_1."c1" as c12
            from
              (select
                    subq_0."c1" as c0,
                    subq_0."c0" as c1,
                    subq_0."c1" as c2,
                    subq_0."c0" as c3,
                    subq_0."c1" as c4,
                    subq_0."c0" as c5
                  from
                    (select
                          ref_0."form_of_use" as c0,
                          ref_0."default_collate_schema" as c1
                        from
                          information_schema.character_sets as ref_0
                        ) as subq_0
                    ) as subq_1
            ) as subq_2
      ) as subq_3
where (case when (((select "collection_timestamp" from mz_internal.mz_storage_usage_by_shard limit 1 offset 4)
          ) < ((select pg_catalog.min("occurred_at") from mz_internal.mz_source_status_history)
          ))
      and ((case when subq_3."c0" is NULL then TIMESTAMPTZ '2023-01-01 01:23:45+06' else TIMESTAMPTZ '2023-01-01 01:23:45+06' end
          ) <> (pg_catalog.now())) then case when (((select pg_catalog.sum("connection_id") from mz_internal.mz_sessions)
            ) - (0::uint8)) >= ((select "worker_id" from mz_introspection.mz_arrangement_sharing_per_worker limit 1 offset 3)
          ) then numrange(0,0) else numrange(0,0) end
       else case when (((select pg_catalog.sum("connection_id") from mz_internal.mz_sessions)
            ) - (0::uint8)) >= ((select "worker_id" from mz_introspection.mz_arrangement_sharing_per_worker limit 1 offset 3)
          ) then numrange(0,0) else numrange(0,0) end
       end
    ) >= (case when (0::uint4) > (pg_catalog.mod(
        CAST((select 1 from mz_internal.mz_subscriptions limit 1 offset 2)
           as uint4),
        CAST((4294967295::uint4) * (2::uint4) as uint4))) then case when (mz_catalog.map_length(
          mz_internal.mz_role_oid_memberships())) = ((select pg_catalog.min("rolconnlimit") from pg_catalog.pg_roles)
          ) then case when (null::uint4) = (null::uint4) then numrange(0,0) else numrange(0,0) end
         else case when (null::uint4) = (null::uint4) then numrange(0,0) else numrange(0,0) end
         end
       else case when (mz_catalog.map_length(
          mz_internal.mz_role_oid_memberships())) = ((select pg_catalog.min("rolconnlimit") from pg_catalog.pg_roles)
          ) then case when (null::uint4) = (null::uint4) then numrange(0,0) else numrange(0,0) end
         else case when (null::uint4) = (null::uint4) then numrange(0,0) else numrange(0,0) end
         end
       end
    );

## Tests for window function fusion

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT
  *,
  lag(x)          OVER (ORDER BY x),
  first_value(x)  OVER (ORDER BY x),
  last_value(x)   OVER (ORDER BY x),
  x*y,
  lag(y)          OVER (ORDER BY x),
  lag(x+x,1,null) OVER (PARTITION BY x ORDER BY y),
  lead(x,2,null)  OVER (PARTITION BY x ORDER BY -y NULLS FIRST),
  lead(x,2,null)  OVER (PARTITION BY x ORDER BY -y NULLS LAST),
  x+y,
  lag(x+x,2,null) OVER (PARTITION BY x ORDER BY y),
  sum(x)          OVER (ORDER BY x),
  min(x)          OVER (ORDER BY x),
  max(x)          OVER (ORDER BY y)
FROM t7
ORDER BY x,y;
----
Explained Query:
  Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#14]
    Project (#3, #4, #16, #15, #14, #17, #13, #9, #11, #10, #18, #8, #7, #6, #5)
      Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[2](#2), record_get[4](#2), record_get[5](#2), record_get[7](#2), record_get[8](#2), record_get[10](#2), record_get[12](#2), record_get[0](#1), record_get[0](#12), record_get[1](#12), record_get[2](#12), record_get[3](#12), (#3{x} * #4{y}), (#3{x} + #4{y}))
        FlatMap unnest_list(#0{fused_value_window_func})
          Reduce aggregates=[fused_value_window_func[lag[order_by=[#0 asc nulls_last]], last_value[order_by=[#0 asc nulls_last]], first_value[order_by=[#0 asc nulls_last]], lag[order_by=[#0 asc nulls_last]] order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[4](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[6](record_get[1](#0)), record_get[7](record_get[1](#0)), record_get[8](record_get[1](#0)), record_get[9](record_get[1](#0)), record_get[10](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), row(row(record_get[1](record_get[1](#0)), 1, null), record_get[0](record_get[1](#0)), record_get[0](record_get[1](#0)), row(record_get[0](record_get[1](#0)), 1, null))), record_get[0](record_get[1](#0))))]
            Project (#1)
              FlatMap unnest_list(#0{lead})
                Project (#1{lead})
                  Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[lead[order_by=[#0 asc nulls_first]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[4](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[6](record_get[1](#0)), record_get[7](record_get[1](#0)), record_get[8](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), row(record_get[0](record_get[1](#0)), 2, null)), -(record_get[1](record_get[1](#0)))))]
                    Project (#1)
                      FlatMap unnest_list(#0{lead})
                        Project (#1{lead})
                          Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[lead[order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[4](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[0](#0), record_get[0](record_get[0](#0)), record_get[1](record_get[0](#0))), row(record_get[0](record_get[1](#0)), 2, null)), -(record_get[1](record_get[1](#0)))))]
                            Project (#1)
                              FlatMap unnest_list(#0{fused_value_window_func})
                                Project (#1{fused_value_window_func})
                                  Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[fused_value_window_func[lag[order_by=[#0 asc nulls_last]], lag[order_by=[#0 asc nulls_last]] order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[0](#0), record_get[0](record_get[0](#0)), record_get[1](record_get[0](#0))), row(row((record_get[0](record_get[1](#0)) + record_get[0](record_get[1](#0))), 2, null), row((record_get[0](record_get[1](#0)) + record_get[0](record_get[1](#0))), 1, null))), record_get[1](record_get[1](#0))))]
                                    Project (#1)
                                      FlatMap unnest_list(#0{fused_window_agg})
                                        Reduce aggregates=[fused_window_agg(row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), row(record_get[0](record_get[1](#0)), record_get[0](record_get[1](#0)))), record_get[0](record_get[1](#0))))]
                                          Project (#1)
                                            FlatMap unnest_list(#0{window_agg})
                                              Reduce aggregates=[window_agg[max order_by=[#0{x} asc nulls_last]](row(row(row(#0{x}, #1{y}), #0{x}), #1{y}))]
                                                ReadStorage materialize.public.t7

Source materialize.public.t7

Target cluster: quickstart

EOF

query IIIIIIIIIIIIIII
SELECT
  *,
  lag(x)          OVER (ORDER BY x),
  first_value(x)  OVER (ORDER BY x),
  last_value(x)   OVER (ORDER BY x),
  x*y,
  lag(y)          OVER (ORDER BY x),
  lag(x+x,1,null) OVER (PARTITION BY x ORDER BY y),
  lead(x,2,null)  OVER (PARTITION BY x ORDER BY -y NULLS FIRST),
  lead(x,2,null)  OVER (PARTITION BY x ORDER BY -y NULLS LAST),
  x+y,
  lag(x+x,2,null) OVER (PARTITION BY x ORDER BY y),
  sum(x)          OVER (ORDER BY x),
  min(x)          OVER (ORDER BY x),
  max(x)          OVER (ORDER BY y)
FROM t7
ORDER BY x,y;
----
1  2  NULL  1  1  2  NULL  NULL  NULL  NULL  3  NULL  1  1  10
3  NULL  1  1  3  NULL  2  NULL  NULL  NULL  NULL  NULL  4  1  17
5  6  3  1  5  30  NULL  NULL  NULL  NULL  11  NULL  9  1  10
7  8  5  1  7  56  6  NULL  NULL  NULL  15  NULL  16  1  10
9  NULL  7  1  9  NULL  8  NULL  NULL  NULL  NULL  NULL  25  1  17
10  -50  9  1  10  -500  NULL  NULL  NULL  NULL  -40  NULL  45  1  10
10  -40  10  1  10  -400  -50  20  NULL  NULL  -30  NULL  45  1  10
11  NULL  10  1  11  NULL  -40  NULL  NULL  NULL  NULL  NULL  56  1  17
13  14  11  1  13  182  NULL  NULL  NULL  NULL  27  NULL  69  1  13
15  16  13  1  15  240  14  NULL  NULL  NULL  31  NULL  84  1  15
17  18  15  1  17  306  16  NULL  NULL  NULL  35  NULL  101  1  17

# In the following query, currently we only fuse the two inner `lag`s.
# In theory, we could also fuse the two `last_value`s.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT
  *,
  first_value(
    lag(x*x,y,1111) OVER (PARTITION BY x ORDER BY y)
  ) OVER (PARTITION BY x ORDER BY y),
  x*y,
  last_value(
    lag(x*x,y,2222) OVER (PARTITION BY x ORDER BY y)
  ) OVER (PARTITION BY x ORDER BY y+y),
  last_value(x+y) OVER (PARTITION BY x ORDER BY y+y),
  lag(y)          OVER (ORDER BY x),
  x+y
FROM t7
ORDER BY x,y;
----
Explained Query:
  Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#7]
    Project (#3, #4, #8, #9, #7, #6, #5, #10)
      Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[2](#2), record_get[3](#2), record_get[8](#2), record_get[0](#1), (#3{x} * #4{y}), (#3{x} + #4{y}))
        FlatMap unnest_list(#0{first_value})
          Project (#1{first_value})
            Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[first_value[order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[4](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[6](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), record_get[6](record_get[1](#0))), record_get[1](record_get[1](#0))))]
              Project (#1)
                FlatMap unnest_list(#0{last_value})
                  Project (#1{last_value})
                    Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[last_value[order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[0](#0), record_get[0](record_get[0](#0)), record_get[1](record_get[0](#0))), record_get[0](record_get[0](#0))), (record_get[1](record_get[1](#0)) + record_get[1](record_get[1](#0)))))]
                      Project (#1)
                        FlatMap unnest_list(#0{fused_value_window_func})
                          Project (#1{fused_value_window_func})
                            Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[fused_value_window_func[lag[order_by=[#0 asc nulls_last]], lag[order_by=[#0 asc nulls_last]] order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), row(row((record_get[0](record_get[1](#0)) * record_get[0](record_get[1](#0))), record_get[1](record_get[1](#0)), 2222), row((record_get[0](record_get[1](#0)) * record_get[0](record_get[1](#0))), record_get[1](record_get[1](#0)), 1111))), record_get[1](record_get[1](#0))))]
                              Project (#1)
                                FlatMap unnest_list(#0{last_value})
                                  Project (#1{last_value})
                                    Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[last_value[order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), (record_get[0](record_get[1](#0)) + record_get[1](record_get[1](#0)))), (record_get[1](record_get[1](#0)) + record_get[1](record_get[1](#0)))))]
                                      Project (#1)
                                        FlatMap unnest_list(#0{lag})
                                          Reduce aggregates=[lag[order_by=[#0{x} asc nulls_last]](row(row(row(#0{x}, #1{y}), row(#1{y}, 1, null)), #0{x}))]
                                            ReadStorage materialize.public.t7

Source materialize.public.t7

Target cluster: quickstart

EOF

query IIIIIIII
SELECT
  *,
  first_value(
    lag(x*x,y,1111) OVER (PARTITION BY x ORDER BY y)
  ) OVER (PARTITION BY x ORDER BY y),
  x*y,
  last_value(
    lag(x*x,y,2222) OVER (PARTITION BY x ORDER BY y)
  ) OVER (PARTITION BY x ORDER BY y+y),
  last_value(x+y) OVER (PARTITION BY x ORDER BY y+y),
  lag(y)          OVER (ORDER BY x),
  x+y
FROM t7
ORDER BY x,y;
----
1  2  1111  2  2222  3  NULL  3
3  NULL  NULL  NULL  NULL  NULL  2  NULL
5  6  1111  30  2222  11  NULL  11
7  8  1111  56  2222  15  6  15
9  NULL  NULL  NULL  NULL  NULL  8  NULL
10  -50  1111  -500  2222  -40  NULL  -40
10  -40  1111  -400  2222  -30  -50  -30
11  NULL  NULL  NULL  NULL  NULL  -40  NULL
13  14  1111  182  2222  27  NULL  27
15  16  1111  240  2222  31  14  31
17  18  1111  306  2222  35  16  35

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT
  *,
  lag(x) OVER (),
  lag(y) OVER (),
  sum(x) OVER (),
  min(x) OVER ()
FROM t7
ORDER BY x,y;
----
Explained Query:
  Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#5]
    Project (#3, #4, #9, #8, #6, #5)
      Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[3](#2), record_get[4](#2), record_get[0](#1), record_get[0](#7), record_get[1](#7))
        FlatMap unnest_list(#0{fused_value_window_func})
          Reduce aggregates=[fused_value_window_func[lag[order_by=[]], lag[order_by=[]] order_by=[]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[0](#0), record_get[0](record_get[0](#0)), record_get[1](record_get[0](#0))), row(row(record_get[1](record_get[1](#0)), 1, null), row(record_get[0](record_get[1](#0)), 1, null)))))]
            Project (#1)
              FlatMap unnest_list(#0{fused_window_agg})
                Reduce aggregates=[fused_window_agg(row(row(row(#0{x}, #1{y}), row(#0{x}, #0{x}))))]
                  ReadStorage materialize.public.t7

Source materialize.public.t7

Target cluster: quickstart

EOF

# Test the situation when the MFP above is fused into the Reduce, and the MFP can error.
query III
SELECT *
FROM (
  SELECT *, lag(x) OVER (ORDER BY x) AS l
  FROM t7
)
WHERE l/2 < 7
ORDER BY x,y,l;
----
3  NULL  1
5  6  3
7  8  5
9  NULL  7
10  -50  9
10  -40  10
11  NULL  10
13  14  11
15  16  13

# Test the situation when the MFP above is fused into the Reduce, but the MFP can't error.
query III
SELECT *
FROM (
  SELECT *, lag(x) OVER (ORDER BY x) AS l
  FROM t7
)
WHERE l < 14
ORDER BY x,y,l;
----
3  NULL  1
5  6  3
7  8  5
9  NULL  7
10  -50  9
10  -40  10
11  NULL  10
13  14  11
15  16  13

## Check some LIR plans that the optimization of fusing `Reduce` with `FlatMap UnnestList` happens.
## https://github.com/MaterializeInc/materialize/pull/29554
## These should show `fused_unnest_list=true`.

# Simple situation
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  lead(x,y-2,120) OVER (ORDER BY x)
FROM t9;
----
Explained Query:
  Mfp
    project=(#1)
    map=(record_get[0](#0))
    input_key=
    Reduce::Basic
      aggr=(0, lead[order_by=[#0 asc nulls_last]](row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x})), fused_unnest_list=true)
      val_plan
        project=(#2)
        map=(row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x}))
      key_plan
        project=()
      Get::PassArrangements materialize.public.t9
        raw=true

Source materialize.public.t9

Target cluster: quickstart

EOF

# PARTITION BY
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  lead(x,y-2,120) OVER (PARTITION BY x/2 ORDER BY x)
FROM t9;
----
Explained Query:
  Mfp
    project=(#2)
    map=(record_get[0](#1))
    input_key=#0
    Reduce::Basic
      aggr=(0, lead[order_by=[#0 asc nulls_last]](row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x})), fused_unnest_list=true)
      val_plan
        project=(#2)
        map=(row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x}))
      key_plan
        project=(#2)
        map=((#0{x} / 2))
      Get::PassArrangements materialize.public.t9
        raw=true

Source materialize.public.t9

Target cluster: quickstart

EOF

# PARTITION BY multiple columns
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  lead(x,y-2,120) OVER (PARTITION BY x/2, y ORDER BY x)
FROM t9;
----
Explained Query:
  Mfp
    project=(#3)
    map=(record_get[0](#2))
    input_key=#0, #1
    Reduce::Basic
      aggr=(0, lead[order_by=[#0 asc nulls_last]](row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x})), fused_unnest_list=true)
      val_plan
        project=(#2)
        map=(row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x}))
      key_plan
        project=(#2, #1)
        map=((#0{x} / 2))
      Get::PassArrangements materialize.public.t9
        raw=true

Source materialize.public.t9

Target cluster: quickstart

EOF

# row_number()
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  row_number() OVER (ORDER BY x),
  x
FROM t7
ORDER BY row_number
----
Explained Query:
  Finish order_by=[#0 asc nulls_last] output=[#0, #1]
    Mfp
      project=(#2, #1)
      map=(record_get[0](record_get[1](#0)), record_get[0](#0))
      input_key=
      Reduce::Basic
        aggr=(0, row_number[order_by=[#0 asc nulls_last]](row(list[row(#0, #1)], #0{x})), fused_unnest_list=true)
        val_plan
          project=(#2)
          map=(row(list[row(#0, #1)], #0{x}))
        key_plan
          project=()
        Get::PassArrangements materialize.public.t7
          raw=true

Source materialize.public.t7

Target cluster: quickstart

EOF

# MFP after is fused into the Reduce
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT *
FROM (
  SELECT *, lag(x) OVER (ORDER BY x) AS l
  FROM t7
)
WHERE l < 14
ORDER BY x,y,l;
----
Explained Query:
  Finish order_by=[#0 asc nulls_last, #1 asc nulls_last, #2 asc nulls_last] output=[#0..=#2]
    Mfp
      project=(#3, #4, #1)
      map=(record_get[0](#0), record_get[1](#0), record_get[0](#2), record_get[1](#2))
      input_key=
      Reduce::Basic
        aggr=(0, lag[order_by=[#0 asc nulls_last]](row(row(row(#0, #1), row(#0{x}, 1, null)), #0{x})), fused_unnest_list=true)
        val_plan
          project=(#2)
          map=(row(row(row(#0, #1), row(#0{x}, 1, null)), #0{x}))
        key_plan
          project=()
        mfp_after
          filter=((record_get[0](#0) < 14))
        Get::PassArrangements materialize.public.t7
          raw=true

Source materialize.public.t7

Target cluster: quickstart

EOF

# Two lags fused with each other + Reduce-FlatMap fusion.
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  *,
  lag(x) OVER (),
  lag(y) OVER ()
FROM t7
ORDER BY x,y;
----
Explained Query:
  Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#3]
    Mfp
      project=(#2, #3, #6, #5)
      map=(record_get[1](#0), record_get[0](#1), record_get[1](#1), record_get[0](#0), record_get[0](#4), record_get[1](#4))
      input_key=
      Reduce::Basic
        aggr=(0, fused_value_window_func[lag[order_by=[]], lag[order_by=[]] order_by=[]](row(row(row(#0, #1), row(row(#1{y}, 1, null), row(#0{x}, 1, null))))), fused_unnest_list=true)
        val_plan
          project=(#2)
          map=(row(row(row(#0, #1), row(row(#1{y}, 1, null), row(#0{x}, 1, null)))))
        key_plan
          project=()
        Get::PassArrangements materialize.public.t7
          raw=true

Source materialize.public.t7

Target cluster: quickstart

EOF

## Window functions on big relations.

statement ok
CREATE TABLE t10(t timestamptz, d1 int, d2 text, d3 int, d4 uint8, d5 int, d6 text);

statement ok
INSERT INTO t10
SELECT generate_series(1,1000)::mz_timestamp::timestamptz, 5, 'aaa', 7, 8, 9, 'bbb';

statement ok
CREATE VIEW v1 AS
SELECT t, d1, d2, EXTRACT(MILLISECOND FROM t) * 91 % 1223 AS d3, d4, d5, d6
FROM t10;

statement ok
CREATE MATERIALIZED VIEW mv1 AS
SELECT t, d1, d2, d3, d4, d6, lag(d1) OVER (ORDER BY t) - d1 AS r
FROM v1;

query I
SELECT sum(r)
FROM mv1;
----
0

statement ok
CREATE MATERIALIZED VIEW mv2 AS
SELECT *, row_number() OVER (ORDER BY t) AS rn
FROM mv1;

query I
SELECT sum(r)
FROM (
  SELECT rn - lag(rn) OVER (ORDER BY t) AS r
  FROM mv2
);
----
999

query I
SELECT sum(lv)
FROM (
  SELECT last_value(rn) OVER () AS lv
  FROM mv2
);
----
1000000

statement ok
DELETE FROM t10

statement ok
INSERT INTO t10
SELECT generate_series(1,70000)::mz_timestamp::timestamptz, 5, 'aaa', 7, 8, 9, 'bbb';

query I
SELECT sum(r)
FROM mv1;
----
0

query I
SELECT sum(r)
FROM (
  SELECT rn - lag(rn) OVER (ORDER BY t) AS r
  FROM mv2
);
----
69999

## QUALIFY
query II
SELECT *
FROM t6
QUALIFY row_number() OVER (PARTITION BY x%3 ORDER BY y) = 1;
----
1  2
5  6
15  16

query IIII rowsort
SELECT *, x%3, row_number() OVER (PARTITION BY x%3 ORDER BY y)
FROM t6
QUALIFY row_number() OVER (PARTITION BY x%3 ORDER BY y) = 1;
----
15  16  0  1
1  2  1  1
5  6  2  1

query II
SELECT *
FROM t6
QUALIFY sum(x) OVER (ORDER BY y) = 26;
----
13  14

query II
SELECT *
FROM (SELECT * FROM t6 UNION ALL SELECT * FROM t6)
QUALIFY sum(x) OVER (ORDER BY y) = 26;
----
7  8
7  8

query I
SELECT avg(u)
FROM (
  SELECT x, unnest(l) AS u
  FROM (
    SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l
    FROM t7
  ) AS ff1
) AS ff2
GROUP BY x%5
QUALIFY sum(avg(u)) OVER (ORDER BY x%5) = -15
ORDER BY x%5;
----
2

query II
SELECT *
FROM (
  SELECT x, unnest(l) AS u
  FROM (
    SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l
    FROM t7
  ) AS ff1
)
QUALIFY x-lag(x) OVER (ORDER BY x) = 0 AND lag(x) OVER (ORDER BY x) - lag(lag(x) OVER (ORDER BY x)) OVER (ORDER BY x) = 0
ORDER BY x,u;
----
5  6
5  16
10  -40
10  -40
10  6
10  6
10  16
10  16
15  6
15  16

query III
SELECT *, sum(x-9) OVER (ORDER BY x, u)
FROM
(
  SELECT DISTINCT *
  FROM (
    SELECT x, unnest(l) AS u
    FROM (
      SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l
      FROM t7
    ) AS ff1
  )
  QUALIFY x-lag(x) OVER (ORDER BY x) = 0 AND lag(x) OVER (ORDER BY x) - lag(lag(x) OVER (ORDER BY x)) OVER (ORDER BY x) = 0
)
QUALIFY sum(x-9) OVER (ORDER BY x, u) > -6
ORDER BY x,u;
----
5  6  -4
10  16  -5
15  6  1
15  16  7