cockroachdb / cockroachdb/cockroach

opt: EXPLAIN does not work with enforce_home_region

Open
#99,510 4 comments 0 reactions 0 assignees View on GitHub
A-multiregion A-sql-explain A-sql-optimizer C-bug O-qa T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.