delete

exec-ddl
CREATE TABLE abcde (
    a INT NOT NULL,
    b INT,
    c INT DEFAULT (10),
    d INT AS (b + c + 1) STORED,
    e INT AS (a) STORED
)
----

exec-ddl
CREATE TABLE xyz (
    x TEXT PRIMARY KEY,
    y INT8,
    z FLOAT8
)
----

exec-ddl
CREATE TABLE uv (
    u DECIMAL,
    v BYTES
)
----

exec-ddl
CREATE TABLE mutation (
    m INT PRIMARY KEY,
    n INT,
    "o:write-only" INT DEFAULT(10),
    "p:delete-only" INT AS (o + n) STORED
)
----

exec-ddl
CREATE TABLE u_a (
    a INT NOT NULL PRIMARY KEY,
    b STRING,
    c INT
)
----

exec-ddl
CREATE TABLE u_b (
  a INT NOT NULL PRIMARY KEY,
  b STRING
)
----

exec-ddl
CREATE TABLE u_c (
  a INT NOT NULL PRIMARY KEY,
  b STRING,
  c INT
)
----

exec-ddl
CREATE TABLE u_e (
  a INT NOT NULL,
  b INT
)
----

# ------------------------------------------------------------------------------
# Basic tests.
# ------------------------------------------------------------------------------

# No extra clauses.
build
DELETE FROM abcde
----
delete abcde
 ├── columns: <none>
 ├── fetch columns: a:9 b:10 c:11 d:12 e:13 rowid:14
 └── scan abcde
      ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
      └── computed column expressions
           ├── d:12
           │    └── (b:10 + c:11) + 1
           └── e:13
                └── a:9

# Use WHERE, ORDER BY, LIMIT.
build
DELETE FROM abcde WHERE a>0 ORDER BY a LIMIT 10
----
delete abcde
 ├── columns: <none>
 ├── fetch columns: a:9 b:10 c:11 d:12 e:13 rowid:14
 └── limit
      ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
      ├── internal-ordering: +9
      ├── sort
      │    ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
      │    ├── ordering: +9
      │    ├── limit hint: 10.00
      │    └── select
      │         ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
      │         ├── scan abcde
      │         │    ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
      │         │    └── computed column expressions
      │         │         ├── d:12
      │         │         │    └── (b:10 + c:11) + 1
      │         │         └── e:13
      │         │              └── a:9
      │         └── filters
      │              └── a:9 > 0
      └── 10

# Use aliased table name.
build
DELETE FROM abcde AS foo WHERE foo.a>0 ORDER BY foo.a LIMIT 10
----
delete abcde [as=foo]
 ├── columns: <none>
 ├── fetch columns: a:9 b:10 c:11 d:12 e:13 rowid:14
 └── limit
      ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
      ├── internal-ordering: +9
      ├── sort
      │    ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
      │    ├── ordering: +9
      │    ├── limit hint: 10.00
      │    └── select
      │         ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
      │         ├── scan abcde [as=foo]
      │         │    ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
      │         │    └── computed column expressions
      │         │         ├── d:12
      │         │         │    └── (b:10 + c:11) + 1
      │         │         └── e:13
      │         │              └── a:9
      │         └── filters
      │              └── a:9 > 0
      └── 10

# DELETE with index hints.
exec-ddl
CREATE TABLE xyzw (
  x INT PRIMARY KEY,
  y INT,
  z INT,
  w INT,
  INDEX foo (z, y)
)
----

build
DELETE FROM xyzw@xyzw_pkey
----
delete xyzw
 ├── columns: <none>
 ├── fetch columns: x:7 y:8 z:9 w:10
 └── scan xyzw
      ├── columns: x:7!null y:8 z:9 w:10 crdb_internal_mvcc_timestamp:11 tableoid:12
      └── flags: force-index=xyzw_pkey

build
DELETE FROM xyzw@foo
----
delete xyzw
 ├── columns: <none>
 ├── fetch columns: x:7 y:8 z:9 w:10
 └── scan xyzw
      ├── columns: x:7!null y:8 z:9 w:10 crdb_internal_mvcc_timestamp:11 tableoid:12
      └── flags: force-index=foo

