github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/select_for_update (about) 1 exec-ddl 2 CREATE TABLE t (a INT PRIMARY KEY, b INT) 3 ---- 4 5 exec-ddl 6 CREATE TABLE u (a INT PRIMARY KEY, c INT) 7 ---- 8 9 exec-ddl 10 CREATE VIEW v AS SELECT a FROM t AS t2 11 ---- 12 13 # ------------------------------------------------------------------------------ 14 # Basic tests. 15 # ------------------------------------------------------------------------------ 16 17 build 18 SELECT * FROM t FOR UPDATE 19 ---- 20 scan t 21 ├── columns: a:1!null b:2 22 └── locking: for-update 23 24 build 25 SELECT * FROM t FOR NO KEY UPDATE 26 ---- 27 scan t 28 ├── columns: a:1!null b:2 29 └── locking: for-no-key-update 30 31 build 32 SELECT * FROM t FOR SHARE 33 ---- 34 scan t 35 ├── columns: a:1!null b:2 36 └── locking: for-share 37 38 build 39 SELECT * FROM t FOR KEY SHARE 40 ---- 41 scan t 42 ├── columns: a:1!null b:2 43 └── locking: for-key-share 44 45 build 46 SELECT * FROM t FOR KEY SHARE FOR SHARE 47 ---- 48 scan t 49 ├── columns: a:1!null b:2 50 └── locking: for-share 51 52 build 53 SELECT * FROM t FOR KEY SHARE FOR SHARE FOR NO KEY UPDATE 54 ---- 55 scan t 56 ├── columns: a:1!null b:2 57 └── locking: for-no-key-update 58 59 build 60 SELECT * FROM t FOR KEY SHARE FOR SHARE FOR NO KEY UPDATE FOR UPDATE 61 ---- 62 scan t 63 ├── columns: a:1!null b:2 64 └── locking: for-update 65 66 build 67 SELECT * FROM t FOR UPDATE OF t 68 ---- 69 scan t 70 ├── columns: a:1!null b:2 71 └── locking: for-update 72 73 build 74 SELECT * FROM t FOR UPDATE OF t2 75 ---- 76 error (42P01): relation "t2" in FOR UPDATE clause not found in FROM clause 77 78 build 79 SELECT 1 FROM t FOR UPDATE OF t 80 ---- 81 project 82 ├── columns: "?column?":3!null 83 ├── scan t 84 │ ├── columns: a:1!null b:2 85 │ └── locking: for-update 86 └── projections 87 └── 1 [as="?column?":3] 88 89 # ------------------------------------------------------------------------------ 90 # Tests with table aliases. 91 # ------------------------------------------------------------------------------ 92 93 build 94 SELECT * FROM t AS t2 FOR UPDATE 95 ---- 96 scan t2 97 ├── columns: a:1!null b:2 98 └── locking: for-update 99 100 build 101 SELECT * FROM t AS t2 FOR UPDATE OF t 102 ---- 103 error (42P01): relation "t" in FOR UPDATE clause not found in FROM clause 104 105 build 106 SELECT * FROM t AS t2 FOR UPDATE OF t2 107 ---- 108 scan t2 109 ├── columns: a:1!null b:2 110 └── locking: for-update 111 112 # ------------------------------------------------------------------------------ 113 # Tests with numeric table references. 114 # Cockroach numeric references start after 53 for user tables. 115 # ------------------------------------------------------------------------------ 116 117 build 118 SELECT * FROM [53 AS t] FOR UPDATE 119 ---- 120 scan t 121 ├── columns: a:1!null b:2 122 └── locking: for-update 123 124 build 125 SELECT * FROM [53 AS t] FOR UPDATE OF t 126 ---- 127 scan t 128 ├── columns: a:1!null b:2 129 └── locking: for-update 130 131 build 132 SELECT * FROM [53 AS t] FOR UPDATE OF t2 133 ---- 134 error (42P01): relation "t2" in FOR UPDATE clause not found in FROM clause 135 136 # ------------------------------------------------------------------------------ 137 # Tests with views. 138 # ------------------------------------------------------------------------------ 139 140 build 141 SELECT * FROM v FOR UPDATE 142 ---- 143 project 144 ├── columns: a:1!null 145 └── scan t2 146 ├── columns: a:1!null b:2 147 └── locking: for-update 148 149 build 150 SELECT * FROM v FOR UPDATE OF v 151 ---- 152 project 153 ├── columns: a:1!null 154 └── scan t2 155 ├── columns: a:1!null b:2 156 └── locking: for-update 157 158 build 159 SELECT * FROM v FOR UPDATE OF v2 160 ---- 161 error (42P01): relation "v2" in FOR UPDATE clause not found in FROM clause 162 163 build 164 SELECT * FROM v FOR UPDATE OF t 165 ---- 166 error (42P01): relation "t" in FOR UPDATE clause not found in FROM clause 167 168 build 169 SELECT * FROM v FOR UPDATE OF t2 170 ---- 171 error (42P01): relation "t2" in FOR UPDATE clause not found in FROM clause 172 173 # ------------------------------------------------------------------------------ 174 # Tests with aliased views. 175 # ------------------------------------------------------------------------------ 176 177 build 178 SELECT * FROM v AS v2 FOR UPDATE 179 ---- 180 project 181 ├── columns: a:1!null 182 └── scan t2 183 ├── columns: a:1!null b:2 184 └── locking: for-update 185 186 build 187 SELECT * FROM v AS v2 FOR UPDATE OF v 188 ---- 189 error (42P01): relation "v" in FOR UPDATE clause not found in FROM clause 190 191 build 192 SELECT * FROM v AS v2 FOR UPDATE OF v2 193 ---- 194 project 195 ├── columns: a:1!null 196 └── scan t2 197 ├── columns: a:1!null b:2 198 └── locking: for-update 199 200 # ------------------------------------------------------------------------------ 201 # Tests with subqueries. 202 # 203 # Row-level locking clauses only apply to subqueries in the FROM clause of a 204 # SELECT statement. They don't apply to subqueries in the projection or in 205 # the filter. 206 # ------------------------------------------------------------------------------ 207 208 build 209 SELECT * FROM (SELECT a FROM t) FOR UPDATE 210 ---- 211 project 212 ├── columns: a:1!null 213 └── scan t 214 ├── columns: a:1!null b:2 215 └── locking: for-update 216 217 build 218 SELECT * FROM (SELECT a FROM t FOR UPDATE) 219 ---- 220 project 221 ├── columns: a:1!null 222 └── scan t 223 ├── columns: a:1!null b:2 224 └── locking: for-update 225 226 build 227 SELECT * FROM (SELECT a FROM t FOR NO KEY UPDATE) FOR KEY SHARE 228 ---- 229 project 230 ├── columns: a:1!null 231 └── scan t 232 ├── columns: a:1!null b:2 233 └── locking: for-no-key-update 234 235 build 236 SELECT * FROM (SELECT a FROM t FOR KEY SHARE) FOR NO KEY UPDATE 237 ---- 238 project 239 ├── columns: a:1!null 240 └── scan t 241 ├── columns: a:1!null b:2 242 └── locking: for-no-key-update 243 244 build 245 SELECT * FROM (SELECT a FROM t) FOR UPDATE OF t 246 ---- 247 error (42P01): relation "t" in FOR UPDATE clause not found in FROM clause 248 249 build 250 SELECT * FROM (SELECT a FROM t FOR UPDATE OF t) 251 ---- 252 project 253 ├── columns: a:1!null 254 └── scan t 255 ├── columns: a:1!null b:2 256 └── locking: for-update 257 258 build 259 SELECT * FROM (SELECT a FROM t) AS r FOR UPDATE 260 ---- 261 project 262 ├── columns: a:1!null 263 └── scan t 264 ├── columns: a:1!null b:2 265 └── locking: for-update 266 267 build 268 SELECT * FROM (SELECT a FROM t FOR UPDATE) AS r 269 ---- 270 project 271 ├── columns: a:1!null 272 └── scan t 273 ├── columns: a:1!null b:2 274 └── locking: for-update 275 276 build 277 SELECT * FROM (SELECT a FROM t) AS r FOR UPDATE OF t 278 ---- 279 error (42P01): relation "t" in FOR UPDATE clause not found in FROM clause 280 281 build 282 SELECT * FROM (SELECT a FROM t FOR UPDATE OF t) AS r 283 ---- 284 project 285 ├── columns: a:1!null 286 └── scan t 287 ├── columns: a:1!null b:2 288 └── locking: for-update 289 290 build 291 SELECT (SELECT a FROM t) FOR UPDATE 292 ---- 293 project 294 ├── columns: a:3 295 ├── values 296 │ └── () 297 └── projections 298 └── subquery [as=a:3] 299 └── max1-row 300 ├── columns: t.a:1!null 301 └── project 302 ├── columns: t.a:1!null 303 └── scan t 304 └── columns: t.a:1!null b:2 305 306 build 307 SELECT (SELECT a FROM t FOR UPDATE) 308 ---- 309 project 310 ├── columns: a:3 311 ├── values 312 │ └── () 313 └── projections 314 └── subquery [as=a:3] 315 └── max1-row 316 ├── columns: t.a:1!null 317 └── project 318 ├── columns: t.a:1!null 319 └── scan t 320 ├── columns: t.a:1!null b:2 321 └── locking: for-update 322 323 build 324 SELECT (SELECT a FROM t) FOR UPDATE OF t 325 ---- 326 error (42P01): relation "t" in FOR UPDATE clause not found in FROM clause 327 328 build 329 SELECT (SELECT a FROM t FOR UPDATE OF t) 330 ---- 331 project 332 ├── columns: a:3 333 ├── values 334 │ └── () 335 └── projections 336 └── subquery [as=a:3] 337 └── max1-row 338 ├── columns: t.a:1!null 339 └── project 340 ├── columns: t.a:1!null 341 └── scan t 342 ├── columns: t.a:1!null b:2 343 └── locking: for-update 344 345 build 346 SELECT (SELECT a FROM t) AS r FOR UPDATE 347 ---- 348 project 349 ├── columns: r:3 350 ├── values 351 │ └── () 352 └── projections 353 └── subquery [as=r:3] 354 └── max1-row 355 ├── columns: a:1!null 356 └── project 357 ├── columns: a:1!null 358 └── scan t 359 └── columns: a:1!null b:2 360 361 build 362 SELECT (SELECT a FROM t FOR UPDATE) AS r 363 ---- 364 project 365 ├── columns: r:3 366 ├── values 367 │ └── () 368 └── projections 369 └── subquery [as=r:3] 370 └── max1-row 371 ├── columns: a:1!null 372 └── project 373 ├── columns: a:1!null 374 └── scan t 375 ├── columns: a:1!null b:2 376 └── locking: for-update 377 378 build 379 SELECT (SELECT a FROM t) AS r FOR UPDATE OF t 380 ---- 381 error (42P01): relation "t" in FOR UPDATE clause not found in FROM clause 382 383 build 384 SELECT (SELECT a FROM t FOR UPDATE OF t) AS r 385 ---- 386 project 387 ├── columns: r:3 388 ├── values 389 │ └── () 390 └── projections 391 └── subquery [as=r:3] 392 └── max1-row 393 ├── columns: a:1!null 394 └── project 395 ├── columns: a:1!null 396 └── scan t 397 ├── columns: a:1!null b:2 398 └── locking: for-update 399 400 build 401 SELECT * FROM t WHERE a IN (SELECT a FROM t) FOR UPDATE 402 ---- 403 select 404 ├── columns: a:1!null b:2 405 ├── scan t 406 │ ├── columns: a:1!null b:2 407 │ └── locking: for-update 408 └── filters 409 └── any: eq 410 ├── project 411 │ ├── columns: a:3!null 412 │ └── scan t 413 │ └── columns: a:3!null b:4 414 └── a:1 415 416 build 417 SELECT * FROM t WHERE a IN (SELECT a FROM t FOR UPDATE) 418 ---- 419 select 420 ├── columns: a:1!null b:2 421 ├── scan t 422 │ └── columns: a:1!null b:2 423 └── filters 424 └── any: eq 425 ├── project 426 │ ├── columns: a:3!null 427 │ └── scan t 428 │ ├── columns: a:3!null b:4 429 │ └── locking: for-update 430 └── a:1 431 432 build 433 SELECT * FROM t WHERE a IN (SELECT a FROM t) FOR UPDATE OF t 434 ---- 435 select 436 ├── columns: a:1!null b:2 437 ├── scan t 438 │ ├── columns: a:1!null b:2 439 │ └── locking: for-update 440 └── filters 441 └── any: eq 442 ├── project 443 │ ├── columns: a:3!null 444 │ └── scan t 445 │ └── columns: a:3!null b:4 446 └── a:1 447 448 build 449 SELECT * FROM t WHERE a IN (SELECT a FROM t FOR UPDATE OF t) 450 ---- 451 select 452 ├── columns: a:1!null b:2 453 ├── scan t 454 │ └── columns: a:1!null b:2 455 └── filters 456 └── any: eq 457 ├── project 458 │ ├── columns: a:3!null 459 │ └── scan t 460 │ ├── columns: a:3!null b:4 461 │ └── locking: for-update 462 └── a:1 463 464 # ------------------------------------------------------------------------------ 465 # Tests with common-table expressions. 466 # 467 # Unlike with FROM subqueries, row-level locking clauses do not apply to WITH 468 # queries referenced by the primary query. To achieve row locking within a WITH 469 # query, a locking clause should be specified within the WITH query. 470 # ------------------------------------------------------------------------------ 471 472 build 473 SELECT * FROM [SELECT a FROM t] FOR UPDATE 474 ---- 475 with &1 476 ├── columns: a:3!null 477 ├── project 478 │ ├── columns: t.a:1!null 479 │ └── scan t 480 │ └── columns: t.a:1!null b:2 481 └── with-scan &1 482 ├── columns: a:3!null 483 └── mapping: 484 └── t.a:1 => a:3 485 486 build 487 WITH cte AS (SELECT a FROM t) SELECT * FROM cte FOR UPDATE 488 ---- 489 with &1 (cte) 490 ├── columns: a:3!null 491 ├── project 492 │ ├── columns: t.a:1!null 493 │ └── scan t 494 │ └── columns: t.a:1!null b:2 495 └── with-scan &1 (cte) 496 ├── columns: a:3!null 497 └── mapping: 498 └── t.a:1 => a:3 499 500 build 501 SELECT * FROM [SELECT a FROM t FOR UPDATE] 502 ---- 503 with &1 504 ├── columns: a:3!null 505 ├── project 506 │ ├── columns: t.a:1!null 507 │ └── scan t 508 │ ├── columns: t.a:1!null b:2 509 │ └── locking: for-update 510 └── with-scan &1 511 ├── columns: a:3!null 512 └── mapping: 513 └── t.a:1 => a:3 514 515 build 516 WITH cte AS (SELECT a FROM t FOR UPDATE) SELECT * FROM cte 517 ---- 518 with &1 (cte) 519 ├── columns: a:3!null 520 ├── project 521 │ ├── columns: t.a:1!null 522 │ └── scan t 523 │ ├── columns: t.a:1!null b:2 524 │ └── locking: for-update 525 └── with-scan &1 (cte) 526 ├── columns: a:3!null 527 └── mapping: 528 └── t.a:1 => a:3 529 530 # ------------------------------------------------------------------------------ 531 # Tests with joins. 532 # ------------------------------------------------------------------------------ 533 534 build 535 SELECT * FROM t JOIN u USING (a) FOR UPDATE 536 ---- 537 project 538 ├── columns: a:1!null b:2 c:4 539 └── inner-join (hash) 540 ├── columns: t.a:1!null b:2 u.a:3!null c:4 541 ├── scan t 542 │ ├── columns: t.a:1!null b:2 543 │ └── locking: for-update 544 ├── scan u 545 │ ├── columns: u.a:3!null c:4 546 │ └── locking: for-update 547 └── filters 548 └── t.a:1 = u.a:3 549 550 build 551 SELECT * FROM t JOIN u USING (a) FOR UPDATE OF t 552 ---- 553 project 554 ├── columns: a:1!null b:2 c:4 555 └── inner-join (hash) 556 ├── columns: t.a:1!null b:2 u.a:3!null c:4 557 ├── scan t 558 │ ├── columns: t.a:1!null b:2 559 │ └── locking: for-update 560 ├── scan u 561 │ └── columns: u.a:3!null c:4 562 └── filters 563 └── t.a:1 = u.a:3 564 565 build 566 SELECT * FROM t JOIN u USING (a) FOR UPDATE OF u 567 ---- 568 project 569 ├── columns: a:1!null b:2 c:4 570 └── inner-join (hash) 571 ├── columns: t.a:1!null b:2 u.a:3!null c:4 572 ├── scan t 573 │ └── columns: t.a:1!null b:2 574 ├── scan u 575 │ ├── columns: u.a:3!null c:4 576 │ └── locking: for-update 577 └── filters 578 └── t.a:1 = u.a:3 579 580 build 581 SELECT * FROM t JOIN u USING (a) FOR UPDATE OF t, u 582 ---- 583 project 584 ├── columns: a:1!null b:2 c:4 585 └── inner-join (hash) 586 ├── columns: t.a:1!null b:2 u.a:3!null c:4 587 ├── scan t 588 │ ├── columns: t.a:1!null b:2 589 │ └── locking: for-update 590 ├── scan u 591 │ ├── columns: u.a:3!null c:4 592 │ └── locking: for-update 593 └── filters 594 └── t.a:1 = u.a:3 595 596 build 597 SELECT * FROM t JOIN u USING (a) FOR UPDATE OF t FOR SHARE OF u 598 ---- 599 project 600 ├── columns: a:1!null b:2 c:4 601 └── inner-join (hash) 602 ├── columns: t.a:1!null b:2 u.a:3!null c:4 603 ├── scan t 604 │ ├── columns: t.a:1!null b:2 605 │ └── locking: for-update 606 ├── scan u 607 │ ├── columns: u.a:3!null c:4 608 │ └── locking: for-share 609 └── filters 610 └── t.a:1 = u.a:3 611 612 build 613 SELECT * FROM t JOIN u USING (a) FOR UPDATE OF t2 FOR SHARE OF u2 614 ---- 615 error (42P01): relation "t2" in FOR UPDATE clause not found in FROM clause 616 617 build 618 SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t2 FOR SHARE OF u2 619 ---- 620 project 621 ├── columns: a:1!null b:2 c:4 622 └── inner-join (hash) 623 ├── columns: t2.a:1!null b:2 u2.a:3!null c:4 624 ├── scan t2 625 │ ├── columns: t2.a:1!null b:2 626 │ └── locking: for-update 627 ├── scan u2 628 │ ├── columns: u2.a:3!null c:4 629 │ └── locking: for-share 630 └── filters 631 └── t2.a:1 = u2.a:3 632 633 build 634 SELECT * FROM t JOIN u USING (a) FOR KEY SHARE FOR UPDATE 635 ---- 636 project 637 ├── columns: a:1!null b:2 c:4 638 └── inner-join (hash) 639 ├── columns: t.a:1!null b:2 u.a:3!null c:4 640 ├── scan t 641 │ ├── columns: t.a:1!null b:2 642 │ └── locking: for-update 643 ├── scan u 644 │ ├── columns: u.a:3!null c:4 645 │ └── locking: for-update 646 └── filters 647 └── t.a:1 = u.a:3 648 649 build 650 SELECT * FROM t JOIN u USING (a) FOR KEY SHARE FOR NO KEY UPDATE OF t 651 ---- 652 project 653 ├── columns: a:1!null b:2 c:4 654 └── inner-join (hash) 655 ├── columns: t.a:1!null b:2 u.a:3!null c:4 656 ├── scan t 657 │ ├── columns: t.a:1!null b:2 658 │ └── locking: for-no-key-update 659 ├── scan u 660 │ ├── columns: u.a:3!null c:4 661 │ └── locking: for-key-share 662 └── filters 663 └── t.a:1 = u.a:3 664 665 build 666 SELECT * FROM t JOIN u USING (a) FOR SHARE FOR NO KEY UPDATE OF t FOR UPDATE OF u 667 ---- 668 project 669 ├── columns: a:1!null b:2 c:4 670 └── inner-join (hash) 671 ├── columns: t.a:1!null b:2 u.a:3!null c:4 672 ├── scan t 673 │ ├── columns: t.a:1!null b:2 674 │ └── locking: for-no-key-update 675 ├── scan u 676 │ ├── columns: u.a:3!null c:4 677 │ └── locking: for-update 678 └── filters 679 └── t.a:1 = u.a:3 680 681 # ------------------------------------------------------------------------------ 682 # Tests with joins of aliased tables and aliased joins. 683 # ------------------------------------------------------------------------------ 684 685 build 686 SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE 687 ---- 688 project 689 ├── columns: a:1!null b:2 c:4 690 └── inner-join (hash) 691 ├── columns: t2.a:1!null b:2 u2.a:3!null c:4 692 ├── scan t2 693 │ ├── columns: t2.a:1!null b:2 694 │ └── locking: for-update 695 ├── scan u2 696 │ ├── columns: u2.a:3!null c:4 697 │ └── locking: for-update 698 └── filters 699 └── t2.a:1 = u2.a:3 700 701 build 702 SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t 703 ---- 704 error (42P01): relation "t" in FOR UPDATE clause not found in FROM clause 705 706 build 707 SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF u 708 ---- 709 error (42P01): relation "u" in FOR UPDATE clause not found in FROM clause 710 711 build 712 SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t, u 713 ---- 714 error (42P01): relation "t" in FOR UPDATE clause not found in FROM clause 715 716 build 717 SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t2 718 ---- 719 project 720 ├── columns: a:1!null b:2 c:4 721 └── inner-join (hash) 722 ├── columns: t2.a:1!null b:2 u2.a:3!null c:4 723 ├── scan t2 724 │ ├── columns: t2.a:1!null b:2 725 │ └── locking: for-update 726 ├── scan u2 727 │ └── columns: u2.a:3!null c:4 728 └── filters 729 └── t2.a:1 = u2.a:3 730 731 build 732 SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF u2 733 ---- 734 project 735 ├── columns: a:1!null b:2 c:4 736 └── inner-join (hash) 737 ├── columns: t2.a:1!null b:2 u2.a:3!null c:4 738 ├── scan t2 739 │ └── columns: t2.a:1!null b:2 740 ├── scan u2 741 │ ├── columns: u2.a:3!null c:4 742 │ └── locking: for-update 743 └── filters 744 └── t2.a:1 = u2.a:3 745 746 build 747 SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t2, u2 748 ---- 749 project 750 ├── columns: a:1!null b:2 c:4 751 └── inner-join (hash) 752 ├── columns: t2.a:1!null b:2 u2.a:3!null c:4 753 ├── scan t2 754 │ ├── columns: t2.a:1!null b:2 755 │ └── locking: for-update 756 ├── scan u2 757 │ ├── columns: u2.a:3!null c:4 758 │ └── locking: for-update 759 └── filters 760 └── t2.a:1 = u2.a:3 761 762 763 # Postgres doesn't support applying locking clauses to joins. The following 764 # queries all return the error: "FOR UPDATE cannot be applied to a join". 765 # We could do the same, but it's not hard to support these, so we do. 766 767 build 768 SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE 769 ---- 770 project 771 ├── columns: a:1!null b:2 c:4 772 └── inner-join (hash) 773 ├── columns: t.a:1!null b:2 u2.a:3!null c:4 774 ├── scan t 775 │ ├── columns: t.a:1!null b:2 776 │ └── locking: for-update 777 ├── scan u2 778 │ ├── columns: u2.a:3!null c:4 779 │ └── locking: for-update 780 └── filters 781 └── t.a:1 = u2.a:3 782 783 build 784 SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE OF t 785 ---- 786 error (42P01): relation "t" in FOR UPDATE clause not found in FROM clause 787 788 build 789 SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE OF u 790 ---- 791 error (42P01): relation "u" in FOR UPDATE clause not found in FROM clause 792 793 build 794 SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE OF u2 795 ---- 796 error (42P01): relation "u2" in FOR UPDATE clause not found in FROM clause 797 798 build 799 SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE OF j 800 ---- 801 project 802 ├── columns: a:1!null b:2 c:4 803 └── inner-join (hash) 804 ├── columns: t.a:1!null b:2 u2.a:3!null c:4 805 ├── scan t 806 │ ├── columns: t.a:1!null b:2 807 │ └── locking: for-update 808 ├── scan u2 809 │ ├── columns: u2.a:3!null c:4 810 │ └── locking: for-update 811 └── filters 812 └── t.a:1 = u2.a:3 813 814 # ------------------------------------------------------------------------------ 815 # Tests with lateral joins. 816 # ------------------------------------------------------------------------------ 817 818 build 819 SELECT * FROM t, u FOR UPDATE 820 ---- 821 inner-join (cross) 822 ├── columns: a:1!null b:2 a:3!null c:4 823 ├── scan t 824 │ ├── columns: t.a:1!null b:2 825 │ └── locking: for-update 826 ├── scan u 827 │ ├── columns: u.a:3!null c:4 828 │ └── locking: for-update 829 └── filters (true) 830 831 build 832 SELECT * FROM t, u FOR UPDATE OF t 833 ---- 834 inner-join (cross) 835 ├── columns: a:1!null b:2 a:3!null c:4 836 ├── scan t 837 │ ├── columns: t.a:1!null b:2 838 │ └── locking: for-update 839 ├── scan u 840 │ └── columns: u.a:3!null c:4 841 └── filters (true) 842 843 build 844 SELECT * FROM t, u FOR SHARE OF t FOR UPDATE OF u 845 ---- 846 inner-join (cross) 847 ├── columns: a:1!null b:2 a:3!null c:4 848 ├── scan t 849 │ ├── columns: t.a:1!null b:2 850 │ └── locking: for-share 851 ├── scan u 852 │ ├── columns: u.a:3!null c:4 853 │ └── locking: for-update 854 └── filters (true) 855 856 build 857 SELECT * FROM t, LATERAL (SELECT * FROM u) sub FOR UPDATE 858 ---- 859 inner-join-apply 860 ├── columns: a:1!null b:2 a:3!null c:4 861 ├── scan t 862 │ ├── columns: t.a:1!null b:2 863 │ └── locking: for-update 864 ├── scan sub 865 │ ├── columns: sub.a:3!null c:4 866 │ └── locking: for-update 867 └── filters (true) 868 869 build 870 SELECT * FROM t, LATERAL (SELECT * FROM u) sub FOR UPDATE OF u 871 ---- 872 error (42P01): relation "u" in FOR UPDATE clause not found in FROM clause 873 874 build 875 SELECT * FROM t, LATERAL (SELECT * FROM u) sub FOR UPDATE OF sub 876 ---- 877 inner-join-apply 878 ├── columns: a:1!null b:2 a:3!null c:4 879 ├── scan t 880 │ └── columns: t.a:1!null b:2 881 ├── scan sub 882 │ ├── columns: sub.a:3!null c:4 883 │ └── locking: for-update 884 └── filters (true) 885 886 # ------------------------------------------------------------------------------ 887 # Tests with virtual tables. 888 # ------------------------------------------------------------------------------ 889 890 build 891 SELECT * FROM information_schema.columns FOR UPDATE 892 ---- 893 error (42601): FOR UPDATE not allowed with virtual tables