Materialize/test/sqllogictest/transform/fold_constants.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.

statement ok
CREATE VIEW billion AS SELECT * FROM generate_series(0, 999) AS x, generate_series(0, 999) AS y, generate_series(0, 999) AS z;

statement ok
CREATE TABLE edges(src INTEGER NOT NULL, dst INTEGER NOT NULL);

# Test that this query doesn't compute the answer entirely as a constant
# (as the way that works currently would require a huge memory blowup).
#
# If we make it so that reductions on constants
# can be computed without writing out
# the entire input constant in unary, we can get rid of this test:
# See https://github.com/MaterializeInc/database-issues/issues/3723 .

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT count(*) FROM billion;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[count(*)] monotonic // { arity: 1 }
        Constant // { arity: 0 }
          - (() x 1000000000)
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Map (0) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Target cluster: quickstart

EOF

# Regression test for https://github.com/MaterializeInc/database-issues/issues/4672

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

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

query IIIRR
SELECT  (a1.f1) AS c1, (a2.f1) AS c2, (a1.f2) AS c3, (MIN ( 4 )) AS agg1 , (AVG ( a1 . f2 + 4 )) AS agg2 FROM ( SELECT a1 . f2 + 4 AS f1 , a2 . f2 AS f2 FROM t1 AS a1  JOIN t2 AS a2 ON ( a1 . f1 = a1 . f2 + 8 ) WHERE a1 . f1 + a1 . f2 < a2 . f2 + a1 . f2 AND NOT ( NOT ( a2 . f2 IS NOT NULL ) ) ORDER BY 1 , 2 LIMIT 4  ) AS a1  JOIN ( SELECT AVG ( a2 . f2 + a2 . f2 ) AS f1 , AVG ( a2 . f2 ) AS f2 FROM t2 AS a1  JOIN t2 AS a2 ON ( NOT ( NOT ( a2 . f2 = 5 ) ) ) WHERE a2 . f1 IS NOT NULL AND a2 . f2  IN ( 0 , 3 , 0 , 4 , 9 , 6 ) ORDER BY 1 , 2 LIMIT 2  ) AS a2 ON ( NOT ( 4 = 3 ) ) WHERE a1 . f2 + a2 . f1  IN ( 0 , 7 ) AND 5 NOT IN ( SELECT  c3 AS x1 FROM ( SELECT  (a1.f1) AS c1, (a2.f1) AS c2, (a1.f2) AS c3, (FIRST_VALUE ( a2.f1 ) OVER (  ORDER BY a1.f1 , a2.f1 , a1.f2 )) AS agg1 , (MIN ( 7 )) AS agg2 FROM ( SELECT a2 . f1 AS f1 , COUNT ( 2 ) AS f2 FROM t1 AS a1  JOIN t1 AS a2 ON ( 8 NOT IN ( 3 , 2 ) ) WHERE a1 . f1 + a2 . f2 IS NOT NULL AND a2 . f2  IN ( SELECT DISTINCT agg2 AS x1 FROM ( SELECT  (a1.f1) AS c1, (a2.f1) AS c2, (a1.f2) AS c3, (MIN ( a2 . f1 )) AS agg1 , (MIN ( 2 )) AS agg2 FROM t1 AS a1  JOIN ( SELECT 8 AS f1 , COUNT ( 8 ) AS f2 FROM t2 AS a1 LEFT JOIN t2 AS a2 ON ( 1 < a1 . f2 ) WHERE 2 > 7 AND a2 . f2 + a1 . f2 > a1 . f2 + a1 . f2 AND a2 . f2 + a2 . f1 IS  NULL GROUP BY 1 ORDER BY 1 , 2 LIMIT 5 OFFSET 2 ) AS a2 USING ( f2 , f1 ) WHERE a1 . f1 + a1 . f2 IS NOT NULL AND NOT ( 2  IN ( 8 , 2 ) ) AND 3 > a1 . f1 AND a2 . f2 IS  NULL GROUP BY 1 , 2 , 3  ) AS dt ORDER BY 1 LIMIT 5  ) OR 3 = a1 . f1 GROUP BY 1 ORDER BY 1 , 2 LIMIT 8  ) AS a1 RIGHT JOIN t1 AS a2 ON ( NOT ( 4 NOT IN ( 5 , 3 ) ) ) WHERE NOT ( 8 > a1 . f2 + a1 . f1 ) OR a2 . f2 IS NOT NULL AND 5 NOT IN ( 4 , 4 , 2 , 6 ) GROUP BY 1 , 2 , 3  ) AS dt ORDER BY 1 LIMIT 9 OFFSET 8 ) GROUP BY 1 , 2 , 3 ;
----



