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

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

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

statement ok
CREATE TABLE t (a int, b int)

statement ok
CREATE TABLE t2 (a int, b int, c int)

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t

query IIII colnames
FETCH 0 c
----
mz_timestamp mz_diff a b

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t WITH (PROGRESS)

query IIIII colnames
FETCH 0 c
----
mz_timestamp mz_progressed mz_diff a b

statement ok
COMMIT

# ENVELOPE UPSERT
statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t ENVELOPE UPSERT (KEY (a))

query IIII colnames
FETCH 0 c
----
mz_timestamp mz_state a b

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t WITH (PROGRESS) ENVELOPE UPSERT (KEY (a))

query IIIII colnames
FETCH 0 c
----
mz_timestamp mz_progressed mz_state a b

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t ENVELOPE UPSERT (KEY (a)) WITH (PROGRESS)

query IIIII colnames
FETCH 0 c
----
mz_timestamp mz_progressed mz_state a b

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t ENVELOPE UPSERT (KEY (b))

query IIII colnames
FETCH 0 c
----
mz_timestamp mz_state b a

statement ok
COMMIT

# ENVELOPE DEBEZIUM
statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t ENVELOPE DEBEZIUM (KEY (a))

query IIIII colnames
FETCH 0 c
----
mz_timestamp mz_state a before_b after_b

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t WITH (PROGRESS) ENVELOPE DEBEZIUM (KEY (a))

query IIIIII colnames
FETCH 0 c
----
mz_timestamp mz_progressed mz_state a before_b after_b

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t ENVELOPE DEBEZIUM (KEY (b))

query IIIII colnames
FETCH 0 c
----
mz_timestamp mz_state b before_a after_a

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t2 ENVELOPE DEBEZIUM (KEY (b))

query IIIIIII colnames
FETCH 0 c
----
mz_timestamp mz_state b before_a before_c after_a after_c

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t2 ENVELOPE DEBEZIUM (KEY (c, b))

query IIIIII colnames
FETCH 0 c
----
mz_timestamp mz_state c b before_a after_a

statement ok
COMMIT

# WITHIN TIMESTAMP ORDER BY
statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t WITHIN TIMESTAMP ORDER BY a, mz_diff

query IIII colnames
FETCH 0 c
----
mz_timestamp mz_diff a b

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE t WITH (PROGRESS) WITHIN TIMESTAMP ORDER BY a, mz_diff

query IIIII colnames
FETCH 0 c
----
mz_timestamp mz_progressed mz_diff a b

statement ok
COMMIT

# SHOW commands are not allowed in maintained (i.e., non-one-shot) dataflows, but SUBSCRIBE _does_ allow it, even though
# it's a maintained dataflow.

statement ok
BEGIN

statement ok
DECLARE c2 CURSOR FOR SUBSCRIBE TO (SHOW CLUSTER REPLICAS);

statement ok
COMMIT