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

     1  # LogicTest: local
     2  
     3  # savetables is the name of the database where tables created by the
     4  # saveTableNode are stored.
     5  statement ok
     6  CREATE DATABASE savetables; USE savetables
     7  
     8  statement ok
     9  CREATE TABLE t (k INT PRIMARY KEY, str STRING)
    10  
    11  statement ok
    12  INSERT INTO t SELECT i, to_english(i) FROM generate_series(1, 5) AS g(i)
    13  
    14  statement ok
    15  CREATE TABLE u (key INT PRIMARY KEY, val STRING)
    16  
    17  statement ok
    18  INSERT INTO u SELECT i, to_english(i) FROM generate_series(2, 10) AS g(i)
    19  
    20  statement ok
    21  SET save_tables_prefix = 'save_table_test'
    22  
    23  query IT rowsort
    24  SELECT * FROM t
    25  ----
    26  1  one
    27  2  two
    28  3  three
    29  4  four
    30  5  five
    31  
    32  query error create save table: relation "save_table_test_scan_1" already exists
    33  SELECT * FROM u
    34  
    35  statement ok
    36  SET save_tables_prefix = 'st_test'
    37  
    38  query IT rowsort
    39  SELECT u.key, t.str FROM t, u WHERE t.k = u.key AND t.k >= 3
    40  ----
    41  3  three
    42  4  four
    43  5  five
    44  
    45  statement ok
    46  SET save_tables_prefix = 'st'
    47  
    48  query IT rowsort
    49  SELECT u.key, t.str FROM t, u WHERE t.k = u.key AND u.val LIKE 't%'
    50  ----
    51  2  two
    52  3  three
    53  
    54  # Disable creation of saveTable nodes.
    55  statement ok
    56  SET save_tables_prefix = ''
    57  
    58  query ITI colnames
    59  SELECT * FROM st_test_merge_join_2 ORDER BY k
    60  ----
    61  k  str    key
    62  3  three  3
    63  4  four   4
    64  5  five   5
    65  
    66  query IT colnames
    67  SELECT * FROM st_scan_4 ORDER BY key
    68  ----
    69  key  val
    70  2    two
    71  3    three
    72  4    four
    73  5    five
    74  6    six
    75  7    seven
    76  8    eight
    77  9    nine
    78  10   one-zero
    79  
    80  query TTT rowsort
    81  SHOW TABLES
    82  ----
    83  public  save_table_test_scan_1  table
    84  public  st_lookup_join_2        table
    85  public  st_project_1            table
    86  public  st_scan_4               table
    87  public  st_select_3             table
    88  public  st_test_merge_join_2    table
    89  public  st_test_project_1       table
    90  public  st_test_scan_3          table
    91  public  st_test_scan_4          table
    92  public  t                       table
    93  public  u                       table
    94  
    95  # Only root may use the saveTableNode.
    96  
    97  statement ok
    98  GRANT ALL ON t TO testuser
    99  
   100  user testuser
   101  
   102  statement ok
   103  USE savetables
   104  
   105  query IT rowsort
   106  SELECT * FROM t
   107  ----
   108  1  one
   109  2  two
   110  3  three
   111  4  four
   112  5  five
   113  
   114  statement ok
   115  SET save_tables_prefix = 'tt'
   116  
   117  statement error sub-expression tables creation may only be used by root
   118  SELECT * FROM t