build
DELETE FROM xyzw@{FORCE_INDEX=foo,ASC}
----
delete xyzw
 ├── columns: <none>
 ├── fetch columns: x:7 y:8 z:9 w:10
 └── scan xyzw
      ├── columns: x:7!null y:8 z:9 w:10 crdb_internal_mvcc_timestamp:11 tableoid:12
      └── flags: force-index=foo,fwd

build
DELETE FROM xyzw@{FORCE_INDEX=foo,DESC}
----
delete xyzw
 ├── columns: <none>
 ├── fetch columns: x:7 y:8 z:9 w:10
 └── scan xyzw,rev
      ├── columns: x:7!null y:8 z:9 w:10 crdb_internal_mvcc_timestamp:11 tableoid:12
      └── flags: force-index=foo,rev

build
DELETE FROM xyzw@{NO_INDEX_JOIN}
----
delete xyzw
 ├── columns: <none>
 ├── fetch columns: x:7 y:8 z:9 w:10
 └── scan xyzw
      ├── columns: x:7!null y:8 z:9 w:10 crdb_internal_mvcc_timestamp:11 tableoid:12
      └── flags: no-index-join

build
DELETE FROM xyzw@bad_idx
----
error (42704): index "bad_idx" not found

# Use placeholders.
build
DELETE FROM xyz WHERE x=$1 ORDER BY y+$2 DESC LIMIT 2
----
delete xyz
 ├── columns: <none>
 ├── fetch columns: x:6 y:7 z:8
 └── limit
      ├── columns: x:6!null y:7 z:8 crdb_internal_mvcc_timestamp:9 tableoid:10 column11:11
      ├── internal-ordering: -11
      ├── project
      │    ├── columns: column11:11 x:6!null y:7 z:8 crdb_internal_mvcc_timestamp:9 tableoid:10
      │    ├── ordering: -11
      │    ├── limit hint: 2.00
      │    ├── select
      │    │    ├── columns: x:6!null y:7 z:8 crdb_internal_mvcc_timestamp:9 tableoid:10
      │    │    ├── limit hint: 2.00
      │    │    ├── scan xyz
      │    │    │    └── columns: x:6!null y:7 z:8 crdb_internal_mvcc_timestamp:9 tableoid:10
      │    │    └── filters
      │    │         └── x:6 = $1
      │    └── projections
      │         └── y:7 + $2 [as=column11:11]
      └── 2


# Use CTE within WHERE clause.
build
WITH cte AS (SELECT x FROM xyz) DELETE FROM abcde WHERE EXISTS(SELECT * FROM cte)
----
with &1 (cte)
 ├── project
 │    ├── columns: xyz.x:1!null
 │    └── scan xyz
 │         └── columns: xyz.x:1!null y:2 z:3 xyz.crdb_internal_mvcc_timestamp:4 xyz.tableoid:5
 └── delete abcde
      ├── columns: <none>
      ├── fetch columns: a:14 b:15 c:16 d:17 e:18 rowid:19
      └── select
           ├── columns: a:14!null b:15 c:16 d:17 e:18 rowid:19!null abcde.crdb_internal_mvcc_timestamp:20 abcde.tableoid:21
           ├── scan abcde
           │    ├── columns: a:14!null b:15 c:16 d:17 e:18 rowid:19!null abcde.crdb_internal_mvcc_timestamp:20 abcde.tableoid:21
           │    └── computed column expressions
           │         ├── d:17
           │         │    └── (b:15 + c:16) + 1
           │         └── e:18
           │              └── a:14
           └── filters
                └── exists
                     └── with-scan &1 (cte)
                          ├── columns: x:22!null
                          └── mapping:
                               └──  xyz.x:1 => x:22

# Unknown target table.
build
DELETE FROM unknown WHERE x=1
----
error (42P01): no data source matches prefix: "unknown"

# Try to use non-returning UPDATE as expression.
build
SELECT * FROM [DELETE FROM abcde WHERE a=1]
----
error (42703): statement source "DELETE FROM abcde WHERE a = 1" does not return any columns

