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

     1  # Test that pg_catalog tables are accessible without qualifying table/view
     2  # names.
     3  
     4  query TTT
     5  SHOW TABLES
     6  ----
     7  
     8  # There's no table with default values in the default test database.
     9  query I
    10  SELECT count(DISTINCT(1)) FROM pg_attrdef
    11  ----
    12  0
    13  
    14  query I
    15  SELECT count(DISTINCT(1)) FROM pg_attribute
    16  ----
    17  1
    18  
    19  query I
    20  SELECT count(DISTINCT(1)) FROM pg_class
    21  ----
    22  1
    23  
    24  query I
    25  SELECT count(DISTINCT(1)) FROM pg_namespace
    26  ----
    27  1
    28  
    29  query I
    30  SELECT count(DISTINCT(1)) FROM pg_tables
    31  ----
    32  1
    33  
    34  
    35  statement ok
    36  CREATE DATABASE t1
    37  
    38  statement ok
    39  CREATE TABLE t1.numbers (n INTEGER)
    40  
    41  statement ok
    42  CREATE DATABASE t2
    43  
    44  statement ok
    45  CREATE TABLE t2.words (w TEXT)
    46  
    47  # Test that we can query with unqualified table names from t1 and pg_catalog
    48  # (but not t2) when t1 is the session database.
    49  
    50  statement ok
    51  SET DATABASE = t1
    52  
    53  query I
    54  SELECT count(*) FROM numbers
    55  ----
    56  0
    57  
    58  query error pq: relation "words" does not exist
    59  SELECT count(*) FROM words
    60  
    61  # There's no table with default values in t1.
    62  query I
    63  SELECT count(DISTINCT(1)) FROM pg_attrdef
    64  ----
    65  1
    66  
    67  query I
    68  SELECT count(DISTINCT(1)) FROM pg_attribute
    69  ----
    70  1
    71  
    72  query I
    73  SELECT count(DISTINCT(1)) FROM pg_class
    74  ----
    75  1
    76  
    77  query I
    78  SELECT count(DISTINCT(1)) FROM pg_namespace
    79  ----
    80  1
    81  
    82  query I
    83  SELECT count(DISTINCT(1)) FROM pg_tables
    84  ----
    85  1
    86  
    87  # Test that we can query with unqualified table names from t2 and pg_catalog
    88  # (but not t1) when t2 is the session database.
    89  
    90  statement ok
    91  SET DATABASE = t2
    92  
    93  query error pq: relation "numbers" does not exist
    94  SELECT count(*) FROM numbers
    95  
    96  query I
    97  SELECT count(*) FROM words
    98  ----
    99  0
   100  
   101  # There's no table with default values in t2.
   102  query I
   103  SELECT count(DISTINCT(1)) FROM pg_attrdef
   104  ----
   105  1
   106  
   107  query I
   108  SELECT count(DISTINCT(1)) FROM pg_attribute
   109  ----
   110  1
   111  
   112  query I
   113  SELECT count(DISTINCT(1)) FROM pg_class
   114  ----
   115  1
   116  
   117  query I
   118  SELECT count(DISTINCT(1)) FROM pg_namespace
   119  ----
   120  1
   121  
   122  query I
   123  SELECT count(DISTINCT(1)) FROM pg_tables
   124  ----
   125  1
   126  
   127  # Test that current_schema reports the first valid entry in search_path, or
   128  # NULL if there is no such entry.
   129  
   130  statement ok
   131  SET search_path = nonexistent, public
   132  
   133  query T
   134  SELECT current_schema
   135  ----
   136  public
   137  
   138  statement ok
   139  SET search_path = nonexistent
   140  
   141  query T
   142  SELECT current_schema
   143  ----
   144  NULL
   145  
   146  # Test that current_schemas only reports the valid entries in
   147  # search_path.
   148  
   149  statement ok
   150  SET search_path = nonexistent, public
   151  
   152  query T
   153  SELECT current_schemas(false)
   154  ----
   155  {public}
   156  
   157  # Test that object creation targets the first valid entry in
   158  # search_path, not just the first entry.
   159  
   160  statement ok
   161  CREATE TABLE sometable(x INT); SELECT * FROM public.sometable