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

     1  exec-ddl
     2  CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON)
     3  ----
     4  
     5  exec-ddl
     6  CREATE VIEW av AS SELECT k, i, s FROM a
     7  ----
     8  
     9  build
    10  SELECT * FROM av
    11  ----
    12  project
    13   ├── columns: k:1!null i:2 s:4
    14   └── scan a
    15        └── columns: k:1!null i:2 f:3 s:4 j:5
    16  
    17  build
    18  SELECT av.i, s, t.public.av.s AS s2 FROM t.av
    19  ----
    20  project
    21   ├── columns: i:2 s:4 s2:4
    22   └── project
    23        ├── columns: k:1!null i:2 s:4
    24        └── scan a
    25             └── columns: k:1!null i:2 f:3 s:4 j:5
    26  
    27  # Self view join (multiple references to view).
    28  build
    29  SELECT av.k, av2.s FROM av, av AS av2 WHERE av.k=av2.k
    30  ----
    31  project
    32   ├── columns: k:1!null s:9
    33   └── select
    34        ├── columns: k:1!null i:2 s:4 k:6!null i:7 s:9
    35        ├── inner-join (cross)
    36        │    ├── columns: k:1!null i:2 s:4 k:6!null i:7 s:9
    37        │    ├── project
    38        │    │    ├── columns: k:1!null i:2 s:4
    39        │    │    └── scan a
    40        │    │         └── columns: k:1!null i:2 f:3 s:4 j:5
    41        │    ├── project
    42        │    │    ├── columns: k:6!null i:7 s:9
    43        │    │    └── scan a
    44        │    │         └── columns: k:6!null i:7 f:8 s:9 j:10
    45        │    └── filters (true)
    46        └── filters
    47             └── k:1 = k:6
    48  
    49  # View with aliased column names, filter, and ORDER BY.
    50  exec-ddl
    51  CREATE VIEW av2 (x, y) AS SELECT k, f FROM a WHERE i=10 ORDER BY s
    52  ----
    53  
    54  # Result is not ordered.
    55  build
    56  SELECT * FROM av2
    57  ----
    58  project
    59   ├── columns: x:1!null y:3
    60   └── project
    61        ├── columns: k:1!null f:3 s:4
    62        └── select
    63             ├── columns: k:1!null i:2!null f:3 s:4 j:5
    64             ├── scan a
    65             │    └── columns: k:1!null i:2 f:3 s:4 j:5
    66             └── filters
    67                  └── i:2 = 10
    68  
    69  # Sort used by group by because of presence of ARRAY_AGG.
    70  build
    71  SELECT array_agg(y) FROM av2
    72  ----
    73  scalar-group-by
    74   ├── columns: array_agg:6
    75   ├── internal-ordering: +4
    76   ├── sort
    77   │    ├── columns: f:3 s:4
    78   │    ├── ordering: +4
    79   │    └── project
    80   │         ├── columns: f:3 s:4
    81   │         └── project
    82   │              ├── columns: k:1!null f:3 s:4
    83   │              └── select
    84   │                   ├── columns: k:1!null i:2!null f:3 s:4 j:5
    85   │                   ├── scan a
    86   │                   │    └── columns: k:1!null i:2 f:3 s:4 j:5
    87   │                   └── filters
    88   │                        └── i:2 = 10
    89   └── aggregations
    90        └── array-agg [as=array_agg:6]
    91             └── f:3
    92  
    93  # Verify that an outer table is visible from a subquery that uses
    94  # a view (#46180).
    95  exec-ddl
    96  CREATE VIEW v AS SELECT x FROM (VALUES (1), (2)) AS foo(x);
    97  ----
    98  
    99  build
   100  SELECT (SELECT x FROM v WHERE x=t.a) FROM (VALUES (3), (4)) AS t(a);
   101  ----
   102  project
   103   ├── columns: x:3
   104   ├── values
   105   │    ├── columns: column1:1!null
   106   │    ├── (3,)
   107   │    └── (4,)
   108   └── projections
   109        └── subquery [as=x:3]
   110             └── max1-row
   111                  ├── columns: column1:2!null
   112                  └── select
   113                       ├── columns: column1:2!null
   114                       ├── values
   115                       │    ├── columns: column1:2!null
   116                       │    ├── (1,)
   117                       │    └── (2,)
   118                       └── filters
   119                            └── column1:2 = column1:1