opt: Allow matching constraints for leading locality tiers to be omitted #36644
Description
REPRO:
- Start 3 nodes.
./cockroach start --insecure --locality=cloud=gce,region=us-east1,zone=us-east1-b --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8080
./cockroach start --insecure --locality=cloud=gce,region=us-west1,zone=us-west1-b --store=node2 --listen-addr=localhost:26258 --http-addr=localhost:8081 --join=localhost:26257
./cockroach start --insecure --locality=cloud=gce,region=europe-west2,zone=europe-west2-b --store=node3 --listen-addr=localhost:26259 --http-addr=localhost:8082 --join=localhost:26257
- Connect to node tidy up some correctness issues reported by go vet #1 via the SQL CLI and issue the following commands:
CREATE TABLE t (
id INT PRIMARY KEY,
UNIQUE INDEX idx (id ASC)
);
ALTER TABLE t CONFIGURE ZONE USING constraints = '[+cloud=gce,+region=us-west1,+zone=us-west1-b]';
ALTER INDEX t@idx CONFIGURE ZONE USING constraints = '[+zone=us-east1-b]';
EXPLAIN (OPT) SELECT id FROM t WHERE id = 10;
EXPECTED: The query should use the t@idx index, since it matches the zone, even though it does not explicitly match the cloud or region. Allowing this behavior makes it more convenient to use the zone locality feature.
ACTUAL: The query uses the primary index.
Metadata
Metadata
Assignees
Labels
No labels