# Non-referenced CTE with mutation.
build
WITH cte AS (SELECT y FROM [DELETE FROM xyz WHERE z > 0 RETURNING *]) DELETE FROM abcde WHERE a=b
----
with &1
 ├── delete xyz
 │    ├── columns: xyz.x:1!null xyz.y:2 xyz.z:3!null
 │    ├── fetch columns: xyz.x:6 xyz.y:7 xyz.z:8
 │    ├── return-mapping:
 │    │    ├── xyz.x:6 => xyz.x:1
 │    │    ├── xyz.y:7 => xyz.y:2
 │    │    └── xyz.z:8 => xyz.z:3
 │    └── select
 │         ├── columns: xyz.x:6!null xyz.y:7 xyz.z:8!null xyz.crdb_internal_mvcc_timestamp:9 xyz.tableoid:10
 │         ├── scan xyz
 │         │    └── columns: xyz.x:6!null xyz.y:7 xyz.z:8 xyz.crdb_internal_mvcc_timestamp:9 xyz.tableoid:10
 │         └── filters
 │              └── xyz.z:8 > 0.0
 └── with &2 (cte)
      ├── project
      │    ├── columns: y:12
      │    └── with-scan &1
      │         ├── columns: x:11!null y:12 z:13!null
      │         └── mapping:
      │              ├──  xyz.x:1 => x:11
      │              ├──  xyz.y:2 => y:12
      │              └──  xyz.z:3 => z:13
      └── delete abcde
           ├── columns: <none>
           ├── fetch columns: a:22 b:23 c:24 d:25 e:26 rowid:27
           └── select
                ├── columns: a:22!null b:23!null c:24 d:25 e:26 rowid:27!null abcde.crdb_internal_mvcc_timestamp:28 abcde.tableoid:29
                ├── scan abcde
                │    ├── columns: a:22!null b:23 c:24 d:25 e:26 rowid:27!null abcde.crdb_internal_mvcc_timestamp:28 abcde.tableoid:29
                │    └── computed column expressions
                │         ├── d:25
                │         │    └── (b:23 + c:24) + 1
                │         └── e:26
                │              └── a:22
                └── filters
                     └── a:22 = b:23

# With alias, original table name should be inaccessible.
build
DELETE FROM abcde AS foo WHERE a=abcde.b
----
error (42P01): no data source matches prefix: abcde in this context

# ORDER BY can only be used with LIMIT.
build
DELETE FROM abcde WHERE b=1 ORDER BY c
----
error (42601): DELETE statement requires LIMIT when ORDER BY is used

# ------------------------------------------------------------------------------
# Test RETURNING.
# ------------------------------------------------------------------------------

# Return values from delete.
build
DELETE FROM abcde WHERE a=1 RETURNING *
----
project
 ├── columns: a:1!null b:2 c:3 d:4 e:5
 └── delete abcde
      ├── columns: a:1!null b:2 c:3 d:4 e:5 rowid:6!null
      ├── fetch columns: a:9 b:10 c:11 d:12 e:13 rowid:14
      ├── return-mapping:
      │    ├── a:9 => a:1
      │    ├── b:10 => b:2
      │    ├── c:11 => c:3
      │    ├── d:12 => d:4
      │    ├── e:13 => e:5
      │    └── rowid:14 => rowid:6
      └── select
           ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
           ├── scan abcde
           │    ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
           │    └── computed column expressions
           │         ├── d:12
           │         │    └── (b:10 + c:11) + 1
           │         └── e:13
           │              └── a:9
           └── filters
                └── a:9 = 1

# Return values from aliased table.
build
DELETE FROM abcde AS foo WHERE a=1 RETURNING foo.a+1, foo.b * foo.d
----
project
 ├── columns: "?column?":17!null "?column?":18
 ├── delete abcde [as=foo]
 │    ├── columns: a:1!null b:2 c:3 d:4 e:5 rowid:6!null
 │    ├── fetch columns: a:9 b:10 c:11 d:12 e:13 rowid:14
 │    ├── return-mapping:
 │    │    ├── a:9 => a:1
 │    │    ├── b:10 => b:2
 │    │    ├── c:11 => c:3
 │    │    ├── d:12 => d:4
 │    │    ├── e:13 => e:5
 │    │    └── rowid:14 => rowid:6
 │    └── select
 │         ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
 │         ├── scan abcde [as=foo]
 │         │    ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
 │         │    └── computed column expressions
 │         │         ├── d:12
 │         │         │    └── (b:10 + c:11) + 1
 │         │         └── e:13
 │         │              └── a:9
 │         └── filters
 │              └── a:9 = 1
 └── projections
      ├── a:1 + 1 [as="?column?":17]
      └── b:2 * d:4 [as="?column?":18]

