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

     1  # This table has ID 53.
     2  exec-ddl
     3  CREATE TABLE ab (a INT PRIMARY KEY, b INT, INDEX idx(b))
     4  ----
     5  
     6  exec-ddl
     7  CREATE TABLE cd (c INT PRIMARY KEY, d INT)
     8  ----
     9  
    10  exec-ddl
    11  CREATE SEQUENCE s
    12  ----
    13  
    14  build
    15  CREATE VIEW v1 AS VALUES (1)
    16  ----
    17  create-view t.public.v1
    18   ├── VALUES (1)
    19   ├── columns: column1:1
    20   └── dependencies
    21  
    22  build
    23  CREATE VIEW v1 AS SELECT a FROM ab 
    24  ----
    25  create-view t.public.v1
    26   ├── SELECT a FROM t.public.ab
    27   ├── columns: a:1
    28   └── dependencies
    29        └── ab [columns: (0,1)]
    30  
    31  # Test dependency on specific index.
    32  build
    33  CREATE VIEW v1 AS SELECT a FROM ab@idx
    34  ----
    35  create-view t.public.v1
    36   ├── SELECT a FROM t.public.ab@idx
    37   ├── columns: a:1
    38   └── dependencies
    39        └── ab@idx [columns: (0,1)]
    40  
    41  build
    42  CREATE VIEW v1 AS SELECT a FROM ab@primary
    43  ----
    44  create-view t.public.v1
    45   ├── SELECT a FROM t.public.ab@primary
    46   ├── columns: a:1
    47   └── dependencies
    48        └── ab@primary [columns: (0,1)]
    49  
    50  # Test dependency on view.
    51  exec-ddl
    52  CREATE VIEW av AS SELECT a FROM ab
    53  ----
    54  
    55  build
    56  CREATE VIEW v1 AS SELECT a FROM av 
    57  ----
    58  create-view t.public.v1
    59   ├── SELECT a FROM t.public.av
    60   ├── columns: a:1
    61   └── dependencies
    62        └── av [columns: (0)]
    63  
    64  build
    65  CREATE VIEW v1 AS SELECT av.a, ab.a FROM av, ab
    66  ----
    67  create-view t.public.v1
    68   ├── SELECT av.a, ab.a FROM t.public.av, t.public.ab
    69   ├── columns: a:1 a:3
    70   └── dependencies
    71        ├── av [columns: (0)]
    72        └── ab [columns: (0,1)]
    73  
    74  # Test that we don't report virtual table dependencies.
    75  build
    76  CREATE VIEW v1 AS SELECT a, table_schema FROM ab, information_schema.columns
    77  ----
    78  create-view t.public.v1
    79   ├── SELECT a, table_schema FROM t.public.ab, "".information_schema.columns
    80   ├── columns: a:1 table_schema:5
    81   └── dependencies
    82        └── ab [columns: (0,1)]
    83  
    84  # Test cases with specified column names.
    85  build
    86  CREATE VIEW v2 (x) AS SELECT ab.a FROM ab, ab AS ab2, cd
    87  ----
    88  create-view t.public.v2
    89   ├── SELECT ab.a FROM t.public.ab, t.public.ab AS ab2, t.public.cd
    90   ├── columns: x:1
    91   └── dependencies
    92        ├── ab [columns: (0,1)]
    93        ├── ab [columns: (0,1)]
    94        └── cd [columns: (0,1)]
    95  
    96  build
    97  CREATE VIEW v3 (x, y) AS SELECT a FROM ab
    98  ----
    99  error (42601): CREATE VIEW specifies 2 column names, but data source has 1 column
   100  
   101  build
   102  CREATE VIEW v3 (x) AS SELECT a, b FROM ab
   103  ----
   104  error (42601): CREATE VIEW specifies 1 column name, but data source has 2 columns
   105  
   106  # Verify that we disallow * in view definitions (#10028).
   107  build
   108  CREATE VIEW v4 AS SELECT * FROM ab
   109  ----
   110  error (0A000): unimplemented: views do not currently support * expressions
   111  
   112  build
   113  CREATE VIEW v5 AS SELECT a FROM [53 AS t]
   114  ----
   115  create-view t.public.v5
   116   ├── SELECT a FROM [53 AS t]
   117   ├── columns: a:1
   118   └── dependencies
   119        └── ab [columns: (0,1)]
   120  
   121  # Verify that we only depend on the specified column.
   122  build
   123  CREATE VIEW v6 AS SELECT a FROM [53(1) AS t]
   124  ----
   125  create-view t.public.v6
   126   ├── SELECT a FROM [53(1) AS t]
   127   ├── columns: a:1
   128   └── dependencies
   129        └── ab [columns: (0)]
   130  
   131  # Verify dependency on sequence.
   132  build
   133  CREATE VIEW v7 AS SELECT last_value FROM s
   134  ----
   135  create-view t.public.v7
   136   ├── SELECT last_value FROM t.public.s
   137   ├── columns: last_value:1
   138   └── dependencies
   139        └── s
   140  
   141  # Verify CTEs (and that we don't depend on tables with the same name).
   142  build
   143  CREATE VIEW v8 AS
   144  WITH cd AS (SELECT a, b FROM ab)
   145    SELECT a+b FROM cd
   146  ----
   147  create-view t.public.v8
   148   ├── WITH cd AS (SELECT a, b FROM t.public.ab) SELECT a + b FROM cd
   149   ├── columns: "?column?":5
   150   └── dependencies
   151        └── ab [columns: (0,1)]
   152  
   153  # Verify that we disallow mutation statements.
   154  build
   155  CREATE VIEW v8 AS SELECT a,b FROM [INSERT INTO ab VALUES (1,1) RETURNING a, b]
   156  ----
   157  error (42601): INSERT cannot be used inside a view definition
   158  
   159  build
   160  CREATE VIEW v9 AS SELECT a,b FROM [UPDATE ab SET a=a+1 RETURNING a, b]
   161  ----
   162  error (42601): UPDATE cannot be used inside a view definition
   163  
   164  build
   165  CREATE VIEW v9 AS SELECT a,b FROM [DELETE FROM ab WHERE a>b RETURNING a, b]
   166  ----
   167  error (42601): DELETE cannot be used inside a view definition