distsql_auto_mode

# LogicTest: local

#
# Tests that verify DistSQL support and auto mode determination.
# The cluster size or distsql mode aren't important for these tests.
#

# "local" logic test configuration overrides the DistSQL mode to 'off', but
# we're interested in behavior with 'auto' in this test file.
statement ok
SET distsql=auto

statement ok
CREATE TABLE kv (k INT PRIMARY KEY, v INT)

# Verify the JSON variant.
query T
EXPLAIN (DISTSQL, JSON) SELECT 1
----
{"sql":"EXPLAIN (DISTSQL, JSON) SELECT 1","nodeNames":["1"],"processors":[{"nodeIdx":0,"inputs":[],"core":{"title":"local values 0/0","details":[]},"outputs":[],"stage":1},{"nodeIdx":0,"inputs":[],"core":{"title":"Response","details":[]},"outputs":[],"stage":0}],"edges":[{"sourceProc":0,"sourceOutput":0,"destProc":1,"destInput":0}]}

# Full table scan - distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv] WHERE info LIKE 'distribution%'
----
distribution: full

# Partial scan - don't distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv WHERE k=1] WHERE info LIKE 'distribution%'
----
distribution: local

# Partial scan - don't distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv WHERE k>1] WHERE info LIKE 'distribution%'
----
distribution: local

# Partial scan with filter - don't distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv WHERE k>1 AND v=1] WHERE info LIKE 'distribution%'
----
distribution: local

# Sort - distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv WHERE k>1 ORDER BY v] WHERE info LIKE 'distribution%'
----
distribution: full

# Aggregation - distribute.
query T
SELECT info FROM [EXPLAIN SELECT k, sum(v) FROM kv WHERE k>1 GROUP BY k] WHERE info LIKE 'distribution%'
----
distribution: full

# Hard limit in scan - distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv LIMIT 1] WHERE info LIKE 'distribution%'
----
distribution: full

# Soft limit in scan - don't distribute.
# TODO(yuzefovich): soft limits are currently ignored in scans.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv UNION SELECT * FROM kv LIMIT 1] WHERE info LIKE 'distribution%'
----
distribution: full

# Limit after sort - distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv WHERE k>1 ORDER BY v LIMIT 1] WHERE info LIKE 'distribution%'
----
distribution: full

# Limit after aggregation - distribute.
query T
SELECT info FROM [EXPLAIN SELECT k, sum(v) FROM kv WHERE k>1 GROUP BY k LIMIT 1] WHERE info LIKE 'distribution%'
----
distribution: full

statement ok
CREATE TABLE kw (k INT PRIMARY KEY, w INT)

# Join - distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv NATURAL JOIN kw] WHERE info LIKE 'distribution%'
----
distribution: full

# Join with span - distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv NATURAL JOIN kw WHERE k=1] WHERE info LIKE 'distribution%'
----
distribution: full

statement ok
CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT, INDEX b (b))

# Index join - don't distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM abc WHERE b=1] WHERE info LIKE 'distribution%'
----
distribution: local

# Index join with filter on result - don't distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM abc WHERE b=1 AND c%2=0] WHERE info LIKE 'distribution%'
----
distribution: local

# Index join with filter on index scan - don't distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM abc WHERE b=1 AND a%2=0] WHERE info LIKE 'distribution%'
----
distribution: local

# Lookup join - distribute.
query T
SELECT info FROM [EXPLAIN SELECT a FROM abc INNER LOOKUP JOIN kv ON b = k WHERE k < 10] WHERE info LIKE 'distribution%'
----
distribution: full

# Lookup join on top of the full scan - distribute.
query T
SELECT info FROM [EXPLAIN SELECT a FROM abc INNER LOOKUP JOIN kv ON b = k] WHERE info LIKE 'distribution%'
----
distribution: full

statement ok
SET CLUSTER SETTING sql.distsql.prefer_local_execution.enabled = true;

# Limit after sort (i.e. top K sort) - don't distribute when preferring local
# execution.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv WHERE k>1 ORDER BY v LIMIT 1] WHERE info LIKE 'distribution%'
----
distribution: local

# General sort - distribute.
query T
SELECT info FROM [EXPLAIN SELECT * FROM kv WHERE k>1 ORDER BY v] WHERE info LIKE 'distribution%'
----
distribution: full

# Lookup join - don't distribute when preferring local execution.
query T
SELECT info FROM [EXPLAIN SELECT a FROM abc INNER LOOKUP JOIN kv ON b = k WHERE k < 10] WHERE info LIKE 'distribution%'
----
distribution: local

# Lookup join on top of the full scan - distribute.
query T
SELECT info FROM [EXPLAIN SELECT a FROM abc INNER LOOKUP JOIN kv ON b = k] WHERE info LIKE 'distribution%'
----
distribution: full

statement ok
RESET CLUSTER SETTING sql.distsql.prefer_local_execution.enabled;