github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/schema_change_in_txn (about)

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE TABLE t (
     5    a INT PRIMARY KEY,
     6    b DECIMAL(10,1) NOT NULL DEFAULT(1000.15),
     7    c TEXT COLLATE en_US DEFAULT('empty' COLLATE en_US),
     8    d DECIMAL(10,2) NOT NULL,
     9    e TIME,
    10    f DECIMAL AS (a + b + d) STORED,
    11    --UNIQUE INDEX t_secondary (c, d), -- Fails due to #46276
    12    FAMILY (a, b, c),
    13    FAMILY (d, e, f)
    14  )
    15  
    16  statement ok
    17  INSERT INTO t VALUES (100, 500.5, 'stuff' COLLATE en_US, 600.6, '12:12:12')
    18  
    19  # Drop all columns except "a" and perform interesting operations.
    20  statement ok
    21  BEGIN;
    22  --DROP INDEX t_secondary CASCADE;
    23  ALTER TABLE t DROP COLUMN f, DROP COLUMN b, DROP COLUMN c, DROP COLUMN d, DROP COLUMN e;
    24  ALTER TABLE t ADD COLUMN g INT NOT NULL DEFAULT(15)
    25  
    26  # Expect default values for b, c, zero value for d, and NULL value for e.
    27  query T kvtrace(prefix=/Table/53/)
    28  INSERT INTO t SELECT a + 1 FROM t
    29  ----
    30  Scan /Table/53/{1-2}
    31  CPut /Table/53/1/101/0 -> /TUPLE/2:2:Decimal/1000.2/1:3:Bytes/empty
    32  CPut /Table/53/1/101/1/1 -> /TUPLE/4:4:Decimal/0.00/2:6:Decimal/1101.2
    33  
    34  # Expect default values for b, c, zero value for d, and NULL value for e.
    35  query T kvtrace(prefix=/Table/53/)
    36  UPSERT INTO t SELECT a + 1 FROM t
    37  ----
    38  Scan /Table/53/{1-2}
    39  Scan /Table/53/{1-2}
    40  Put /Table/53/1/101/0 -> /TUPLE/2:2:Decimal/1000.2/1:3:Bytes/empty
    41  Put /Table/53/1/101/1/1 -> /TUPLE/4:4:Decimal/0.00/2:6:Decimal/1101.2
    42  CPut /Table/53/1/102/0 -> /TUPLE/2:2:Decimal/1000.2/1:3:Bytes/empty
    43  CPut /Table/53/1/102/1/1 -> /TUPLE/4:4:Decimal/0.00/2:6:Decimal/1102.2
    44  
    45  # Expect default values for b, c, zero value for d, and NULL value for e.
    46  query T kvtrace(prefix=/Table/53/)
    47  UPDATE t SET a = a + 100
    48  ----
    49  Scan /Table/53/{1-2}
    50  Del /Table/53/1/100/0
    51  Del /Table/53/1/100/1/1
    52  CPut /Table/53/1/200/0 -> /TUPLE/2:2:Decimal/1000.2/1:3:Bytes/empty
    53  CPut /Table/53/1/200/1/1 -> /TUPLE/4:4:Decimal/0.00/2:6:Decimal/1200.2
    54  Del /Table/53/1/101/0
    55  Del /Table/53/1/101/1/1
    56  CPut /Table/53/1/201/0 -> /TUPLE/2:2:Decimal/1000.2/1:3:Bytes/empty
    57  CPut /Table/53/1/201/1/1 -> /TUPLE/4:4:Decimal/0.00/2:6:Decimal/1201.2
    58  Del /Table/53/1/102/0
    59  Del /Table/53/1/102/1/1
    60  CPut /Table/53/1/202/0 -> /TUPLE/2:2:Decimal/1000.2/1:3:Bytes/empty
    61  CPut /Table/53/1/202/1/1 -> /TUPLE/4:4:Decimal/0.00/2:6:Decimal/1202.2
    62  
    63  statement ok
    64  DELETE FROM t WHERE a=201
    65  
    66  statement ok
    67  COMMIT
    68  
    69  query II
    70  SELECT * FROM t
    71  ----
    72  200  15
    73  202  15