# 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
