github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats/upsert (about) 1 exec-ddl 2 CREATE TABLE abc ( 3 a INT NOT NULL, 4 b TEXT DEFAULT ('foo'), 5 c FLOAT AS (a::float) STORED 6 ) 7 ---- 8 9 exec-ddl 10 ALTER TABLE abc INJECT STATISTICS '[ 11 { 12 "columns": ["a"], 13 "created_at": "2018-01-01 1:00:00.00000+00:00", 14 "row_count": 2000, 15 "distinct_count": 2000 16 }, 17 { 18 "columns": ["b"], 19 "created_at": "2018-01-01 1:30:00.00000+00:00", 20 "row_count": 2000, 21 "distinct_count": 10 22 } 23 ]' 24 ---- 25 26 exec-ddl 27 CREATE TABLE xyz ( 28 x TEXT PRIMARY KEY, 29 y INT8 NOT NULL, 30 z FLOAT8 31 ) 32 ---- 33 34 # Table with unique secondary index over nullable column. 35 exec-ddl 36 CREATE TABLE uv ( 37 u INT PRIMARY KEY DEFAULT unique_rowid(), 38 v INT, 39 UNIQUE (v) 40 ) 41 ---- 42 43 # Table with multi-column key. 44 exec-ddl 45 CREATE TABLE mno ( 46 m INT PRIMARY KEY, 47 n INT, 48 o INT, 49 UNIQUE (n, o) 50 ) 51 ---- 52 53 exec-ddl 54 ALTER TABLE mno INJECT STATISTICS '[ 55 { 56 "columns": ["m"], 57 "created_at": "2018-01-01 1:00:00.00000+00:00", 58 "row_count": 2000, 59 "distinct_count": 100 60 }, 61 { 62 "columns": ["n"], 63 "created_at": "2018-01-01 1:00:00.00000+00:00", 64 "row_count": 2000, 65 "distinct_count": 100, 66 "null_count": 10 67 }, 68 { 69 "columns": ["o"], 70 "created_at": "2018-01-01 1:00:00.00000+00:00", 71 "row_count": 2000, 72 "distinct_count": 1900, 73 "null_count": 100 74 } 75 ]' 76 ---- 77 78 # Statistics should be derived from input columns and transferred to RETURNING 79 # columns. 80 build 81 SELECT * 82 FROM 83 [ 84 INSERT INTO xyz (x, y) 85 SELECT b, a FROM abc WHERE c=1.0 86 ON CONFLICT (x) DO UPDATE SET y=5 87 RETURNING * 88 ] 89 WHERE y=10 90 ---- 91 with &1 92 ├── columns: x:16(string!null) y:17(int!null) z:18(float) 93 ├── volatile, side-effects, mutations 94 ├── stats: [rows=9.94974874, distinct(17)=0.994974874, null(17)=0] 95 ├── fd: ()-->(17) 96 ├── upsert xyz 97 │ ├── columns: xyz.x:1(string!null) xyz.y:2(int!null) xyz.z:3(float) 98 │ ├── canary column: 9 99 │ ├── fetch columns: xyz.x:9(string) xyz.y:10(int) xyz.z:11(float) 100 │ ├── insert-mapping: 101 │ │ ├── b:5 => xyz.x:1 102 │ │ ├── a:4 => xyz.y:2 103 │ │ └── column8:8 => xyz.z:3 104 │ ├── update-mapping: 105 │ │ └── upsert_y:14 => xyz.y:2 106 │ ├── return-mapping: 107 │ │ ├── upsert_x:13 => xyz.x:1 108 │ │ ├── upsert_y:14 => xyz.y:2 109 │ │ └── upsert_z:15 => xyz.z:3 110 │ ├── volatile, side-effects, mutations 111 │ ├── stats: [rows=9.94974874] 112 │ └── project 113 │ ├── columns: upsert_x:13(string) upsert_y:14(int!null) upsert_z:15(float) a:4(int!null) b:5(string) column8:8(float) xyz.x:9(string) xyz.y:10(int) xyz.z:11(float) y_new:12(int!null) 114 │ ├── stats: [rows=9.94974874] 115 │ ├── lax-key: (5,9) 116 │ ├── fd: ()-->(8,12), (5)~~>(4), (9)-->(10,11), (5,9)-->(13), (4,9)-->(14), (5,9)~~>(4,14,15) 117 │ ├── project 118 │ │ ├── columns: y_new:12(int!null) a:4(int!null) b:5(string) column8:8(float) xyz.x:9(string) xyz.y:10(int) xyz.z:11(float) 119 │ │ ├── stats: [rows=9.94974874] 120 │ │ ├── lax-key: (5,9) 121 │ │ ├── fd: ()-->(8,12), (5)~~>(4), (9)-->(10,11) 122 │ │ ├── left-join (hash) 123 │ │ │ ├── columns: a:4(int!null) b:5(string) column8:8(float) xyz.x:9(string) xyz.y:10(int) xyz.z:11(float) 124 │ │ │ ├── stats: [rows=9.94974874, distinct(9)=9.94974874, null(9)=0] 125 │ │ │ ├── lax-key: (5,9) 126 │ │ │ ├── fd: ()-->(8), (5)~~>(4), (9)-->(10,11) 127 │ │ │ ├── ensure-upsert-distinct-on 128 │ │ │ │ ├── columns: a:4(int!null) b:5(string) column8:8(float) 129 │ │ │ │ ├── grouping columns: b:5(string) 130 │ │ │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 131 │ │ │ │ ├── stats: [rows=9.94974874, distinct(4)=6.31184239, null(4)=0, distinct(5)=9.94974874, null(5)=0] 132 │ │ │ │ ├── lax-key: (5) 133 │ │ │ │ ├── fd: ()-->(8), (5)~~>(4,8) 134 │ │ │ │ ├── project 135 │ │ │ │ │ ├── columns: column8:8(float) a:4(int!null) b:5(string) 136 │ │ │ │ │ ├── stats: [rows=9.94974874, distinct(5)=6.31184239, null(5)=0] 137 │ │ │ │ │ ├── fd: ()-->(8) 138 │ │ │ │ │ ├── project 139 │ │ │ │ │ │ ├── columns: a:4(int!null) b:5(string) 140 │ │ │ │ │ │ ├── stats: [rows=9.94974874, distinct(5)=6.31184239, null(5)=0] 141 │ │ │ │ │ │ └── select 142 │ │ │ │ │ │ ├── columns: a:4(int!null) b:5(string) c:6(float!null) rowid:7(int!null) 143 │ │ │ │ │ │ ├── stats: [rows=9.94974874, distinct(5)=6.31184239, null(5)=0, distinct(6)=1, null(6)=0] 144 │ │ │ │ │ │ ├── key: (7) 145 │ │ │ │ │ │ ├── fd: ()-->(6), (7)-->(4,5) 146 │ │ │ │ │ │ ├── scan abc 147 │ │ │ │ │ │ │ ├── columns: a:4(int!null) b:5(string) c:6(float) rowid:7(int!null) 148 │ │ │ │ │ │ │ ├── computed column expressions 149 │ │ │ │ │ │ │ │ └── c:6 150 │ │ │ │ │ │ │ │ └── a:4::FLOAT8 [type=float] 151 │ │ │ │ │ │ │ ├── stats: [rows=2000, distinct(4)=2000, null(4)=0, distinct(5)=10, null(5)=0, distinct(6)=200, null(6)=20, distinct(7)=2000, null(7)=0] 152 │ │ │ │ │ │ │ ├── key: (7) 153 │ │ │ │ │ │ │ └── fd: (7)-->(4-6) 154 │ │ │ │ │ │ └── filters 155 │ │ │ │ │ │ └── c:6 = 1.0 [type=bool, outer=(6), constraints=(/6: [/1.0 - /1.0]; tight), fd=()-->(6)] 156 │ │ │ │ │ └── projections 157 │ │ │ │ │ └── NULL::FLOAT8 [as=column8:8, type=float] 158 │ │ │ │ └── aggregations 159 │ │ │ │ ├── first-agg [as=a:4, type=int, outer=(4)] 160 │ │ │ │ │ └── a:4 [type=int] 161 │ │ │ │ └── first-agg [as=column8:8, type=float, outer=(8)] 162 │ │ │ │ └── column8:8 [type=float] 163 │ │ │ ├── scan xyz 164 │ │ │ │ ├── columns: xyz.x:9(string!null) xyz.y:10(int!null) xyz.z:11(float) 165 │ │ │ │ ├── stats: [rows=1000, distinct(9)=1000, null(9)=0] 166 │ │ │ │ ├── key: (9) 167 │ │ │ │ └── fd: (9)-->(10,11) 168 │ │ │ └── filters 169 │ │ │ └── b:5 = xyz.x:9 [type=bool, outer=(5,9), constraints=(/5: (/NULL - ]; /9: (/NULL - ]), fd=(5)==(9), (9)==(5)] 170 │ │ └── projections 171 │ │ └── 5 [as=y_new:12, type=int] 172 │ └── projections 173 │ ├── CASE WHEN xyz.x:9 IS NULL THEN b:5 ELSE xyz.x:9 END [as=upsert_x:13, type=string, outer=(5,9)] 174 │ ├── CASE WHEN xyz.x:9 IS NULL THEN a:4 ELSE y_new:12 END [as=upsert_y:14, type=int, outer=(4,9,12)] 175 │ └── CASE WHEN xyz.x:9 IS NULL THEN column8:8 ELSE xyz.z:11 END [as=upsert_z:15, type=float, outer=(8,9,11)] 176 └── select 177 ├── columns: x:16(string!null) y:17(int!null) z:18(float) 178 ├── stats: [rows=9.94974874, distinct(17)=0.994974874, null(17)=0] 179 ├── fd: ()-->(17) 180 ├── with-scan &1 181 │ ├── columns: x:16(string!null) y:17(int!null) z:18(float) 182 │ ├── mapping: 183 │ │ ├── xyz.x:1(string) => x:16(string) 184 │ │ ├── xyz.y:2(int) => y:17(int) 185 │ │ └── xyz.z:3(float) => z:18(float) 186 │ └── stats: [rows=9.94974874, distinct(16)=0.994974874, null(16)=0, distinct(17)=0.994974874, null(17)=0] 187 └── filters 188 └── y:17 = 10 [type=bool, outer=(17), constraints=(/17: [/10 - /10]; tight), fd=()-->(17)] 189 190 # Cardinality is zero. 191 build 192 UPSERT INTO xyz SELECT b, a FROM abc WHERE False RETURNING * 193 ---- 194 upsert xyz 195 ├── columns: x:1(string!null) y:2(int!null) z:3(float) 196 ├── upsert-mapping: 197 │ ├── b:5 => x:1 198 │ ├── a:4 => y:2 199 │ └── column8:8 => z:3 200 ├── cardinality: [0 - 0] 201 ├── volatile, side-effects, mutations 202 ├── stats: [rows=0] 203 ├── fd: ()-->(3) 204 └── project 205 ├── columns: column8:8(float) a:4(int!null) b:5(string) 206 ├── cardinality: [0 - 0] 207 ├── stats: [rows=0] 208 ├── fd: ()-->(8) 209 ├── project 210 │ ├── columns: a:4(int!null) b:5(string) 211 │ ├── cardinality: [0 - 0] 212 │ ├── stats: [rows=0] 213 │ └── select 214 │ ├── columns: a:4(int!null) b:5(string) c:6(float) rowid:7(int!null) 215 │ ├── cardinality: [0 - 0] 216 │ ├── stats: [rows=0] 217 │ ├── key: (7) 218 │ ├── fd: (7)-->(4-6) 219 │ ├── scan abc 220 │ │ ├── columns: a:4(int!null) b:5(string) c:6(float) rowid:7(int!null) 221 │ │ ├── computed column expressions 222 │ │ │ └── c:6 223 │ │ │ └── a:4::FLOAT8 [type=float] 224 │ │ ├── stats: [rows=2000] 225 │ │ ├── key: (7) 226 │ │ └── fd: (7)-->(4-6) 227 │ └── filters 228 │ └── false [type=bool] 229 └── projections 230 └── NULL::FLOAT8 [as=column8:8, type=float] 231 232 # Nullable conflict column. Ensure that ensure-upsert-distinct-on passes through 233 # the input's null count. 234 build 235 INSERT INTO uv (v) 236 SELECT z::int FROM xyz 237 ON CONFLICT (v) DO UPDATE SET v=1 238 ---- 239 upsert uv 240 ├── columns: <none> 241 ├── canary column: 8 242 ├── fetch columns: u:8(int) v:9(int) 243 ├── insert-mapping: 244 │ ├── column7:7 => u:1 245 │ └── z:6 => v:2 246 ├── update-mapping: 247 │ └── upsert_v:12 => v:2 248 ├── cardinality: [0 - 0] 249 ├── volatile, side-effects, mutations 250 ├── stats: [rows=0] 251 └── project 252 ├── columns: upsert_u:11(int) upsert_v:12(int) z:6(int) column7:7(int) u:8(int) v:9(int) v_new:10(int!null) 253 ├── volatile, side-effects 254 ├── stats: [rows=1000] 255 ├── lax-key: (6,8) 256 ├── fd: ()-->(10), (6)~~>(7), (8)-->(9), (9)~~>(8), (7,8)-->(11), (6,8)-->(12), (6,8)~~>(7,11) 257 ├── project 258 │ ├── columns: v_new:10(int!null) z:6(int) column7:7(int) u:8(int) v:9(int) 259 │ ├── volatile, side-effects 260 │ ├── stats: [rows=1000] 261 │ ├── lax-key: (6,8) 262 │ ├── fd: ()-->(10), (6)~~>(7), (8)-->(9), (9)~~>(8) 263 │ ├── left-join (hash) 264 │ │ ├── columns: z:6(int) column7:7(int) u:8(int) v:9(int) 265 │ │ ├── volatile, side-effects 266 │ │ ├── stats: [rows=1000, distinct(9)=991, null(9)=0] 267 │ │ ├── lax-key: (6,8) 268 │ │ ├── fd: (6)~~>(7), (8)-->(9), (9)~~>(8) 269 │ │ ├── ensure-upsert-distinct-on 270 │ │ │ ├── columns: z:6(int) column7:7(int) 271 │ │ │ ├── grouping columns: z:6(int) 272 │ │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 273 │ │ │ ├── volatile, side-effects 274 │ │ │ ├── stats: [rows=1000, distinct(6)=1000, null(6)=0] 275 │ │ │ ├── lax-key: (6) 276 │ │ │ ├── fd: (6)~~>(7) 277 │ │ │ ├── project 278 │ │ │ │ ├── columns: column7:7(int) z:6(int) 279 │ │ │ │ ├── volatile, side-effects 280 │ │ │ │ ├── stats: [rows=1000, distinct(6)=100, null(6)=0] 281 │ │ │ │ ├── project 282 │ │ │ │ │ ├── columns: z:6(int) 283 │ │ │ │ │ ├── stats: [rows=1000, distinct(6)=100, null(6)=0] 284 │ │ │ │ │ ├── scan xyz 285 │ │ │ │ │ │ ├── columns: x:3(string!null) y:4(int!null) xyz.z:5(float) 286 │ │ │ │ │ │ ├── stats: [rows=1000, distinct(5)=100, null(5)=10] 287 │ │ │ │ │ │ ├── key: (3) 288 │ │ │ │ │ │ └── fd: (3)-->(4,5) 289 │ │ │ │ │ └── projections 290 │ │ │ │ │ └── xyz.z:5::INT8 [as=z:6, type=int, outer=(5)] 291 │ │ │ │ └── projections 292 │ │ │ │ └── unique_rowid() [as=column7:7, type=int, volatile, side-effects] 293 │ │ │ └── aggregations 294 │ │ │ └── first-agg [as=column7:7, type=int, outer=(7)] 295 │ │ │ └── column7:7 [type=int] 296 │ │ ├── scan uv 297 │ │ │ ├── columns: u:8(int!null) v:9(int) 298 │ │ │ ├── stats: [rows=1000, distinct(9)=991, null(9)=10] 299 │ │ │ ├── key: (8) 300 │ │ │ └── fd: (8)-->(9), (9)~~>(8) 301 │ │ └── filters 302 │ │ └── z:6 = v:9 [type=bool, outer=(6,9), constraints=(/6: (/NULL - ]; /9: (/NULL - ]), fd=(6)==(9), (9)==(6)] 303 │ └── projections 304 │ └── 1 [as=v_new:10, type=int] 305 └── projections 306 ├── CASE WHEN u:8 IS NULL THEN column7:7 ELSE u:8 END [as=upsert_u:11, type=int, outer=(7,8)] 307 └── CASE WHEN u:8 IS NULL THEN z:6 ELSE v_new:10 END [as=upsert_v:12, type=int, outer=(6,8,10)] 308 309 # Multiple conflict columns. 310 # TODO(andyk): The null counts for the left join are surprisingly high. It's due 311 # to the stats code deciding that the left join will only return a tiny number 312 # of matches, which then implies all non-matches are NULL (due to null extending 313 # behavior of left join). This will get better once we improve multi-column 314 # stats. 315 build 316 INSERT INTO mno 317 SELECT * FROM mno 318 ON CONFLICT (n, o) DO UPDATE SET o = 5 319 ---- 320 upsert mno 321 ├── columns: <none> 322 ├── canary column: 7 323 ├── fetch columns: m:7(int) n:8(int) o:9(int) 324 ├── insert-mapping: 325 │ ├── m:4 => m:1 326 │ ├── n:5 => n:2 327 │ └── o:6 => o:3 328 ├── update-mapping: 329 │ └── upsert_o:13 => o:3 330 ├── cardinality: [0 - 0] 331 ├── volatile, side-effects, mutations 332 ├── stats: [rows=0] 333 └── project 334 ├── columns: upsert_m:11(int) upsert_n:12(int) upsert_o:13(int) m:4(int!null) n:5(int) o:6(int) m:7(int) n:8(int) o:9(int) o_new:10(int!null) 335 ├── stats: [rows=2000] 336 ├── key: (4,7) 337 ├── fd: ()-->(10), (4)-->(5,6), (5,6)~~>(4), (7)-->(8,9), (8,9)~~>(7), (4,7)-->(11), (5,7,8)-->(12), (6,7)-->(13) 338 ├── project 339 │ ├── columns: o_new:10(int!null) m:4(int!null) n:5(int) o:6(int) m:7(int) n:8(int) o:9(int) 340 │ ├── stats: [rows=2000] 341 │ ├── key: (4,7) 342 │ ├── fd: ()-->(10), (4)-->(5,6), (5,6)~~>(4), (7)-->(8,9), (8,9)~~>(7) 343 │ ├── left-join (hash) 344 │ │ ├── columns: m:4(int!null) n:5(int) o:6(int) m:7(int) n:8(int) o:9(int) 345 │ │ ├── stats: [rows=2000, distinct(8)=21.0526316, null(8)=1988.94737, distinct(9)=21.0526316, null(9)=2000] 346 │ │ ├── key: (4,7) 347 │ │ ├── fd: (4)-->(5,6), (5,6)~~>(4), (7)-->(8,9), (8,9)~~>(7) 348 │ │ ├── ensure-upsert-distinct-on 349 │ │ │ ├── columns: m:4(int!null) n:5(int) o:6(int) 350 │ │ │ ├── grouping columns: n:5(int) o:6(int) 351 │ │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 352 │ │ │ ├── stats: [rows=2000, distinct(4)=1981, null(4)=0, distinct(5)=100, null(5)=10, distinct(6)=1900, null(6)=100] 353 │ │ │ ├── key: (4) 354 │ │ │ ├── fd: (4)-->(5,6), (5,6)~~>(4) 355 │ │ │ ├── scan mno 356 │ │ │ │ ├── columns: m:4(int!null) n:5(int) o:6(int) 357 │ │ │ │ ├── stats: [rows=2000, distinct(5)=100, null(5)=10, distinct(6)=1900, null(6)=100, distinct(5,6)=1981, null(5,6)=20] 358 │ │ │ │ ├── key: (4) 359 │ │ │ │ └── fd: (4)-->(5,6), (5,6)~~>(4) 360 │ │ │ └── aggregations 361 │ │ │ └── first-agg [as=m:4, type=int, outer=(4)] 362 │ │ │ └── m:4 [type=int] 363 │ │ ├── scan mno 364 │ │ │ ├── columns: m:7(int!null) n:8(int) o:9(int) 365 │ │ │ ├── stats: [rows=2000, distinct(8)=100, null(8)=10, distinct(9)=1900, null(9)=100] 366 │ │ │ ├── key: (7) 367 │ │ │ └── fd: (7)-->(8,9), (8,9)~~>(7) 368 │ │ └── filters 369 │ │ ├── n:5 = n:8 [type=bool, outer=(5,8), constraints=(/5: (/NULL - ]; /8: (/NULL - ]), fd=(5)==(8), (8)==(5)] 370 │ │ └── o:6 = o:9 [type=bool, outer=(6,9), constraints=(/6: (/NULL - ]; /9: (/NULL - ]), fd=(6)==(9), (9)==(6)] 371 │ └── projections 372 │ └── 5 [as=o_new:10, type=int] 373 └── projections 374 ├── CASE WHEN m:7 IS NULL THEN m:4 ELSE m:7 END [as=upsert_m:11, type=int, outer=(4,7)] 375 ├── CASE WHEN m:7 IS NULL THEN n:5 ELSE n:8 END [as=upsert_n:12, type=int, outer=(5,7,8)] 376 └── CASE WHEN m:7 IS NULL THEN o:6 ELSE o_new:10 END [as=upsert_o:13, type=int, outer=(6,7,10)]