# WITH MUTUALLY RECURSIVE support
# -------------------------------

# Fold `Constant` inputs in WMR branches.
# In theory we should be able to run the WMR loop once under the assumption
# that all initial LetRec bindings are empty. If do this, the `l0` binding
# below will simplify to a constant.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(src INT, dst INT) AS (
    SELECT * FROM c0
    UNION
    SELECT src * 2, dst * 2 FROM (VALUES (31, 32), (33, 34)) AS constant(src, dst)
    UNION
    (
      SELECT src + 1, dst + 1 FROM (VALUES (41, 42), (43, 44), (44, 45)) AS constant(src, dst) WHERE src > 1
      EXCEPT
      SELECT src + 2, dst + 2 FROM (VALUES (41, 42), (43, 44), (44, 45)) AS constant(src, dst)
    )
    UNION
    (
      SELECT DISTINCT
        x.src, y.dst
      FROM
        (VALUES (51, 52), (52, 53), (53, 54), (54, 55)) AS x(src, dst),
        (VALUES (52, 53), (53, 54), (54, 55)) AS y(src, dst),
        (VALUES (53, 51), (54, 52), (54, 53)) AS z(src, dst)
      WHERE
        x.dst = y.src AND y.dst = z.src AND z.dst = x.src
    )
  )
