autocommit_nonmetamorphic

# LogicTest: local !metamorphic

# This file tests against mutations that we expect to be handled with one-phase
# commit transactions. In addition to checking the planning part, we also check
# (using traces) that this is implemented correctly in terms of KV operations.
# Any change to the kv batches produced by these statements should be treated
# with care.

statement ok
CREATE TABLE ab (a INT PRIMARY KEY, b INT, FAMILY f1 (a, b))

# Get the range id.
let $rangeid
SELECT range_id FROM [ SHOW RANGES FROM TABLE ab ]

# Populate table descriptor cache.
query II
SELECT * FROM ab
----

# ------------
# INSERT tests
# ------------

# Single-row insert should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) INSERT INTO ab VALUES (1, 1)
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  INSERT INTO ab VALUES (1, 1);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 1 CPut, 1 EndTxn to (n1,s1):1

# Multi-row insert should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) INSERT INTO ab VALUES (2, 2), (3, 3)
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  INSERT INTO ab VALUES (2, 2), (3, 3);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 2 CPut, 1 EndTxn to (n1,s1):1

# No auto-commit inside a transaction.
statement ok
BEGIN

query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) INSERT INTO ab VALUES (4, 4), (5, 5)
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  INSERT INTO ab VALUES (4, 4), (5, 5);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 2 CPut to (n1,s1):1

statement ok
ROLLBACK

# Insert with simple RETURNING statement should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) INSERT INTO ab VALUES (6, 6), (7, 7) RETURNING a, b
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  INSERT INTO ab VALUES (6, 6), (7, 7) RETURNING a, b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 2 CPut, 1 EndTxn to (n1,s1):1

# TODO(radu): allow non-side-effecting projections.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) INSERT INTO ab VALUES (8, 8), (9, 9) RETURNING a + b
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  INSERT INTO ab VALUES (8, 8), (9, 9) RETURNING a + b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

# Insert with RETURNING statement with side-effects should not auto-commit.
# In this case division can (in principle) error out.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) INSERT INTO ab VALUES (10, 10), (11, 11) RETURNING a / b
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  INSERT INTO ab VALUES (10, 10), (11, 11) RETURNING a / b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

# Another way to test the scenario above: generate an error and ensure that the
# mutation was not committed.
statement error division by zero
INSERT INTO ab VALUES (12, 0) RETURNING a / b

query I
SELECT count(*) FROM ab WHERE b=0
----
0

# ------------
# UPSERT tests
# ------------

# Single-row upsert should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (1, 1)
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  UPSERT INTO ab VALUES (1, 1);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 1 Put, 1 EndTxn to (n1,s1):1

# Multi-row upsert should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (2, 2), (3, 3)
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  UPSERT INTO ab VALUES (2, 2), (3, 3);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 2 Put, 1 EndTxn to (n1,s1):1

# No auto-commit inside a transaction.
statement ok
BEGIN

query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (4, 4), (5, 5)
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  UPSERT INTO ab VALUES (4, 4), (5, 5);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 2 Put to (n1,s1):1

statement ok
ROLLBACK

# Upsert with simple RETURNING statement should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (6, 6), (7, 7) RETURNING a, b
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  UPSERT INTO ab VALUES (6, 6), (7, 7) RETURNING a, b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 2 Put, 1 EndTxn to (n1,s1):1

# TODO(radu): allow non-side-effecting projections.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (8, 8), (9, 9) RETURNING a + b
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  UPSERT INTO ab VALUES (8, 8), (9, 9) RETURNING a + b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

# Upsert with RETURNING statement with side-effects should not auto-commit.
# In this case division can (in principle) error out.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (10, 10), (11, 11) RETURNING a / b
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  UPSERT INTO ab VALUES (10, 10), (11, 11) RETURNING a / b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

# Another way to test the scenario above: generate an error and ensure that the
# mutation was not committed.
statement error division by zero
UPSERT INTO ab VALUES (12, 0) RETURNING a / b

query I
SELECT count(*) FROM ab WHERE b=0
----
0

# ------------
# UPDATE tests
# ------------

# Simple update should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  UPDATE ab SET b=b+1 WHERE a < 3;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put, 1 EndTxn to (n1,s1):1

# No auto-commit inside a transaction.
statement ok
BEGIN

query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  UPDATE ab SET b=b+1 WHERE a < 3;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1

statement ok
ROLLBACK

# Update with simple RETURNING statement should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a, b
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a, b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put, 1 EndTxn to (n1,s1):1

# TODO(radu): allow non-side-effecting projections.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a + b
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a + b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

# Update with RETURNING statement with side-effects should not auto-commit.
# In this case division can (in principle) error out.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a / b
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a / b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

# Another way to test the scenario above: generate an error and ensure that the
# mutation was not committed.
statement error division by zero
UPDATE ab SET b=0 WHERE a < 3 RETURNING a / b;

query I
SELECT count(*) FROM ab WHERE b=0
----
0

# ------------
# DELETE tests
# ------------

