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

     1  statement ok
     2  CREATE TABLE users (
     3    id    INT PRIMARY KEY,
     4    name  VARCHAR NOT NULL,
     5    title VARCHAR,
     6    INDEX foo (name),
     7    UNIQUE INDEX bar (id, name)
     8  )
     9  
    10  statement ok
    11  CREATE TABLE users_dupe (
    12    id    INT PRIMARY KEY,
    13    name  VARCHAR NOT NULL,
    14    title VARCHAR,
    15    INDEX foo (name),
    16    UNIQUE INDEX bar (id, name)
    17  )
    18  
    19  statement ok
    20  INSERT INTO users VALUES (1, 'tom', 'cat'),(2, 'jerry', 'rat')
    21  
    22  statement ok
    23  INSERT INTO users_dupe VALUES (1, 'tom', 'cat'),(2, 'jerry', 'rat')
    24  
    25  query TTBITTBB colnames
    26  SHOW INDEXES FROM users
    27  ----
    28  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
    29  users       primary     false       1             id           ASC        false    false
    30  users       foo         true        1             name         ASC        false    false
    31  users       foo         true        2             id           ASC        false    true
    32  users       bar         false       1             id           ASC        false    false
    33  users       bar         false       2             name         ASC        false    false
    34  
    35  query TTBITTBB colnames
    36  SHOW INDEXES FROM users_dupe
    37  ----
    38  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
    39  users_dupe  primary     false       1             id           ASC        false    false
    40  users_dupe  foo         true        1             name         ASC        false    false
    41  users_dupe  foo         true        2             id           ASC        false    true
    42  users_dupe  bar         false       1             id           ASC        false    false
    43  users_dupe  bar         false       2             name         ASC        false    false
    44  
    45  statement error index name "bar" already exists
    46  ALTER INDEX users@foo RENAME TO bar
    47  
    48  statement error pgcode 42601 empty index name
    49  ALTER INDEX users@foo RENAME TO ""
    50  
    51  statement error index "ffo" does not exist
    52  ALTER INDEX users@ffo RENAME TO ufo
    53  
    54  statement error index "ffo" does not exist
    55  ALTER INDEX ffo RENAME TO ufo
    56  
    57  statement error index name "foo" is ambiguous
    58  ALTER INDEX foo RENAME TO ufo
    59  
    60  statement error index name "foo" is ambiguous
    61  ALTER INDEX IF EXISTS foo RENAME TO ufo
    62  
    63  statement ok
    64  ALTER INDEX IF EXISTS users@ffo RENAME TO ufo
    65  
    66  # Regression test for #42399.
    67  statement ok
    68  ALTER INDEX IF EXISTS ffo RENAME TO ufo
    69  
    70  statement ok
    71  ALTER INDEX users@foo RENAME TO ufooo
    72  
    73  statement ok
    74  ALTER INDEX IF EXISTS ufooo RENAME TO ufoo
    75  
    76  statement ok
    77  ALTER INDEX ufoo RENAME TO ufo
    78  
    79  query TTBITTBB colnames
    80  SHOW INDEXES FROM users
    81  ----
    82  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
    83  users       primary     false       1             id           ASC        false    false
    84  users       ufo         true        1             name         ASC        false    false
    85  users       ufo         true        2             id           ASC        false    true
    86  users       bar         false       1             id           ASC        false    false
    87  users       bar         false       2             name         ASC        false    false
    88  
    89  user testuser
    90  
    91  statement error user testuser does not have CREATE privilege on relation users
    92  ALTER INDEX users@bar RENAME TO rar
    93  
    94  user root
    95  
    96  statement ok
    97  GRANT CREATE ON TABLE users TO testuser
    98  
    99  user testuser
   100  
   101  statement ok
   102  ALTER INDEX users@bar RENAME TO rar
   103  
   104  query TTBITTBB colnames
   105  SHOW INDEXES FROM users
   106  ----
   107  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
   108  users       primary     false       1             id           ASC        false    false
   109  users       ufo         true        1             name         ASC        false    false
   110  users       ufo         true        2             id           ASC        false    true
   111  users       rar         false       1             id           ASC        false    false
   112  users       rar         false       2             name         ASC        false    false
   113  
   114  user root
   115  
   116  query ITT rowsort
   117  SELECT * FROM users
   118  ----
   119  1 tom   cat
   120  2 jerry rat
   121  
   122  statement ok
   123  CREATE VIEW v AS SELECT name FROM users@{FORCE_INDEX=ufo}
   124  
   125  statement error cannot rename index "ufo" because view "v" depends on it
   126  ALTER INDEX users@ufo RENAME TO foo
   127  
   128  statement ok
   129  ALTER INDEX users@rar RENAME TO bar
   130  
   131  # Regression test for #24774
   132  statement ok
   133  ALTER INDEX users@"primary" RENAME TO pk
   134  
   135  query ITT rowsort
   136  SELECT * FROM users@pk
   137  ----
   138  1 tom   cat
   139  2 jerry rat
   140  
   141  query TTT
   142  EXPLAIN ALTER INDEX users@bar RENAME TO woo
   143  ----
   144  ·             distributed  false
   145  ·             vectorized   false
   146  rename index  ·            ·
   147  
   148  # Verify that EXPLAIN did not actually rename the index (#30543)
   149  query T rowsort
   150  SELECT DISTINCT index_name FROM [SHOW INDEXES FROM users]
   151  ----
   152  pk
   153  ufo
   154  bar