github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/storing (about)

     1  statement ok
     2  CREATE TABLE t (
     3    a INT PRIMARY KEY,
     4    b INT,
     5    c INT,
     6    d INT,
     7    INDEX b_idx (b) STORING (c, d),
     8    UNIQUE INDEX c_idx (c) STORING (b, d)
     9  )
    10  
    11  query TTBITTBB colnames
    12  SHOW INDEXES FROM t
    13  ----
    14  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
    15  t           primary     false       1             a            ASC        false    false
    16  t           b_idx       true        1             b            ASC        false    false
    17  t           b_idx       true        2             c            N/A        true     false
    18  t           b_idx       true        3             d            N/A        true     false
    19  t           b_idx       true        4             a            ASC        false    true
    20  t           c_idx       false       1             c            ASC        false    false
    21  t           c_idx       false       2             b            N/A        true     false
    22  t           c_idx       false       3             d            N/A        true     false
    23  t           c_idx       false       4             a            ASC        false    true
    24  
    25  statement ok
    26  INSERT INTO t VALUES (1, 2, 3, 4)
    27  
    28  query IIII
    29  SELECT a, b, c, d FROM t@b_idx
    30  ----
    31  1 2 3 4
    32  
    33  query IIII
    34  SELECT a, b, c, d FROM t@c_idx
    35  ----
    36  1 2 3 4
    37  
    38  # Test index backfill for UNIQUE and non-UNIQUE indexes with STORING columns.
    39  
    40  statement ok
    41  CREATE INDEX d_idx ON t (d) STORING ( b)
    42  
    43  query III
    44  SELECT a, b, d FROM t@d_idx
    45  ----
    46  1 2 4
    47  
    48  statement error index "error" already contains column "d"
    49  CREATE INDEX error ON t (d) STORING (d)
    50  
    51  statement error index "error" already contains column "a"
    52  CREATE INDEX error ON t (d) STORING (a)
    53  
    54  statement error index "b_idx" already contains column "a"
    55  CREATE TABLE t30984 (
    56    a INT PRIMARY KEY,
    57    b INT,
    58    c INT,
    59    INDEX b_idx (b) STORING (c, a)
    60  )
    61  
    62  statement error index "b_idx" already contains column "a"
    63  CREATE TABLE t30984 (
    64    a INT PRIMARY KEY,
    65    b INT,
    66    c INT,
    67    UNIQUE INDEX b_idx (b) STORING (c, a)
    68  )
    69  
    70  statement error index "b_idx" already contains column "d"
    71  CREATE TABLE t30984 (
    72    a INT,
    73    b INT,
    74    c INT,
    75    d INT,
    76    PRIMARY KEY (a, d),
    77    UNIQUE INDEX b_idx (b) STORING (c, d)
    78  )
    79  
    80  statement ok
    81  CREATE UNIQUE INDEX a_idx ON t (a) STORING (b)
    82  
    83  # Regression test for #14601.
    84  
    85  statement ok
    86  CREATE TABLE t14601 (a STRING, b BOOL)
    87  
    88  statement ok
    89  CREATE INDEX i14601 ON t14601 (a) STORING (b)
    90  
    91  statement ok
    92  INSERT INTO t14601 VALUES
    93    ('a', FALSE),
    94    ('b', FALSE),
    95    ('c', FALSE)
    96  
    97  statement ok
    98  DELETE FROM t14601 WHERE a > 'a' AND a < 'c'
    99  
   100  query T
   101  SELECT a FROM t14601 ORDER BY a
   102  ----
   103  a
   104  c
   105  
   106  statement ok
   107  DROP INDEX i14601
   108  
   109  query T
   110  SELECT a FROM t14601 ORDER BY a
   111  ----
   112  a
   113  c
   114  
   115  # Updates were broken too.
   116  
   117  statement ok
   118  CREATE TABLE t14601a (
   119    a STRING,
   120    b BOOL,
   121    c INT,
   122    FAMILY f1 (a),
   123    FAMILY f2 (b),
   124    FAMILY f3 (c)
   125  )
   126  
   127  statement ok
   128  CREATE INDEX i14601a ON t14601a (a) STORING (b, c)
   129  
   130  statement ok
   131  INSERT INTO t14601a VALUES
   132    ('a', FALSE, 1),
   133    ('b', TRUE, 2),
   134    ('c', FALSE, 3)
   135  
   136  statement ok
   137  UPDATE t14601a SET b = NOT b WHERE a > 'a' AND a < 'c'
   138  
   139  query TB
   140  SELECT a, b FROM t14601a ORDER BY a
   141  ----
   142  a  false
   143  b  false
   144  c  false
   145  
   146  statement ok
   147  DROP INDEX i14601a
   148  
   149  query TB
   150  SELECT a, b FROM t14601a ORDER BY a
   151  ----
   152  a  false
   153  b  false
   154  c  false
   155  
   156  statement ok
   157  DELETE FROM t14601a
   158  
   159  statement ok
   160  CREATE UNIQUE INDEX i14601a ON t14601a (a) STORING (b)
   161  
   162  statement ok
   163  INSERT INTO t14601a VALUES
   164    ('a', FALSE),
   165    ('b', TRUE),
   166    ('c', FALSE)
   167  
   168  statement ok
   169  UPDATE t14601a SET b = NOT b WHERE a > 'a' AND a < 'c'
   170  
   171  query TB
   172  SELECT a, b FROM t14601a ORDER BY a
   173  ----
   174  a  false
   175  b  false
   176  c  false
   177  
   178  statement ok
   179  DROP INDEX i14601a CASCADE
   180  
   181  query TB
   182  SELECT a, b FROM t14601a ORDER BY a
   183  ----
   184  a  false
   185  b  false
   186  c  false
   187  
   188  # Test that unspecified storing values are treated like NULL values.
   189  statement ok
   190  INSERT INTO t (a) VALUES (2)
   191  
   192  statement ok
   193  INSERT INTO t VALUES (3)
   194  
   195  # Regression test for #30984: indirectly ensure that index descriptors don't
   196  # get recreated every time any other schema change occurs.
   197  
   198  statement ok
   199  CREATE TABLE a(a INT, b INT, c INT, PRIMARY KEY(a, b))
   200  
   201  statement ok
   202  CREATE UNIQUE INDEX foo ON a(a) STORING(c)
   203  
   204  statement ok
   205  INSERT INTO a VALUES(1,2,3)
   206  
   207  statement ok
   208  CREATE UNIQUE INDEX ON a(a) STORING(c)
   209  
   210  query III
   211  SELECT * FROM a@foo
   212  ----
   213  1 2 3