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

     1  exec-ddl
     2  CREATE TABLE a (x INT PRIMARY KEY, y FLOAT)
     3  ----
     4  
     5  exec-ddl
     6  CREATE TABLE b (x INT, y FLOAT)
     7  ----
     8  
     9  exec-ddl
    10  CREATE TABLE c (x INT, y FLOAT, z VARCHAR, CONSTRAINT fk_x_ref_a FOREIGN KEY (x) REFERENCES a (x))
    11  ----
    12  
    13  build
    14  SELECT * FROM a, b
    15  ----
    16  project
    17   ├── columns: x:1!null y:2 x:3 y:4
    18   └── inner-join (cross)
    19        ├── columns: a.x:1!null a.y:2 b.x:3 b.y:4 rowid:5!null
    20        ├── scan a
    21        │    └── columns: a.x:1!null a.y:2
    22        ├── scan b
    23        │    └── columns: b.x:3 b.y:4 rowid:5!null
    24        └── filters (true)
    25  
    26  build
    27  SELECT a.x, b.y FROM a, b WHERE a.x = b.x
    28  ----
    29  project
    30   ├── columns: x:1!null y:4
    31   └── select
    32        ├── columns: a.x:1!null a.y:2 b.x:3!null b.y:4 rowid:5!null
    33        ├── inner-join (cross)
    34        │    ├── columns: a.x:1!null a.y:2 b.x:3 b.y:4 rowid:5!null
    35        │    ├── scan a
    36        │    │    └── columns: a.x:1!null a.y:2
    37        │    ├── scan b
    38        │    │    └── columns: b.x:3 b.y:4 rowid:5!null
    39        │    └── filters (true)
    40        └── filters
    41             └── a.x:1 = b.x:3
    42  
    43  build
    44  SELECT * FROM c, b, a WHERE c.x = a.x AND b.x = a.x
    45  ----
    46  project
    47   ├── columns: x:1!null y:2 z:3 x:5!null y:6 x:8!null y:9
    48   └── select
    49        ├── columns: c.x:1!null c.y:2 z:3 c.rowid:4!null b.x:5!null b.y:6 b.rowid:7!null a.x:8!null a.y:9
    50        ├── inner-join (cross)
    51        │    ├── columns: c.x:1 c.y:2 z:3 c.rowid:4!null b.x:5 b.y:6 b.rowid:7!null a.x:8!null a.y:9
    52        │    ├── scan c
    53        │    │    └── columns: c.x:1 c.y:2 z:3 c.rowid:4!null
    54        │    ├── inner-join (cross)
    55        │    │    ├── columns: b.x:5 b.y:6 b.rowid:7!null a.x:8!null a.y:9
    56        │    │    ├── scan b
    57        │    │    │    └── columns: b.x:5 b.y:6 b.rowid:7!null
    58        │    │    ├── scan a
    59        │    │    │    └── columns: a.x:8!null a.y:9
    60        │    │    └── filters (true)
    61        │    └── filters (true)
    62        └── filters
    63             └── (c.x:1 = a.x:8) AND (b.x:5 = a.x:8)
    64  
    65  exec-ddl
    66  CREATE TABLE db1.a (x INT PRIMARY KEY, y FLOAT, z STRING)
    67  ----
    68  
    69  exec-ddl
    70  CREATE TABLE db2.a (x INT PRIMARY KEY, y FLOAT)
    71  ----
    72  
    73  build fully-qualify-names
    74  SELECT a.x FROM db1.a, db2.a
    75  ----
    76  error (42P09): ambiguous source name: "a"
    77  
    78  build fully-qualify-names
    79  SELECT x FROM a, b
    80  ----
    81  error (42702): column reference "x" is ambiguous (candidates: a.x, b.x)
    82  
    83  build fully-qualify-names
    84  SELECT * FROM db1.a, db2.a
    85  ----
    86  inner-join (cross)
    87   ├── columns: x:1!null y:2 z:3 x:4!null y:5
    88   ├── scan db1.public.a
    89   │    └── columns: db1.public.a.x:1!null db1.public.a.y:2 db1.public.a.z:3
    90   ├── scan db2.public.a
    91   │    └── columns: db2.public.a.x:4!null db2.public.a.y:5
    92   └── filters (true)
    93  
    94  build fully-qualify-names
    95  SELECT * FROM a, a
    96  ----
    97  error (42712): source name "a" specified more than once (missing AS clause)
    98  
    99  # TODO(justin): this case should be rejected for having a name specified twice.
   100  build fully-qualify-names
   101  SELECT * FROM a, (SELECT * FROM a) AS a
   102  ----
   103  inner-join (cross)
   104   ├── columns: x:1!null y:2 x:3!null y:4
   105   ├── scan t.public.a
   106   │    └── columns: t.public.a.x:1!null t.public.a.y:2
   107   ├── scan t.public.a
   108   │    └── columns: t.public.a.x:3!null t.public.a.y:4
   109   └── filters (true)
   110  
   111  build fully-qualify-names
   112  SELECT * FROM t.a, a
   113  ----
   114  error (42712): source name "a" specified more than once (missing AS clause)
   115  
   116  build fully-qualify-names
   117  SELECT * FROM t.a, a AS a
   118  ----
   119  inner-join (cross)
   120   ├── columns: x:1!null y:2 x:3!null y:4
   121   ├── scan t.public.a
   122   │    └── columns: t.public.a.x:1!null t.public.a.y:2
   123   ├── scan t.public.a
   124   │    └── columns: t.public.a.x:3!null t.public.a.y:4
   125   └── filters (true)
   126  
   127  build fully-qualify-names
   128  SELECT a.* FROM t.a, a AS a
   129  ----
   130  error (42P09): ambiguous source name: "a"