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

     1  # Ensure updates that remove a k/v pair succeed.
     2  statement ok
     3  CREATE TABLE t (
     4    x INT PRIMARY KEY,
     5    y INT,
     6    z INT,
     7    w INT,
     8    INDEX i (y) STORING (z, w),
     9    FAMILY (x), FAMILY (y), FAMILY (z), FAMILY (w)
    10  );
    11  INSERT INTO t VALUES (1, 2, 3, 4);
    12  UPDATE t SET z = NULL, w = NULL WHERE y = 2
    13  
    14  query III
    15  SELECT y, z, w FROM t@i WHERE y = 2
    16  ----
    17  2 NULL NULL
    18  
    19  # Test some cases around insert on conflict.
    20  statement ok
    21  DROP TABLE IF EXISTS t;
    22  CREATE TABLE t (
    23    x INT PRIMARY KEY,
    24    y INT,
    25    z STRING,
    26    v INT,
    27    UNIQUE INDEX i (y) STORING (z, v)
    28  );
    29  INSERT INTO t VALUES (1, 2, '3', 4), (5, 6, '7', 8);
    30  INSERT INTO t VALUES (10, 2, '10', 10) ON CONFLICT (y) DO NOTHING
    31  
    32  query ITI rowsort
    33  SELECT y, z, v FROM t@i
    34  ----
    35  2 3 4
    36  6 7 8
    37  
    38  statement ok
    39  INSERT INTO t VALUES (10, 2, '10', 10) ON CONFLICT (y) DO UPDATE set x = 20, z = '20', v = 20 WHERE t.y = 2
    40  
    41  query ITI rowsort
    42  SELECT y, z, v FROM t@i
    43  ----
    44  2 20 20
    45  6 7 8
    46  
    47  # Test some cases around upsert.
    48  statement ok
    49  DROP TABLE IF EXISTS t;
    50  CREATE TABLE t (
    51    x INT PRIMARY KEY,
    52    y STRING,
    53    z DECIMAL,
    54    w INT,
    55    FAMILY (y), FAMILY (z), FAMILY (x, w),
    56    INDEX i (y) STORING (z, w)
    57  );
    58  INSERT INTO t VALUES (1, '2', 3.0, 4), (5, '6', 7.00, 8);
    59  UPSERT INTO t VALUES (9, '10', 11.000, 12), (1, '3', 5.0, 16)
    60  
    61  query TTI rowsort
    62  SELECT y, z, w FROM t@i
    63  ----
    64  3 5.0 16
    65  6 7.00 8
    66  10 11.000 12
    67  
    68  # Test some cases around schema changes.
    69  statement ok
    70  DROP TABLE IF EXISTS t;
    71  CREATE TABLE t (
    72    x INT PRIMARY KEY,
    73    y DECIMAL,
    74    z INT,
    75    w INT,
    76    v INT
    77  );
    78  INSERT INTO t VALUES (1, 2, 3, 4, 5), (6, 7, 8, 9, 10), (11, 12, 13, 14, 15);
    79  CREATE INDEX i ON t (y) STORING (z, w, v)
    80  
    81  query TIII rowsort
    82  SELECT y, z, w, v FROM t@i
    83  ----
    84  2 3 4 5
    85  7 8 9 10
    86  12 13 14 15
    87  
    88  statement ok
    89  DROP INDEX t@i
    90  
    91  query ITIII rowsort
    92  SELECT * FROM t
    93  ----
    94  1 2 3 4 5
    95  6 7 8 9 10
    96  11 12 13 14 15
    97  
    98  statement ok
    99  ALTER TABLE t ADD COLUMN u INT DEFAULT (20) CREATE FAMILY new_fam;
   100  CREATE INDEX i ON t (y) STORING (z, w, v, u)
   101  
   102  query TIIII rowsort
   103  SELECT y, z, w, v, u FROM t@i
   104  ----
   105  2 3 4 5 20
   106  7 8 9 10 20
   107  12 13 14 15 20
   108  
   109  # Regression for #42992.
   110  statement ok
   111  CREATE TABLE t42992 (x TIMESTAMP PRIMARY KEY, y INT, z INT, UNIQUE INDEX i (y) STORING (z), FAMILY (x), FAMILY (y), FAMILY (z))
   112  
   113  statement ok
   114  INSERT INTO t42992 VALUES (now(), NULL, 2)
   115  
   116  query II
   117  SELECT y, z FROM t42992@i
   118  ----
   119  NULL 2