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