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

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE TABLE ok1 (
     5      a INT PRIMARY KEY, b INT,
     6      INDEX (b) PARTITION BY LIST (b) (
     7          PARTITION p1 VALUES IN (1),
     8          PARTITION pu VALUES IN (NULL)
     9      ),
    10      FAMILY "primary" (a, b)
    11  )
    12  
    13  query TT
    14  SHOW CREATE TABLE ok1
    15  ----
    16  ok1  CREATE TABLE ok1 (
    17       a INT8 NOT NULL,
    18       b INT8 NULL,
    19       CONSTRAINT "primary" PRIMARY KEY (a ASC),
    20       INDEX ok1_b_idx (b ASC) PARTITION BY LIST (b) (
    21         PARTITION p1 VALUES IN ((1)),
    22         PARTITION pu VALUES IN ((NULL))
    23       ),
    24       FAMILY "primary" (a, b)
    25  )
    26  -- Warning: Partitioned table with no zone configurations.
    27  
    28  # Verify that secondary indexes with a partition for NULLs can actually store
    29  # NULLs.
    30  statement ok
    31  INSERT INTO ok1 (a, b) VALUES (1, NULL), (2, NULL)
    32  
    33  statement ok
    34  CREATE TABLE ok2 (
    35      a INT PRIMARY KEY, b INT,
    36      UNIQUE (b) PARTITION BY LIST (b) (
    37          PARTITION p1 VALUES IN (1)
    38      ),
    39      FAMILY "primary" (a, b)
    40  )
    41  
    42  query TT
    43  SHOW CREATE TABLE ok2
    44  ----
    45  ok2  CREATE TABLE ok2 (
    46       a INT8 NOT NULL,
    47       b INT8 NULL,
    48       CONSTRAINT "primary" PRIMARY KEY (a ASC),
    49       UNIQUE INDEX ok2_b_key (b ASC) PARTITION BY LIST (b) (
    50         PARTITION p1 VALUES IN ((1))
    51       ),
    52       FAMILY "primary" (a, b)
    53  )
    54  -- Warning: Partitioned table with no zone configurations.
    55  
    56  statement ok
    57  CREATE TABLE ok3 (
    58      a INT PRIMARY KEY, b INT,
    59      UNIQUE INDEX (b) PARTITION BY LIST (b) (
    60          PARTITION p1 VALUES IN (1)
    61      ),
    62      FAMILY "primary" (a, b)
    63  )
    64  
    65  statement ok
    66  INSERT INTO ok3 VALUES (1,1), (2,2), (3,3)
    67  
    68  statement ok
    69  CREATE UNIQUE INDEX ok3_b_key2 ON ok3 (b ASC) PARTITION BY LIST (b) (
    70         PARTITION p2 VALUES IN ((1))
    71  )
    72  
    73  query TT
    74  SHOW CREATE TABLE ok3
    75  ----
    76  ok3  CREATE TABLE ok3 (
    77       a INT8 NOT NULL,
    78       b INT8 NULL,
    79       CONSTRAINT "primary" PRIMARY KEY (a ASC),
    80       UNIQUE INDEX ok3_b_key (b ASC) PARTITION BY LIST (b) (
    81         PARTITION p1 VALUES IN ((1))
    82       ),
    83       UNIQUE INDEX ok3_b_key2 (b ASC) PARTITION BY LIST (b) (
    84              PARTITION p2 VALUES IN ((1))
    85       ),
    86       FAMILY "primary" (a, b)
    87  )
    88  -- Warning: Partitioned table with no zone configurations.
    89  
    90  statement ok
    91  CREATE TABLE indexes (a INT PRIMARY KEY, b INT, FAMILY "primary" (a, b))
    92  
    93  statement ok
    94  INSERT INTO indexes VALUES (1,1), (2,2), (3,3)
    95  
    96  statement ok
    97  CREATE INDEX i1 ON indexes (b) PARTITION BY LIST (b) (
    98      PARTITION p1 VALUES IN (1)
    99  )
   100  
   101  statement ok
   102  CREATE UNIQUE INDEX i2 ON indexes (b) PARTITION BY LIST (b) (
   103      PARTITION p2 VALUES IN (1)
   104  )
   105  
   106  statement ok
   107  CREATE INDEX IF NOT EXISTS i3 ON indexes (b) PARTITION BY LIST (b) (
   108      PARTITION p3 VALUES IN (1)
   109  )
   110  
   111  statement ok
   112  CREATE UNIQUE INDEX IF NOT EXISTS i4 ON indexes (b) PARTITION BY LIST (b) (
   113      PARTITION p4 VALUES IN (1)
   114  )
   115  
   116  statement error PARTITION p1: name must be unique \(used twice in index "i5"\)
   117  CREATE INDEX i5 ON indexes (b) PARTITION BY LIST (b) (
   118      PARTITION p1 VALUES IN (1),
   119      PARTITION p1 VALUES IN (2)
   120  )
   121  
   122  # Partition names can be reused across indexes.
   123  statement ok
   124  CREATE INDEX i5 ON indexes (b) PARTITION BY LIST (b) (
   125      PARTITION p1 VALUES IN (1)
   126  )
   127  
   128  query TT
   129  SHOW CREATE TABLE indexes
   130  ----
   131  indexes  CREATE TABLE indexes (
   132           a INT8 NOT NULL,
   133           b INT8 NULL,
   134           CONSTRAINT "primary" PRIMARY KEY (a ASC),
   135           INDEX i1 (b ASC) PARTITION BY LIST (b) (
   136             PARTITION p1 VALUES IN ((1))
   137           ),
   138           UNIQUE INDEX i2 (b ASC) PARTITION BY LIST (b) (
   139             PARTITION p2 VALUES IN ((1))
   140           ),
   141           INDEX i3 (b ASC) PARTITION BY LIST (b) (
   142             PARTITION p3 VALUES IN ((1))
   143           ),
   144           UNIQUE INDEX i4 (b ASC) PARTITION BY LIST (b) (
   145             PARTITION p4 VALUES IN ((1))
   146           ),
   147           INDEX i5 (b ASC) PARTITION BY LIST (b) (
   148             PARTITION p1 VALUES IN ((1))
   149           ),
   150           FAMILY "primary" (a, b)
   151  )
   152  -- Warning: Partitioned table with no zone configurations.