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

     1  # Cockroach currently supports all of the row locking modes as no-ops, so just
     2  # test that they parse and run.
     3  query I
     4  SELECT 1 FOR UPDATE
     5  ----
     6  1
     7  
     8  query I
     9  SELECT 1 FOR NO KEY UPDATE
    10  ----
    11  1
    12  
    13  query I
    14  SELECT 1 FOR SHARE
    15  ----
    16  1
    17  
    18  query I
    19  SELECT 1 FOR KEY SHARE
    20  ----
    21  1
    22  
    23  query I
    24  SELECT 1 FOR UPDATE FOR SHARE FOR NO KEY UPDATE FOR KEY SHARE
    25  ----
    26  1
    27  
    28  query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
    29  SELECT 1 FOR UPDATE OF a
    30  
    31  query error pgcode 42P01 relation "a" in FOR SHARE clause not found in FROM clause
    32  SELECT 1 FOR SHARE OF a, b
    33  
    34  query error pgcode 42P01 relation "a" in FOR UPDATE clause not found in FROM clause
    35  SELECT 1 FOR UPDATE OF a FOR SHARE OF b, c FOR NO KEY UPDATE OF d FOR KEY SHARE OF e, f
    36  
    37  query I
    38  SELECT 1 FROM
    39      (SELECT 1) a,
    40      (SELECT 1) b,
    41      (SELECT 1) c,
    42      (SELECT 1) d,
    43      (SELECT 1) e,
    44      (SELECT 1) f
    45  FOR UPDATE OF a FOR SHARE OF b, c FOR NO KEY UPDATE OF d FOR KEY SHARE OF e, f
    46  ----
    47  1
    48  
    49  # However, we do mirror Postgres in that we require FOR UPDATE targets to be
    50  # unqualified names and reject anything else.
    51  
    52  query error pgcode 42601 FOR UPDATE must specify unqualified relation names
    53  SELECT 1 FOR UPDATE OF public.a
    54  
    55  query error pgcode 42601 FOR UPDATE must specify unqualified relation names
    56  SELECT 1 FOR UPDATE OF db.public.a
    57  
    58  # We can't support SKIP LOCKED or NOWAIT, since they would actually behave
    59  # differently - NOWAIT returns an error to the client instead of blocking,
    60  # and SKIP LOCKED returns an inconsistent view.
    61  
    62  query error unimplemented: SKIP LOCKED lock wait policy is not supported
    63  SELECT 1 FOR UPDATE SKIP LOCKED
    64  
    65  query error unimplemented: SKIP LOCKED lock wait policy is not supported
    66  SELECT 1 FOR NO KEY UPDATE SKIP LOCKED
    67  
    68  query error unimplemented: SKIP LOCKED lock wait policy is not supported
    69  SELECT 1 FOR SHARE SKIP LOCKED
    70  
    71  query error unimplemented: SKIP LOCKED lock wait policy is not supported
    72  SELECT 1 FOR KEY SHARE SKIP LOCKED
    73  
    74  query error unimplemented: SKIP LOCKED lock wait policy is not supported
    75  SELECT 1 FOR UPDATE OF a SKIP LOCKED
    76  
    77  query error unimplemented: SKIP LOCKED lock wait policy is not supported
    78  SELECT 1 FOR UPDATE OF a SKIP LOCKED FOR NO KEY UPDATE OF b SKIP LOCKED
    79  
    80  query error unimplemented: SKIP LOCKED lock wait policy is not supported
    81  SELECT 1 FOR UPDATE OF a SKIP LOCKED FOR NO KEY UPDATE OF b NOWAIT
    82  
    83  query error unimplemented: NOWAIT lock wait policy is not supported
    84  SELECT 1 FOR UPDATE NOWAIT
    85  
    86  query error unimplemented: NOWAIT lock wait policy is not supported
    87  SELECT 1 FOR NO KEY UPDATE NOWAIT
    88  
    89  query error unimplemented: NOWAIT lock wait policy is not supported
    90  SELECT 1 FOR SHARE NOWAIT
    91  
    92  query error unimplemented: NOWAIT lock wait policy is not supported
    93  SELECT 1 FOR KEY SHARE NOWAIT
    94  
    95  query error unimplemented: NOWAIT lock wait policy is not supported
    96  SELECT 1 FOR UPDATE OF a NOWAIT
    97  
    98  query error unimplemented: NOWAIT lock wait policy is not supported
    99  SELECT 1 FOR UPDATE OF a NOWAIT FOR NO KEY UPDATE OF b NOWAIT
   100  
   101  # Locking clauses both inside and outside of parenthesis are handled correctly.
   102  
   103  query error unimplemented: SKIP LOCKED lock wait policy is not supported
   104  ((SELECT 1)) FOR UPDATE SKIP LOCKED
   105  
   106  query error unimplemented: SKIP LOCKED lock wait policy is not supported
   107  ((SELECT 1) FOR UPDATE SKIP LOCKED)
   108  
   109  query error unimplemented: SKIP LOCKED lock wait policy is not supported
   110  ((SELECT 1 FOR UPDATE SKIP LOCKED))
   111  
   112  # FOR READ ONLY is ignored, like in Postgres.
   113  query I
   114  SELECT 1 FOR READ ONLY
   115  ----
   116  1
   117  
   118  # Various operations are not supported when locking clauses are provided.
   119  # FeatureNotSupported errors are thrown for each of them.
   120  
   121  statement error pgcode 0A000 FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT
   122  SELECT 1 UNION SELECT 1 FOR UPDATE
   123  
   124  statement error pgcode 0A000 FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT
   125  SELECT * FROM (SELECT 1 UNION SELECT 1) a FOR UPDATE
   126  
   127  statement error pgcode 0A000 FOR UPDATE is not allowed with VALUES
   128  VALUES (1) FOR UPDATE
   129  
   130  statement error pgcode 0A000 FOR UPDATE is not allowed with VALUES
   131  SELECT * FROM (VALUES (1)) a FOR UPDATE
   132  
   133  statement error pgcode 0A000 FOR UPDATE is not allowed with DISTINCT clause
   134  SELECT DISTINCT 1 FOR UPDATE
   135  
   136  statement error pgcode 0A000 FOR UPDATE is not allowed with DISTINCT clause
   137  SELECT * FROM (SELECT DISTINCT 1) a FOR UPDATE
   138  
   139  statement error pgcode 0A000 FOR UPDATE is not allowed with GROUP BY clause
   140  SELECT 1 GROUP BY 1 FOR UPDATE
   141  
   142  statement error pgcode 0A000 FOR UPDATE is not allowed with GROUP BY clause
   143  SELECT * FROM (SELECT 1 GROUP BY 1) a FOR UPDATE
   144  
   145  statement error pgcode 0A000 FOR UPDATE is not allowed with HAVING clause
   146  SELECT 1 HAVING TRUE FOR UPDATE
   147  
   148  statement error pgcode 0A000 FOR UPDATE is not allowed with HAVING clause
   149  SELECT * FROM (SELECT 1 HAVING TRUE) a FOR UPDATE
   150  
   151  statement error pgcode 0A000 FOR UPDATE is not allowed with aggregate functions
   152  SELECT count(1) FOR UPDATE
   153  
   154  statement error pgcode 0A000 FOR UPDATE is not allowed with aggregate functions
   155  SELECT * FROM (SELECT count(1)) a FOR UPDATE
   156  
   157  statement error pgcode 0A000 FOR UPDATE is not allowed with window functions
   158  SELECT count(1) OVER () FOR UPDATE
   159  
   160  statement error pgcode 0A000 FOR UPDATE is not allowed with window functions
   161  SELECT * FROM (SELECT count(1) OVER ()) a FOR UPDATE
   162  
   163  statement error pgcode 0A000 FOR UPDATE is not allowed with set-returning functions in the target list
   164  SELECT generate_series(1, 2) FOR UPDATE
   165  
   166  statement error pgcode 0A000 FOR UPDATE is not allowed with set-returning functions in the target list
   167  SELECT * FROM (SELECT generate_series(1, 2)) a FOR UPDATE
   168  
   169  # Set-returning functions in the from list are allowed.
   170  query I
   171  SELECT * FROM generate_series(1, 2) FOR UPDATE
   172  ----
   173  1
   174  2
   175  
   176  query I
   177  SELECT * FROM (SELECT * FROM generate_series(1, 2)) a FOR UPDATE
   178  ----
   179  1
   180  2
   181  
   182  # Use of SELECT FOR UPDATE/SHARE requires UPDATE privileges, not just SELECT privileges.
   183  
   184  statement ok
   185  CREATE TABLE t (k INT PRIMARY KEY, v int)
   186  
   187  user testuser
   188  
   189  statement error pgcode 42501 user testuser does not have SELECT privilege on relation t
   190  SELECT * FROM t
   191  
   192  user root
   193  
   194  statement ok
   195  GRANT SELECT ON t TO testuser
   196  
   197  user testuser
   198  
   199  statement ok
   200  SELECT * FROM t
   201  
   202  statement error pgcode 42501 user testuser does not have UPDATE privilege on relation t
   203  SELECT * FROM t FOR UPDATE
   204  
   205  statement error pgcode 42501 user testuser does not have UPDATE privilege on relation t
   206  SELECT * FROM t FOR SHARE
   207  
   208  user root
   209  
   210  statement ok
   211  GRANT UPDATE ON t TO testuser
   212  
   213  user testuser
   214  
   215  statement ok
   216  SELECT * FROM t FOR UPDATE
   217  
   218  statement ok
   219  SELECT * FROM t FOR SHARE
   220  
   221  user root
   222  
   223  statement ok
   224  DROP TABLE t