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

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE TABLE a (k STRING PRIMARY KEY, v STRING)
     5  
     6  statement ok
     7  INSERT INTO a VALUES ('a', '1'), ('b', '2'), ('c', '3')
     8  
     9  statement ok
    10  CREATE VIEW b AS SELECT k,v from a
    11  
    12  statement ok
    13  CREATE VIEW c AS SELECT k,v from b
    14  
    15  query TTT
    16  SHOW TABLES FROM test
    17  ----
    18  public  a  table
    19  public  b  view
    20  public  c  view
    21  
    22  statement error cannot drop relation "a" because view "b" depends on it
    23  DROP TABLE a
    24  
    25  statement error pgcode 42809 "b" is not a table
    26  DROP TABLE b
    27  
    28  statement error cannot drop relation "b" because view "c" depends on it
    29  DROP VIEW b
    30  
    31  statement ok
    32  CREATE VIEW d AS SELECT k,v FROM a
    33  
    34  statement ok
    35  CREATE VIEW diamond AS SELECT count(*) FROM b AS b JOIN d AS d ON b.k = d.k
    36  
    37  statement error cannot drop relation "d" because view "diamond" depends on it
    38  DROP VIEW d
    39  
    40  statement ok
    41  GRANT ALL ON d TO testuser
    42  
    43  query TTT
    44  SHOW TABLES FROM test
    45  ----
    46  public  a        table
    47  public  b        view
    48  public  c        view
    49  public  d        view
    50  public  diamond  view
    51  
    52  user testuser
    53  
    54  statement error user testuser does not have DROP privilege on relation diamond
    55  DROP VIEW diamond
    56  
    57  statement error cannot drop relation "d" because view "diamond" depends on it
    58  DROP VIEW d
    59  
    60  user root
    61  
    62  statement ok
    63  CREATE VIEW testuser1 AS SELECT k,v FROM a
    64  
    65  statement ok
    66  CREATE VIEW testuser2 AS SELECT k,v FROM testuser1
    67  
    68  statement ok
    69  CREATE VIEW testuser3 AS SELECT k,v FROM testuser2
    70  
    71  statement ok
    72  GRANT ALL ON testuser1 to testuser
    73  
    74  statement ok
    75  GRANT ALL ON testuser2 to testuser
    76  
    77  statement ok
    78  GRANT ALL ON testuser3 to testuser
    79  
    80  query TTT
    81  SHOW TABLES FROM test
    82  ----
    83  public  a          table
    84  public  b          view
    85  public  c          view
    86  public  d          view
    87  public  diamond    view
    88  public  testuser1  view
    89  public  testuser2  view
    90  public  testuser3  view
    91  
    92  user testuser
    93  
    94  statement ok
    95  DROP VIEW testuser3
    96  
    97  query TTT
    98  SHOW TABLES FROM test
    99  ----
   100  
   101  statement error cannot drop relation "testuser1" because view "testuser2" depends on it
   102  DROP VIEW testuser1
   103  
   104  statement error cannot drop relation "testuser1" because view "testuser2" depends on it
   105  DROP VIEW testuser1 RESTRICT
   106  
   107  statement ok
   108  DROP VIEW testuser1 CASCADE
   109  
   110  query TTT
   111  SHOW TABLES FROM test
   112  ----
   113  
   114  statement error pgcode 42P01 relation "testuser2" does not exist
   115  DROP VIEW testuser2
   116  
   117  user root
   118  
   119  statement ok
   120  GRANT ALL ON a to testuser
   121  
   122  statement ok
   123  GRANT ALL ON b to testuser
   124  
   125  statement ok
   126  GRANT ALL ON c to testuser
   127  
   128  statement ok
   129  GRANT ALL ON d to testuser
   130  
   131  user testuser
   132  
   133  statement error user testuser does not have DROP privilege on relation diamond
   134  DROP TABLE a CASCADE
   135  
   136  user root
   137  
   138  statement ok
   139  DROP TABLE a CASCADE
   140  
   141  query TTT
   142  SHOW TABLES FROM test
   143  ----
   144  
   145  statement ok
   146  CREATE VIEW x AS VALUES (1, 2), (3, 4)
   147  
   148  statement ok
   149  CREATE VIEW y AS SELECT column1, column2 FROM x
   150  
   151  statement error cannot drop relation "x" because view "y" depends on it
   152  DROP VIEW x
   153  
   154  statement ok
   155  DROP VIEW x, y
   156  
   157  statement ok
   158  CREATE VIEW x AS VALUES (1, 2), (3, 4)
   159  
   160  statement ok
   161  CREATE VIEW y AS SELECT column1, column2 FROM x
   162  
   163  statement error cannot drop relation "x" because view "y" depends on it
   164  DROP VIEW x
   165  
   166  statement ok
   167  DROP VIEW y, x
   168  
   169  # Ensure that dropping a database works even when views get referred to more=
   170  # than once. See #15953 for more details.
   171  statement ok
   172  CREATE DATABASE a
   173  
   174  statement ok
   175  SET DATABASE=a
   176  
   177  statement ok
   178  CREATE TABLE a (a int);
   179  
   180  statement ok
   181  CREATE TABLE b (b int);
   182  
   183  statement ok
   184  CREATE VIEW v AS SELECT a.a, b.b FROM a CROSS JOIN b
   185  
   186  statement ok
   187  CREATE VIEW u AS SELECT a FROM a UNION SELECT a FROM a
   188  
   189  statement ok
   190  DROP DATABASE a CASCADE