github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/external/tpcc-no-stats (about) 1 import file=tpcc_schema 2 ---- 3 4 # -------------------------------------------------- 5 # 2.4 The New Order Transaction 6 # 7 # The New-Order business transaction consists of entering a complete order 8 # through a single database transaction. It represents a mid-weight, read-write 9 # transaction with a high frequency of execution and stringent response time 10 # requirements to satisfy on-line users. This transaction is the backbone of 11 # the workload. It is designed to place a variable load on the system to 12 # reflect on-line database activity as typically found in production 13 # environments. 14 # -------------------------------------------------- 15 opt format=hide-qual 16 UPDATE district 17 SET d_next_o_id = d_next_o_id + 1 18 WHERE d_w_id = 10 AND d_id = 5 19 RETURNING d_tax, d_next_o_id 20 ---- 21 project 22 ├── columns: d_tax:9 d_next_o_id:11 23 ├── cardinality: [0 - 1] 24 ├── volatile, side-effects, mutations 25 ├── key: () 26 ├── fd: ()-->(9,11) 27 └── update district 28 ├── columns: d_id:1!null d_w_id:2!null d_tax:9 d_next_o_id:11 29 ├── fetch columns: d_id:12 d_w_id:13 d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 d_ytd:21 d_next_o_id:22 30 ├── update-mapping: 31 │ └── d_next_o_id_new:23 => d_next_o_id:11 32 ├── cardinality: [0 - 1] 33 ├── volatile, side-effects, mutations 34 ├── key: () 35 ├── fd: ()-->(1,2,9,11) 36 └── project 37 ├── columns: d_next_o_id_new:23 d_id:12!null d_w_id:13!null d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 d_ytd:21 d_next_o_id:22 38 ├── cardinality: [0 - 1] 39 ├── key: () 40 ├── fd: ()-->(12-23) 41 ├── scan district 42 │ ├── columns: d_id:12!null d_w_id:13!null d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 d_ytd:21 d_next_o_id:22 43 │ ├── constraint: /13/12: [/10/5 - /10/5] 44 │ ├── cardinality: [0 - 1] 45 │ ├── key: () 46 │ └── fd: ()-->(12-22) 47 └── projections 48 └── d_next_o_id:22 + 1 [as=d_next_o_id_new:23, outer=(22)] 49 50 opt format=hide-qual 51 SELECT w_tax FROM warehouse WHERE w_id = 10 52 ---- 53 project 54 ├── columns: w_tax:8 55 ├── cardinality: [0 - 1] 56 ├── key: () 57 ├── fd: ()-->(8) 58 └── scan warehouse 59 ├── columns: w_id:1!null w_tax:8 60 ├── constraint: /1: [/10 - /10] 61 ├── cardinality: [0 - 1] 62 ├── key: () 63 └── fd: ()-->(1,8) 64 65 opt format=hide-qual 66 SELECT c_discount, c_last, c_credit 67 FROM customer 68 WHERE c_w_id = 10 AND c_d_id = 100 AND c_id = 50 69 ---- 70 project 71 ├── columns: c_discount:16 c_last:6 c_credit:14 72 ├── cardinality: [0 - 1] 73 ├── key: () 74 ├── fd: ()-->(6,14,16) 75 └── scan customer 76 ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_last:6 c_credit:14 c_discount:16 77 ├── constraint: /3/2/1: [/10/100/50 - /10/100/50] 78 ├── cardinality: [0 - 1] 79 ├── key: () 80 └── fd: ()-->(1-3,6,14,16) 81 82 opt format=hide-qual 83 SELECT i_price, i_name, i_data 84 FROM item 85 WHERE i_id IN (125, 150, 175, 200, 25, 50, 75, 100, 225, 250, 275, 300) 86 ORDER BY i_id 87 ---- 88 scan item 89 ├── columns: i_price:4 i_name:3 i_data:5 [hidden: i_id:1!null] 90 ├── constraint: /1 91 │ ├── [/25 - /25] 92 │ ├── [/50 - /50] 93 │ ├── [/75 - /75] 94 │ ├── [/100 - /100] 95 │ ├── [/125 - /125] 96 │ ├── [/150 - /150] 97 │ ├── [/175 - /175] 98 │ ├── [/200 - /200] 99 │ ├── [/225 - /225] 100 │ ├── [/250 - /250] 101 │ ├── [/275 - /275] 102 │ └── [/300 - /300] 103 ├── cardinality: [0 - 12] 104 ├── key: (1) 105 ├── fd: (1)-->(3-5) 106 └── ordering: +1 107 108 opt format=hide-qual 109 SELECT s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data, s_dist_05 110 FROM stock 111 WHERE (s_i_id, s_w_id) IN ((1000, 4), (900, 4), (1100, 4), (1500, 4), (1400, 4)) 112 ORDER BY s_i_id 113 ---- 114 project 115 ├── columns: s_quantity:3 s_ytd:14 s_order_cnt:15 s_remote_cnt:16 s_data:17 s_dist_05:8 [hidden: s_i_id:1!null] 116 ├── cardinality: [0 - 5] 117 ├── key: (1) 118 ├── fd: (1)-->(3,8,14-17) 119 ├── ordering: +1 120 └── scan stock 121 ├── columns: s_i_id:1!null s_w_id:2!null s_quantity:3 s_dist_05:8 s_ytd:14 s_order_cnt:15 s_remote_cnt:16 s_data:17 122 ├── constraint: /2/1 123 │ ├── [/4/900 - /4/900] 124 │ ├── [/4/1000 - /4/1000] 125 │ ├── [/4/1100 - /4/1100] 126 │ ├── [/4/1400 - /4/1400] 127 │ └── [/4/1500 - /4/1500] 128 ├── cardinality: [0 - 5] 129 ├── key: (1) 130 ├── fd: ()-->(2), (1)-->(3,8,14-17) 131 └── ordering: +1 opt(2) [actual: +1] 132 133 opt format=hide-qual 134 INSERT INTO "order" (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) 135 VALUES (100, 5, 10, 50, '2019-08-26 16:50:41', 10, 1) 136 ---- 137 insert "order" 138 ├── columns: <none> 139 ├── insert-mapping: 140 │ ├── column1:9 => o_id:1 141 │ ├── column2:10 => o_d_id:2 142 │ ├── column3:11 => o_w_id:3 143 │ ├── column4:12 => o_c_id:4 144 │ ├── column5:13 => o_entry_d:5 145 │ ├── column16:16 => o_carrier_id:6 146 │ ├── column6:14 => o_ol_cnt:7 147 │ └── column7:15 => o_all_local:8 148 ├── input binding: &1 149 ├── cardinality: [0 - 0] 150 ├── volatile, side-effects, mutations 151 ├── values 152 │ ├── columns: column1:9!null column2:10!null column3:11!null column4:12!null column5:13!null column6:14!null column7:15!null column16:16 153 │ ├── cardinality: [1 - 1] 154 │ ├── key: () 155 │ ├── fd: ()-->(9-16) 156 │ └── (100, 5, 10, 50, '2019-08-26 16:50:41+00:00', 10, 1, NULL) 157 └── f-k-checks 158 └── f-k-checks-item: order(o_w_id,o_d_id,o_c_id) -> customer(c_w_id,c_d_id,c_id) 159 └── anti-join (lookup customer) 160 ├── columns: column3:17!null column2:18!null column4:19!null 161 ├── key columns: [17 18 19] = [22 21 20] 162 ├── lookup columns are key 163 ├── cardinality: [0 - 1] 164 ├── key: () 165 ├── fd: ()-->(17-19) 166 ├── with-scan &1 167 │ ├── columns: column3:17!null column2:18!null column4:19!null 168 │ ├── mapping: 169 │ │ ├── column3:11 => column3:17 170 │ │ ├── column2:10 => column2:18 171 │ │ └── column4:12 => column4:19 172 │ ├── cardinality: [1 - 1] 173 │ ├── key: () 174 │ └── fd: ()-->(17-19) 175 └── filters (true) 176 177 opt format=hide-qual 178 INSERT INTO new_order (no_o_id, no_d_id, no_w_id) VALUES (2000, 100, 10) 179 ---- 180 insert new_order 181 ├── columns: <none> 182 ├── insert-mapping: 183 │ ├── column1:4 => no_o_id:1 184 │ ├── column2:5 => no_d_id:2 185 │ └── column3:6 => no_w_id:3 186 ├── input binding: &1 187 ├── cardinality: [0 - 0] 188 ├── volatile, side-effects, mutations 189 ├── values 190 │ ├── columns: column1:4!null column2:5!null column3:6!null 191 │ ├── cardinality: [1 - 1] 192 │ ├── key: () 193 │ ├── fd: ()-->(4-6) 194 │ └── (2000, 100, 10) 195 └── f-k-checks 196 └── f-k-checks-item: new_order(no_w_id,no_d_id,no_o_id) -> order(o_w_id,o_d_id,o_id) 197 └── anti-join (lookup order) 198 ├── columns: column3:7!null column2:8!null column1:9!null 199 ├── key columns: [7 8 9] = [12 11 10] 200 ├── lookup columns are key 201 ├── cardinality: [0 - 1] 202 ├── key: () 203 ├── fd: ()-->(7-9) 204 ├── with-scan &1 205 │ ├── columns: column3:7!null column2:8!null column1:9!null 206 │ ├── mapping: 207 │ │ ├── column3:6 => column3:7 208 │ │ ├── column2:5 => column2:8 209 │ │ └── column1:4 => column1:9 210 │ ├── cardinality: [1 - 1] 211 │ ├── key: () 212 │ └── fd: ()-->(7-9) 213 └── filters (true) 214 215 opt format=hide-qual 216 UPDATE 217 stock 218 SET 219 s_quantity 220 = CASE (s_i_id, s_w_id) 221 WHEN (6823, 0) THEN 26 222 WHEN (7853, 0) THEN 10 223 WHEN (8497, 0) THEN 62 224 WHEN (10904, 0) THEN 54 225 WHEN (16152, 0) THEN 80 226 WHEN (41382, 0) THEN 18 227 WHEN (55952, 0) THEN 56 228 WHEN (64817, 0) THEN 26 229 WHEN (66335, 0) THEN 30 230 WHEN (76567, 0) THEN 71 231 WHEN (81680, 0) THEN 51 232 WHEN (89641, 0) THEN 51 233 WHEN (89905, 0) THEN 77 234 ELSE crdb_internal.force_error('', 'unknown case') 235 END, 236 s_ytd 237 = CASE (s_i_id, s_w_id) 238 WHEN (6823, 0) THEN 6 239 WHEN (7853, 0) THEN 9 240 WHEN (8497, 0) THEN 13 241 WHEN (10904, 0) THEN 1 242 WHEN (16152, 0) THEN 2 243 WHEN (41382, 0) THEN 3 244 WHEN (55952, 0) THEN 10 245 WHEN (64817, 0) THEN 31 246 WHEN (66335, 0) THEN 9 247 WHEN (76567, 0) THEN 7 248 WHEN (81680, 0) THEN 4 249 WHEN (89641, 0) THEN 13 250 WHEN (89905, 0) THEN 20 251 END, 252 s_order_cnt 253 = CASE (s_i_id, s_w_id) 254 WHEN (6823, 0) THEN 1 255 WHEN (7853, 0) THEN 1 256 WHEN (8497, 0) THEN 2 257 WHEN (10904, 0) THEN 1 258 WHEN (16152, 0) THEN 1 259 WHEN (41382, 0) THEN 1 260 WHEN (55952, 0) THEN 1 261 WHEN (64817, 0) THEN 4 262 WHEN (66335, 0) THEN 2 263 WHEN (76567, 0) THEN 1 264 WHEN (81680, 0) THEN 1 265 WHEN (89641, 0) THEN 2 266 WHEN (89905, 0) THEN 4 267 END, 268 s_remote_cnt 269 = CASE (s_i_id, s_w_id) 270 WHEN (6823, 0) THEN 0 271 WHEN (7853, 0) THEN 0 272 WHEN (8497, 0) THEN 0 273 WHEN (10904, 0) THEN 0 274 WHEN (16152, 0) THEN 0 275 WHEN (41382, 0) THEN 0 276 WHEN (55952, 0) THEN 0 277 WHEN (64817, 0) THEN 0 278 WHEN (66335, 0) THEN 0 279 WHEN (76567, 0) THEN 0 280 WHEN (81680, 0) THEN 0 281 WHEN (89641, 0) THEN 0 282 WHEN (89905, 0) THEN 0 283 END 284 WHERE 285 (s_i_id, s_w_id) 286 IN ( 287 (6823, 0), 288 (7853, 0), 289 (8497, 0), 290 (10904, 0), 291 (16152, 0), 292 (41382, 0), 293 (55952, 0), 294 (64817, 0), 295 (66335, 0), 296 (76567, 0), 297 (81680, 0), 298 (89641, 0), 299 (89905, 0) 300 ) 301 ---- 302 update stock 303 ├── columns: <none> 304 ├── fetch columns: s_i_id:18 s_w_id:19 s_quantity:20 s_dist_01:21 s_dist_02:22 s_dist_03:23 s_dist_04:24 s_dist_05:25 s_dist_06:26 s_dist_07:27 s_dist_08:28 s_dist_09:29 s_dist_10:30 s_ytd:31 s_order_cnt:32 s_remote_cnt:33 s_data:34 305 ├── update-mapping: 306 │ ├── s_quantity_new:35 => s_quantity:3 307 │ ├── s_ytd_new:36 => s_ytd:14 308 │ ├── s_order_cnt_new:37 => s_order_cnt:15 309 │ └── s_remote_cnt_new:38 => s_remote_cnt:16 310 ├── cardinality: [0 - 0] 311 ├── volatile, side-effects, mutations 312 └── project 313 ├── columns: s_quantity_new:35 s_ytd_new:36 s_order_cnt_new:37 s_remote_cnt_new:38 s_i_id:18!null s_w_id:19!null s_quantity:20 s_dist_01:21 s_dist_02:22 s_dist_03:23 s_dist_04:24 s_dist_05:25 s_dist_06:26 s_dist_07:27 s_dist_08:28 s_dist_09:29 s_dist_10:30 s_ytd:31 s_order_cnt:32 s_remote_cnt:33 s_data:34 314 ├── cardinality: [0 - 13] 315 ├── volatile, side-effects 316 ├── key: (18) 317 ├── fd: ()-->(19), (18)-->(20-35), (18)-->(36-38) 318 ├── scan stock 319 │ ├── columns: s_i_id:18!null s_w_id:19!null s_quantity:20 s_dist_01:21 s_dist_02:22 s_dist_03:23 s_dist_04:24 s_dist_05:25 s_dist_06:26 s_dist_07:27 s_dist_08:28 s_dist_09:29 s_dist_10:30 s_ytd:31 s_order_cnt:32 s_remote_cnt:33 s_data:34 320 │ ├── constraint: /19/18 321 │ │ ├── [/0/6823 - /0/6823] 322 │ │ ├── [/0/7853 - /0/7853] 323 │ │ ├── [/0/8497 - /0/8497] 324 │ │ ├── [/0/10904 - /0/10904] 325 │ │ ├── [/0/16152 - /0/16152] 326 │ │ ├── [/0/41382 - /0/41382] 327 │ │ ├── [/0/55952 - /0/55952] 328 │ │ ├── [/0/64817 - /0/64817] 329 │ │ ├── [/0/66335 - /0/66335] 330 │ │ ├── [/0/76567 - /0/76567] 331 │ │ ├── [/0/81680 - /0/81680] 332 │ │ ├── [/0/89641 - /0/89641] 333 │ │ └── [/0/89905 - /0/89905] 334 │ ├── cardinality: [0 - 13] 335 │ ├── key: (18) 336 │ └── fd: ()-->(19), (18)-->(20-34) 337 └── projections 338 ├── CASE (s_i_id:18, s_w_id:19) WHEN (6823, 0) THEN 26 WHEN (7853, 0) THEN 10 WHEN (8497, 0) THEN 62 WHEN (10904, 0) THEN 54 WHEN (16152, 0) THEN 80 WHEN (41382, 0) THEN 18 WHEN (55952, 0) THEN 56 WHEN (64817, 0) THEN 26 WHEN (66335, 0) THEN 30 WHEN (76567, 0) THEN 71 WHEN (81680, 0) THEN 51 WHEN (89641, 0) THEN 51 WHEN (89905, 0) THEN 77 ELSE crdb_internal.force_error('', 'unknown case') END [as=s_quantity_new:35, outer=(18,19), volatile, side-effects] 339 ├── CASE (s_i_id:18, s_w_id:19) WHEN (6823, 0) THEN 6 WHEN (7853, 0) THEN 9 WHEN (8497, 0) THEN 13 WHEN (10904, 0) THEN 1 WHEN (16152, 0) THEN 2 WHEN (41382, 0) THEN 3 WHEN (55952, 0) THEN 10 WHEN (64817, 0) THEN 31 WHEN (66335, 0) THEN 9 WHEN (76567, 0) THEN 7 WHEN (81680, 0) THEN 4 WHEN (89641, 0) THEN 13 WHEN (89905, 0) THEN 20 END [as=s_ytd_new:36, outer=(18,19)] 340 ├── CASE (s_i_id:18, s_w_id:19) WHEN (6823, 0) THEN 1 WHEN (7853, 0) THEN 1 WHEN (8497, 0) THEN 2 WHEN (10904, 0) THEN 1 WHEN (16152, 0) THEN 1 WHEN (41382, 0) THEN 1 WHEN (55952, 0) THEN 1 WHEN (64817, 0) THEN 4 WHEN (66335, 0) THEN 2 WHEN (76567, 0) THEN 1 WHEN (81680, 0) THEN 1 WHEN (89641, 0) THEN 2 WHEN (89905, 0) THEN 4 END [as=s_order_cnt_new:37, outer=(18,19)] 341 └── CASE (s_i_id:18, s_w_id:19) WHEN (6823, 0) THEN 0 WHEN (7853, 0) THEN 0 WHEN (8497, 0) THEN 0 WHEN (10904, 0) THEN 0 WHEN (16152, 0) THEN 0 WHEN (41382, 0) THEN 0 WHEN (55952, 0) THEN 0 WHEN (64817, 0) THEN 0 WHEN (66335, 0) THEN 0 WHEN (76567, 0) THEN 0 WHEN (81680, 0) THEN 0 WHEN (89641, 0) THEN 0 WHEN (89905, 0) THEN 0 END [as=s_remote_cnt_new:38, outer=(18,19)] 342 343 opt format=hide-qual 344 INSERT INTO order_line 345 (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) 346 VALUES 347 (3045, 2, 10, 3, 648, 0, 9, 394.470000, 'YhgLRrwsmd68P2bElAgrnp8u'), 348 (3045, 2, 10, 5, 25393, 0, 10, 830.600000, 'dLXe0YhgLRrwsmd68P2bElAg'), 349 (3045, 2, 10, 1, 47887, 0, 9, 204.390000, 'Xe0YhgLRrwsmd68P2bElAgrn'), 350 (3045, 2, 10, 2, 52000, 0, 6, 561.660000, 'ElAgrnp8ueWNXJpBB0ObpVWo'), 351 (3045, 2, 10, 4, 56624, 0, 6, 273.360000, 'RsaCXoEzmssaF9m9cdLXe0Yh'), 352 (3045, 2, 10, 6, 92966, 0, 4, 366.760000, 'saCXoEzmssaF9m9cdLXe0Yhg') 353 ---- 354 insert order_line 355 ├── columns: <none> 356 ├── insert-mapping: 357 │ ├── column1:11 => ol_o_id:1 358 │ ├── column2:12 => ol_d_id:2 359 │ ├── column3:13 => ol_w_id:3 360 │ ├── column4:14 => ol_number:4 361 │ ├── column5:15 => ol_i_id:5 362 │ ├── column6:16 => ol_supply_w_id:6 363 │ ├── column20:20 => ol_delivery_d:7 364 │ ├── column7:17 => ol_quantity:8 365 │ ├── ol_amount:21 => order_line.ol_amount:9 366 │ └── column9:19 => ol_dist_info:10 367 ├── input binding: &1 368 ├── cardinality: [0 - 0] 369 ├── volatile, side-effects, mutations 370 ├── project 371 │ ├── columns: ol_amount:21 column20:20 column1:11!null column2:12!null column3:13!null column4:14!null column5:15!null column6:16!null column7:17!null column9:19!null 372 │ ├── cardinality: [6 - 6] 373 │ ├── immutable 374 │ ├── fd: ()-->(20) 375 │ ├── values 376 │ │ ├── columns: column1:11!null column2:12!null column3:13!null column4:14!null column5:15!null column6:16!null column7:17!null column8:18!null column9:19!null 377 │ │ ├── cardinality: [6 - 6] 378 │ │ ├── (3045, 2, 10, 3, 648, 0, 9, 394.470000, 'YhgLRrwsmd68P2bElAgrnp8u') 379 │ │ ├── (3045, 2, 10, 5, 25393, 0, 10, 830.600000, 'dLXe0YhgLRrwsmd68P2bElAg') 380 │ │ ├── (3045, 2, 10, 1, 47887, 0, 9, 204.390000, 'Xe0YhgLRrwsmd68P2bElAgrn') 381 │ │ ├── (3045, 2, 10, 2, 52000, 0, 6, 561.660000, 'ElAgrnp8ueWNXJpBB0ObpVWo') 382 │ │ ├── (3045, 2, 10, 4, 56624, 0, 6, 273.360000, 'RsaCXoEzmssaF9m9cdLXe0Yh') 383 │ │ └── (3045, 2, 10, 6, 92966, 0, 4, 366.760000, 'saCXoEzmssaF9m9cdLXe0Yhg') 384 │ └── projections 385 │ ├── crdb_internal.round_decimal_values(column8:18, 2) [as=ol_amount:21, outer=(18), immutable] 386 │ └── CAST(NULL AS TIMESTAMP) [as=column20:20] 387 └── f-k-checks 388 ├── f-k-checks-item: order_line(ol_w_id,ol_d_id,ol_o_id) -> order(o_w_id,o_d_id,o_id) 389 │ └── anti-join (lookup order) 390 │ ├── columns: column3:22!null column2:23!null column1:24!null 391 │ ├── key columns: [22 23 24] = [27 26 25] 392 │ ├── lookup columns are key 393 │ ├── cardinality: [0 - 6] 394 │ ├── with-scan &1 395 │ │ ├── columns: column3:22!null column2:23!null column1:24!null 396 │ │ ├── mapping: 397 │ │ │ ├── column3:13 => column3:22 398 │ │ │ ├── column2:12 => column2:23 399 │ │ │ └── column1:11 => column1:24 400 │ │ └── cardinality: [6 - 6] 401 │ └── filters (true) 402 └── f-k-checks-item: order_line(ol_supply_w_id,ol_i_id) -> stock(s_w_id,s_i_id) 403 └── anti-join (lookup stock@stock_item_fk_idx) 404 ├── columns: column6:33!null column5:34!null 405 ├── key columns: [34 33] = [35 36] 406 ├── lookup columns are key 407 ├── cardinality: [0 - 6] 408 ├── with-scan &1 409 │ ├── columns: column6:33!null column5:34!null 410 │ ├── mapping: 411 │ │ ├── column6:16 => column6:33 412 │ │ └── column5:15 => column5:34 413 │ └── cardinality: [6 - 6] 414 └── filters (true) 415 416 # -------------------------------------------------- 417 # 2.5 The Payment Transaction 418 # 419 # The Payment business transaction updates the customer's balance and reflects 420 # the payment on the district and warehouse sales statistics. It represents a 421 # light-weight, read-write transaction with a high frequency of execution and 422 # stringent response time requirements to satisfy on-line users. In addition, 423 # this transaction includes non-primary key access to the CUSTOMER table. 424 # -------------------------------------------------- 425 opt format=hide-qual 426 UPDATE warehouse SET w_ytd = w_ytd + 3860.61 WHERE w_id = 10 427 RETURNING w_name, w_street_1, w_street_2, w_city, w_state, w_zip 428 ---- 429 project 430 ├── columns: w_name:2 w_street_1:3 w_street_2:4 w_city:5 w_state:6 w_zip:7 431 ├── cardinality: [0 - 1] 432 ├── volatile, side-effects, mutations 433 ├── key: () 434 ├── fd: ()-->(2-7) 435 └── update warehouse 436 ├── columns: w_id:1!null w_name:2 w_street_1:3 w_street_2:4 w_city:5 w_state:6 w_zip:7 437 ├── fetch columns: w_id:10 w_name:11 w_street_1:12 w_street_2:13 w_city:14 w_state:15 w_zip:16 w_tax:17 warehouse.w_ytd:18 438 ├── update-mapping: 439 │ └── w_ytd:20 => warehouse.w_ytd:9 440 ├── cardinality: [0 - 1] 441 ├── volatile, side-effects, mutations 442 ├── key: () 443 ├── fd: ()-->(1-7) 444 └── project 445 ├── columns: w_ytd:20 w_id:10!null w_name:11 w_street_1:12 w_street_2:13 w_city:14 w_state:15 w_zip:16 w_tax:17 warehouse.w_ytd:18 446 ├── cardinality: [0 - 1] 447 ├── immutable 448 ├── key: () 449 ├── fd: ()-->(10-18,20) 450 ├── scan warehouse 451 │ ├── columns: w_id:10!null w_name:11 w_street_1:12 w_street_2:13 w_city:14 w_state:15 w_zip:16 w_tax:17 warehouse.w_ytd:18 452 │ ├── constraint: /10: [/10 - /10] 453 │ ├── cardinality: [0 - 1] 454 │ ├── key: () 455 │ └── fd: ()-->(10-18) 456 └── projections 457 └── crdb_internal.round_decimal_values(warehouse.w_ytd:18 + 3860.61, 2) [as=w_ytd:20, outer=(18), immutable] 458 459 opt format=hide-qual 460 UPDATE district SET d_ytd = d_ytd + 3860.61 WHERE (d_w_id = 10) AND (d_id = 5) 461 RETURNING d_name, d_street_1, d_street_2, d_city, d_state, d_zip 462 ---- 463 project 464 ├── columns: d_name:3 d_street_1:4 d_street_2:5 d_city:6 d_state:7 d_zip:8 465 ├── cardinality: [0 - 1] 466 ├── volatile, side-effects, mutations 467 ├── key: () 468 ├── fd: ()-->(3-8) 469 └── update district 470 ├── columns: d_id:1!null d_w_id:2!null d_name:3 d_street_1:4 d_street_2:5 d_city:6 d_state:7 d_zip:8 471 ├── fetch columns: d_id:12 d_w_id:13 d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 district.d_ytd:21 d_next_o_id:22 472 ├── update-mapping: 473 │ └── d_ytd:24 => district.d_ytd:10 474 ├── cardinality: [0 - 1] 475 ├── volatile, side-effects, mutations 476 ├── key: () 477 ├── fd: ()-->(1-8) 478 └── project 479 ├── columns: d_ytd:24 d_id:12!null d_w_id:13!null d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 district.d_ytd:21 d_next_o_id:22 480 ├── cardinality: [0 - 1] 481 ├── immutable 482 ├── key: () 483 ├── fd: ()-->(12-22,24) 484 ├── scan district 485 │ ├── columns: d_id:12!null d_w_id:13!null d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 district.d_ytd:21 d_next_o_id:22 486 │ ├── constraint: /13/12: [/10/5 - /10/5] 487 │ ├── cardinality: [0 - 1] 488 │ ├── key: () 489 │ └── fd: ()-->(12-22) 490 └── projections 491 └── crdb_internal.round_decimal_values(district.d_ytd:21 + 3860.61, 2) [as=d_ytd:24, outer=(21), immutable] 492 493 opt format=hide-qual 494 SELECT c_id 495 FROM customer 496 WHERE c_w_id = 10 AND c_d_id = 100 AND c_last = 'Smith' 497 ORDER BY c_first ASC 498 ---- 499 project 500 ├── columns: c_id:1!null [hidden: c_first:4] 501 ├── key: (1) 502 ├── fd: (1)-->(4) 503 ├── ordering: +4 504 └── scan customer@customer_idx 505 ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_last:6!null 506 ├── constraint: /3/2/6/4/1: [/10/100/'Smith' - /10/100/'Smith'] 507 ├── key: (1) 508 ├── fd: ()-->(2,3,6), (1)-->(4) 509 └── ordering: +4 opt(2,3,6) [actual: +4] 510 511 opt format=hide-qual 512 UPDATE customer 513 SET (c_balance, c_ytd_payment, c_payment_cnt, c_data) 514 = ( 515 c_balance - (3860.61:::FLOAT8)::DECIMAL, 516 c_ytd_payment + (3860.61:::FLOAT8)::DECIMAL, 517 c_payment_cnt + 1, 518 CASE c_credit 519 WHEN 'BC' 520 THEN "left"( 521 c_id::STRING 522 || c_d_id::STRING 523 || c_w_id::STRING 524 || (5:::INT8)::STRING 525 || (10:::INT8)::STRING 526 || (3860.61:::FLOAT8)::STRING 527 || c_data, 528 500 529 ) 530 ELSE c_data 531 END 532 ) 533 WHERE 534 (c_w_id = 10 AND c_d_id = 5) AND c_id = 1343 535 RETURNING 536 c_first, 537 c_middle, 538 c_last, 539 c_street_1, 540 c_street_2, 541 c_city, 542 c_state, 543 c_zip, 544 c_phone, 545 c_since, 546 c_credit, 547 c_credit_lim, 548 c_discount, 549 c_balance, 550 CASE c_credit WHEN 'BC' THEN "left"(c_data, 200) ELSE '' END 551 ---- 552 project 553 ├── columns: c_first:4 c_middle:5 c_last:6 c_street_1:7 c_street_2:8 c_city:9 c_state:10 c_zip:11 c_phone:12 c_since:13 c_credit:14 c_credit_lim:15 c_discount:16 c_balance:17 case:49 554 ├── cardinality: [0 - 1] 555 ├── volatile, side-effects, mutations 556 ├── key: () 557 ├── fd: ()-->(4-17,49) 558 ├── update customer 559 │ ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_middle:5 c_last:6 c_street_1:7 c_street_2:8 c_city:9 c_state:10 c_zip:11 c_phone:12 c_since:13 c_credit:14 c_credit_lim:15 c_discount:16 customer.c_balance:17 c_data:21 560 │ ├── fetch columns: c_id:22 c_d_id:23 c_w_id:24 c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 customer.c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 561 │ ├── update-mapping: 562 │ │ ├── c_balance:47 => customer.c_balance:17 563 │ │ ├── c_ytd_payment:48 => customer.c_ytd_payment:18 564 │ │ ├── c_payment_cnt_new:45 => c_payment_cnt:19 565 │ │ └── c_data_new:46 => c_data:21 566 │ ├── cardinality: [0 - 1] 567 │ ├── volatile, side-effects, mutations 568 │ ├── key: () 569 │ ├── fd: ()-->(1-17,21) 570 │ └── project 571 │ ├── columns: c_balance:47 c_ytd_payment:48 c_payment_cnt_new:45 c_data_new:46 c_id:22!null c_d_id:23!null c_w_id:24!null c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 customer.c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 572 │ ├── cardinality: [0 - 1] 573 │ ├── immutable 574 │ ├── key: () 575 │ ├── fd: ()-->(22-42,45-48) 576 │ ├── scan customer 577 │ │ ├── columns: c_id:22!null c_d_id:23!null c_w_id:24!null c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 customer.c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 578 │ │ ├── constraint: /24/23/22: [/10/5/1343 - /10/5/1343] 579 │ │ ├── cardinality: [0 - 1] 580 │ │ ├── key: () 581 │ │ └── fd: ()-->(22-42) 582 │ └── projections 583 │ ├── crdb_internal.round_decimal_values(customer.c_balance:38 - 3860.61, 2) [as=c_balance:47, outer=(38), immutable] 584 │ ├── crdb_internal.round_decimal_values(customer.c_ytd_payment:39 + 3860.61, 2) [as=c_ytd_payment:48, outer=(39), immutable] 585 │ ├── c_payment_cnt:40 + 1 [as=c_payment_cnt_new:45, outer=(40)] 586 │ └── CASE c_credit:35 WHEN 'BC' THEN left((((((c_id:22::STRING || c_d_id:23::STRING) || c_w_id:24::STRING) || '5') || '10') || '3860.61') || c_data:42, 500) ELSE c_data:42 END [as=c_data_new:46, outer=(22-24,35,42), immutable] 587 └── projections 588 └── CASE c_credit:14 WHEN 'BC' THEN left(c_data:21, 200) ELSE '' END [as=case:49, outer=(14,21), immutable] 589 590 opt format=hide-qual 591 INSERT INTO history 592 (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_amount, h_date, h_data) 593 VALUES 594 (1343, 5, 10, 5, 10, 3860.61, '2019-08-26 16:50:41', '8 Kdcgphy3') 595 ---- 596 insert history 597 ├── columns: <none> 598 ├── insert-mapping: 599 │ ├── column18:18 => rowid:1 600 │ ├── column1:10 => h_c_id:2 601 │ ├── column2:11 => h_c_d_id:3 602 │ ├── column3:12 => h_c_w_id:4 603 │ ├── column4:13 => h_d_id:5 604 │ ├── column5:14 => h_w_id:6 605 │ ├── column7:16 => h_date:7 606 │ ├── h_amount:19 => history.h_amount:8 607 │ └── column8:17 => h_data:9 608 ├── input binding: &1 609 ├── cardinality: [0 - 0] 610 ├── volatile, side-effects, mutations 611 ├── values 612 │ ├── columns: column1:10!null column2:11!null column3:12!null column4:13!null column5:14!null column7:16!null column8:17!null column18:18 h_amount:19!null 613 │ ├── cardinality: [1 - 1] 614 │ ├── volatile, side-effects 615 │ ├── key: () 616 │ ├── fd: ()-->(10-14,16-19) 617 │ └── (1343, 5, 10, 5, 10, '2019-08-26 16:50:41+00:00', '8 Kdcgphy3', gen_random_uuid(), 3860.61) 618 └── f-k-checks 619 ├── f-k-checks-item: history(h_c_w_id,h_c_d_id,h_c_id) -> customer(c_w_id,c_d_id,c_id) 620 │ └── anti-join (lookup customer) 621 │ ├── columns: column3:20!null column2:21!null column1:22!null 622 │ ├── key columns: [20 21 22] = [25 24 23] 623 │ ├── lookup columns are key 624 │ ├── cardinality: [0 - 1] 625 │ ├── key: () 626 │ ├── fd: ()-->(20-22) 627 │ ├── with-scan &1 628 │ │ ├── columns: column3:20!null column2:21!null column1:22!null 629 │ │ ├── mapping: 630 │ │ │ ├── column3:12 => column3:20 631 │ │ │ ├── column2:11 => column2:21 632 │ │ │ └── column1:10 => column1:22 633 │ │ ├── cardinality: [1 - 1] 634 │ │ ├── key: () 635 │ │ └── fd: ()-->(20-22) 636 │ └── filters (true) 637 └── f-k-checks-item: history(h_w_id,h_d_id) -> district(d_w_id,d_id) 638 └── anti-join (lookup district) 639 ├── columns: column5:44!null column4:45!null 640 ├── key columns: [44 45] = [47 46] 641 ├── lookup columns are key 642 ├── cardinality: [0 - 1] 643 ├── key: () 644 ├── fd: ()-->(44,45) 645 ├── with-scan &1 646 │ ├── columns: column5:44!null column4:45!null 647 │ ├── mapping: 648 │ │ ├── column5:14 => column5:44 649 │ │ └── column4:13 => column4:45 650 │ ├── cardinality: [1 - 1] 651 │ ├── key: () 652 │ └── fd: ()-->(44,45) 653 └── filters (true) 654 655 # -------------------------------------------------- 656 # 2.6 The Order Status Transaction 657 # 658 # The Order-Status business transaction queries the status of a customer's last 659 # order. It represents a mid-weight read-only database transaction with a low 660 # frequency of execution and response time requirement to satisfy on-line 661 # users. In addition, this table includes non-primary key access to the 662 # CUSTOMER table. 663 # -------------------------------------------------- 664 opt format=hide-qual 665 SELECT c_balance, c_first, c_middle, c_last 666 FROM customer 667 WHERE c_w_id = 10 AND c_d_id = 100 AND c_id = 50 668 ---- 669 project 670 ├── columns: c_balance:17 c_first:4 c_middle:5 c_last:6 671 ├── cardinality: [0 - 1] 672 ├── key: () 673 ├── fd: ()-->(4-6,17) 674 └── scan customer 675 ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_middle:5 c_last:6 c_balance:17 676 ├── constraint: /3/2/1: [/10/100/50 - /10/100/50] 677 ├── cardinality: [0 - 1] 678 ├── key: () 679 └── fd: ()-->(1-6,17) 680 681 opt format=hide-qual 682 SELECT c_id, c_balance, c_first, c_middle 683 FROM customer 684 WHERE c_w_id = 10 AND c_d_id = 100 AND c_last = 'Smith' 685 ORDER BY c_first ASC 686 ---- 687 sort 688 ├── columns: c_id:1!null c_balance:17 c_first:4 c_middle:5 689 ├── key: (1) 690 ├── fd: (1)-->(4,5,17) 691 ├── ordering: +4 692 └── project 693 ├── columns: c_id:1!null c_first:4 c_middle:5 c_balance:17 694 ├── key: (1) 695 ├── fd: (1)-->(4,5,17) 696 └── select 697 ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_middle:5 c_last:6!null c_balance:17 698 ├── key: (1) 699 ├── fd: ()-->(2,3,6), (1)-->(4,5,17) 700 ├── scan customer 701 │ ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_middle:5 c_last:6 c_balance:17 702 │ ├── constraint: /3/2/1: [/10/100 - /10/100] 703 │ ├── key: (1) 704 │ └── fd: ()-->(2,3), (1)-->(4-6,17) 705 └── filters 706 └── c_last:6 = 'Smith' [outer=(6), constraints=(/6: [/'Smith' - /'Smith']; tight), fd=()-->(6)] 707 708 opt format=hide-qual 709 SELECT o_id, o_entry_d, o_carrier_id 710 FROM "order" 711 WHERE o_w_id = 10 AND o_d_id = 100 AND o_c_id = 50 712 ORDER BY o_id DESC 713 LIMIT 1 714 ---- 715 project 716 ├── columns: o_id:1!null o_entry_d:5 o_carrier_id:6 717 ├── cardinality: [0 - 1] 718 ├── key: () 719 ├── fd: ()-->(1,5,6) 720 └── scan "order"@order_idx 721 ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_c_id:4!null o_entry_d:5 o_carrier_id:6 722 ├── constraint: /3/2/4/-1: [/10/100/50 - /10/100/50] 723 ├── limit: 1 724 ├── key: () 725 └── fd: ()-->(1-6) 726 727 opt format=hide-qual 728 SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d 729 FROM order_line 730 WHERE ol_w_id = 10 AND ol_d_id = 100 AND ol_o_id = 1000 731 ---- 732 project 733 ├── columns: ol_i_id:5!null ol_supply_w_id:6 ol_quantity:8 ol_amount:9 ol_delivery_d:7 734 └── scan order_line 735 ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null ol_i_id:5!null ol_supply_w_id:6 ol_delivery_d:7 ol_quantity:8 ol_amount:9 736 ├── constraint: /3/2/-1/4: [/10/100/1000 - /10/100/1000] 737 └── fd: ()-->(1-3) 738 739 # -------------------------------------------------- 740 # 2.7 The Delivery Transaction 741 # 742 # The Delivery business transaction consists of processing a batch of 10 new 743 # (not yet delivered) orders. Each order is processed (delivered) in full 744 # within the scope of a read-write database transaction. The number of orders 745 # delivered as a group (or batched) within the same database transaction is 746 # implementation specific. The business transaction, comprised of one or more 747 # (up to 10) database transactions, has a low frequency of execution and must 748 # complete within a relaxed response time requirement. 749 # 750 # The Delivery transaction is intended to be executed in deferred mode through 751 # a queuing mechanism, rather than interactively, with terminal response 752 # indicating transaction completion. The result of the deferred execution is 753 # recorded into a result file. 754 # -------------------------------------------------- 755 opt format=hide-qual 756 SELECT no_o_id 757 FROM new_order 758 WHERE no_w_id = 10 AND no_d_id = 100 759 ORDER BY no_o_id ASC 760 LIMIT 1 761 ---- 762 project 763 ├── columns: no_o_id:1!null 764 ├── cardinality: [0 - 1] 765 ├── key: () 766 ├── fd: ()-->(1) 767 └── scan new_order 768 ├── columns: no_o_id:1!null no_d_id:2!null no_w_id:3!null 769 ├── constraint: /3/2/1: [/10/100 - /10/100] 770 ├── limit: 1 771 ├── key: () 772 └── fd: ()-->(1-3) 773 774 opt format=hide-qual 775 SELECT sum(ol_amount) 776 FROM order_line 777 WHERE ol_w_id = 10 AND ol_d_id = 100 AND ol_o_id = 1000 778 ---- 779 scalar-group-by 780 ├── columns: sum:11 781 ├── cardinality: [1 - 1] 782 ├── key: () 783 ├── fd: ()-->(11) 784 ├── scan order_line 785 │ ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null ol_amount:9 786 │ ├── constraint: /3/2/-1/4: [/10/100/1000 - /10/100/1000] 787 │ └── fd: ()-->(1-3) 788 └── aggregations 789 └── sum [as=sum:11, outer=(9)] 790 └── ol_amount:9 791 792 opt format=hide-qual 793 UPDATE "order" 794 SET o_carrier_id = 10 795 WHERE o_w_id = 10 796 AND (o_d_id, o_id) IN ( 797 (10, 2167), 798 (5, 2167), 799 (6, 2167), 800 (9, 2167), 801 (4, 2167), 802 (7, 2167), 803 (8, 2167), 804 (1, 2167), 805 (2, 2167), 806 (3, 2167) 807 ) 808 RETURNING 809 o_d_id, o_c_id 810 ---- 811 project 812 ├── columns: o_d_id:2!null o_c_id:4 813 ├── cardinality: [0 - 10] 814 ├── volatile, side-effects, mutations 815 ├── key: (2) 816 ├── fd: (2)-->(4) 817 └── update "order" 818 ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_c_id:4 819 ├── fetch columns: o_id:9 o_d_id:10 o_w_id:11 o_c_id:12 o_entry_d:13 o_carrier_id:14 o_ol_cnt:15 o_all_local:16 820 ├── update-mapping: 821 │ └── o_carrier_id_new:17 => o_carrier_id:6 822 ├── cardinality: [0 - 10] 823 ├── volatile, side-effects, mutations 824 ├── key: (2) 825 ├── fd: ()-->(1,3), (2)-->(4) 826 └── project 827 ├── columns: o_carrier_id_new:17!null o_id:9!null o_d_id:10!null o_w_id:11!null o_c_id:12 o_entry_d:13 o_carrier_id:14 o_ol_cnt:15 o_all_local:16 828 ├── cardinality: [0 - 10] 829 ├── key: (10) 830 ├── fd: ()-->(9,11,17), (10)-->(12-16) 831 ├── scan "order" 832 │ ├── columns: o_id:9!null o_d_id:10!null o_w_id:11!null o_c_id:12 o_entry_d:13 o_carrier_id:14 o_ol_cnt:15 o_all_local:16 833 │ ├── constraint: /11/10/-9 834 │ │ ├── [/10/1/2167 - /10/1/2167] 835 │ │ ├── [/10/2/2167 - /10/2/2167] 836 │ │ ├── [/10/3/2167 - /10/3/2167] 837 │ │ ├── [/10/4/2167 - /10/4/2167] 838 │ │ ├── [/10/5/2167 - /10/5/2167] 839 │ │ ├── [/10/6/2167 - /10/6/2167] 840 │ │ ├── [/10/7/2167 - /10/7/2167] 841 │ │ ├── [/10/8/2167 - /10/8/2167] 842 │ │ ├── [/10/9/2167 - /10/9/2167] 843 │ │ └── [/10/10/2167 - /10/10/2167] 844 │ ├── cardinality: [0 - 10] 845 │ ├── key: (10) 846 │ └── fd: ()-->(9,11), (10)-->(12-16) 847 └── projections 848 └── 10 [as=o_carrier_id_new:17] 849 850 opt format=hide-qual 851 UPDATE customer 852 SET c_delivery_cnt = c_delivery_cnt + 1, 853 c_balance = c_balance + CASE c_d_id 854 WHEN 6 THEN 57214.780000 855 WHEN 8 THEN 67755.430000 856 WHEN 1 THEN 51177.840000 857 WHEN 2 THEN 73840.700000 858 WHEN 4 THEN 45906.990000 859 WHEN 9 THEN 32523.760000 860 WHEN 10 THEN 20240.200000 861 WHEN 3 THEN 75299.790000 862 WHEN 5 THEN 56543.340000 863 WHEN 7 THEN 67157.940000 864 END 865 WHERE c_w_id = 10 AND (c_d_id, c_id) IN ( 866 (1, 1405), 867 (2, 137), 868 (3, 309), 869 (7, 2377), 870 (8, 2106), 871 (10, 417), 872 (4, 98), 873 (5, 1683), 874 (6, 2807), 875 (9, 1412) 876 ) 877 ---- 878 update customer 879 ├── columns: <none> 880 ├── fetch columns: c_id:22 c_d_id:23 c_w_id:24 c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 881 ├── update-mapping: 882 │ ├── c_balance:45 => customer.c_balance:17 883 │ └── c_delivery_cnt_new:43 => c_delivery_cnt:20 884 ├── cardinality: [0 - 0] 885 ├── volatile, side-effects, mutations 886 └── project 887 ├── columns: c_balance:45 c_delivery_cnt_new:43 c_id:22!null c_d_id:23!null c_w_id:24!null c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 888 ├── cardinality: [0 - 10] 889 ├── immutable 890 ├── key: (22,23) 891 ├── fd: ()-->(24), (22,23)-->(25-42,45), (41)-->(43) 892 ├── scan customer 893 │ ├── columns: c_id:22!null c_d_id:23!null c_w_id:24!null c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 894 │ ├── constraint: /24/23/22 895 │ │ ├── [/10/1/1405 - /10/1/1405] 896 │ │ ├── [/10/2/137 - /10/2/137] 897 │ │ ├── [/10/3/309 - /10/3/309] 898 │ │ ├── [/10/4/98 - /10/4/98] 899 │ │ ├── [/10/5/1683 - /10/5/1683] 900 │ │ ├── [/10/6/2807 - /10/6/2807] 901 │ │ ├── [/10/7/2377 - /10/7/2377] 902 │ │ ├── [/10/8/2106 - /10/8/2106] 903 │ │ ├── [/10/9/1412 - /10/9/1412] 904 │ │ └── [/10/10/417 - /10/10/417] 905 │ ├── cardinality: [0 - 10] 906 │ ├── key: (22,23) 907 │ └── fd: ()-->(24), (22,23)-->(25-42) 908 └── projections 909 ├── crdb_internal.round_decimal_values(customer.c_balance:38 + CASE c_d_id:23 WHEN 6 THEN 57214.780000 WHEN 8 THEN 67755.430000 WHEN 1 THEN 51177.840000 WHEN 2 THEN 73840.700000 WHEN 4 THEN 45906.990000 WHEN 9 THEN 32523.760000 WHEN 10 THEN 20240.200000 WHEN 3 THEN 75299.790000 WHEN 5 THEN 56543.340000 WHEN 7 THEN 67157.940000 END, 2) [as=c_balance:45, outer=(23,38), immutable] 910 └── c_delivery_cnt:41 + 1 [as=c_delivery_cnt_new:43, outer=(41)] 911 912 opt format=hide-qual 913 DELETE FROM new_order 914 WHERE no_w_id = 10 AND (no_d_id, no_o_id) IN ( 915 (10, 2167), 916 (5, 2167), 917 (6, 2167), 918 (9, 2167), 919 (4, 2167), 920 (7, 2167), 921 (8, 2167), 922 (1, 2167), 923 (2, 2167), 924 (3, 2167) 925 ) 926 ---- 927 delete new_order 928 ├── columns: <none> 929 ├── fetch columns: no_o_id:4 no_d_id:5 no_w_id:6 930 ├── cardinality: [0 - 0] 931 ├── volatile, side-effects, mutations 932 └── scan new_order 933 ├── columns: no_o_id:4!null no_d_id:5!null no_w_id:6!null 934 ├── constraint: /6/5/4 935 │ ├── [/10/1/2167 - /10/1/2167] 936 │ ├── [/10/2/2167 - /10/2/2167] 937 │ ├── [/10/3/2167 - /10/3/2167] 938 │ ├── [/10/4/2167 - /10/4/2167] 939 │ ├── [/10/5/2167 - /10/5/2167] 940 │ ├── [/10/6/2167 - /10/6/2167] 941 │ ├── [/10/7/2167 - /10/7/2167] 942 │ ├── [/10/8/2167 - /10/8/2167] 943 │ ├── [/10/9/2167 - /10/9/2167] 944 │ └── [/10/10/2167 - /10/10/2167] 945 ├── cardinality: [0 - 10] 946 ├── key: (5) 947 └── fd: ()-->(4,6) 948 949 opt format=hide-qual 950 UPDATE order_line 951 SET ol_delivery_d = '2019-08-26 16:50:41' 952 WHERE ol_w_id = 10 AND (ol_d_id, ol_o_id) IN ( 953 (10, 2167), 954 (5, 2167), 955 (6, 2167), 956 (9, 2167), 957 (4, 2167), 958 (7, 2167), 959 (8, 2167), 960 (1, 2167), 961 (2, 2167), 962 (3, 2167) 963 ) 964 ---- 965 update order_line 966 ├── columns: <none> 967 ├── fetch columns: ol_o_id:11 ol_d_id:12 ol_w_id:13 ol_number:14 ol_i_id:15 ol_supply_w_id:16 ol_delivery_d:17 ol_quantity:18 ol_amount:19 ol_dist_info:20 968 ├── update-mapping: 969 │ └── ol_delivery_d_new:21 => ol_delivery_d:7 970 ├── cardinality: [0 - 0] 971 ├── volatile, side-effects, mutations 972 └── project 973 ├── columns: ol_delivery_d_new:21!null ol_o_id:11!null ol_d_id:12!null ol_w_id:13!null ol_number:14!null ol_i_id:15!null ol_supply_w_id:16 ol_delivery_d:17 ol_quantity:18 ol_amount:19 ol_dist_info:20 974 ├── key: (12,14) 975 ├── fd: ()-->(11,13,21), (12,14)-->(15-20) 976 ├── scan order_line 977 │ ├── columns: ol_o_id:11!null ol_d_id:12!null ol_w_id:13!null ol_number:14!null ol_i_id:15!null ol_supply_w_id:16 ol_delivery_d:17 ol_quantity:18 ol_amount:19 ol_dist_info:20 978 │ ├── constraint: /13/12/-11/14 979 │ │ ├── [/10/1/2167 - /10/1/2167] 980 │ │ ├── [/10/2/2167 - /10/2/2167] 981 │ │ ├── [/10/3/2167 - /10/3/2167] 982 │ │ ├── [/10/4/2167 - /10/4/2167] 983 │ │ ├── [/10/5/2167 - /10/5/2167] 984 │ │ ├── [/10/6/2167 - /10/6/2167] 985 │ │ ├── [/10/7/2167 - /10/7/2167] 986 │ │ ├── [/10/8/2167 - /10/8/2167] 987 │ │ ├── [/10/9/2167 - /10/9/2167] 988 │ │ └── [/10/10/2167 - /10/10/2167] 989 │ ├── key: (12,14) 990 │ └── fd: ()-->(11,13), (12,14)-->(15-20) 991 └── projections 992 └── '2019-08-26 16:50:41+00:00' [as=ol_delivery_d_new:21] 993 994 # -------------------------------------------------- 995 # 2.8 The Stock-Level Transaction 996 # 997 # The Stock-Level business transaction determines the number of recently sold 998 # items that have a stock level below a specified threshold. It represents a 999 # heavy read-only database transaction with a low frequency of execution, a 1000 # relaxed response time requirement, and relaxed consistency requirements. 1001 # -------------------------------------------------- 1002 opt format=hide-qual 1003 SELECT d_next_o_id 1004 FROM district 1005 WHERE d_w_id = 10 AND d_id = 100 1006 ---- 1007 project 1008 ├── columns: d_next_o_id:11 1009 ├── cardinality: [0 - 1] 1010 ├── key: () 1011 ├── fd: ()-->(11) 1012 └── scan district 1013 ├── columns: d_id:1!null d_w_id:2!null d_next_o_id:11 1014 ├── constraint: /2/1: [/10/100 - /10/100] 1015 ├── cardinality: [0 - 1] 1016 ├── key: () 1017 └── fd: ()-->(1,2,11) 1018 1019 opt format=hide-qual 1020 SELECT count(DISTINCT s_i_id) 1021 FROM order_line 1022 JOIN stock 1023 ON s_i_id=ol_i_id AND s_w_id=ol_w_id 1024 WHERE ol_w_id = 10 1025 AND ol_d_id = 100 1026 AND ol_o_id BETWEEN 1000 - 20 AND 1000 - 1 1027 AND s_quantity < 15 1028 ---- 1029 scalar-group-by 1030 ├── columns: count:28!null 1031 ├── cardinality: [1 - 1] 1032 ├── key: () 1033 ├── fd: ()-->(28) 1034 ├── distinct-on 1035 │ ├── columns: s_i_id:11!null 1036 │ ├── grouping columns: s_i_id:11!null 1037 │ ├── key: (11) 1038 │ └── inner-join (lookup stock) 1039 │ ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null ol_i_id:5!null s_i_id:11!null s_w_id:12!null s_quantity:13!null 1040 │ ├── key columns: [3 5] = [12 11] 1041 │ ├── lookup columns are key 1042 │ ├── fd: ()-->(2,3,12), (11)-->(13), (5)==(11), (11)==(5), (3)==(12), (12)==(3) 1043 │ ├── scan order_line 1044 │ │ ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null ol_i_id:5!null 1045 │ │ ├── constraint: /3/2/-1/4: [/10/100/999 - /10/100/980] 1046 │ │ └── fd: ()-->(2,3) 1047 │ └── filters 1048 │ ├── s_w_id:12 = 10 [outer=(12), constraints=(/12: [/10 - /10]; tight), fd=()-->(12)] 1049 │ └── s_quantity:13 < 15 [outer=(13), constraints=(/13: (/NULL - /14]; tight)] 1050 └── aggregations 1051 └── count-rows [as=count:28] 1052 1053 # -------------------------------------------------- 1054 # Consistency Queries 1055 # 1056 # These queries run after TPCC in order to check database consistency. 1057 # They are not part of the benchmark itself. 1058 # -------------------------------------------------- 1059 opt format=hide-qual 1060 SELECT count(*) 1061 FROM warehouse 1062 FULL OUTER JOIN 1063 ( 1064 SELECT d_w_id, sum(d_ytd) as sum_d_ytd 1065 FROM district 1066 GROUP BY d_w_id 1067 ) 1068 ON (w_id = d_w_id) 1069 WHERE w_ytd != sum_d_ytd 1070 ---- 1071 scalar-group-by 1072 ├── columns: count:22!null 1073 ├── cardinality: [1 - 1] 1074 ├── key: () 1075 ├── fd: ()-->(22) 1076 ├── inner-join (lookup warehouse) 1077 │ ├── columns: w_id:1!null w_ytd:9!null d_w_id:11!null sum:21!null 1078 │ ├── key columns: [11] = [1] 1079 │ ├── lookup columns are key 1080 │ ├── key: (11) 1081 │ ├── fd: (1)-->(9), (11)-->(21), (1)==(11), (11)==(1) 1082 │ ├── group-by 1083 │ │ ├── columns: d_w_id:11!null sum:21 1084 │ │ ├── grouping columns: d_w_id:11!null 1085 │ │ ├── internal-ordering: +11 1086 │ │ ├── key: (11) 1087 │ │ ├── fd: (11)-->(21) 1088 │ │ ├── scan district 1089 │ │ │ ├── columns: d_w_id:11!null d_ytd:19 1090 │ │ │ └── ordering: +11 1091 │ │ └── aggregations 1092 │ │ └── sum [as=sum:21, outer=(19)] 1093 │ │ └── d_ytd:19 1094 │ └── filters 1095 │ └── w_ytd:9 != sum:21 [outer=(9,21), constraints=(/9: (/NULL - ]; /21: (/NULL - ])] 1096 └── aggregations 1097 └── count-rows [as=count_rows:22] 1098 1099 opt format=hide-qual 1100 SELECT d_next_o_id 1101 FROM district 1102 ORDER BY d_w_id, d_id 1103 ---- 1104 scan district 1105 ├── columns: d_next_o_id:11 [hidden: d_id:1!null d_w_id:2!null] 1106 ├── key: (1,2) 1107 ├── fd: (1,2)-->(11) 1108 └── ordering: +2,+1 1109 1110 opt format=hide-qual 1111 SELECT max(no_o_id) 1112 FROM new_order 1113 GROUP BY no_d_id, no_w_id 1114 ORDER BY no_w_id, no_d_id 1115 ---- 1116 group-by 1117 ├── columns: max:4!null [hidden: no_d_id:2!null no_w_id:3!null] 1118 ├── grouping columns: no_d_id:2!null no_w_id:3!null 1119 ├── key: (2,3) 1120 ├── fd: (2,3)-->(4) 1121 ├── ordering: +3,+2 1122 ├── scan new_order 1123 │ ├── columns: no_o_id:1!null no_d_id:2!null no_w_id:3!null 1124 │ ├── key: (1-3) 1125 │ └── ordering: +3,+2 1126 └── aggregations 1127 └── max [as=max:4, outer=(1)] 1128 └── no_o_id:1 1129 1130 opt format=hide-qual 1131 SELECT max(o_id) 1132 FROM "order" 1133 GROUP BY o_d_id, o_w_id 1134 ORDER BY o_w_id, o_d_id 1135 ---- 1136 group-by 1137 ├── columns: max:9!null [hidden: o_d_id:2!null o_w_id:3!null] 1138 ├── grouping columns: o_d_id:2!null o_w_id:3!null 1139 ├── key: (2,3) 1140 ├── fd: (2,3)-->(9) 1141 ├── ordering: +3,+2 1142 ├── scan "order"@order_idx 1143 │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null 1144 │ ├── key: (1-3) 1145 │ └── ordering: +3,+2 1146 └── aggregations 1147 └── max [as=max:9, outer=(1)] 1148 └── o_id:1 1149 1150 opt format=hide-qual 1151 SELECT count(*) 1152 FROM 1153 ( 1154 SELECT max(no_o_id) - min(no_o_id) - count(*) AS nod 1155 FROM new_order 1156 GROUP BY no_w_id, no_d_id 1157 ) 1158 WHERE nod != -1 1159 ---- 1160 scalar-group-by 1161 ├── columns: count:8!null 1162 ├── cardinality: [1 - 1] 1163 ├── key: () 1164 ├── fd: ()-->(8) 1165 ├── select 1166 │ ├── columns: no_d_id:2!null no_w_id:3!null max:4!null min:5!null count_rows:6!null 1167 │ ├── key: (2,3) 1168 │ ├── fd: (2,3)-->(4-6) 1169 │ ├── group-by 1170 │ │ ├── columns: no_d_id:2!null no_w_id:3!null max:4!null min:5!null count_rows:6!null 1171 │ │ ├── grouping columns: no_d_id:2!null no_w_id:3!null 1172 │ │ ├── internal-ordering: +3,+2 1173 │ │ ├── key: (2,3) 1174 │ │ ├── fd: (2,3)-->(4-6) 1175 │ │ ├── scan new_order 1176 │ │ │ ├── columns: no_o_id:1!null no_d_id:2!null no_w_id:3!null 1177 │ │ │ ├── key: (1-3) 1178 │ │ │ └── ordering: +3,+2 1179 │ │ └── aggregations 1180 │ │ ├── max [as=max:4, outer=(1)] 1181 │ │ │ └── no_o_id:1 1182 │ │ ├── min [as=min:5, outer=(1)] 1183 │ │ │ └── no_o_id:1 1184 │ │ └── count-rows [as=count_rows:6] 1185 │ └── filters 1186 │ └── ((max:4 - min:5) - count_rows:6) != -1 [outer=(4-6)] 1187 └── aggregations 1188 └── count-rows [as=count_rows:8] 1189 1190 opt format=hide-qual 1191 SELECT sum(o_ol_cnt) 1192 FROM "order" 1193 GROUP BY o_w_id, o_d_id 1194 ORDER BY o_w_id, o_d_id 1195 ---- 1196 group-by 1197 ├── columns: sum:9 [hidden: o_d_id:2!null o_w_id:3!null] 1198 ├── grouping columns: o_d_id:2!null o_w_id:3!null 1199 ├── key: (2,3) 1200 ├── fd: (2,3)-->(9) 1201 ├── ordering: +3,+2 1202 ├── scan "order" 1203 │ ├── columns: o_d_id:2!null o_w_id:3!null o_ol_cnt:7 1204 │ └── ordering: +3,+2 1205 └── aggregations 1206 └── sum [as=sum:9, outer=(7)] 1207 └── o_ol_cnt:7 1208 1209 opt format=hide-qual 1210 SELECT count(*) 1211 FROM order_line 1212 GROUP BY ol_w_id, ol_d_id 1213 ORDER BY ol_w_id, ol_d_id 1214 ---- 1215 group-by 1216 ├── columns: count:11!null [hidden: ol_d_id:2!null ol_w_id:3!null] 1217 ├── grouping columns: ol_d_id:2!null ol_w_id:3!null 1218 ├── key: (2,3) 1219 ├── fd: (2,3)-->(11) 1220 ├── ordering: +3,+2 1221 ├── scan order_line 1222 │ ├── columns: ol_d_id:2!null ol_w_id:3!null 1223 │ └── ordering: +3,+2 1224 └── aggregations 1225 └── count-rows [as=count_rows:11] 1226 1227 opt format=hide-qual 1228 (SELECT no_w_id, no_d_id, no_o_id FROM new_order) 1229 EXCEPT ALL 1230 (SELECT o_w_id, o_d_id, o_id FROM "order" WHERE o_carrier_id IS NULL) 1231 ---- 1232 except-all 1233 ├── columns: no_w_id:3!null no_d_id:2!null no_o_id:1!null 1234 ├── left columns: no_w_id:3!null no_d_id:2!null no_o_id:1!null 1235 ├── right columns: o_w_id:6 o_d_id:5 o_id:4 1236 ├── scan new_order 1237 │ ├── columns: no_o_id:1!null no_d_id:2!null no_w_id:3!null 1238 │ └── key: (1-3) 1239 └── project 1240 ├── columns: o_id:4!null o_d_id:5!null o_w_id:6!null 1241 ├── key: (4-6) 1242 └── select 1243 ├── columns: o_id:4!null o_d_id:5!null o_w_id:6!null o_carrier_id:9 1244 ├── key: (4-6) 1245 ├── fd: ()-->(9) 1246 ├── scan "order"@order_idx 1247 │ ├── columns: o_id:4!null o_d_id:5!null o_w_id:6!null o_carrier_id:9 1248 │ ├── key: (4-6) 1249 │ └── fd: (4-6)-->(9) 1250 └── filters 1251 └── o_carrier_id:9 IS NULL [outer=(9), constraints=(/9: [/NULL - /NULL]; tight), fd=()-->(9)] 1252 1253 opt format=hide-qual 1254 (SELECT o_w_id, o_d_id, o_id FROM "order" WHERE o_carrier_id IS NULL) 1255 EXCEPT ALL 1256 (SELECT no_w_id, no_d_id, no_o_id FROM new_order) 1257 ---- 1258 except-all 1259 ├── columns: o_w_id:3!null o_d_id:2!null o_id:1!null 1260 ├── left columns: o_w_id:3!null o_d_id:2!null o_id:1!null 1261 ├── right columns: no_w_id:11 no_d_id:10 no_o_id:9 1262 ├── project 1263 │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null 1264 │ ├── key: (1-3) 1265 │ └── select 1266 │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_carrier_id:6 1267 │ ├── key: (1-3) 1268 │ ├── fd: ()-->(6) 1269 │ ├── scan "order"@order_idx 1270 │ │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_carrier_id:6 1271 │ │ ├── key: (1-3) 1272 │ │ └── fd: (1-3)-->(6) 1273 │ └── filters 1274 │ └── o_carrier_id:6 IS NULL [outer=(6), constraints=(/6: [/NULL - /NULL]; tight), fd=()-->(6)] 1275 └── scan new_order 1276 ├── columns: no_o_id:9!null no_d_id:10!null no_w_id:11!null 1277 └── key: (9-11) 1278 1279 opt format=hide-qual 1280 ( 1281 SELECT o_w_id, o_d_id, o_id, o_ol_cnt 1282 FROM "order" 1283 ORDER BY o_w_id, o_d_id, o_id DESC 1284 ) 1285 EXCEPT ALL 1286 ( 1287 SELECT ol_w_id, ol_d_id, ol_o_id, count(*) 1288 FROM order_line 1289 GROUP BY (ol_w_id, ol_d_id, ol_o_id) 1290 ORDER BY ol_w_id, ol_d_id, ol_o_id DESC 1291 ) 1292 ---- 1293 except-all 1294 ├── columns: o_w_id:3!null o_d_id:2!null o_id:1!null o_ol_cnt:7 1295 ├── left columns: o_w_id:3!null o_d_id:2!null o_id:1!null o_ol_cnt:7 1296 ├── right columns: ol_w_id:11 ol_d_id:10 ol_o_id:9 count_rows:19 1297 ├── scan "order" 1298 │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_ol_cnt:7 1299 │ ├── key: (1-3) 1300 │ └── fd: (1-3)-->(7) 1301 └── group-by 1302 ├── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null count_rows:19!null 1303 ├── grouping columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null 1304 ├── key: (9-11) 1305 ├── fd: (9-11)-->(19) 1306 ├── scan order_line@order_line_stock_fk_idx 1307 │ └── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null 1308 └── aggregations 1309 └── count-rows [as=count_rows:19] 1310 1311 opt format=hide-qual 1312 ( 1313 SELECT ol_w_id, ol_d_id, ol_o_id, count(*) 1314 FROM order_line 1315 GROUP BY (ol_w_id, ol_d_id, ol_o_id) 1316 ORDER BY ol_w_id, ol_d_id, ol_o_id DESC 1317 ) 1318 EXCEPT ALL 1319 ( 1320 SELECT o_w_id, o_d_id, o_id, o_ol_cnt 1321 FROM "order" 1322 ORDER BY o_w_id, o_d_id, o_id DESC 1323 ) 1324 ---- 1325 except-all 1326 ├── columns: ol_w_id:3!null ol_d_id:2!null ol_o_id:1!null count:11 1327 ├── left columns: ol_w_id:3!null ol_d_id:2!null ol_o_id:1!null count_rows:11 1328 ├── right columns: o_w_id:14 o_d_id:13 o_id:12 o_ol_cnt:18 1329 ├── group-by 1330 │ ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null count_rows:11!null 1331 │ ├── grouping columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null 1332 │ ├── key: (1-3) 1333 │ ├── fd: (1-3)-->(11) 1334 │ ├── scan order_line@order_line_stock_fk_idx 1335 │ │ └── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null 1336 │ └── aggregations 1337 │ └── count-rows [as=count_rows:11] 1338 └── scan "order" 1339 ├── columns: o_id:12!null o_d_id:13!null o_w_id:14!null o_ol_cnt:18 1340 ├── key: (12-14) 1341 └── fd: (12-14)-->(18) 1342 1343 opt format=hide-qual 1344 SELECT count(*) 1345 FROM 1346 ( 1347 SELECT o_w_id, o_d_id, o_id 1348 FROM "order" 1349 WHERE o_carrier_id IS NULL 1350 ) 1351 FULL OUTER JOIN 1352 ( 1353 SELECT ol_w_id, ol_d_id, ol_o_id 1354 FROM order_line 1355 WHERE ol_delivery_d IS NULL 1356 ) 1357 ON (ol_w_id = o_w_id AND ol_d_id = o_d_id AND ol_o_id = o_id) 1358 WHERE ol_o_id IS NULL OR o_id IS NULL 1359 ---- 1360 scalar-group-by 1361 ├── columns: count:19!null 1362 ├── cardinality: [1 - 1] 1363 ├── key: () 1364 ├── fd: ()-->(19) 1365 ├── select 1366 │ ├── columns: o_id:1 o_d_id:2 o_w_id:3 ol_o_id:9 ol_d_id:10 ol_w_id:11 1367 │ ├── full-join (hash) 1368 │ │ ├── columns: o_id:1 o_d_id:2 o_w_id:3 ol_o_id:9 ol_d_id:10 ol_w_id:11 1369 │ │ ├── project 1370 │ │ │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null 1371 │ │ │ ├── key: (1-3) 1372 │ │ │ └── select 1373 │ │ │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_carrier_id:6 1374 │ │ │ ├── key: (1-3) 1375 │ │ │ ├── fd: ()-->(6) 1376 │ │ │ ├── scan "order"@order_idx 1377 │ │ │ │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_carrier_id:6 1378 │ │ │ │ ├── key: (1-3) 1379 │ │ │ │ └── fd: (1-3)-->(6) 1380 │ │ │ └── filters 1381 │ │ │ └── o_carrier_id:6 IS NULL [outer=(6), constraints=(/6: [/NULL - /NULL]; tight), fd=()-->(6)] 1382 │ │ ├── project 1383 │ │ │ ├── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null 1384 │ │ │ └── select 1385 │ │ │ ├── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null ol_delivery_d:15 1386 │ │ │ ├── fd: ()-->(15) 1387 │ │ │ ├── scan order_line 1388 │ │ │ │ └── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null ol_delivery_d:15 1389 │ │ │ └── filters 1390 │ │ │ └── ol_delivery_d:15 IS NULL [outer=(15), constraints=(/15: [/NULL - /NULL]; tight), fd=()-->(15)] 1391 │ │ └── filters 1392 │ │ ├── ol_w_id:11 = o_w_id:3 [outer=(3,11), constraints=(/3: (/NULL - ]; /11: (/NULL - ]), fd=(3)==(11), (11)==(3)] 1393 │ │ ├── ol_d_id:10 = o_d_id:2 [outer=(2,10), constraints=(/2: (/NULL - ]; /10: (/NULL - ]), fd=(2)==(10), (10)==(2)] 1394 │ │ └── ol_o_id:9 = o_id:1 [outer=(1,9), constraints=(/1: (/NULL - ]; /9: (/NULL - ]), fd=(1)==(9), (9)==(1)] 1395 │ └── filters 1396 │ └── (ol_o_id:9 IS NULL) OR (o_id:1 IS NULL) [outer=(1,9)] 1397 └── aggregations 1398 └── count-rows [as=count_rows:19]