github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/parallel_stmts_compat (about) 1 # Parallel statement execution was removed in 19.2. See #34789. 2 # The RETURNING NOTHING syntax was retained to ensure backward compatibility, 3 # even though the runtime semantics of parallel statements were removed. This 4 # logic test ensures that we continue to maintain compatibility with clients 5 # using the syntax, even though it is now ignored. 6 7 statement ok 8 CREATE TABLE kv( 9 k INT PRIMARY KEY, 10 v INT CHECK(v < 100) 11 ) 12 13 statement ok 14 CREATE TABLE fk( 15 f INT REFERENCES kv 16 ) 17 18 19 # RETURNING NOTHING can be used outside a transaction 20 21 statement ok 22 INSERT INTO kv VALUES (1, 2) RETURNING NOTHING 23 24 statement error duplicate key value \(k\)=\(1\) violates unique constraint "primary" 25 INSERT INTO kv VALUES (1, 2) RETURNING NOTHING 26 27 statement ok 28 UPSERT INTO kv VALUES (2, 2) RETURNING NOTHING 29 30 statement error failed to satisfy CHECK constraint \(v < 100:::INT8\) 31 UPSERT INTO kv VALUES (2, 500) RETURNING NOTHING 32 33 statement ok 34 UPDATE kv SET v = k WHERE k = 3 RETURNING NOTHING 35 36 statement error duplicate key value \(k\)=\(1\) violates unique constraint "primary" 37 UPDATE kv SET k = 1 WHERE k = 2 RETURNING NOTHING 38 39 statement ok 40 DELETE FROM kv WHERE k = 1 RETURNING NOTHING 41 42 statement ok 43 INSERT INTO fk VALUES (2) 44 45 statement error delete on table "kv" violates foreign key constraint "fk_f_ref_kv" on table "fk"\nDETAIL: Key \(k\)=\(2\) is still referenced from table "fk" 46 DELETE FROM kv WHERE k = 2 RETURNING NOTHING 47 48 statement ok 49 DELETE FROM fk 50 51 statement ok 52 DELETE FROM kv 53 54 55 # insert with RETURNING NOTHING can be used inside a successful transaction 56 57 statement ok 58 BEGIN 59 60 statement ok 61 INSERT INTO kv VALUES (1, 2) RETURNING NOTHING 62 63 statement ok 64 INSERT INTO kv VALUES (2, 3) RETURNING NOTHING 65 66 statement ok 67 INSERT INTO kv VALUES (3, 4) RETURNING NOTHING 68 69 statement ok 70 COMMIT 71 72 query II 73 SELECT k, v FROM kv ORDER BY k 74 ---- 75 1 2 76 2 3 77 3 4 78 79 # insert with RETURNING NOTHING can be used inside an unsuccessful transaction 80 81 statement ok 82 BEGIN 83 84 statement ok 85 INSERT INTO kv VALUES (4, 5) RETURNING NOTHING 86 87 statement error duplicate key value \(k\)=\(2\) violates unique constraint "primary" 88 INSERT INTO kv VALUES (2, 3) RETURNING NOTHING 89 90 statement error current transaction is aborted, commands ignored until end of transaction block 91 INSERT INTO kv VALUES (5, 6) RETURNING NOTHING 92 93 statement ok 94 COMMIT 95 96 query T 97 SHOW TRANSACTION STATUS 98 ---- 99 NoTxn 100 101 query II 102 SELECT k, v FROM kv ORDER BY k 103 ---- 104 1 2 105 2 3 106 3 4 107 108 109 # upsert with RETURNING NOTHING can be used inside a successful transaction 110 111 statement ok 112 BEGIN 113 114 statement ok 115 UPSERT INTO kv VALUES (1, 7) RETURNING NOTHING 116 117 statement ok 118 UPSERT INTO kv VALUES (4, 8) RETURNING NOTHING 119 120 statement ok 121 UPSERT INTO kv VALUES (3, 9) RETURNING NOTHING 122 123 statement ok 124 COMMIT 125 126 query II 127 SELECT k, v FROM kv ORDER BY k 128 ---- 129 1 7 130 2 3 131 3 9 132 4 8 133 134 # upsert with RETURNING NOTHING can be used inside an unsuccessful transaction 135 136 statement ok 137 BEGIN 138 139 statement ok 140 UPSERT INTO kv VALUES (1, 8) RETURNING NOTHING 141 142 statement error failed to satisfy CHECK constraint \(v < 100:::INT8\) 143 UPSERT INTO kv VALUES (4, 500) RETURNING NOTHING 144 145 statement error current transaction is aborted, commands ignored until end of transaction block 146 UPSERT INTO kv VALUES (3, 10) RETURNING NOTHING 147 148 statement ok 149 COMMIT 150 151 query II 152 SELECT k, v FROM kv ORDER BY k 153 ---- 154 1 7 155 2 3 156 3 9 157 4 8 158 159 160 # update with RETURNING NOTHING can be used inside a successful transaction 161 162 statement ok 163 BEGIN 164 165 statement ok 166 UPDATE kv SET v = k WHERE k = 1 RETURNING NOTHING 167 168 statement ok 169 UPDATE kv SET v = k WHERE k = 3 RETURNING NOTHING 170 171 statement ok 172 UPDATE kv SET v = k WHERE k = 9 RETURNING NOTHING 173 174 statement ok 175 COMMIT 176 177 query II 178 SELECT k, v FROM kv ORDER BY k 179 ---- 180 1 1 181 2 3 182 3 3 183 4 8 184 185 # update with RETURNING NOTHING can be used inside an unsuccessful transaction 186 187 statement ok 188 BEGIN 189 190 statement ok 191 UPDATE kv SET k = 9 WHERE k = 1 RETURNING NOTHING 192 193 statement error duplicate key value \(k\)=\(3\) violates unique constraint "primary" 194 UPDATE kv SET k = 3 WHERE k = 2 RETURNING NOTHING 195 196 statement error current transaction is aborted, commands ignored until end of transaction block 197 UPDATE kv SET k = 10 WHERE k = 4 RETURNING NOTHING 198 199 statement ok 200 COMMIT 201 202 query II 203 SELECT k, v FROM kv ORDER BY k 204 ---- 205 1 1 206 2 3 207 3 3 208 4 8 209 210 211 # delete with RETURNING NOTHING can be used inside a successful transaction 212 213 statement ok 214 BEGIN 215 216 statement ok 217 DELETE FROM kv WHERE k = 1 RETURNING NOTHING 218 219 statement ok 220 DELETE FROM kv WHERE k = 5 RETURNING NOTHING 221 222 statement ok 223 COMMIT 224 225 query II 226 SELECT k, v FROM kv ORDER BY k 227 ---- 228 2 3 229 3 3 230 4 8 231 232 # delete with RETURNING NOTHING can be used inside an unsuccessful transaction 233 234 statement ok 235 INSERT INTO fk VALUES (2) 236 237 statement ok 238 BEGIN 239 240 statement ok 241 DELETE FROM kv WHERE k = 1 RETURNING NOTHING 242 243 statement error delete on table "kv" violates foreign key constraint "fk_f_ref_kv" on table "fk"\nDETAIL: Key \(k\)=\(2\) is still referenced from table "fk" 244 DELETE FROM kv WHERE k = 2 RETURNING NOTHING 245 246 statement error current transaction is aborted, commands ignored until end of transaction block 247 DELETE FROM kv WHERE k = 3 RETURNING NOTHING 248 249 statement ok 250 COMMIT 251 252 query II 253 SELECT k, v FROM kv ORDER BY k 254 ---- 255 2 3 256 3 3 257 4 8 258 259 statement ok 260 DELETE FROM fk 261 262 263 # mixed mutations with RETURNING NOTHING can be used inside a successful transaction 264 265 statement ok 266 BEGIN 267 268 statement ok 269 INSERT INTO kv VALUES (1, 2) RETURNING NOTHING 270 271 statement ok 272 INSERT INTO kv VALUES (5, 9) 273 274 query II 275 SELECT k, v FROM kv ORDER BY k 276 ---- 277 1 2 278 2 3 279 3 3 280 4 8 281 5 9 282 283 statement ok 284 UPSERT INTO kv VALUES (6, 10) RETURNING NOTHING 285 286 statement ok 287 UPDATE kv SET v = k+1 WHERE k = 3 RETURNING NOTHING 288 289 query II 290 SELECT k, v FROM kv ORDER BY k 291 ---- 292 1 2 293 2 3 294 3 4 295 4 8 296 5 9 297 6 10 298 299 statement ok 300 DELETE FROM kv WHERE k = 2 RETURNING NOTHING 301 302 statement ok 303 COMMIT 304 305 query II 306 SELECT k, v FROM kv ORDER BY k 307 ---- 308 1 2 309 3 4 310 4 8 311 5 9 312 6 10 313 314 # mixed mutations with RETURNING NOTHING can be used inside an unsuccessful transaction 315 316 statement ok 317 BEGIN 318 319 statement error duplicate key value \(k\)=\(1\) violates unique constraint "primary" 320 INSERT INTO kv VALUES (1, 2) RETURNING NOTHING 321 322 statement error current transaction is aborted, commands ignored until end of transaction block 323 INSERT INTO kv VALUES (7, 7) 324 325 statement ok 326 ROLLBACK 327 328 query II 329 SELECT k, v FROM kv ORDER BY k 330 ---- 331 1 2 332 3 4 333 4 8 334 5 9 335 6 10 336 337 338 # Throw statement planning error 339 340 statement ok 341 BEGIN 342 343 statement error pgcode 42703 column "z" does not exist 344 UPDATE kv SET z = 10 WHERE k = 3 RETURNING NOTHING 345 346 statement ok 347 ROLLBACK 348 349 350 # RETURNING NOTHING can be prepared and executed. 351 352 statement ok 353 DELETE FROM kv 354 355 statement ok 356 PREPARE x AS INSERT INTO kv VALUES ($1, $2) RETURNING NOTHING 357 358 statement ok 359 BEGIN 360 361 statement ok 362 EXECUTE x(1, 2) 363 364 statement error duplicate key value \(k\)=\(1\) violates unique constraint "primary" 365 EXECUTE x(1, 2) 366 367 statement ok 368 ROLLBACK 369 370 statement ok 371 BEGIN 372 373 statement ok 374 EXECUTE x(1, 2) 375 376 statement ok 377 EXECUTE x(2, 3) 378 379 statement ok 380 EXECUTE x(3, 4) 381 382 statement ok 383 COMMIT 384 385 query II 386 SELECT k, v FROM kv ORDER BY k 387 ---- 388 1 2 389 2 3 390 3 4