# Single-row delete should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) DELETE FROM ab WHERE a = 1
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  DELETE FROM ab WHERE a = 1;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 DelRng, 1 EndTxn to (n1,s1):1

# Multi-row delete should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (2, 3)
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  DELETE FROM ab WHERE a IN (2, 3);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Put to (n1,s1):1
dist sender send  r35: sending batch 1 DelRng, 1 EndTxn to (n1,s1):1

# No auto-commit inside a transaction.
statement ok
BEGIN

query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (4, 5)
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  DELETE FROM ab WHERE a IN (4, 5);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1

statement ok
ROLLBACK

# Delete with simple RETURNING statement should auto-commit.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (6, 7) RETURNING a, b
] WHERE info LIKE '%auto commit%'
----
true

statement ok
SET TRACING=ON;
  DELETE FROM ab WHERE a IN (6, 7) RETURNING a, b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message     LIKE '%r$rangeid: sending batch%'
  AND message NOT LIKE '%PushTxn%'
  AND message NOT LIKE '%QueryTxn%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Del, 1 EndTxn to (n1,s1):1

# TODO(radu): allow non-side-effecting projections.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (8, 9) RETURNING a + b
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  DELETE FROM ab WHERE a IN (8, 9) RETURNING a + b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Del to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

# Insert with RETURNING statement with side-effects should not auto-commit.
# In this case division can (in principle) error out.
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (10, 11) RETURNING a / b
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  DELETE FROM ab WHERE a IN (10, 11) RETURNING a / b;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 2 Del to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

statement ok
INSERT INTO ab VALUES (12, 0);

# Another way to test the scenario above: generate an error and ensure that the
# mutation was not committed.
statement error division by zero
DELETE FROM ab WHERE a = 12 RETURNING a / b

query I
SELECT count(*) FROM ab WHERE b=0
----
1

# -----------------------
# Tests with foreign keys
# -----------------------

statement ok
CREATE TABLE fk_parent (p INT PRIMARY KEY, q INT, FAMILY f1 (p, q));
INSERT INTO fk_parent VALUES (1, 10), (2, 20), (3, 30);
CREATE TABLE fk_child (a INT, b INT REFERENCES fk_parent(p), FAMILY f1 (a, b));

# Populate table descriptor cache.
statement ok
SELECT * FROM fk_parent JOIN fk_child ON p = b

query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) INSERT INTO fk_child VALUES (1, 1), (2, 2)
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  INSERT INTO fk_child VALUES (1, 1), (2, 2);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 2 Scan to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) UPDATE fk_child SET b=b+1 WHERE a < 2
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  UPDATE fk_child SET b=b+1 WHERE a < 2;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 1 Put to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) DELETE FROM fk_parent WHERE p = 3
] WHERE info LIKE '%auto commit%'
----
false


statement ok
SET TRACING=ON;
  DELETE FROM fk_parent WHERE p = 3;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 1 Del to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

# Test with a single cascade, which should use autocommit.
statement ok
DROP TABLE fk_child;
CREATE TABLE fk_child (a INT, b INT REFERENCES fk_parent(p) ON DELETE CASCADE, FAMILY f1 (a, b));
INSERT INTO fk_child VALUES (1, 1), (2, 2)

# Populate table descriptor cache.
statement ok
SELECT * FROM fk_parent JOIN fk_child ON p = b

statement ok
SET TRACING=ON;
  DELETE FROM fk_parent WHERE p = 2;
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 1 Scan to (n1,s1):1
dist sender send  r35: sending batch 1 Del, 1 EndTxn to (n1,s1):1

# -----------------------
# Multiple mutation tests
# -----------------------
query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) INSERT INTO ab (
    SELECT a*10, b*10 FROM [ INSERT INTO ab VALUES (1, 1), (2, 2) RETURNING a, b ]
  )
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  INSERT INTO ab (
    SELECT a*10, b*10 FROM [ INSERT INTO ab VALUES (1, 1), (2, 2) RETURNING a, b ]
  );
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1

query B
SELECT count(*) > 0 FROM [
  EXPLAIN (VERBOSE) WITH cte AS (INSERT INTO ab VALUES (3, 3), (4, 4) RETURNING a, b)
    INSERT INTO ab (SELECT a*10, b*10 FROM cte)
] WHERE info LIKE '%auto commit%'
----
false

statement ok
SET TRACING=ON;
  WITH cte AS (INSERT INTO ab VALUES (3, 3), (4, 4) RETURNING a, b)
  INSERT INTO ab (SELECT a*10, b*10 FROM cte);
SET TRACING=OFF

query TT
SELECT operation, message FROM [SHOW KV TRACE FOR SESSION]
WHERE message       LIKE '%r$rangeid: sending batch%'
  AND message   NOT LIKE '%PushTxn%'
  AND message   NOT LIKE '%QueryTxn%'
  AND operation NOT LIKE '%async%'
----
dist sender send  r35: sending batch 1 DelRng to (n1,s1):1
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 2 CPut to (n1,s1):1
dist sender send  r35: sending batch 1 EndTxn to (n1,s1):1