Materialize/test/sqllogictest/order_by.slt

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

mode cockroach

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

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

query I nosort
SELECT a FROM foo ORDER BY a
----
0
1
2

query I nosort
SELECT a FROM foo ORDER BY a DESC
----
2
1
0

query I nosort
SELECT a + 1 FROM foo ORDER BY a + 1
----
1
2
3

query I nosort
SELECT 2*a as d
FROM foo
ORDER BY d;
----
0
2
4

query I nosort
SELECT 2*a as d
FROM foo
ORDER BY -2*a;
----
4
2
0

# This looks a bit weird if we compare it with the previous two tests, but Postgres does the same
# See https://www.postgresql.org/docs/current/queries-order.html
# "Note that an output column name has to stand alone, that is, it cannot be used in an expression"
query error db error: ERROR: column "d" does not exist
SELECT 2*a as d
FROM foo
ORDER BY -d;

query I nosort
SELECT sum(a) FROM foo ORDER BY sum(a)
----
3

query error db error: ERROR: column "foo\.a" must appear in the GROUP BY clause or be used in an aggregate function
SELECT a FROM foo ORDER BY sum(a);

query error db error: ERROR: column "foo\.a" must appear in the GROUP BY clause or be used in an aggregate function
SELECT sum(a) FROM foo ORDER BY a;

query error db error: ERROR: column "foo\.b" must appear in the GROUP BY clause or be used in an aggregate function
SELECT sum(a) FROM foo ORDER BY sum(a), b;

query error db error: ERROR: column "foo\.b" must appear in the GROUP BY clause or be used in an aggregate function
SELECT sum(a), b FROM foo ORDER BY sum(a), b;

query I nosort
SELECT sum(a)
FROM foo
GROUP BY b
ORDER BY sum(a), b;
----
0
1
2

query I nosort
SELECT sum(a)
FROM foo
GROUP BY b
ORDER BY -sum(a), b;
----
2
1
0

query I nosort
SELECT right_a
FROM foo LEFT JOIN (SELECT a as right_a FROM foo WHERE a<2) ON foo.a = right_a
GROUP BY b, right_a
ORDER BY -right_a, -sum(foo.a), b;
----
1
0
NULL

query IIT nosort
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
ORDER BY lag(10*right_a+3) OVER (ORDER BY right_a NULLS FIRST), -sum(foo.a), b;
----
3  -1  one
NULL  -2  two
NULL  0  zero

query IT nosort
SELECT -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
ORDER BY lag(10*right_a+3) OVER (ORDER BY right_a NULLS FIRST), -sum(foo.a), b;
----
-1  one
-2  two
0  zero

