Materialize/test/sqllogictest/explain/pushdown.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

# Disable persist inline writes so we get real part numbers below
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET persist_inline_writes_single_max_bytes = 0
----
COMPLETE 0

# EXPLAIN FILTER PUSHDOWN statements are blocked by a feature flag
statement ok
CREATE TABLE numbers (
    value int
);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM numbers where value > 10;
----
Explained Query:
  Filter (#0{value} > 10)
    ReadStorage materialize.public.numbers

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

Target cluster: quickstart

EOF

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_explain_pushdown = false
----
COMPLETE 0

query error db error: ERROR: EXPLAIN FILTER PUSHDOWN is not available
EXPLAIN FILTER PUSHDOWN FOR SELECT * FROM numbers where value > 10;

# Even when the feature flag is enabled, the feature is blocked in adapter for most queries

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_explain_pushdown = true
----
COMPLETE 0

query error db error: ERROR: EXPLAIN FILTER PUSHDOWN queries for this explainee type are not supported
EXPLAIN FILTER PUSHDOWN FOR CREATE MATERIALIZED VIEW foo AS SELECT * FROM numbers where value > 10;

# However, EXPLAIN FILTER PUSHDOWN FOR SELECT is now supported

query TIIII
EXPLAIN FILTER PUSHDOWN FOR SELECT * FROM numbers where value > 10;
----
materialize.public.numbers  0  0  0  0

statement ok
INSERT INTO numbers VALUES (1), (2), (3);

# The next two queries may be slightly brittle, since they depend on part sizes.
# Feel free to --rewrite-results or delete them if they prove difficult to maintain.
query TIIII
EXPLAIN FILTER PUSHDOWN FOR SELECT * FROM numbers where value > 10;
----
materialize.public.numbers  1233  0  1  0

query TIIII
EXPLAIN FILTER PUSHDOWN FOR SELECT * FROM numbers where value < 10;
----
materialize.public.numbers  1233  1233  1  1

# Verify that pushdown of jsonb_get_string is infallible. Before this was
# fixed, a filter expression on a jsonb field that is not present in all parts
# would cause those parts to be fetched, even when AND'ed together with an
# expression that would definitely filter out the part otherwise.

statement ok
CREATE TABLE jsonb_fields (
    timestamp int,
    payload jsonb
);

statement ok
INSERT INTO jsonb_fields VALUES (1, '{ "field": "value" }');

statement ok
INSERT INTO jsonb_fields VALUES (2, '{ "other-field": "value" }');

# The `timestamp > 1000` part filters out everything, regardless of whether the
# referenced field exists in the payload or not.

query TIIII
EXPLAIN FILTER PUSHDOWN FOR SELECT * FROM jsonb_fields where timestamp > 1000 AND payload->>'field' = 'not-value';
----
materialize.public.jsonb_fields  2826  0  2  0

# EXPLAIN FILTER PUSHDOWN FOR MATERIALIZED VIEW is also supported

statement ok
CREATE MATERIALIZED VIEW big_numbers AS SELECT * FROM numbers WHERE value > 10000;

statement ok
SELECT mz_unsafe.mz_sleep(3);

query TIIII
EXPLAIN FILTER PUSHDOWN FOR MATERIALIZED VIEW big_numbers
----
materialize.public.numbers  1233  0  1  0

# EXPLAIN FILTER PUSHDOWN should work even if there are no replicas.
statement ok
CREATE CLUSTER no_replicas (SIZE '1', REPLICATION FACTOR 0);

statement ok
SET CLUSTER = no_replicas;

query TIIII
EXPLAIN FILTER PUSHDOWN FOR SELECT * FROM jsonb_fields where timestamp > 1000 AND payload->>'field' = 'not-value';
----
materialize.public.jsonb_fields  2826  0  2  0

# ----------------------------------------
# Cleanup
# ----------------------------------------

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_explain_pushdown = false
----
COMPLETE 0