github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/delete (about) 1 exec-ddl 2 CREATE TABLE abcde ( 3 a INT NOT NULL, 4 b INT, 5 c INT DEFAULT (10), 6 d INT AS (b + c + 1) STORED, 7 e INT AS (a) STORED 8 ) 9 ---- 10 11 exec-ddl 12 CREATE TABLE xyz ( 13 x TEXT PRIMARY KEY, 14 y INT8, 15 z FLOAT8 16 ) 17 ---- 18 19 exec-ddl 20 CREATE TABLE uv ( 21 u DECIMAL, 22 v BYTES 23 ) 24 ---- 25 26 exec-ddl 27 CREATE TABLE mutation ( 28 m INT PRIMARY KEY, 29 n INT, 30 "o:write-only" INT DEFAULT(10), 31 "p:delete-only" INT AS (o + n) STORED 32 ) 33 ---- 34 35 # ------------------------------------------------------------------------------ 36 # Basic tests. 37 # ------------------------------------------------------------------------------ 38 39 # No extra clauses. 40 build 41 DELETE FROM abcde 42 ---- 43 delete abcde 44 ├── columns: <none> 45 ├── fetch columns: a:7 b:8 c:9 d:10 e:11 rowid:12 46 └── scan abcde 47 ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 48 └── computed column expressions 49 ├── d:10 50 │ └── (b:8 + c:9) + 1 51 └── e:11 52 └── a:7 53 54 # Use WHERE, ORDER BY, LIMIT. 55 build 56 DELETE FROM abcde WHERE a>0 ORDER BY a LIMIT 10 57 ---- 58 delete abcde 59 ├── columns: <none> 60 ├── fetch columns: a:7 b:8 c:9 d:10 e:11 rowid:12 61 └── limit 62 ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 63 ├── internal-ordering: +7 64 ├── sort 65 │ ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 66 │ ├── ordering: +7 67 │ ├── limit hint: 10.00 68 │ └── select 69 │ ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 70 │ ├── scan abcde 71 │ │ ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 72 │ │ └── computed column expressions 73 │ │ ├── d:10 74 │ │ │ └── (b:8 + c:9) + 1 75 │ │ └── e:11 76 │ │ └── a:7 77 │ └── filters 78 │ └── a:7 > 0 79 └── 10 80 81 # Use aliased table name. 82 build 83 DELETE FROM abcde AS foo WHERE foo.a>0 ORDER BY foo.a LIMIT 10 84 ---- 85 delete foo 86 ├── columns: <none> 87 ├── fetch columns: a:7 b:8 c:9 d:10 e:11 rowid:12 88 └── limit 89 ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 90 ├── internal-ordering: +7 91 ├── sort 92 │ ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 93 │ ├── ordering: +7 94 │ ├── limit hint: 10.00 95 │ └── select 96 │ ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 97 │ ├── scan foo 98 │ │ ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 99 │ │ └── computed column expressions 100 │ │ ├── d:10 101 │ │ │ └── (b:8 + c:9) + 1 102 │ │ └── e:11 103 │ │ └── a:7 104 │ └── filters 105 │ └── a:7 > 0 106 └── 10 107 108 # DELETE with index hints. 109 exec-ddl 110 CREATE TABLE xyzw ( 111 x INT PRIMARY KEY, 112 y INT, 113 z INT, 114 w INT, 115 INDEX foo (z, y) 116 ) 117 ---- 118 119 build 120 DELETE FROM xyzw@primary 121 ---- 122 delete xyzw 123 ├── columns: <none> 124 ├── fetch columns: x:5 y:6 z:7 w:8 125 └── scan xyzw 126 ├── columns: x:5!null y:6 z:7 w:8 127 └── flags: force-index=primary 128 129 build 130 DELETE FROM xyzw@foo 131 ---- 132 delete xyzw 133 ├── columns: <none> 134 ├── fetch columns: x:5 y:6 z:7 w:8 135 └── scan xyzw 136 ├── columns: x:5!null y:6 z:7 w:8 137 └── flags: force-index=foo 138 139 build 140 DELETE FROM xyzw@{FORCE_INDEX=foo,ASC} 141 ---- 142 delete xyzw 143 ├── columns: <none> 144 ├── fetch columns: x:5 y:6 z:7 w:8 145 └── scan xyzw 146 ├── columns: x:5!null y:6 z:7 w:8 147 └── flags: force-index=foo,fwd 148 149 build 150 DELETE FROM xyzw@{FORCE_INDEX=foo,DESC} 151 ---- 152 delete xyzw 153 ├── columns: <none> 154 ├── fetch columns: x:5 y:6 z:7 w:8 155 └── scan xyzw,rev 156 ├── columns: x:5!null y:6 z:7 w:8 157 └── flags: force-index=foo,rev 158 159 build 160 DELETE FROM xyzw@{NO_INDEX_JOIN} 161 ---- 162 delete xyzw 163 ├── columns: <none> 164 ├── fetch columns: x:5 y:6 z:7 w:8 165 └── scan xyzw 166 ├── columns: x:5!null y:6 z:7 w:8 167 └── flags: no-index-join 168 169 build 170 DELETE FROM xyzw@bad_idx 171 ---- 172 error: index "bad_idx" not found 173 174 # Use placeholders. 175 build 176 DELETE FROM xyz WHERE x=$1 ORDER BY y+$2 DESC LIMIT 2 177 ---- 178 delete xyz 179 ├── columns: <none> 180 ├── fetch columns: x:4 y:5 z:6 181 └── limit 182 ├── columns: x:4!null y:5 z:6 column7:7 183 ├── internal-ordering: -7 184 ├── sort 185 │ ├── columns: x:4!null y:5 z:6 column7:7 186 │ ├── ordering: -7 187 │ ├── limit hint: 2.00 188 │ └── project 189 │ ├── columns: column7:7 x:4!null y:5 z:6 190 │ ├── select 191 │ │ ├── columns: x:4!null y:5 z:6 192 │ │ ├── scan xyz 193 │ │ │ └── columns: x:4!null y:5 z:6 194 │ │ └── filters 195 │ │ └── x:4 = $1 196 │ └── projections 197 │ └── y:5 + $2 [as=column7:7] 198 └── 2 199 200 201 # Use CTE within WHERE clause. 202 build 203 WITH cte AS (SELECT x FROM xyz) DELETE FROM abcde WHERE EXISTS(SELECT * FROM cte) 204 ---- 205 with &1 (cte) 206 ├── project 207 │ ├── columns: xyz.x:1!null 208 │ └── scan xyz 209 │ └── columns: xyz.x:1!null y:2 z:3 210 └── delete abcde 211 ├── columns: <none> 212 ├── fetch columns: a:10 b:11 c:12 d:13 e:14 rowid:15 213 └── select 214 ├── columns: a:10!null b:11 c:12 d:13 e:14 rowid:15!null 215 ├── scan abcde 216 │ ├── columns: a:10!null b:11 c:12 d:13 e:14 rowid:15!null 217 │ └── computed column expressions 218 │ ├── d:13 219 │ │ └── (b:11 + c:12) + 1 220 │ └── e:14 221 │ └── a:10 222 └── filters 223 └── exists 224 └── with-scan &1 (cte) 225 ├── columns: x:16!null 226 └── mapping: 227 └── xyz.x:1 => x:16 228 229 # Unknown target table. 230 build 231 DELETE FROM unknown WHERE x=1 232 ---- 233 error (42P01): no data source matches prefix: "unknown" 234 235 # Try to use non-returning UPDATE as expression. 236 build 237 SELECT * FROM [DELETE FROM abcde WHERE a=1] 238 ---- 239 error (42703): statement source "DELETE FROM abcde WHERE a = 1" does not return any columns 240 241 # Non-referenced CTE with mutation. 242 build 243 WITH cte AS (SELECT b FROM [DELETE FROM abcde WHERE a=b RETURNING *]) DELETE FROM abcde WHERE a=b 244 ---- 245 with &1 246 ├── project 247 │ ├── columns: abcde.a:1!null abcde.b:2!null abcde.c:3 abcde.d:4 abcde.e:5 248 │ └── delete abcde 249 │ ├── columns: abcde.a:1!null abcde.b:2!null abcde.c:3 abcde.d:4 abcde.e:5 rowid:6!null 250 │ ├── fetch columns: abcde.a:7 abcde.b:8 abcde.c:9 abcde.d:10 abcde.e:11 rowid:12 251 │ └── select 252 │ ├── columns: abcde.a:7!null abcde.b:8!null abcde.c:9 abcde.d:10 abcde.e:11 rowid:12!null 253 │ ├── scan abcde 254 │ │ ├── columns: abcde.a:7!null abcde.b:8 abcde.c:9 abcde.d:10 abcde.e:11 rowid:12!null 255 │ │ └── computed column expressions 256 │ │ ├── abcde.d:10 257 │ │ │ └── (abcde.b:8 + abcde.c:9) + 1 258 │ │ └── abcde.e:11 259 │ │ └── abcde.a:7 260 │ └── filters 261 │ └── abcde.a:7 = abcde.b:8 262 └── with &2 (cte) 263 ├── project 264 │ ├── columns: b:14!null 265 │ └── with-scan &1 266 │ ├── columns: a:13!null b:14!null c:15 d:16 e:17 267 │ └── mapping: 268 │ ├── abcde.a:1 => a:13 269 │ ├── abcde.b:2 => b:14 270 │ ├── abcde.c:3 => c:15 271 │ ├── abcde.d:4 => d:16 272 │ └── abcde.e:5 => e:17 273 └── delete abcde 274 ├── columns: <none> 275 ├── fetch columns: abcde.a:24 abcde.b:25 abcde.c:26 abcde.d:27 abcde.e:28 rowid:29 276 └── select 277 ├── columns: abcde.a:24!null abcde.b:25!null abcde.c:26 abcde.d:27 abcde.e:28 rowid:29!null 278 ├── scan abcde 279 │ ├── columns: abcde.a:24!null abcde.b:25 abcde.c:26 abcde.d:27 abcde.e:28 rowid:29!null 280 │ └── computed column expressions 281 │ ├── abcde.d:27 282 │ │ └── (abcde.b:25 + abcde.c:26) + 1 283 │ └── abcde.e:28 284 │ └── abcde.a:24 285 └── filters 286 └── abcde.a:24 = abcde.b:25 287 288 # With alias, original table name should be inaccessible. 289 build 290 DELETE FROM abcde AS foo WHERE a=abcde.b 291 ---- 292 error (42P01): no data source matches prefix: abcde 293 294 # ORDER BY can only be used with LIMIT. 295 build 296 DELETE FROM abcde WHERE b=1 ORDER BY c 297 ---- 298 error (42601): DELETE statement requires LIMIT when ORDER BY is used 299 300 # ------------------------------------------------------------------------------ 301 # Test RETURNING. 302 # ------------------------------------------------------------------------------ 303 304 # Return values from delete. 305 build 306 DELETE FROM abcde WHERE a=1 RETURNING * 307 ---- 308 project 309 ├── columns: a:1!null b:2 c:3 d:4 e:5 310 └── delete abcde 311 ├── columns: a:1!null b:2 c:3 d:4 e:5 rowid:6!null 312 ├── fetch columns: a:7 b:8 c:9 d:10 e:11 rowid:12 313 └── select 314 ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 315 ├── scan abcde 316 │ ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 317 │ └── computed column expressions 318 │ ├── d:10 319 │ │ └── (b:8 + c:9) + 1 320 │ └── e:11 321 │ └── a:7 322 └── filters 323 └── a:7 = 1 324 325 # Return values from aliased table. 326 build 327 DELETE FROM abcde AS foo WHERE a=1 RETURNING foo.a+1, foo.b * foo.d 328 ---- 329 project 330 ├── columns: "?column?":13!null "?column?":14 331 ├── delete foo 332 │ ├── columns: a:1!null b:2 c:3 d:4 e:5 rowid:6!null 333 │ ├── fetch columns: a:7 b:8 c:9 d:10 e:11 rowid:12 334 │ └── select 335 │ ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 336 │ ├── scan foo 337 │ │ ├── columns: a:7!null b:8 c:9 d:10 e:11 rowid:12!null 338 │ │ └── computed column expressions 339 │ │ ├── d:10 340 │ │ │ └── (b:8 + c:9) + 1 341 │ │ └── e:11 342 │ │ └── a:7 343 │ └── filters 344 │ └── a:7 = 1 345 └── projections 346 ├── a:1 + 1 [as="?column?":13] 347 └── b:2 * d:4 [as="?column?":14] 348 349 # Use returning DELETE as a FROM expression. 350 build 351 SELECT a, d FROM [DELETE FROM abcde WHERE a>0 ORDER BY b LIMIT 10 RETURNING *] 352 ---- 353 with &1 354 ├── columns: a:13!null d:16 355 ├── project 356 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3 abcde.d:4 abcde.e:5 357 │ └── delete abcde 358 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3 abcde.d:4 abcde.e:5 rowid:6!null 359 │ ├── fetch columns: abcde.a:7 abcde.b:8 abcde.c:9 abcde.d:10 abcde.e:11 rowid:12 360 │ └── limit 361 │ ├── columns: abcde.a:7!null abcde.b:8 abcde.c:9 abcde.d:10 abcde.e:11 rowid:12!null 362 │ ├── internal-ordering: +8 363 │ ├── sort 364 │ │ ├── columns: abcde.a:7!null abcde.b:8 abcde.c:9 abcde.d:10 abcde.e:11 rowid:12!null 365 │ │ ├── ordering: +8 366 │ │ ├── limit hint: 10.00 367 │ │ └── select 368 │ │ ├── columns: abcde.a:7!null abcde.b:8 abcde.c:9 abcde.d:10 abcde.e:11 rowid:12!null 369 │ │ ├── scan abcde 370 │ │ │ ├── columns: abcde.a:7!null abcde.b:8 abcde.c:9 abcde.d:10 abcde.e:11 rowid:12!null 371 │ │ │ └── computed column expressions 372 │ │ │ ├── abcde.d:10 373 │ │ │ │ └── (abcde.b:8 + abcde.c:9) + 1 374 │ │ │ └── abcde.e:11 375 │ │ │ └── abcde.a:7 376 │ │ └── filters 377 │ │ └── abcde.a:7 > 0 378 │ └── 10 379 └── project 380 ├── columns: a:13!null d:16 381 └── with-scan &1 382 ├── columns: a:13!null b:14 c:15 d:16 e:17 383 └── mapping: 384 ├── abcde.a:1 => a:13 385 ├── abcde.b:2 => b:14 386 ├── abcde.c:3 => c:15 387 ├── abcde.d:4 => d:16 388 └── abcde.e:5 => e:17 389 390 # ------------------------------------------------------------------------------ 391 # Tests with mutations. 392 # ------------------------------------------------------------------------------ 393 394 # Without RETURNING clause. 395 build 396 DELETE FROM mutation WHERE m=1 397 ---- 398 delete mutation 399 ├── columns: <none> 400 ├── fetch columns: m:5 n:6 o:7 p:8 401 └── select 402 ├── columns: m:5!null n:6 o:7 p:8 403 ├── scan mutation 404 │ └── columns: m:5!null n:6 o:7 p:8 405 └── filters 406 └── m:5 = 1 407 408 # With RETURNING clause. 409 build 410 DELETE FROM mutation WHERE m=1 RETURNING * 411 ---- 412 delete mutation 413 ├── columns: m:1!null n:2 414 ├── fetch columns: m:5 n:6 o:7 p:8 415 └── select 416 ├── columns: m:5!null n:6 o:7 p:8 417 ├── scan mutation 418 │ └── columns: m:5!null n:6 o:7 p:8 419 └── filters 420 └── m:5 = 1 421 422 423 # Try to return a mutation column. 424 build 425 DELETE FROM mutation RETURNING o 426 ---- 427 error (42703): column "o" does not exist 428 429 # Try to use mutation column in WHERE clause. 430 build 431 DELETE FROM mutation WHERE o=10 432 ---- 433 error (42P10): column "o" is being backfilled 434 435 # Try to use mutation column in ORDER BY expression. 436 build 437 DELETE FROM mutation ORDER BY p LIMIT 2 438 ---- 439 error (42P10): column "p" is being backfilled