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

     1  statement ok
     2  CREATE TABLE t (a INT PRIMARY KEY, b INT, c INT, d INT, INDEX (b) STORING (d))
     3  
     4  # Empty data
     5  query TT
     6  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t
     7  ----
     8  primary  NULL
     9  t_b_idx  NULL
    10  
    11  statement ok
    12  INSERT INTO t VALUES (1, 2, 3, 4), (5, 6, 7, 8), (9, 10, 11, 12)
    13  
    14  # Add some initial data
    15  query TT
    16  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t
    17  ----
    18  primary  -7903300865687235210
    19  t_b_idx  -5073888452016928166
    20  
    21  statement ok
    22  UPDATE t SET b = 9
    23  
    24  # b is encoded in both indexes, so both should change
    25  query TT
    26  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t
    27  ----
    28  primary  3722816579880544080
    29  t_b_idx  -8494698744159250398
    30  
    31  statement ok
    32  UPDATE t SET c = 10
    33  
    34  # c is encoded only in primary, so t_b_idx shouldn't change
    35  query TT
    36  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t
    37  ----
    38  primary  4547357529681250049
    39  t_b_idx  -8494698744159250398
    40  
    41  statement ok
    42  UPDATE t SET d = 10
    43  
    44  # d is encoded in both indexes, so both should change
    45  query TT
    46  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t
    47  ----
    48  primary  492416650140211287
    49  t_b_idx  -8497500299788131628
    50  
    51  statement ok
    52  ALTER TABLE t ADD COLUMN e string;
    53  
    54  # Table changed, but the new column is all NULLs so neither fingerprint should
    55  # change
    56  query TT
    57  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t
    58  ----
    59  primary  492416650140211287
    60  t_b_idx  -8497500299788131628
    61  
    62  statement ok
    63  UPDATE t SET e = 'foo' WHERE a = 1;
    64  
    65  # Column e is not in index t_b_idx so its fingerprint shouldn't change
    66  query TT
    67  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t
    68  ----
    69  primary  1205834892498753533
    70  t_b_idx  -8497500299788131628
    71  
    72  statement ok
    73  DROP INDEX t@t_b_idx
    74  
    75  # Double check that dropping an index doesn't affect the fingerprint of primary
    76  query TT
    77  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t
    78  ----
    79  primary  1205834892498753533
    80  
    81  # Make sure fully qualified table names work
    82  query TT
    83  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE test.t
    84  ----
    85  primary  1205834892498753533
    86  
    87  statement ok
    88  CREATE TABLE "foo""bar" ("a""b" INT PRIMARY KEY, b INT, INDEX "id""x" (b))
    89  
    90  statement ok
    91  INSERT INTO "foo""bar" VALUES (1, 2), (3, 4), (5, 6)
    92  
    93  # Make sure we handle table, index, and column name escaping correctly in the
    94  # internally generated query.
    95  query TT
    96  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE "foo""bar"
    97  ----
    98  primary  590693963425091008
    99  id"x     590692863913460538
   100  
   101  # BYTES is special cased so make sure tables with both BYTES and non-BYTES
   102  # columns work
   103  statement ok
   104  CREATE TABLE blocks (block_id INT PRIMARY KEY, raw_bytes BYTES NOT NULL)
   105  
   106  statement ok
   107  INSERT INTO blocks VALUES (1, b'\x01')
   108  
   109  query TT
   110  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE blocks
   111  ----
   112  primary  590700560494856555
   113  
   114  # Verify that we can show fingerprints from a read-only transaction (#39204).
   115  statement ok
   116  BEGIN TRANSACTION AS OF SYSTEM TIME '-1us'
   117  
   118  query TT
   119  SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t
   120  ----
   121  primary  1205834892498753533
   122  
   123  statement ok
   124  COMMIT