# Use returning DELETE as a FROM expression.
build
SELECT a, d FROM [DELETE FROM abcde WHERE a>0 ORDER BY b LIMIT 10 RETURNING *]
----
with &1
 ├── columns: a:17!null d:20
 ├── project
 │    ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3 abcde.d:4 abcde.e:5
 │    └── delete abcde
 │         ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3 abcde.d:4 abcde.e:5 rowid:6!null
 │         ├── fetch columns: abcde.a:9 abcde.b:10 abcde.c:11 abcde.d:12 abcde.e:13 rowid:14
 │         ├── return-mapping:
 │         │    ├── abcde.a:9 => abcde.a:1
 │         │    ├── abcde.b:10 => abcde.b:2
 │         │    ├── abcde.c:11 => abcde.c:3
 │         │    ├── abcde.d:12 => abcde.d:4
 │         │    ├── abcde.e:13 => abcde.e:5
 │         │    └── rowid:14 => rowid:6
 │         └── limit
 │              ├── columns: abcde.a:9!null abcde.b:10 abcde.c:11 abcde.d:12 abcde.e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
 │              ├── internal-ordering: +10
 │              ├── sort
 │              │    ├── columns: abcde.a:9!null abcde.b:10 abcde.c:11 abcde.d:12 abcde.e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
 │              │    ├── ordering: +10
 │              │    ├── limit hint: 10.00
 │              │    └── select
 │              │         ├── columns: abcde.a:9!null abcde.b:10 abcde.c:11 abcde.d:12 abcde.e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
 │              │         ├── scan abcde
 │              │         │    ├── columns: abcde.a:9!null abcde.b:10 abcde.c:11 abcde.d:12 abcde.e:13 rowid:14!null crdb_internal_mvcc_timestamp:15 tableoid:16
 │              │         │    └── computed column expressions
 │              │         │         ├── abcde.d:12
 │              │         │         │    └── (abcde.b:10 + abcde.c:11) + 1
 │              │         │         └── abcde.e:13
 │              │         │              └── abcde.a:9
 │              │         └── filters
 │              │              └── abcde.a:9 > 0
 │              └── 10
 └── project
      ├── columns: a:17!null d:20
      └── with-scan &1
           ├── columns: a:17!null b:18 c:19 d:20 e:21
           └── mapping:
                ├──  abcde.a:1 => a:17
                ├──  abcde.b:2 => b:18
                ├──  abcde.c:3 => c:19
                ├──  abcde.d:4 => d:20
                └──  abcde.e:5 => e:21

# ------------------------------------------------------------------------------
# Tests with mutations.
# ------------------------------------------------------------------------------

# Without RETURNING clause.
build
DELETE FROM mutation WHERE m=1
----
delete mutation
 ├── columns: <none>
 ├── fetch columns: m:7 n:8 o:9 p:10
 └── select
      ├── columns: m:7!null n:8 o:9 p:10 crdb_internal_mvcc_timestamp:11 tableoid:12
      ├── scan mutation
      │    └── columns: m:7!null n:8 o:9 p:10 crdb_internal_mvcc_timestamp:11 tableoid:12
      └── filters
           └── m:7 = 1

# With RETURNING clause.
build
DELETE FROM mutation WHERE m=1 RETURNING *
----
delete mutation
 ├── columns: m:1!null n:2
 ├── fetch columns: m:7 n:8 o:9 p:10
 ├── return-mapping:
 │    ├── m:7 => m:1
 │    └── n:8 => n:2
 └── select
      ├── columns: m:7!null n:8 o:9 p:10 crdb_internal_mvcc_timestamp:11 tableoid:12
      ├── scan mutation
      │    └── columns: m:7!null n:8 o:9 p:10 crdb_internal_mvcc_timestamp:11 tableoid:12
      └── filters
           └── m:7 = 1


# Try to return a mutation column.
build
DELETE FROM mutation RETURNING o
----
error (42703): column "o" does not exist

# Try to use mutation column in WHERE clause.
build
DELETE FROM mutation WHERE o=10
----
error (42P10): column "o" is being backfilled

# Try to use mutation column in ORDER BY expression.
build
DELETE FROM mutation ORDER BY p LIMIT 2
----
error (42P10): column "p" is being backfilled

