opt: TestCCLLogic/multiregion-9node-3region-3azs/regional_by_row flaky #67869
Description
Check out the following failure. SELECT * FROM regional_by_row_table got an unexpected plan.
It happened on one of my builds, but I'm fairly sure that I have nothing to do with it,
https://teamcity.cockroachdb.com/viewLog.html?buildId=3207375&buildTypeId=Cockroach_GitHubCiOptional
plan diff
=== CONT TestCCLLogic/multiregion-9node-3region-3azs/regional_by_row
=== CONT TestCCLLogic/multiregion-9node-3region-3azs/regional_by_row
logic.go:2283:
testdata/logic_test/regional_by_row:276: EXPLAIN (OPT, CATALOG) SELECT * FROM regional_by_row_table
expected:
TABLE regional_by_row_table
├── pk int not null
├── pk2 int not null
├── a int not null
├── b int not null
├── j jsonb
├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100054) [hidden]
├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
├── tableoid oid [hidden] [system]
├── j_inverted_key bytes not null [virtual-inverted]
├── FAMILY fam_0_pk_pk2_a_b_j_crdb_region (pk, pk2, a, b, j, crdb_region)
├── CHECK (crdb_region IN (x'40':::@100054, x'80':::@100054, x'c0':::@100054))
├── PRIMARY INDEX primary
│ ├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100054) [hidden] (implicit)
│ ├── pk int not null
│ ├── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── partitions
│ ├── ap-southeast-2
│ │ ├── partition by list prefixes
│ │ │ └── ('ap-southeast-2')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ap-southeast-2]
│ │ └── lease preference: [+region=ap-southeast-2]
│ ├── ca-central-1
│ │ ├── partition by list prefixes
│ │ │ └── ('ca-central-1')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── us-east-1
│ ├── partition by list prefixes
│ │ └── ('us-east-1')
│ └── ZONE
│ ├── replica constraints
│ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ ├── 1 replicas: [+region=ca-central-1]
│ │ ├── 1 replicas: [+region=us-east-1]
│ │ └── voter constraints: [+region=us-east-1]
│ └── lease preference: [+region=us-east-1]
├── INDEX regional_by_row_table_a_idx
│ ├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100054) [hidden] (implicit)
│ ├── a int not null
│ ├── pk int not null
│ ├── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── partitions
│ ├── ap-southeast-2
│ │ ├── partition by list prefixes
│ │ │ └── ('ap-southeast-2')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ap-southeast-2]
│ │ └── lease preference: [+region=ap-southeast-2]
│ ├── ca-central-1
│ │ ├── partition by list prefixes
│ │ │ └── ('ca-central-1')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── us-east-1
│ ├── partition by list prefixes
│ │ └── ('us-east-1')
│ └── ZONE
│ ├── replica constraints
│ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ ├── 1 replicas: [+region=ca-central-1]
│ │ ├── 1 replicas: [+region=us-east-1]
│ │ └── voter constraints: [+region=us-east-1]
│ └── lease preference: [+region=us-east-1]
├── UNIQUE INDEX regional_by_row_table_b_key
│ ├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100054) [hidden] (implicit)
│ ├── b int not null
│ ├── pk int not null (storing)
│ ├── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── partitions
│ ├── ap-southeast-2
│ │ ├── partition by list prefixes
│ │ │ └── ('ap-southeast-2')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ap-southeast-2]
│ │ └── lease preference: [+region=ap-southeast-2]
│ ├── ca-central-1
│ │ ├── partition by list prefixes
│ │ │ └── ('ca-central-1')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── us-east-1
│ ├── partition by list prefixes
│ │ └── ('us-east-1')
│ └── ZONE
│ ├── replica constraints
│ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ ├── 1 replicas: [+region=ca-central-1]
│ │ ├── 1 replicas: [+region=us-east-1]
│ │ └── voter constraints: [+region=us-east-1]
│ └── lease preference: [+region=us-east-1]
├── INVERTED INDEX regional_by_row_table_j_idx
│ ├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100054) [hidden] (implicit)
│ ├── j_inverted_key bytes not null [virtual-inverted]
│ ├── pk int not null
│ ├── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── partitions
│ ├── ap-southeast-2
│ │ ├── partition by list prefixes
│ │ │ └── ('ap-southeast-2')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ap-southeast-2]
│ │ └── lease preference: [+region=ap-southeast-2]
│ ├── ca-central-1
│ │ ├── partition by list prefixes
│ │ │ └── ('ca-central-1')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── us-east-1
│ ├── partition by list prefixes
│ │ └── ('us-east-1')
│ └── ZONE
│ ├── replica constraints
│ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ ├── 1 replicas: [+region=ca-central-1]
│ │ ├── 1 replicas: [+region=us-east-1]
│ │ └── voter constraints: [+region=us-east-1]
│ └── lease preference: [+region=us-east-1]
├── UNIQUE WITHOUT INDEX (pk)
└── UNIQUE WITHOUT INDEX (b)
scan regional_by_row_table
└── check constraint expressions
└── crdb_region IN ('ap-southeast-2', 'ca-central-1', 'us-east-1')
but found (query options: "") :
TABLE regional_by_row_table
├── pk int not null
├── pk2 int not null
├── a int not null
├── b int not null
├── j jsonb
├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100057) [hidden]
├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
├── tableoid oid [hidden] [system]
├── j_inverted_key bytes not null [virtual-inverted]
├── FAMILY fam_0_pk_pk2_a_b_j_crdb_region (pk, pk2, a, b, j, crdb_region)
├── CHECK (crdb_region IN (x'40':::@100057, x'80':::@100057, x'c0':::@100057))
├── PRIMARY INDEX primary
│ ├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100057) [hidden] (implicit)
│ ├── pk int not null
│ ├── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── partitions
│ ├── ap-southeast-2
│ │ ├── partition by list prefixes
│ │ │ └── ('ap-southeast-2')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ap-southeast-2]
│ │ └── lease preference: [+region=ap-southeast-2]
│ ├── ca-central-1
│ │ ├── partition by list prefixes
│ │ │ └── ('ca-central-1')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── us-east-1
│ ├── partition by list prefixes
│ │ └── ('us-east-1')
│ └── ZONE
│ ├── replica constraints
│ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ ├── 1 replicas: [+region=ca-central-1]
│ │ ├── 1 replicas: [+region=us-east-1]
│ │ └── voter constraints: [+region=us-east-1]
│ └── lease preference: [+region=us-east-1]
├── INDEX regional_by_row_table_a_idx
│ ├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100057) [hidden] (implicit)
│ ├── a int not null
│ ├── pk int not null
│ ├── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── partitions
│ ├── ap-southeast-2
│ │ ├── partition by list prefixes
│ │ │ └── ('ap-southeast-2')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ap-southeast-2]
│ │ └── lease preference: [+region=ap-southeast-2]
│ ├── ca-central-1
│ │ ├── partition by list prefixes
│ │ │ └── ('ca-central-1')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── us-east-1
│ ├── partition by list prefixes
│ │ └── ('us-east-1')
│ └── ZONE
│ ├── replica constraints
│ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ ├── 1 replicas: [+region=ca-central-1]
│ │ ├── 1 replicas: [+region=us-east-1]
│ │ └── voter constraints: [+region=us-east-1]
│ └── lease preference: [+region=us-east-1]
├── UNIQUE INDEX regional_by_row_table_b_key
│ ├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100057) [hidden] (implicit)
│ ├── b int not null
│ ├── pk int not null (storing)
│ ├── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── partitions
│ ├── ap-southeast-2
│ │ ├── partition by list prefixes
│ │ │ └── ('ap-southeast-2')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ap-southeast-2]
│ │ └── lease preference: [+region=ap-southeast-2]
│ ├── ca-central-1
│ │ ├── partition by list prefixes
│ │ │ └── ('ca-central-1')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── us-east-1
│ ├── partition by list prefixes
│ │ └── ('us-east-1')
│ └── ZONE
│ ├── replica constraints
│ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ ├── 1 replicas: [+region=ca-central-1]
│ │ ├── 1 replicas: [+region=us-east-1]
│ │ └── voter constraints: [+region=us-east-1]
│ └── lease preference: [+region=us-east-1]
├── INVERTED INDEX regional_by_row_table_j_idx
│ ├── crdb_region crdb_internal_region not null default (default_to_database_primary_region(gateway_region())::@100057) [hidden] (implicit)
│ ├── j_inverted_key bytes not null [virtual-inverted]
│ ├── pk int not null
│ ├── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── partitions
│ ├── ap-southeast-2
│ │ ├── partition by list prefixes
│ │ │ └── ('ap-southeast-2')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ap-southeast-2]
│ │ └── lease preference: [+region=ap-southeast-2]
│ ├── ca-central-1
│ │ ├── partition by list prefixes
│ │ │ └── ('ca-central-1')
│ │ └── ZONE
│ │ ├── replica constraints
│ │ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ │ ├── 1 replicas: [+region=ca-central-1]
│ │ │ ├── 1 replicas: [+region=us-east-1]
│ │ │ └── voter constraints: [+region=ca-central-1]
│ │ └── lease preference: [+region=ca-central-1]
│ └── us-east-1
│ ├── partition by list prefixes
│ │ └── ('us-east-1')
│ └── ZONE
│ ├── replica constraints
│ │ ├── 1 replicas: [+region=ap-southeast-2]
│ │ ├── 1 replicas: [+region=ca-central-1]
│ │ ├── 1 replicas: [+region=us-east-1]
│ │ └── voter constraints: [+region=us-east-1]
│ └── lease preference: [+region=us-east-1]
├── UNIQUE WITHOUT INDEX (pk)
└── UNIQUE WITHOUT INDEX (b)
scan regional_by_row_table
└── check constraint expressions
└── crdb_region IN ('ap-southeast-2', 'ca-central-1', 'us-east-1')