github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/delete (about) 1 exec-ddl 2 CREATE TABLE abcde ( 3 a INT NOT NULL, 4 b INT, 5 c INT NOT NULL DEFAULT (10), 6 d INT AS (b + c + 1) STORED, 7 "e:write-only" INT NOT NULL 8 ) 9 ---- 10 11 exec-ddl 12 CREATE TABLE xyz ( 13 x TEXT PRIMARY KEY, 14 y INT8 NOT NULL, 15 z FLOAT8 16 ) 17 ---- 18 19 # Properties with no RETURNING clause. 20 build 21 DELETE FROM abcde WHERE a=1 22 ---- 23 delete abcde 24 ├── columns: <none> 25 ├── fetch columns: a:7(int) b:8(int) c:9(int) d:10(int) rowid:11(int) e:12(int) 26 ├── cardinality: [0 - 0] 27 ├── volatile, side-effects, mutations 28 └── select 29 ├── columns: a:7(int!null) b:8(int) c:9(int!null) d:10(int) rowid:11(int!null) e:12(int) 30 ├── key: (11) 31 ├── fd: ()-->(7), (11)-->(8-10,12) 32 ├── prune: (8-12) 33 ├── interesting orderings: (+11) 34 ├── scan abcde 35 │ ├── columns: a:7(int!null) b:8(int) c:9(int!null) d:10(int) rowid:11(int!null) e:12(int) 36 │ ├── computed column expressions 37 │ │ └── d:10 38 │ │ └── plus [type=int] 39 │ │ ├── plus [type=int] 40 │ │ │ ├── variable: b:8 [type=int] 41 │ │ │ └── variable: c:9 [type=int] 42 │ │ └── const: 1 [type=int] 43 │ ├── key: (11) 44 │ ├── fd: (11)-->(7-10,12) 45 │ ├── prune: (7-12) 46 │ └── interesting orderings: (+11) 47 └── filters 48 └── eq [type=bool, outer=(7), constraints=(/7: [/1 - /1]; tight), fd=()-->(7)] 49 ├── variable: a:7 [type=int] 50 └── const: 1 [type=int] 51 52 # Properties with RETURNING clause. 53 build 54 DELETE FROM abcde WHERE a=1 RETURNING * 55 ---- 56 project 57 ├── columns: a:1(int!null) b:2(int) c:3(int!null) d:4(int) 58 ├── volatile, side-effects, mutations 59 ├── fd: ()-->(1) 60 ├── prune: (1-4) 61 └── delete abcde 62 ├── columns: a:1(int!null) b:2(int) c:3(int!null) d:4(int) rowid:5(int!null) 63 ├── fetch columns: a:7(int) b:8(int) c:9(int) d:10(int) rowid:11(int) e:12(int) 64 ├── volatile, side-effects, mutations 65 ├── key: (5) 66 ├── fd: ()-->(1), (5)-->(2-4) 67 ├── prune: (1-4) 68 └── select 69 ├── columns: a:7(int!null) b:8(int) c:9(int!null) d:10(int) rowid:11(int!null) e:12(int) 70 ├── key: (11) 71 ├── fd: ()-->(7), (11)-->(8-10,12) 72 ├── prune: (8-12) 73 ├── interesting orderings: (+11) 74 ├── scan abcde 75 │ ├── columns: a:7(int!null) b:8(int) c:9(int!null) d:10(int) rowid:11(int!null) e:12(int) 76 │ ├── computed column expressions 77 │ │ └── d:10 78 │ │ └── plus [type=int] 79 │ │ ├── plus [type=int] 80 │ │ │ ├── variable: b:8 [type=int] 81 │ │ │ └── variable: c:9 [type=int] 82 │ │ └── const: 1 [type=int] 83 │ ├── key: (11) 84 │ ├── fd: (11)-->(7-10,12) 85 │ ├── prune: (7-12) 86 │ └── interesting orderings: (+11) 87 └── filters 88 └── eq [type=bool, outer=(7), constraints=(/7: [/1 - /1]; tight), fd=()-->(7)] 89 ├── variable: a:7 [type=int] 90 └── const: 1 [type=int] 91 92 # Input is cardinality 0-1 expression. 93 build 94 DELETE FROM abcde WHERE rowid=1 RETURNING * 95 ---- 96 project 97 ├── columns: a:1(int!null) b:2(int) c:3(int!null) d:4(int) 98 ├── cardinality: [0 - 1] 99 ├── volatile, side-effects, mutations 100 ├── key: () 101 ├── fd: ()-->(1-4) 102 ├── prune: (1-4) 103 └── delete abcde 104 ├── columns: a:1(int!null) b:2(int) c:3(int!null) d:4(int) rowid:5(int!null) 105 ├── fetch columns: a:7(int) b:8(int) c:9(int) d:10(int) rowid:11(int) e:12(int) 106 ├── cardinality: [0 - 1] 107 ├── volatile, side-effects, mutations 108 ├── key: () 109 ├── fd: ()-->(1-5) 110 ├── prune: (1-4) 111 └── select 112 ├── columns: a:7(int!null) b:8(int) c:9(int!null) d:10(int) rowid:11(int!null) e:12(int) 113 ├── cardinality: [0 - 1] 114 ├── key: () 115 ├── fd: ()-->(7-12) 116 ├── prune: (7-10,12) 117 ├── interesting orderings: (+11) 118 ├── scan abcde 119 │ ├── columns: a:7(int!null) b:8(int) c:9(int!null) d:10(int) rowid:11(int!null) e:12(int) 120 │ ├── computed column expressions 121 │ │ └── d:10 122 │ │ └── plus [type=int] 123 │ │ ├── plus [type=int] 124 │ │ │ ├── variable: b:8 [type=int] 125 │ │ │ └── variable: c:9 [type=int] 126 │ │ └── const: 1 [type=int] 127 │ ├── key: (11) 128 │ ├── fd: (11)-->(7-10,12) 129 │ ├── prune: (7-12) 130 │ └── interesting orderings: (+11) 131 └── filters 132 └── eq [type=bool, outer=(11), constraints=(/11: [/1 - /1]; tight), fd=()-->(11)] 133 ├── variable: rowid:11 [type=int] 134 └── const: 1 [type=int] 135 136 # Filter FD set. 137 build 138 DELETE FROM abcde WHERE b=c RETURNING *; 139 ---- 140 project 141 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int) 142 ├── volatile, side-effects, mutations 143 ├── fd: (2)==(3), (3)==(2) 144 ├── prune: (1-4) 145 └── delete abcde 146 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int) rowid:5(int!null) 147 ├── fetch columns: a:7(int) b:8(int) c:9(int) d:10(int) rowid:11(int) e:12(int) 148 ├── volatile, side-effects, mutations 149 ├── key: (5) 150 ├── fd: (2)==(3), (3)==(2), (5)-->(1-4) 151 ├── prune: (1-4) 152 └── select 153 ├── columns: a:7(int!null) b:8(int!null) c:9(int!null) d:10(int) rowid:11(int!null) e:12(int) 154 ├── key: (11) 155 ├── fd: (11)-->(7-10,12), (8)==(9), (9)==(8) 156 ├── prune: (7,10-12) 157 ├── interesting orderings: (+11) 158 ├── scan abcde 159 │ ├── columns: a:7(int!null) b:8(int) c:9(int!null) d:10(int) rowid:11(int!null) e:12(int) 160 │ ├── computed column expressions 161 │ │ └── d:10 162 │ │ └── plus [type=int] 163 │ │ ├── plus [type=int] 164 │ │ │ ├── variable: b:8 [type=int] 165 │ │ │ └── variable: c:9 [type=int] 166 │ │ └── const: 1 [type=int] 167 │ ├── key: (11) 168 │ ├── fd: (11)-->(7-10,12) 169 │ ├── prune: (7-12) 170 │ └── interesting orderings: (+11) 171 └── filters 172 └── eq [type=bool, outer=(8,9), constraints=(/8: (/NULL - ]; /9: (/NULL - ]), fd=(8)==(9), (9)==(8)] 173 ├── variable: b:8 [type=int] 174 └── variable: c:9 [type=int]