# ------------------------------------------------------------------------------
# Test USING.
# ------------------------------------------------------------------------------

# Test a simple join with a filter
build format=show-qual
DELETE FROM u_a USING u_b WHERE c = u_b.a AND u_b.b = 'd'
----
delete t.public.u_a
 ├── columns: <none>
 ├── fetch columns: t.public.u_a.a:6 t.public.u_a.b:7 t.public.u_a.c:8
 ├── passthrough columns t.public.u_b.a:11 t.public.u_b.b:12 t.public.u_b.crdb_internal_mvcc_timestamp:13 t.public.u_b.tableoid:14
 └── select
      ├── columns: t.public.u_a.a:6!null t.public.u_a.b:7 t.public.u_a.c:8!null t.public.u_a.crdb_internal_mvcc_timestamp:9 t.public.u_a.tableoid:10 t.public.u_b.a:11!null t.public.u_b.b:12!null t.public.u_b.crdb_internal_mvcc_timestamp:13 t.public.u_b.tableoid:14
      ├── inner-join (cross)
      │    ├── columns: t.public.u_a.a:6!null t.public.u_a.b:7 t.public.u_a.c:8 t.public.u_a.crdb_internal_mvcc_timestamp:9 t.public.u_a.tableoid:10 t.public.u_b.a:11!null t.public.u_b.b:12 t.public.u_b.crdb_internal_mvcc_timestamp:13 t.public.u_b.tableoid:14
      │    ├── scan t.public.u_a
      │    │    └── columns: t.public.u_a.a:6!null t.public.u_a.b:7 t.public.u_a.c:8 t.public.u_a.crdb_internal_mvcc_timestamp:9 t.public.u_a.tableoid:10
      │    ├── scan t.public.u_b
      │    │    └── columns: t.public.u_b.a:11!null t.public.u_b.b:12 t.public.u_b.crdb_internal_mvcc_timestamp:13 t.public.u_b.tableoid:14
      │    └── filters (true)
      └── filters
           └── (t.public.u_a.c:8 = t.public.u_b.a:11) AND (t.public.u_b.b:12 = 'd')

# Test a self join
build
DELETE FROM u_a USING u_a u_a2 WHERE u_a.a = u_a2.c
----
delete u_a
 ├── columns: <none>
 ├── fetch columns: u_a.a:6 u_a.b:7 u_a.c:8
 ├── passthrough columns u_a2.a:11 u_a2.b:12 u_a2.c:13 u_a2.crdb_internal_mvcc_timestamp:14 u_a2.tableoid:15
 └── select
      ├── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_a2.a:11!null u_a2.b:12 u_a2.c:13!null u_a2.crdb_internal_mvcc_timestamp:14 u_a2.tableoid:15
      ├── inner-join (cross)
      │    ├── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_a2.a:11!null u_a2.b:12 u_a2.c:13 u_a2.crdb_internal_mvcc_timestamp:14 u_a2.tableoid:15
      │    ├── scan u_a
      │    │    └── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10
      │    ├── scan u_a [as=u_a2]
      │    │    └── columns: u_a2.a:11!null u_a2.b:12 u_a2.c:13 u_a2.crdb_internal_mvcc_timestamp:14 u_a2.tableoid:15
      │    └── filters (true)
      └── filters
           └── u_a.a:6 = u_a2.c:13

# Test when USING uses multiple tables
build
DELETE FROM u_a USING u_b, u_c WHERE u_a.c = u_b.a AND u_a.c = u_c.a
----
delete u_a
 ├── columns: <none>
 ├── fetch columns: u_a.a:6 u_a.b:7 u_a.c:8
 ├── passthrough columns u_b.a:11 u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14 u_c.a:15 u_c.b:16 u_c.c:17 u_c.crdb_internal_mvcc_timestamp:18 u_c.tableoid:19
 └── select
      ├── columns: u_a.a:6!null u_a.b:7 u_a.c:8!null u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14 u_c.a:15!null u_c.b:16 u_c.c:17 u_c.crdb_internal_mvcc_timestamp:18 u_c.tableoid:19
      ├── inner-join (cross)
      │    ├── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14 u_c.a:15!null u_c.b:16 u_c.c:17 u_c.crdb_internal_mvcc_timestamp:18 u_c.tableoid:19
      │    ├── scan u_a
      │    │    └── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10
      │    ├── inner-join (cross)
      │    │    ├── columns: u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14 u_c.a:15!null u_c.b:16 u_c.c:17 u_c.crdb_internal_mvcc_timestamp:18 u_c.tableoid:19
      │    │    ├── scan u_b
      │    │    │    └── columns: u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
      │    │    ├── scan u_c
      │    │    │    └── columns: u_c.a:15!null u_c.b:16 u_c.c:17 u_c.crdb_internal_mvcc_timestamp:18 u_c.tableoid:19
      │    │    └── filters (true)
      │    └── filters (true)
      └── filters
           └── (u_a.c:8 = u_b.a:11) AND (u_a.c:8 = u_c.a:15)