SELECT * FROM c0
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Distinct project=[#0, #1] monotonic // { arity: 2 }
        Union // { arity: 2 }
          Get l0 // { arity: 2 }
          Constant // { arity: 2 }
            - (42, 43)
            - (44, 45)
            - (51, 53)
            - (52, 54)
            - (62, 64)
            - (66, 68)
  Return // { arity: 2 }
    Get l0 // { arity: 2 }

Target cluster: quickstart

EOF


# Replace subtrees rooted at `Filter false` with `Constant <empty>`
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(n INT) AS (
    SELECT src FROM edges WHERE false -- literal false
    UNION ALL
    SELECT dst FROM edges
    UNION ALL
    SELECT * FROM c0 WHERE n IS NULL -- impossible condition (depends on column_knowledge)
    UNION
    SELECT * FROM c0 WHERE n IS NOT NULL -- complement (always true)
  )
SELECT * FROM c0
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Distinct project=[#0{dst}] // { arity: 1 }
        Union // { arity: 1 }
          Project (#1{dst}) // { arity: 1 }
            ReadStorage materialize.public.edges // { arity: 2 }
          Get l0 // { arity: 1 }
  Return // { arity: 1 }
    Get l0 // { arity: 1 }

Source materialize.public.edges

Target cluster: quickstart

EOF

statement ok
DROP TABLE t1;

statement ok
DROP TABLE t2;

# Window function tests
# These tests were mostly copied (at some point) from `window_funcs.slt`, but here they have `WITH ... VALUES ...`, i.e.
# constant inputs. These are good to have because constant folding is a different code path from the normal execution.
# ----------------------------------------------------------------------------------------------------------------------

mode cockroach

statement error db error: ERROR: window function pg_catalog\.row_number requires an OVER clause
WITH t (x) AS (VALUES ('a'), ('b'), ('c'))
SELECT row_number() FROM t

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

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

query IT
WITH t (x, y) AS (VALUES ('a', 98), ('b', 99), ('c', 98))
SELECT row_number() OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY row_number, x
----
1  a
1  b
2  c

query IT
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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

# Make sure a non-column expression following the window function is correctly
# handled.
query ITT
WITH t (x) AS (VALUES ('a'))
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

query II
WITH t (x) AS (VALUES ('a'), ('b'))
SELECT row_number() OVER (), row_number() OVER () from t
----
1 1
2 2

# Regression for database-issues#2962
query error window functions are not allowed in ON
WITH t (x) AS (VALUES ('a'), ('b'), ('c'))
SELECT * FROM t AS v JOIN t ON row_number() over () > 1;

query error window functions are not allowed in WHERE
WITH t (x) AS (VALUES ('a'), ('b'), ('c'))
SELECT * FROM t
WHERE row_number() over () > 1;

query T
WITH t (x) AS (VALUES ('a'), ('b'), ('c'))
SELECT DISTINCT ON (row_number() OVER ()) *
FROM t
ORDER BY row_number() OVER ()
----
a
b
c

# dense_rank

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

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

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

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

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

query IT
WITH t (x) AS (VALUES ('a'), ('b'), ('b'), ('c'), ('c'))
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 IT
WITH t (x, y) AS (VALUES ('a', 98), ('b', 99), ('c', 98))
SELECT dense_rank() OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY dense_rank, x
----
1  a
1  b
2  c

query IT
WITH t (x, y) AS (VALUES ('a', 98), ('b', 99), ('c', 98), ('a', 98), ('a', 99))
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

query IT
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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 IT
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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 IT
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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

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


query IITT
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 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 IITT
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 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

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

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

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

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

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

query TT
WITH t (x) AS (VALUES ('a'), ('b'), ('b'), ('c'), ('c'))
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

query TT
WITH t (x, y) AS (VALUES ('a', 98), ('b', 99), ('c', 98))
SELECT lag(x) OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY x, lag
----
NULL  a
NULL  b
a     c

query TT
WITH t (x, y) AS (VALUES ('a', 98), ('b', 99), ('c', 98), ('a', 98), ('a', 99))
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

query TT
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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

query ITTT
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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

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

query II
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
SELECT f1, lag(f1, 1, f1 * f2) OVER (PARTITION BY f1, f2)
FROM (SELECT DISTINCT f1, f2 FROM t) q
----
1  2
3  12

## lead

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

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

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

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

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

query TT
WITH t (x) AS (VALUES ('a'), ('b'), ('b'), ('c'), ('c'))
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

query TT
WITH t (x, y) AS (VALUES ('a', 98), ('b', 99), ('c', 98))
SELECT lead(x) OVER (PARTITION BY y ORDER BY x), x FROM t
ORDER BY x, lead
----
c     a
NULL  b
NULL  c

query TT
WITH t (x, y) AS (VALUES ('a', 98), ('b', 99), ('c', 98), ('a', 98), ('a', 99))
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

query TT
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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
WITH t (x, y) AS (VALUES ('a', 1), ('b', 2), ('c', 1))
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

query ITTT
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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
WITH t (f1, f2, f3) 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), (7, 'd', -5.0))
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

# reduce_elision code path
# Default offset
query II
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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
WITH t (f1, f2) AS (VALUES (1, 2), (1, 2), (3, 4))
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

## 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

# Default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
query ITII
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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 near-overflow behavior on offsets
# u64::MAX FOLLOWING
query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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, first_value

# u64::MAX PRECEDING
query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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

## last_value

# Default frame (RANGE BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW)
query ITII
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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
query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
WITH t (f1, f2, f3) AS (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))
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 18446744073709551615 PRECEDING AND 18446744073709551615 PRECEDING)
FROM t
ORDER BY f2, f3, f1, last_value

query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported
WITH t (f1, f2, f3) AS (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))
SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 18446744073709551615 PRECEDING AND 18446744073709551614 PRECEDING)
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
WITH t (f1, f2, f3) AS (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))
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
WITH t (f1, f2, f3) AS (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))
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

# Test window aggregations with and without fusion
query III
WITH t (f1, f2, f3) AS (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))
SELECT
  sum(f1)   OVER (ORDER BY f2) s,
  max(f1)   OVER (ORDER BY f2) m,
  count(f1) OVER (ORDER BY f3) c
FROM t
ORDER BY s, m, c;
----
8  3  1
8  3  2
8  3  3
8  3  4
16  4  5
16  4  6
22  4  7
22  4  8
22  4  9
29  7  10