Materialize/test/sqllogictest/persist-fast-path.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

# Verify that the persist fast path only kicks in when it's expected to do so.

# Generate a table, with multiple batches of data and some partial overlaps
statement ok
CREATE TABLE numbers (
    value int
);

# Applies when the limit is below some threshold. Mapping and
# projecting is fine.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * from numbers limit 10;
----
Explained Query (fast path):
  Finish limit=10 output=[#0]
    PeekPersist materialize.public.numbers

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT value + 1000 from numbers LIMIT 10;
----
Explained Query (fast path):
  Finish limit=10 output=[#0]
    Project (#1)
      Map ((#0{value} + 1000))
        PeekPersist materialize.public.numbers

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * from numbers LIMIT 10 OFFSET 10;
----
Explained Query (fast path):
  Finish limit=10 offset=10 output=[#0]
    PeekPersist materialize.public.numbers

Target cluster: quickstart

EOF

# Check that some fast-path queries succeed.

statement ok
INSERT INTO numbers SELECT generate_series(1, 3);

statement ok
INSERT INTO numbers SELECT generate_series(1, 10);

# We should deterministically return the first N values.

query T valuesort
SELECT value from numbers LIMIT 20;
----
1
1
2
2
3
3
4
5
6
7
8
9
10

query T
SELECT value from numbers LIMIT 10 OFFSET 6;
----
4
5
6
7
8
9
10

statement ok
INSERT INTO numbers SELECT generate_series(5, 100);

statement ok
INSERT INTO numbers SELECT generate_series(-1, 10);

statement ok
INSERT INTO numbers SELECT generate_series(500, 10000);

# Since we order the data in terms of its structured representation, we see the
# smallest values regardless of which order they were inserted in.

query T valuesort
SELECT value from numbers LIMIT 5;
----
-1
0
1
1
1

# Errors should always be returned even when the limit is small
statement ok
CREATE MATERIALIZED VIEW erroring AS SELECT 10.0 / (value - 1) FROM numbers;

query error db error: ERROR: Evaluation error: division by zero
SELECT * FROM erroring LIMIT 10;

# Does not apply when the limit is high, or when mixed with features
# that might require a full scan.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * from numbers LIMIT 1000;
----
Explained Query:
  Finish limit=1000 output=[#0]
    ReadStorage materialize.public.numbers

Source materialize.public.numbers

Target cluster: quickstart

EOF

# ORDER BY is only okay when the ordering matches the shard ordering exactly

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * from numbers ORDER BY value ASC LIMIT 10;
----
Explained Query (fast path):
  Finish order_by=[#0 asc nulls_last] limit=10 output=[#0]
    PeekPersist materialize.public.numbers

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * from numbers ORDER BY value DESC LIMIT 10;
----
Explained Query:
  Finish order_by=[#0{value} desc nulls_first] limit=10 output=[#0]
    ReadStorage materialize.public.numbers

Source materialize.public.numbers

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT value % 2 from numbers ORDER BY value % 2 LIMIT 10;
----
Explained Query:
  Finish order_by=[#0 asc nulls_last] limit=10 output=[#0]
    Project (#1)
      Map ((#0{value} % 2))
        ReadStorage materialize.public.numbers

Source materialize.public.numbers

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * from numbers ORDER BY value NULLS FIRST LIMIT 10;
----
Explained Query:
  Finish order_by=[#0{value} asc nulls_first] limit=10 output=[#0]
    ReadStorage materialize.public.numbers

Source materialize.public.numbers

Target cluster: quickstart

EOF

# Arbitrary filters can't be pushed down... we may need to scan
# an arbitrary number of records to find one that matches.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * from numbers WHERE value > mz_now() LIMIT 10;
----
Explained Query:
  Finish limit=10 output=[#0]
    Filter (integer_to_mz_timestamp(#0{value}) > mz_now())
      ReadStorage materialize.public.numbers

Source materialize.public.numbers
  filter=((integer_to_mz_timestamp(#0{value}) > mz_now()))

Target cluster: quickstart

EOF

# Check that we bound result set size correctly

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

statement ok
INSERT INTO large_rows SELECT * FROM generate_series(1, 100), repeat('a', 100000)

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET max_result_size TO '1MB';
----
COMPLETE 0

statement ok
SELECT * FROM large_rows LIMIT 1;

# Need to disable the result stash, so that we actually exceed max result size
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_compute_peek_response_stash = false
----
COMPLETE 0

query error db error: ERROR: result exceeds max size of 1048.6 KB
SELECT * FROM large_rows LIMIT 99;

simple conn=mz_system,user=mz_system
ALTER SYSTEM RESET max_result_size
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SYSTEM RESET enable_compute_peek_response_stash
----
COMPLETE 0

# Does not apply when an index exists.

statement ok
CREATE DEFAULT INDEX ON numbers;

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * from numbers LIMIT 10;
----
Explained Query (fast path):
  Finish limit=10 output=[#0]
    ReadIndex on=materialize.public.numbers numbers_primary_idx=[*** full scan ***]

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

Target cluster: quickstart

EOF

# Issue 22577
statement ok
CREATE TABLE t1 (f1 INTEGER);

statement ok
INSERT INTO t1 VALUES (2), (2);

statement ok
INSERT INTO t1 VALUES (1);

query T
SELECT * FROM t1 LIMIT 1 OFFSET 0;
----
1

query T
SELECT * FROM t1 LIMIT 1 OFFSET 1;
----
2

simple conn=mz_system,user=mz_system
ALTER SYSTEM RESET persist_fast_path_limit
----
COMPLETE 0