# Test if USING works well with RETURNING expressions that reference
# the USING table
build
DELETE FROM
  u_a
USING
  u_c
WHERE
  u_c.a > u_a.c AND u_c.c <= 4
RETURNING
  u_c.a, u_c.b, u_c.c
----
project
 ├── columns: a:11 b:12 c:13
 └── delete u_a
      ├── columns: u_a.a:1!null u_a.b:2 u_a.c:3!null u_c.a:11 u_c.b:12 u_c.c:13 u_c.crdb_internal_mvcc_timestamp:14 u_c.tableoid:15
      ├── fetch columns: u_a.a:6 u_a.b:7 u_a.c:8
      ├── return-mapping:
      │    ├── u_a.a:6 => u_a.a:1
      │    ├── u_a.b:7 => u_a.b:2
      │    └── u_a.c:8 => u_a.c:3
      ├── passthrough columns u_c.a:11 u_c.b:12 u_c.c:13 u_c.crdb_internal_mvcc_timestamp:14 u_c.tableoid:15
      └── select
           ├── columns: u_a.a:6!null u_a.b:7 u_a.c:8!null u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_c.a:11!null u_c.b:12 u_c.c:13!null u_c.crdb_internal_mvcc_timestamp:14 u_c.tableoid:15
           ├── inner-join (cross)
           │    ├── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_c.a:11!null u_c.b:12 u_c.c:13 u_c.crdb_internal_mvcc_timestamp:14 u_c.tableoid:15
           │    ├── scan u_a
           │    │    └── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10
           │    ├── scan u_c
           │    │    └── columns: u_c.a:11!null u_c.b:12 u_c.c:13 u_c.crdb_internal_mvcc_timestamp:14 u_c.tableoid:15
           │    └── filters (true)
           └── filters
                └── (u_c.a:11 > u_a.c:8) AND (u_c.c:13 <= 4)

# Test if RETURNING * returns everything
build
DELETE FROM u_a USING u_b WHERE c = u_b.a AND u_b.b = 'd' RETURNING *
----
project
 ├── columns: a:1!null b:2 c:3!null a:11 b:12
 └── delete u_a
      ├── columns: u_a.a:1!null u_a.b:2 c:3!null u_b.a:11 u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
      ├── fetch columns: u_a.a:6 u_a.b:7 c:8
      ├── return-mapping:
      │    ├── u_a.a:6 => u_a.a:1
      │    ├── u_a.b:7 => u_a.b:2
      │    └── c:8 => c:3
      ├── passthrough columns u_b.a:11 u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
      └── select
           ├── columns: u_a.a:6!null u_a.b:7 c:8!null u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_b.a:11!null u_b.b:12!null u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
           ├── inner-join (cross)
           │    ├── columns: u_a.a:6!null u_a.b:7 c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
           │    ├── scan u_a
           │    │    └── columns: u_a.a:6!null u_a.b:7 c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10
           │    ├── scan u_b
           │    │    └── columns: u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
           │    └── filters (true)
           └── filters
                └── (c:8 = u_b.a:11) AND (u_b.b:12 = 'd')

