# Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. # This test may seem simple, but it is surprisingly good at verifying that # logical timestamp handling for internal inputs is sane. mode cockroach statement ok CREATE TABLE t(x string); statement ok INSERT INTO t VALUES ('a'), ('b'), ('c'); statement error db error: ERROR: window function pg_catalog\.row_number requires an OVER clause SELECT row_number() FROM t; statement error db error: ERROR: window function pg_catalog\.row_number requires an OVER clause SELECT * FROM t ORDER BY row_number(); statement error db error: ERROR: window function pg_catalog\.row_number requires an OVER clause SELECT * FROM t QUALIFY row_number(); statement error db error: ERROR: OVER clause not allowed on pg_catalog\.int8range\. The OVER clause can only be used with window functions \(including aggregations\)\. SELECT int8range(1, 1, '') OVER (PARTITION BY 1 ORDER BY 1); query IT SELECT row_number() OVER (ORDER BY x), x FROM t ORDER BY row_number ---- 1 a 2 b 3 c query IT SELECT row_number() OVER (ORDER BY x DESC), x FROM t ORDER BY row_number ---- 1 c 2 b 3 a statement ok DROP TABLE t; statement ok CREATE TABLE t(x string, y int); statement ok INSERT INTO t VALUES ('a', 98), ('b', 99), ('c', 98); query IT SELECT row_number() OVER (PARTITION BY y ORDER BY x), x FROM t ORDER BY row_number, x ---- 1 a 1 b 2 c statement ok DROP TABLE t; statement ok CREATE TABLE t(x string, y int); statement ok INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1); query IT SELECT row_number() OVER (PARTITION BY y ORDER BY x DESC), x FROM t ORDER BY row_number, x ---- 1 b 1 c 2 a query IT SELECT row_number() OVER (PARTITION BY x ORDER BY x), x FROM t ORDER BY row_number, x ---- 1 a 1 b 1 c query IT SELECT row_number() OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x FROM t AS a1, t AS a2 ORDER BY q DESC ---- 9 c 8 c 7 c 6 b 5 b 4 b 3 a 2 a 1 a statement ok DROP TABLE t; statement ok CREATE TABLE t(x string); statement ok INSERT INTO t VALUES ('a'); # Make sure a non-column expression following the window function is correctly # handled. query ITT SELECT row_number() OVER (PARTITION BY NULL) AS q, x, 'b' FROM t ---- 1 a b # Regression test for database-issues#2730 query II SELECT row_number() OVER (), row_number() OVER () ---- 1 1 statement ok INSERT INTO t VALUES ('b'); query II SELECT row_number() OVER (), row_number() OVER () from t ---- 1 1 2 2 statement ok DROP TABLE t; statement ok CREATE TABLE t(x string, y int); statement ok INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1); query T multiline EXPLAIN RAW PLAN FOR SELECT row_number() OVER (PARTITION BY x ORDER BY x), x FROM t ORDER BY row_number, x ---- Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0, #1] Project (#2, #0) Map (row_number() over (partition by [#0{x}] order by [#0{x} asc nulls_last])) Get materialize.public.t Target cluster: quickstart EOF query T multiline EXPLAIN DECORRELATED PLAN WITH(arity) FOR SELECT row_number() OVER (PARTITION BY x ORDER BY x), x FROM t ORDER BY row_number, x ---- Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0, #1] Project (#3, #0) // { arity: 2 } Map (#2) // { arity: 4 } Project (#3..=#5) // { arity: 3 } Map (record_get[0](record_get[1](#2)), record_get[1](record_get[1](#2)), record_get[0](#2)) // { arity: 6 } FlatMap unnest_list(#1) // { arity: 3 } Reduce group_by=[#0] aggregates=[row_number[order_by=[#0 asc nulls_last]](row(list[row(#0, #1)], #0{x}))] // { arity: 2 } CrossJoin // { arity: 2 } Constant // { arity: 0 } - () Get materialize.public.t // { arity: 2 } Target cluster: quickstart EOF # # Regression test for database-issues#2760 # statement ok CREATE TABLE t1 (f1 INTEGER, f2 INTEGER); ---- statement ok ---- INSERT INTO t1 VALUES (1, 1), (2, 2), (4, 4); statement ok CREATE TABLE t2 (f1 INTEGER, f2 INTEGER); ---- statement ok INSERT INTO t2 VALUES (1, 1), (1, 2), (2, 2); ---- query I SELECT f1 FROM t1 WHERE f1 IN (SELECT ROW_NUMBER() OVER () FROM t2); ---- 1 2 query T multiline EXPLAIN DECORRELATED PLAN WITH(arity) FOR SELECT f1 FROM t1 WHERE f1 IN (SELECT ROW_NUMBER() OVER () FROM t2); ---- With cte l0 = CrossJoin // { arity: 2 } Constant // { arity: 0 } - () Get materialize.public.t1 // { arity: 2 } cte l1 = Distinct project=[#0] // { arity: 1 } Get l0 // { arity: 2 } cte l2 = Map (true) // { arity: 2 } Distinct project=[#0] // { arity: 1 } Filter (integer_to_bigint(#0{f1}) = #1{right_col0_0}) // { arity: 2 } Project (#0, #4) // { arity: 2 } Map (#3) // { arity: 5 } Project (#3..=#6) // { arity: 4 } Map (record_get[0](record_get[1](#2)), record_get[1](record_get[1](#2)), record_get[2](record_get[1](#2)), record_get[0](#2)) // { arity: 7 } FlatMap unnest_list(#1) // { arity: 3 } Reduce group_by=[#0] aggregates=[row_number[order_by=[]](row(list[row(#0, #1, #2)]))] // { arity: 2 } CrossJoin // { arity: 3 } Get l1 // { arity: 1 } Get materialize.public.t2 // { arity: 2 } Return // { arity: 1 } Project (#0) // { arity: 1 } Filter #2 // { arity: 3 } Project (#0, #1, #3) // { arity: 3 } Join on=(#0 = #2) // { arity: 4 } Get l0 // { arity: 2 } Union // { arity: 2 } Get l2 // { arity: 2 } CrossJoin // { arity: 2 } Project (#0) // { arity: 1 } Join on=(#0 = #1) // { arity: 2 } Union // { arity: 1 } Negate // { arity: 1 } Distinct project=[#0] // { arity: 1 } Get l2 // { arity: 2 } Distinct project=[#0] // { arity: 1 } Get l1 // { arity: 1 } Get l1 // { arity: 1 } Constant // { arity: 1 } - (false) Target cluster: quickstart EOF query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT f1 FROM t1 WHERE f1 IN (SELECT ROW_NUMBER() OVER () FROM t2); ---- Explained Query: With cte l0 = Project (#0{f1}) // { arity: 1 } ReadStorage materialize.public.t1 // { arity: 2 } Return // { arity: 1 } Project (#0{f1}) // { arity: 1 } Join on=(#0{f1} = #1) type=differential // { arity: 2 } implementation %1[#0]UKA » %0:l0[#0]K ArrangeBy keys=[[#0{f1}]] // { arity: 1 } Get l0 // { arity: 1 } ArrangeBy keys=[[#0]] // { arity: 1 } Distinct project=[record_get[0](record_get[1](#0))] // { arity: 1 } Project (#1) // { arity: 1 } Filter (integer_to_bigint(record_get[0](record_get[1](#1))) = record_get[0](#1)) // { arity: 2 } FlatMap unnest_list(#0{row_number}) // { arity: 2 } Project (#1{row_number}) // { arity: 1 } Reduce group_by=[#0{f1}] aggregates=[row_number[order_by=[]](row(list[row(#0{f1}, #1{f1}, #2{f2})]))] // { arity: 2 } CrossJoin type=differential // { arity: 3 } implementation %0[×] » %1:t2[×] ArrangeBy keys=[[]] // { arity: 1 } Distinct project=[#0{f1}] // { arity: 1 } Get l0 // { arity: 1 } ArrangeBy keys=[[]] // { arity: 2 } ReadStorage materialize.public.t2 // { arity: 2 } Source materialize.public.t1 Source materialize.public.t2 Target cluster: quickstart EOF query IIIII SELECT * FROM t1, LATERAL(SELECT t2.*, ROW_NUMBER() OVER() FROM t2 WHERE t1.f1 = t2.f1) AS foo; ---- 1 1 1 1 1 1 1 1 2 2 2 2 2 2 1 query IIIII SELECT * FROM t1, LATERAL(SELECT t2.*, ROW_NUMBER() OVER() FROM t2 WHERE t1.f1 = t2.f1 AND t1.f2 = t2.f2) AS foo; ---- 1 1 1 1 1 2 2 2 2 1 query IIIII SELECT * FROM t1, LATERAL(SELECT t2.*, ROW_NUMBER() OVER() FROM t2 WHERE t1.f2 = t2.f2) AS foo; ---- 1 1 1 1 1 2 2 1 2 1 2 2 2 2 2 query IIIII rowsort SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER() FROM t1 WHERE t1.f1 = t2.f1) AS foo; ---- 1 1 1 1 1 1 2 1 1 1 2 2 2 2 1 query IIIII SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER() FROM t1 WHERE t1.f1 = t2.f1 AND t1.f2 = t2.f2) AS foo; ---- 1 1 1 1 1 2 2 2 2 1 query IIIII rowsort SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER() FROM t1 WHERE t1.f2 = t2.f2) AS foo; ---- 1 1 1 1 1 1 2 2 2 1 2 2 2 2 1 # Check that the partition key comes after the outer columns in the grouping key query T multiline EXPLAIN DECORRELATED PLAN WITH(arity) FOR SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER() FROM t1 WHERE t1.f2 = t2.f2) AS foo; ---- With cte l0 = CrossJoin // { arity: 2 } Constant // { arity: 0 } - () Get materialize.public.t2 // { arity: 2 } Return // { arity: 5 } Project (#0, #1, #3..=#5) // { arity: 5 } Join on=(#1 = #2) // { arity: 6 } Get l0 // { arity: 2 } Project (#0..=#2, #4) // { arity: 4 } Map (#3) // { arity: 5 } Project (#3..=#6) // { arity: 4 } Map (record_get[0](record_get[1](#2)), record_get[1](record_get[1](#2)), record_get[2](record_get[1](#2)), record_get[0](#2)) // { arity: 7 } FlatMap unnest_list(#1) // { arity: 3 } Reduce group_by=[#0] aggregates=[row_number[order_by=[]](row(list[row(#0, #1, #2)]))] // { arity: 2 } Filter (#2{f2} = #0{f2}) // { arity: 3 } CrossJoin // { arity: 3 } Distinct project=[#1] // { arity: 1 } Get l0 // { arity: 2 } Get materialize.public.t1 // { arity: 2 } Target cluster: quickstart EOF query T multiline EXPLAIN DECORRELATED PLAN WITH(arity) FOR SELECT * FROM t2, LATERAL(SELECT t1.*, ROW_NUMBER() OVER(PARTITION BY f1) FROM t1 WHERE t1.f2 = t2.f2) AS foo; ---- With cte l0 = CrossJoin // { arity: 2 } Constant // { arity: 0 } - () Get materialize.public.t2 // { arity: 2 } Return // { arity: 5 } Project (#0, #1, #3..=#5) // { arity: 5 } Join on=(#1 = #2) // { arity: 6 } Get l0 // { arity: 2 } Project (#0..=#2, #4) // { arity: 4 } Map (#3) // { arity: 5 } Project (#4..=#7) // { arity: 4 } Map (record_get[0](record_get[1](#3)), record_get[1](record_get[1](#3)), record_get[2](record_get[1](#3)), record_get[0](#3)) // { arity: 8 } FlatMap unnest_list(#2) // { arity: 4 } Reduce group_by=[#0, #1] aggregates=[row_number[order_by=[]](row(list[row(#0, #1, #2)]))] // { arity: 3 } Filter (#2{f2} = #0{f2}) // { arity: 3 } CrossJoin // { arity: 3 } Distinct project=[#1] // { arity: 1 } Get l0 // { arity: 2 } Get materialize.public.t1 // { arity: 2 } Target cluster: quickstart EOF statement ok DROP TABLE t; statement ok CREATE TABLE t(x string); statement ok INSERT INTO t VALUES ('a'), ('b'), ('c'); # Regression for database-issues#2962 query error window functions are not allowed in ON SELECT * FROM t AS v JOIN t ON row_number() over () > 1; query error window functions are not allowed in WHERE SELECT * FROM t WHERE row_number() over () > 1; statement ok CREATE TABLE not_allowed_tests ( v INT, w INT, k INT ); query error window functions are not allowed in GROUP BY SELECT * FROM not_allowed_tests GROUP BY v, count(w) OVER (); query error window functions are not allowed in GROUP BY SELECT count(w) OVER () FROM not_allowed_tests GROUP BY 1; query error window functions are not allowed in RETURNING INSERT INTO not_allowed_tests (k, v) VALUES (99, 100) RETURNING sum(v) OVER (); query error window functions are not allowed in LIMIT SELECT sum(v) FROM not_allowed_tests GROUP BY k LIMIT sum(v) OVER (); query error db error: ERROR: window functions are not allowed in OFFSET \(function pg_catalog\.sum\) SELECT sum(v) FROM not_allowed_tests GROUP BY k LIMIT 1 OFFSET sum(v) OVER (); query error window functions are not allowed in VALUES INSERT INTO not_allowed_tests (k, v) VALUES (99, count(1) OVER ()); query error window functions are not allowed in WHERE SELECT k FROM not_allowed_tests WHERE avg(k) OVER () > 1; query error window functions are not allowed in HAVING SELECT 1 FROM not_allowed_tests GROUP BY 1 HAVING sum(1) OVER (PARTITION BY 1) > 1; query T SELECT DISTINCT ON (row_number() OVER ()) * FROM t ORDER BY row_number() OVER () ---- a b c # rank and dense_rank query error db error: ERROR: function rank has 0 parameters, but was called with 1 SELECT rank(x) OVER (ORDER BY x), dense_rank() OVER (ORDER BY x), x FROM t ORDER BY dense_rank; query IT SELECT dense_rank() OVER (ORDER BY x), x FROM t ORDER BY dense_rank ---- 1 a 2 b 3 c statement ok INSERT INTO t VALUES ('b'); query IT SELECT dense_rank() OVER (ORDER BY x), x FROM t ORDER BY dense_rank ---- 1 a 2 b 2 b 3 c statement ok INSERT INTO t VALUES ('c'); query IT SELECT dense_rank() OVER (ORDER BY x), x FROM t ORDER BY dense_rank ---- 1 a 2 b 2 b 3 c 3 c statement ok DROP TABLE t; statement ok CREATE TABLE t(x string); statement ok INSERT INTO t VALUES ('a'), ('b'), ('c'); query IT SELECT dense_rank() OVER (ORDER BY x DESC), x FROM t ORDER BY dense_rank ---- 1 c 2 b 3 a query IIT rowsort SELECT rank() OVER (ORDER BY x), dense_rank() OVER (ORDER BY x), x FROM t ---- 1 1 a 2 2 b 3 3 c query IIT SELECT rank() OVER (ORDER BY x DESC), dense_rank() OVER (ORDER BY x DESC), x FROM t ORDER BY dense_rank ---- 1 1 c 2 2 b 3 3 a statement ok INSERT INTO t VALUES ('b'); query IT SELECT dense_rank() OVER (ORDER BY x DESC), x FROM t ORDER BY dense_rank ---- 1 c 2 b 2 b 3 a query IIT SELECT rank() OVER (ORDER BY x), dense_rank() OVER (ORDER BY x), x FROM t ---- 1 1 a 2 2 b 2 2 b 4 3 c query IIT SELECT rank() OVER (ORDER BY x DESC), dense_rank() OVER (ORDER BY x DESC), x FROM t ORDER BY dense_rank ---- 1 1 c 2 2 b 2 2 b 4 3 a statement ok INSERT INTO t VALUES ('c'); query IT SELECT dense_rank() OVER (ORDER BY x DESC), x FROM t ORDER BY dense_rank ---- 1 c 1 c 2 b 2 b 3 a query IIT SELECT rank() OVER (ORDER BY x DESC), dense_rank() OVER (ORDER BY x DESC), x FROM t ORDER BY dense_rank ---- 1 1 c 1 1 c 3 2 b 3 2 b 5 3 a statement ok DROP TABLE t; statement ok CREATE TABLE t(x string, y int); statement ok INSERT INTO t VALUES ('a', 98), ('b', 99), ('c', 98); query IIT rowsort SELECT rank() OVER (PARTITION BY y ORDER BY x), dense_rank() OVER (PARTITION BY y ORDER BY x), x FROM t; ---- 1 1 a 1 1 b 2 2 c query IT SELECT dense_rank() OVER (PARTITION BY y ORDER BY x), x FROM t ORDER BY dense_rank, x; ---- 1 a 1 b 2 c statement ok INSERT INTO t VALUES ('a', 98), ('a', 99); query IIT SELECT rank() OVER (PARTITION BY y ORDER BY x), dense_rank() OVER (PARTITION BY y ORDER BY x), x FROM t; ---- 1 1 a 1 1 a 1 1 a 2 2 b 3 2 c query IT SELECT dense_rank() OVER (PARTITION BY y ORDER BY x), x FROM t ORDER BY dense_rank, x; ---- 1 a 1 a 1 a 2 b 2 c statement ok DROP TABLE t; statement ok CREATE TABLE t(x string, y int); statement ok INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1); query IIT SELECT rank() OVER (PARTITION BY y ORDER BY x DESC), dense_rank() OVER (PARTITION BY y ORDER BY x DESC), x FROM t; ---- 1 1 b 1 1 c 2 2 a query IT SELECT dense_rank() OVER (PARTITION BY y ORDER BY x DESC), x FROM t ORDER BY dense_rank, x; ---- 1 b 1 c 2 a query IIT rowsort SELECT rank() OVER (PARTITION BY x ORDER BY x), dense_rank() OVER (PARTITION BY x ORDER BY x), x FROM t; ---- 1 1 a 1 1 b 1 1 c query IT SELECT dense_rank() OVER (PARTITION BY x ORDER BY x), x FROM t ORDER BY dense_rank, x; ---- 1 a 1 b 1 c query IIT rowsort SELECT rank() OVER (PARTITION BY NULL ORDER BY 10000), dense_rank() OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x FROM t AS a1, t AS a2; ---- 1 1 a 1 1 a 1 1 a 1 1 b 1 1 b 1 1 b 1 1 c 1 1 c 1 1 c query IT SELECT dense_rank() OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x FROM t AS a1, t AS a2 ORDER BY q DESC, a1.x DESC; ---- 1 c 1 c 1 c 1 b 1 b 1 b 1 a 1 a 1 a statement ok DROP TABLE t; statement ok CREATE TABLE t(x string); statement ok INSERT INTO t VALUES ('a'); # Make sure a non-column expression following the window function is correctly # handled. query IITT SELECT rank() OVER (PARTITION BY NULL), dense_rank() OVER (PARTITION BY NULL) AS q, x, 'b' FROM t; ---- 1 1 a b query ITT SELECT dense_rank() OVER (PARTITION BY NULL) AS q, x, 'b' FROM t; ---- 1 a b statement ok DROP TABLE t; statement ok CREATE TABLE t(x int, y string, z numeric); statement ok INSERT INTO t VALUES (1, 'a', 1.0), (2, 'a', 1.0), (2, 'a', 1.0), (3, 'a', 1.0), (4, 'b', 0), (4, 'b', 1), (1, 'c', 'NaN'), (2, 'c', 'NaN'), (3, 'c', 1.0); query IIITT SELECT rank() OVER (PARTITION BY y ORDER BY x DESC, z), dense_rank() OVER (PARTITION BY y ORDER BY x DESC, z), x, y, z FROM t; ---- 1 1 3 a 1 1 1 3 c 1 1 1 4 b 0 2 2 2 a 1 2 2 2 a 1 2 2 2 c NaN 2 2 4 b 1 3 3 1 c NaN 4 3 1 a 1 query IITT SELECT dense_rank() OVER (PARTITION BY y ORDER BY x DESC, z), x, y, z FROM t ORDER BY y, x DESC, z; ---- 1 3 a 1 2 2 a 1 2 2 a 1 3 1 a 1 1 4 b 0 2 4 b 1 1 3 c 1 2 2 c NaN 3 1 c NaN # NaNs have the same rank query IIITT WITH t (x, y, z) AS (VALUES (1, 'a', 1.0), (2, 'a', 1.0), (2, 'a', 1.0), (3, 'a', 1.0), (4, 'b', 0), (4, 'b', 1), (1, 'c', 'NaN'), (2, 'c', 'NaN'), (3, 'c', 1.0)) SELECT rank() OVER (PARTITION BY y ORDER BY z DESC), dense_rank() OVER (PARTITION BY y ORDER BY z DESC), x, y, z FROM t; ---- 1 1 1 a 1 1 1 1 c NaN 1 1 2 a 1 1 1 2 a 1 1 1 2 c NaN 1 1 3 a 1 1 1 4 b 1 2 2 4 b 0 3 2 3 c 1 query IITT SELECT dense_rank() OVER (PARTITION BY y ORDER BY z DESC), x, y, z FROM t ORDER BY y, z DESC, x; ---- 1 1 a 1 1 2 a 1 1 2 a 1 1 3 a 1 1 4 b 1 2 4 b 0 1 1 c NaN 1 2 c NaN 2 3 c 1 ## lag statement ok DROP TABLE t; statement ok CREATE TABLE t(x string); statement ok INSERT INTO t VALUES ('a'), ('b'), ('c'); # Simple cases query TT SELECT lag(x) OVER (ORDER BY x), x FROM t ORDER BY x, lag ---- NULL a a b b c statement ok INSERT INTO t VALUES ('b'); query TT SELECT lag(x) OVER (ORDER BY x), x FROM t ORDER BY x, lag ---- NULL a a b b b b c statement ok INSERT INTO t VALUES ('c'); query TT SELECT lag(x) OVER (ORDER BY x), x FROM t ORDER BY x, lag ---- NULL a a b b b b c c c statement ok DROP TABLE t; statement ok CREATE TABLE t(x string); statement ok INSERT INTO t VALUES ('a'), ('b'), ('c'); query TT SELECT lag(x) OVER (ORDER BY x DESC), x FROM t ORDER BY x, lag ---- b a c b NULL c statement ok INSERT INTO t VALUES ('b'); query TT SELECT lag(x) OVER (ORDER BY x DESC), x FROM t ORDER BY x, lag ---- b a b b c b NULL c statement ok INSERT INTO t VALUES ('c'); query TT SELECT lag(x) OVER (ORDER BY x DESC), x FROM t ORDER BY x, lag ---- b a b b c b c c NULL c statement ok DROP TABLE t; statement ok CREATE TABLE t(x string, y int); statement ok INSERT INTO t VALUES ('a', 98), ('b', 99), ('c', 98); query TT SELECT lag(x) OVER (PARTITION BY y ORDER BY x), x FROM t ORDER BY x, lag ---- NULL a NULL b a c statement ok INSERT INTO t VALUES ('a', 98), ('a', 99); query TT SELECT lag(x) OVER (PARTITION BY y ORDER BY x), x FROM t ORDER BY x, lag ---- a a NULL a NULL a a b a c statement ok DROP TABLE t; statement ok CREATE TABLE t(x string, y int); statement ok INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1); query TT SELECT lag(x) OVER (PARTITION BY y ORDER BY x DESC), x FROM t ORDER BY x, lag ---- c a NULL b NULL c query TT SELECT lag(x) OVER (PARTITION BY x ORDER BY x), x FROM t ORDER BY x, lag ---- NULL a NULL b NULL c query TT SELECT lag(a1.x) OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x FROM t AS a1, t AS a2 ORDER BY q DESC, a1.x DESC ---- NULL a c c c c b c b b b b a b a a a a statement ok DROP TABLE t; statement ok CREATE TABLE t(f1 int, f2 string, f3 numeric); statement ok INSERT INTO t VALUES (1, 'a', 1.0), (2, 'a', 1.0), (2, 'a', 1.0), (3, 'a', 1.0), (4, 'b', 0), (4, 'b', 1), (1, 'c', 'NaN'), (2, 'c', 'NaN'), (3, 'c', 1.0), (7, 'd', -5.0); query ITTT SELECT f1, f2, f3, lag(f1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN 1 3 c 1 2 4 b 1 NULL 4 b 0 4 1 a 1 NULL 2 a 1 1 2 a 1 2 3 a 1 2 query ITTT SELECT f1, f2, f3, lag(f1, 1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN 1 3 c 1 2 4 b 1 NULL 4 b 0 4 1 a 1 NULL 2 a 1 1 2 a 1 2 3 a 1 2 query ITTT SELECT f1, f2, f3, lag(f1, 1, NULL) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN 1 3 c 1 2 4 b 1 NULL 4 b 0 4 1 a 1 NULL 2 a 1 1 2 a 1 2 3 a 1 2 # With default value query ITTT SELECT f1, f2, f3, lag(f1, 1, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 -1 1 c NaN -1 2 c NaN 1 3 c 1 2 4 b 1 -1 4 b 0 4 1 a 1 -1 2 a 1 1 2 a 1 2 3 a 1 2 # Complex expressions query ITTT SELECT f1, f2, f3, lag(f1 + coalesce(nullif(f3, 'NaN'), -10)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN -9 3 c 1 -8 4 b 1 NULL 4 b 0 5 1 a 1 NULL 2 a 1 2 2 a 1 3 3 a 1 3 # Nulls in the first argument query ITTT SELECT f1, f2, f3, lag(NULL::int) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lag(nullif(f1, 4)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN 1 3 c 1 2 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 1 2 a 1 2 3 a 1 2 # Nulls in the first argument with a default value in the third argument query ITTT SELECT f1, f2, f3, lag(NULL::int, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL # Zero offset query ITTT SELECT f1, f2, f3, lag(f1, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 7 1 c NaN 1 2 c NaN 2 3 c 1 3 4 b 1 4 4 b 0 4 1 a 1 1 2 a 1 2 2 a 1 2 3 a 1 3 query ITTT SELECT f1, f2, f3, lag(f1 + f3, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 2 1 c NaN NaN 2 c NaN NaN 3 c 1 4 4 b 1 5 4 b 0 4 1 a 1 2 2 a 1 3 2 a 1 3 3 a 1 4 # Positive offsets query ITTT SELECT f1, f2, f3, lag(f1, 2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 1 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 1 2 a 1 NULL 3 a 1 2 query ITTT SELECT f1, f2, f3, lag(f1 + f3, 2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NaN 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 2 2 a 1 NULL 3 a 1 3 # Out of range positive offsets query ITTT SELECT f1, f2, f3, lag(f1, 10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lag(f1 + f3, 10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lag(f1 + f3, 10, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 0 1 c NaN 0 2 c NaN 0 3 c 1 0 4 b 1 0 4 b 0 0 1 a 1 0 2 a 1 0 2 a 1 0 3 a 1 0 # Negative offsets query ITTT SELECT f1, f2, f3, lag(f1, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN 2 2 c NaN 3 3 c 1 NULL 4 b 1 4 4 b 0 NULL 1 a 1 2 2 a 1 2 2 a 1 3 3 a 1 NULL query ITTT SELECT f1, f2, f3, lag(f1 + f3, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NaN 2 c NaN 4 3 c 1 NULL 4 b 1 4 4 b 0 NULL 1 a 1 3 2 a 1 3 2 a 1 4 3 a 1 NULL query ITTT SELECT f1, f2, f3, lag(f1 + f3, -2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN 4 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 3 2 a 1 4 2 a 1 NULL 3 a 1 NULL # Out of range negative offsets query ITTT SELECT f1, f2, f3, lag(f1, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lag(f1 + f3, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lag(f1, -10, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 0 1 c NaN 0 2 c NaN 0 3 c 1 0 4 b 1 0 4 b 0 0 1 a 1 0 2 a 1 0 2 a 1 0 3 a 1 0 # Variable per row offsets query ITTT SELECT f1, f2, f3, lag(f1, f1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 1 2 a 1 NULL 3 a 1 1 query ITTT SELECT f1, f2, f3, lag(f1, f1 - 1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN 1 2 c NaN 1 3 c 1 1 4 b 1 NULL 4 b 0 NULL 1 a 1 1 2 a 1 1 2 a 1 2 3 a 1 2 # Null offsets query ITTT SELECT f1, f2, f3, lag(f1, NULL) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lag(f1, nullif(f1, 1)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 1 2 a 1 NULL 3 a 1 1 # Null offset with default value query ITTT SELECT f1, f2, f3, lag(f1, NULL, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL # Variable per row default value query ITTT SELECT f1, f2, f3, lag(f1, 1, f3) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lag ---- 7 d -5 -5 1 c NaN NaN 2 c NaN 1 3 c 1 2 4 b 1 1 4 b 0 4 1 a 1 1 2 a 1 1 2 a 1 2 3 a 1 2 statement ok DROP TABLE t; statement ok CREATE TABLE t(f1 int, f2 int); statement ok INSERT INTO t VALUES (1, 2), (1, 2), (3, 4); # reduce_elision code path # Default offset query II SELECT f1, lag(f1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 NULL 3 NULL query II SELECT f1, lag(f1, 1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 NULL 3 NULL # Zero offset query II SELECT f1, lag(f1, 0) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 1 3 3 # Negative offset query II SELECT f1, lag(f1, -1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 NULL 3 NULL # Default value with offset 1 query II SELECT f1, lag(f1, 1, 10) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 10 3 10 # Default value with offset 0 query II SELECT f1, lag(f1, 0, 10) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 1 3 3 # Complex expression query II SELECT f1, lag(f1 * f2, 0, 10) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 2 3 12 query II SELECT f1, lag(f1 * f2, 1, f1 * f2 + 1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 3 3 13 # Complex offset query II SELECT f1, lag(f1 * f2, f1 - f1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 2 3 12 query II rowsort SELECT f1, lag(f1 * f2, f1 - 1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 3 NULL 1 2 query II SELECT f1, lag(f1 * f2, f2 - 2 * f1, f2) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 2 3 4 # Complex default value query II SELECT f1, lag(f1, 0, f1 * f2) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 1 3 3 query II SELECT f1, lag(f1, 1, f1 * f2) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ---- 1 2 3 12 # Null value in input relation # This was caused by a bug in the reduce elision logic statement ok CREATE TABLE t3 (f1 INTEGER) ---- statement ok INSERT INTO t3 VALUES (NULL) ---- query II SELECT f1, lag(0, f1 , 0) OVER (PARTITION BY f1 ORDER BY f1) FROM t3 GROUP BY f1 ORDER BY 1 ---- NULL NULL ## lead statement ok DROP TABLE t; statement ok CREATE TABLE t(x string); statement ok INSERT INTO t VALUES ('a'), ('b'), ('c'); # Simple cases query TT SELECT lead(x) OVER (ORDER BY x), x FROM t ORDER BY x, lead ---- b a c b NULL c statement ok INSERT INTO t VALUES ('b'); query TT SELECT lead(x) OVER (ORDER BY x), x FROM t ORDER BY x, lead ---- b a b b c b NULL c statement ok INSERT INTO t VALUES ('c'); query TT SELECT lead(x) OVER (ORDER BY x), x FROM t ORDER BY x, lead ---- b a b b c b c c NULL c statement ok DROP TABLE t; statement ok CREATE TABLE t(x string); statement ok INSERT INTO t VALUES ('a'), ('b'), ('c'); query TT SELECT lead(x) OVER (ORDER BY x DESC), x FROM t ORDER BY x, lead ---- NULL a a b b c statement ok INSERT INTO t VALUES ('b'); query TT SELECT lead(x) OVER (ORDER BY x DESC), x FROM t ORDER BY x, lead ---- NULL a a b b b b c statement ok INSERT INTO t VALUES ('c'); query TT SELECT lead(x) OVER (ORDER BY x DESC), x FROM t ORDER BY x, lead ---- NULL a a b b b b c c c statement ok DROP TABLE t; statement ok CREATE TABLE t(x string, y int); statement ok INSERT INTO t VALUES ('a', 98), ('b', 99), ('c', 98); query TT SELECT lead(x) OVER (PARTITION BY y ORDER BY x), x FROM t ORDER BY x, lead ---- c a NULL b NULL c statement ok INSERT INTO t VALUES ('a', 98), ('a', 99); query TT SELECT lead(x) OVER (PARTITION BY y ORDER BY x), x FROM t ORDER BY x, lead ---- a a b a c a NULL b NULL c statement ok DROP TABLE t; statement ok CREATE TABLE t(x string, y int); statement ok INSERT INTO t VALUES ('a', 1), ('b', 2), ('c', 1); query TT SELECT lead(x) OVER (PARTITION BY y ORDER BY x DESC), x FROM t ORDER BY x, lead ---- NULL a NULL b a c query TT SELECT lead(x) OVER (PARTITION BY x ORDER BY x), x FROM t ORDER BY x, lead ---- NULL a NULL b NULL c query TT SELECT lead(a1.x) OVER (PARTITION BY NULL ORDER BY 10000) AS q, a1.x FROM t AS a1, t AS a2 ORDER BY q DESC, a1.x DESC ---- NULL c c c c c c b b b b b b a a a a a statement ok DROP TABLE t; statement ok CREATE TABLE t(f1 int, f2 string, f3 numeric); statement ok INSERT INTO t VALUES (1, 'a', 1.0), (2, 'a', 1.0), (2, 'a', 1.0), (3, 'a', 1.0), (4, 'b', 0), (4, 'b', 1), (1, 'c', 'NaN'), (2, 'c', 'NaN'), (3, 'c', 1.0), (7, 'd', -5.0); query ITTT SELECT f1, f2, f3, lead(f1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN 2 2 c NaN 3 3 c 1 NULL 4 b 1 4 4 b 0 NULL 1 a 1 2 2 a 1 2 2 a 1 3 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(f1, 1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN 2 2 c NaN 3 3 c 1 NULL 4 b 1 4 4 b 0 NULL 1 a 1 2 2 a 1 2 2 a 1 3 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(f1, 1, NULL) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN 2 2 c NaN 3 3 c 1 NULL 4 b 1 4 4 b 0 NULL 1 a 1 2 2 a 1 2 2 a 1 3 3 a 1 NULL # With default value query ITTT SELECT f1, f2, f3, lead(f1, 1, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 -1 1 c NaN 2 2 c NaN 3 3 c 1 -1 4 b 1 4 4 b 0 -1 1 a 1 2 2 a 1 2 2 a 1 3 3 a 1 -1 # Complex expressions query ITTT SELECT f1, f2, f3, lead(f1 + coalesce(nullif(f3, 'NaN'), -10)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN -8 2 c NaN 4 3 c 1 NULL 4 b 1 4 4 b 0 NULL 1 a 1 3 2 a 1 3 2 a 1 4 3 a 1 NULL # Nulls in the first argument query ITTT SELECT f1, f2, f3, lead(NULL::int) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(nullif(f1, 4)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN 2 2 c NaN 3 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 2 2 a 1 2 2 a 1 3 3 a 1 NULL # Nulls in the first argument with a default value in the third argument query ITTT SELECT f1, f2, f3, lead(NULL::int, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL # Zero offset query ITTT SELECT f1, f2, f3, lead(f1, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 7 1 c NaN 1 2 c NaN 2 3 c 1 3 4 b 1 4 4 b 0 4 1 a 1 1 2 a 1 2 2 a 1 2 3 a 1 3 query ITTT SELECT f1, f2, f3, lead(f1 + f3, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 2 1 c NaN NaN 2 c NaN NaN 3 c 1 4 4 b 1 5 4 b 0 4 1 a 1 2 2 a 1 3 2 a 1 3 3 a 1 4 # Positive offsets query ITTT SELECT f1, f2, f3, lead(f1, 2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN 3 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 2 2 a 1 3 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(f1 + f3, 2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN 4 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 3 2 a 1 4 2 a 1 NULL 3 a 1 NULL # Out of range positive offsets query ITTT SELECT f1, f2, f3, lead(f1, 10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(f1 + f3, 10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(f1 + f3, 10, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 0 1 c NaN 0 2 c NaN 0 3 c 1 0 4 b 1 0 4 b 0 0 1 a 1 0 2 a 1 0 2 a 1 0 3 a 1 0 # Negative offsets query ITTT SELECT f1, f2, f3, lead(f1, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN 1 3 c 1 2 4 b 1 NULL 4 b 0 4 1 a 1 NULL 2 a 1 1 2 a 1 2 3 a 1 2 query ITTT SELECT f1, f2, f3, lead(f1 + f3, -1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NaN 3 c 1 NaN 4 b 1 NULL 4 b 0 5 1 a 1 NULL 2 a 1 2 2 a 1 3 3 a 1 3 query ITTT SELECT f1, f2, f3, lead(f1 + f3, -2) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NaN 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 2 2 a 1 NULL 3 a 1 3 # Out of range negative offsets query ITTT SELECT f1, f2, f3, lead(f1, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(f1 + f3, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(f1, -10, 0) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 0 1 c NaN 0 2 c NaN 0 3 c 1 0 4 b 1 0 4 b 0 0 1 a 1 0 2 a 1 0 2 a 1 0 3 a 1 0 # Variable per row offsets query ITTT SELECT f1, f2, f3, lead(f1, f1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN 2 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 2 2 a 1 3 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(f1, f1 - 1) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN 1 2 c NaN 3 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 1 2 a 1 2 2 a 1 3 3 a 1 NULL # Null offsets query ITTT SELECT f1, f2, f3, lead(f1, NULL) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL query ITTT SELECT f1, f2, f3, lead(f1, nullif(f1, 1)) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 3 2 a 1 NULL 3 a 1 NULL # Null offset with default value query ITTT SELECT f1, f2, f3, lead(f1, NULL, -10) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 NULL 1 c NaN NULL 2 c NaN NULL 3 c 1 NULL 4 b 1 NULL 4 b 0 NULL 1 a 1 NULL 2 a 1 NULL 2 a 1 NULL 3 a 1 NULL # Variable per row default value query ITTT SELECT f1, f2, f3, lead(f1, 1, f3) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1) FROM t ORDER BY f2 DESC, f3 DESC, f1, lead ---- 7 d -5 -5 1 c NaN 2 2 c NaN 3 3 c 1 1 4 b 1 4 4 b 0 0 1 a 1 2 2 a 1 2 2 a 1 3 3 a 1 1 statement ok DROP TABLE t; statement ok CREATE TABLE t(f1 int, f2 int); statement ok INSERT INTO t VALUES (1, 2), (1, 2), (3, 4); # reduce_elision code path # Default offset query II SELECT f1, lead(f1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 NULL 3 NULL query II SELECT f1, lead(f1, 1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 NULL 3 NULL # Zero offset query II SELECT f1, lead(f1, 0) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 1 3 3 # Negative offset query II SELECT f1, lead(f1, -1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 NULL 3 NULL # Default value with offset 1 query II SELECT f1, lead(f1, 1, 10) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 10 3 10 # Default value with offset 0 query II SELECT f1, lead(f1, 0, 10) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 1 3 3 # Complex expression query II SELECT f1, lead(f1 * f2, 0, 10) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 2 3 12 query II SELECT f1, lead(f1 * f2, 1, f1 * f2 + 1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 3 3 13 # Complex offset query II SELECT f1, lead(f1 * f2, f1 - f1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 2 3 12 query II SELECT f1, lead(f1 * f2, f1 - 1) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 2 3 NULL query II SELECT f1, lead(f1 * f2, f2 - 2 * f1, f2) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 2 3 4 # Complex default value query II SELECT f1, lead(f1, 0, f1 * f2) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 1 3 3 query II SELECT f1, lead(f1, 1, f1 * f2) OVER (PARTITION BY f1, f2) FROM (SELECT DISTINCT f1, f2 FROM t) q ORDER BY 1, 2 ---- 1 2 3 12 # Null value in input relation # This was caused by a bug in the reduce elision logic statement ok CREATE TABLE t4 (f1 INTEGER) ---- statement ok INSERT INTO t4 VALUES (NULL) ---- query II SELECT f1, lead(0, f1 , 0) OVER (PARTITION BY f1 ORDER BY f1) FROM t4 GROUP BY f1 ORDER BY 1 ---- NULL NULL ## lag/lead ignore nulls statement ok create table t6(x int, y int); statement ok insert into t6 values (1,2), (3,null), (5,6), (7,8), (9, null), (11, null), (13, 14), (15, 16), (17, 18); query IIIIIIIIIIIIIIIIIIII select x, y, lag(y) over (order by x) as lag1, lag(y) respect nulls over (order by x) as lag1_resp, lag(y) ignore nulls over (order by x) as lag1_ign, lead(y) over (order by x) as lead1, lead(y) ignore nulls over (order by x) as lead1_ign, lag(y, 2) over (order by x) as lag2, lag(y, 2) ignore nulls over (order by x) as lag2_ign, lead(y, 2) over (order by x) as lead2, lead(y, 2) ignore nulls over (order by x) as lead2_ign, lag(y, 2, -1) ignore nulls over (order by x) as lag2_ign_def, lead(y, 2, -1) ignore nulls over (order by x) as lead2_ign_def, lag(y, 2, 16) ignore nulls over (order by x) as lag2_ign_def16, lead(y, 2, 16) ignore nulls over (order by x) as lead2_ign_def16, lag(y, -1, 100) ignore nulls over (order by x) as lag_neg_offset, lead(y, -2, 100) ignore nulls over (order by x) as lead_neg_offset, lag(y, y/5+1) ignore nulls over (order by x) as lag_dynamic_offset, lead(y, y/5+1) ignore nulls over (order by x) as lead_dynamic_offset, lag(y, null) ignore nulls over (order by x) as lag_literal_null_offset from t6 order by x; ---- 1 2 NULL NULL NULL NULL 6 NULL NULL 6 8 -1 8 16 8 6 100 NULL 6 NULL 3 NULL 2 2 2 6 6 NULL NULL 8 8 -1 8 16 8 6 100 NULL NULL NULL 5 6 NULL NULL 2 8 8 2 NULL NULL 14 -1 14 16 14 8 100 NULL 14 NULL 7 8 6 6 6 NULL 14 NULL 2 NULL 16 2 16 2 16 14 2 2 16 NULL 9 NULL 8 8 8 NULL 14 6 6 14 16 6 16 6 16 14 6 NULL NULL NULL 11 NULL NULL NULL 8 14 14 8 6 16 16 6 16 6 16 14 6 NULL NULL NULL 13 14 NULL NULL 8 16 16 NULL 6 18 18 6 18 6 18 16 6 2 NULL NULL 15 16 14 14 14 18 18 NULL 8 NULL NULL 8 -1 8 16 18 8 2 NULL NULL 17 18 16 16 16 NULL NULL 14 14 NULL NULL 14 -1 14 16 100 14 6 NULL NULL # Same as above, but with a `partition by` query IIIIIIIIIIIIIIIIIIII select x, y, lag(y) over (partition by x%4 order by x) as lag1, lag(y) respect nulls over (partition by x%4 order by x) as lag1_resp, lag(y) ignore nulls over (partition by x%4 order by x) as lag1_ign, lead(y) over (partition by x%4 order by x) as lead1, lead(y) ignore nulls over (partition by x%4 order by x) as lead1_ign, lag(y, 2) over (partition by x%4 order by x) as lag2, lag(y, 2) ignore nulls over (partition by x%4 order by x) as lag2_ign, lead(y, 2) over (partition by x%4 order by x) as lead2, lead(y, 2) ignore nulls over (partition by x%4 order by x) as lead2_ign, lag(y, 2, -1) ignore nulls over (partition by x%4 order by x) as lag2_ign_def, lead(y, 2, -1) ignore nulls over (partition by x%4 order by x) as lead2_ign_def, lag(y, 2, 16) ignore nulls over (partition by x%4 order by x) as lag2_ign_def16, lead(y, 2, 16) ignore nulls over (partition by x%4 order by x) as lead2_ign_def16, lag(y, -1, 100) ignore nulls over (partition by x%4 order by x) as lag_neg_offset, lead(y, -2, 100) ignore nulls over (partition by x%4 order by x) as lead_neg_offset, lag(y, y/5+1) ignore nulls over (partition by x%4 order by x) as lag_dynamic_offset, lead(y, y/5+1) ignore nulls over (partition by x%4 order by x) as lead_dynamic_offset, lag(y, null) ignore nulls over (partition by x%4 order by x) as lag_literal_null_offset from t6 order by x%4, x; ---- 1 2 NULL NULL NULL 6 6 NULL NULL NULL 14 -1 14 16 14 6 100 NULL 6 NULL 5 6 2 2 2 NULL 14 NULL NULL 14 18 -1 18 16 18 14 100 NULL 18 NULL 9 NULL 6 6 6 14 14 2 2 18 18 2 18 2 18 14 2 NULL NULL NULL 13 14 NULL NULL 6 18 18 6 2 NULL NULL 2 -1 2 16 18 2 NULL NULL NULL 17 18 14 14 14 NULL NULL NULL 6 NULL NULL 6 -1 6 16 100 6 NULL NULL NULL 3 NULL NULL NULL NULL 8 8 NULL NULL NULL 16 -1 16 16 16 8 100 NULL NULL NULL 7 8 NULL NULL NULL NULL 16 NULL NULL 16 NULL -1 -1 16 16 16 100 NULL NULL NULL 11 NULL 8 8 8 16 16 NULL NULL NULL NULL -1 -1 16 16 16 100 NULL NULL NULL 15 16 NULL NULL 8 NULL NULL 8 NULL NULL NULL -1 -1 16 16 100 100 NULL NULL NULL statement ok CREATE VIEW t6_more_nulls AS SELECT x, CASE WHEN y < 15 AND y > 7 THEN null ELSE y END AS y FROM t6; query IIIIIIII select x, y, lag(y) ignore nulls over (order by x) as lag1, lag(y,2) ignore nulls over (order by x) as lag2, lag(y,3) ignore nulls over (order by x) as lag3, lead(y) ignore nulls over (order by x) as lead1, lead(y,2) ignore nulls over (order by x) as lead2, lead(y,3) ignore nulls over (order by x) as lead3 from t6_more_nulls order by x; ---- 1 2 NULL NULL NULL 6 16 18 3 NULL 2 NULL NULL 6 16 18 5 6 2 NULL NULL 16 18 NULL 7 NULL 6 2 NULL 16 18 NULL 9 NULL 6 2 NULL 16 18 NULL 11 NULL 6 2 NULL 16 18 NULL 13 NULL 6 2 NULL 16 18 NULL 15 16 6 2 NULL 18 NULL NULL 17 18 16 6 2 NULL NULL NULL # Test the `offset = 0` code path. This currently doesn't work with nulls if IGNORE NULLS is specified, # so we just filter out nulls for now. # See https://materializeinc.slack.com/archives/C063H5S7NKE/p1724962369706729 # Also tests negative dynamic offsets. statement ok CREATE VIEW t6_no_nulls AS SELECT x, COALESCE(y,-11) AS y FROM t6; query IIIIIII SELECT x, y, y/10, lag(y, y/10) OVER (ORDER BY x), lag(y, y/10) IGNORE NULLS OVER (ORDER BY x), lead(y, y/10) OVER (ORDER BY x), lead(y, y/10) IGNORE NULLS OVER (ORDER BY x) FROM t6_no_nulls ORDER BY x; ---- 1 2 0 2 2 2 2 3 -11 -1 6 6 2 2 5 6 0 6 6 6 6 7 8 0 8 8 8 8 9 -11 -1 -11 -11 8 8 11 -11 -1 14 14 -11 -11 13 14 1 -11 -11 16 16 15 16 1 14 14 18 18 17 18 1 16 16 NULL NULL query error db error: ERROR: IGNORE NULLS and RESPECT NULLS options for functions other than LAG and LEAD not yet supported select first_value(x) ignore nulls over() from t6; query error db error: ERROR: IGNORE NULLS and RESPECT NULLS options for functions other than LAG and LEAD not yet supported select row_number() ignore nulls over(); query error db error: ERROR: Both IGNORE NULLS and RESPECT NULLS were given\. select row_number() ignore nulls respect nulls over(); ## window frames # Invalid frame start query error frame start cannot be UNBOUNDED FOLLOWING SELECT row_number() OVER (ROWS UNBOUNDED FOLLOWING) # Invalid frame end query error frame end cannot be UNBOUNDED PRECEDING SELECT row_number() OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING) # End frame can't be of a type that comes before the start bound query error frame starting from current row cannot have preceding rows SELECT row_number() OVER (ROWS BETWEEN CURRENT ROW AND 1 PRECEDING) query error frame starting from following row cannot have preceding rows SELECT row_number() OVER (ROWS BETWEEN 1 FOLLOWING AND 1 PRECEDING) query error frame starting from following row cannot have preceding rows SELECT row_number() OVER (ROWS BETWEEN 1 FOLLOWING AND CURRENT ROW) # But end offsets can come before start offsets query I SELECT row_number() OVER (ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING) ---- 1 query I SELECT row_number() OVER (ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING) ---- 1 # Negative offsets are not allowed # Our error message is different from Postgres, so it's not checked here query error SELECT row_number() OVER (ROWS -1 PRECEDING) query error SELECT row_number() OVER (ROWS -1 FOLLOWING) # Current implementation restrictions # RANGE is not supported outside of the default frame query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN 1 FOLLOWING AND 1 FOLLOWING) query error RANGE in non-default window frames not yet supported SELECT row_number() OVER (RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) # Default window frame works fine query I SELECT row_number() OVER () ---- 1 query I SELECT row_number() OVER (RANGE UNBOUNDED PRECEDING) ---- 1 query I SELECT row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ---- 1 # GROUPS is not supported at all query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN 1 PRECEDING AND 1 PRECEDING) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN CURRENT ROW AND CURRENT ROW) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) query error GROUPS in window frames not yet supported SELECT row_number() OVER (GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) ## first_value statement ok DROP TABLE t; statement ok CREATE TABLE t(f1 int, f2 string, f3 int); statement ok INSERT INTO t VALUES (1, 'a', 1), (2, 'a', 2), (2, 'a', 3), (3, 'a', 4), (4, 'b', 5), (4, 'b', 6), (1, 'c', 7), (2, 'c', 8), (3, 'c', 9), (7, 'd', 10); # Default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN UNBOUNDED PRECEDING AND x PRECEDING query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 NULL 4 b 6 4 1 c 7 NULL 2 c 8 1 3 c 9 1 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 1 3 a 4 1 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 1 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 10 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN UNBOUNDED PRECEDING AND x FOLLOWING query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN 0 PRECEDING AND x PRECEDING query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 2 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 10 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN x PRECEDING AND 0 PRECEDING query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN x PRECEDING AND y PRECEDING, where x < y query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN x PRECEDING AND y PRECEDING, where x > y query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 NULL 4 b 6 4 1 c 7 NULL 2 c 8 1 3 c 9 1 7 d 10 NULL # ROWS BETWEEN x PRECEDING AND y PRECEDING, where x == y query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 NULL 4 b 6 4 1 c 7 NULL 2 c 8 1 3 c 9 2 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 1 3 a 4 2 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 1 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND 10 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN x PRECEDING AND CURRENT ROW query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN x PRECEDING AND x FOLLOWING # Equivalent to ROWS BETWEEN x PRECEDING AND CURRENT ROW for first_value query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN x PRECEDING AND UNBOUNDED FOLLOWING # Equivalent to ROWS BETWEEN x PRECEDING AND CURRENT ROW for first_value query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN CURRENT ROW AND CURRENT ROW # Always returns current row query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 # ROWS BETWEEN CURRENT ROW AND x FOLLOWING # Always returns current row query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 # ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING # Always returns current row query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 # ROWS BETWEEN 0 FOLLOWING AND x FOLLOWING query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 10 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 # ROWS BETWEEN x FOLLOWING AND 0 FOLLOWING query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN x FOLLOWING AND y FOLLOWING, where x < y query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 2 2 a 2 2 2 a 3 3 3 a 4 NULL 4 b 5 4 4 b 6 NULL 1 c 7 2 2 c 8 3 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN x FOLLOWING AND y FOLLOWING, where x > y query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN x FOLLOWING AND y FOLLOWING, where x == y query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 2 2 a 2 2 2 a 3 3 3 a 4 NULL 4 b 5 4 4 b 6 NULL 1 c 7 2 2 c 8 3 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 2 2 a 2 3 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 3 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 FOLLOWING AND 10 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN x FOLLOWING AND UNBOUNDED FOLLOWING query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 2 2 a 2 2 2 a 3 3 3 a 4 NULL 4 b 5 4 4 b 6 NULL 1 c 7 2 2 c 8 3 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 2 2 a 2 3 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 3 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # Test offset limits query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 100 FOLLOWING AND 1000001 FOLLOWING) # Test near-overflow behavior on offsets # u64::MAX FOLLOWING query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 18446744073709551615 FOLLOWING AND 18446744073709551615 FOLLOWING) FROM t ORDER BY f2, f3, f1, first_value query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1000001 PRECEDING AND 100 FOLLOWING) query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 18446744073709551614 FOLLOWING AND 18446744073709551615 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value query error Expected literal unsigned integer, found operator "\-" SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND -1 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value # u64::MAX PRECEDING query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 18446744073709551615 PRECEDING AND 18446744073709551615 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 18446744073709551615 PRECEDING AND 18446744073709551614 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1000 PRECEDING AND 1000 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, first_value(f1) OVER (PARTITION BY f2 ORDER BY f1, f3 ROWS BETWEEN 1000 PRECEDING AND 999 PRECEDING) FROM t ORDER BY f2, f3, f1, first_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # reduce_elision code path statement ok CREATE TABLE t5 (f1 INTEGER) ---- statement ok INSERT INTO t5 VALUES (1) ---- # Default frame, includes current row query II SELECT f1, first_value(f1) OVER (PARTITION BY f1) FROM t5 GROUP BY f1 ---- 1 1 # Frame starting at UNBOUNDED PRECEDING query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 # Frame starting at 1 PRECEDING query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 # Frame starting at 0 PRECEDING query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND CURRENT ROW) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 # Frame starting at CURRENT ROW query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 # Frame starting at 0 FOLLOWING query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 # Frame starting at 1 FOLLOWING query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, first_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 NULL ## last_value # Default frame (RANGE BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW) query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 # Default frame with large peer group # Note: there are multiple valid results of this query (because the default frame is up through the current row's last # ORDER BY peer, and the ordering among ORDER BY peers is unspecified), but we make the result stable by internally # always adding all remaining columns into our orderings. query ITII SELECT f1, f2, f3, last_value(f3) OVER (PARTITION BY f2 ORDER BY f1) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 3 2 a 3 3 3 a 4 4 4 b 5 6 4 b 6 6 1 c 7 7 2 c 8 8 3 c 9 9 7 d 10 10 # ROWS BETWEEN x FOLLOWING AND UNBOUNDED FOLLOWING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 NULL 4 b 6 4 1 c 7 NULL 2 c 8 1 3 c 9 1 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 1 3 a 4 1 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 1 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN x PRECEDING AND UNBOUNDED FOLLOWING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN x FOLLOWING AND 0 FOLLOWING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN 0 FOLLOWING AND x FOLLOWING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 10 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN y FOLLOWING AND x FOLLOWING, where x < y query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN y FOLLOWING AND x FOLLOWING, where x > y query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 NULL 4 b 6 4 1 c 7 NULL 2 c 8 1 3 c 9 1 7 d 10 NULL # ROWS BETWEEN x FOLLOWING AND y FOLLOWING, where x == y query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 NULL 4 b 6 4 1 c 7 NULL 2 c 8 1 3 c 9 2 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 1 3 a 4 2 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 1 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 FOLLOWING AND 10 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN CURRENT ROW AND x FOLLOWING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN x PRECEDING AND x FOLLOWING # Equivalent to ROWS BETWEEN CURRENT ROW AND x FOLLOWING for last_value query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 10 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN UNBOUNDED PRECEDING AND x FOLLOWING # Equivalent to ROWS BETWEEN CURRENT ROW AND x FOLLOWING for last_value query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 2 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 2 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 2 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 1 2 a 3 1 3 a 4 1 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 1 3 c 9 1 7 d 10 7 # ROWS BETWEEN CURRENT ROW AND CURRENT ROW # Always returns current row query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 # ROWS BETWEEN x PRECEDING AND CURRENT ROW # Always returns current row query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 # ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW # Always returns current row query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 # ROWS BETWEEN x PRECEDING AND 0 PRECEDING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 # ROWS BETWEEN 0 PRECEDING AND x PRECEDING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 2 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 10 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN y PRECEDING AND x PRECEDING, where x < y query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 2 2 a 2 2 2 a 3 3 3 a 4 NULL 4 b 5 4 4 b 6 NULL 1 c 7 2 2 c 8 3 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN y PRECEDING AND x PRECEDING, where x > y query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN y PRECEDING AND x PRECEDING, where x == y query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 2 2 a 2 2 2 a 3 3 3 a 4 NULL 4 b 5 4 4 b 6 NULL 1 c 7 2 2 c 8 3 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 2 2 a 2 3 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 3 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 10 PRECEDING AND 10 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # ROWS BETWEEN UNBOUNDED PRECEDING AND x PRECEDING query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 1 2 a 2 2 2 a 3 2 3 a 4 3 4 b 5 4 4 b 6 4 1 c 7 1 2 c 8 2 3 c 9 3 7 d 10 7 query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 2 2 a 2 2 2 a 3 3 3 a 4 NULL 4 b 5 4 4 b 6 NULL 1 c 7 2 2 c 8 3 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 2 2 a 2 3 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 3 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 10 PRECEDING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # Test near-overflow behavior on offsets # u64::MAX FOLLOWING # u64::MAX PRECEDING query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 18446744073709551615 FOLLOWING AND 18446744073709551615 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value query error db error: ERROR: Window frame offsets greater than 1000000 are currently not supported SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 18446744073709551614 FOLLOWING AND 18446744073709551615 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1000 FOLLOWING AND 1000 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL query ITII SELECT f1, f2, f3, last_value(f1) OVER (PARTITION BY f2 ORDER BY f1 DESC, f3 DESC ROWS BETWEEN 1000 FOLLOWING AND 1001 FOLLOWING) FROM t ORDER BY f2, f3, f1, last_value ---- 1 a 1 NULL 2 a 2 NULL 2 a 3 NULL 3 a 4 NULL 4 b 5 NULL 4 b 6 NULL 1 c 7 NULL 2 c 8 NULL 3 c 9 NULL 7 d 10 NULL # reduce_elision code path # Using the same table as first_value # Default frame, includes current row query II SELECT f1, last_value(f1) OVER (PARTITION BY f1) FROM t5 GROUP BY f1 ---- 1 1 # Frame ending at UNBOUNDED FOLLOWING query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 # Frame ending at 1 FOLLOWING query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 # Frame ending at 0 FOLLOWING query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 FOLLOWING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) FROM t5 GROUP BY f1 ---- 1 1 # Frame ending at CURRENT ROW query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND CURRENT ROW) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t5 GROUP BY f1 ---- 1 1 # Frame ending at 0 PRECEDING query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING) FROM t5 GROUP BY f1 ---- 1 1 query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) FROM t5 GROUP BY f1 ---- 1 1 # Frame ending at 1 PRECEDING query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) FROM t5 GROUP BY f1 ---- 1 NULL query II SELECT f1, last_value(f1) OVER (PARTITION BY f1 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) FROM t5 GROUP BY f1 ---- 1 NULL # Check some HIR plans to verify that the lifting of window functions to the top of Maps is actually happening. statement ok CREATE TABLE foo ( a int, b text ); statement ok INSERT INTO foo (a, b) VALUES (0, 'zero'), (1, 'one'), (2, 'two'), (3, 'three'); query T multiline EXPLAIN RAW PLAN FOR SELECT 3 + lag(a) OVER (ORDER BY a) + 5 + 27 FROM foo; ---- Project (#3) Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (((3 + #2{?column?}) + 5) + 27)) Get materialize.public.foo Target cluster: quickstart EOF query T multiline EXPLAIN RAW PLAN FOR SELECT -lag(10*right_a+3) OVER (ORDER BY right_a NULLS FIRST), -sum(foo.a), b FROM foo LEFT JOIN (SELECT a AS right_a FROM foo WHERE a<2) ON foo.a = right_a GROUP BY b, right_a; ---- Project (#4, #5, #0) Map (lag(row(((10 * #1{right_a}) + 3), 1, null)) over (order by [#1{right_a} asc nulls_first]), -(#3{?column?}), -(#2{?column?})) Reduce group_by=[#3, #4] aggregates=[sum(#0{a})] Map (#1{b}, #2{right_a}) LeftOuterJoin (#0{a} = #2{right_a}) Get materialize.public.foo Project (#0) Filter (#0{a} < 2) Get materialize.public.foo Target cluster: quickstart EOF query T multiline EXPLAIN RAW PLAN FOR SELECT 3 + lag(a) OVER (ORDER BY a) AS o FROM foo ORDER BY o; ---- Finish order_by=[#0 asc nulls_last] output=[#0] Project (#3) Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (3 + #2{?column?})) Get materialize.public.foo Target cluster: quickstart EOF query T multiline EXPLAIN RAW PLAN FOR SELECT a, 3 + lag(a) OVER (ORDER BY a), 3 + lag(a) OVER (ORDER BY a), lag(a) OVER (ORDER BY a), lead(a) OVER (ORDER BY a), 5 + lag(a) OVER (ORDER BY a) FROM foo ORDER BY a; ---- Finish order_by=[#0 asc nulls_last] output=[#0..=#5] Project (#0, #4, #4, #2, #3, #5) Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), lead(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (3 + #2{?column?}), (5 + #2{?column?})) Get materialize.public.foo Target cluster: quickstart EOF query IIIIII nosort SELECT a, 3 + lag(a) OVER (ORDER BY a), 3 + lag(a) OVER (ORDER BY a), lag(a) OVER (ORDER BY a), lead(a) OVER (ORDER BY a), 5 + lag(a) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 NULL NULL NULL 1 NULL 1 3 3 0 2 5 2 4 4 1 3 6 3 5 5 2 NULL 7 # Two window function calls in the same HirScalarExpr, but both calls are the same query T multiline EXPLAIN RAW PLAN FOR SELECT a, b, lag(a) OVER (ORDER BY a) + lag(a) OVER (ORDER BY a) FROM foo ORDER BY a; ---- Finish order_by=[#0 asc nulls_last] output=[#0..=#2] Project (#0, #1, #3) Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (#2{?column?} + #2{?column?})) Get materialize.public.foo Target cluster: quickstart EOF query ITI nosort SELECT a, b, lag(a) OVER (ORDER BY a) + lag(a) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 zero NULL 1 one 0 2 two 2 3 three 4 # Two window function calls in the same HirScalarExpr, and they are different query T multiline EXPLAIN RAW PLAN FOR SELECT a, b, lag(a) OVER (ORDER BY a) + lead(a) OVER (ORDER BY a) FROM foo ORDER BY a; ---- Finish order_by=[#0 asc nulls_last] output=[#0..=#2] Project (#0, #1, #4) Map (lead(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), (#3{?column?} + #2{?column?})) Get materialize.public.foo Target cluster: quickstart EOF query ITI nosort SELECT a, b, lag(a) OVER (ORDER BY a) + lead(a) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 zero NULL 1 one 2 2 two 4 3 three NULL # Window function in DISTINCT ON query T multiline EXPLAIN RAW PLAN FOR SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) * FROM foo; ---- Project (#0, #1) TopK group_by=[#3] limit=1 Map (lag(row(#0{a}, 1, 0)) over (order by [#0{a} asc nulls_last]), (1 + #2{?column?})) Get materialize.public.foo Target cluster: quickstart EOF query IIT SELECT 1 + lag(a, 1, 0) OVER (ORDER BY a), * FROM foo ORDER BY a; ---- 1 0 zero 1 1 one 2 2 two 3 3 three query IT SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) * FROM foo ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a); ---- 0 zero 2 two 3 three query IIT nosort SELECT 1 + lag(a, 1, 0) OVER (ORDER BY a), * FROM foo ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a), a ASC; ---- 1 0 zero 1 1 one 2 2 two 3 3 three query IT nosort SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) * FROM foo ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a), a ASC; ---- 0 zero 2 two 3 three query error db error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) * FROM foo ORDER BY lag(a, 1, 0) OVER (ORDER BY a), a DESC; query IIIT nosort SELECT 1 + lag(a, 1, 0) OVER (ORDER BY a), lead(a) OVER (ORDER BY b) AS o, a, b FROM foo ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a), o DESC; ---- 1 NULL 0 zero 1 3 1 one 2 0 2 two 3 2 3 three query IIT nosort SELECT DISTINCT ON(1 + lag(a, 1, 0) OVER (ORDER BY a)) lead(a) OVER (ORDER BY b) AS o, a, b FROM foo ORDER BY 1 + lag(a, 1, 0) OVER (ORDER BY a), o DESC; ---- NULL 0 zero 0 2 two 2 3 three query T multiline EXPLAIN RAW PLAN FOR SELECT DISTINCT ON(5 + lag(a) OVER (ORDER BY a)) lead(a) OVER (ORDER BY b) AS o FROM foo ORDER BY 5 + lag(a) OVER (ORDER BY a), o; ---- Finish order_by=[#4 asc nulls_last, #3 asc nulls_last] output=[#3] TopK group_by=[#4] order_by=[#3 asc nulls_last] limit=1 Map (lag(row(#0{a}, 1, null)) over (order by [#0{a} asc nulls_last]), lead(row(#0{a}, 1, null)) over (order by [#1{b} asc nulls_last]), (5 + #2{?column?})) Get materialize.public.foo Target cluster: quickstart EOF # Nested window function call in the argument of a window function call (Postgres doesn't allow this, but we do) query ITI nosort SELECT a, b, lag(lag(a) OVER (ORDER BY a)) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 zero NULL 1 one NULL 2 two 0 3 three 1 query ITI nosort SELECT a, b, lag(lag(a) OVER (PARTITION BY length(b) ORDER BY a)) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 zero NULL 1 one NULL 2 two NULL 3 three 1 # The inner window function call also appears outside query ITII nosort SELECT a, b, lag(lag(a) OVER (ORDER BY a)) OVER (ORDER BY a), lag(a) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 zero NULL NULL 1 one NULL 0 2 two 0 1 3 three 1 2 query ITII nosort SELECT a, b, lag(a) OVER (ORDER BY a), lag(lag(a) OVER (ORDER BY a)) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 zero NULL NULL 1 one 0 NULL 2 two 1 0 3 three 2 1 # Nested window function call in the PARTITION BY of a window function call (Postgres doesn't allow this, but we do) query ITII nosort SELECT a, b, lag(a) OVER (PARTITION BY lag(length(b)) OVER (ORDER BY a) ORDER BY a), lag(length(b)) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 zero NULL NULL 1 one NULL 4 2 two NULL 3 3 three 2 3 query ITI nosort SELECT a, b, lag(a) OVER (PARTITION BY lag(length(b)) OVER (ORDER BY a) ORDER BY a) FROM foo ORDER BY a; ---- 0 zero NULL 1 one NULL 2 two NULL 3 three 2 query ITII nosort SELECT a, b, row_number() OVER (PARTITION BY lag(length(b)) OVER (ORDER BY a) ORDER BY a), lag(length(b)) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 zero 1 NULL 1 one 1 4 2 two 1 3 3 three 2 3 # Nested window function calls in both the argument and the PARTITION BY of a window function call (Postgres doesn't # allow this, but we do) query ITI nosort SELECT a, b, lead(lag(a) OVER (PARTITION BY lag(length(b)) OVER (ORDER BY a) ORDER BY a), 1, -5) OVER (ORDER BY a) FROM foo ORDER BY a; ---- 0 zero NULL 1 one NULL 2 two 2 3 three -5 # Nested window function calls in the ORDER BY of a window function call (Postgres doesn't allow this, but we do) query ITII nosort SELECT a, b, lag(a) OVER (ORDER BY lag(a) OVER (ORDER BY a)), lag(a) OVER (ORDER BY a) FROM foo ORDER BY lag(a) OVER (ORDER BY a); ---- 1 one NULL 0 2 two 1 1 3 three 2 2 0 zero 3 NULL query ITI SELECT a, b, lag(a) OVER (ORDER BY lag(a) OVER (ORDER BY a)) FROM foo ORDER BY a; ---- 0 zero 3 1 one NULL 2 two 1 3 three 2 # Window function inside CASE WHEN # Regression test for https://github.com/MaterializeInc/database-issues/issues/6250 statement ok CREATE TABLE bools(cond bool, x int); statement ok INSERT INTO bools VALUES (true, 0), (false, 1), (false, 2); # The window function call should be lifted outside of the If query T multiline EXPLAIN RAW PLAN FOR SELECT cond, x, CASE WHEN cond THEN first_value(x) OVER (ORDER BY x DESC) ELSE 42 END FROM bools; ---- Project (#0, #1, #3) Map (first_value(#1{x}) over (order by [#1{x} desc nulls_first]), case when #0{cond} then #2{?column?} else 42 end) Get materialize.public.bools Target cluster: quickstart EOF query TII SELECT cond, x, CASE WHEN cond THEN first_value(x) OVER (ORDER BY x DESC) ELSE 42 END FROM bools ORDER BY x; ---- true 0 2 false 1 42 false 2 42 statement ok CREATE TABLE tcm( chat_id int, chat_message_id int, message_tstamp date, xxx_id int, first_xxx_id int, user_id int, boolean_flag bool ); statement ok INSERT INTO tcm VALUES ( 5814889, 78687406, '2023-08-04'::DATE, 11111111, null, 22222221, true ), ( 5814889, 78707836, '2023-08-05'::DATE, 11111112, 923095, 22222222, false ), ( 5814889, 78708445, '2023-08-06'::DATE, 11111113, 743482, 22222223, false ), ( 581488900, 78687406, '2023-08-04'::DATE, 11111111, null, 22222221, true ), ( 581488900, 78707836, '2023-08-05'::DATE, 11111112, 923095, 22222222, false ), ( 581488900, 78708445, '2023-08-06'::DATE, 11111113, 743482, 22222223, true ); query IITIIIIT nosort SELECT tcm.chat_id AS chat_id, tcm.chat_message_id AS chat_message_id, tcm.message_tstamp AS message_tstamp, tcm.first_xxx_id AS first_xxx_id, lead(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp) AS _lead, lag(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp) AS _lag, CASE WHEN tcm.boolean_flag THEN coalesce( lead(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp), lag(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp), tcm.xxx_id ) ELSE tcm.user_id end AS xxx_id, tcm.boolean_flag AS boolean_flag FROM tcm ORDER BY chat_id, message_tstamp; ---- 5814889 78687406 2023-08-04 NULL 923095 NULL 923095 true 5814889 78707836 2023-08-05 923095 743482 NULL 22222222 false 5814889 78708445 2023-08-06 743482 NULL 923095 22222223 false 581488900 78687406 2023-08-04 NULL 923095 NULL 923095 true 581488900 78707836 2023-08-05 923095 743482 NULL 22222222 false 581488900 78708445 2023-08-06 743482 NULL 923095 923095 true query IITIIT nosort SELECT tcm.chat_id AS chat_id, tcm.chat_message_id AS chat_message_id, tcm.message_tstamp AS message_tstamp, tcm.first_xxx_id AS first_xxx_id, CASE WHEN tcm.boolean_flag THEN coalesce( lead(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp), lag(tcm.first_xxx_id) ignore nulls over (partition by tcm.chat_id order by tcm.message_tstamp), tcm.xxx_id ) ELSE tcm.user_id end AS xxx_id, tcm.boolean_flag AS boolean_flag FROM tcm ORDER BY chat_id, message_tstamp; ---- 5814889 78687406 2023-08-04 NULL 923095 true 5814889 78707836 2023-08-05 923095 22222222 false 5814889 78708445 2023-08-06 743482 22222223 false 581488900 78687406 2023-08-04 NULL 923095 true 581488900 78707836 2023-08-05 923095 22222222 false 581488900 78708445 2023-08-06 743482 923095 true # Window func with (non-windowed) aggregations query error db error: ERROR: window functions are not allowed in aggregate function \(function pg_catalog\.lag\) EXPLAIN RAW PLAN FOR SELECT sum(lag(a) OVER ()) FROM foo; query III nosort SELECT length(b), sum(a), lag(sum(a)) OVER (ORDER BY sum(a)) FROM foo GROUP BY length(b) ORDER BY sum(a), length(b); ---- 4 0 NULL 3 3 0 5 3 3 query III nosort SELECT length(b), sum(a), lag(sum(a)) OVER (ORDER BY length(b)) FROM foo GROUP BY length(b) ORDER BY length(b); ---- 3 3 NULL 4 0 3 5 3 0 query III SELECT * FROM ((SELECT length(b), sum(a), lag(sum(a)) OVER (ORDER BY sum(a)) FROM foo GROUP BY length(b)) UNION (SELECT length(b), sum(a), lag(sum(a)) OVER (ORDER BY length(b)) FROM foo GROUP BY length(b))) r(x, y, z) ORDER BY x, y, z; ---- 3 3 0 3 3 NULL 4 0 3 4 0 NULL 5 3 0 5 3 3 query IIII nosort SELECT *, row_number() OVER (PARTITION BY len ORDER BY sum DESC, lag NULLS FIRST) as row_num FROM ( (SELECT length(b) AS len, sum(a) AS sum, lag(sum(a)) OVER (ORDER BY sum(a)) AS lag FROM foo GROUP BY length(b)) UNION (SELECT length(b), -1 + sum(a) + row_number() OVER (ORDER BY length(b)), lag(sum(a)) OVER (ORDER BY length(b)) FROM foo GROUP BY length(b)) ) AS sq ORDER BY len, row_num; ---- 3 3 NULL 1 3 3 0 2 4 1 3 1 4 0 NULL 2 5 5 0 1 5 3 3 2 ## Subqueries # Correlated scalar subquery in the 2nd argument of lag query ITI nosort SELECT *, lag( outer_a, (SELECT count(*) FROM foo WHERE length(b) = length(outer_b))::integer ) OVER (ORDER BY outer_a) FROM ( SELECT a AS outer_a, b AS outer_b FROM foo ) as fsq ORDER BY outer_a; ---- 0 zero NULL 1 one NULL 2 two 0 3 three 2 # Add a correlated IN subquery at the 3rd argument of lag query ITI nosort SELECT *, lag( outer_a, (SELECT count(*) FROM foo WHERE length(b) = length(outer_b))::integer, CASE WHEN outer_a - 1 IN (SELECT a FrOM foo) THEN 100 ELSE 500 END ) OVER (ORDER BY outer_a) FROM ( SELECT a AS outer_a, b AS outer_b FROM foo ) as fsq ORDER BY outer_a; ---- 0 zero 500 1 one 100 2 two 0 3 three 2 # Correlated subquery in the PARTITION BY. # The same subquery is also present in the SELECT to make the output more human-friendly. query IITI nosort SELECT (SELECT count(*) FROM foo WHERE length(b) = length(outer_b)) as part, *, lag(outer_a) OVER ( PARTITION BY (SELECT count(*) FROM foo WHERE length(b) = length(outer_b)) ORDER BY outer_a ) FROM ( SELECT a AS outer_a, b AS outer_b FROM foo ) as fsq ORDER BY part, outer_a; ---- 1 0 zero NULL 1 3 three 0 2 1 one NULL 2 2 two 1 # Same query as the previous, but the subquery is not present in the SELECT query ITI nosort SELECT *, lag(outer_a) OVER ( PARTITION BY (SELECT count(*) FROM foo WHERE length(b) = length(outer_b)) ORDER BY outer_a ) FROM ( SELECT a AS outer_a, b AS outer_b FROM foo ) as fsq ORDER BY outer_a; ---- 0 zero NULL 1 one NULL 2 two 1 3 three 0 # Correlated subquery in the ORDER BY of a window function. # The same subquery is also present in the SELECT to make the output more human-friendly. query IITI nosort SELECT (SELECT count(*) FROM foo WHERE length(outer_b) > a + 2) as ord, *, lag(outer_a) OVER ( ORDER BY (SELECT count(*) FROM foo WHERE length(outer_b) > a + 2), outer_a ) FROM ( SELECT a AS outer_a, b AS outer_b FROM foo ) as fsq ORDER BY ord, outer_a; ---- 1 1 one NULL 1 2 two 1 2 0 zero 2 3 3 three 0 # Same query as the previous, but the subquery is not present in the SELECT query ITI nosort SELECT *, lag(outer_a) OVER ( ORDER BY (SELECT count(*) FROM foo WHERE length(outer_b) > a + 2), outer_a ) FROM ( SELECT a AS outer_a, b AS outer_b FROM foo ) as fsq ORDER BY outer_a; ---- 0 zero 2 1 one NULL 2 two 1 3 three 0 # Window func in a correlated subquery, but the correlating column reference is outside the window function query ITI nosort SELECT *, ( SELECT sum(lead_a) FROM ( SELECT lead(a) OVER (ORDER BY a) AS lead_a FROM foo WHERE a <= outer_a AND a < 3 ) as fsq2 ) FROM ( SELECT a AS outer_a, b AS outer_b FROM foo ) as fsq ORDER BY outer_a; ---- 0 zero NULL 1 one 1 2 two 3 3 three 3 # Similar to the previous one, but more window functions and aggregations query ITI nosort SELECT *, ( SELECT sum(2 * w) + max(w) FROM ( SELECT lead(a) OVER (ORDER BY a) + row_number() OVER (ORDER BY a) AS w FROM foo WHERE a <= outer_a AND a < 3 ) as fsq2 ) FROM ( SELECT a AS outer_a, b AS outer_b FROM foo ) as fsq ORDER BY outer_a; ---- 0 zero NULL 1 one 6 2 two 16 3 three 16 # Window func in a correlated subquery, and the window function argument refers to the outer query query ITI nosort SELECT *, ( SELECT sum(lead_a) FROM ( SELECT lead(outer_a + a) OVER (ORDER BY a) AS lead_a FROM foo ) as fsq2 ) FROM ( SELECT a AS outer_a, b AS outer_b FROM foo ) as fsq ORDER BY outer_a; ---- 0 zero 6 1 one 9 2 two 12 3 three 15 # Window func in a correlated subquery, and the window function's PARTITION BY refers to the outer query query ITI SELECT *, ( SELECT sum(lead_a) FROM ( SELECT 3 + lead(a) OVER (PARTITION BY 4 * a / (outer_a + length(outer_b)) ORDER BY a) AS lead_a FROM foo ) as fsq2 ) FROM ( (SELECT a AS outer_a, b AS outer_b FROM foo) UNION (SELECT a + 2 AS outer_a, b AS outer_b FROM foo) UNION (SELECT a + 5 AS outer_a, b AS outer_b FROM foo) ) as fsq ORDER BY outer_a, outer_b; ---- 0 zero NULL 1 one NULL 2 two 4 2 zero 4 3 one 4 3 three 10 4 two 10 5 three 9 5 zero 9 6 one 9 7 two 9 8 three 15 # Let's check the HIR plan for the above query to see if the window function is lifted out from behind the `3 +` query T multiline EXPLAIN RAW PLAN FOR SELECT *, ( SELECT sum(lead_a) FROM ( SELECT 3 + lead(a) OVER (PARTITION BY 4 * a / (outer_a + length(outer_b)) ORDER BY a) AS lead_a FROM foo ) as fsq2 ) FROM ( (SELECT a AS outer_a, b AS outer_b FROM foo) UNION (SELECT a + 2 AS outer_a, b AS outer_b FROM foo) UNION (SELECT a + 5 AS outer_a, b AS outer_b FROM foo) ) as fsq ORDER BY outer_a, outer_b; ---- Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#2] With cte [l1 as subquery-1] = Reduce aggregates=[sum(#0{lead_a})] Project (#3) Map (lead(row(#0{a}, 1, null)) over (partition by [((4 * #0{a}) / (#^0{outer_a} + char_length(#^1{outer_b})))] order by [#0{a} asc nulls_last]), (3 + #2{?column?})) Get materialize.public.foo Return Map (select(Get l1)) Distinct Union Distinct Union Get materialize.public.foo Project (#2, #1) Map ((#0{a} + 2)) Get materialize.public.foo Project (#2, #1) Map ((#0{a} + 5)) Get materialize.public.foo Target cluster: quickstart EOF ################################## ## Window aggregations ################################## statement ok CREATE TABLE t7(x INT, y INT); # Plans query T multiline EXPLAIN DECORRELATED PLAN FOR SELECT sum(y-3) OVER (PARTITION BY 2*x, 3*y ORDER BY x+1, y+2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t7 ORDER BY x; ---- Finish order_by=[#0 asc nulls_last] output=[#2] Project (#0, #1, #3) Map (#2) Project (#4..=#6) Map (record_get[0](record_get[1](#3)), record_get[1](record_get[1](#3)), record_get[0](#3)) FlatMap unnest_list(#2) Reduce group_by=[#2, #3] aggregates=[window_agg[sum order_by=[#0 asc nulls_last, #1 asc nulls_last] rows between unbounded preceding and current row](row(row(row(#0, #1), (#1{y} - 3)), (#0{x} + 1), (#1{y} + 2)))] Map ((2 * #0{x}), (3 * #1{y})) CrossJoin Constant - () Get materialize.public.t7 Target cluster: quickstart EOF query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT sum(y-3) OVER (PARTITION BY 2*x, 3*y ORDER BY x+1, y+2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t7 ORDER BY x; ---- Explained Query: Finish order_by=[#0 asc nulls_last] output=[#2] Project (#3..=#5) Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[0](#1)) FlatMap unnest_list(#0{window_agg}) Project (#2{window_agg}) Reduce group_by=[(2 * #0{x}), (3 * #1{y})] aggregates=[window_agg[sum order_by=[#0{x} asc nulls_last, #1{y} asc nulls_last] rows between unbounded preceding and current row](row(row(row(#0{x}, #1{y}), (#1{y} - 3)), (#0{x} + 1), (#1{y} + 2)))] ReadStorage materialize.public.t7 Source materialize.public.t7 Target cluster: quickstart EOF # Empty aggregations query IIIIIT SELECT x, y, sum(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), max(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), array_agg(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t7 ORDER BY x; ---- query IIIIIT SELECT x, y, sum(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), max(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), min(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), jsonb_agg(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM t7 ORDER BY x; ---- query IIIIIT SELECT x, y, sum(y) OVER (ORDER BY x ROWS BETWEEN 2 FOLLOWING AND 5 FOLLOWING), max(y) OVER (ORDER BY x ROWS BETWEEN 2 FOLLOWING AND 5 FOLLOWING), min(y) OVER (ORDER BY x ROWS BETWEEN 2 FOLLOWING AND 5 FOLLOWING), list_agg(y) OVER (ORDER BY x ROWS BETWEEN 2 FOLLOWING AND 5 FOLLOWING)[1] FROM t7 ORDER BY x; ---- statement ok INSERT INTO t7 VALUES (1,2), (3,null), (5,6), (7,8), (9, null), (11, null), (13, 14), (15, 16), (17, 18), (10, -40), (10, -50); # Test various nestings # Postgres also doesn't support this. query error db error: ERROR: window functions are not allowed in aggregate function \(function pg_catalog\.sum\) SELECT sum(sum(x) OVER ()) FROM t7; # Other way around: grouped aggregate inside a window aggregate. # What this query is asking us to do is to # 1. First, perform the inner sum (with its GROUP BY), and then # 2. the outer sum (with its OVER clause). # (Postgres handles this the same.) query I SELECT sum(sum(x)) OVER (ORDER BY y) FROM t7 GROUP BY y; ---- 10 20 21 26 33 46 61 78 101 # Window aggregation inside a window aggregation. We support this, and queries like this might actually make sense in # some cases. (I think we already had a customer with some other window functions nested inside each other.) # (Postgres doesn't support this.) query I SELECT sum(sum(x) OVER ()) OVER () FROM t7; ---- 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 query III SELECT x, sum(x) OVER (ORDER BY x), sum(sum(x) OVER (ORDER BY x)) OVER (ORDER BY x) FROM (SELECT DISTINCT ON(x) * FROM t7); ---- 1 1 1 3 4 5 5 9 14 7 16 30 9 25 55 10 35 90 11 46 136 13 59 195 15 74 269 17 91 360 # Lots of different aggregation functions. This covers # - basic, hierarchical, accumulable aggregations # - count's null handling # - count(*) # - FILTER clause # - Aggregation functions that are transformed away by `transform_ast.rs`: bool_and, bool_or. # These are all supported by Postgres, and the output should be identical. query IIIIIITIIIITT SELECT x-y, x, y, sum(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), max(x*y+1) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(x*y+1) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), array_agg(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), count(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), count(CASE WHEN x%3 != 0 THEN x ELSE null END) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), count(*) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), sum(x) FILTER (WHERE x%3 != 0) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), bool_and(x%3 != 0) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), bool_or(x%3 = 0) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t7 ORDER BY x-y, x; ---- -1 1 2 116 307 3 {2,10,14,26,30,34} 6 5 6 43 false true -1 5 6 114 307 31 {10,14,26,30,34} 5 4 5 42 false true -1 7 8 104 307 57 {14,26,30,34} 4 3 4 37 false true -1 13 14 90 307 183 {26,30,34} 3 2 3 30 false true -1 15 16 64 307 241 {30,34} 2 1 2 17 false true -1 17 18 34 307 307 {34} 1 1 1 17 true false 50 10 -40 20 -399 -399 {20} 1 1 1 10 true false 60 10 -50 20 -499 -499 {20} 1 1 1 10 true false NULL 3 NULL 46 NULL NULL {6,18,22} 3 1 3 11 false true NULL 9 NULL 40 NULL NULL {18,22} 2 1 2 11 false true NULL 11 NULL 22 NULL NULL {22} 1 1 1 11 true false # Also supported by Postgres, but output text formatting is a bit different, so putting this in a separate test. # avg, variance, var_pop, stddev, stddev_pop are transformed away by `transform_ast.rs`. query IIITRRRRR SELECT x-y, x, y, jsonb_agg(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), avg(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), variance(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), var_pop(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), stddev(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), stddev_pop(x+y) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t7 ORDER BY x-y, x; ---- -1 1 2 [2,10,14,26,30,34] 20.3333333333333333333333333333333333333 157.866666666666666666666666666666666666 131.555555555555555555555555555555555555 12.5645002553490628940078294328743895394 11.469767022723502362305964713399618176 -1 5 6 [10,14,26,30,34] 23.8 107.2 85.76 10.3537432844358276565421773235938072887 9.26066952223218037884464808956388302968 -1 7 8 [14,26,30,34] 27 74.6666666666666666666666666666666666667 56 8.64098759787714697462128991478399554361 7.48331477354788277116749746463309860351 -1 13 14 [26,30,34] 31 16 10.6666666666666666666666666666666666667 4 3.26598632371090413092971209960785518929 -1 15 16 [30,34] 33 8 4 2.82842712474619009760337744841939615714 2 -1 17 18 [34] 35 NULL 0 NULL 0 50 10 -40 [20] -30 NULL 0 NULL 0 60 10 -50 [20] -40 NULL 0 NULL 0 NULL 3 NULL [6,18,22] NULL NULL NULL NULL NULL NULL 9 NULL [18,22] NULL NULL NULL NULL NULL NULL 11 NULL [22] NULL NULL NULL NULL NULL # These are not supported by Postgres. # - ORDER BY in aggr # - list indexing after the OVER clause query IIITTI SELECT x-y, x, y, array_agg(x ORDER BY CASE WHEN y IS NOT NULL THEN y ELSE x%5 END DESC) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), array_agg(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), list_agg(2*x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)[2] FROM t7 ORDER BY x-y, x; ---- -1 1 2 {17,15,13,7,5,1} {2,10,14,26,30,34} 10 -1 5 6 {17,15,13,7,5} {10,14,26,30,34} 14 -1 7 8 {17,15,13,7} {14,26,30,34} 26 -1 13 14 {17,15,13} {26,30,34} 30 -1 15 16 {17,15} {30,34} 34 -1 17 18 {17} {34} NULL 50 10 -40 {10} {20} NULL 60 10 -50 {10} {20} NULL NULL 3 NULL {9,3,11} {6,18,22} 18 NULL 9 NULL {9,11} {18,22} 22 NULL 11 NULL {11} {22} NULL # https://github.com/MaterializeInc/database-issues/issues/6626 query error db error: ERROR: DISTINCT in window aggregates not yet supported SELECT x-y, x, y, array_agg(x/10) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), count(DISTINCT x/10) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t7 ORDER BY x-y, x; # No ORDER BY in OVER clause. query IIIIITTTT SELECT x, y, sum(y) OVER (), max(y) OVER (), min(y) OVER (), array_agg(y ORDER BY y) OVER (), array_agg(y ORDER BY y NULLS LAST) OVER (), array_agg(y ORDER BY y NULLS FIRST) OVER (), bool_and(x%3 != 0) OVER () FROM t7 ORDER BY x; ---- 1 2 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 3 NULL -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 5 6 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 7 8 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 9 NULL -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 10 -50 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 10 -40 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 11 NULL -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 13 14 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 15 16 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false 17 18 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} {NULL,NULL,NULL,-50,-40,2,6,8,14,16,18} false query IIIIIIT SELECT x-y+x/10, x, y, sum(y) OVER (PARTITION BY x-y+x/10), max(y) OVER (PARTITION BY x-y+x/10), min(y) OVER (PARTITION BY x-y+x/10), array_agg(y) OVER (PARTITION BY x-y+x/10) FROM t7 ORDER BY x-y+x/10; ---- -1 1 2 16 8 2 {2,6,8} -1 5 6 16 8 2 {2,6,8} -1 7 8 16 8 2 {2,6,8} 0 13 14 48 18 14 {14,16,18} 0 15 16 48 18 14 {14,16,18} 0 17 18 48 18 14 {14,16,18} 51 10 -40 -40 -40 -40 {-40} 61 10 -50 -50 -50 -50 {-50} NULL 3 NULL NULL NULL NULL {NULL,NULL,NULL} NULL 9 NULL NULL NULL NULL {NULL,NULL,NULL} NULL 11 NULL NULL NULL NULL {NULL,NULL,NULL} # Test the situation when we don't have an ORDER BY, but we have a frame that doesn't include the current row. # This verifies that the `order_by.is_empty()` if condition also includes `window_frame.includes_current_row()`. query IIIIITR SELECT x-y+x/10, x, y, sum(y) OVER (PARTITION BY x-y+x/10 ROWS BETWEEN 200 PRECEDING AND 100 PRECEDING), count(y) OVER (PARTITION BY x-y+x/10 ROWS BETWEEN 200 PRECEDING AND 100 PRECEDING), array_agg(y) OVER (PARTITION BY x-y+x/10 ROWS BETWEEN 200 PRECEDING AND 100 PRECEDING), avg(y) OVER (PARTITION BY x-y+x/10 ROWS BETWEEN 200 PRECEDING AND 100 PRECEDING) FROM t7 ORDER BY x, y; ---- -1 1 2 NULL 0 NULL NULL NULL 3 NULL NULL 0 NULL NULL -1 5 6 NULL 0 NULL NULL -1 7 8 NULL 0 NULL NULL NULL 9 NULL NULL 0 NULL NULL 61 10 -50 NULL 0 NULL NULL 51 10 -40 NULL 0 NULL NULL NULL 11 NULL NULL 0 NULL NULL 0 13 14 NULL 0 NULL NULL 0 15 16 NULL 0 NULL NULL 0 17 18 NULL 0 NULL NULL # ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW query IIIIIT SELECT x, y, sum(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), max(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), array_agg(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t7 ORDER BY x; ---- 1 2 2 2 2 {2} 3 NULL 2 2 2 {2,NULL} 5 6 8 6 2 {2,6,NULL} 7 8 16 8 2 {2,6,8,NULL} 9 NULL 16 8 2 {2,6,8,NULL,NULL} 10 -50 -34 8 -50 {-50,2,6,8,NULL,NULL} 10 -40 -74 8 -50 {-50,-40,2,6,8,NULL,NULL} 11 NULL -74 8 -50 {-50,-40,2,6,8,NULL,NULL,NULL} 13 14 -60 14 -50 {-50,-40,2,6,8,14,NULL,NULL,NULL} 15 16 -44 16 -50 {-50,-40,2,6,8,14,16,NULL,NULL,NULL} 17 18 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} statement ok CREATE TABLE t8(o1 INT, o2 INT, v INT); statement ok INSERT INTO t8 VALUES (1,1,-2), (1,1,-3), (1,2,-4), (1,3,-7), (1,3,-8), (2,1,-10), (2,2,-1000), (2,2,-1000), (2,2,-1000), (3,0,-100); # Default frame, i.e. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW query IIIIIIT SELECT o1, o2, v, sum(v) OVER (ORDER BY o1, o2), max(v) OVER (ORDER BY o1, o2), min(v) OVER (ORDER BY o1, o2), array_agg(v) OVER (ORDER BY o1, o2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t8 ORDER BY o1, o2; ---- 1 1 -3 -5 -2 -3 {-3,-2} 1 1 -2 -5 -2 -3 {-3,-2} 1 2 -4 -9 -2 -4 {-4,-3,-2} 1 3 -8 -24 -2 -8 {-8,-7,-4,-3,-2} 1 3 -7 -24 -2 -8 {-8,-7,-4,-3,-2} 2 1 -10 -34 -2 -10 {-10,-8,-7,-4,-3,-2} 2 2 -1000 -3034 -2 -1000 {-1000,-1000,-1000,-10,-8,-7,-4,-3,-2} 2 2 -1000 -3034 -2 -1000 {-1000,-1000,-1000,-10,-8,-7,-4,-3,-2} 2 2 -1000 -3034 -2 -1000 {-1000,-1000,-1000,-10,-8,-7,-4,-3,-2} 3 0 -100 -3134 -2 -1000 {-1000,-1000,-1000,-100,-10,-8,-7,-4,-3,-2} query IIIIIIT SELECT o1, o2, v, sum(v) OVER (PARTITION BY o1 ORDER BY o2), max(v) OVER (PARTITION BY o1 ORDER BY o2), min(v) OVER (PARTITION BY o1 ORDER BY o2), array_agg(v) OVER (PARTITION BY o1 ORDER BY o2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t8 ORDER BY o1, o2; ---- 1 1 -3 -5 -2 -3 {-3,-2} 1 1 -2 -5 -2 -3 {-3,-2} 1 2 -4 -9 -2 -4 {-4,-3,-2} 1 3 -8 -24 -2 -8 {-8,-7,-4,-3,-2} 1 3 -7 -24 -2 -8 {-8,-7,-4,-3,-2} 2 1 -10 -10 -10 -10 {-10} 2 2 -1000 -3010 -10 -1000 {-1000,-1000,-1000,-10} 2 2 -1000 -3010 -10 -1000 {-1000,-1000,-1000,-10} 2 2 -1000 -3010 -10 -1000 {-1000,-1000,-1000,-10} 3 0 -100 -100 -100 -100 {-100} # ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING # Note that x=10 occurs twice. This means that the ordering of these rows is undefined, so it can change between # Materialize versions. query IIIIIT SELECT x, y, sum(y) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), max(y) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(y) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), array_agg(y) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t7 ORDER BY x; ---- 1 2 -26 18 -50 {-50,-40,2,6,8,14,16,18,NULL,NULL,NULL} 3 NULL -28 18 -50 {-50,-40,6,8,14,16,18,NULL,NULL,NULL} 5 6 -28 18 -50 {-50,-40,6,8,14,16,18,NULL,NULL} 7 8 -34 18 -50 {-50,-40,8,14,16,18,NULL,NULL} 9 NULL -42 18 -50 {-50,-40,14,16,18,NULL,NULL} 10 -40 8 18 -40 {-40,14,16,18,NULL} 10 -50 -42 18 -50 {-50,-40,14,16,18,NULL} 11 NULL 48 18 14 {14,16,18,NULL} 13 14 48 18 14 {14,16,18} 15 16 34 18 16 {16,18} 17 18 18 18 18 {18} # RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING # TODO: Actually, we already have the rendering code for this. So, we just need to either hack planning a bit to add an # exception for window aggregations, or add rendering support also for other window functions. query error RANGE in non-default window frames not yet supported SELECT x, y, sum(y) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), max(y) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(y) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), array_agg(y) OVER (ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t7 ORDER BY x; # PARTITION BY # ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING # Here, x=10 occurring twice does NOT mean that the result for these rows would be undefined. query IIIIIIT SELECT x-y, x, y, sum(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), max(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), array_agg(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t7 ORDER BY x-y, x; ---- -1 1 2 116 34 2 {2,10,14,26,30,34} -1 5 6 114 34 10 {10,14,26,30,34} -1 7 8 104 34 14 {14,26,30,34} -1 13 14 90 34 26 {26,30,34} -1 15 16 64 34 30 {30,34} -1 17 18 34 34 34 {34} 50 10 -40 20 20 20 {20} 60 10 -50 20 20 20 {20} NULL 3 NULL 46 22 6 {6,18,22} NULL 9 NULL 40 22 18 {18,22} NULL 11 NULL 22 22 22 {22} query IIIIIT SELECT x, y, sum(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), max(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), array_agg(x+x) OVER (PARTITION BY x-y ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t7 ORDER BY x-y, x; ---- 1 2 116 34 2 {2,10,14,26,30,34} 5 6 114 34 10 {10,14,26,30,34} 7 8 104 34 14 {14,26,30,34} 13 14 90 34 26 {26,30,34} 15 16 64 34 30 {30,34} 17 18 34 34 34 {34} 10 -40 20 20 20 {20} 10 -50 20 20 20 {20} 3 NULL 46 22 6 {6,18,22} 9 NULL 40 22 18 {18,22} 11 NULL 22 22 22 {22} # PARTITION BY # RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING query error RANGE in non-default window frames not yet supported SELECT x, y, sum(x+x) OVER (PARTITION BY x-y ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), max(x+x) OVER (PARTITION BY x-y ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(x+x) OVER (PARTITION BY x-y ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), array_agg(x+x) OVER (PARTITION BY x-y ORDER BY x RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t7 ORDER BY x-y, x; # ROWS BETWEEN offset PRECEDING AND offset FOLLOWING query IIIIIT SELECT x, y, sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING), max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING), min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING), array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) FROM t7 ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END; ---- 10 -50 20 10 10 {10,10} 10 -40 21 10 1 {1,10,10} 1 2 24 10 1 {1,3,10,10} 3 NULL 23 10 1 {1,3,9,10} 9 NULL 24 11 1 {1,3,9,11} 11 NULL 28 11 3 {3,5,9,11} 5 6 32 11 5 {5,7,9,11} 7 8 36 13 5 {5,7,11,13} 13 14 40 15 5 {5,7,13,15} 15 16 52 17 7 {7,13,15,17} 17 18 45 17 13 {13,15,17} query IIIIIT SELECT x, y, sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING), max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING), min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING), array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) FROM t7 ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END; ---- 10 -50 20 10 10 {10,10} 10 -40 11 10 1 {1,10} 1 2 4 3 1 {1,3} 3 NULL 12 9 3 {3,9} 9 NULL 20 11 9 {9,11} 11 NULL 16 11 5 {5,11} 5 6 12 7 5 {5,7} 7 8 20 13 7 {7,13} 13 14 28 15 13 {13,15} 15 16 32 17 15 {15,17} 17 18 17 17 17 {17} query IIIIIT SELECT x, y, sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING), max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING), min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING), array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING) FROM t7 ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END; ---- 10 -50 10 10 10 {10} 10 -40 10 10 10 {10} 1 2 1 1 1 {1} 3 NULL 3 3 3 {3} 9 NULL 9 9 9 {9} 11 NULL 11 11 11 {11} 5 6 5 5 5 {5} 7 8 7 7 7 {7} 13 14 13 13 13 {13} 15 16 15 15 15 {15} 17 18 17 17 17 {17} # mixed UNBOUNDED - OFFSET frames query error db error: ERROR: mixed unbounded \- offset frames not yet supported SELECT sum(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING) FROM t7; query error db error: ERROR: mixed unbounded \- offset frames not yet supported SELECT sum(y) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING) FROM t7; query error db error: ERROR: mixed unbounded \- offset frames not yet supported SELECT sum(y) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) FROM t7; query error db error: ERROR: mixed unbounded \- offset frames not yet supported SELECT sum(y) OVER (ORDER BY x ROWS BETWEEN 3 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t7; # And a parse error as a bonus query error Expected PRECEDING or FOLLOWING, found UNBOUNDED SELECT sum(y) OVER (ORDER BY x ROWS BETWEEN 3 FOLLOWING AND 2 UNBOUNDED FOLLOWING) FROM t7; # ROWS BETWEEN offset PRECEDING AND offset PRECEDING query IIIIIT SELECT x, y, sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING), max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING), min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING), array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) FROM t7 ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END; ---- 10 -50 NULL NULL NULL NULL 10 -40 10 10 10 {10} 1 2 20 10 10 {10,10} 3 NULL 21 10 1 {1,10,10} 9 NULL 14 10 1 {1,3,10} 11 NULL 13 9 1 {1,3,9} 5 6 23 11 3 {3,9,11} 7 8 25 11 5 {5,9,11} 13 14 23 11 5 {5,7,11} 15 16 25 13 5 {5,7,13} 17 18 35 15 7 {7,13,15} # ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING query IIIIIT SELECT x, y, sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING), max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING), min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING), array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) FROM t7 ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END; ---- 10 -50 13 9 1 {1,3,9} 10 -40 23 11 3 {3,9,11} 1 2 25 11 5 {5,9,11} 3 NULL 23 11 5 {5,7,11} 9 NULL 25 13 5 {5,7,13} 11 NULL 35 15 7 {7,13,15} 5 6 45 17 13 {13,15,17} 7 8 32 17 15 {15,17} 13 14 17 17 17 {17} 15 16 NULL NULL NULL NULL 17 18 NULL NULL NULL NULL # Frame end is later than frame start. All frames are empty. query IIIIIT SELECT x, y, sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 30 FOLLOWING AND 1 FOLLOWING), max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 30 FOLLOWING AND 1 FOLLOWING), min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 30 FOLLOWING AND 1 FOLLOWING), array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 30 FOLLOWING AND 1 FOLLOWING) FROM t7 ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END; ---- 10 -50 NULL NULL NULL NULL 10 -40 NULL NULL NULL NULL 1 2 NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL 9 NULL NULL NULL NULL NULL 11 NULL NULL NULL NULL NULL 5 6 NULL NULL NULL NULL 7 8 NULL NULL NULL NULL 13 14 NULL NULL NULL NULL 15 16 NULL NULL NULL NULL 17 18 NULL NULL NULL NULL query IIIIIT SELECT x, y, sum(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING), max(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING), min(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING), array_agg(x) OVER (ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING) FROM t7 ORDER BY CASE WHEN y IS NOT NULL THEN x*y ELSE x END; ---- 10 -50 NULL NULL NULL NULL 10 -40 NULL NULL NULL NULL 1 2 NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL 9 NULL NULL NULL NULL NULL 11 NULL NULL NULL NULL NULL 5 6 NULL NULL NULL NULL 7 8 NULL NULL NULL NULL 13 14 NULL NULL NULL NULL 15 16 NULL NULL NULL NULL 17 18 NULL NULL NULL NULL # ROWS BETWEEN offset PRECEDING AND offset FOLLOWING query IIIIIIT SELECT x-y+x/10, x, y, sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t7 ORDER BY x-y+x/10, x; ---- -1 1 2 12 10 2 {2,10} -1 5 6 26 14 2 {2,10,14} -1 7 8 24 14 10 {10,14} 0 13 14 56 30 26 {26,30} 0 15 16 90 34 26 {26,30,34} 0 17 18 64 34 30 {30,34} 51 10 -40 20 20 20 {20} 61 10 -50 20 20 20 {20} NULL 3 NULL 24 18 6 {6,18} NULL 9 NULL 46 22 6 {6,18,22} NULL 11 NULL 40 22 18 {18,22} query IIIIIIT SELECT x-y+x/10, x, y, sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1000000 PRECEDING AND 1000000 FOLLOWING), max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1000000 PRECEDING AND 1000000 FOLLOWING), min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1000000 PRECEDING AND 1000000 FOLLOWING), array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 1000000 PRECEDING AND 1000000 FOLLOWING) FROM t7 ORDER BY x-y+x/10, x; ---- -1 1 2 26 14 2 {2,10,14} -1 5 6 26 14 2 {2,10,14} -1 7 8 26 14 2 {2,10,14} 0 13 14 90 34 26 {26,30,34} 0 15 16 90 34 26 {26,30,34} 0 17 18 90 34 26 {26,30,34} 51 10 -40 20 20 20 {20} 61 10 -50 20 20 20 {20} NULL 3 NULL 46 22 6 {6,18,22} NULL 9 NULL 46 22 6 {6,18,22} NULL 11 NULL 46 22 6 {6,18,22} query IIIIIIT SELECT x-row_number() OVER (ORDER BY x+y), x, y, sum(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), max(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), min(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), array_agg(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t7 ORDER BY x-row_number() OVER (ORDER BY x+y), x; ---- -6 3 NULL 6 6 6 {6} -2 1 2 2 2 2 {2} -1 9 NULL 18 18 18 {18} 0 11 NULL 22 22 22 {22} 1 5 6 10 10 10 {10} 2 7 8 14 14 14 {14} 7 13 14 26 26 26 {26} 8 10 -40 50 30 20 {20,30} 8 15 16 50 30 20 {20,30} 9 10 -50 54 34 20 {20,34} 9 17 18 54 34 20 {20,34} # Offset has to be a literal query error Expected literal unsigned integer, found identifier "row_number" SELECT x-row_number() OVER (ORDER BY x+y), x, y, sum(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN row_number() OVER (ORDER BY x+y) PRECEDING AND 0 FOLLOWING), max(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN row_number() OVER (ORDER BY x+y) PRECEDING AND 0 FOLLOWING), min(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN row_number() OVER (ORDER BY x+y) PRECEDING AND 0 FOLLOWING), array_agg(x+x) OVER (PARTITION BY x-row_number() OVER (ORDER BY x+y) ORDER BY x ROWS BETWEEN row_number() OVER (ORDER BY x+y) PRECEDING AND 0 FOLLOWING) FROM t7 ORDER BY x-row_number() OVER (ORDER BY x+y), x; # ROWS BETWEEN offset PRECEDING AND CURRENT ROW query IIIIIIT SELECT x-y+x/10, x, y, sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM t7 ORDER BY x-y+x/10, x; ---- -1 1 2 2 2 2 {2} -1 5 6 12 10 2 {2,10} -1 7 8 26 14 2 {2,10,14} 0 13 14 26 26 26 {26} 0 15 16 56 30 26 {26,30} 0 17 18 90 34 26 {26,30,34} 51 10 -40 20 20 20 {20} 61 10 -50 20 20 20 {20} NULL 3 NULL 6 6 6 {6} NULL 9 NULL 24 18 6 {6,18} NULL 11 NULL 46 22 6 {6,18,22} # ROWS BETWEEN CURRENT ROW AND offset FOLLOWING query IIIIIIT SELECT x-y+x/10, x, y, sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM t7 ORDER BY x-y+x/10, x; ---- -1 1 2 12 10 2 {2,10} -1 5 6 24 14 10 {10,14} -1 7 8 14 14 14 {14} 0 13 14 56 30 26 {26,30} 0 15 16 64 34 30 {30,34} 0 17 18 34 34 34 {34} 51 10 -40 20 20 20 {20} 61 10 -50 20 20 20 {20} NULL 3 NULL 24 18 6 {6,18} NULL 9 NULL 40 22 18 {18,22} NULL 11 NULL 22 22 22 {22} # ROWS BETWEEN CURRENT ROW AND CURRENT ROW query IIIIIIT SELECT x-y+x/10, x, y, sum(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW), max(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW), min(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW), array_agg(x+x) OVER (PARTITION BY x-y+x/10 ORDER BY x ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM t7 ORDER BY x-y+x/10, x; ---- -1 1 2 2 2 2 {2} -1 5 6 10 10 10 {10} -1 7 8 14 14 14 {14} 0 13 14 26 26 26 {26} 0 15 16 30 30 30 {30} 0 17 18 34 34 34 {34} 51 10 -40 20 20 20 {20} 61 10 -50 20 20 20 {20} NULL 3 NULL 6 6 6 {6} NULL 9 NULL 18 18 18 {18} NULL 11 NULL 22 22 22 {22} ## Tests for `AggregateExpr::on_unique` # In the next several tests, the global aggregates make the input column to the window aggregate a key, so # `ReduceElision` invokes `on_unique`. # # First, let's see that `ReduceElision` indeed kicks in, by observing that the Reduce of the window aggregation is not # present in the plan. (There is 1 Reduce present from the global aggregation.) # # Note that in these tests that rely on the window function's PARTITION BY being a key, we unfortunately can't test more # than one window function call in one test, because we currently forget the key information after a window function # call (even when `ReduceElision` simplifies the window function call). # TODO: Add an optimization that eliminates a Map-FlatMap pair where the Map is just creating a 1-element list on which # the FlatMap is immediately calling `unnest_list`. query T multiline EXPLAIN OPTIMIZED PLAN WITH(keys, humanized expressions) AS VERBOSE TEXT FOR SELECT sum(sum(x)) OVER () FROM t7; ---- Explained Query: With cte l0 = Reduce aggregates=[sum(#0{x})] // { keys: "([])" } Project (#0{x}) // { keys: "()" } ReadStorage materialize.public.t7 // { keys: "()" } Return // { keys: "()" } Project (#2) // { keys: "()" } Map (record_get[0](#1)) // { keys: "()" } FlatMap unnest_list(#0) // { keys: "()" } Project (#1) // { keys: "([])" } Map (list[row(bigint_to_numeric(#0{sum_x}), row(#0{sum_x}))]) // { keys: "([])" } Union // { keys: "([])" } Get l0 // { keys: "([])" } Map (null) // { keys: "()" } Union // { keys: "()" } Negate // { keys: "()" } Project () // { keys: "([])" } Get l0 // { keys: "([])" } Constant // { keys: "([])" } - () Source materialize.public.t7 Target cluster: quickstart EOF query I SELECT sum(sum(x)) OVER () FROM t7; ---- 101 query I SELECT min(count(x)) OVER () FROM t7; ---- 11 query I SELECT count(min(x)) OVER () FROM t7; ---- 1 simple conn=mz_system,user=mz_system ALTER SYSTEM SET unsafe_enable_table_keys = true ---- COMPLETE 0 statement ok CREATE TABLE t9(x INT UNIQUE, y INT); statement ok INSERT INTO t9 VALUES (1,2), (3,null), (5,6), (7,8), (9, null), (11, null), (13, 14), (15, 16), (17, 18); # Still testing `AggregateExpr::on_unique`. Here, the uniqueness constraint on the table makes `ReduceElision` call # `on_unique`. There should be 0 Reduce operations in the plan. query T multiline EXPLAIN OPTIMIZED PLAN WITH(keys, humanized expressions) AS VERBOSE TEXT FOR SELECT sum(y) OVER (PARTITION BY x) FROM t9 ORDER BY x; ---- Explained Query: Finish order_by=[#0 asc nulls_last] output=[#2] Project (#3..=#5) // { keys: "()" } Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[0](#1)) // { keys: "()" } FlatMap unnest_list(#0) // { keys: "()" } Project (#2) // { keys: "()" } Map (list[row(integer_to_bigint(#1{y}), row(#0{x}, #1{y}))]) // { keys: "([0])" } ReadStorage materialize.public.t9 // { keys: "([0])" } Source materialize.public.t9 Target cluster: quickstart EOF query I SELECT sum(y) OVER (PARTITION BY x) FROM t9 ORDER BY x; ---- 2 NULL 6 8 NULL NULL 14 16 18 query I SELECT min(y) OVER (PARTITION BY x) FROM t9 ORDER BY x; ---- 2 NULL 6 8 NULL NULL 14 16 18 query I SELECT count(y) OVER (PARTITION BY x) FROM t9 ORDER BY x; ---- 1 0 1 1 0 0 1 1 1 query I SELECT count(*) OVER (PARTITION BY x) FROM t9 ORDER BY x; ---- 1 1 1 1 1 1 1 1 1 query T SELECT array_agg(y) OVER (PARTITION BY x) FROM t9 ORDER BY x; ---- {2} {NULL} {6} {8} {NULL} {NULL} {14} {16} {18} query R SELECT avg(y+y) OVER (PARTITION BY x) FROM t9 ORDER BY x; ---- 4 NULL 12 16 NULL NULL 28 32 36 # Test that, for window aggregations, `on_unique` is properly calling `wrapped_aggregate.default()` when the frame is # empty (and not just returns null as the other window function cases in `on_unique`). query I SELECT count(y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM t9 ORDER BY x; ---- 0 0 0 0 0 0 0 0 0 query I SELECT sum(y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM t9 ORDER BY x; ---- NULL NULL NULL NULL NULL NULL NULL NULL NULL query II SELECT count(y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING), sum(y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM t9 ORDER BY x; ---- 0 NULL 0 NULL 0 NULL 0 NULL 0 NULL 0 NULL 0 NULL 0 NULL 0 NULL query T SELECT array_agg(y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM t9 ORDER BY x; ---- NULL NULL NULL NULL NULL NULL NULL NULL NULL query R SELECT avg(y+y) OVER (PARTITION BY x ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM t9 ORDER BY x; ---- NULL NULL NULL NULL NULL NULL NULL NULL NULL # Test `on_unique` for lag/lead query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT lag(x,y,100) OVER (PARTITION BY x ORDER BY y+y) FROM t9; ---- Explained Query: Project (#2) Map (record_get[0](#1)) FlatMap unnest_list(#0) Project (#2) Map (list[row(case when (#1{y}) IS NULL then null else case when (#1{y} = 0) then #0{x} else 100 end end, row(#0{x}, #1{y}))]) ReadStorage materialize.public.t9 Source materialize.public.t9 Target cluster: quickstart EOF query I rowsort SELECT lag(x,y,100) OVER (PARTITION BY x ORDER BY y+y) FROM t9; ---- NULL NULL NULL 100 100 100 100 100 100 query I rowsort SELECT lead(x,y-2,120) OVER (PARTITION BY x ORDER BY x) FROM t9; ---- NULL NULL NULL 1 120 120 120 120 120 # Test `on_unique` for `FusedValueWindowFunc`. There should be NO window function call in the following plan. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT x, y, lag(x,y,100) OVER (PARTITION BY x ORDER BY y+y), lag(x,y,110) OVER (PARTITION BY x ORDER BY y+y), lag(x,y,100) OVER (PARTITION BY x ORDER BY y+y), first_value(y) OVER (PARTITION BY x ORDER BY y+y), last_value(y) OVER (PARTITION BY x ORDER BY y+y), lead(x,y-2,120) OVER (PARTITION BY x ORDER BY y+y) FROM t9 ORDER BY x; ---- Explained Query: Finish order_by=[#0 asc nulls_last] output=[#0..=#7] Project (#3, #4, #9, #10, #9, #8, #7, #6) Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[0](#1), record_get[0](#5), record_get[1](#5), record_get[2](#5), record_get[3](#5), record_get[4](#5)) FlatMap unnest_list(#0) Project (#3) Map ((#1{y}) IS NULL, list[row(row(case when #2 then null else case when (0 = (#1{y} - 2)) then #0{x} else 120 end end, #1{y}, #1{y}, case when #2 then null else case when (#1{y} = 0) then #0{x} else 100 end end, case when #2 then null else case when (#1{y} = 0) then #0{x} else 110 end end), row(#0{x}, #1{y}))]) ReadStorage materialize.public.t9 Source materialize.public.t9 Target cluster: quickstart EOF query IIIIIIII SELECT x, y, lag(x,y,100) OVER (PARTITION BY x ORDER BY y+y), lag(x,y,110) OVER (PARTITION BY x ORDER BY y+y), lag(x,y,100) OVER (PARTITION BY x ORDER BY y+y), first_value(y) OVER (PARTITION BY x ORDER BY y+y), last_value(y) OVER (PARTITION BY x ORDER BY y+y), lead(x,y-2,120) OVER (PARTITION BY x ORDER BY y+y) FROM t9 ORDER BY x; ---- 1 2 100 110 100 2 2 1 3 NULL NULL NULL NULL NULL NULL NULL 5 6 100 110 100 6 6 120 7 8 100 110 100 8 8 120 9 NULL NULL NULL NULL NULL NULL NULL 11 NULL NULL NULL NULL NULL NULL NULL 13 14 100 110 100 14 14 120 15 16 100 110 100 16 16 120 17 18 100 110 100 18 18 120 ## Complex tests with array_agg and then unnesting the array query R SELECT avg(u) FROM ( SELECT x, unnest(l) AS u FROM ( SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l FROM t7 ) AS ff1 ) AS ff2 GROUP BY x%5 ORDER BY x%5; ---- -17 2 13 14 NULL query IBRR WITH array_agg_unnest_grouped_agg AS ( SELECT x%5 AS k1, avg(u) AS avg1 FROM ( SELECT x, unnest(l) AS u FROM ( SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l FROM t7 ) AS ff1 ) AS ff2 GROUP BY x%5 ), grouped_agg AS ( SELECT x%5 AS k2, avg(y) AS avg2 FROM t7 GROUP BY x%5 ) SELECT k1, avg1 = avg2, avg1, avg2 FROM array_agg_unnest_grouped_agg, grouped_agg WHERE k1 = k2 AND avg1 IS NOT NULL ORDER BY k1; ---- 0 true -17 -17 1 true 2 2 2 true 13 13 3 true 14 14 query I WITH array_agg AS ( SELECT x%5, array_agg(y) OVER (PARTITION BY x%5) AS l FROM t7 ) SELECT s FROM array_agg, LATERAL ( SELECT sum(ul) AS s FROM (SELECT unnest(l) AS ul FROM array_agg) AS sq1 ) AS sq2; ---- -188 -188 -188 -188 -188 -188 -188 -188 -188 -188 -188 query IRRB WITH simple AS ( SELECT x%5 AS x5_simple, avg(y) OVER (PARTITION BY x%5) AS avg_simple FROM t7 ), complicated AS ( -- array_agg, then do an unnest and global agg in a subquery in a SELECT WITH array_agg AS ( SELECT x%5 AS x5, array_agg(y) OVER (PARTITION BY x%5) AS l FROM t7 ) SELECT x5 AS x5_complicated, ( SELECT avg(uy) FROM unnest(l) AS uy ) AS avg_complicated FROM array_agg ) SELECT DISTINCT x5_simple, avg_simple, avg_complicated, avg_simple = avg_complicated FROM simple, complicated WHERE x5_simple = x5_complicated ORDER BY x5_simple; ---- 0 -17 -17 true 1 2 2 true 2 13 13 true 3 14 14 true 4 NULL NULL NULL # Regression test for https://github.com/MaterializeInc/materialize/pull/22270#issuecomment-1761432124 statement ok SET cluster_replica = r1 statement error db error: ERROR: Evaluation error: "4294967295 \* 2" uint4 out of range select max(1) over (partition by 1 order by 1, 1) from (select subq_2."c9" as c0 from (select subq_1."c0" as c0, subq_1."c2" as c1, subq_1."c4" as c2, subq_1."c2" as c3, subq_1."c1" as c4, subq_1."c0" as c5, subq_1."c5" as c6, subq_1."c0" as c7, subq_1."c5" as c8, subq_1."c1" as c9, subq_1."c2" as c10, 87 as c11, subq_1."c1" as c12 from (select subq_0."c1" as c0, subq_0."c0" as c1, subq_0."c1" as c2, subq_0."c0" as c3, subq_0."c1" as c4, subq_0."c0" as c5 from (select ref_0."form_of_use" as c0, ref_0."default_collate_schema" as c1 from information_schema.character_sets as ref_0 ) as subq_0 ) as subq_1 ) as subq_2 ) as subq_3 where (case when (((select "collection_timestamp" from mz_internal.mz_storage_usage_by_shard limit 1 offset 4) ) < ((select pg_catalog.min("occurred_at") from mz_internal.mz_source_status_history) )) and ((case when subq_3."c0" is NULL then TIMESTAMPTZ '2023-01-01 01:23:45+06' else TIMESTAMPTZ '2023-01-01 01:23:45+06' end ) <> (pg_catalog.now())) then case when (((select pg_catalog.sum("connection_id") from mz_internal.mz_sessions) ) - (0::uint8)) >= ((select "worker_id" from mz_introspection.mz_arrangement_sharing_per_worker limit 1 offset 3) ) then numrange(0,0) else numrange(0,0) end else case when (((select pg_catalog.sum("connection_id") from mz_internal.mz_sessions) ) - (0::uint8)) >= ((select "worker_id" from mz_introspection.mz_arrangement_sharing_per_worker limit 1 offset 3) ) then numrange(0,0) else numrange(0,0) end end ) >= (case when (0::uint4) > (pg_catalog.mod( CAST((select 1 from mz_internal.mz_subscriptions limit 1 offset 2) as uint4), CAST((4294967295::uint4) * (2::uint4) as uint4))) then case when (mz_catalog.map_length( mz_internal.mz_role_oid_memberships())) = ((select pg_catalog.min("rolconnlimit") from pg_catalog.pg_roles) ) then case when (null::uint4) = (null::uint4) then numrange(0,0) else numrange(0,0) end else case when (null::uint4) = (null::uint4) then numrange(0,0) else numrange(0,0) end end else case when (mz_catalog.map_length( mz_internal.mz_role_oid_memberships())) = ((select pg_catalog.min("rolconnlimit") from pg_catalog.pg_roles) ) then case when (null::uint4) = (null::uint4) then numrange(0,0) else numrange(0,0) end else case when (null::uint4) = (null::uint4) then numrange(0,0) else numrange(0,0) end end end ); ## Tests for window function fusion query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT *, lag(x) OVER (ORDER BY x), first_value(x) OVER (ORDER BY x), last_value(x) OVER (ORDER BY x), x*y, lag(y) OVER (ORDER BY x), lag(x+x,1,null) OVER (PARTITION BY x ORDER BY y), lead(x,2,null) OVER (PARTITION BY x ORDER BY -y NULLS FIRST), lead(x,2,null) OVER (PARTITION BY x ORDER BY -y NULLS LAST), x+y, lag(x+x,2,null) OVER (PARTITION BY x ORDER BY y), sum(x) OVER (ORDER BY x), min(x) OVER (ORDER BY x), max(x) OVER (ORDER BY y) FROM t7 ORDER BY x,y; ---- Explained Query: Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#14] Project (#3, #4, #16, #15, #14, #17, #13, #9, #11, #10, #18, #8, #7, #6, #5) Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[2](#2), record_get[4](#2), record_get[5](#2), record_get[7](#2), record_get[8](#2), record_get[10](#2), record_get[12](#2), record_get[0](#1), record_get[0](#12), record_get[1](#12), record_get[2](#12), record_get[3](#12), (#3{x} * #4{y}), (#3{x} + #4{y})) FlatMap unnest_list(#0{fused_value_window_func}) Reduce aggregates=[fused_value_window_func[lag[order_by=[#0 asc nulls_last]], last_value[order_by=[#0 asc nulls_last]], first_value[order_by=[#0 asc nulls_last]], lag[order_by=[#0 asc nulls_last]] order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[4](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[6](record_get[1](#0)), record_get[7](record_get[1](#0)), record_get[8](record_get[1](#0)), record_get[9](record_get[1](#0)), record_get[10](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), row(row(record_get[1](record_get[1](#0)), 1, null), record_get[0](record_get[1](#0)), record_get[0](record_get[1](#0)), row(record_get[0](record_get[1](#0)), 1, null))), record_get[0](record_get[1](#0))))] Project (#1) FlatMap unnest_list(#0{lead}) Project (#1{lead}) Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[lead[order_by=[#0 asc nulls_first]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[4](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[6](record_get[1](#0)), record_get[7](record_get[1](#0)), record_get[8](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), row(record_get[0](record_get[1](#0)), 2, null)), -(record_get[1](record_get[1](#0)))))] Project (#1) FlatMap unnest_list(#0{lead}) Project (#1{lead}) Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[lead[order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[4](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[0](#0), record_get[0](record_get[0](#0)), record_get[1](record_get[0](#0))), row(record_get[0](record_get[1](#0)), 2, null)), -(record_get[1](record_get[1](#0)))))] Project (#1) FlatMap unnest_list(#0{fused_value_window_func}) Project (#1{fused_value_window_func}) Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[fused_value_window_func[lag[order_by=[#0 asc nulls_last]], lag[order_by=[#0 asc nulls_last]] order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[0](#0), record_get[0](record_get[0](#0)), record_get[1](record_get[0](#0))), row(row((record_get[0](record_get[1](#0)) + record_get[0](record_get[1](#0))), 2, null), row((record_get[0](record_get[1](#0)) + record_get[0](record_get[1](#0))), 1, null))), record_get[1](record_get[1](#0))))] Project (#1) FlatMap unnest_list(#0{fused_window_agg}) Reduce aggregates=[fused_window_agg(row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), row(record_get[0](record_get[1](#0)), record_get[0](record_get[1](#0)))), record_get[0](record_get[1](#0))))] Project (#1) FlatMap unnest_list(#0{window_agg}) Reduce aggregates=[window_agg[max order_by=[#0{x} asc nulls_last]](row(row(row(#0{x}, #1{y}), #0{x}), #1{y}))] ReadStorage materialize.public.t7 Source materialize.public.t7 Target cluster: quickstart EOF query IIIIIIIIIIIIIII SELECT *, lag(x) OVER (ORDER BY x), first_value(x) OVER (ORDER BY x), last_value(x) OVER (ORDER BY x), x*y, lag(y) OVER (ORDER BY x), lag(x+x,1,null) OVER (PARTITION BY x ORDER BY y), lead(x,2,null) OVER (PARTITION BY x ORDER BY -y NULLS FIRST), lead(x,2,null) OVER (PARTITION BY x ORDER BY -y NULLS LAST), x+y, lag(x+x,2,null) OVER (PARTITION BY x ORDER BY y), sum(x) OVER (ORDER BY x), min(x) OVER (ORDER BY x), max(x) OVER (ORDER BY y) FROM t7 ORDER BY x,y; ---- 1 2 NULL 1 1 2 NULL NULL NULL NULL 3 NULL 1 1 10 3 NULL 1 1 3 NULL 2 NULL NULL NULL NULL NULL 4 1 17 5 6 3 1 5 30 NULL NULL NULL NULL 11 NULL 9 1 10 7 8 5 1 7 56 6 NULL NULL NULL 15 NULL 16 1 10 9 NULL 7 1 9 NULL 8 NULL NULL NULL NULL NULL 25 1 17 10 -50 9 1 10 -500 NULL NULL NULL NULL -40 NULL 45 1 10 10 -40 10 1 10 -400 -50 20 NULL NULL -30 NULL 45 1 10 11 NULL 10 1 11 NULL -40 NULL NULL NULL NULL NULL 56 1 17 13 14 11 1 13 182 NULL NULL NULL NULL 27 NULL 69 1 13 15 16 13 1 15 240 14 NULL NULL NULL 31 NULL 84 1 15 17 18 15 1 17 306 16 NULL NULL NULL 35 NULL 101 1 17 # In the following query, currently we only fuse the two inner `lag`s. # In theory, we could also fuse the two `last_value`s. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT *, first_value( lag(x*x,y,1111) OVER (PARTITION BY x ORDER BY y) ) OVER (PARTITION BY x ORDER BY y), x*y, last_value( lag(x*x,y,2222) OVER (PARTITION BY x ORDER BY y) ) OVER (PARTITION BY x ORDER BY y+y), last_value(x+y) OVER (PARTITION BY x ORDER BY y+y), lag(y) OVER (ORDER BY x), x+y FROM t7 ORDER BY x,y; ---- Explained Query: Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#7] Project (#3, #4, #8, #9, #7, #6, #5, #10) Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[2](#2), record_get[3](#2), record_get[8](#2), record_get[0](#1), (#3{x} * #4{y}), (#3{x} + #4{y})) FlatMap unnest_list(#0{first_value}) Project (#1{first_value}) Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[first_value[order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[4](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[6](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), record_get[6](record_get[1](#0))), record_get[1](record_get[1](#0))))] Project (#1) FlatMap unnest_list(#0{last_value}) Project (#1{last_value}) Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[last_value[order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[5](record_get[1](#0)), record_get[0](#0), record_get[0](record_get[0](#0)), record_get[1](record_get[0](#0))), record_get[0](record_get[0](#0))), (record_get[1](record_get[1](#0)) + record_get[1](record_get[1](#0)))))] Project (#1) FlatMap unnest_list(#0{fused_value_window_func}) Project (#1{fused_value_window_func}) Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[fused_value_window_func[lag[order_by=[#0 asc nulls_last]], lag[order_by=[#0 asc nulls_last]] order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[2](record_get[1](#0)), record_get[3](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), row(row((record_get[0](record_get[1](#0)) * record_get[0](record_get[1](#0))), record_get[1](record_get[1](#0)), 2222), row((record_get[0](record_get[1](#0)) * record_get[0](record_get[1](#0))), record_get[1](record_get[1](#0)), 1111))), record_get[1](record_get[1](#0))))] Project (#1) FlatMap unnest_list(#0{last_value}) Project (#1{last_value}) Reduce group_by=[record_get[0](record_get[1](#0))] aggregates=[last_value[order_by=[#0 asc nulls_last]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[0](#0), record_get[0](#0)), (record_get[0](record_get[1](#0)) + record_get[1](record_get[1](#0)))), (record_get[1](record_get[1](#0)) + record_get[1](record_get[1](#0)))))] Project (#1) FlatMap unnest_list(#0{lag}) Reduce aggregates=[lag[order_by=[#0{x} asc nulls_last]](row(row(row(#0{x}, #1{y}), row(#1{y}, 1, null)), #0{x}))] ReadStorage materialize.public.t7 Source materialize.public.t7 Target cluster: quickstart EOF query IIIIIIII SELECT *, first_value( lag(x*x,y,1111) OVER (PARTITION BY x ORDER BY y) ) OVER (PARTITION BY x ORDER BY y), x*y, last_value( lag(x*x,y,2222) OVER (PARTITION BY x ORDER BY y) ) OVER (PARTITION BY x ORDER BY y+y), last_value(x+y) OVER (PARTITION BY x ORDER BY y+y), lag(y) OVER (ORDER BY x), x+y FROM t7 ORDER BY x,y; ---- 1 2 1111 2 2222 3 NULL 3 3 NULL NULL NULL NULL NULL 2 NULL 5 6 1111 30 2222 11 NULL 11 7 8 1111 56 2222 15 6 15 9 NULL NULL NULL NULL NULL 8 NULL 10 -50 1111 -500 2222 -40 NULL -40 10 -40 1111 -400 2222 -30 -50 -30 11 NULL NULL NULL NULL NULL -40 NULL 13 14 1111 182 2222 27 NULL 27 15 16 1111 240 2222 31 14 31 17 18 1111 306 2222 35 16 35 query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT *, lag(x) OVER (), lag(y) OVER (), sum(x) OVER (), min(x) OVER () FROM t7 ORDER BY x,y; ---- Explained Query: Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#5] Project (#3, #4, #9, #8, #6, #5) Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[3](#2), record_get[4](#2), record_get[0](#1), record_get[0](#7), record_get[1](#7)) FlatMap unnest_list(#0{fused_value_window_func}) Reduce aggregates=[fused_value_window_func[lag[order_by=[]], lag[order_by=[]] order_by=[]](row(row(row(record_get[0](record_get[1](#0)), record_get[1](record_get[1](#0)), record_get[0](#0), record_get[0](record_get[0](#0)), record_get[1](record_get[0](#0))), row(row(record_get[1](record_get[1](#0)), 1, null), row(record_get[0](record_get[1](#0)), 1, null)))))] Project (#1) FlatMap unnest_list(#0{fused_window_agg}) Reduce aggregates=[fused_window_agg(row(row(row(#0{x}, #1{y}), row(#0{x}, #0{x}))))] ReadStorage materialize.public.t7 Source materialize.public.t7 Target cluster: quickstart EOF # Test the situation when the MFP above is fused into the Reduce, and the MFP can error. query III SELECT * FROM ( SELECT *, lag(x) OVER (ORDER BY x) AS l FROM t7 ) WHERE l/2 < 7 ORDER BY x,y,l; ---- 3 NULL 1 5 6 3 7 8 5 9 NULL 7 10 -50 9 10 -40 10 11 NULL 10 13 14 11 15 16 13 # Test the situation when the MFP above is fused into the Reduce, but the MFP can't error. query III SELECT * FROM ( SELECT *, lag(x) OVER (ORDER BY x) AS l FROM t7 ) WHERE l < 14 ORDER BY x,y,l; ---- 3 NULL 1 5 6 3 7 8 5 9 NULL 7 10 -50 9 10 -40 10 11 NULL 10 13 14 11 15 16 13 ## Check some LIR plans that the optimization of fusing `Reduce` with `FlatMap UnnestList` happens. ## https://github.com/MaterializeInc/materialize/pull/29554 ## These should show `fused_unnest_list=true`. # Simple situation query T multiline EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT lead(x,y-2,120) OVER (ORDER BY x) FROM t9; ---- Explained Query: Mfp project=(#1) map=(record_get[0](#0)) input_key= Reduce::Basic aggr=(0, lead[order_by=[#0 asc nulls_last]](row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x})), fused_unnest_list=true) val_plan project=(#2) map=(row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x})) key_plan project=() Get::PassArrangements materialize.public.t9 raw=true Source materialize.public.t9 Target cluster: quickstart EOF # PARTITION BY query T multiline EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT lead(x,y-2,120) OVER (PARTITION BY x/2 ORDER BY x) FROM t9; ---- Explained Query: Mfp project=(#2) map=(record_get[0](#1)) input_key=#0 Reduce::Basic aggr=(0, lead[order_by=[#0 asc nulls_last]](row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x})), fused_unnest_list=true) val_plan project=(#2) map=(row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x})) key_plan project=(#2) map=((#0{x} / 2)) Get::PassArrangements materialize.public.t9 raw=true Source materialize.public.t9 Target cluster: quickstart EOF # PARTITION BY multiple columns query T multiline EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT lead(x,y-2,120) OVER (PARTITION BY x/2, y ORDER BY x) FROM t9; ---- Explained Query: Mfp project=(#3) map=(record_get[0](#2)) input_key=#0, #1 Reduce::Basic aggr=(0, lead[order_by=[#0 asc nulls_last]](row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x})), fused_unnest_list=true) val_plan project=(#2) map=(row(row(row(#0, #1), row(#0{x}, (#1{y} - 2), 120)), #0{x})) key_plan project=(#2, #1) map=((#0{x} / 2)) Get::PassArrangements materialize.public.t9 raw=true Source materialize.public.t9 Target cluster: quickstart EOF # row_number() query T multiline EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT row_number() OVER (ORDER BY x), x FROM t7 ORDER BY row_number ---- Explained Query: Finish order_by=[#0 asc nulls_last] output=[#0, #1] Mfp project=(#2, #1) map=(record_get[0](record_get[1](#0)), record_get[0](#0)) input_key= Reduce::Basic aggr=(0, row_number[order_by=[#0 asc nulls_last]](row(list[row(#0, #1)], #0{x})), fused_unnest_list=true) val_plan project=(#2) map=(row(list[row(#0, #1)], #0{x})) key_plan project=() Get::PassArrangements materialize.public.t7 raw=true Source materialize.public.t7 Target cluster: quickstart EOF # MFP after is fused into the Reduce query T multiline EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT * FROM ( SELECT *, lag(x) OVER (ORDER BY x) AS l FROM t7 ) WHERE l < 14 ORDER BY x,y,l; ---- Explained Query: Finish order_by=[#0 asc nulls_last, #1 asc nulls_last, #2 asc nulls_last] output=[#0..=#2] Mfp project=(#3, #4, #1) map=(record_get[0](#0), record_get[1](#0), record_get[0](#2), record_get[1](#2)) input_key= Reduce::Basic aggr=(0, lag[order_by=[#0 asc nulls_last]](row(row(row(#0, #1), row(#0{x}, 1, null)), #0{x})), fused_unnest_list=true) val_plan project=(#2) map=(row(row(row(#0, #1), row(#0{x}, 1, null)), #0{x})) key_plan project=() mfp_after filter=((record_get[0](#0) < 14)) Get::PassArrangements materialize.public.t7 raw=true Source materialize.public.t7 Target cluster: quickstart EOF # Two lags fused with each other + Reduce-FlatMap fusion. query T multiline EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT *, lag(x) OVER (), lag(y) OVER () FROM t7 ORDER BY x,y; ---- Explained Query: Finish order_by=[#0 asc nulls_last, #1 asc nulls_last] output=[#0..=#3] Mfp project=(#2, #3, #6, #5) map=(record_get[1](#0), record_get[0](#1), record_get[1](#1), record_get[0](#0), record_get[0](#4), record_get[1](#4)) input_key= Reduce::Basic aggr=(0, fused_value_window_func[lag[order_by=[]], lag[order_by=[]] order_by=[]](row(row(row(#0, #1), row(row(#1{y}, 1, null), row(#0{x}, 1, null))))), fused_unnest_list=true) val_plan project=(#2) map=(row(row(row(#0, #1), row(row(#1{y}, 1, null), row(#0{x}, 1, null))))) key_plan project=() Get::PassArrangements materialize.public.t7 raw=true Source materialize.public.t7 Target cluster: quickstart EOF ## Window functions on big relations. statement ok CREATE TABLE t10(t timestamptz, d1 int, d2 text, d3 int, d4 uint8, d5 int, d6 text); statement ok INSERT INTO t10 SELECT generate_series(1,1000)::mz_timestamp::timestamptz, 5, 'aaa', 7, 8, 9, 'bbb'; statement ok CREATE VIEW v1 AS SELECT t, d1, d2, EXTRACT(MILLISECOND FROM t) * 91 % 1223 AS d3, d4, d5, d6 FROM t10; statement ok CREATE MATERIALIZED VIEW mv1 AS SELECT t, d1, d2, d3, d4, d6, lag(d1) OVER (ORDER BY t) - d1 AS r FROM v1; query I SELECT sum(r) FROM mv1; ---- 0 statement ok CREATE MATERIALIZED VIEW mv2 AS SELECT *, row_number() OVER (ORDER BY t) AS rn FROM mv1; query I SELECT sum(r) FROM ( SELECT rn - lag(rn) OVER (ORDER BY t) AS r FROM mv2 ); ---- 999 query I SELECT sum(lv) FROM ( SELECT last_value(rn) OVER () AS lv FROM mv2 ); ---- 1000000 statement ok DELETE FROM t10 statement ok INSERT INTO t10 SELECT generate_series(1,70000)::mz_timestamp::timestamptz, 5, 'aaa', 7, 8, 9, 'bbb'; query I SELECT sum(r) FROM mv1; ---- 0 query I SELECT sum(r) FROM ( SELECT rn - lag(rn) OVER (ORDER BY t) AS r FROM mv2 ); ---- 69999 ## QUALIFY query II SELECT * FROM t6 QUALIFY row_number() OVER (PARTITION BY x%3 ORDER BY y) = 1; ---- 1 2 5 6 15 16 query IIII rowsort SELECT *, x%3, row_number() OVER (PARTITION BY x%3 ORDER BY y) FROM t6 QUALIFY row_number() OVER (PARTITION BY x%3 ORDER BY y) = 1; ---- 15 16 0 1 1 2 1 1 5 6 2 1 query II SELECT * FROM t6 QUALIFY sum(x) OVER (ORDER BY y) = 26; ---- 13 14 query II SELECT * FROM (SELECT * FROM t6 UNION ALL SELECT * FROM t6) QUALIFY sum(x) OVER (ORDER BY y) = 26; ---- 7 8 7 8 query I SELECT avg(u) FROM ( SELECT x, unnest(l) AS u FROM ( SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l FROM t7 ) AS ff1 ) AS ff2 GROUP BY x%5 QUALIFY sum(avg(u)) OVER (ORDER BY x%5) = -15 ORDER BY x%5; ---- 2 query II SELECT * FROM ( SELECT x, unnest(l) AS u FROM ( SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l FROM t7 ) AS ff1 ) QUALIFY x-lag(x) OVER (ORDER BY x) = 0 AND lag(x) OVER (ORDER BY x) - lag(lag(x) OVER (ORDER BY x)) OVER (ORDER BY x) = 0 ORDER BY x,u; ---- 5 6 5 16 10 -40 10 -40 10 6 10 6 10 16 10 16 15 6 15 16 query III SELECT *, sum(x-9) OVER (ORDER BY x, u) FROM ( SELECT DISTINCT * FROM ( SELECT x, unnest(l) AS u FROM ( SELECT x, array_agg(y) OVER (PARTITION BY x%5) AS l FROM t7 ) AS ff1 ) QUALIFY x-lag(x) OVER (ORDER BY x) = 0 AND lag(x) OVER (ORDER BY x) - lag(lag(x) OVER (ORDER BY x)) OVER (ORDER BY x) = 0 ) QUALIFY sum(x-9) OVER (ORDER BY x, u) > -6 ORDER BY x,u; ---- 5 6 -4 10 16 -5 15 6 1 15 16 7