github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/ccl/logictestccl/testdata/logic_test/partitioning (about)

     1  # LogicTest: local
     2  
     3  statement error syntax
     4  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST ()
     5  
     6  statement error syntax
     7  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) ()
     8  
     9  statement error syntax
    10  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
    11      PARTITION p1 VALUES IN ()
    12  )
    13  
    14  # NB: This table gets the automatic, hidden unique_rowid PK.
    15  statement error declared partition columns \(a\) do not match first 1 columns in index being partitioned \(rowid\)
    16  CREATE TABLE t (a INT, b INT, c INT) PARTITION BY LIST (a) (
    17      PARTITION p1 VALUES IN (0)
    18  )
    19  
    20  statement error declared partition columns \(a, b, c\) exceed the number of columns in index being partitioned \(a, b\)
    21  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a, b, c) (
    22      PARTITION p1 VALUES IN (0)
    23  )
    24  
    25  statement error declared partition columns \(b\) do not match first 1 columns in index being partitioned \(a\)
    26  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (b) (
    27      PARTITION p1 VALUES IN (0)
    28  )
    29  
    30  statement error declared partition columns \(c\) do not match first 1 columns in index being partitioned \(a\)
    31  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (c) (
    32      PARTITION p1 VALUES IN (0)
    33  )
    34  
    35  statement error declared partition columns \(a, a\) do not match first 2 columns in index being partitioned \(a, b\)
    36  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
    37      PARTITION p1 VALUES IN (0) PARTITION BY LIST (a) (
    38          PARTITION p1_1 VALUES IN (0)
    39      )
    40  )
    41  
    42  statement error declared partition columns \(a, c\) do not match first 2 columns in index being partitioned \(a, b\)
    43  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b, c)) PARTITION BY LIST (a) (
    44      PARTITION p1 VALUES IN (0) PARTITION BY LIST (c) (
    45          PARTITION p1_1 VALUES IN (0)
    46      )
    47  )
    48  
    49  # Regression test for #37682
    50  statement error declared partition columns \(a, b\) do not match first 1 columns in index being partitioned \(rowid\)
    51  CREATE TABLE t (a INT DEFAULT 12, b INT DEFAULT 34) PARTITION BY LIST (a, b) (
    52    PARTITION p1 VALUES IN ((1,2)),
    53    PARTITION default VALUES IN (DEFAULT)
    54  )
    55  
    56  statement error PARTITION p1: name must be unique \(used twice in index "primary"\)
    57  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
    58      PARTITION p1 VALUES IN (1),
    59      PARTITION p1 VALUES IN (2)
    60  )
    61  
    62  statement error PARTITION p1: name must be unique \(used twice in index "primary"\)
    63  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a) (
    64      PARTITION p1 VALUES FROM (1) TO (2),
    65      PARTITION p1 VALUES FROM (2) TO (3)
    66  )
    67  
    68  statement error PARTITION p1: name must be unique \(used twice in index "primary"\)
    69  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
    70      PARTITION p1 VALUES IN (1),
    71      PARTITION P1 VALUES IN (2)
    72  )
    73  
    74  statement error PARTITION p1: name must be unique \(used twice in index "primary"\)
    75  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
    76      PARTITION p1 VALUES IN (1) PARTITION BY LIST (b) (
    77          PARTITION p1 VALUES IN (2)
    78      )
    79  )
    80  
    81  statement error PARTITION p1: name must be unique \(used twice in index "primary"\)
    82  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
    83      PARTITION p1 VALUES IN (1) PARTITION BY RANGE (b) (
    84          PARTITION p1 VALUES FROM (1) TO (2)
    85      )
    86  )
    87  
    88  statement error PARTITION p1: cannot subpartition a range partition
    89  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a) (
    90      PARTITION p1 VALUES FROM (0) TO (1) PARTITION BY LIST (b) (
    91          PARTITION p2 VALUES IN (2)
    92      )
    93  )
    94  
    95  statement ok
    96  CREATE TABLE interleave_root (a INT PRIMARY KEY) PARTITION BY LIST (a) (
    97      PARTITION p0 VALUES IN (0)
    98  )
    99  
   100  statement ok
   101  CREATE TABLE interleave_child (a INT PRIMARY KEY) INTERLEAVE IN PARENT interleave_root (a)
   102  
   103  statement error cannot set a zone config for interleaved index primary; set it on the root of the interleaved hierarchy instead
   104  CREATE TABLE t (a INT PRIMARY KEY) INTERLEAVE IN PARENT interleave_root (a) PARTITION BY LIST (a) (
   105      PARTITION p0 VALUES IN (0)
   106  )
   107  
   108  statement error PARTITION p1: partition has 1 columns but 2 values were supplied
   109  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   110      PARTITION p1 VALUES IN ((0, 1))
   111  )
   112  
   113  statement error PARTITION p1: partition has 2 columns but 1 values were supplied
   114  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a, b) (
   115      PARTITION p1 VALUES IN (0)
   116  )
   117  
   118  statement error \(1\) cannot be present in more than one partition
   119  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   120      PARTITION p1 VALUES IN (1),
   121      PARTITION p2 VALUES IN (1)
   122  )
   123  
   124  statement error \(NULL\) cannot be present in more than one partition
   125  CREATE TABLE t (a INT, b INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   126      PARTITION p1 VALUES IN (1, NULL),
   127      PARTITION p2 VALUES IN (2, NULL)
   128  )
   129  
   130  statement error \(DEFAULT\) cannot be present in more than one partition
   131  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   132      PARTITION p1 VALUES IN (DEFAULT),
   133      PARTITION p2 VALUES IN (DEFAULT)
   134  )
   135  
   136  statement error \(1, 2, DEFAULT\) cannot be present in more than one partition
   137  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b, c)) PARTITION BY LIST (a, b, c) (
   138      PARTITION p1 VALUES IN ((1, 2, DEFAULT)),
   139      PARTITION p2 VALUES IN ((1, 2, DEFAULT))
   140  )
   141  
   142  statement error \(1, DEFAULT, DEFAULT\) cannot be present in more than one partition
   143  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b, c)) PARTITION BY LIST (a, b, c) (
   144      PARTITION p1 VALUES IN ((1, DEFAULT, DEFAULT)),
   145      PARTITION p2 VALUES IN ((1, DEFAULT, DEFAULT))
   146  )
   147  
   148  statement error \(1.000\) cannot be present in more than one partition
   149  CREATE TABLE t (a DECIMAL PRIMARY KEY) PARTITION BY LIST (a) (
   150      PARTITION p1 VALUES IN (1.0:::decimal),
   151      PARTITION p2 VALUES IN (1.000:::decimal)
   152  )
   153  
   154  statement error PARTITION p1: non-DEFAULT value \(1\) not allowed after DEFAULT
   155  CREATE TABLE t (a INT, b INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a, b) (
   156      PARTITION p1 VALUES IN ((DEFAULT, 1))
   157  )
   158  
   159  # Ensure this error takes precedence over the "cannot be present in more than
   160  # one partition" error.
   161  statement error PARTITION p1: non-DEFAULT value \(1\) not allowed after DEFAULT
   162  CREATE TABLE t (a INT, b INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a, b) (
   163      PARTITION p1 VALUES IN ((DEFAULT, 1)),
   164      PARTITION p2 VALUES IN ((DEFAULT, 1))
   165  )
   166  
   167  statement error PARTITION p1: empty range: lower bound \(0\) is equal to upper bound \(0\)
   168  CREATE TABLE t (a INT PRIMARY KEY) PARTITION BY RANGE (a) (
   169      PARTITION p1 VALUES FROM (0) TO (0)
   170  )
   171  
   172  statement error PARTITION p1: empty range: lower bound \(1.00\) is equal to upper bound \(1.000\)
   173  CREATE TABLE t (a DECIMAL PRIMARY KEY) PARTITION BY RANGE (a) (
   174      PARTITION p1 VALUES FROM (1.00:::decimal) TO (1.000::decimal)
   175  )
   176  
   177  statement error PARTITION p1: empty range: lower bound \(1, MAXVALUE\) is equal to upper bound \(2, MINVALUE\)
   178  CREATE TABLE t (a INT, b INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b) (
   179    PARTITION p1 VALUES FROM (1, MAXVALUE) TO (2, MINVALUE)
   180  )
   181  
   182  statement error PARTITION p1: empty range: lower bound \(MAXVALUE\) is greater than upper bound \(MINVALUE\)
   183  CREATE TABLE t (a INT PRIMARY KEY) PARTITION BY RANGE (a) (
   184      PARTITION p1 VALUES FROM (MAXVALUE) TO (MINVALUE)
   185  )
   186  
   187  statement error PARTITION p1: empty range: lower bound \(1\) is greater than upper bound \(0\)
   188  CREATE TABLE t (a INT PRIMARY KEY) PARTITION BY RANGE (a) (
   189      PARTITION p1 VALUES FROM (1) TO (0)
   190  )
   191  
   192  statement error PARTITION p1: empty range: lower bound \(0\) is greater than upper bound \(NULL\)
   193  CREATE TABLE t (a INT PRIMARY KEY) PARTITION BY RANGE (a) (
   194      PARTITION p1 VALUES FROM (0) TO (NULL)
   195  )
   196  
   197  statement error PARTITION p1: empty range: lower bound \(NULL\) is greater than upper bound \(0\)
   198  CREATE TABLE t (a INT, PRIMARY KEY (a DESC)) PARTITION BY RANGE (a) (
   199      PARTITION p1 VALUES FROM (NULL) TO (0)
   200  )
   201  
   202  statement error PARTITION p1: empty range: lower bound \(NULL\) is greater than upper bound \(MINVALUE\)
   203  CREATE TABLE t (a INT PRIMARY KEY) PARTITION BY RANGE (a) (
   204      PARTITION p1 VALUES FROM (NULL) TO (MINVALUE)
   205  )
   206  
   207  statement error PARTITION p1: empty range: lower bound \(MAXVALUE\) is greater than upper bound \(NULL\)
   208  CREATE TABLE t (a INT PRIMARY KEY) PARTITION BY RANGE (a) (
   209      PARTITION p1 VALUES FROM (MAXVALUE) TO (NULL)
   210  )
   211  
   212  statement error PARTITION p1: empty range: lower bound \(NULL\) is greater than upper bound \(MINVALUE\)
   213  CREATE TABLE t (a INT, PRIMARY KEY (a DESC)) PARTITION BY RANGE (a) (
   214      PARTITION p1 VALUES FROM (NULL) TO (MINVALUE)
   215  )
   216  
   217  statement error PARTITION p1: empty range: lower bound \(MAXVALUE\) is greater than upper bound \(NULL\)
   218  CREATE TABLE t (a INT, PRIMARY KEY (a DESC)) PARTITION BY RANGE (a) (
   219      PARTITION p1 VALUES FROM (MAXVALUE) TO (NULL)
   220  )
   221  
   222  statement error partitions p1 and p2 overlap
   223  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a) (
   224      PARTITION p1 VALUES FROM (-3) TO (2),
   225      PARTITION p2 VALUES FROM (1) TO (3)
   226  )
   227  
   228  statement error partitions p2 and p1 overlap
   229  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b, c)) PARTITION BY RANGE (a, b, c) (
   230      PARTITION p2 VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, 2, MAXVALUE),
   231      PARTITION p1 VALUES FROM (1, 2, 99) TO (MAXVALUE, MAXVALUE, MAXVALUE)
   232  )
   233  
   234  statement error partitions p1 and p2 overlap
   235  CREATE TABLE t (a INT PRIMARY KEY) PARTITION BY RANGE (a) (
   236      PARTITION p1 VALUES FROM (NULL) TO (0),
   237      PARTITION p2 VALUES FROM (NULL) TO (1)
   238  )
   239  
   240  statement error partitions p1 and p2 overlap
   241  CREATE TABLE t (a INT PRIMARY KEY) PARTITION BY RANGE (a) (
   242      PARTITION p1 VALUES FROM (NULL) TO (MAXVALUE),
   243      PARTITION p2 VALUES FROM (1) TO (2)
   244  )
   245  
   246  statement error partitions p1 and p2 overlap
   247  CREATE TABLE t (a INT, PRIMARY KEY (a DESC)) PARTITION BY RANGE (a) (
   248      PARTITION p1 VALUES FROM (MINVALUE) TO (NULL),
   249      PARTITION p2 VALUES FROM (2) TO (1)
   250  )
   251  
   252  statement error partitions p2 and p1 overlap
   253  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b, c)) PARTITION BY RANGE (a, b, c) (
   254      PARTITION p2 VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE),
   255      PARTITION p1 VALUES FROM (1, 999, 9999) TO (MAXVALUE, MAXVALUE, MAXVALUE)
   256  )
   257  
   258  statement error PARTITION p1: non-MAXVALUE value \(1\) not allowed after MAXVALUE
   259  CREATE TABLE t (a INT, b INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b) (
   260      PARTITION p1 VALUES FROM (MAXVALUE, 1) TO (MAXVALUE, MAXVALUE)
   261  )
   262  
   263  statement error PARTITION p1: non-MAXVALUE value \(MINVALUE\) not allowed after MAXVALUE
   264  CREATE TABLE t (a INT, b INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b) (
   265      PARTITION p1 VALUES FROM (MAXVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE)
   266  )
   267  
   268  statement error PARTITION p1: non-MINVALUE value \(MAXVALUE\) not allowed after MINVALUE
   269  CREATE TABLE t (a INT, b INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b) (
   270      PARTITION p1 VALUES FROM (MINVALUE, MAXVALUE) TO (MAXVALUE, MAXVALUE)
   271  )
   272  
   273  statement error PARTITION p1: empty range: lower bound \(e'\\u00FC' COLLATE da\) is greater than upper bound \('x' COLLATE da\)
   274  CREATE TABLE t (a STRING COLLATE da PRIMARY KEY) PARTITION BY RANGE (a) (
   275      PARTITION p1 VALUES FROM ('ü' COLLATE da) TO ('x' COLLATE da)
   276  )
   277  
   278  statement ok
   279  CREATE TABLE collate_da (a STRING COLLATE da PRIMARY KEY) PARTITION BY RANGE (a) (
   280      PARTITION p1 VALUES FROM ('x' COLLATE da) TO ('ü' COLLATE da)
   281  )
   282  
   283  statement error PARTITION p1: empty range: lower bound \('x' COLLATE de\) is greater than upper bound \(e'\\u00FC' COLLATE de\)
   284  CREATE TABLE t (a STRING COLLATE de PRIMARY KEY) PARTITION BY RANGE (a) (
   285      PARTITION p1 VALUES FROM ('x' COLLATE de) TO ('ü' COLLATE de)
   286  )
   287  
   288  statement ok
   289  CREATE TABLE collate_de (a STRING COLLATE de PRIMARY KEY) PARTITION BY RANGE (a) (
   290      PARTITION p1 VALUES FROM ('ü' COLLATE de) TO ('x' COLLATE de)
   291  )
   292  
   293  statement error expected partition expression to have type int, but '1.2' has type decimal
   294  CREATE TABLE foo (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   295      PARTITION p1 VALUES IN (1.2)
   296  )
   297  
   298  statement error expected partition expression to have type int, but '1.2' has type decimal
   299  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   300      PARTITION p1 VALUES IN (0) PARTITION BY LIST (b) (
   301          PARTITION p1_1 VALUES IN (1.2)
   302      )
   303  )
   304  
   305  statement error PARTITION p1: MINVALUE cannot be used with PARTITION BY LIST
   306  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   307      PARTITION p1 VALUES IN (MINVALUE)
   308  )
   309  
   310  statement error PARTITION p1: MINVALUE cannot be used with PARTITION BY LIST
   311  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a, b) (
   312      PARTITION p1 VALUES IN ((1, 1), (1, (MINVALUE)))
   313  )
   314  
   315  statement error PARTITION p1: MAXVALUE cannot be used with PARTITION BY LIST
   316  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   317      PARTITION p1 VALUES IN (MAXVALUE)
   318  )
   319  
   320  statement error PARTITION p1: MAXVALUE cannot be used with PARTITION BY LIST
   321  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a, b) (
   322      PARTITION p1 VALUES IN ((1, 1), (1, (MAXVALUE)))
   323  )
   324  
   325  statement error PARTITION p1: DEFAULT cannot be used with PARTITION BY RANGE
   326  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a) (
   327      PARTITION p1 VALUES FROM (DEFAULT) TO (0)
   328  )
   329  
   330  statement error PARTITION p1: DEFAULT cannot be used with PARTITION BY RANGE
   331  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a) (
   332      PARTITION p1 VALUES FROM (0) TO (DEFAULT)
   333  )
   334  
   335  statement error PARTITION p1: DEFAULT cannot be used with PARTITION BY RANGE
   336  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a) (
   337      PARTITION p1 VALUES FROM ((DEFAULT)) TO (0)
   338  )
   339  
   340  statement error PARTITION p1: DEFAULT cannot be used with PARTITION BY RANGE
   341  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a) (
   342      PARTITION p1 VALUES FROM (0) TO ((DEFAULT))
   343  )
   344  
   345  statement error PARTITION p1: DEFAULT cannot be used with PARTITION BY RANGE
   346  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b) (
   347      PARTITION p1 VALUES FROM (0, (DEFAULT)) TO (0, 0)
   348  )
   349  
   350  statement error PARTITION p1: DEFAULT cannot be used with PARTITION BY RANGE
   351  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b) (
   352      PARTITION p1 VALUES FROM (0, 0) TO (0, (DEFAULT))
   353  )
   354  
   355  # TODO(radu): we are not properly walking the expressions when
   356  # walking CREATE TABLE.
   357  statement error pq: could not determine data type of placeholder \$1
   358  PREPARE a AS CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   359      PARTITION p1 VALUES IN ($1:::int)
   360  )
   361  
   362  statement error syntax error
   363  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   364      PARTITION p1 VALUES IN (SELECT 1)
   365  )
   366  
   367  statement error PARTITION p1: variable sub-expressions are not allowed in partition
   368  CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY (a, b)) PARTITION BY LIST (a) (
   369      PARTITION p1 VALUES IN ((SELECT 1))
   370  )
   371  
   372  statement error PARTITION p1: now\(\): impure functions are not allowed in partition
   373  CREATE TABLE t (a TIMESTAMP PRIMARY KEY) PARTITION BY LIST (a) (
   374      PARTITION p1 VALUES IN (now())
   375  )
   376  
   377  statement error PARTITION p1: uuid_v4\(\): impure functions are not allowed in partition
   378  CREATE TABLE t (a TIMESTAMP PRIMARY KEY) PARTITION BY LIST (a) (
   379      PARTITION p1 VALUES IN (uuid_v4() || 'foo')
   380  )
   381  
   382  statement ok
   383  CREATE TABLE ok1 (
   384    a INT, b INT, c INT,
   385    PRIMARY KEY (a, b),
   386    FAMILY "primary" (a, b, c)
   387  )
   388    PARTITION BY LIST (a)
   389      (
   390        PARTITION p1 VALUES IN (1),
   391        PARTITION p2 VALUES IN (2)
   392      )
   393  
   394  query TT
   395  SHOW CREATE TABLE ok1
   396  ----
   397  ok1  CREATE TABLE ok1 (
   398       a INT8 NOT NULL,
   399       b INT8 NOT NULL,
   400       c INT8 NULL,
   401       CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   402       FAMILY "primary" (a, b, c)
   403  ) PARTITION BY LIST (a) (
   404     PARTITION p1 VALUES IN ((1)),
   405     PARTITION p2 VALUES IN ((2))
   406  )
   407  -- Warning: Partitioned table with no zone configurations.
   408  
   409  query T
   410  SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.show.create' AND usage_count > 0
   411  ----
   412  sql.show.create
   413  
   414  query T
   415  EXPLAIN (OPT, CATALOG) SELECT * from ok1
   416  ----
   417  TABLE ok1
   418   ├── a int not null
   419   ├── b int not null
   420   ├── c int
   421   └── INDEX primary
   422        ├── a int not null
   423        ├── b int not null
   424        └── partition by list prefixes
   425             ├── (1)
   426             └── (2)
   427  scan ok1
   428  
   429  statement ok
   430  CREATE TABLE ok2 (
   431    a INT, b INT, c INT,
   432    PRIMARY KEY (a, b),
   433    FAMILY "primary" (a, b, c)
   434  )
   435    PARTITION BY LIST (a)
   436      (
   437        PARTITION p1 VALUES IN (1),
   438        PARTITION p2 VALUES IN (2)
   439      )
   440  
   441  query TT
   442  SHOW CREATE TABLE ok2
   443  ----
   444  ok2  CREATE TABLE ok2 (
   445       a INT8 NOT NULL,
   446       b INT8 NOT NULL,
   447       c INT8 NULL,
   448       CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   449       FAMILY "primary" (a, b, c)
   450  ) PARTITION BY LIST (a) (
   451     PARTITION p1 VALUES IN ((1)),
   452     PARTITION p2 VALUES IN ((2))
   453  )
   454  -- Warning: Partitioned table with no zone configurations.
   455  
   456  query T
   457  EXPLAIN (OPT, CATALOG) SELECT * from ok2
   458  ----
   459  TABLE ok2
   460   ├── a int not null
   461   ├── b int not null
   462   ├── c int
   463   └── INDEX primary
   464        ├── a int not null
   465        ├── b int not null
   466        └── partition by list prefixes
   467             ├── (1)
   468             └── (2)
   469  scan ok2
   470  
   471  statement ok
   472  CREATE TABLE ok3 (
   473    a INT, b INT, c INT,
   474    PRIMARY KEY (a, b),
   475    FAMILY "primary" (a, b, c)
   476  )
   477    PARTITION BY LIST (a)
   478      (
   479        PARTITION p1 VALUES IN (1),
   480        PARTITION p2 VALUES IN (DEFAULT)
   481      )
   482  
   483  query TT
   484  SHOW CREATE TABLE ok3
   485  ----
   486  ok3  CREATE TABLE ok3 (
   487       a INT8 NOT NULL,
   488       b INT8 NOT NULL,
   489       c INT8 NULL,
   490       CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   491       FAMILY "primary" (a, b, c)
   492  ) PARTITION BY LIST (a) (
   493     PARTITION p1 VALUES IN ((1)),
   494     PARTITION p2 VALUES IN ((DEFAULT))
   495  )
   496  -- Warning: Partitioned table with no zone configurations.
   497  
   498  query T
   499  EXPLAIN (OPT, CATALOG) SELECT * from ok3
   500  ----
   501  TABLE ok3
   502   ├── a int not null
   503   ├── b int not null
   504   ├── c int
   505   └── INDEX primary
   506        ├── a int not null
   507        ├── b int not null
   508        └── partition by list prefixes
   509             └── (1)
   510  scan ok3
   511  
   512  statement ok
   513  CREATE TABLE ok4 (
   514    a INT, b INT, c INT,
   515    PRIMARY KEY (a, b),
   516    FAMILY "primary" (a, b, c)
   517  )
   518    PARTITION BY LIST (a, b)
   519      (
   520        PARTITION p1 VALUES IN ((1, 1)),
   521        PARTITION p2 VALUES IN ((1, DEFAULT)),
   522        PARTITION p3 VALUES IN ((2, 3)),
   523        PARTITION p4 VALUES IN ((DEFAULT, DEFAULT))
   524      )
   525  
   526  query TT
   527  SHOW CREATE TABLE ok4
   528  ----
   529  ok4  CREATE TABLE ok4 (
   530       a INT8 NOT NULL,
   531       b INT8 NOT NULL,
   532       c INT8 NULL,
   533       CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   534       FAMILY "primary" (a, b, c)
   535  ) PARTITION BY LIST (a, b) (
   536     PARTITION p1 VALUES IN ((1, 1)),
   537     PARTITION p2 VALUES IN ((1, DEFAULT)),
   538     PARTITION p3 VALUES IN ((2, 3)),
   539     PARTITION p4 VALUES IN ((DEFAULT, DEFAULT))
   540  )
   541  -- Warning: Partitioned table with no zone configurations.
   542  
   543  query T
   544  EXPLAIN (OPT, CATALOG) SELECT * from ok4
   545  ----
   546  TABLE ok4
   547   ├── a int not null
   548   ├── b int not null
   549   ├── c int
   550   └── INDEX primary
   551        ├── a int not null
   552        ├── b int not null
   553        └── partition by list prefixes
   554             ├── (1, 1)
   555             ├── (1)
   556             └── (2, 3)
   557  scan ok4
   558  
   559  statement ok
   560  CREATE TABLE ok5 (
   561    a INT, b INT, c INT,
   562    PRIMARY KEY (a, b),
   563    FAMILY "primary" (a, b, c)
   564  )
   565    PARTITION BY LIST (a)
   566      (
   567        PARTITION p1
   568          VALUES IN (1)
   569          PARTITION BY LIST (b)
   570            (
   571              PARTITION p1_1 VALUES IN (1),
   572              PARTITION p1_2 VALUES IN (DEFAULT)
   573            ),
   574        PARTITION p2
   575          VALUES IN (2)
   576          PARTITION BY LIST (b)
   577            (PARTITION p2_1 VALUES IN (3)),
   578        PARTITION p3 VALUES IN (DEFAULT)
   579      )
   580  
   581  query T
   582  EXPLAIN (OPT, CATALOG) SELECT * from ok5
   583  ----
   584  TABLE ok5
   585   ├── a int not null
   586   ├── b int not null
   587   ├── c int
   588   └── INDEX primary
   589        ├── a int not null
   590        ├── b int not null
   591        └── partition by list prefixes
   592             ├── (1)
   593             └── (2)
   594  scan ok5
   595  
   596  query TT
   597  SHOW CREATE TABLE ok5
   598  ----
   599  ok5  CREATE TABLE ok5 (
   600       a INT8 NOT NULL,
   601       b INT8 NOT NULL,
   602       c INT8 NULL,
   603       CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   604       FAMILY "primary" (a, b, c)
   605  ) PARTITION BY LIST (a) (
   606     PARTITION p1 VALUES IN ((1)) PARTITION BY LIST (b) (
   607       PARTITION p1_1 VALUES IN ((1)),
   608       PARTITION p1_2 VALUES IN ((DEFAULT))
   609     ),
   610     PARTITION p2 VALUES IN ((2)) PARTITION BY LIST (b) (
   611       PARTITION p2_1 VALUES IN ((3))
   612     ),
   613     PARTITION p3 VALUES IN ((DEFAULT))
   614  )
   615  -- Warning: Partitioned table with no zone configurations.
   616  
   617  statement ok
   618  CREATE TABLE ok6 (
   619    a INT, b INT, c INT,
   620    PRIMARY KEY (a, b),
   621    FAMILY "primary" (a, b, c)
   622  )
   623    PARTITION BY RANGE (a)
   624      (
   625        PARTITION p1 VALUES FROM (0) TO (1),
   626        PARTITION p2 VALUES FROM (1) TO (2)
   627      )
   628  
   629  query TT
   630  SHOW CREATE TABLE ok6
   631  ----
   632  ok6  CREATE TABLE ok6 (
   633       a INT8 NOT NULL,
   634       b INT8 NOT NULL,
   635       c INT8 NULL,
   636       CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   637       FAMILY "primary" (a, b, c)
   638  ) PARTITION BY RANGE (a) (
   639     PARTITION p1 VALUES FROM (0) TO (1),
   640     PARTITION p2 VALUES FROM (1) TO (2)
   641  )
   642  -- Warning: Partitioned table with no zone configurations.
   643  
   644  query T
   645  EXPLAIN (OPT, CATALOG) SELECT * from ok6
   646  ----
   647  TABLE ok6
   648   ├── a int not null
   649   ├── b int not null
   650   ├── c int
   651   └── INDEX primary
   652        ├── a int not null
   653        └── b int not null
   654  scan ok6
   655  
   656  statement ok
   657  CREATE TABLE ok7 (
   658    a INT, b INT, c INT,
   659    PRIMARY KEY (a, b),
   660    FAMILY "primary" (a, b, c)
   661  )
   662    PARTITION BY RANGE (a)
   663      (PARTITION p1 VALUES FROM (0) TO (1))
   664  
   665  query TT
   666  SHOW CREATE TABLE ok7
   667  ----
   668  ok7  CREATE TABLE ok7 (
   669       a INT8 NOT NULL,
   670       b INT8 NOT NULL,
   671       c INT8 NULL,
   672       CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   673       FAMILY "primary" (a, b, c)
   674  ) PARTITION BY RANGE (a) (
   675     PARTITION p1 VALUES FROM (0) TO (1)
   676  )
   677  -- Warning: Partitioned table with no zone configurations.
   678  
   679  query T
   680  EXPLAIN (OPT, CATALOG) SELECT * from ok7
   681  ----
   682  TABLE ok7
   683   ├── a int not null
   684   ├── b int not null
   685   ├── c int
   686   └── INDEX primary
   687        ├── a int not null
   688        └── b int not null
   689  scan ok7
   690  
   691  statement ok
   692  CREATE TABLE ok8 (
   693    a INT, b INT, c INT,
   694    PRIMARY KEY (a, b),
   695    FAMILY "primary" (a, b, c)
   696  )
   697    PARTITION BY RANGE (a)
   698      (
   699        PARTITION p1 VALUES FROM (minvalue) TO (1),
   700        PARTITION p2 VALUES FROM (1) TO (2),
   701        PARTITION p3 VALUES FROM (2) TO (maxvalue)
   702      )
   703  
   704  query TT
   705  SHOW CREATE TABLE ok8
   706  ----
   707  ok8  CREATE TABLE ok8 (
   708       a INT8 NOT NULL,
   709       b INT8 NOT NULL,
   710       c INT8 NULL,
   711       CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   712       FAMILY "primary" (a, b, c)
   713  ) PARTITION BY RANGE (a) (
   714     PARTITION p1 VALUES FROM (MINVALUE) TO (1),
   715     PARTITION p2 VALUES FROM (1) TO (2),
   716     PARTITION p3 VALUES FROM (2) TO (MAXVALUE)
   717  )
   718  -- Warning: Partitioned table with no zone configurations.
   719  
   720  query T
   721  EXPLAIN (OPT, CATALOG) SELECT * from ok8
   722  ----
   723  TABLE ok8
   724   ├── a int not null
   725   ├── b int not null
   726   ├── c int
   727   └── INDEX primary
   728        ├── a int not null
   729        └── b int not null
   730  scan ok8
   731  
   732  statement ok
   733  CREATE TABLE ok9 (
   734    a INT, b INT, c INT,
   735    PRIMARY KEY (a, b),
   736    FAMILY "primary" (a, b, c)
   737  )
   738    PARTITION BY RANGE (a, b)
   739      (
   740        PARTITION p1 VALUES FROM (minvalue, minvalue) TO (1, maxvalue),
   741        PARTITION p2 VALUES FROM (1, maxvalue) TO (3, minvalue),
   742        PARTITION p3 VALUES FROM (3, minvalue) TO (3, maxvalue),
   743        PARTITION p4 VALUES FROM (3, maxvalue) TO (maxvalue, maxvalue)
   744      )
   745  
   746  query TT
   747  SHOW CREATE TABLE ok9
   748  ----
   749  ok9  CREATE TABLE ok9 (
   750       a INT8 NOT NULL,
   751       b INT8 NOT NULL,
   752       c INT8 NULL,
   753       CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   754       FAMILY "primary" (a, b, c)
   755  ) PARTITION BY RANGE (a, b) (
   756     PARTITION p1 VALUES FROM (MINVALUE, MINVALUE) TO (1, MAXVALUE),
   757     PARTITION p2 VALUES FROM (1, MAXVALUE) TO (3, MINVALUE),
   758     PARTITION p3 VALUES FROM (3, MINVALUE) TO (3, MAXVALUE),
   759     PARTITION p4 VALUES FROM (3, MAXVALUE) TO (MAXVALUE, MAXVALUE)
   760  )
   761  -- Warning: Partitioned table with no zone configurations.
   762  
   763  query T
   764  EXPLAIN (OPT, CATALOG) SELECT * from ok9
   765  ----
   766  TABLE ok9
   767   ├── a int not null
   768   ├── b int not null
   769   ├── c int
   770   └── INDEX primary
   771        ├── a int not null
   772        └── b int not null
   773  scan ok9
   774  
   775  statement ok
   776  CREATE TABLE ok10 (
   777    a INT, b INT, c INT,
   778    PRIMARY KEY (a, b),
   779    FAMILY "primary" (a, b, c)
   780  )
   781    PARTITION BY RANGE (a, b)
   782      (
   783        PARTITION p1 VALUES FROM (minvalue, minvalue) TO (1, 1),
   784        PARTITION p2 VALUES FROM (1, 1) TO (1, maxvalue),
   785        PARTITION p3 VALUES FROM (1, maxvalue) TO (2, maxvalue),
   786        PARTITION p4 VALUES FROM (2, maxvalue) TO (3, 4),
   787        PARTITION p5 VALUES FROM (3, 4) TO (maxvalue, maxvalue)
   788      )
   789  
   790  query TT
   791  SHOW CREATE TABLE ok10
   792  ----
   793  ok10  CREATE TABLE ok10 (
   794        a INT8 NOT NULL,
   795        b INT8 NOT NULL,
   796        c INT8 NULL,
   797        CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   798        FAMILY "primary" (a, b, c)
   799  ) PARTITION BY RANGE (a, b) (
   800     PARTITION p1 VALUES FROM (MINVALUE, MINVALUE) TO (1, 1),
   801     PARTITION p2 VALUES FROM (1, 1) TO (1, MAXVALUE),
   802     PARTITION p3 VALUES FROM (1, MAXVALUE) TO (2, MAXVALUE),
   803     PARTITION p4 VALUES FROM (2, MAXVALUE) TO (3, 4),
   804     PARTITION p5 VALUES FROM (3, 4) TO (MAXVALUE, MAXVALUE)
   805  )
   806  -- Warning: Partitioned table with no zone configurations.
   807  
   808  query T
   809  EXPLAIN (OPT, CATALOG) SELECT * from ok10
   810  ----
   811  TABLE ok10
   812   ├── a int not null
   813   ├── b int not null
   814   ├── c int
   815   └── INDEX primary
   816        ├── a int not null
   817        └── b int not null
   818  scan ok10
   819  
   820  statement ok
   821  CREATE TABLE ok11 (
   822    a INT, b INT, c INT,
   823    PRIMARY KEY (a, b, c),
   824    FAMILY "primary" (a, b, c)
   825  )
   826    PARTITION BY LIST (a)
   827      (
   828        PARTITION p1
   829          VALUES IN (1)
   830          PARTITION BY LIST (b)
   831            (
   832              PARTITION p1_1
   833                VALUES IN (3) PARTITION BY LIST (c) (PARTITION p1_1_1 VALUES IN (8)),
   834              PARTITION p1_2 VALUES IN (4)
   835            ),
   836        PARTITION p2
   837          VALUES IN (6) PARTITION BY RANGE (b) (PARTITION p2_1 VALUES FROM (7) TO (8))
   838      )
   839  
   840  query TT
   841  SHOW CREATE TABLE ok11
   842  ----
   843  ok11  CREATE TABLE ok11 (
   844        a INT8 NOT NULL,
   845        b INT8 NOT NULL,
   846        c INT8 NOT NULL,
   847        CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC, c ASC),
   848        FAMILY "primary" (a, b, c)
   849  ) PARTITION BY LIST (a) (
   850     PARTITION p1 VALUES IN ((1)) PARTITION BY LIST (b) (
   851       PARTITION p1_1 VALUES IN ((3)) PARTITION BY LIST (c) (
   852         PARTITION p1_1_1 VALUES IN ((8))
   853       ),
   854       PARTITION p1_2 VALUES IN ((4))
   855     ),
   856     PARTITION p2 VALUES IN ((6)) PARTITION BY RANGE (b) (
   857       PARTITION p2_1 VALUES FROM (7) TO (8)
   858     )
   859  )
   860  -- Warning: Partitioned table with no zone configurations.
   861  
   862  query T
   863  EXPLAIN (OPT, CATALOG) SELECT * from ok11
   864  ----
   865  TABLE ok11
   866   ├── a int not null
   867   ├── b int not null
   868   ├── c int not null
   869   └── INDEX primary
   870        ├── a int not null
   871        ├── b int not null
   872        ├── c int not null
   873        └── partition by list prefixes
   874             ├── (1)
   875             └── (6)
   876  scan ok11
   877  
   878  statement ok
   879  CREATE TABLE IF NOT EXISTS ok12 (
   880    a INT, b INT, c INT,
   881    PRIMARY KEY (a, b),
   882    FAMILY "primary" (a, b, c)
   883  )
   884    PARTITION BY LIST (a)
   885      (
   886        PARTITION pu VALUES IN (NULL),
   887        PARTITION p1 VALUES IN (1),
   888        PARTITION p2 VALUES IN (2)
   889      )
   890  
   891  query TT
   892  SHOW CREATE TABLE ok12
   893  ----
   894  ok12  CREATE TABLE ok12 (
   895        a INT8 NOT NULL,
   896        b INT8 NOT NULL,
   897        c INT8 NULL,
   898        CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC),
   899        FAMILY "primary" (a, b, c)
   900  ) PARTITION BY LIST (a) (
   901     PARTITION pu VALUES IN ((NULL)),
   902     PARTITION p1 VALUES IN ((1)),
   903     PARTITION p2 VALUES IN ((2))
   904  )
   905  -- Warning: Partitioned table with no zone configurations.
   906  
   907  query T
   908  EXPLAIN (OPT, CATALOG) SELECT * from ok12
   909  ----
   910  TABLE ok12
   911   ├── a int not null
   912   ├── b int not null
   913   ├── c int
   914   └── INDEX primary
   915        ├── a int not null
   916        ├── b int not null
   917        └── partition by list prefixes
   918             ├── (NULL)
   919             ├── (1)
   920             └── (2)
   921  scan ok12
   922  
   923  # Verify that creating a partition that includes NULL does not change the
   924  # implicit NOT NULL contrainst of a primary key.
   925  statement error null value in column "a" violates not-null constraint
   926  INSERT INTO ok12 (a, b, c) VALUES (NULL, 2, 3)
   927  
   928  query T noticetrace
   929  CREATE INDEX non_partitioned_idx ON ok1 (c)
   930  ----
   931  NOTICE: creating non-partitioned index on partitioned table may not be performant
   932  HINT: Consider modifying the index such that it is also partitioned.
   933  
   934  statement ok
   935  DROP INDEX ok1@non_partitioned_idx
   936  
   937  query T noticetrace
   938  CREATE TABLE t (a INT PRIMARY KEY, b INT, INDEX (b)) PARTITION BY LIST (a) (
   939      PARTITION p1 VALUES IN (1)
   940  )
   941  ----
   942  NOTICE: creating non-partitioned index on partitioned table may not be performant
   943  HINT: Consider modifying the index such that it is also partitioned.
   944  
   945  statement ok
   946  DROP TABLE t
   947  
   948  # regression tests for #40450
   949  statement ok
   950  CREATE DATABASE d_show_partitions
   951  
   952  statement ok
   953  CREATE TABLE d_show_partitions.t (x INT PRIMARY KEY) PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1))
   954  
   955  query TTTTTTTTT
   956  SHOW PARTITIONS FROM DATABASE d_show_partitions
   957  ----
   958  d_show_partitions  t  p1  NULL  x  t@primary  (1)  NULL  range_min_bytes = 134217728,
   959  range_max_bytes = 536870912,
   960  gc.ttlseconds = 90000,
   961  num_replicas = 3,
   962  constraints = '[]',
   963  lease_preferences = '[]'
   964  
   965  query TTTTTTTTT
   966  SHOW PARTITIONS FROM TABLE d_show_partitions.t
   967  ----
   968  d_show_partitions  t  p1  NULL  x  t@primary  (1)  NULL  range_min_bytes = 134217728,
   969  range_max_bytes = 536870912,
   970  gc.ttlseconds = 90000,
   971  num_replicas = 3,
   972  constraints = '[]',
   973  lease_preferences = '[]'
   974  
   975  query TTTTTTTTT
   976  SHOW PARTITIONS FROM INDEX d_show_partitions.t@primary
   977  ----
   978  d_show_partitions  t  p1  NULL  x  t@primary  (1)  NULL  range_min_bytes = 134217728,
   979  range_max_bytes = 536870912,
   980  gc.ttlseconds = 90000,
   981  num_replicas = 3,
   982  constraints = '[]',
   983  lease_preferences = '[]'
   984  
   985  statement ok
   986  CREATE DATABASE "show partitions"
   987  
   988  statement ok
   989  CREATE TABLE "show partitions".t (x INT PRIMARY KEY) PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1))
   990  
   991  query TTTTTTTTT
   992  SHOW PARTITIONS FROM DATABASE "show partitions"
   993  ----
   994  show partitions  t  p1  NULL  x  t@primary  (1)  NULL  range_min_bytes = 134217728,
   995  range_max_bytes = 536870912,
   996  gc.ttlseconds = 90000,
   997  num_replicas = 3,
   998  constraints = '[]',
   999  lease_preferences = '[]'
  1000  
  1001  query TTTTTTTTT
  1002  SHOW PARTITIONS FROM TABLE "show partitions".t
  1003  ----
  1004  show partitions  t  p1  NULL  x  t@primary  (1)  NULL  range_min_bytes = 134217728,
  1005  range_max_bytes = 536870912,
  1006  gc.ttlseconds = 90000,
  1007  num_replicas = 3,
  1008  constraints = '[]',
  1009  lease_preferences = '[]'
  1010  
  1011  query TTTTTTTTT
  1012  SHOW PARTITIONS FROM INDEX "show partitions".t@primary
  1013  ----
  1014  show partitions  t  p1  NULL  x  t@primary  (1)  NULL  range_min_bytes = 134217728,
  1015  range_max_bytes = 536870912,
  1016  gc.ttlseconds = 90000,
  1017  num_replicas = 3,
  1018  constraints = '[]',
  1019  lease_preferences = '[]'
  1020  
  1021  statement ok
  1022  CREATE DATABASE """"
  1023  
  1024  statement ok
  1025  CREATE TABLE """".t (x INT PRIMARY KEY) PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1))
  1026  
  1027  query TTTTTTTTT
  1028  SHOW PARTITIONS FROM DATABASE """"
  1029  ----
  1030  "  t  p1  NULL  x  t@primary  (1)  NULL  range_min_bytes = 134217728,
  1031  range_max_bytes = 536870912,
  1032  gc.ttlseconds = 90000,
  1033  num_replicas = 3,
  1034  constraints = '[]',
  1035  lease_preferences = '[]'
  1036  
  1037  query TTTTTTTTT
  1038  SHOW PARTITIONS FROM TABLE """".t
  1039  ----
  1040  "  t  p1  NULL  x  t@primary  (1)  NULL  range_min_bytes = 134217728,
  1041  range_max_bytes = 536870912,
  1042  gc.ttlseconds = 90000,
  1043  num_replicas = 3,
  1044  constraints = '[]',
  1045  lease_preferences = '[]'
  1046  
  1047  query TTTTTTTTT
  1048  SHOW PARTITIONS FROM INDEX """".t@primary
  1049  ----
  1050  "  t  p1  NULL  x  t@primary  (1)  NULL  range_min_bytes = 134217728,
  1051  range_max_bytes = 536870912,
  1052  gc.ttlseconds = 90000,
  1053  num_replicas = 3,
  1054  constraints = '[]',
  1055  lease_preferences = '[]'
  1056  
  1057  query T
  1058  SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.show.partitions' AND usage_count > 0
  1059  ----
  1060  sql.show.partitions
  1061  
  1062  # Testing show partitions with inherited constraints.
  1063  statement ok
  1064  CREATE TABLE t_inherit (x INT PRIMARY KEY)
  1065  
  1066  statement ok
  1067  ALTER TABLE t_inherit PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1) )
  1068  
  1069  query TTTTTTTTT
  1070  SHOW PARTITIONS FROM TABLE t_inherit
  1071  ----
  1072  test  t_inherit  p1  NULL  x  t_inherit@primary  (1)  NULL  range_min_bytes = 134217728,
  1073  range_max_bytes = 536870912,
  1074  gc.ttlseconds = 90000,
  1075  num_replicas = 3,
  1076  constraints = '[]',
  1077  lease_preferences = '[]'
  1078  
  1079  statement ok
  1080  ALTER PARTITION p1 of TABLE t_inherit CONFIGURE ZONE USING num_replicas=5
  1081  
  1082  query TTTTTTTTT
  1083  SHOW PARTITIONS FROM TABLE t_inherit
  1084  ----
  1085  test  t_inherit  p1  NULL  x  t_inherit@primary  (1)  num_replicas = 5  range_min_bytes = 134217728,
  1086  range_max_bytes = 536870912,
  1087  gc.ttlseconds = 90000,
  1088  num_replicas = 5,
  1089  constraints = '[]',
  1090  lease_preferences = '[]'
  1091  
  1092  statement ok
  1093  CREATE TABLE t_inherit_range (x INT PRIMARY KEY)
  1094  
  1095  statement ok
  1096  ALTER TABLE t_inherit_range PARTITION BY RANGE (x) ( PARTITION p1 VALUES FROM (1) to (2) )
  1097  
  1098  query TTTTTTTTT
  1099  SHOW PARTITIONS FROM TABLE t_inherit_range
  1100  ----
  1101  test  t_inherit_range  p1  NULL  x  t_inherit_range@primary  (1) TO (2)  NULL  range_min_bytes = 134217728,
  1102  range_max_bytes = 536870912,
  1103  gc.ttlseconds = 90000,
  1104  num_replicas = 3,
  1105  constraints = '[]',
  1106  lease_preferences = '[]'
  1107  
  1108  statement ok
  1109  ALTER PARTITION p1 of TABLE t_inherit_range CONFIGURE ZONE USING num_replicas=5
  1110  
  1111  query TTTTTTTTT
  1112  SHOW PARTITIONS FROM TABLE t_inherit_range
  1113  ----
  1114  test  t_inherit_range  p1  NULL  x  t_inherit_range@primary  (1) TO (2)  num_replicas = 5  range_min_bytes = 134217728,
  1115  range_max_bytes = 536870912,
  1116  gc.ttlseconds = 90000,
  1117  num_replicas = 5,
  1118  constraints = '[]',
  1119  lease_preferences = '[]'