# Test aliased table names and order by and limit
build
DELETE FROM abcde AS foo USING xyz AS bar WHERE bar.y > 0 ORDER BY bar.y LIMIT 5;
----
delete abcde [as=foo]
 ├── columns: <none>
 ├── fetch columns: a:9 b:10 c:11 d:12 e:13 rowid:14
 ├── passthrough columns x:17 y:18 z:19 bar.crdb_internal_mvcc_timestamp:20 bar.tableoid:21
 └── limit
      ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null foo.crdb_internal_mvcc_timestamp:15 foo.tableoid:16 x:17!null y:18!null z:19 bar.crdb_internal_mvcc_timestamp:20 bar.tableoid:21
      ├── internal-ordering: +18
      ├── sort
      │    ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null foo.crdb_internal_mvcc_timestamp:15 foo.tableoid:16 x:17!null y:18!null z:19 bar.crdb_internal_mvcc_timestamp:20 bar.tableoid:21
      │    ├── ordering: +18
      │    ├── limit hint: 5.00
      │    └── select
      │         ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null foo.crdb_internal_mvcc_timestamp:15 foo.tableoid:16 x:17!null y:18!null z:19 bar.crdb_internal_mvcc_timestamp:20 bar.tableoid:21
      │         ├── inner-join (cross)
      │         │    ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null foo.crdb_internal_mvcc_timestamp:15 foo.tableoid:16 x:17!null y:18 z:19 bar.crdb_internal_mvcc_timestamp:20 bar.tableoid:21
      │         │    ├── scan abcde [as=foo]
      │         │    │    ├── columns: a:9!null b:10 c:11 d:12 e:13 rowid:14!null foo.crdb_internal_mvcc_timestamp:15 foo.tableoid:16
      │         │    │    └── computed column expressions
      │         │    │         ├── d:12
      │         │    │         │    └── (b:10 + c:11) + 1
      │         │    │         └── e:13
      │         │    │              └── a:9
      │         │    ├── scan xyz [as=bar]
      │         │    │    └── columns: x:17!null y:18 z:19 bar.crdb_internal_mvcc_timestamp:20 bar.tableoid:21
      │         │    └── filters (true)
      │         └── filters
      │              └── y:18 > 0
      └── 5

# Test if DELETE FROM ... USING can return hidden columns
build
DELETE FROM
  abcde
USING
  u_e
WHERE
  abcde.a = u_e.a
RETURNING
  u_e.rowid
----
project
 ├── columns: rowid:19
 └── delete abcde
      ├── columns: abcde.a:1!null abcde.b:2 c:3 d:4 e:5 abcde.rowid:6!null u_e.a:17 u_e.b:18 u_e.rowid:19 u_e.crdb_internal_mvcc_timestamp:20 u_e.tableoid:21
      ├── fetch columns: abcde.a:9 abcde.b:10 c:11 d:12 e:13 abcde.rowid:14
      ├── return-mapping:
      │    ├── abcde.a:9 => abcde.a:1
      │    ├── abcde.b:10 => abcde.b:2
      │    ├── c:11 => c:3
      │    ├── d:12 => d:4
      │    ├── e:13 => e:5
      │    └── abcde.rowid:14 => abcde.rowid:6
      ├── passthrough columns u_e.a:17 u_e.b:18 u_e.rowid:19 u_e.crdb_internal_mvcc_timestamp:20 u_e.tableoid:21
      └── select
           ├── columns: abcde.a:9!null abcde.b:10 c:11 d:12 e:13 abcde.rowid:14!null abcde.crdb_internal_mvcc_timestamp:15 abcde.tableoid:16 u_e.a:17!null u_e.b:18 u_e.rowid:19!null u_e.crdb_internal_mvcc_timestamp:20 u_e.tableoid:21
           ├── inner-join (cross)
           │    ├── columns: abcde.a:9!null abcde.b:10 c:11 d:12 e:13 abcde.rowid:14!null abcde.crdb_internal_mvcc_timestamp:15 abcde.tableoid:16 u_e.a:17!null u_e.b:18 u_e.rowid:19!null u_e.crdb_internal_mvcc_timestamp:20 u_e.tableoid:21
           │    ├── scan abcde
           │    │    ├── columns: abcde.a:9!null abcde.b:10 c:11 d:12 e:13 abcde.rowid:14!null abcde.crdb_internal_mvcc_timestamp:15 abcde.tableoid:16
           │    │    └── computed column expressions
           │    │         ├── d:12
           │    │         │    └── (abcde.b:10 + c:11) + 1
           │    │         └── e:13
           │    │              └── abcde.a:9
           │    ├── scan u_e
           │    │    └── columns: u_e.a:17!null u_e.b:18 u_e.rowid:19!null u_e.crdb_internal_mvcc_timestamp:20 u_e.tableoid:21
           │    └── filters (true)
           └── filters
                └── abcde.a:9 = u_e.a:17

