github.com/dolthub/go-mysql-server@v0.18.0/enginetest/queries/tpcc_plans.go (about) 1 // Code generated by plangen. 2 3 // Copyright 2024 Dolthub, Inc. 4 // 5 // Licensed under the Apache License, Version 2.0 (the "License"); 6 // you may not use this file except in compliance with the License. 7 // You may obtain a copy of the License at 8 // 9 // http://www.apache.org/licenses/LICENSE-2.0 10 // 11 // Unless required by applicable law or agreed to in writing, software 12 // distributed under the License is distributed on an "AS IS" BASIS, 13 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 14 // See the License for the specific language governing permissions and 15 // limitations under the License. 16 17 package queries 18 19 var TpccPlanTests = []QueryPlanTest{ 20 { 21 Query: ` 22 -- cycle 1a 23 SELECT c_discount, c_last, c_credit, w_tax FROM customer2, warehouse2 WHERE w_id = 1 AND c_w_id = w_id AND c_d_id = 9 AND c_id = 2151`, 24 ExpectedPlan: "Project\n" + 25 " ├─ columns: [customer2.c_discount:7, customer2.c_last:5, customer2.c_credit:6, warehouse2.w_tax:1]\n" + 26 " └─ LookupJoin\n" + 27 " ├─ IndexedTableAccess(warehouse2)\n" + 28 " │ ├─ index: [warehouse2.w_id]\n" + 29 " │ ├─ static: [{[1, 1]}]\n" + 30 " │ ├─ colSet: (22-30)\n" + 31 " │ ├─ tableId: 2\n" + 32 " │ └─ Table\n" + 33 " │ ├─ name: warehouse2\n" + 34 " │ └─ columns: [w_id w_tax]\n" + 35 " └─ Filter\n" + 36 " ├─ AND\n" + 37 " │ ├─ Eq\n" + 38 " │ │ ├─ customer2.c_d_id:1!null\n" + 39 " │ │ └─ 9 (tinyint)\n" + 40 " │ └─ Eq\n" + 41 " │ ├─ customer2.c_id:0!null\n" + 42 " │ └─ 2151 (smallint)\n" + 43 " └─ IndexedTableAccess(customer2)\n" + 44 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_id]\n" + 45 " ├─ keys: [warehouse2.w_id:0!null 9 (tinyint) 2151 (smallint)]\n" + 46 " ├─ colSet: (1-21)\n" + 47 " ├─ tableId: 1\n" + 48 " └─ Table\n" + 49 " ├─ name: customer2\n" + 50 " └─ columns: [c_id c_d_id c_w_id c_last c_credit c_discount]\n" + 51 "", 52 ExpectedEstimates: "Project\n" + 53 " ├─ columns: [customer2.c_discount, customer2.c_last, customer2.c_credit, warehouse2.w_tax]\n" + 54 " └─ LookupJoin (estimated cost=3.300 rows=1)\n" + 55 " ├─ IndexedTableAccess(warehouse2)\n" + 56 " │ ├─ index: [warehouse2.w_id]\n" + 57 " │ ├─ filters: [{[1, 1]}]\n" + 58 " │ └─ columns: [w_id w_tax]\n" + 59 " └─ Filter\n" + 60 " ├─ ((customer2.c_d_id = 9) AND (customer2.c_id = 2151))\n" + 61 " └─ IndexedTableAccess(customer2)\n" + 62 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_id]\n" + 63 " ├─ columns: [c_id c_d_id c_w_id c_last c_credit c_discount]\n" + 64 " └─ keys: warehouse2.w_id, 9, 2151\n" + 65 "", 66 ExpectedAnalysis: "Project\n" + 67 " ├─ columns: [customer2.c_discount, customer2.c_last, customer2.c_credit, warehouse2.w_tax]\n" + 68 " └─ LookupJoin (estimated cost=3.300 rows=1) (actual rows=0 loops=1)\n" + 69 " ├─ IndexedTableAccess(warehouse2)\n" + 70 " │ ├─ index: [warehouse2.w_id]\n" + 71 " │ ├─ filters: [{[1, 1]}]\n" + 72 " │ └─ columns: [w_id w_tax]\n" + 73 " └─ Filter\n" + 74 " ├─ ((customer2.c_d_id = 9) AND (customer2.c_id = 2151))\n" + 75 " └─ IndexedTableAccess(customer2)\n" + 76 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_id]\n" + 77 " ├─ columns: [c_id c_d_id c_w_id c_last c_credit c_discount]\n" + 78 " └─ keys: warehouse2.w_id, 9, 2151\n" + 79 "", 80 }, 81 { 82 Query: `SELECT d_next_o_id, d_tax FROM district2 WHERE d_w_id = 1 AND d_id = 9 FOR UPDATE`, 83 ExpectedPlan: "Project\n" + 84 " ├─ columns: [district2.d_next_o_id:3, district2.d_tax:2]\n" + 85 " └─ IndexedTableAccess(district2)\n" + 86 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 87 " ├─ static: [{[1, 1], [9, 9]}]\n" + 88 " ├─ colSet: (1-11)\n" + 89 " ├─ tableId: 1\n" + 90 " └─ Table\n" + 91 " ├─ name: district2\n" + 92 " └─ columns: [d_id d_w_id d_tax d_next_o_id]\n" + 93 "", 94 ExpectedEstimates: "Project\n" + 95 " ├─ columns: [district2.d_next_o_id, district2.d_tax]\n" + 96 " └─ IndexedTableAccess(district2)\n" + 97 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 98 " ├─ filters: [{[1, 1], [9, 9]}]\n" + 99 " └─ columns: [d_id d_w_id d_tax d_next_o_id]\n" + 100 "", 101 ExpectedAnalysis: "Project\n" + 102 " ├─ columns: [district2.d_next_o_id, district2.d_tax]\n" + 103 " └─ IndexedTableAccess(district2)\n" + 104 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 105 " ├─ filters: [{[1, 1], [9, 9]}]\n" + 106 " └─ columns: [d_id d_w_id d_tax d_next_o_id]\n" + 107 "", 108 }, 109 { 110 Query: `UPDATE district2 SET d_next_o_id = 3002 WHERE d_id = 9 AND d_w_id= 1`, 111 ExpectedPlan: "RowUpdateAccumulator\n" + 112 " └─ Update\n" + 113 " └─ UpdateSource(SET district2.d_next_o_id:10 = 3002 (smallint))\n" + 114 " └─ IndexedTableAccess(district2)\n" + 115 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 116 " ├─ static: [{[1, 1], [9, 9]}]\n" + 117 " ├─ colSet: (1-11)\n" + 118 " ├─ tableId: 1\n" + 119 " └─ Table\n" + 120 " ├─ name: district2\n" + 121 " └─ columns: [d_id d_w_id d_name d_street_1 d_street_2 d_city d_state d_zip d_tax d_ytd d_next_o_id]\n" + 122 "", 123 }, 124 { 125 Query: `INSERT INTO orders2 (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (3001,9,1,2151,NOW(),12,1)`, 126 ExpectedPlan: "RowUpdateAccumulator\n" + 127 " └─ Insert(o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local)\n" + 128 " ├─ InsertDestination\n" + 129 " │ └─ ProcessTable\n" + 130 " │ └─ Table\n" + 131 " │ ├─ name: orders2\n" + 132 " │ └─ columns: [o_id o_d_id o_w_id o_c_id o_entry_d o_carrier_id o_ol_cnt o_all_local]\n" + 133 " └─ Project\n" + 134 " ├─ columns: [o_id:0!null, o_d_id:1!null, o_w_id:2!null, o_c_id:3, o_entry_d:4, , o_ol_cnt:5, o_all_local:6]\n" + 135 " └─ Values([3001 (smallint),9 (tinyint),1 (tinyint),2151 (smallint),NOW(),12 (tinyint),1 (tinyint)])\n" + 136 "", 137 }, 138 { 139 Query: `INSERT INTO new_orders2 (no_o_id, no_d_id, no_w_id) VALUES (3001,9,1)`, 140 ExpectedPlan: "RowUpdateAccumulator\n" + 141 " └─ Insert(no_o_id, no_d_id, no_w_id)\n" + 142 " ├─ InsertDestination\n" + 143 " │ └─ ProcessTable\n" + 144 " │ └─ Table\n" + 145 " │ ├─ name: new_orders2\n" + 146 " │ └─ columns: [no_o_id no_d_id no_w_id]\n" + 147 " └─ Project\n" + 148 " ├─ columns: [no_o_id:0!null, no_d_id:1!null, no_w_id:2!null]\n" + 149 " └─ Values([3001 (smallint),9 (tinyint),1 (tinyint)])\n" + 150 "", 151 }, 152 { 153 Query: `SELECT i_price, i_name, i_data FROM item2 WHERE i_id = 2532`, 154 ExpectedPlan: "Project\n" + 155 " ├─ columns: [item2.i_price:2, item2.i_name:1, item2.i_data:3]\n" + 156 " └─ IndexedTableAccess(item2)\n" + 157 " ├─ index: [item2.i_id]\n" + 158 " ├─ static: [{[2532, 2532]}]\n" + 159 " ├─ colSet: (1-5)\n" + 160 " ├─ tableId: 1\n" + 161 " └─ Table\n" + 162 " ├─ name: item2\n" + 163 " └─ columns: [i_id i_name i_price i_data]\n" + 164 "", 165 ExpectedEstimates: "Project\n" + 166 " ├─ columns: [item2.i_price, item2.i_name, item2.i_data]\n" + 167 " └─ IndexedTableAccess(item2)\n" + 168 " ├─ index: [item2.i_id]\n" + 169 " ├─ filters: [{[2532, 2532]}]\n" + 170 " └─ columns: [i_id i_name i_price i_data]\n" + 171 "", 172 ExpectedAnalysis: "Project\n" + 173 " ├─ columns: [item2.i_price, item2.i_name, item2.i_data]\n" + 174 " └─ IndexedTableAccess(item2)\n" + 175 " ├─ index: [item2.i_id]\n" + 176 " ├─ filters: [{[2532, 2532]}]\n" + 177 " └─ columns: [i_id i_name i_price i_data]\n" + 178 "", 179 }, 180 { 181 Query: `SELECT s_quantity, s_data, s_dist_09 s_dist FROM stock2 WHERE s_i_id = 2532 AND s_w_id= 1 FOR UPDATE`, 182 ExpectedPlan: "Project\n" + 183 " ├─ columns: [stock2.s_quantity:2, stock2.s_data:4, stock2.s_dist_09:3 as s_dist]\n" + 184 " └─ Filter\n" + 185 " ├─ Eq\n" + 186 " │ ├─ stock2.s_w_id:1!null\n" + 187 " │ └─ 1 (tinyint)\n" + 188 " └─ IndexedTableAccess(stock2)\n" + 189 " ├─ index: [stock2.s_i_id]\n" + 190 " ├─ static: [{[2532, 2532]}]\n" + 191 " ├─ colSet: (1-17)\n" + 192 " ├─ tableId: 1\n" + 193 " └─ Table\n" + 194 " ├─ name: stock2\n" + 195 " └─ columns: [s_i_id s_w_id s_quantity s_dist_09 s_data]\n" + 196 "", 197 ExpectedEstimates: "Project\n" + 198 " ├─ columns: [stock2.s_quantity, stock2.s_data, stock2.s_dist_09 as s_dist]\n" + 199 " └─ Filter\n" + 200 " ├─ (stock2.s_w_id = 1)\n" + 201 " └─ IndexedTableAccess(stock2)\n" + 202 " ├─ index: [stock2.s_i_id]\n" + 203 " ├─ filters: [{[2532, 2532]}]\n" + 204 " └─ columns: [s_i_id s_w_id s_quantity s_dist_09 s_data]\n" + 205 "", 206 ExpectedAnalysis: "Project\n" + 207 " ├─ columns: [stock2.s_quantity, stock2.s_data, stock2.s_dist_09 as s_dist]\n" + 208 " └─ Filter\n" + 209 " ├─ (stock2.s_w_id = 1)\n" + 210 " └─ IndexedTableAccess(stock2)\n" + 211 " ├─ index: [stock2.s_i_id]\n" + 212 " ├─ filters: [{[2532, 2532]}]\n" + 213 " └─ columns: [s_i_id s_w_id s_quantity s_dist_09 s_data]\n" + 214 "", 215 }, 216 { 217 Query: `UPDATE stock2 SET s_quantity = 39 WHERE s_i_id = 2532 AND s_w_id= 1`, 218 ExpectedPlan: "RowUpdateAccumulator\n" + 219 " └─ Update\n" + 220 " └─ UpdateSource(SET stock2.s_quantity:2 = 39 (tinyint))\n" + 221 " └─ Filter\n" + 222 " ├─ Eq\n" + 223 " │ ├─ stock2.s_w_id:1!null\n" + 224 " │ └─ 1 (tinyint)\n" + 225 " └─ IndexedTableAccess(stock2)\n" + 226 " ├─ index: [stock2.s_i_id]\n" + 227 " ├─ static: [{[2532, 2532]}]\n" + 228 " ├─ colSet: (1-17)\n" + 229 " ├─ tableId: 1\n" + 230 " └─ Table\n" + 231 " ├─ name: stock2\n" + 232 " └─ columns: [s_i_id s_w_id s_quantity s_dist_01 s_dist_02 s_dist_03 s_dist_04 s_dist_05 s_dist_06 s_dist_07 s_dist_08 s_dist_09 s_dist_10 s_ytd s_order_cnt s_remote_cnt s_data]\n" + 233 "", 234 }, 235 { 236 Query: `INSERT INTO order_line2 (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) VALUES ( 237 3001,9,1,1,2532,1,5,301,'kkkkkkkkkkkkkkkkkkkkkkkk')`, 238 ExpectedPlan: "RowUpdateAccumulator\n" + 239 " └─ Insert(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)\n" + 240 " ├─ InsertDestination\n" + 241 " │ └─ ProcessTable\n" + 242 " │ └─ Table\n" + 243 " │ ├─ name: order_line2\n" + 244 " │ └─ columns: [ol_o_id ol_d_id ol_w_id ol_number ol_i_id ol_supply_w_id ol_delivery_d ol_quantity ol_amount ol_dist_info]\n" + 245 " └─ Project\n" + 246 " ├─ columns: [ol_o_id:0!null, ol_d_id:1!null, ol_w_id:2!null, ol_number:3!null, ol_i_id:4, ol_supply_w_id:5, , ol_quantity:6, ol_amount:7, ol_dist_info:8]\n" + 247 " └─ Values([3001 (smallint),9 (tinyint),1 (tinyint),1 (tinyint),2532 (smallint),1 (tinyint),5 (tinyint),301 (smallint),kkkkkkkkkkkkkkkkkkkkkkkk (longtext)])\n" + 248 "", 249 }, 250 { 251 Query: ` 252 -- cycle 1b 253 SELECT i_price, i_name, i_data FROM item2 WHERE i_id = 2532`, 254 ExpectedPlan: "Project\n" + 255 " ├─ columns: [item2.i_price:2, item2.i_name:1, item2.i_data:3]\n" + 256 " └─ IndexedTableAccess(item2)\n" + 257 " ├─ index: [item2.i_id]\n" + 258 " ├─ static: [{[2532, 2532]}]\n" + 259 " ├─ colSet: (1-5)\n" + 260 " ├─ tableId: 1\n" + 261 " └─ Table\n" + 262 " ├─ name: item2\n" + 263 " └─ columns: [i_id i_name i_price i_data]\n" + 264 "", 265 ExpectedEstimates: "Project\n" + 266 " ├─ columns: [item2.i_price, item2.i_name, item2.i_data]\n" + 267 " └─ IndexedTableAccess(item2)\n" + 268 " ├─ index: [item2.i_id]\n" + 269 " ├─ filters: [{[2532, 2532]}]\n" + 270 " └─ columns: [i_id i_name i_price i_data]\n" + 271 "", 272 ExpectedAnalysis: "Project\n" + 273 " ├─ columns: [item2.i_price, item2.i_name, item2.i_data]\n" + 274 " └─ IndexedTableAccess(item2)\n" + 275 " ├─ index: [item2.i_id]\n" + 276 " ├─ filters: [{[2532, 2532]}]\n" + 277 " └─ columns: [i_id i_name i_price i_data]\n" + 278 "", 279 }, 280 { 281 Query: `SELECT s_quantity, s_data, s_dist_09 s_dist FROM stock2 WHERE s_i_id = 2532 AND s_w_id= 1 FOR UPDATE`, 282 ExpectedPlan: "Project\n" + 283 " ├─ columns: [stock2.s_quantity:2, stock2.s_data:4, stock2.s_dist_09:3 as s_dist]\n" + 284 " └─ Filter\n" + 285 " ├─ Eq\n" + 286 " │ ├─ stock2.s_w_id:1!null\n" + 287 " │ └─ 1 (tinyint)\n" + 288 " └─ IndexedTableAccess(stock2)\n" + 289 " ├─ index: [stock2.s_i_id]\n" + 290 " ├─ static: [{[2532, 2532]}]\n" + 291 " ├─ colSet: (1-17)\n" + 292 " ├─ tableId: 1\n" + 293 " └─ Table\n" + 294 " ├─ name: stock2\n" + 295 " └─ columns: [s_i_id s_w_id s_quantity s_dist_09 s_data]\n" + 296 "", 297 ExpectedEstimates: "Project\n" + 298 " ├─ columns: [stock2.s_quantity, stock2.s_data, stock2.s_dist_09 as s_dist]\n" + 299 " └─ Filter\n" + 300 " ├─ (stock2.s_w_id = 1)\n" + 301 " └─ IndexedTableAccess(stock2)\n" + 302 " ├─ index: [stock2.s_i_id]\n" + 303 " ├─ filters: [{[2532, 2532]}]\n" + 304 " └─ columns: [s_i_id s_w_id s_quantity s_dist_09 s_data]\n" + 305 "", 306 ExpectedAnalysis: "Project\n" + 307 " ├─ columns: [stock2.s_quantity, stock2.s_data, stock2.s_dist_09 as s_dist]\n" + 308 " └─ Filter\n" + 309 " ├─ (stock2.s_w_id = 1)\n" + 310 " └─ IndexedTableAccess(stock2)\n" + 311 " ├─ index: [stock2.s_i_id]\n" + 312 " ├─ filters: [{[2532, 2532]}]\n" + 313 " └─ columns: [s_i_id s_w_id s_quantity s_dist_09 s_data]\n" + 314 "", 315 }, 316 { 317 Query: `UPDATE stock2 SET s_quantity = 5 WHERE s_i_id = 64568 AND s_w_id= 1`, 318 ExpectedPlan: "RowUpdateAccumulator\n" + 319 " └─ Update\n" + 320 " └─ UpdateSource(SET stock2.s_quantity:2 = 5 (tinyint))\n" + 321 " └─ Filter\n" + 322 " ├─ Eq\n" + 323 " │ ├─ stock2.s_w_id:1!null\n" + 324 " │ └─ 1 (tinyint)\n" + 325 " └─ IndexedTableAccess(stock2)\n" + 326 " ├─ index: [stock2.s_i_id]\n" + 327 " ├─ static: [{[64568, 64568]}]\n" + 328 " ├─ colSet: (1-17)\n" + 329 " ├─ tableId: 1\n" + 330 " └─ Table\n" + 331 " ├─ name: stock2\n" + 332 " └─ columns: [s_i_id s_w_id s_quantity s_dist_01 s_dist_02 s_dist_03 s_dist_04 s_dist_05 s_dist_06 s_dist_07 s_dist_08 s_dist_09 s_dist_10 s_ytd s_order_cnt s_remote_cnt s_data]\n" + 333 "", 334 }, 335 { 336 Query: `INSERT INTO order_line2 (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) VALUES ( 337 3001,9,1,11,64568,1,7,298,'oooooooooooooooooooooooo')`, 338 ExpectedPlan: "RowUpdateAccumulator\n" + 339 " └─ Insert(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)\n" + 340 " ├─ InsertDestination\n" + 341 " │ └─ ProcessTable\n" + 342 " │ └─ Table\n" + 343 " │ ├─ name: order_line2\n" + 344 " │ └─ columns: [ol_o_id ol_d_id ol_w_id ol_number ol_i_id ol_supply_w_id ol_delivery_d ol_quantity ol_amount ol_dist_info]\n" + 345 " └─ Project\n" + 346 " ├─ columns: [ol_o_id:0!null, ol_d_id:1!null, ol_w_id:2!null, ol_number:3!null, ol_i_id:4, ol_supply_w_id:5, , ol_quantity:6, ol_amount:7, ol_dist_info:8]\n" + 347 " └─ Values([3001 (smallint),9 (tinyint),1 (tinyint),11 (tinyint),64568 (smallint unsigned),1 (tinyint),7 (tinyint),298 (smallint),oooooooooooooooooooooooo (longtext)])\n" + 348 "", 349 }, 350 { 351 Query: ` 352 -- cycle 2 353 UPDATE warehouse2 SET w_ytd = w_ytd + 1767 WHERE w_id = 1`, 354 ExpectedPlan: "RowUpdateAccumulator\n" + 355 " └─ Update\n" + 356 " └─ UpdateSource(SET warehouse2.w_ytd:8 = (warehouse2.w_ytd:8 + 1767 (smallint)))\n" + 357 " └─ IndexedTableAccess(warehouse2)\n" + 358 " ├─ index: [warehouse2.w_id]\n" + 359 " ├─ static: [{[1, 1]}]\n" + 360 " ├─ colSet: (1-9)\n" + 361 " ├─ tableId: 1\n" + 362 " └─ Table\n" + 363 " ├─ name: warehouse2\n" + 364 " └─ columns: [w_id w_name w_street_1 w_street_2 w_city w_state w_zip w_tax w_ytd]\n" + 365 "", 366 }, 367 { 368 Query: `SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name FROM warehouse2 WHERE w_id = 1`, 369 ExpectedPlan: "Project\n" + 370 " ├─ columns: [warehouse2.w_street_1:2, warehouse2.w_street_2:3, warehouse2.w_city:4, warehouse2.w_state:5, warehouse2.w_zip:6, warehouse2.w_name:1]\n" + 371 " └─ IndexedTableAccess(warehouse2)\n" + 372 " ├─ index: [warehouse2.w_id]\n" + 373 " ├─ static: [{[1, 1]}]\n" + 374 " ├─ colSet: (1-9)\n" + 375 " ├─ tableId: 1\n" + 376 " └─ Table\n" + 377 " ├─ name: warehouse2\n" + 378 " └─ columns: [w_id w_name w_street_1 w_street_2 w_city w_state w_zip]\n" + 379 "", 380 ExpectedEstimates: "Project\n" + 381 " ├─ columns: [warehouse2.w_street_1, warehouse2.w_street_2, warehouse2.w_city, warehouse2.w_state, warehouse2.w_zip, warehouse2.w_name]\n" + 382 " └─ IndexedTableAccess(warehouse2)\n" + 383 " ├─ index: [warehouse2.w_id]\n" + 384 " ├─ filters: [{[1, 1]}]\n" + 385 " └─ columns: [w_id w_name w_street_1 w_street_2 w_city w_state w_zip]\n" + 386 "", 387 ExpectedAnalysis: "Project\n" + 388 " ├─ columns: [warehouse2.w_street_1, warehouse2.w_street_2, warehouse2.w_city, warehouse2.w_state, warehouse2.w_zip, warehouse2.w_name]\n" + 389 " └─ IndexedTableAccess(warehouse2)\n" + 390 " ├─ index: [warehouse2.w_id]\n" + 391 " ├─ filters: [{[1, 1]}]\n" + 392 " └─ columns: [w_id w_name w_street_1 w_street_2 w_city w_state w_zip]\n" + 393 "", 394 }, 395 { 396 Query: `UPDATE district2 SET d_ytd = d_ytd + 1767 WHERE d_w_id = 1 AND d_id= 8`, 397 ExpectedPlan: "RowUpdateAccumulator\n" + 398 " └─ Update\n" + 399 " └─ UpdateSource(SET district2.d_ytd:9 = (district2.d_ytd:9 + 1767 (smallint)))\n" + 400 " └─ IndexedTableAccess(district2)\n" + 401 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 402 " ├─ static: [{[1, 1], [8, 8]}]\n" + 403 " ├─ colSet: (1-11)\n" + 404 " ├─ tableId: 1\n" + 405 " └─ Table\n" + 406 " ├─ name: district2\n" + 407 " └─ columns: [d_id d_w_id d_name d_street_1 d_street_2 d_city d_state d_zip d_tax d_ytd d_next_o_id]\n" + 408 "", 409 }, 410 { 411 Query: `SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name FROM district2 WHERE d_w_id = 1 AND d_id = 8`, 412 ExpectedPlan: "Project\n" + 413 " ├─ columns: [district2.d_street_1:3, district2.d_street_2:4, district2.d_city:5, district2.d_state:6, district2.d_zip:7, district2.d_name:2]\n" + 414 " └─ IndexedTableAccess(district2)\n" + 415 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 416 " ├─ static: [{[1, 1], [8, 8]}]\n" + 417 " ├─ colSet: (1-11)\n" + 418 " ├─ tableId: 1\n" + 419 " └─ Table\n" + 420 " ├─ name: district2\n" + 421 " └─ columns: [d_id d_w_id d_name d_street_1 d_street_2 d_city d_state d_zip]\n" + 422 "", 423 ExpectedEstimates: "Project\n" + 424 " ├─ columns: [district2.d_street_1, district2.d_street_2, district2.d_city, district2.d_state, district2.d_zip, district2.d_name]\n" + 425 " └─ IndexedTableAccess(district2)\n" + 426 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 427 " ├─ filters: [{[1, 1], [8, 8]}]\n" + 428 " └─ columns: [d_id d_w_id d_name d_street_1 d_street_2 d_city d_state d_zip]\n" + 429 "", 430 ExpectedAnalysis: "Project\n" + 431 " ├─ columns: [district2.d_street_1, district2.d_street_2, district2.d_city, district2.d_state, district2.d_zip, district2.d_name]\n" + 432 " └─ IndexedTableAccess(district2)\n" + 433 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 434 " ├─ filters: [{[1, 1], [8, 8]}]\n" + 435 " └─ columns: [d_id d_w_id d_name d_street_1 d_street_2 d_city d_state d_zip]\n" + 436 "", 437 }, 438 { 439 Query: `SELECT count(c_id) namecnt FROM customer2 WHERE c_w_id = 1 AND c_d_id= 5 AND c_last='ESEEINGABLE'`, 440 ExpectedPlan: "Project\n" + 441 " ├─ columns: [count(customer2.c_id):0!null as namecnt]\n" + 442 " └─ GroupBy\n" + 443 " ├─ select: COUNT(customer2.c_id:0!null)\n" + 444 " ├─ group: \n" + 445 " └─ Filter\n" + 446 " ├─ Eq\n" + 447 " │ ├─ customer2.c_last:3\n" + 448 " │ └─ ESEEINGABLE (longtext)\n" + 449 " └─ IndexedTableAccess(customer2)\n" + 450 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 451 " ├─ static: [{[1, 1], [5, 5], [ESEEINGABLE, ESEEINGABLE], [NULL, ∞)}]\n" + 452 " ├─ colSet: (1-21)\n" + 453 " ├─ tableId: 1\n" + 454 " └─ Table\n" + 455 " ├─ name: customer2\n" + 456 " └─ columns: [c_id c_d_id c_w_id c_last]\n" + 457 "", 458 ExpectedEstimates: "Project\n" + 459 " ├─ columns: [count(customer2.c_id) as namecnt]\n" + 460 " └─ GroupBy\n" + 461 " ├─ SelectedExprs(COUNT(customer2.c_id))\n" + 462 " ├─ Grouping()\n" + 463 " └─ Filter\n" + 464 " ├─ (customer2.c_last = 'ESEEINGABLE')\n" + 465 " └─ IndexedTableAccess(customer2)\n" + 466 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 467 " ├─ filters: [{[1, 1], [5, 5], [ESEEINGABLE, ESEEINGABLE], [NULL, ∞)}]\n" + 468 " └─ columns: [c_id c_d_id c_w_id c_last]\n" + 469 "", 470 ExpectedAnalysis: "Project\n" + 471 " ├─ columns: [count(customer2.c_id) as namecnt]\n" + 472 " └─ GroupBy\n" + 473 " ├─ SelectedExprs(COUNT(customer2.c_id))\n" + 474 " ├─ Grouping()\n" + 475 " └─ Filter\n" + 476 " ├─ (customer2.c_last = 'ESEEINGABLE')\n" + 477 " └─ IndexedTableAccess(customer2)\n" + 478 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 479 " ├─ filters: [{[1, 1], [5, 5], [ESEEINGABLE, ESEEINGABLE], [NULL, ∞)}]\n" + 480 " └─ columns: [c_id c_d_id c_w_id c_last]\n" + 481 "", 482 }, 483 { 484 Query: `SELECT c_id FROM customer2 WHERE c_w_id = 1 AND c_d_id= 5 AND c_last='ESEEINGABLE' ORDER BY c_first`, 485 ExpectedPlan: "Project\n" + 486 " ├─ columns: [customer2.c_id:0!null]\n" + 487 " └─ Sort(customer2.c_first:3 ASC nullsFirst)\n" + 488 " └─ Filter\n" + 489 " ├─ Eq\n" + 490 " │ ├─ customer2.c_last:4\n" + 491 " │ └─ ESEEINGABLE (longtext)\n" + 492 " └─ IndexedTableAccess(customer2)\n" + 493 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 494 " ├─ static: [{[1, 1], [5, 5], [ESEEINGABLE, ESEEINGABLE], [NULL, ∞)}]\n" + 495 " ├─ colSet: (1-21)\n" + 496 " ├─ tableId: 1\n" + 497 " └─ Table\n" + 498 " ├─ name: customer2\n" + 499 " └─ columns: [c_id c_d_id c_w_id c_first c_last]\n" + 500 "", 501 ExpectedEstimates: "Project\n" + 502 " ├─ columns: [customer2.c_id]\n" + 503 " └─ Sort(customer2.c_first ASC)\n" + 504 " └─ Filter\n" + 505 " ├─ (customer2.c_last = 'ESEEINGABLE')\n" + 506 " └─ IndexedTableAccess(customer2)\n" + 507 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 508 " ├─ filters: [{[1, 1], [5, 5], [ESEEINGABLE, ESEEINGABLE], [NULL, ∞)}]\n" + 509 " └─ columns: [c_id c_d_id c_w_id c_first c_last]\n" + 510 "", 511 ExpectedAnalysis: "Project\n" + 512 " ├─ columns: [customer2.c_id]\n" + 513 " └─ Sort(customer2.c_first ASC)\n" + 514 " └─ Filter\n" + 515 " ├─ (customer2.c_last = 'ESEEINGABLE')\n" + 516 " └─ IndexedTableAccess(customer2)\n" + 517 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 518 " ├─ filters: [{[1, 1], [5, 5], [ESEEINGABLE, ESEEINGABLE], [NULL, ∞)}]\n" + 519 " └─ columns: [c_id c_d_id c_w_id c_first c_last]\n" + 520 "", 521 }, 522 { 523 Query: `SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_credit, c_credit_lim, c_discount, c_balance, c_ytd_payment, c_since FROM customer2 WHERE c_w_id = 1 AND c_d_id= 5 AND c_id=1838 FOR UPDATE`, 524 ExpectedPlan: "Project\n" + 525 " ├─ columns: [customer2.c_first:3, customer2.c_middle:4, customer2.c_last:5, customer2.c_street_1:6, customer2.c_street_2:7, customer2.c_city:8, customer2.c_state:9, customer2.c_zip:10, customer2.c_phone:11, customer2.c_credit:13, customer2.c_credit_lim:14, customer2.c_discount:15, customer2.c_balance:16, customer2.c_ytd_payment:17, customer2.c_since:12]\n" + 526 " └─ IndexedTableAccess(customer2)\n" + 527 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_id]\n" + 528 " ├─ static: [{[1, 1], [5, 5], [1838, 1838]}]\n" + 529 " ├─ colSet: (1-21)\n" + 530 " ├─ tableId: 1\n" + 531 " └─ Table\n" + 532 " ├─ name: customer2\n" + 533 " └─ columns: [c_id c_d_id c_w_id c_first c_middle c_last c_street_1 c_street_2 c_city c_state c_zip c_phone c_since c_credit c_credit_lim c_discount c_balance c_ytd_payment]\n" + 534 "", 535 ExpectedEstimates: "Project\n" + 536 " ├─ columns: [customer2.c_first, customer2.c_middle, customer2.c_last, customer2.c_street_1, customer2.c_street_2, customer2.c_city, customer2.c_state, customer2.c_zip, customer2.c_phone, customer2.c_credit, customer2.c_credit_lim, customer2.c_discount, customer2.c_balance, customer2.c_ytd_payment, customer2.c_since]\n" + 537 " └─ IndexedTableAccess(customer2)\n" + 538 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_id]\n" + 539 " ├─ filters: [{[1, 1], [5, 5], [1838, 1838]}]\n" + 540 " └─ columns: [c_id c_d_id c_w_id c_first c_middle c_last c_street_1 c_street_2 c_city c_state c_zip c_phone c_since c_credit c_credit_lim c_discount c_balance c_ytd_payment]\n" + 541 "", 542 ExpectedAnalysis: "Project\n" + 543 " ├─ columns: [customer2.c_first, customer2.c_middle, customer2.c_last, customer2.c_street_1, customer2.c_street_2, customer2.c_city, customer2.c_state, customer2.c_zip, customer2.c_phone, customer2.c_credit, customer2.c_credit_lim, customer2.c_discount, customer2.c_balance, customer2.c_ytd_payment, customer2.c_since]\n" + 544 " └─ IndexedTableAccess(customer2)\n" + 545 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_id]\n" + 546 " ├─ filters: [{[1, 1], [5, 5], [1838, 1838]}]\n" + 547 " └─ columns: [c_id c_d_id c_w_id c_first c_middle c_last c_street_1 c_street_2 c_city c_state c_zip c_phone c_since c_credit c_credit_lim c_discount c_balance c_ytd_payment]\n" + 548 "", 549 }, 550 { 551 Query: `UPDATE customer2 SET c_balance=-1777.000000, c_ytd_payment=1777.000000 WHERE c_w_id = 1 AND c_d_id=5 AND c_id=1838`, 552 ExpectedPlan: "RowUpdateAccumulator\n" + 553 " └─ Update\n" + 554 " └─ UpdateSource(SET customer2.c_balance:16 = -1777.000000,SET customer2.c_ytd_payment:17 = 1777 (decimal(10,6)))\n" + 555 " └─ IndexedTableAccess(customer2)\n" + 556 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_id]\n" + 557 " ├─ static: [{[1, 1], [5, 5], [1838, 1838]}]\n" + 558 " ├─ colSet: (1-21)\n" + 559 " ├─ tableId: 1\n" + 560 " └─ Table\n" + 561 " ├─ name: customer2\n" + 562 " └─ columns: [c_id c_d_id c_w_id c_first c_middle c_last c_street_1 c_street_2 c_city c_state c_zip c_phone c_since c_credit c_credit_lim c_discount c_balance c_ytd_payment c_payment_cnt c_delivery_cnt c_data]\n" + 563 "", 564 }, 565 { 566 Query: `INSERT INTO history2 (h_c_d_id, h_c_w_id, h_c_id, h_d_id, h_w_id, h_date, h_amount, h_data) VALUES (5,1,1838,8,1,NOW(),1767,'name-rqojn name-dnvgs ')`, 567 ExpectedPlan: "RowUpdateAccumulator\n" + 568 " └─ Insert(h_c_d_id, h_c_w_id, h_c_id, h_d_id, h_w_id, h_date, h_amount, h_data)\n" + 569 " ├─ InsertDestination\n" + 570 " │ └─ ProcessTable\n" + 571 " │ └─ Table\n" + 572 " │ ├─ name: history2\n" + 573 " │ └─ columns: [h_c_id h_c_d_id h_c_w_id h_d_id h_w_id h_date h_amount h_data]\n" + 574 " └─ Project\n" + 575 " ├─ columns: [h_c_id:2, h_c_d_id:0, h_c_w_id:1, h_d_id:3, h_w_id:4, h_date:5, h_amount:6, h_data:7]\n" + 576 " └─ Values([5 (tinyint),1 (tinyint),1838 (smallint),8 (tinyint),1 (tinyint),NOW(),1767 (smallint),name-rqojn name-dnvgs (longtext)])\n" + 577 "", 578 }, 579 { 580 Query: ` 581 -- cycle 3 582 SELECT count(c_id) namecnt FROM customer2 WHERE c_w_id = 1 AND c_d_id= 1 AND c_last='PRIESEPRES'`, 583 ExpectedPlan: "Project\n" + 584 " ├─ columns: [count(customer2.c_id):0!null as namecnt]\n" + 585 " └─ GroupBy\n" + 586 " ├─ select: COUNT(customer2.c_id:0!null)\n" + 587 " ├─ group: \n" + 588 " └─ Filter\n" + 589 " ├─ Eq\n" + 590 " │ ├─ customer2.c_last:3\n" + 591 " │ └─ PRIESEPRES (longtext)\n" + 592 " └─ IndexedTableAccess(customer2)\n" + 593 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 594 " ├─ static: [{[1, 1], [1, 1], [PRIESEPRES, PRIESEPRES], [NULL, ∞)}]\n" + 595 " ├─ colSet: (1-21)\n" + 596 " ├─ tableId: 1\n" + 597 " └─ Table\n" + 598 " ├─ name: customer2\n" + 599 " └─ columns: [c_id c_d_id c_w_id c_last]\n" + 600 "", 601 ExpectedEstimates: "Project\n" + 602 " ├─ columns: [count(customer2.c_id) as namecnt]\n" + 603 " └─ GroupBy\n" + 604 " ├─ SelectedExprs(COUNT(customer2.c_id))\n" + 605 " ├─ Grouping()\n" + 606 " └─ Filter\n" + 607 " ├─ (customer2.c_last = 'PRIESEPRES')\n" + 608 " └─ IndexedTableAccess(customer2)\n" + 609 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 610 " ├─ filters: [{[1, 1], [1, 1], [PRIESEPRES, PRIESEPRES], [NULL, ∞)}]\n" + 611 " └─ columns: [c_id c_d_id c_w_id c_last]\n" + 612 "", 613 ExpectedAnalysis: "Project\n" + 614 " ├─ columns: [count(customer2.c_id) as namecnt]\n" + 615 " └─ GroupBy\n" + 616 " ├─ SelectedExprs(COUNT(customer2.c_id))\n" + 617 " ├─ Grouping()\n" + 618 " └─ Filter\n" + 619 " ├─ (customer2.c_last = 'PRIESEPRES')\n" + 620 " └─ IndexedTableAccess(customer2)\n" + 621 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 622 " ├─ filters: [{[1, 1], [1, 1], [PRIESEPRES, PRIESEPRES], [NULL, ∞)}]\n" + 623 " └─ columns: [c_id c_d_id c_w_id c_last]\n" + 624 "", 625 }, 626 { 627 Query: `SELECT c_balance, c_first, c_middle, c_id FROM customer2 WHERE c_w_id = 1 AND c_d_id= 1 AND c_last='PRIESEPRES' ORDER BY c_first`, 628 ExpectedPlan: "Project\n" + 629 " ├─ columns: [customer2.c_balance:6, customer2.c_first:3, customer2.c_middle:4, customer2.c_id:0!null]\n" + 630 " └─ Sort(customer2.c_first:3 ASC nullsFirst)\n" + 631 " └─ Filter\n" + 632 " ├─ Eq\n" + 633 " │ ├─ customer2.c_last:5\n" + 634 " │ └─ PRIESEPRES (longtext)\n" + 635 " └─ IndexedTableAccess(customer2)\n" + 636 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 637 " ├─ static: [{[1, 1], [1, 1], [PRIESEPRES, PRIESEPRES], [NULL, ∞)}]\n" + 638 " ├─ colSet: (1-21)\n" + 639 " ├─ tableId: 1\n" + 640 " └─ Table\n" + 641 " ├─ name: customer2\n" + 642 " └─ columns: [c_id c_d_id c_w_id c_first c_middle c_last c_balance]\n" + 643 "", 644 ExpectedEstimates: "Project\n" + 645 " ├─ columns: [customer2.c_balance, customer2.c_first, customer2.c_middle, customer2.c_id]\n" + 646 " └─ Sort(customer2.c_first ASC)\n" + 647 " └─ Filter\n" + 648 " ├─ (customer2.c_last = 'PRIESEPRES')\n" + 649 " └─ IndexedTableAccess(customer2)\n" + 650 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 651 " ├─ filters: [{[1, 1], [1, 1], [PRIESEPRES, PRIESEPRES], [NULL, ∞)}]\n" + 652 " └─ columns: [c_id c_d_id c_w_id c_first c_middle c_last c_balance]\n" + 653 "", 654 ExpectedAnalysis: "Project\n" + 655 " ├─ columns: [customer2.c_balance, customer2.c_first, customer2.c_middle, customer2.c_id]\n" + 656 " └─ Sort(customer2.c_first ASC)\n" + 657 " └─ Filter\n" + 658 " ├─ (customer2.c_last = 'PRIESEPRES')\n" + 659 " └─ IndexedTableAccess(customer2)\n" + 660 " ├─ index: [customer2.c_w_id,customer2.c_d_id,customer2.c_last,customer2.c_first]\n" + 661 " ├─ filters: [{[1, 1], [1, 1], [PRIESEPRES, PRIESEPRES], [NULL, ∞)}]\n" + 662 " └─ columns: [c_id c_d_id c_w_id c_first c_middle c_last c_balance]\n" + 663 "", 664 }, 665 { 666 Query: `SELECT o_id, o_carrier_id, o_entry_d FROM orders2 WHERE o_w_id = 1 AND o_d_id = 1 AND o_c_id = 355 ORDER BY o_id DESC`, 667 ExpectedPlan: "Project\n" + 668 " ├─ columns: [orders2.o_id:0!null, orders2.o_carrier_id:5, orders2.o_entry_d:4]\n" + 669 " └─ Sort(orders2.o_id:0!null DESC nullsFirst)\n" + 670 " └─ IndexedTableAccess(orders2)\n" + 671 " ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 672 " ├─ static: [{[1, 1], [1, 1], [355, 355], [NULL, ∞)}]\n" + 673 " ├─ colSet: (1-8)\n" + 674 " ├─ tableId: 1\n" + 675 " └─ Table\n" + 676 " ├─ name: orders2\n" + 677 " └─ columns: [o_id o_d_id o_w_id o_c_id o_entry_d o_carrier_id]\n" + 678 "", 679 ExpectedEstimates: "Project\n" + 680 " ├─ columns: [orders2.o_id, orders2.o_carrier_id, orders2.o_entry_d]\n" + 681 " └─ Sort(orders2.o_id DESC)\n" + 682 " └─ IndexedTableAccess(orders2)\n" + 683 " ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 684 " ├─ filters: [{[1, 1], [1, 1], [355, 355], [NULL, ∞)}]\n" + 685 " └─ columns: [o_id o_d_id o_w_id o_c_id o_entry_d o_carrier_id]\n" + 686 "", 687 ExpectedAnalysis: "Project\n" + 688 " ├─ columns: [orders2.o_id, orders2.o_carrier_id, orders2.o_entry_d]\n" + 689 " └─ Sort(orders2.o_id DESC)\n" + 690 " └─ IndexedTableAccess(orders2)\n" + 691 " ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 692 " ├─ filters: [{[1, 1], [1, 1], [355, 355], [NULL, ∞)}]\n" + 693 " └─ columns: [o_id o_d_id o_w_id o_c_id o_entry_d o_carrier_id]\n" + 694 "", 695 }, 696 { 697 Query: `SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM order_line2 WHERE ol_w_id = 1 AND ol_d_id = 1 AND ol_o_id = 1`, 698 ExpectedPlan: "Project\n" + 699 " ├─ columns: [order_line2.ol_i_id:3, order_line2.ol_supply_w_id:4, order_line2.ol_quantity:6, order_line2.ol_amount:7, order_line2.ol_delivery_d:5]\n" + 700 " └─ IndexedTableAccess(order_line2)\n" + 701 " ├─ index: [order_line2.ol_w_id,order_line2.ol_d_id,order_line2.ol_o_id,order_line2.ol_number]\n" + 702 " ├─ static: [{[1, 1], [1, 1], [1, 1], [NULL, ∞)}]\n" + 703 " ├─ colSet: (1-10)\n" + 704 " ├─ tableId: 1\n" + 705 " └─ Table\n" + 706 " ├─ name: order_line2\n" + 707 " └─ columns: [ol_o_id ol_d_id ol_w_id ol_i_id ol_supply_w_id ol_delivery_d ol_quantity ol_amount]\n" + 708 "", 709 ExpectedEstimates: "Project\n" + 710 " ├─ columns: [order_line2.ol_i_id, order_line2.ol_supply_w_id, order_line2.ol_quantity, order_line2.ol_amount, order_line2.ol_delivery_d]\n" + 711 " └─ IndexedTableAccess(order_line2)\n" + 712 " ├─ index: [order_line2.ol_w_id,order_line2.ol_d_id,order_line2.ol_o_id,order_line2.ol_number]\n" + 713 " ├─ filters: [{[1, 1], [1, 1], [1, 1], [NULL, ∞)}]\n" + 714 " └─ columns: [ol_o_id ol_d_id ol_w_id ol_i_id ol_supply_w_id ol_delivery_d ol_quantity ol_amount]\n" + 715 "", 716 ExpectedAnalysis: "Project\n" + 717 " ├─ columns: [order_line2.ol_i_id, order_line2.ol_supply_w_id, order_line2.ol_quantity, order_line2.ol_amount, order_line2.ol_delivery_d]\n" + 718 " └─ IndexedTableAccess(order_line2)\n" + 719 " ├─ index: [order_line2.ol_w_id,order_line2.ol_d_id,order_line2.ol_o_id,order_line2.ol_number]\n" + 720 " ├─ filters: [{[1, 1], [1, 1], [1, 1], [NULL, ∞)}]\n" + 721 " └─ columns: [ol_o_id ol_d_id ol_w_id ol_i_id ol_supply_w_id ol_delivery_d ol_quantity ol_amount]\n" + 722 "", 723 }, 724 { 725 Query: ` 726 -- cycle 4 727 SELECT d_next_o_id FROM district2 WHERE d_id = 5 AND d_w_id= 1`, 728 ExpectedPlan: "Project\n" + 729 " ├─ columns: [district2.d_next_o_id:2]\n" + 730 " └─ IndexedTableAccess(district2)\n" + 731 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 732 " ├─ static: [{[1, 1], [5, 5]}]\n" + 733 " ├─ colSet: (1-11)\n" + 734 " ├─ tableId: 1\n" + 735 " └─ Table\n" + 736 " ├─ name: district2\n" + 737 " └─ columns: [d_id d_w_id d_next_o_id]\n" + 738 "", 739 ExpectedEstimates: "Project\n" + 740 " ├─ columns: [district2.d_next_o_id]\n" + 741 " └─ IndexedTableAccess(district2)\n" + 742 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 743 " ├─ filters: [{[1, 1], [5, 5]}]\n" + 744 " └─ columns: [d_id d_w_id d_next_o_id]\n" + 745 "", 746 ExpectedAnalysis: "Project\n" + 747 " ├─ columns: [district2.d_next_o_id]\n" + 748 " └─ IndexedTableAccess(district2)\n" + 749 " ├─ index: [district2.d_w_id,district2.d_id]\n" + 750 " ├─ filters: [{[1, 1], [5, 5]}]\n" + 751 " └─ columns: [d_id d_w_id d_next_o_id]\n" + 752 "", 753 }, 754 { 755 Query: `SELECT COUNT(DISTINCT (s_i_id)) FROM order_line2, stock2 WHERE ol_w_id = 1 AND ol_d_id = 5 AND ol_o_id < 3003 AND ol_o_id >= 2983 AND s_w_id= 1 AND s_i_id=ol_i_id AND s_quantity < 18`, 756 ExpectedPlan: "Project\n" + 757 " ├─ columns: [countdistinct([stock2.s_i_id]):0!null as COUNT(DISTINCT (s_i_id))]\n" + 758 " └─ GroupBy\n" + 759 " ├─ select: COUNTDISTINCT([stock2.s_i_id])\n" + 760 " ├─ group: \n" + 761 " └─ LookupJoin\n" + 762 " ├─ IndexedTableAccess(order_line2)\n" + 763 " │ ├─ index: [order_line2.ol_w_id,order_line2.ol_d_id,order_line2.ol_o_id,order_line2.ol_number]\n" + 764 " │ ├─ static: [{[1, 1], [5, 5], [2983, 3003), [NULL, ∞)}]\n" + 765 " │ ├─ colSet: (1-10)\n" + 766 " │ ├─ tableId: 1\n" + 767 " │ └─ Table\n" + 768 " │ ├─ name: order_line2\n" + 769 " │ └─ columns: [ol_o_id ol_d_id ol_w_id ol_i_id]\n" + 770 " └─ Filter\n" + 771 " ├─ AND\n" + 772 " │ ├─ Eq\n" + 773 " │ │ ├─ stock2.s_w_id:1!null\n" + 774 " │ │ └─ 1 (tinyint)\n" + 775 " │ └─ LessThan\n" + 776 " │ ├─ stock2.s_quantity:2\n" + 777 " │ └─ 18 (tinyint)\n" + 778 " └─ IndexedTableAccess(stock2)\n" + 779 " ├─ index: [stock2.s_w_id,stock2.s_i_id]\n" + 780 " ├─ keys: [1 (tinyint) order_line2.ol_i_id:3]\n" + 781 " ├─ colSet: (11-27)\n" + 782 " ├─ tableId: 2\n" + 783 " └─ Table\n" + 784 " ├─ name: stock2\n" + 785 " └─ columns: [s_i_id s_w_id s_quantity]\n" + 786 "", 787 ExpectedEstimates: "Project\n" + 788 " ├─ columns: [countdistinct([stock2.s_i_id]) as COUNT(DISTINCT (s_i_id))]\n" + 789 " └─ GroupBy\n" + 790 " ├─ SelectedExprs(COUNTDISTINCT([stock2.s_i_id]))\n" + 791 " ├─ Grouping()\n" + 792 " └─ LookupJoin\n" + 793 " ├─ IndexedTableAccess(order_line2)\n" + 794 " │ ├─ index: [order_line2.ol_w_id,order_line2.ol_d_id,order_line2.ol_o_id,order_line2.ol_number]\n" + 795 " │ ├─ filters: [{[1, 1], [5, 5], [2983, 3003), [NULL, ∞)}]\n" + 796 " │ └─ columns: [ol_o_id ol_d_id ol_w_id ol_i_id]\n" + 797 " └─ Filter\n" + 798 " ├─ ((stock2.s_w_id = 1) AND (stock2.s_quantity < 18))\n" + 799 " └─ IndexedTableAccess(stock2)\n" + 800 " ├─ index: [stock2.s_w_id,stock2.s_i_id]\n" + 801 " ├─ columns: [s_i_id s_w_id s_quantity]\n" + 802 " └─ keys: 1, order_line2.ol_i_id\n" + 803 "", 804 ExpectedAnalysis: "Project\n" + 805 " ├─ columns: [countdistinct([stock2.s_i_id]) as COUNT(DISTINCT (s_i_id))]\n" + 806 " └─ GroupBy\n" + 807 " ├─ SelectedExprs(COUNTDISTINCT([stock2.s_i_id]))\n" + 808 " ├─ Grouping()\n" + 809 " └─ LookupJoin\n" + 810 " ├─ IndexedTableAccess(order_line2)\n" + 811 " │ ├─ index: [order_line2.ol_w_id,order_line2.ol_d_id,order_line2.ol_o_id,order_line2.ol_number]\n" + 812 " │ ├─ filters: [{[1, 1], [5, 5], [2983, 3003), [NULL, ∞)}]\n" + 813 " │ └─ columns: [ol_o_id ol_d_id ol_w_id ol_i_id]\n" + 814 " └─ Filter\n" + 815 " ├─ ((stock2.s_w_id = 1) AND (stock2.s_quantity < 18))\n" + 816 " └─ IndexedTableAccess(stock2)\n" + 817 " ├─ index: [stock2.s_w_id,stock2.s_i_id]\n" + 818 " ├─ columns: [s_i_id s_w_id s_quantity]\n" + 819 " └─ keys: 1, order_line2.ol_i_id\n" + 820 "", 821 }, 822 { 823 Query: ` 824 -- other 825 SELECT o_id, o_entry_d, COALESCE(o_carrier_id,0) 826 FROM orders2 827 WHERE 828 o_w_id = 1 AND 829 o_d_id = 3 AND 830 o_c_id = 20001 AND 831 o_id = (SELECT MAX(o_id) FROM orders2 WHERE o_w_id = 1 AND o_d_id = 3 AND o_c_id = 20001)`, 832 ExpectedPlan: "Project\n" + 833 " ├─ columns: [orders2.o_id:0!null, orders2.o_entry_d:4, coalesce(orders2.o_carrier_id:5,0 (tinyint)) as COALESCE(o_carrier_id,0)]\n" + 834 " └─ Filter\n" + 835 " ├─ Eq\n" + 836 " │ ├─ orders2.o_id:0!null\n" + 837 " │ └─ Subquery\n" + 838 " │ ├─ cacheable: true\n" + 839 " │ ├─ alias-string: select MAX(o_id) from orders2 where o_w_id = 1 and o_d_id = 3 and o_c_id = 20001\n" + 840 " │ └─ Project\n" + 841 " │ ├─ columns: [max(orders2.o_id):8!null as MAX(o_id)]\n" + 842 " │ └─ GroupBy\n" + 843 " │ ├─ select: MAX(orders2.o_id:8!null)\n" + 844 " │ ├─ group: \n" + 845 " │ └─ IndexedTableAccess(orders2)\n" + 846 " │ ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 847 " │ ├─ static: [{[1, 1], [3, 3], [20001, 20001], [NULL, ∞)}]\n" + 848 " │ ├─ colSet: (9-16)\n" + 849 " │ ├─ tableId: 2\n" + 850 " │ └─ Table\n" + 851 " │ ├─ name: orders2\n" + 852 " │ └─ columns: [o_id o_d_id o_w_id o_c_id]\n" + 853 " └─ IndexedTableAccess(orders2)\n" + 854 " ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 855 " ├─ static: [{[1, 1], [3, 3], [20001, 20001], [NULL, ∞)}]\n" + 856 " ├─ colSet: (1-8)\n" + 857 " ├─ tableId: 1\n" + 858 " └─ Table\n" + 859 " ├─ name: orders2\n" + 860 " └─ columns: [o_id o_d_id o_w_id o_c_id o_entry_d o_carrier_id o_ol_cnt o_all_local]\n" + 861 "", 862 ExpectedEstimates: "Project\n" + 863 " ├─ columns: [orders2.o_id, orders2.o_entry_d, coalesce(orders2.o_carrier_id,0) as COALESCE(o_carrier_id,0)]\n" + 864 " └─ Filter\n" + 865 " ├─ (orders2.o_id = Subquery\n" + 866 " │ ├─ cacheable: true\n" + 867 " │ └─ Project\n" + 868 " │ ├─ columns: [max(orders2.o_id) as MAX(o_id)]\n" + 869 " │ └─ GroupBy\n" + 870 " │ ├─ SelectedExprs(MAX(orders2.o_id))\n" + 871 " │ ├─ Grouping()\n" + 872 " │ └─ IndexedTableAccess(orders2)\n" + 873 " │ ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 874 " │ ├─ filters: [{[1, 1], [3, 3], [20001, 20001], [NULL, ∞)}]\n" + 875 " │ └─ columns: [o_id o_d_id o_w_id o_c_id]\n" + 876 " │ )\n" + 877 " └─ IndexedTableAccess(orders2)\n" + 878 " ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 879 " └─ filters: [{[1, 1], [3, 3], [20001, 20001], [NULL, ∞)}]\n" + 880 "", 881 ExpectedAnalysis: "Project\n" + 882 " ├─ columns: [orders2.o_id, orders2.o_entry_d, coalesce(orders2.o_carrier_id,0) as COALESCE(o_carrier_id,0)]\n" + 883 " └─ Filter\n" + 884 " ├─ (orders2.o_id = Subquery\n" + 885 " │ ├─ cacheable: true\n" + 886 " │ └─ Project\n" + 887 " │ ├─ columns: [max(orders2.o_id) as MAX(o_id)]\n" + 888 " │ └─ GroupBy\n" + 889 " │ ├─ SelectedExprs(MAX(orders2.o_id))\n" + 890 " │ ├─ Grouping()\n" + 891 " │ └─ IndexedTableAccess(orders2)\n" + 892 " │ ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 893 " │ ├─ filters: [{[1, 1], [3, 3], [20001, 20001], [NULL, ∞)}]\n" + 894 " │ └─ columns: [o_id o_d_id o_w_id o_c_id]\n" + 895 " │ )\n" + 896 " └─ IndexedTableAccess(orders2)\n" + 897 " ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 898 " └─ filters: [{[1, 1], [3, 3], [20001, 20001], [NULL, ∞)}]\n" + 899 "", 900 }, 901 { 902 Query: ` 903 select o_id, o.o_d_id 904 from 905 orders2 o, 906 ( 907 select o_c_id, o_w_id, o_d_id, count(distinct o_id) 908 from orders2 909 where o_w_id=1 and o_id > 2100 and o_id < 11153 910 group by o_c_id,o_d_id,o_w_id 911 having count( distinct o_id) > 1 912 limit 1 913 ) t 914 where 915 t.o_w_id=o.o_w_id and 916 t.o_d_id=o.o_d_id and 917 t.o_c_id=o.o_c_id 918 limit 1;`, 919 ExpectedPlan: "Limit(1)\n" + 920 " └─ Project\n" + 921 " ├─ columns: [o.o_id:4!null, o.o_d_id:5!null]\n" + 922 " └─ LookupJoin\n" + 923 " ├─ SubqueryAlias\n" + 924 " │ ├─ name: t\n" + 925 " │ ├─ outerVisibility: false\n" + 926 " │ ├─ isLateral: false\n" + 927 " │ ├─ cacheable: true\n" + 928 " │ ├─ colSet: (18-21)\n" + 929 " │ ├─ tableId: 3\n" + 930 " │ └─ Limit(1)\n" + 931 " │ └─ Project\n" + 932 " │ ├─ columns: [orders2.o_c_id:1, orders2.o_w_id:2!null, orders2.o_d_id:3!null, countdistinct([orders2.o_id]):0!null as count(distinct o_id)]\n" + 933 " │ └─ Having\n" + 934 " │ ├─ GreaterThan\n" + 935 " │ │ ├─ countdistinct([orders2.o_id]):0!null\n" + 936 " │ │ └─ 1 (tinyint)\n" + 937 " │ └─ GroupBy\n" + 938 " │ ├─ select: COUNTDISTINCT([orders2.o_id]), orders2.o_c_id:3, orders2.o_w_id:2!null, orders2.o_d_id:1!null, orders2.o_id:0!null\n" + 939 " │ ├─ group: orders2.o_c_id:3, orders2.o_d_id:1!null, orders2.o_w_id:2!null\n" + 940 " │ └─ IndexedTableAccess(orders2)\n" + 941 " │ ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_id]\n" + 942 " │ ├─ static: [{[1, 1], [NULL, ∞), (2100, 11153)}]\n" + 943 " │ ├─ colSet: (9-16)\n" + 944 " │ ├─ tableId: 2\n" + 945 " │ └─ Table\n" + 946 " │ ├─ name: orders2\n" + 947 " │ └─ columns: [o_id o_d_id o_w_id o_c_id o_entry_d o_carrier_id o_ol_cnt o_all_local]\n" + 948 " └─ TableAlias(o)\n" + 949 " └─ IndexedTableAccess(orders2)\n" + 950 " ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 951 " ├─ keys: [t.o_w_id:1!null t.o_d_id:2!null t.o_c_id:0]\n" + 952 " ├─ colSet: (1-8)\n" + 953 " ├─ tableId: 1\n" + 954 " └─ Table\n" + 955 " ├─ name: orders2\n" + 956 " └─ columns: [o_id o_d_id o_w_id o_c_id]\n" + 957 "", 958 ExpectedEstimates: "Limit(1)\n" + 959 " └─ Project\n" + 960 " ├─ columns: [o.o_id, o.o_d_id]\n" + 961 " └─ LookupJoin\n" + 962 " ├─ SubqueryAlias\n" + 963 " │ ├─ name: t\n" + 964 " │ ├─ outerVisibility: false\n" + 965 " │ ├─ isLateral: false\n" + 966 " │ ├─ cacheable: true\n" + 967 " │ └─ Limit(1)\n" + 968 " │ └─ Project\n" + 969 " │ ├─ columns: [orders2.o_c_id, orders2.o_w_id, orders2.o_d_id, countdistinct([orders2.o_id]) as count(distinct o_id)]\n" + 970 " │ └─ Having((countdistinct([orders2.o_id]) > 1))\n" + 971 " │ └─ GroupBy\n" + 972 " │ ├─ SelectedExprs(COUNTDISTINCT([orders2.o_id]), orders2.o_c_id, orders2.o_w_id, orders2.o_d_id, orders2.o_id)\n" + 973 " │ ├─ Grouping(orders2.o_c_id, orders2.o_d_id, orders2.o_w_id)\n" + 974 " │ └─ IndexedTableAccess(orders2)\n" + 975 " │ ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_id]\n" + 976 " │ └─ filters: [{[1, 1], [NULL, ∞), (2100, 11153)}]\n" + 977 " └─ TableAlias(o)\n" + 978 " └─ IndexedTableAccess(orders2)\n" + 979 " ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 980 " ├─ columns: [o_id o_d_id o_w_id o_c_id]\n" + 981 " └─ keys: t.o_w_id, t.o_d_id, t.o_c_id\n" + 982 "", 983 ExpectedAnalysis: "Limit(1)\n" + 984 " └─ Project\n" + 985 " ├─ columns: [o.o_id, o.o_d_id]\n" + 986 " └─ LookupJoin\n" + 987 " ├─ SubqueryAlias\n" + 988 " │ ├─ name: t\n" + 989 " │ ├─ outerVisibility: false\n" + 990 " │ ├─ isLateral: false\n" + 991 " │ ├─ cacheable: true\n" + 992 " │ └─ Limit(1)\n" + 993 " │ └─ Project\n" + 994 " │ ├─ columns: [orders2.o_c_id, orders2.o_w_id, orders2.o_d_id, countdistinct([orders2.o_id]) as count(distinct o_id)]\n" + 995 " │ └─ Having((countdistinct([orders2.o_id]) > 1))\n" + 996 " │ └─ GroupBy\n" + 997 " │ ├─ SelectedExprs(COUNTDISTINCT([orders2.o_id]), orders2.o_c_id, orders2.o_w_id, orders2.o_d_id, orders2.o_id)\n" + 998 " │ ├─ Grouping(orders2.o_c_id, orders2.o_d_id, orders2.o_w_id)\n" + 999 " │ └─ IndexedTableAccess(orders2)\n" + 1000 " │ ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_id]\n" + 1001 " │ └─ filters: [{[1, 1], [NULL, ∞), (2100, 11153)}]\n" + 1002 " └─ TableAlias(o)\n" + 1003 " └─ IndexedTableAccess(orders2)\n" + 1004 " ├─ index: [orders2.o_w_id,orders2.o_d_id,orders2.o_c_id,orders2.o_id]\n" + 1005 " ├─ columns: [o_id o_d_id o_w_id o_c_id]\n" + 1006 " └─ keys: t.o_w_id, t.o_d_id, t.o_c_id\n" + 1007 "", 1008 }, 1009 }