query IIT nosort
SELECT lag(10*right_a+3) OVER (ORDER BY right_a NULLS FIRST), sum(foo.a), length(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
ORDER BY lag(10*right_a+3) OVER (ORDER BY right_a NULLS FIRST), -sum(foo.a)/10, length(b) DESC NULLS FIRST;
----
3  1  3
NULL  0  4
NULL  2  3

query I nosort
SELECT 10-sum(a)
FROM foo
GROUP BY b
ORDER BY 1;
----
8
9
10

query error db error: ERROR: column reference 2 in ORDER BY clause is out of range \(1 \- 1\)
SELECT 10-sum(a)
FROM foo
GROUP BY b
ORDER BY 2;

query II nosort
SELECT 10-sum(a), lag(sum(a)) OVER (ORDER BY sum(a))
FROM foo
GROUP BY b
ORDER BY 2;
----
9  0
8  1
10  NULL

query error db error: ERROR: column reference 3 in ORDER BY clause is out of range \(1 \- 2\)
SELECT 10-sum(a), lag(sum(a)) OVER (ORDER BY sum(a))
FROM foo
GROUP BY b
ORDER BY 3;

query II
SELECT a, lag(a) OVER (ORDER BY a) as d
FROM foo
ORDER BY d;
----
1  0
2  1
0  NULL

# When an ORDER BY refers to an output column name, the column name can't be part of a bigger expression.
# (This is the same in Postgres.)
query error db error: ERROR: column "d" does not exist
SELECT a, lag(a) OVER (ORDER BY a) as d
FROM foo
ORDER BY -d;

query error db error: ERROR: column "d" does not exist
SELECT a, 2*a as d
FROM foo
ORDER BY lag(d) OVER (ORDER BY d);

query I nosort
SELECT a FROM foo ORDER BY (0-a)
----
2
1
0

# ORDER BY can implicitly project columns from the inner SELECT...
query I
SELECT a FROM foo ORDER BY b
----
1
2
0

# ...including when they are used in functions...
query I
SELECT a FROM foo ORDER BY b || 'blah'
----
1
2
0

# ...or even in aggregate functions...
query I
SELECT a FROM foo GROUP BY a ORDER BY max(b)
----
1
2
0

# ...unless you use DISTINCT...
query error for SELECT DISTINCT, ORDER BY expressions must appear in select list
SELECT DISTINCT a FROM foo ORDER BY b

query error for SELECT DISTINCT, ORDER BY expressions must appear in select list
SELECT DISTINCT a FROM foo ORDER BY a + 1

# ...or a set expression.
query error column "b" does not exist
(SELECT a FROM foo) UNION (SELECT a FROM foo) ORDER BY b

# We should pick up the column name from the first input of UNION.
query I
(SELECT a FROM foo)
UNION
(SELECT a-3 FROM foo)
ORDER BY a;
----
-3
-2
-1
0
1
2

query I
(SELECT a FROM foo)
UNION
(SELECT a FROM foo)
ORDER BY a;
----
0
1
2

query error ERROR: column "a" does not exist
(SELECT a-3 FROM foo)
UNION
(SELECT a FROM foo)
ORDER BY a;

# We support complex expressions in the ORDER BY of a set expression (Postgres doesn't).
query I
(SELECT a FROM foo)
UNION
(SELECT a-3 FROM foo)
ORDER BY -2*a+3;
----
2
1
0
-1
-2
-3

# But we don't support aggregations in the ORDER BY of a set expression (same in Postgres)
query error db error: ERROR: aggregate functions are not allowed in ORDER BY clause of a set expression \(function pg_catalog\.sum\)
(SELECT a FROM foo)
UNION
(SELECT a-3 FROM foo)
ORDER BY sum(a);

# ... or window functions (same in Postgres).
query error db error: ERROR: window functions are not allowed in ORDER BY clause of a set expression \(function pg_catalog\.lag\)
(SELECT a FROM foo)
UNION
(SELECT a-3 FROM foo)
ORDER BY lag(a) OVER ();

# We support window functions in a normal ORDER BY, though
query I
SELECT a FROM foo
ORDER BY lag(a) OVER (ORDER BY a) NULLS LAST;
----
1
2
0

query I
SELECT a FROM foo
ORDER BY lag(a) OVER (ORDER BY -a NULLS FIRST) DESC NULLS LAST;
----
1
0
2

# We support subqueries in ORDER BY
query IT
SELECT a, b FROM foo
ORDER BY a IN (SELECT length(b)-1 FROM foo), -a;
----
1  one
0  zero
2  two

# ... even for a set expression
query I
(SELECT a FROM foo)
UNION
(SELECT a+1 FROM foo)
ORDER BY a IN (SELECT length(b)-1 FROM foo), -a;
----
1
0
3
2

# Using a column twice and referring to it by its alias in the ORDER BY should
# work.
query II
SELECT a, a AS c FROM foo ORDER BY c
----
0  0
1  1
2  2

# Columns introduced in the select list are not available when using an
# arbitrary ORDER BY expression.
query error column "c" does not exist
SELECT a + 1 AS c FROM foo ORDER BY a + c

# When a name from the underlying table is shadowed, using the shadowed name in
# the ORDER BY should refer to the column in the select list...
query T
SELECT b AS a FROM foo ORDER BY a
----
one
two
zero

# ...unless the shadowed name is ambiguous...
query error column reference "a" is ambiguous
SELECT 1 AS a, b AS a FROM foo ORDER BY a

# ..or the ORDER BY is an arbitrary expression.
query T
SELECT b AS a FROM foo ORDER BY -a
----
two
one
zero

statement ok
CREATE TABLE bar (a int)

statement ok
INSERT INTO bar (a) VALUES (1)

query I nosort
SELECT a FROM foo ORDER BY exists (SELECT * FROM bar WHERE bar.a = foo.a), a
----
0
2
1

query TI nosort
SELECT b, a FROM foo ORDER BY b
----
one  1
two  2
zero 0

# Ensure SELECT DISTINCT is not confused by permuting columns in the SELECT
# list.
query TI nosort
SELECT DISTINCT b, a FROM foo ORDER BY b
----
one  1
two  2
zero 0

# LIMIT (or FETCH) and OFFSET should be accepted in any order. (Postgres also does, and Monte Carlo needs this.)
query TI
SELECT b, a
FROM foo
ORDER BY b
LIMIT 2;
----
one  1
two  2

query TI
SELECT b, a
FROM foo
ORDER BY b
FETCH FIRST 2 ROWS ONLY;
----
one  1
two  2

query TI
SELECT b, a
FROM foo
ORDER BY b
LIMIT 2 OFFSET 1;
----
two  2
zero  0

query TI
SELECT b, a
FROM foo
ORDER BY b
FETCH FIRST 2 ROWS ONLY OFFSET 1;
----
two  2
zero  0

query TI
SELECT b, a
FROM foo
ORDER BY b
OFFSET 1 LIMIT 1;
----
two  2

query TI
SELECT b, a
FROM foo
ORDER BY b
OFFSET 1;
----
two  2
zero  0

# FETCH FIRST and FETCH NEXT mean the same thing.
query TI
SELECT b, a
FROM foo
ORDER BY b
OFFSET 1
FETCH FIRST 1 ROWS ONLY;
----
two  2

query TI
SELECT b, a
FROM foo
ORDER BY b
OFFSET 1
FETCH NEXT 1 ROWS ONLY;
----
two  2

# OFFSET can have optional ROW or ROWS (which doesn't mean anything)
query TI
SELECT b, a
FROM foo
ORDER BY b
OFFSET 1 ROW LIMIT 3;
----
two  2
zero  0

query TI
SELECT b, a
FROM foo
ORDER BY b
OFFSET 1 ROWS LIMIT 3;
----
two  2
zero  0

# Multiple LIMIT/FETCH or multiple OFFSET not allowed
query error multiple LIMIT/FETCH clauses not allowed
SELECT b, a
FROM foo
ORDER BY b
LIMIT 1 LIMIT 2;

query error multiple LIMIT/FETCH clauses not allowed
SELECT b, a
FROM foo
ORDER BY b
LIMIT 1
FETCH FIRST 3 ROWS ONLY;

query error multiple OFFSET clauses not allowed
SELECT b, a
FROM foo
ORDER BY b
OFFSET 1 OFFSET 2;

query error multiple LIMIT/FETCH clauses not allowed
SELECT b, a
FROM foo
ORDER BY b
LIMIT 1 OFFSET 1 LIMIT 2;

query error multiple OFFSET clauses not allowed
SELECT b, a
FROM foo
ORDER BY b
OFFSET 1 LIMIT 3 OFFSET 2;

query error multiple OFFSET clauses not allowed
SELECT b, a
FROM foo
ORDER BY b
OFFSET 1 OFFSET 2 LIMIT 3;

### sorts, limits, and offsets in subqueries ###

# These tests have been designed to cover a wide range of situations where there
# may be a subquery. Be sure when modifying these tests to maintain a
# representation for each situation.

statement ok
CREATE TABLE fizz(a int, b text)

statement ok
INSERT INTO fizz(a, b) VALUES
    (2079, 'thirteen'), (12345, 'one'),
    (12345, 'two'), (12345, 'three'),
    (6745, 'five'), (24223, 'four'),
    (21243, 'four'), (1735, 'two'),
    (25040, 'two')

# the ORDER BY's inside the subquery are technically meaningless because they do not
# propagate to the outer query, but we should still return correct results.
query T rowsort
SELECT b FROM (SELECT min(b) AS b FROM fizz GROUP BY a ORDER BY a DESC)
----
five
four
four
one
thirteen
two
two

query I rowsort
SELECT ascii(b) FROM (SELECT a, b FROM fizz ORDER BY a ASC, b DESC)
----
102
102
102
111
116
116
116
116
116

statement ok
CREATE TABLE baz (
    val1 int,
    val2 int
)

statement ok
INSERT INTO baz VALUES
    (12345, 1735), (12345, 1735), (12345, 1735),
    (1735, 24223), (12345, 12345), (2079, 24223),
    (1735, 2079), (1735, 2079), (1735, 2079)

# offset

query I rowsort
SELECT a FROM fizz WHERE a > ANY(SELECT val1 FROM baz ORDER BY val1 offset 3 ROWS)
----
12345
12345
12345
2079
21243
24223
25040
6745

query I rowsort
SELECT a FROM fizz WHERE a IN (SELECT val1 FROM baz ORDER BY val1 offset 0 rows)
----
12345
12345
12345
1735
2079

# limit
query I
SELECT a FROM fizz WHERE a < ALL(SELECT val1 FROM baz ORDER BY val1 DESC limit 5)
----
1735

query I
SELECT count(*) FROM fizz WHERE exists(SELECT val1 FROM baz ORDER BY val1 limit 0)
----
0

query error FETCH ... WITH TIES not yet supported
SELECT * FROM fizz FETCH FIRST 2 ROWS WITH TIES

# offset + limit
query TI
SELECT b, (SELECT val1 FROM baz WHERE val2 = a ORDER BY val1 limit 1 offset 1 rows) c
FROM fizz ORDER BY b, c DESC
----
five      NULL
four      NULL
four      2079
one       NULL
thirteen  1735
three     NULL
two       NULL
two       NULL
two       12345

# limit + offset return correct results when there are identical rows
query I
SELECT val1 FROM (SELECT val1, val2 FROM baz ORDER BY val2 LIMIT 2)
----
12345
12345

query I
SELECT val1 FROM (SELECT val1, val2 FROM baz ORDER BY val2 DESC OFFSET 7 ROWS)
----
12345
12345

query I rowsort
SELECT val1 FROM (SELECT val1, val2 FROM baz ORDER BY val2 LIMIT 2 OFFSET 2 ROWS)
----
1735
12345

query I
SELECT val1 FROM (SELECT val1, val2 FROM baz ORDER BY val2 DESC LIMIT 1 OFFSET 7 ROWS)
----
12345

# ORDER BY/limit/offset in subqueries still works after deleting some entries
# Note: the parentheses around the SELECT statement currently makes it a subquery
# test. Change the test if we optimize so that the SELECT is no longer treated
# as a subquery.
statement ok
CREATE VIEW bazv AS (SELECT val1, val2 FROM baz ORDER BY val2 DESC, val1 LIMIT 2 OFFSET 1 ROW)

query II rowsort
SELECT * FROM bazv
----
2079   24223
12345  12345

statement ok
DELETE FROM baz WHERE val1=12345

query II rowsort
SELECT * FROM bazv
----
1735   2079
2079   24223

### ORDER BY/offset/limit in toplevel select query in view creation ###

statement ok
CREATE VIEW fizzorderview AS SELECT a, b FROM fizz ORDER BY a DESC, b

# TODO: database-issues#236 take out the rowsort and rearrange results
# when ORDER BY's persist past the view creation
query IT rowsort
SELECT * FROM fizzorderview
----
12345 one
12345 three
12345 two
1735  two
2079  thirteen
6745  five
21243 four
24223 four
25040 two

statement ok
CREATE VIEW fizzlimitview AS SELECT a, b FROM fizz LIMIT 4

query II
SELECT count(a), count(b) FROM fizzlimitview
----
4 4

statement ok
CREATE VIEW fizzlimitview2 AS SELECT b, a FROM fizz ORDER BY a ASC LIMIT 2

query TI rowsort
SELECT * FROM fizzlimitview2
----
thirteen 2079
two      1735

statement ok
CREATE VIEW fizzoffsetview AS SELECT a, b FROM fizz OFFSET 6 ROWS

query II
SELECT count(b), count(a) FROM fizzoffsetview
----
3 3

statement ok
CREATE VIEW fizzoffsetview2 AS SELECT b, a FROM fizz ORDER BY b DESC, a OFFSET 3 ROWS

query TI rowsort
SELECT * FROM fizzoffsetview2
----
four     21243
four     24223
five     6745
one      12345
thirteen 2079
three    12345

statement ok
CREATE VIEW fizzlimitoffsetview AS SELECT sum(a) AS tot, b FROM fizz GROUP BY b
ORDER BY tot LIMIT 1 OFFSET 4 ROWS

query I
SELECT count(tot) FROM fizzlimitoffsetview
----
1

statement ok
CREATE VIEW fizzlimitoffsetview2 AS SELECT avg(a), b FROM fizz GROUP BY b
ORDER BY b DESC LIMIT 3 OFFSET 2 ROWS

query RT rowsort
SELECT * FROM fizzlimitoffsetview2
----
12345 one
2079  thirteen
22733 four

# delete and add an entry see how views update
statement ok
DELETE FROM fizz WHERE b = 'thirteen'

query IT rowsort
SELECT * FROM fizzorderview
----
12345 one
12345 three
12345 two
1735  two
21243 four
24223 four
25040 two
6745  five

query TI rowsort
SELECT * FROM fizzlimitview2
----
five     6745
two      1735

query II
SELECT count(b), count(a) FROM fizzoffsetview
----
2 2

query TI rowsort
SELECT * FROM fizzoffsetview2
----
five     6745
four     21243
four     24223
one      12345
three    12345

query RT rowsort
SELECT * FROM fizzlimitoffsetview2
----
12345 one
22733 four
6745  five

statement ok
DELETE FROM fizz WHERE b = 'five'

query II
SELECT count(a), count(b) FROM fizzlimitview
----
4 4

query I
SELECT count(tot) FROM fizzlimitoffsetview
----
0

statement ok
INSERT INTO fizz VALUES (7584, 'twelve'), (21758, 'fourteen')

query IT rowsort
SELECT * FROM fizzorderview
----
12345 one
12345 three
12345 two
1735  two
21243 four
21758 fourteen
24223 four
25040 two
7584  twelve

query II
SELECT count(a), count(b) FROM fizzlimitview
----
4 4

query TI rowsort
SELECT * FROM fizzlimitview2
----
twelve   7584
two      1735

query II
SELECT count(b), count(a) FROM fizzoffsetview
----
3 3

query TI rowsort
SELECT * FROM fizzoffsetview2
----
four     21243
four     24223
fourteen 21758
one      12345
three    12345
twelve   7584

query I
SELECT count(tot) FROM fizzlimitoffsetview
----
1

query RT rowsort
SELECT * FROM fizzlimitoffsetview2
----
12345 one
21758 fourteen
12345 three

# NULL should sort last by default. See database-issues#1844.
# NULL (default, default)
query I
SELECT * FROM (VALUES (NULL), (1)) ORDER BY column1
----
1
NULL

# NULL (asc, default)
query I
SELECT * FROM (VALUES (1), (NULL), (3), (2)) ORDER BY column1 asc
----
1
2
3
NULL

# NULL (desc, default)
query I
SELECT * FROM (VALUES (1), (NULL), (3), (2)) ORDER BY column1 desc
----
NULL
3
2
1

# NULL (default, last)
query I
SELECT * FROM (VALUES (1), (NULL), (3), (2)) ORDER BY column1 nulls last
----
1
2
3
NULL

# NULL (asc, last)
query I
SELECT * FROM (VALUES (1), (NULL), (3), (2)) ORDER BY column1 asc nulls last
----
1
2
3
NULL

# NULL (desc, last)
query I
SELECT * FROM (VALUES (1), (NULL), (3), (2)) ORDER BY column1 desc nulls last
----
3
2
1
NULL

# NULL (default, first)
query I
SELECT * FROM (VALUES (1), (NULL), (3), (2)) ORDER BY column1 nulls first
----
NULL
1
2
3

# NULL (asc, first)
query I
SELECT * FROM (VALUES (1), (NULL), (3), (2)) ORDER BY column1 asc nulls first
----
NULL
1
2
3

# NULL (desc, first)
query I
SELECT * FROM (VALUES (1), (NULL), (3), (2)) ORDER BY column1 desc nulls first
----
NULL
3
2
1

# Windows

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

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

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x TEXT);

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

