cockroachdb / cockroachdb/cockroach
opt: EXPLAIN does not work with enforce_home_region
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
When `enforce_home_region` is enabled, `EXPLAIN` of queries without a home region does not work. This can make it difficult to figure out why a query does not have a home region (i.e. why LOS or LOJ are not being used).
Here's a repro using `cockroach demo --global --nodes 9 --multitenant=false --insecure`:
```sql
CREATE DATABASE d PRIMARY REGION "us-east1" REGIONS "europe-west1", "us-west1";
SHOW CREATE DATABASE d;
USE d;
CREATE TABLE a (
a INT PRIMARY KEY,
b INT
) LOCALITY REGIONAL BY ROW;
SHOW CREATE TABLE a;
INSERT INTO a (a, b, crdb_region) VALUES (0, 0, 'us-east1'), (1, 1, 'europe-west1'), (2, 2, 'us-west1');
SELECT * FROM a;
EXPLAIN SELECT * FROM a;
SET enforce_home_region = on;
-- once enforce_home_region is enabled, this query returns an error
SELECT * FROM a;
-- but so does EXPLAIN
EXPLAIN SELECT * FROM a;
```
Here's how it looks on tip of master (`v23.1.0-alpha.8-dev`):
```
root@127.0.0.1:26257/d> SELECT * FROM a;
ERROR: Query has no home region. Try adding a filter on a.crdb_region and/or on key column (a.a). For more information, see https://www.cockroachlabs.com/docs/stable/cost-based-optimizer.html#control-whether-queries-are-limited-to-a-single-region
SQLSTATE: XCHR2
root@127.0.0.1:26257/d> EXPLAIN SELECT * FROM a;
ERROR: Query has no home region. Try adding a filter on a.crdb_region and/or on key column (a.a). For more information, see https://www.cockroachlabs.com/docs/stable/cost-based-optimizer.html#control-whether-queries-are-limited-to-a-single-region
SQLSTATE: XCHR2
```
Jira issue: CRDB-25909
Contributor guide
Assessment
This issue has not been assessed yet.