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

     1  statement error pgcode 42P01 relation "t" does not exist
     2  EXPERIMENTAL SCRUB TABLE t
     3  -----
     4  
     5  # TODO(mjibson): remove FAMILY definition after #41002 is fixed.
     6  statement ok
     7  CREATE TABLE t (
     8    id int PRIMARY KEY,
     9    name STRING,
    10    data INT DEFAULT 2,
    11    CONSTRAINT abc CHECK (name > 'he'),
    12    INDEX name_idx (name),
    13    FAMILY "primary" (id, name, data)
    14  )
    15  
    16  statement ok
    17  INSERT INTO t VALUES (1, 'hello'), (2, 'help'), (0, 'heeee')
    18  
    19  # Test scrub against a table with some data and with various different options.
    20  
    21  query TTTTTTTT
    22  EXPERIMENTAL SCRUB TABLE t
    23  -----
    24  
    25  query TTTTTTTT
    26  EXPERIMENTAL SCRUB TABLE t WITH OPTIONS PHYSICAL
    27  -----
    28  
    29  query TTTTTTTT
    30  EXPERIMENTAL SCRUB TABLE t WITH OPTIONS INDEX ALL
    31  ------
    32  
    33  query TTTTTTTT
    34  EXPERIMENTAL SCRUB TABLE t WITH OPTIONS PHYSICAL, INDEX (name_idx)
    35  -----
    36  
    37  statement error specified indexes to check that do not exist on table "t": not_an_index, also_not
    38  EXPERIMENTAL SCRUB TABLE t WITH OPTIONS INDEX (not_an_index, also_not, name_idx)
    39  
    40  query TTTTTTTT
    41  EXPERIMENTAL SCRUB TABLE t WITH OPTIONS CONSTRAINT ALL
    42  -----
    43  
    44  query TTTTTTTT
    45  EXPERIMENTAL SCRUB TABLE t WITH OPTIONS CONSTRAINT (abc)
    46  -----
    47  
    48  statement error pq: constraint "xyz" of relation "t" does not exist
    49  EXPERIMENTAL SCRUB TABLE t WITH OPTIONS CONSTRAINT (xyz)
    50  
    51  # test that scrub cannot be used with views
    52  
    53  statement ok
    54  CREATE VIEW v1 AS select id, name from t
    55  
    56  statement error "v1" is not a table
    57  EXPERIMENTAL SCRUB TABLE v1
    58  
    59  # scrub database will skip views so it does not raise an error
    60  
    61  query TTTTTTTT
    62  EXPERIMENTAL SCRUB DATABASE test
    63  -----
    64  
    65  # make sure there are no errors when values in the index are NULL
    66  
    67  statement ok
    68  CREATE TABLE test.xyz (
    69    x INT PRIMARY KEY,
    70    y INT,
    71    z INT,
    72    INDEX foo (z, y)
    73  )
    74  
    75  statement ok
    76  INSERT INTO test.xyz (x, y) VALUES (8, 2), (9, 2);
    77  
    78  query TTTTTTBT
    79  EXPERIMENTAL SCRUB TABLE xyz WITH OPTIONS INDEX ALL
    80  -----
    81  
    82  # Test that scrub checks work when a table has an implicit rowid primary key.
    83  
    84  statement ok
    85  CREATE TABLE test.xz (
    86    y INT,
    87    z INT,
    88    INDEX foo (z, y)
    89  )
    90  
    91  statement ok
    92  EXPERIMENTAL SCRUB TABLE xz
    93  
    94  # make sure there are no false positives when there are NULL values in foreign key values
    95  
    96  statement ok
    97  CREATE TABLE test.fk_child (id INT PRIMARY KEY, v INT, UNIQUE INDEX (id, v))
    98  
    99  statement ok
   100  CREATE TABLE test.fk_parent (id INT PRIMARY KEY, k INT, INDEX (id, k), CONSTRAINT fkey FOREIGN KEY (id, k) REFERENCES test.fk_child (id, v))
   101  
   102  statement ok
   103  INSERT INTO test.fk_child VALUES (1,1), (2,1), (3,NULL)
   104  
   105  statement ok
   106  INSERT INTO test.fk_parent VALUES (1,1), (2,1), (3,NULL)
   107  
   108  query TTTTTTTT
   109  EXPERIMENTAL SCRUB TABLE test.fk_parent WITH OPTIONS CONSTRAINT (fkey)
   110  -----
   111  
   112  # Test AS OF SYSTEM TIME
   113  
   114  statement error pgcode 42P01 relation "xz" does not exist
   115  EXPERIMENTAL SCRUB TABLE xz AS OF SYSTEM TIME '2017' WITH OPTIONS PHYSICAL
   116  
   117  statement error pgcode 42P01 relation "xz" does not exist
   118  EXPERIMENTAL SCRUB TABLE xz AS OF SYSTEM TIME '2017' WITH OPTIONS INDEX ALL
   119  
   120  statement error pgcode 42P01 relation "xz" does not exist
   121  EXPERIMENTAL SCRUB TABLE xz AS OF SYSTEM TIME '2017' WITH OPTIONS CONSTRAINT ALL
   122  
   123  # Test scrubbing sequences.
   124  
   125  statement ok
   126  CREATE DATABASE seq_db
   127  
   128  statement ok
   129  CREATE SEQUENCE seq_db.my_seq
   130  
   131  statement ok
   132  CREATE TABLE seq_db.my_tbl (id INT PRIMARY KEY DEFAULT nextval('seq_db.my_seq'))
   133  
   134  statement ok
   135  EXPERIMENTAL SCRUB DATABASE seq_db
   136  
   137  statement error pq: "seq_db.public.my_seq" is not a table
   138  EXPERIMENTAL SCRUB TABLE seq_db.my_seq
   139  
   140  # Test for false positives when checking key order (#32874)
   141  
   142  statement ok
   143  CREATE TABLE test.order (a INT, b INT, c INT, CONSTRAINT "primary" PRIMARY KEY (a, b, c DESC))
   144  
   145  statement ok
   146  INSERT INTO test.order VALUES (0, 0, 0), (0, 0, 1), (0, 1, 0), (0, 1, 1), (1, 0, 0);
   147  
   148  query TTTTTTTT
   149  EXPERIMENTAL SCRUB TABLE test.order WITH OPTIONS PHYSICAL
   150  
   151  # Test that scrubbing timestamp works as expected.
   152  subtest regression_44992
   153  
   154  statement ok
   155  CREATE TABLE t0(c0 TIMESTAMP UNIQUE); INSERT INTO t0(c0) VALUES(TIMESTAMP '1969-1-1'); INSERT INTO t0(c0) VALUES(TIMESTAMP '1969-1-2')
   156  
   157  statement ok
   158  EXPERIMENTAL SCRUB TABLE t0
   159  
   160  statement ok
   161  DROP TABLE t0