# Test if returning returns columns in the target table and USING table
build
DELETE FROM u_a USING u_b WHERE u_a.a = u_b.a RETURNING u_b.a, u_a.a
----
project
 ├── columns: a:11 a:1!null
 └── delete u_a
      ├── columns: u_a.a:1!null u_a.b:2 c:3 u_b.a:11 u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
      ├── fetch columns: u_a.a:6 u_a.b:7 c:8
      ├── return-mapping:
      │    ├── u_a.a:6 => u_a.a:1
      │    ├── u_a.b:7 => u_a.b:2
      │    └── c:8 => c:3
      ├── passthrough columns u_b.a:11 u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
      └── select
           ├── columns: u_a.a:6!null u_a.b:7 c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
           ├── inner-join (cross)
           │    ├── columns: u_a.a:6!null u_a.b:7 c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
           │    ├── scan u_a
           │    │    └── columns: u_a.a:6!null u_a.b:7 c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10
           │    ├── scan u_b
           │    │    └── columns: u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
           │    └── filters (true)
           └── filters
                └── u_a.a:6 = u_b.a:11

# Test if DELETE FROM ... USING works with LATERAL
build
DELETE FROM u_a USING u_b, LATERAL (SELECT a FROM u_c WHERE u_b.a > u_c.a) AS other WHERE other.a > 6
----
delete u_a
 ├── columns: <none>
 ├── fetch columns: u_a.a:6 u_a.b:7 u_a.c:8
 ├── passthrough columns u_b.a:11 u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14 u_c.a:15
 └── select
      ├── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14 u_c.a:15!null
      ├── inner-join (cross)
      │    ├── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10 u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14 u_c.a:15!null
      │    ├── scan u_a
      │    │    └── columns: u_a.a:6!null u_a.b:7 u_a.c:8 u_a.crdb_internal_mvcc_timestamp:9 u_a.tableoid:10
      │    ├── inner-join-apply
      │    │    ├── columns: u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14 u_c.a:15!null
      │    │    ├── scan u_b
      │    │    │    └── columns: u_b.a:11!null u_b.b:12 u_b.crdb_internal_mvcc_timestamp:13 u_b.tableoid:14
      │    │    ├── project
      │    │    │    ├── columns: u_c.a:15!null
      │    │    │    └── select
      │    │    │         ├── columns: u_c.a:15!null u_c.b:16 u_c.c:17 u_c.crdb_internal_mvcc_timestamp:18 u_c.tableoid:19
      │    │    │         ├── scan u_c
      │    │    │         │    └── columns: u_c.a:15!null u_c.b:16 u_c.c:17 u_c.crdb_internal_mvcc_timestamp:18 u_c.tableoid:19
      │    │    │         └── filters
      │    │    │              └── u_b.a:11 > u_c.a:15
      │    │    └── filters (true)
      │    └── filters (true)
      └── filters
           └── u_c.a:15 > 6

# Test if DELETE FROM ... USING works with partial indexes

exec-ddl
CREATE TABLE pindex (
  a DECIMAL(10, 2),
  CHECK (a > 0)
)
----

build
DELETE FROM pindex p USING (VALUES (1.0)) v(b) WHERE p.a = v.b RETURNING p.a, v.b
----
project
 ├── columns: a:1!null b:9
 └── delete pindex [as=p]
      ├── columns: a:1!null rowid:2!null column1:9
      ├── fetch columns: a:5 rowid:6
      ├── return-mapping:
      │    ├── a:5 => a:1
      │    └── rowid:6 => rowid:2
      ├── passthrough columns column1:9
      └── select
           ├── columns: a:5!null rowid:6!null crdb_internal_mvcc_timestamp:7 tableoid:8 column1:9!null
           ├── inner-join (cross)
           │    ├── columns: a:5 rowid:6!null crdb_internal_mvcc_timestamp:7 tableoid:8 column1:9!null
           │    ├── scan pindex [as=p]
           │    │    └── columns: a:5 rowid:6!null crdb_internal_mvcc_timestamp:7 tableoid:8
           │    ├── values
           │    │    ├── columns: column1:9!null
           │    │    └── (1.0,)
           │    └── filters (true)
           └── filters
                └── a:5 = column1:9