query IT rowsort
SELECT row_number() OVER (ORDER BY x NULLS FIRST), x FROM t;
----
1 NULL
2 a
3 b
4 c

query IT rowsort
SELECT row_number() OVER (ORDER BY x NULLS LAST), x FROM t;
----
1 a
2 b
3 c
4 NULL

## TopK removal when it's completely covered by the finishing.
## See https://github.com/MaterializeInc/database-issues/issues/2498

statement ok
DROP TABLE t;

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

# We could remove the TopK, but we don't do this on the slow path currently.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM t LIMIT 10000) LIMIT 8000;
----
Explained Query:
  Finish limit=8000 output=[#0, #1]
    TopK limit=10000
      ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

statement ok
CREATE INDEX t_idx on t(x);

# Same as above, but for fast path recognition we already do the TopK removal.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM t LIMIT 10) LIMIT 8;
----
Explained Query (fast path):
  Finish limit=8 output=[#0, #1]
    ReadIndex on=materialize.public.t t_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_idx (fast path limit)

Target cluster: quickstart

EOF

# Same as above, but the finishing would need to be modified (merge the TopK into the finishing), because the TopK's
# LIMIT is smaller. We don't do this currently, but it wouldn't be too difficult.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM t LIMIT 8) LIMIT 10;
----
Explained Query:
  Finish limit=10 output=[#0, #1]
    TopK limit=8
      ReadIndex on=t t_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_idx (*** full scan ***)

Target cluster: quickstart

EOF

# A negative example for the TopK removal: The TopK has a grouping key, so it shouldn't be removed.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (
  SELECT * FROM
    (SELECT DISTINCT x FROM t) grp,
    LATERAL (
        SELECT y FROM t
        WHERE x = grp.x
        ORDER BY y LIMIT 4
    )
) LIMIT 8;
----
Explained Query:
  Finish limit=8 output=[#0, #1]
    TopK group_by=[#0{x}] order_by=[#1{y} asc nulls_last] limit=4
      Filter (#0{x}) IS NOT NULL
        ReadIndex on=t t_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_idx (*** full scan ***)

Target cluster: quickstart

EOF

# A negative example for the TopK removal: The TopK has a different ordering key, so it shouldn't be removed.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM t ORDER BY x LIMIT 10) ORDER BY y LIMIT 8;
----
Explained Query:
  Finish order_by=[#1{y} asc nulls_last] limit=8 output=[#0, #1]
    TopK order_by=[#0{x} asc nulls_last] limit=10
      ReadIndex on=t t_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_idx (*** full scan ***)

Target cluster: quickstart

EOF

# The TopK has an ordering key, but the finishing doesn't. We could merge the TopK into the finishing, but we don't
# currently do this.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM t ORDER BY x LIMIT 10) LIMIT 8;
----
Explained Query:
  Finish limit=8 output=[#0, #1]
    TopK order_by=[#0{x} asc nulls_last] limit=10
      ReadIndex on=t t_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Trivial finishing. We could merge the TopK into the finishing, but we don't currently do this.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM t ORDER BY x LIMIT 10);
----
Explained Query:
  TopK order_by=[#0{x} asc nulls_last] limit=10
    ReadIndex on=t t_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_idx (*** full scan ***)

Target cluster: quickstart

EOF

# A negative example for the TopK removal: The TopK's ordering key is a prefix of the RowSetFinishing's, so it shouldn't
# be removed.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM t ORDER BY x LIMIT 10) ORDER BY x, y LIMIT 8;
----
Explained Query:
  Finish order_by=[#0{x} asc nulls_last, #1{y} asc nulls_last] limit=8 output=[#0, #1]
    TopK order_by=[#0{x} asc nulls_last] limit=10
      ReadIndex on=t t_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_idx (*** full scan ***)

Target cluster: quickstart

EOF

# 3 nested LIMITs, so TopK fusion is needed to be able to go to fast path.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM (SELECT * FROM t LIMIT 8) LIMIT 10) LIMIT 6;
----
Explained Query (fast path):
  Finish limit=6 output=[#0, #1]
    ReadIndex on=materialize.public.t t_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_idx (fast path limit)

Target cluster: quickstart

EOF

# The TopK has an offset, so we shouldn't remove it. (We could merge it into the finishing, but we don't do that
# currently.)
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM t LIMIT 10 OFFSET 3) LIMIT 8;
----
Explained Query:
  Finish limit=8 output=[#0, #1]
    TopK limit=10 offset=3
      ReadIndex on=t t_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_idx (*** full scan ***)

Target cluster: quickstart

EOF

########################################################################################################################
# Tests for prepared statement parameters in OFFSET, and for non-trivial expressions in OFFSET.
# (Non-trivial expressions in OFFSET clauses have to be simplifiable to a literal, possibly after parameter binding.)
#
# LIMIT clauses with non-trivial expressions, referring to an outer context of a subquery, have tests in
# `limit_expr.slt`.)
#
# For prepared statements managed through pgwire's Extended Query protocol, see `test_bind_params` in `pgwire.rs`.
########################################################################################################################

statement ok
PREPARE p1 AS
SELECT *
FROM foo
ORDER BY a, b
OFFSET $1;

query IT
EXECUTE p1(0);
----
0  zero
1  one
2  two

query IT
EXECUTE p1(1);
----
1  one
2  two

query IT
EXECUTE p1(1+1);
----
2  two

query error db error: ERROR: Invalid OFFSET clause: Expected an expression that evaluates to a non\-null value, got null
EXECUTE p1(null::bigint);

query error db error: ERROR: Invalid OFFSET clause: Expected an expression that evaluates to a non\-null value, got integer_to_bigint\(null\)
EXECUTE p1(coalesce(null + 5, 7 + null));

query error db error: ERROR: Invalid OFFSET clause: Expected an expression that evaluates to a non\-null value, got null
PREPARE p_error AS
SELECT *
FROM foo
ORDER BY a, b
OFFSET null;

# Prepared statement parameter in OFFSET inside a subquery
statement ok
PREPARE p2 AS
SELECT
  (SELECT sum(a) FROM (
    SELECT a
    FROM foo
    ORDER BY a, b
    OFFSET $1
  ))
FROM foo AS outer_foo
OFFSET $2;

query I
EXECUTE p2(0, 0);
----
3
3
3

query I
EXECUTE p2(2, 0);
----
2
2
2

query I
EXECUTE p2(0, 1);
----
3
3

query I
EXECUTE p2(2, 1);
----
2
2

statement ok
PREPARE p3 AS
SELECT *
FROM foo
ORDER BY a, b
OFFSET $1 - 2;

query error db error: ERROR: Invalid OFFSET clause: must not be negative, got \-2
EXECUTE p3(0);

query II
EXECUTE p3(2);
----
0  0
1  0
2  0

query II
EXECUTE p3(3);
----
1  0
2  0

# This needs a cast to be auto-inserted from Int32 to Int64
query IT
EXECUTE p1(1);
----
1  one
2  two

# The cast has CastContext::Assignment, so even Numeric -> Int64 is allowed.
query IT
EXECUTE p1(0.4);
----
0  zero
1  one
2  two

query IT
EXECUTE p1(0.6);
----
1  one
2  two

# But text -> bigint is not allowed even in CastContext::Assignment.
query error db error: ERROR: unable to cast given parameter \$1: expected bigint, got text
EXECUTE p1('aaa');

query error db error: ERROR: Invalid OFFSET clause: must not be negative, got \-7
PREPARE p_error AS
SELECT *
FROM foo
ORDER BY a, b
OFFSET -7;

query error db error: ERROR: Invalid OFFSET clause: must not be negative, got \-2
PREPARE p_error AS
SELECT *
FROM foo
ORDER BY a, b
OFFSET 5-7;

query error db error: ERROR: Invalid OFFSET clause: invalid input syntax for type bigint: invalid digit found in string: "aaa"
PREPARE p_error AS
SELECT *
FROM foo
ORDER BY a, b
OFFSET 'aaa';

query error db error: ERROR: column "a" does not exist
PREPARE p_error AS
SELECT *
FROM foo
ORDER BY a, b
OFFSET a;

query error db error: ERROR: Invalid OFFSET clause: must be simplifiable to a constant, possibly after parameter binding, got integer_to_bigint\(#\^0\{a\}\)
PREPARE p_error AS
SELECT
  (
    SELECT *
    FROM foo
    ORDER BY a, b
    OFFSET outer_foo.a
  )
FROM foo AS outer_foo;

query error db error: ERROR: Expected subselect to return 1 column, got 2 columns
PREPARE p_error AS
SELECT
  (
    SELECT *
    FROM foo
    ORDER BY a, b
    OFFSET outer_foo.a + $1
  )
FROM foo AS outer_foo;

# It would be nice to error this out already in the PREPARE, but we currently error out only when executing this.
statement ok
PREPARE p_error_1 AS
SELECT
  (
    SELECT b
    FROM foo
    ORDER BY a, b
    OFFSET outer_foo.a + $1
  )
FROM foo AS outer_foo;

# This tests the `plan_select_inner`'s `try_visit_mut_pre` just after binding the parameters of `expr`.
query error db error: ERROR: Invalid OFFSET clause: Expected a constant expression, got integer_to_bigint\(\(#\^0\{a\} \+ 7\)\)
EXECUTE p_error_1(7);

query error db error: ERROR: OFFSET does not allow subqueries
PREPARE p_error AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET (SELECT 2);

query error db error: ERROR: window functions are not allowed in OFFSET \(function pg_catalog\.lag\)
PREPARE p_error AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET lag(5) OVER ();

query error db error: ERROR: window functions are not allowed in OFFSET \(function pg_catalog\.lag\)
PREPARE p_error AS
SELECT
  (
    SELECT a
    FROM foo
    ORDER BY a, b
    OFFSET lag(5) OVER ()
  )
FROM foo AS outer_foo;

# Unmaterializable function calls are not allowed (not deemed a constant by `HirScalarExpr::is_constant`, and then
# not simplified by `MirScalarExpr::reduce`).
query error db error: ERROR: Invalid OFFSET clause: must be simplifiable to a constant, possibly after parameter binding, got text_to_bigint\(mz_timestamp_to_text\(mz_now\(\)\)\)
PREPARE p_error AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET mz_now()::string::bigint;

# OFFSET with CREATE VIEW

statement ok
CREATE VIEW v1 AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET 1;

query T
SELECT b || a
FROM v1
----
two2
zero0

query T
SELECT b || a
FROM v1
OFFSET 1
----
zero0

query T
SELECT b || a
FROM v1
OFFSET 2 - 1
----
zero0

query error db error: ERROR: column "a" does not exist
CREATE VIEW err AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET a;

query error db error: ERROR: Invalid OFFSET clause: invalid input syntax for type bigint: invalid digit found in string: "aaaaa"
CREATE VIEW err AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET 'aaaaa';

query error db error: ERROR: Invalid OFFSET clause: Expected an expression that evaluates to a non\-null value, got null
CREATE VIEW err AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET null;

query error db error: ERROR: Invalid OFFSET clause: must be simplifiable to a constant, possibly after parameter binding, got text_to_bigint\(mz_timestamp_to_text\(mz_now\(\)\)\)
CREATE VIEW err AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET mz_now()::string::bigint;

statement ok
CREATE VIEW v2 AS
SELECT
  (SELECT sum(a) FROM (
    (SELECT a
     FROM foo)
    UNION
    (SELECT a+1
     FROM foo)
    ORDER BY a, b
    OFFSET 2
  )) AS s
FROM foo AS outer_foo
OFFSET 1;

query IIR
SELECT sum(s), count(s), avg(s) FROM v2;
----
10  2  5

# OFFSET with CREATE MATERIALIZED VIEW

statement ok
CREATE MATERIALIZED VIEW mv1 AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET 1;

query T
SELECT b || a
FROM mv1
----
two2
zero0

query T
SELECT b || a
FROM mv1
OFFSET 1
----
zero0

query T
SELECT b || a
FROM mv1
OFFSET 2 - 1
----
zero0

query error db error: ERROR: column "a" does not exist
CREATE MATERIALIZED VIEW err AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET a;

query error db error: ERROR: Invalid OFFSET clause: invalid input syntax for type bigint: invalid digit found in string: "aaaaa"
CREATE MATERIALIZED VIEW err AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET 'aaaaa';

query error db error: ERROR: Invalid OFFSET clause: Expected an expression that evaluates to a non\-null value, got null
CREATE MATERIALIZED VIEW err AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET null;

query error db error: ERROR: Invalid OFFSET clause: must be simplifiable to a constant, possibly after parameter binding, got text_to_bigint\(mz_timestamp_to_text\(mz_now\(\)\)\)
CREATE MATERIALIZED VIEW err AS
SELECT *
FROM foo
ORDER BY b, a
OFFSET mz_now()::string::bigint;

statement ok
CREATE MATERIALIZED VIEW mv2 AS
SELECT
  (SELECT sum(a) FROM (
    (SELECT a
     FROM foo)
    UNION
    (SELECT a+1
     FROM foo)
    ORDER BY a, b
    OFFSET 2
  )) AS s
FROM foo AS outer_foo
OFFSET 1;

query IIR
SELECT sum(s), count(s), avg(s) FROM mv2;
----
10  2  5

# VALUES statement -- OFFSET with parameter

statement ok
PREPARE p4 AS
VALUES (0), (1), (2) OFFSET $1

query I
EXECUTE p4(1);
----
1
2

statement ok
PREPARE p5 AS
VALUES (10), (11), (12) OFFSET $1 - 1

query I
EXECUTE p5(2);
----
11
12

statement ok
PREPARE p6 AS
VALUES (10), (11), (12), ($2), ($3)
ORDER BY 1 DESC
OFFSET $1 - 1

query I
EXECUTE p6(2, 100, 200);
----
100
12
11
10

# Prepared statement parameter in LIMIT
statement ok
PREPARE fizz_paginated AS
SELECT *
FROM fizz
ORDER BY a, b DESC
LIMIT $1
OFFSET $2

query IT
EXECUTE fizz_paginated(4::bigint, 0*4);
----
1735  two
7584  twelve
12345  two
12345  three

query IT
EXECUTE fizz_paginated(4::bigint, 1*4);
----
12345  one
21243  four
21758  fourteen
24223  four

query IT
EXECUTE fizz_paginated(4::bigint, 2*4);
----
25040  two

# TODO: LIMIT currently just tries to match a literal after parameter binding. It should also do constant folding,
# similarly to OFFSET.
query error db error: ERROR: Top\-level LIMIT must be a constant expression, got integer_to_bigint\(4\)
EXECUTE fizz_paginated(4, 2*4);