text: # LogicTest: 5node
  
  # Ensure that cost-based-optimizer uses an index with zone constraints that most
  # closely matches the gateway's locality. Use "retry" option, since it can take
  # a bit of time for gossip to refresh the zone.
  
  statement ok
  CREATE TABLE t (
      k INT PRIMARY KEY,
      v STRING,
      INDEX secondary (k) STORING (v),
      INDEX tertiary (k) STORING (v),
      FAMILY (k, v)
  );
  
  # ------------------------------------------------------------------------------
  # Put table in dc2 and secondary index in dc1 so that the gateway matches the
  # secondary index rather the primary index.
  # ------------------------------------------------------------------------------
  
  statement ok
  ALTER TABLE t CONFIGURE ZONE USING constraints='[+region=test,+dc=dc2]'
  
  statement ok
  ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@secondary
  ·     spans          [/10 - /10]
  
  query T retry
  EXPLAIN (OPT, CATALOG) SELECT * FROM t
  ----
  TABLE t
   ├── k int not null
   ├── v string
   ├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
   ├── tableoid oid [hidden] [system]
   ├── FAMILY fam_0_k_v (k, v)
   ├── INDEX primary
   │    ├── k int not null
   │    └── ZONE
   │         └── constraints: [+region=test,+dc=dc2]
   ├── INDEX secondary
   │    ├── k int not null
   │    ├── v string (storing)
   │    └── ZONE
   │         └── constraints: [+region=test,+dc=dc1]
   └── INDEX tertiary
        ├── k int not null
        ├── v string (storing)
        └── ZONE
             └── constraints: [+region=test,+dc=dc2]
  scan t@secondary
  
  # ------------------------------------------------------------------------------
  # Move secondary to dc3 and put tertiary in dc1 and ensure that gateway matches
  # tertiary instead of secondary. Regression for #35546.
  # ------------------------------------------------------------------------------
  
  statement ok
  ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc3]'
  
  statement ok
  ALTER INDEX t@tertiary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@tertiary
  ·     spans          [/10 - /10]
  
  query T retry
  EXPLAIN (OPT, CATALOG) SELECT * FROM t
  ----
  TABLE t
   ├── k int not null
   ├── v string
   ├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
   ├── tableoid oid [hidden] [system]
   ├── FAMILY fam_0_k_v (k, v)
   ├── INDEX primary
   │    ├── k int not null
   │    └── ZONE
   │         └── constraints: [+region=test,+dc=dc2]
   ├── INDEX secondary
   │    ├── k int not null
   │    ├── v string (storing)
   │    └── ZONE
   │         └── constraints: [+region=test,+dc=dc3]
   └── INDEX tertiary
        ├── k int not null
        ├── v string (storing)
        └── ZONE
             └── constraints: [+region=test,+dc=dc1]
  scan t@tertiary
  
  # ------------------------------------------------------------------------------
  # Swap secondary and tertiary localities and ensure invalidation occurs.
  # Regression for #35546.
  # ------------------------------------------------------------------------------
  
  statement ok
  ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]'
  
  statement ok
  ALTER INDEX t@tertiary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc3]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@secondary
  ·     spans          [/10 - /10]
  
  # ------------------------------------------------------------------------------
  # Swap location of primary and secondary indexes and ensure that primary index
  # is used instead.
  # ------------------------------------------------------------------------------
  
  statement ok
  ALTER TABLE t CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]'
  
  statement ok
  ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc2]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@primary
  ·     spans          [/10 - /10]
  
  query T retry
  EXPLAIN (OPT, CATALOG) SELECT * FROM t
  ----
  TABLE t
   ├── k int not null
   ├── v string
   ├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
   ├── tableoid oid [hidden] [system]
   ├── FAMILY fam_0_k_v (k, v)
   ├── INDEX primary
   │    ├── k int not null
   │    └── ZONE
   │         └── constraints: [+region=test,+dc=dc1]
   ├── INDEX secondary
   │    ├── k int not null
   │    ├── v string (storing)
   │    └── ZONE
   │         └── constraints: [+region=test,+dc=dc2]
   └── INDEX tertiary
        ├── k int not null
        ├── v string (storing)
        └── ZONE
             └── constraints: [+region=test,+dc=dc3]
  scan t
  
  # ------------------------------------------------------------------------------
  # Use PREPARE to make sure that the prepared plan is invalidated when the
  # secondary index's constraints change.
  # ------------------------------------------------------------------------------
  
  statement
  PREPARE p AS SELECT tree, field, description FROM [EXPLAIN SELECT k, v FROM t WHERE k=10]
  
  query TTT retry
  EXECUTE p
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@primary
  ·     spans          [/10 - /10]
  
  statement ok
  ALTER TABLE t CONFIGURE ZONE USING constraints='[+region=test,+dc=dc2]'
  
  statement ok
  ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]'
  
  query TTT retry
  EXECUTE p
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@secondary
  ·     spans          [/10 - /10]
  
  statement ok
  DEALLOCATE p
  
  # ------------------------------------------------------------------------------
  # Put table lease preference in dc2 and secondary index lease preference in dc1
  # so that the gateway matches the secondary index rather the primary index.
  # ------------------------------------------------------------------------------
  
  statement ok
  ALTER TABLE t CONFIGURE ZONE
  USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc2]]'
  
  statement ok
  ALTER INDEX t@secondary CONFIGURE ZONE
  USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@secondary
  ·     spans          [/10 - /10]
  
  # ------------------------------------------------------------------------------
  # Move secondary lease preference to dc3 and put tertiary lease preference in
  # dc1 and ensure that gateway matches tertiary.
  # ------------------------------------------------------------------------------
  
  statement ok
  ALTER INDEX t@secondary CONFIGURE ZONE
  USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc3]]'
  
  statement ok
  ALTER INDEX t@tertiary CONFIGURE ZONE
  USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@tertiary
  ·     spans          [/10 - /10]
  
  query T retry
  EXPLAIN (OPT, CATALOG) SELECT * FROM t
  ----
  TABLE t
   ├── k int not null
   ├── v string
   ├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
   ├── tableoid oid [hidden] [system]
   ├── FAMILY fam_0_k_v (k, v)
   ├── INDEX primary
   │    ├── k int not null
   │    └── ZONE
   │         ├── constraints: [+region=test]
   │         └── lease preference: [+region=test,+dc=dc2]
   ├── INDEX secondary
   │    ├── k int not null
   │    ├── v string (storing)
   │    └── ZONE
   │         ├── constraints: [+region=test]
   │         └── lease preference: [+region=test,+dc=dc3]
   └── INDEX tertiary
        ├── k int not null
        ├── v string (storing)
        └── ZONE
             ├── constraints: [+region=test]
             └── lease preference: [+region=test,+dc=dc1]
  scan t@tertiary
  
  # ------------------------------------------------------------------------------
  # Ensure that an index constrained to a region is preferred over an index that
  # merely has a lease preference in that region (since lease preferences can
  # move, whereas constraints are fixed).
  # ------------------------------------------------------------------------------
  
  statement ok
  ALTER TABLE t CONFIGURE ZONE
  USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]'
  
  statement ok
  ALTER INDEX t@secondary CONFIGURE ZONE
  USING constraints='[+region=test,+dc=dc1]'
  
  statement ok
  ALTER INDEX t@tertiary CONFIGURE ZONE
  USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@secondary
  ·     spans          [/10 - /10]
  
  query T retry
  EXPLAIN (OPT, CATALOG) SELECT * FROM t
  ----
  TABLE t
   ├── k int not null
   ├── v string
   ├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
   ├── tableoid oid [hidden] [system]
   ├── FAMILY fam_0_k_v (k, v)
   ├── INDEX primary
   │    ├── k int not null
   │    └── ZONE
   │         ├── constraints: [+region=test]
   │         └── lease preference: [+region=test,+dc=dc1]
   ├── INDEX secondary
   │    ├── k int not null
   │    ├── v string (storing)
   │    └── ZONE
   │         ├── constraints: [+region=test,+dc=dc1]
   │         └── lease preference: [+region=test,+dc=dc3]
   └── INDEX tertiary
        ├── k int not null
        ├── v string (storing)
        └── ZONE
             ├── constraints: [+region=test]
             └── lease preference: [+region=test,+dc=dc1]
  scan t@secondary
  
  # ------------------------------------------------------------------------------
  # Use PREPARE to make sure that the prepared plan is invalidated when the
  # secondary index's lease preferences change.
  # ------------------------------------------------------------------------------
  
  statement ok
  PREPARE p AS SELECT tree, field, description FROM [EXPLAIN SELECT k, v FROM t WHERE k=10]
  
  query TTT retry
  EXECUTE p
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@secondary
  ·     spans          [/10 - /10]
  
  statement ok
  ALTER INDEX t@secondary CONFIGURE ZONE
  USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc2]]'
  
  query TTT retry
  EXECUTE p
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t@primary
  ·     spans          [/10 - /10]
  
  statement ok
  DEALLOCATE p
  
  
  # ------------------------------------------------------------------------------
  # Regression for issue #36642. Optimizer picked wrong index when the index had
  # constraints / lease preferences, but the table had no zone config.
  # ------------------------------------------------------------------------------
  
  statement ok
  CREATE TABLE t36642 (
      k INT PRIMARY KEY,
      v STRING,
      INDEX secondary (k) STORING (v),
      INDEX tertiary (k) STORING (v),
      FAMILY (k, v)
  );
  
  statement ok
  ALTER INDEX t36642@secondary CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t36642 WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t36642@secondary
  ·     spans          [/10 - /10]
  
  statement ok
  ALTER INDEX t36642@tertiary CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]'
  
  statement ok
  ALTER INDEX t36642@secondary CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc2]]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t36642 WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t36642@tertiary
  ·     spans          [/10 - /10]
  
  query T retry
  EXPLAIN (OPT, CATALOG) SELECT * FROM t
  ----
  TABLE t
   ├── k int not null
   ├── v string
   ├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
   ├── tableoid oid [hidden] [system]
   ├── FAMILY fam_0_k_v (k, v)
   ├── INDEX primary
   │    ├── k int not null
   │    └── ZONE
   │         ├── constraints: [+region=test]
   │         └── lease preference: [+region=test,+dc=dc1]
   ├── INDEX secondary
   │    ├── k int not null
   │    ├── v string (storing)
   │    └── ZONE
   │         ├── constraints: [+region=test]
   │         └── lease preference: [+region=test,+dc=dc2]
   └── INDEX tertiary
        ├── k int not null
        ├── v string (storing)
        └── ZONE
             ├── constraints: [+region=test]
             └── lease preference: [+region=test,+dc=dc1]
  scan t
  
  
  # ------------------------------------------------------------------------------
  # Regression for issue #36644. Allow matching constraints for leading locality
  # tiers to be omitted.
  # ------------------------------------------------------------------------------
  
  statement ok
  CREATE TABLE t36644 (
      k INT PRIMARY KEY,
      v STRING,
      INDEX secondary (k) STORING (v),
      INDEX tertiary (k) STORING (v),
      FAMILY (k, v)
  );
  
  statement ok
  ALTER INDEX t36644@secondary
  CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+dc=dc1]]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t36644 WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t36644@secondary
  ·     spans          [/10 - /10]
  
  statement ok
  ALTER INDEX t36644@secondary CONFIGURE ZONE USING lease_preferences='[[+dc=dc3]]'
  
  statement ok
  ALTER INDEX t36644@tertiary
  CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+dc=dc1]]'
  
  query TTT retry
  EXPLAIN SELECT * FROM t36644 WHERE k=10
  ----
  ·     distribution   full
  ·     vectorized     true
  scan  ·              ·
  ·     missing stats  ·
  ·     table          t36644@tertiary
  ·     spans          [/10 - /10]
  
  subtest regression_35756
  
  statement ok
  CREATE TABLE t35756 (x INT PRIMARY KEY)
    PARTITION BY LIST (x) (
      PARTITION x1 VALUES IN (1),
      PARTITION DEFAULT VALUES IN (DEFAULT)
    )
  
  # When using partitions, if the partition has no zone config set, SHOW
  # should display the default zone config and NOT display the "PARTITION"
  # keyword.
  
  query TT
  SHOW ZONE CONFIGURATION FOR PARTITION x1 OF TABLE t35756
  ----
  RANGE default  ALTER RANGE default CONFIGURE ZONE USING
                 range_min_bytes = 134217728,
                 range_max_bytes = 536870912,
                 gc.ttlseconds = 90000,
                 num_replicas = 3,
                 constraints = '[]',
                 lease_preferences = '[]'
  
  # Regression test for #38391: verify that altering an index's partition really
  # modifies the partition.
  
  statement ok
  CREATE TABLE t38391 (
    x INT, y INT, z INT,
    PRIMARY KEY(x, y),
    INDEX foo (x, z) PARTITION BY LIST (x) (
      PARTITION x1_idx VALUES IN (1),
      PARTITION DEFAULT VALUES IN (DEFAULT)
    ))
    PARTITION BY LIST (x) (
      PARTITION x1 VALUES IN (1),
      PARTITION DEFAULT_idx VALUES IN (DEFAULT)
    )
  
  statement ok
  ALTER PARTITION x1_idx OF INDEX t38391@foo CONFIGURE ZONE USING gc.ttlseconds = 31337
  
  query TT
  SHOW ZONE CONFIGURATION FOR PARTITION x1_idx OF INDEX t38391@foo
  ----
  PARTITION x1_idx OF INDEX t38391@foo  ALTER PARTITION x1_idx OF INDEX t38391@foo CONFIGURE ZONE USING
                                        range_min_bytes = 134217728,
                                        range_max_bytes = 536870912,
                                        gc.ttlseconds = 31337,
                                        num_replicas = 3,
                                        constraints = '[]',
                                        lease_preferences = '[]'
  
  statement ok
  CREATE TABLE dup_constraint (x INT PRIMARY KEY);
  
  statement ok
  ALTER TABLE dup_constraint PARTITION BY LIST (x) (
      PARTITION p1 VALUES IN (1),
      PARTITION p2 VALUES IN (2)
  )
  
  statement error pq: incompatible zone constraints: "\+region=us-east1" and "\+region=us-west1"
  ALTER PARTITION p1 OF TABLE dup_constraint CONFIGURE ZONE USING
  CONSTRAINTS='[+region=us-east1, +region=us-west1]'
  
  statement error pq: incompatible zone constraints: "\+region=us-east1" and "\-region=us-east1"
  ALTER PARTITION p1 OF TABLE dup_constraint CONFIGURE ZONE USING
  CONSTRAINTS='[+region=us-east1, -region=us-east1]'
  
  # Create various identifiers with the space character to test quoting in target
  # names.
  statement ok
  CREATE DATABASE "my database";
  USE "my database";
  CREATE TABLE "my table" (x INT PRIMARY KEY) PARTITION BY LIST (x) (
      PARTITION "my partition" VALUES IN (1)
  );
  CREATE INDEX "my index" ON "my table" (x) PARTITION BY LIST (x) (
      PARTITION "my partition" VALUES IN (1)
  );
  ALTER DATABASE "my database" CONFIGURE ZONE USING num_replicas = 1;
  ALTER TABLE "my table" CONFIGURE ZONE USING num_replicas = 1;
  ALTER INDEX "my table"@"my index" CONFIGURE ZONE USING num_replicas = 1;
  ALTER PARTITION "my partition" OF INDEX "my table"@primary CONFIGURE ZONE USING num_replicas = 1;
  ALTER PARTITION "my partition" OF INDEX "my table"@"my index" CONFIGURE ZONE USING num_replicas = 1
  
  query TTTTTT
  SELECT target, range_name, database_name, table_name, index_name, partition_name
  FROM crdb_internal.zones
  ----
  RANGE default                                                                 default   NULL         NULL                          NULL       NULL
  DATABASE system                                                               NULL      system       NULL                          NULL       NULL
  RANGE meta                                                                    meta      NULL         NULL                          NULL       NULL
  RANGE system                                                                  system    NULL         NULL                          NULL       NULL
  RANGE liveness                                                                liveness  NULL         NULL                          NULL       NULL
  TABLE system.public.replication_constraint_stats                              NULL      system       replication_constraint_stats  NULL       NULL
  TABLE system.public.replication_stats                                         NULL      system       replication_stats             NULL       NULL
  TABLE test.public.t                                                           NULL      test         t                             NULL       NULL
  INDEX test.public.t@secondary                                                 NULL      test         t                             secondary  NULL
  INDEX test.public.t@tertiary                                                  NULL      test         t                             tertiary   NULL
  INDEX test.public.t36642@secondary                                            NULL      test         t36642                        secondary  NULL
  INDEX test.public.t36642@tertiary                                             NULL      test         t36642                        tertiary   NULL
  INDEX test.public.t36644@secondary                                            NULL      test         t36644                        secondary  NULL
  INDEX test.public.t36644@tertiary                                             NULL      test         t36644                        tertiary   NULL
  PARTITION x1_idx OF INDEX test.public.t38391@foo                              NULL      test         t38391                        foo        x1_idx
  DATABASE "my database"                                                        NULL      my database  NULL                          NULL       NULL
  TABLE "my database".public."my table"                                         NULL      my database  my table                      NULL       NULL
  INDEX "my database".public."my table"@"my index"                              NULL      my database  my table                      my index   NULL
  PARTITION "my partition" OF INDEX "my database".public."my table"@primary     NULL      my database  my table                      primary    my partition
  PARTITION "my partition" OF INDEX "my database".public."my table"@"my index"  NULL      my database  my table                      my index   my partition
  
  # Test the zone information being displayed in SHOW CREATE
  statement ok
  CREATE TABLE show_test (x INT PRIMARY KEY) PARTITION BY LIST (x) (
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2)
  )
  
  statement ok
  ALTER PARTITION p1 OF TABLE show_test CONFIGURE ZONE USING CONSTRAINTS='[+dc=dc1]'
  
  statement ok
  ALTER PARTITION p2 OF TABLE show_test CONFIGURE ZONE USING CONSTRAINTS='[+dc=dc2]'
  
  query TT
  SHOW CREATE TABLE show_test
  ----
  show_test  CREATE TABLE public.show_test (
             x INT8 NOT NULL,
             CONSTRAINT "primary" PRIMARY KEY (x ASC),
             FAMILY "primary" (x)
  ) PARTITION BY LIST (x) (
    PARTITION p1 VALUES IN ((1)),
    PARTITION p2 VALUES IN ((2))
  );
  ALTER PARTITION p1 OF INDEX "my database".public.show_test@primary CONFIGURE ZONE USING
    constraints = '[+dc=dc1]';
  ALTER PARTITION p2 OF INDEX "my database".public.show_test@primary CONFIGURE ZONE USING
    constraints = '[+dc=dc2]'
  
  # test warnings on table creation
  statement ok
  CREATE TABLE warning (x INT PRIMARY KEY)
  
  statement ok
  ALTER TABLE warning PARTITION BY LIST (x) (PARTITION p1 VALUES IN (1))
  
  query TT
  SHOW CREATE warning
  ----
  warning  CREATE TABLE public.warning (
           x INT8 NOT NULL,
           CONSTRAINT "primary" PRIMARY KEY (x ASC),
           FAMILY "primary" (x)
  ) PARTITION BY LIST (x) (
    PARTITION p1 VALUES IN ((1))
  )
  -- Warning: Partitioned table with no zone configurations.
  
  subtest alter_partition_across_all_indexes
  
  statement ok
  CREATE TABLE t2 (x INT PRIMARY KEY) PARTITION BY LIST (x) (
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2)
  );
  CREATE INDEX x1 ON t2 (x) PARTITION BY LIST (x) (
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2)
  );
  CREATE INDEX x2 ON t2 (x) PARTITION BY LIST (x) (
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2),
    PARTITION p3 VALUES IN (3)
  )
  
  statement ok
  ALTER PARTITION p1 OF INDEX t2@* CONFIGURE ZONE USING num_replicas = 1
  
  query T
  SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.partitioning.alter-all-partitions' AND usage_count > 0
  ----
  sql.partitioning.alter-all-partitions
  
  statement error index "t2" does not exist\nHINT: try specifying the index as <tablename>@<indexname>
  ALTER PARTITION p1 OF INDEX t2 CONFIGURE ZONE USING num_replicas = 1
  
  query TT
  SELECT * FROM [SHOW ALL ZONE CONFIGURATIONS] WHERE target LIKE '%t2@%'
  ----
  PARTITION p1 OF INDEX "my database".public.t2@primary  ALTER PARTITION p1 OF INDEX "my database".public.t2@primary CONFIGURE ZONE USING
                                                         num_replicas = 1
  PARTITION p1 OF INDEX "my database".public.t2@x1       ALTER PARTITION p1 OF INDEX "my database".public.t2@x1 CONFIGURE ZONE USING
                                                         num_replicas = 1
  PARTITION p1 OF INDEX "my database".public.t2@x2       ALTER PARTITION p1 OF INDEX "my database".public.t2@x2 CONFIGURE ZONE USING
                                                         num_replicas = 1
  
  # ALTER PARTITION ... OF TABLE should only succeed if the partition name is
  # unique across all indexes.
  statement error pq: partition "p1" exists on multiple indexes of table "t2"
  ALTER PARTITION p1 OF TABLE t2 CONFIGURE ZONE USING num_replicas = 1
  
  statement ok
  ALTER PARTITION p3 OF TABLE t2 CONFIGURE ZONE USING num_replicas = 1
  
  query TT
  SELECT * FROM [SHOW ALL ZONE CONFIGURATIONS] WHERE target LIKE '%t2@x2%'
  ----
  PARTITION p1 OF INDEX "my database".public.t2@x2  ALTER PARTITION p1 OF INDEX "my database".public.t2@x2 CONFIGURE ZONE USING
                                                    num_replicas = 1
  PARTITION p3 OF INDEX "my database".public.t2@x2  ALTER PARTITION p3 OF INDEX "my database".public.t2@x2 CONFIGURE ZONE USING
                                                    num_replicas = 1
  
  statement error pq: partition "p4" does not exist on table "t2"
  ALTER PARTITION p4 OF TABLE t2 CONFIGURE ZONE USING num_replicas = 1
  
  # regression for #40417
  statement ok
  CREATE TABLE t40417 (x INT PRIMARY KEY)
  
  statement ok
  ALTER TABLE t40417 PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1));
  
  statement ok
  ALTER PARTITION p1 OF TABLE t40417 CONFIGURE ZONE USING num_replicas = 1
  
  query TT
  SHOW CREATE TABLE t40417
  ----
  t40417  CREATE TABLE public.t40417 (
          x INT8 NOT NULL,
          CONSTRAINT "primary" PRIMARY KEY (x ASC),
          FAMILY "primary" (x)
  ) PARTITION BY LIST (x) (
    PARTITION p1 VALUES IN ((1))
  );
  ALTER PARTITION p1 OF INDEX "my database".public.t40417@primary CONFIGURE ZONE USING
    num_replicas = 1
  
  subtest authorization
  
  statement ok
  CREATE DATABASE auth;
  CREATE TABLE auth.t (x INT PRIMARY KEY) PARTITION BY LIST (x) (
    PARTITION p VALUES IN (1)
  );
  CREATE INDEX x ON auth.t (x) PARTITION BY LIST (x) (
    PARTITION p VALUES IN (1)
  )
  
  user testuser
  
  # User should have no CONFIGURE ZONE abilities by default.
  statement error only users with the admin role are allowed to alter system ranges
  ALTER RANGE default CONFIGURE ZONE USING num_replicas = 3
  
  statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on database auth
  ALTER DATABASE auth CONFIGURE ZONE USING num_replicas = 3
  
  statement error pq: only users with the admin role are allowed to alter system tables
  ALTER TABLE system.jobs CONFIGURE ZONE USING num_replicas = 3
  
  statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t
  ALTER TABLE auth.t CONFIGURE ZONE USING num_replicas = 3
  
  statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t
  ALTER PARTITION p OF TABLE auth.t CONFIGURE ZONE USING num_replicas = 3
  
  statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t
  ALTER PARTITION p OF INDEX auth.t@x CONFIGURE ZONE USING num_replicas = 3
  
  # Granting CREATE on databases and tables should allow CONFIGURE ZONE on those
  # objects.
  user root
  
  statement ok
  GRANT CREATE ON DATABASE auth TO testuser
  
  statement ok
  GRANT CREATE ON TABLE auth.t TO testuser
  
  user testuser
  
  statement ok
  ALTER DATABASE auth CONFIGURE ZONE USING num_replicas = 3
  
  user root
  
  statement ok
  REVOKE CREATE ON DATABASE auth FROM testuser;
  REVOKE CREATE ON TABLE auth.t FROM testuser;
  
  user testuser
  
  statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on database auth
  ALTER DATABASE auth CONFIGURE ZONE USING num_replicas = 3
  
  statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t
  ALTER TABLE auth.t CONFIGURE ZONE USING num_replicas = 3
  
  
  # Granting ZONECONFIG privilege should allow configuring zones on database and tables
  user root
  
  statement ok
  CREATE TABLE auth.t2 (x INT PRIMARY KEY) PARTITION BY LIST (x) (
    PARTITION p VALUES IN (1)
  );
  GRANT ZONECONFIG ON TABLE auth.t2 to testuser
  
  user testuser
  
  statement ok
  ALTER TABLE auth.t2 CONFIGURE ZONE USING num_replicas = 3
  
  user root
  
  statement ok
  REVOKE ZONECONFIG ON TABLE auth.t2 FROM testuser;
  GRANT ZONECONFIG ON DATABASE auth TO testuser
  
  user testuser
  
  statement ok
  ALTER DATABASE auth CONFIGURE ZONE USING num_replicas = 3
  
  # Existing tables should not inherit ZONECONFIG privilege
  statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t
  ALTER TABLE auth.t CONFIGURE ZONE USING num_replicas = 3
  
  # New tables should inherit ZONECONFIG privilege
  user root
  
  statement ok
  CREATE TABLE auth.t3 (x INT PRIMARY KEY) PARTITION BY LIST (x) (
    PARTITION p VALUES IN (1)
  );
  CREATE INDEX x ON auth.t3 (x) PARTITION BY LIST (x) (
    PARTITION p VALUES IN (1)
  );
  
  user testuser
  
  statement ok
  ALTER TABLE auth.t3 CONFIGURE ZONE USING num_replicas = 3
  
  # Index and rows (partitions) should inherit table permissions
  statement ok
  ALTER INDEX auth.t3@x CONFIGURE ZONE USING num_replicas=5;
  ALTER PARTITION p OF INDEX auth.t3@x CONFIGURE ZONE USING num_replicas = 3
  
  # Granting the admin role should allow configuring zones on system tables and
  # ranges.
  user root
  
  statement ok
  GRANT admin TO testuser
  
  user testuser
  
  statement ok
  ALTER RANGE default CONFIGURE ZONE USING num_replicas = 3
  
  statement ok
  ALTER TABLE system.jobs CONFIGURE ZONE USING num_replicas = 3
  
  # Test that index configurations don't infect partition configurations.
  # Specifically we are testing that values written to infect@primary's
  # zone configuration does not appear in partition p1 of infect@primary's zone config.
  statement ok
  CREATE TABLE infect (x INT PRIMARY KEY);
  ALTER TABLE infect PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1));
  ALTER INDEX infect@primary CONFIGURE ZONE USING num_replicas=5;
  ALTER PARTITION p1 OF TABLE infect CONFIGURE ZONE USING constraints='[+dc=dc1]'
  
  query TT
  SELECT partition_name, zone_config FROM [SHOW PARTITIONS FROM TABLE infect]
  ----
  p1 constraints = '[+dc=dc1]'
  
  # regression for #38074
  statement ok
  CREATE TABLE t38074 (x INT, index i(x));
  
  statement ok
  ALTER INDEX t38074@i CONFIGURE ZONE USING gc.ttlseconds = 80000
  
  statement ok
  ALTER TABLE t38074 CONFIGURE ZONE USING gc.ttlseconds = 70000
  
  # Ensure that the table-level zone configuration is no longer a placeholder.
  query TTT
  SELECT table_name, index_name, full_config_sql FROM crdb_internal.zones WHERE
  table_name='t38074'
  ----
  t38074  NULL  ALTER TABLE test.public.t38074 CONFIGURE ZONE USING
          range_min_bytes = 134217728,
          range_max_bytes = 536870912,
          gc.ttlseconds = 70000,
          num_replicas = 3,
          constraints = '[]',
          lease_preferences = '[]'
  t38074  i                         ALTER INDEX test.public.t38074@i CONFIGURE ZONE USING
          range_min_bytes = 134217728,
          range_max_bytes = 536870912,
          gc.ttlseconds = 80000,
          num_replicas = 3,
          constraints = '[]',
          lease_preferences = '[]'
  
  # Regression test for #39994: verify that certain fields have to be set in tandem in indexes and partitions.
  statement ok
  CREATE TABLE validateTandemFields (a INT, b INT, c INT, PRIMARY KEY (a, b))
    PARTITION BY LIST (a, b) (PARTITION simple VALUES IN ((1, 1), (2, 2), (3, 3)))
  
  statement error pq: could not validate zone config: range_min_bytes and range_max_bytes must be set together
  ALTER PARTITION simple OF TABLE validateTandemFields CONFIGURE ZONE USING range_min_bytes = 66666
  
  statement ok
  CREATE INDEX secondary
      ON validateTandemFields (b)
      PARTITION BY LIST (b)
          (
              PARTITION indexPartition VALUES IN (2, 3, 4)
          )
  
  statement error pq: could not validate zone config: range_min_bytes and range_max_bytes must be set together
  ALTER INDEX validateTandemFields@secondary CONFIGURE ZONE USING range_min_bytes = 66666
  
  statement error pq: could not validate zone config: range_min_bytes and range_max_bytes must be set together
  ALTER PARTITION indexPartition OF INDEX validateTandemFields@secondary CONFIGURE ZONE USING range_min_bytes = 66666
  
  # Test that copy from parent works as expected.
  statement ok
  CREATE TABLE copy_from_parent (x INT PRIMARY KEY);
  ALTER TABLE copy_from_parent PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1))
  
  statement ok
  ALTER DATABASE test CONFIGURE ZONE USING num_replicas = 7
  
  # Test that first inheriting from the parent database works correctly.
  statement ok
  ALTER TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = COPY FROM PARENT
  
  query TT
  SELECT table_name, raw_config_sql FROM crdb_internal.zones WHERE table_name = 'copy_from_parent'
  ----
  copy_from_parent  ALTER TABLE test.public.copy_from_parent CONFIGURE ZONE USING
                    num_replicas = 7
  
  # Test that resetting the field manually works correctly.
  statement ok
  ALTER TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = 3
  
  query TT
  SELECT table_name, raw_config_sql FROM crdb_internal.zones WHERE table_name = 'copy_from_parent'
  ----
  copy_from_parent  ALTER TABLE test.public.copy_from_parent CONFIGURE ZONE USING
                    num_replicas = 3
  
  # Test that trying to apply COPY FROM PARENT again picks up the parent's value.
  statement ok
  ALTER TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = COPY FROM PARENT
  
  query TT
  SELECT table_name, raw_config_sql FROM crdb_internal.zones WHERE table_name = 'copy_from_parent'
  ----
  copy_from_parent  ALTER TABLE test.public.copy_from_parent CONFIGURE ZONE USING
                    num_replicas = 7
  
  # Ensure that the table has different zone configurations than its parent in
  # order to avoid accidentally copying the parent value.
  statement ok
  ALTER TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = 6
  
  # Test that the partition can inherit the table's configuration values.
  statement ok
  ALTER PARTITION p1 OF TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = 3
  
  query TTTT
  SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones
  WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name = 'p1'
  ----
  copy_from_parent  primary p1  ALTER PARTITION p1 OF INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING
                                num_replicas = 3
  
  statement ok
  ALTER PARTITION p1 OF TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = COPY FROM PARENT
  
  query TTTT
  SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones
  WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name = 'p1'
  ----
  copy_from_parent  primary p1  ALTER PARTITION p1 OF INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING
                                num_replicas = 6
  
  statement ok
  ALTER INDEX copy_from_parent@primary CONFIGURE ZONE USING num_replicas = 5
  
  query TTTT
  SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones
  WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name IS NULL
  ----
  copy_from_parent  primary  NULL ALTER INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING
                                  num_replicas = 5
  
  
  # Test that an index can inherit from its parent.
  statement ok
  ALTER INDEX copy_from_parent@primary CONFIGURE ZONE USING num_replicas = COPY FROM PARENT
  
  query TTTT
  SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones
  WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name IS NULL
  ----
  copy_from_parent  primary  NULL ALTER INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING
                                  num_replicas = 6
  
  # Test that a partition can inherit from its parent index configuration.
  
  # First change the index's field value.
  statement ok
  ALTER INDEX copy_from_parent@primary CONFIGURE ZONE USING num_replicas = 9
  
  query TTTT
  SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones
  WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name IS NULL
  ----
  copy_from_parent  primary  NULL ALTER INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING
                                  num_replicas = 9
  
  statement ok
  ALTER PARTITION p1 OF TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = COPY FROM PARENT
  
  query TTTT
  SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones
  WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name = 'p1'
  ----
  copy_from_parent  primary p1  ALTER PARTITION p1 OF INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING
                                num_replicas = 9
  
  # check that copy from parent on a subzone doesn't accidentally modify the parent zone.
  statement ok
  CREATE TABLE parent_modify (x INT, INDEX idx (x));
  ALTER TABLE parent_modify CONFIGURE ZONE USING gc.ttlseconds = 700;
  ALTER INDEX parent_modify@idx CONFIGURE ZONE USING num_replicas = COPY FROM PARENT
  
  query TTT
  SELECT table_name, index_name, raw_config_sql FROM crdb_internal.zones
  WHERE table_name = 'parent_modify' AND index_name = 'idx'
  ----
  parent_modify  idx  ALTER INDEX test.public.parent_modify@idx CONFIGURE ZONE USING
                      num_replicas = 7
  
  query TTT
  SELECT table_name, index_name, raw_config_sql FROM crdb_internal.zones
  WHERE table_name = 'parent_modify' AND index_name IS NULL
  ----
  parent_modify  NULL  ALTER TABLE test.public.parent_modify CONFIGURE ZONE USING
                       gc.ttlseconds = 700
  
  # Regression for #48254. Ensure that index rewrites in a primary key
  # change don't drop zone configs on rewritten indexes.
  statement ok
  DROP TABLE IF EXISTS t;
  CREATE TABLE t (
    x INT PRIMARY KEY,
    y INT NOT NULL,
    z INT,
    w INT,
    INDEX i1 (z),
    INDEX i2 (w),
    FAMILY (x, y, z, w)
  );
  ALTER INDEX t@i1 PARTITION BY LIST (z) (
    PARTITION p1 VALUES IN (1, 2),
    PARTITION p2 VALUES IN (3, 4)
  );
  ALTER INDEX t@i2 PARTITION BY LIST (w) (
    PARTITION p3 VALUES IN (5, 6),
    PARTITION p4 VALUES IN (7, 8)
  );
  ALTER PARTITION p1 OF INDEX t@i1 CONFIGURE ZONE USING gc.ttlseconds = 15210;
  ALTER PARTITION p2 OF INDEX t@i1 CONFIGURE ZONE USING gc.ttlseconds = 15213;
  ALTER PARTITION p3 OF INDEX t@i2 CONFIGURE ZONE USING gc.ttlseconds = 15411;
  ALTER PARTITION p4 OF INDEX t@i2 CONFIGURE ZONE USING gc.ttlseconds = 15418;
  ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y)
  
  # Ensure that all the partitions of i1 and i2 still have their zone configs.
  query TT
  SHOW CREATE t
  ----
  t  CREATE TABLE public.t (
     x INT8 NOT NULL,
     y INT8 NOT NULL,
     z INT8 NULL,
     w INT8 NULL,
     CONSTRAINT "primary" PRIMARY KEY (y ASC),
     UNIQUE INDEX t_x_key (x ASC),
     INDEX i1 (z ASC) PARTITION BY LIST (z) (
       PARTITION p1 VALUES IN ((1), (2)),
       PARTITION p2 VALUES IN ((3), (4))
     ),
     INDEX i2 (w ASC) PARTITION BY LIST (w) (
       PARTITION p3 VALUES IN ((5), (6)),
       PARTITION p4 VALUES IN ((7), (8))
     ),
     FAMILY fam_0_x_y_z_w (x, y, z, w)
  );
  ALTER PARTITION p1 OF INDEX test.public.t@i1 CONFIGURE ZONE USING
    gc.ttlseconds = 15210;
  ALTER PARTITION p2 OF INDEX test.public.t@i1 CONFIGURE ZONE USING
    gc.ttlseconds = 15213;
  ALTER PARTITION p3 OF INDEX test.public.t@i2 CONFIGURE ZONE USING
    gc.ttlseconds = 15411;
  ALTER PARTITION p4 OF INDEX test.public.t@i2 CONFIGURE ZONE USING
    gc.ttlseconds = 15418
  