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

     1  # Tests for SELECT with table aliasing.
     2  
     3  statement ok
     4  CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT)
     5  
     6  statement ok
     7  INSERT INTO abc VALUES (1, 2, 3), (4, 5, 6)
     8  
     9  
    10  # Verify output column naming with *.
    11  
    12  query III colnames,rowsort
    13  SELECT * FROM abc
    14  ----
    15  a b c
    16  1 2 3
    17  4 5 6
    18  
    19  query III colnames,rowsort
    20  SELECT * FROM abc AS foo
    21  ----
    22  a b c
    23  1 2 3
    24  4 5 6
    25  
    26  query III colnames,rowsort
    27  SELECT * FROM abc AS foo (foo1)
    28  ----
    29  foo1 b c
    30  1 2 3
    31  4 5 6
    32  
    33  query III colnames,rowsort
    34  SELECT * FROM abc AS foo (foo1, foo2)
    35  ----
    36  foo1 foo2 c
    37  1 2 3
    38  4 5 6
    39  
    40  query III colnames,rowsort
    41  SELECT * FROM abc AS foo (foo1, foo2, foo3)
    42  ----
    43  foo1 foo2 foo3
    44  1 2 3
    45  4 5 6
    46  
    47  
    48  # Verify qualified name resolution.
    49  
    50  query IIII colnames,rowsort
    51  SELECT foo1, foo.foo1, b, foo.c FROM abc AS foo (foo1)
    52  ----
    53  foo1 foo1 b c
    54  1 1 2 3
    55  4 4 5 6
    56  
    57  query III colnames,rowsort
    58  SELECT * FROM abc AS foo (foo1, foo2) WHERE foo.foo1 = 1
    59  ----
    60  foo1 foo2 c
    61  1 2 3
    62  
    63  query III colnames,rowsort
    64  SELECT * FROM abc AS foo (foo1, foo2) WHERE foo.foo2 = 2
    65  ----
    66  foo1 foo2 c
    67  1 2 3
    68  
    69  query III colnames,rowsort
    70  SELECT * FROM abc AS foo (foo1, foo2) WHERE foo.c = 6
    71  ----
    72  foo1 foo2 c
    73  4 5 6
    74  
    75  
    76  # Verify we can't resolve columns using overridden table or colum names.
    77  
    78  query error no data source matches prefix: abc
    79  SELECT abc.foo1 FROM abc AS foo (foo1)
    80  
    81  query error no data source matches prefix: abc
    82  SELECT abc.b FROM abc AS foo (foo1)
    83  
    84  query error column "foo.a" does not exist
    85  SELECT foo.a FROM abc AS foo (foo1)
    86  
    87  
    88  # Verify error for too many column aliases.
    89  
    90  query error pgcode 42P10 source "foo" has 3 columns available but 4 columns specified
    91  SELECT * FROM abc AS foo (foo1, foo2, foo3, foo4)
    92  
    93  
    94  # Verify that implicit columns don't interfere with aliasing.
    95  
    96  statement ok
    97  CREATE TABLE ab (a INT, b INT)
    98  
    99  statement ok
   100  INSERT INTO ab VALUES (1, 2), (1, 3), (2, 5)
   101  
   102  query II colnames,rowsort
   103  SELECT * FROM ab AS foo (foo1, foo2)
   104  ----
   105  foo1 foo2
   106  1 2
   107  1 3
   108  2 5
   109  
   110  statement ok
   111  SELECT rowid, foo.rowid FROM ab AS foo (foo1, foo2)
   112  
   113  query error no data source matches prefix: ab
   114  SELECT ab.rowid FROM ab AS foo (foo1)
   115  
   116  query error source "foo" has 2 columns available but 3 columns specified
   117  SELECT * FROM ab AS foo (foo1, foo2, foo3)
   118  
   119  query T colnames
   120  SELECT * FROM to_english(3) AS x
   121  ----
   122  x
   123  three
   124  
   125  query T colnames
   126  TABLE ROWS FROM (to_english(3)) AS x;
   127  ----
   128  x
   129  three