github.com/dolthub/go-mysql-server@v0.18.0/enginetest/queries/tpch_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 TpchPlanTests = []QueryPlanTest{ 20 { 21 Query: ` 22 --(Q1) 23 select 24 l_returnflag, 25 l_linestatus, 26 sum(l_quantity) as sum_qty, 27 sum(l_extendedprice) as sum_base_price, 28 sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, 29 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, 30 avg(l_quantity) as avg_qty, 31 avg(l_extendedprice) as avg_price, 32 avg(l_discount) as avg_disc, 33 count(*) as count_order 34 from 35 lineitem 36 where 37 l_shipdate <= '1998-12-01' - interval '90' day 38 group by 39 l_returnflag, 40 l_linestatus 41 order by 42 l_returnflag, 43 l_linestatus;`, 44 ExpectedPlan: "Project\n" + 45 " ├─ columns: [lineitem.l_returnflag:8!null, lineitem.l_linestatus:9!null, sum(lineitem.l_quantity):7!null as sum_qty, sum(lineitem.l_extendedprice):6!null as sum_base_price, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):5!null as sum_disc_price, sum(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))):4!null as sum_charge, avg(lineitem.l_quantity):2 as avg_qty, avg(lineitem.l_extendedprice):1 as avg_price, avg(lineitem.l_discount):0 as avg_disc, count(1):3!null as count_order]\n" + 46 " └─ Sort(lineitem.l_returnflag:8!null ASC nullsFirst, lineitem.l_linestatus:9!null ASC nullsFirst)\n" + 47 " └─ Project\n" + 48 " ├─ columns: [avg(lineitem.l_discount):0, avg(lineitem.l_extendedprice):1, avg(lineitem.l_quantity):2, count(1):3!null, sum(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))):4!null, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):5!null, sum(lineitem.l_extendedprice):6!null, sum(lineitem.l_quantity):7!null, lineitem.l_returnflag:8!null, lineitem.l_linestatus:9!null, sum(lineitem.l_quantity):7!null as sum_qty, sum(lineitem.l_extendedprice):6!null as sum_base_price, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):5!null as sum_disc_price, sum(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))):4!null as sum_charge, avg(lineitem.l_quantity):2 as avg_qty, avg(lineitem.l_extendedprice):1 as avg_price, avg(lineitem.l_discount):0 as avg_disc, count(1):3!null as count_order]\n" + 49 " └─ GroupBy\n" + 50 " ├─ select: AVG(lineitem.l_discount:2!null), AVG(lineitem.l_extendedprice:1!null), AVG(lineitem.l_quantity:0!null), COUNT(1 (bigint)), SUM(((lineitem.l_extendedprice:1!null * (1 (tinyint) - lineitem.l_discount:2!null)) * (1 (tinyint) + lineitem.l_tax:3!null))), SUM((lineitem.l_extendedprice:1!null * (1 (tinyint) - lineitem.l_discount:2!null))), SUM(lineitem.l_extendedprice:1!null), SUM(lineitem.l_quantity:0!null), lineitem.l_returnflag:4!null, lineitem.l_linestatus:5!null\n" + 51 " ├─ group: lineitem.l_returnflag:4!null, lineitem.l_linestatus:5!null\n" + 52 " └─ Filter\n" + 53 " ├─ LessThanOrEqual\n" + 54 " │ ├─ lineitem.l_shipdate:6!null\n" + 55 " │ └─ 1998-09-02 00:00:00 +0000 UTC (datetime)\n" + 56 " └─ ProcessTable\n" + 57 " └─ Table\n" + 58 " ├─ name: lineitem\n" + 59 " └─ columns: [l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate]\n" + 60 "", 61 ExpectedEstimates: "Project\n" + 62 " ├─ columns: [lineitem.l_returnflag, lineitem.l_linestatus, sum(lineitem.l_quantity) as sum_qty, sum(lineitem.l_extendedprice) as sum_base_price, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as sum_disc_price, sum(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))) as sum_charge, avg(lineitem.l_quantity) as avg_qty, avg(lineitem.l_extendedprice) as avg_price, avg(lineitem.l_discount) as avg_disc, count(1) as count_order]\n" + 63 " └─ Sort(lineitem.l_returnflag ASC, lineitem.l_linestatus ASC)\n" + 64 " └─ Project\n" + 65 " ├─ columns: [avg(lineitem.l_discount), avg(lineitem.l_extendedprice), avg(lineitem.l_quantity), count(1), sum(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))), sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))), sum(lineitem.l_extendedprice), sum(lineitem.l_quantity), lineitem.l_returnflag, lineitem.l_linestatus, sum(lineitem.l_quantity) as sum_qty, sum(lineitem.l_extendedprice) as sum_base_price, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as sum_disc_price, sum(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))) as sum_charge, avg(lineitem.l_quantity) as avg_qty, avg(lineitem.l_extendedprice) as avg_price, avg(lineitem.l_discount) as avg_disc, count(1) as count_order]\n" + 66 " └─ GroupBy\n" + 67 " ├─ SelectedExprs(AVG(lineitem.l_discount), AVG(lineitem.l_extendedprice), AVG(lineitem.l_quantity), COUNT(1), SUM(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))), SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), SUM(lineitem.l_extendedprice), SUM(lineitem.l_quantity), lineitem.l_returnflag, lineitem.l_linestatus)\n" + 68 " ├─ Grouping(lineitem.l_returnflag, lineitem.l_linestatus)\n" + 69 " └─ Filter\n" + 70 " ├─ (lineitem.l_shipdate <= 1998-09-02 00:00:00 +0000 UTC)\n" + 71 " └─ Table\n" + 72 " ├─ name: lineitem\n" + 73 " └─ columns: [l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate]\n" + 74 "", 75 ExpectedAnalysis: "Project\n" + 76 " ├─ columns: [lineitem.l_returnflag, lineitem.l_linestatus, sum(lineitem.l_quantity) as sum_qty, sum(lineitem.l_extendedprice) as sum_base_price, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as sum_disc_price, sum(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))) as sum_charge, avg(lineitem.l_quantity) as avg_qty, avg(lineitem.l_extendedprice) as avg_price, avg(lineitem.l_discount) as avg_disc, count(1) as count_order]\n" + 77 " └─ Sort(lineitem.l_returnflag ASC, lineitem.l_linestatus ASC)\n" + 78 " └─ Project\n" + 79 " ├─ columns: [avg(lineitem.l_discount), avg(lineitem.l_extendedprice), avg(lineitem.l_quantity), count(1), sum(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))), sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))), sum(lineitem.l_extendedprice), sum(lineitem.l_quantity), lineitem.l_returnflag, lineitem.l_linestatus, sum(lineitem.l_quantity) as sum_qty, sum(lineitem.l_extendedprice) as sum_base_price, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as sum_disc_price, sum(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))) as sum_charge, avg(lineitem.l_quantity) as avg_qty, avg(lineitem.l_extendedprice) as avg_price, avg(lineitem.l_discount) as avg_disc, count(1) as count_order]\n" + 80 " └─ GroupBy\n" + 81 " ├─ SelectedExprs(AVG(lineitem.l_discount), AVG(lineitem.l_extendedprice), AVG(lineitem.l_quantity), COUNT(1), SUM(((lineitem.l_extendedprice * (1 - lineitem.l_discount)) * (1 + lineitem.l_tax))), SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), SUM(lineitem.l_extendedprice), SUM(lineitem.l_quantity), lineitem.l_returnflag, lineitem.l_linestatus)\n" + 82 " ├─ Grouping(lineitem.l_returnflag, lineitem.l_linestatus)\n" + 83 " └─ Filter\n" + 84 " ├─ (lineitem.l_shipdate <= 1998-09-02 00:00:00 +0000 UTC)\n" + 85 " └─ Table\n" + 86 " ├─ name: lineitem\n" + 87 " └─ columns: [l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate]\n" + 88 "", 89 }, 90 { 91 Query: ` 92 --(Q2) 93 select 94 s_acctbal, 95 s_name, 96 n_name, 97 p_partkey, 98 p_mfgr, 99 s_address, 100 s_phone, 101 s_comment 102 from 103 part, 104 supplier, 105 partsupp, 106 nation, 107 region 108 where 109 p_partkey = ps_partkey 110 and s_suppkey = ps_suppkey 111 and p_size = 15 112 and p_type like '%BRASS' 113 and s_nationkey = n_nationkey 114 and n_regionkey = r_regionkey 115 and r_name = 'EUROPE' 116 and ps_supplycost = ( 117 select 118 min(ps_supplycost) 119 from 120 partsupp, 121 supplier, 122 nation, 123 region 124 where 125 p_partkey = ps_partkey 126 and s_suppkey = ps_suppkey 127 and s_nationkey = n_nationkey 128 and n_regionkey = r_regionkey 129 and r_name = 'EUROPE' 130 ) 131 order by 132 s_acctbal desc, 133 n_name, 134 s_name, 135 p_partkey;`, 136 ExpectedPlan: "Project\n" + 137 " ├─ columns: [supplier.s_acctbal:19!null, supplier.s_name:15!null, nation.n_name:22!null, part.p_partkey:0!null, part.p_mfgr:2!null, supplier.s_address:16!null, supplier.s_phone:18!null, supplier.s_comment:20!null]\n" + 138 " └─ Sort(supplier.s_acctbal:19!null DESC nullsFirst, nation.n_name:22!null ASC nullsFirst, supplier.s_name:15!null ASC nullsFirst, part.p_partkey:0!null ASC nullsFirst)\n" + 139 " └─ Filter\n" + 140 " ├─ Eq\n" + 141 " │ ├─ partsupp.ps_supplycost:12!null\n" + 142 " │ └─ Subquery\n" + 143 " │ ├─ cacheable: false\n" + 144 " │ ├─ alias-string: select min(ps_supplycost) from partsupp, supplier, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE'\n" + 145 " │ └─ Project\n" + 146 " │ ├─ columns: [min(partsupp.ps_supplycost):28!null as min(ps_supplycost)]\n" + 147 " │ └─ GroupBy\n" + 148 " │ ├─ select: MIN(partsupp.ps_supplycost:30!null)\n" + 149 " │ ├─ group: \n" + 150 " │ └─ Filter\n" + 151 " │ ├─ Eq\n" + 152 " │ │ ├─ part.p_partkey:0!null\n" + 153 " │ │ └─ partsupp.ps_partkey:28!null\n" + 154 " │ └─ HashJoin\n" + 155 " │ ├─ Eq\n" + 156 " │ │ ├─ nation.n_regionkey:34!null\n" + 157 " │ │ └─ region.r_regionkey:35!null\n" + 158 " │ ├─ LookupJoin\n" + 159 " │ │ ├─ LookupJoin\n" + 160 " │ │ │ ├─ Table\n" + 161 " │ │ │ │ ├─ name: partsupp\n" + 162 " │ │ │ │ ├─ columns: [ps_partkey ps_suppkey ps_supplycost]\n" + 163 " │ │ │ │ ├─ colSet: (29-33)\n" + 164 " │ │ │ │ └─ tableId: 6\n" + 165 " │ │ │ └─ IndexedTableAccess(supplier)\n" + 166 " │ │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 167 " │ │ │ ├─ keys: [partsupp.ps_suppkey:29!null]\n" + 168 " │ │ │ ├─ colSet: (34-40)\n" + 169 " │ │ │ ├─ tableId: 7\n" + 170 " │ │ │ └─ Table\n" + 171 " │ │ │ ├─ name: supplier\n" + 172 " │ │ │ └─ columns: [s_suppkey s_nationkey]\n" + 173 " │ │ └─ IndexedTableAccess(nation)\n" + 174 " │ │ ├─ index: [nation.N_NATIONKEY]\n" + 175 " │ │ ├─ keys: [supplier.s_nationkey:32!null]\n" + 176 " │ │ ├─ colSet: (41-44)\n" + 177 " │ │ ├─ tableId: 8\n" + 178 " │ │ └─ Table\n" + 179 " │ │ ├─ name: nation\n" + 180 " │ │ └─ columns: [n_nationkey n_regionkey]\n" + 181 " │ └─ HashLookup\n" + 182 " │ ├─ left-key: TUPLE(nation.n_regionkey:34!null)\n" + 183 " │ ├─ right-key: TUPLE(region.r_regionkey:28!null)\n" + 184 " │ └─ Filter\n" + 185 " │ ├─ Eq\n" + 186 " │ │ ├─ region.r_name:29!null\n" + 187 " │ │ └─ EUROPE (longtext)\n" + 188 " │ └─ Table\n" + 189 " │ ├─ name: region\n" + 190 " │ ├─ columns: [r_regionkey r_name]\n" + 191 " │ ├─ colSet: (45-47)\n" + 192 " │ └─ tableId: 9\n" + 193 " └─ LookupJoin\n" + 194 " ├─ LookupJoin\n" + 195 " │ ├─ LookupJoin\n" + 196 " │ │ ├─ MergeJoin\n" + 197 " │ │ │ ├─ cmp: Eq\n" + 198 " │ │ │ │ ├─ part.p_partkey:0!null\n" + 199 " │ │ │ │ └─ partsupp.ps_partkey:9!null\n" + 200 " │ │ │ ├─ Filter\n" + 201 " │ │ │ │ ├─ AND\n" + 202 " │ │ │ │ │ ├─ Eq\n" + 203 " │ │ │ │ │ │ ├─ part.p_size:5!null\n" + 204 " │ │ │ │ │ │ └─ 15 (tinyint)\n" + 205 " │ │ │ │ │ └─ part.p_type LIKE '%BRASS'\n" + 206 " │ │ │ │ └─ IndexedTableAccess(part)\n" + 207 " │ │ │ │ ├─ index: [part.P_PARTKEY]\n" + 208 " │ │ │ │ ├─ static: [{[NULL, ∞)}]\n" + 209 " │ │ │ │ ├─ colSet: (1-9)\n" + 210 " │ │ │ │ ├─ tableId: 1\n" + 211 " │ │ │ │ └─ Table\n" + 212 " │ │ │ │ ├─ name: part\n" + 213 " │ │ │ │ └─ columns: [p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment]\n" + 214 " │ │ │ └─ IndexedTableAccess(partsupp)\n" + 215 " │ │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 216 " │ │ │ ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 217 " │ │ │ ├─ colSet: (17-21)\n" + 218 " │ │ │ ├─ tableId: 3\n" + 219 " │ │ │ └─ Table\n" + 220 " │ │ │ ├─ name: partsupp\n" + 221 " │ │ │ └─ columns: [ps_partkey ps_suppkey ps_availqty ps_supplycost ps_comment]\n" + 222 " │ │ └─ IndexedTableAccess(supplier)\n" + 223 " │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 224 " │ │ ├─ keys: [partsupp.ps_suppkey:10!null]\n" + 225 " │ │ ├─ colSet: (10-16)\n" + 226 " │ │ ├─ tableId: 2\n" + 227 " │ │ └─ Table\n" + 228 " │ │ ├─ name: supplier\n" + 229 " │ │ └─ columns: [s_suppkey s_name s_address s_nationkey s_phone s_acctbal s_comment]\n" + 230 " │ └─ IndexedTableAccess(nation)\n" + 231 " │ ├─ index: [nation.N_NATIONKEY]\n" + 232 " │ ├─ keys: [supplier.s_nationkey:17!null]\n" + 233 " │ ├─ colSet: (22-25)\n" + 234 " │ ├─ tableId: 4\n" + 235 " │ └─ Table\n" + 236 " │ ├─ name: nation\n" + 237 " │ └─ columns: [n_nationkey n_name n_regionkey n_comment]\n" + 238 " └─ Filter\n" + 239 " ├─ Eq\n" + 240 " │ ├─ region.r_name:1!null\n" + 241 " │ └─ EUROPE (longtext)\n" + 242 " └─ IndexedTableAccess(region)\n" + 243 " ├─ index: [region.R_REGIONKEY]\n" + 244 " ├─ keys: [nation.n_regionkey:23!null]\n" + 245 " ├─ colSet: (26-28)\n" + 246 " ├─ tableId: 5\n" + 247 " └─ Table\n" + 248 " ├─ name: region\n" + 249 " └─ columns: [r_regionkey r_name r_comment]\n" + 250 "", 251 ExpectedEstimates: "Project\n" + 252 " ├─ columns: [supplier.s_acctbal, supplier.s_name, nation.n_name, part.p_partkey, part.p_mfgr, supplier.s_address, supplier.s_phone, supplier.s_comment]\n" + 253 " └─ Sort(supplier.s_acctbal DESC, nation.n_name ASC, supplier.s_name ASC, part.p_partkey ASC)\n" + 254 " └─ Filter\n" + 255 " ├─ (partsupp.ps_supplycost = Subquery\n" + 256 " │ ├─ cacheable: false\n" + 257 " │ └─ Project\n" + 258 " │ ├─ columns: [min(partsupp.ps_supplycost) as min(ps_supplycost)]\n" + 259 " │ └─ GroupBy\n" + 260 " │ ├─ SelectedExprs(MIN(partsupp.ps_supplycost))\n" + 261 " │ ├─ Grouping()\n" + 262 " │ └─ Filter\n" + 263 " │ ├─ (part.p_partkey = partsupp.ps_partkey)\n" + 264 " │ └─ HashJoin\n" + 265 " │ ├─ (nation.n_regionkey = region.r_regionkey)\n" + 266 " │ ├─ LookupJoin\n" + 267 " │ │ ├─ LookupJoin\n" + 268 " │ │ │ ├─ Table\n" + 269 " │ │ │ │ ├─ name: partsupp\n" + 270 " │ │ │ │ └─ columns: [ps_partkey ps_suppkey ps_supplycost]\n" + 271 " │ │ │ └─ IndexedTableAccess(supplier)\n" + 272 " │ │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 273 " │ │ │ ├─ columns: [s_suppkey s_nationkey]\n" + 274 " │ │ │ └─ keys: partsupp.ps_suppkey\n" + 275 " │ │ └─ IndexedTableAccess(nation)\n" + 276 " │ │ ├─ index: [nation.N_NATIONKEY]\n" + 277 " │ │ ├─ columns: [n_nationkey n_regionkey]\n" + 278 " │ │ └─ keys: supplier.s_nationkey\n" + 279 " │ └─ HashLookup\n" + 280 " │ ├─ left-key: (nation.n_regionkey)\n" + 281 " │ ├─ right-key: (region.r_regionkey)\n" + 282 " │ └─ Filter\n" + 283 " │ ├─ (region.r_name = 'EUROPE')\n" + 284 " │ └─ Table\n" + 285 " │ ├─ name: region\n" + 286 " │ └─ columns: [r_regionkey r_name]\n" + 287 " │ )\n" + 288 " └─ LookupJoin\n" + 289 " ├─ LookupJoin\n" + 290 " │ ├─ LookupJoin\n" + 291 " │ │ ├─ MergeJoin\n" + 292 " │ │ │ ├─ cmp: (part.p_partkey = partsupp.ps_partkey)\n" + 293 " │ │ │ ├─ Filter\n" + 294 " │ │ │ │ ├─ ((part.p_size = 15) AND part.p_type LIKE '%BRASS')\n" + 295 " │ │ │ │ └─ IndexedTableAccess(part)\n" + 296 " │ │ │ │ ├─ index: [part.P_PARTKEY]\n" + 297 " │ │ │ │ └─ filters: [{[NULL, ∞)}]\n" + 298 " │ │ │ └─ IndexedTableAccess(partsupp)\n" + 299 " │ │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 300 " │ │ │ └─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 301 " │ │ └─ IndexedTableAccess(supplier)\n" + 302 " │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 303 " │ │ └─ keys: partsupp.ps_suppkey\n" + 304 " │ └─ IndexedTableAccess(nation)\n" + 305 " │ ├─ index: [nation.N_NATIONKEY]\n" + 306 " │ └─ keys: supplier.s_nationkey\n" + 307 " └─ Filter\n" + 308 " ├─ (region.r_name = 'EUROPE')\n" + 309 " └─ IndexedTableAccess(region)\n" + 310 " ├─ index: [region.R_REGIONKEY]\n" + 311 " └─ keys: nation.n_regionkey\n" + 312 "", 313 ExpectedAnalysis: "Project\n" + 314 " ├─ columns: [supplier.s_acctbal, supplier.s_name, nation.n_name, part.p_partkey, part.p_mfgr, supplier.s_address, supplier.s_phone, supplier.s_comment]\n" + 315 " └─ Sort(supplier.s_acctbal DESC, nation.n_name ASC, supplier.s_name ASC, part.p_partkey ASC)\n" + 316 " └─ Filter\n" + 317 " ├─ (partsupp.ps_supplycost = Subquery\n" + 318 " │ ├─ cacheable: false\n" + 319 " │ └─ Project\n" + 320 " │ ├─ columns: [min(partsupp.ps_supplycost) as min(ps_supplycost)]\n" + 321 " │ └─ GroupBy\n" + 322 " │ ├─ SelectedExprs(MIN(partsupp.ps_supplycost))\n" + 323 " │ ├─ Grouping()\n" + 324 " │ └─ Filter\n" + 325 " │ ├─ (part.p_partkey = partsupp.ps_partkey)\n" + 326 " │ └─ HashJoin\n" + 327 " │ ├─ (nation.n_regionkey = region.r_regionkey)\n" + 328 " │ ├─ LookupJoin\n" + 329 " │ │ ├─ LookupJoin\n" + 330 " │ │ │ ├─ Table\n" + 331 " │ │ │ │ ├─ name: partsupp\n" + 332 " │ │ │ │ └─ columns: [ps_partkey ps_suppkey ps_supplycost]\n" + 333 " │ │ │ └─ IndexedTableAccess(supplier)\n" + 334 " │ │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 335 " │ │ │ ├─ columns: [s_suppkey s_nationkey]\n" + 336 " │ │ │ └─ keys: partsupp.ps_suppkey\n" + 337 " │ │ └─ IndexedTableAccess(nation)\n" + 338 " │ │ ├─ index: [nation.N_NATIONKEY]\n" + 339 " │ │ ├─ columns: [n_nationkey n_regionkey]\n" + 340 " │ │ └─ keys: supplier.s_nationkey\n" + 341 " │ └─ HashLookup\n" + 342 " │ ├─ left-key: (nation.n_regionkey)\n" + 343 " │ ├─ right-key: (region.r_regionkey)\n" + 344 " │ └─ Filter\n" + 345 " │ ├─ (region.r_name = 'EUROPE')\n" + 346 " │ └─ Table\n" + 347 " │ ├─ name: region\n" + 348 " │ └─ columns: [r_regionkey r_name]\n" + 349 " │ )\n" + 350 " └─ LookupJoin\n" + 351 " ├─ LookupJoin\n" + 352 " │ ├─ LookupJoin\n" + 353 " │ │ ├─ MergeJoin\n" + 354 " │ │ │ ├─ cmp: (part.p_partkey = partsupp.ps_partkey)\n" + 355 " │ │ │ ├─ Filter\n" + 356 " │ │ │ │ ├─ ((part.p_size = 15) AND part.p_type LIKE '%BRASS')\n" + 357 " │ │ │ │ └─ IndexedTableAccess(part)\n" + 358 " │ │ │ │ ├─ index: [part.P_PARTKEY]\n" + 359 " │ │ │ │ └─ filters: [{[NULL, ∞)}]\n" + 360 " │ │ │ └─ IndexedTableAccess(partsupp)\n" + 361 " │ │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 362 " │ │ │ └─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 363 " │ │ └─ IndexedTableAccess(supplier)\n" + 364 " │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 365 " │ │ └─ keys: partsupp.ps_suppkey\n" + 366 " │ └─ IndexedTableAccess(nation)\n" + 367 " │ ├─ index: [nation.N_NATIONKEY]\n" + 368 " │ └─ keys: supplier.s_nationkey\n" + 369 " └─ Filter\n" + 370 " ├─ (region.r_name = 'EUROPE')\n" + 371 " └─ IndexedTableAccess(region)\n" + 372 " ├─ index: [region.R_REGIONKEY]\n" + 373 " └─ keys: nation.n_regionkey\n" + 374 "", 375 }, 376 { 377 Query: ` 378 --Q3 379 select 380 l_orderkey, 381 sum(l_extendedprice * (1 - l_discount)) as revenue, 382 o_orderdate, 383 o_shippriority 384 from 385 customer, 386 orders, 387 lineitem 388 where 389 c_mktsegment = 'BUILDING' 390 and c_custkey = o_custkey 391 and l_orderkey = o_orderkey 392 and o_orderdate < '1995-03-15' 393 and l_shipdate > '1995-03-15' 394 group by 395 l_orderkey, 396 o_orderdate, 397 o_shippriority 398 order by 399 revenue desc, 400 o_orderdate;`, 401 ExpectedPlan: "Project\n" + 402 " ├─ columns: [lineitem.l_orderkey:1!null, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue, orders.o_orderdate:2!null, orders.o_shippriority:3!null]\n" + 403 " └─ Sort(sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue DESC nullsFirst, orders.o_orderdate:2!null ASC nullsFirst)\n" + 404 " └─ Project\n" + 405 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null, lineitem.l_orderkey:1!null, orders.o_orderdate:2!null, orders.o_shippriority:3!null, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue]\n" + 406 " └─ GroupBy\n" + 407 " ├─ select: SUM((lineitem.l_extendedprice:5!null * (1 (tinyint) - lineitem.l_discount:6!null))), lineitem.l_orderkey:4!null, orders.o_orderdate:2!null, orders.o_shippriority:3!null\n" + 408 " ├─ group: lineitem.l_orderkey:4!null, orders.o_orderdate:2!null, orders.o_shippriority:3!null\n" + 409 " └─ LookupJoin\n" + 410 " ├─ MergeJoin\n" + 411 " │ ├─ cmp: Eq\n" + 412 " │ │ ├─ orders.o_orderkey:0!null\n" + 413 " │ │ └─ lineitem.l_orderkey:4!null\n" + 414 " │ ├─ Filter\n" + 415 " │ │ ├─ LessThan\n" + 416 " │ │ │ ├─ orders.o_orderdate:2!null\n" + 417 " │ │ │ └─ 1995-03-15 (longtext)\n" + 418 " │ │ └─ IndexedTableAccess(orders)\n" + 419 " │ │ ├─ index: [orders.O_ORDERKEY]\n" + 420 " │ │ ├─ static: [{[NULL, ∞)}]\n" + 421 " │ │ ├─ colSet: (9-17)\n" + 422 " │ │ ├─ tableId: 2\n" + 423 " │ │ └─ Table\n" + 424 " │ │ ├─ name: orders\n" + 425 " │ │ └─ columns: [o_orderkey o_custkey o_orderdate o_shippriority]\n" + 426 " │ └─ Filter\n" + 427 " │ ├─ GreaterThan\n" + 428 " │ │ ├─ lineitem.l_shipdate:3!null\n" + 429 " │ │ └─ 1995-03-15 (longtext)\n" + 430 " │ └─ IndexedTableAccess(lineitem)\n" + 431 " │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 432 " │ ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 433 " │ ├─ colSet: (18-33)\n" + 434 " │ ├─ tableId: 3\n" + 435 " │ └─ Table\n" + 436 " │ ├─ name: lineitem\n" + 437 " │ └─ columns: [l_orderkey l_extendedprice l_discount l_shipdate]\n" + 438 " └─ Filter\n" + 439 " ├─ Eq\n" + 440 " │ ├─ customer.c_mktsegment:1!null\n" + 441 " │ └─ BUILDING (longtext)\n" + 442 " └─ IndexedTableAccess(customer)\n" + 443 " ├─ index: [customer.C_CUSTKEY]\n" + 444 " ├─ keys: [orders.o_custkey:1!null]\n" + 445 " ├─ colSet: (1-8)\n" + 446 " ├─ tableId: 1\n" + 447 " └─ Table\n" + 448 " ├─ name: customer\n" + 449 " └─ columns: [c_custkey c_mktsegment]\n" + 450 "", 451 ExpectedEstimates: "Project\n" + 452 " ├─ columns: [lineitem.l_orderkey, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue, orders.o_orderdate, orders.o_shippriority]\n" + 453 " └─ Sort(sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue DESC, orders.o_orderdate ASC)\n" + 454 " └─ Project\n" + 455 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))), lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 456 " └─ GroupBy\n" + 457 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority)\n" + 458 " ├─ Grouping(lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority)\n" + 459 " └─ LookupJoin\n" + 460 " ├─ MergeJoin\n" + 461 " │ ├─ cmp: (orders.o_orderkey = lineitem.l_orderkey)\n" + 462 " │ ├─ Filter\n" + 463 " │ │ ├─ (orders.o_orderdate < '1995-03-15')\n" + 464 " │ │ └─ IndexedTableAccess(orders)\n" + 465 " │ │ ├─ index: [orders.O_ORDERKEY]\n" + 466 " │ │ ├─ filters: [{[NULL, ∞)}]\n" + 467 " │ │ └─ columns: [o_orderkey o_custkey o_orderdate o_shippriority]\n" + 468 " │ └─ Filter\n" + 469 " │ ├─ (lineitem.l_shipdate > '1995-03-15')\n" + 470 " │ └─ IndexedTableAccess(lineitem)\n" + 471 " │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 472 " │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 473 " │ └─ columns: [l_orderkey l_extendedprice l_discount l_shipdate]\n" + 474 " └─ Filter\n" + 475 " ├─ (customer.c_mktsegment = 'BUILDING')\n" + 476 " └─ IndexedTableAccess(customer)\n" + 477 " ├─ index: [customer.C_CUSTKEY]\n" + 478 " ├─ columns: [c_custkey c_mktsegment]\n" + 479 " └─ keys: orders.o_custkey\n" + 480 "", 481 ExpectedAnalysis: "Project\n" + 482 " ├─ columns: [lineitem.l_orderkey, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue, orders.o_orderdate, orders.o_shippriority]\n" + 483 " └─ Sort(sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue DESC, orders.o_orderdate ASC)\n" + 484 " └─ Project\n" + 485 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))), lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 486 " └─ GroupBy\n" + 487 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority)\n" + 488 " ├─ Grouping(lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority)\n" + 489 " └─ LookupJoin\n" + 490 " ├─ MergeJoin\n" + 491 " │ ├─ cmp: (orders.o_orderkey = lineitem.l_orderkey)\n" + 492 " │ ├─ Filter\n" + 493 " │ │ ├─ (orders.o_orderdate < '1995-03-15')\n" + 494 " │ │ └─ IndexedTableAccess(orders)\n" + 495 " │ │ ├─ index: [orders.O_ORDERKEY]\n" + 496 " │ │ ├─ filters: [{[NULL, ∞)}]\n" + 497 " │ │ └─ columns: [o_orderkey o_custkey o_orderdate o_shippriority]\n" + 498 " │ └─ Filter\n" + 499 " │ ├─ (lineitem.l_shipdate > '1995-03-15')\n" + 500 " │ └─ IndexedTableAccess(lineitem)\n" + 501 " │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 502 " │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 503 " │ └─ columns: [l_orderkey l_extendedprice l_discount l_shipdate]\n" + 504 " └─ Filter\n" + 505 " ├─ (customer.c_mktsegment = 'BUILDING')\n" + 506 " └─ IndexedTableAccess(customer)\n" + 507 " ├─ index: [customer.C_CUSTKEY]\n" + 508 " ├─ columns: [c_custkey c_mktsegment]\n" + 509 " └─ keys: orders.o_custkey\n" + 510 "", 511 }, 512 { 513 Query: ` 514 --Q4 515 select 516 o_orderpriority, 517 count(*) as order_count 518 from 519 orders 520 where 521 o_orderdate >= '1993-07-01' 522 and o_orderdate < '1993-07-01' + interval '3' month 523 and exists ( 524 select 525 * 526 from 527 lineitem 528 where 529 l_orderkey = o_orderkey 530 and l_commitdate < l_receiptdate 531 ) 532 group by 533 o_orderpriority 534 order by 535 o_orderpriority;`, 536 ExpectedPlan: "Project\n" + 537 " ├─ columns: [orders.o_orderpriority:1!null, count(1):0!null as order_count]\n" + 538 " └─ Sort(orders.o_orderpriority:1!null ASC nullsFirst)\n" + 539 " └─ Project\n" + 540 " ├─ columns: [count(1):0!null, orders.o_orderpriority:1!null, count(1):0!null as order_count]\n" + 541 " └─ GroupBy\n" + 542 " ├─ select: COUNT(1 (bigint)), orders.o_orderpriority:5!null\n" + 543 " ├─ group: orders.o_orderpriority:5!null\n" + 544 " └─ Project\n" + 545 " ├─ columns: [orders.O_ORDERKEY:0!null, orders.O_CUSTKEY:1!null, orders.O_ORDERSTATUS:2!null, orders.O_TOTALPRICE:3!null, orders.O_ORDERDATE:4!null, orders.O_ORDERPRIORITY:5!null, orders.O_CLERK:6!null, orders.O_SHIPPRIORITY:7!null, orders.O_COMMENT:8!null]\n" + 546 " └─ MergeJoin\n" + 547 " ├─ cmp: Eq\n" + 548 " │ ├─ orders.o_orderkey:0!null\n" + 549 " │ └─ lineitem.l_orderkey:9!null\n" + 550 " ├─ Filter\n" + 551 " │ ├─ AND\n" + 552 " │ │ ├─ GreaterThanOrEqual\n" + 553 " │ │ │ ├─ orders.o_orderdate:4!null\n" + 554 " │ │ │ └─ 1993-07-01 (longtext)\n" + 555 " │ │ └─ LessThan\n" + 556 " │ │ ├─ orders.o_orderdate:4!null\n" + 557 " │ │ └─ 1993-10-01 00:00:00 +0000 UTC (datetime)\n" + 558 " │ └─ IndexedTableAccess(orders)\n" + 559 " │ ├─ index: [orders.O_ORDERKEY]\n" + 560 " │ ├─ static: [{[NULL, ∞)}]\n" + 561 " │ ├─ colSet: (1-9)\n" + 562 " │ ├─ tableId: 1\n" + 563 " │ └─ Table\n" + 564 " │ ├─ name: orders\n" + 565 " │ └─ columns: [o_orderkey o_custkey o_orderstatus o_totalprice o_orderdate o_orderpriority o_clerk o_shippriority o_comment]\n" + 566 " └─ Project\n" + 567 " ├─ columns: [lineitem.l_orderkey:0!null]\n" + 568 " └─ Filter\n" + 569 " ├─ LessThan\n" + 570 " │ ├─ lineitem.l_commitdate:11!null\n" + 571 " │ └─ lineitem.l_receiptdate:12!null\n" + 572 " └─ IndexedTableAccess(lineitem)\n" + 573 " ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 574 " ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 575 " ├─ colSet: (10-25)\n" + 576 " ├─ tableId: 2\n" + 577 " └─ Table\n" + 578 " ├─ name: lineitem\n" + 579 " └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 580 "", 581 ExpectedEstimates: "Project\n" + 582 " ├─ columns: [orders.o_orderpriority, count(1) as order_count]\n" + 583 " └─ Sort(orders.o_orderpriority ASC)\n" + 584 " └─ Project\n" + 585 " ├─ columns: [count(1), orders.o_orderpriority, count(1) as order_count]\n" + 586 " └─ GroupBy\n" + 587 " ├─ SelectedExprs(COUNT(1), orders.o_orderpriority)\n" + 588 " ├─ Grouping(orders.o_orderpriority)\n" + 589 " └─ Project\n" + 590 " ├─ columns: [orders.O_ORDERKEY, orders.O_CUSTKEY, orders.O_ORDERSTATUS, orders.O_TOTALPRICE, orders.O_ORDERDATE, orders.O_ORDERPRIORITY, orders.O_CLERK, orders.O_SHIPPRIORITY, orders.O_COMMENT]\n" + 591 " └─ MergeJoin\n" + 592 " ├─ cmp: (orders.o_orderkey = lineitem.l_orderkey)\n" + 593 " ├─ Filter\n" + 594 " │ ├─ ((orders.o_orderdate >= '1993-07-01') AND (orders.o_orderdate < 1993-10-01 00:00:00 +0000 UTC))\n" + 595 " │ └─ IndexedTableAccess(orders)\n" + 596 " │ ├─ index: [orders.O_ORDERKEY]\n" + 597 " │ └─ filters: [{[NULL, ∞)}]\n" + 598 " └─ Project\n" + 599 " ├─ columns: [lineitem.l_orderkey]\n" + 600 " └─ Filter\n" + 601 " ├─ (lineitem.l_commitdate < lineitem.l_receiptdate)\n" + 602 " └─ IndexedTableAccess(lineitem)\n" + 603 " ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 604 " ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 605 " └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 606 "", 607 ExpectedAnalysis: "Project\n" + 608 " ├─ columns: [orders.o_orderpriority, count(1) as order_count]\n" + 609 " └─ Sort(orders.o_orderpriority ASC)\n" + 610 " └─ Project\n" + 611 " ├─ columns: [count(1), orders.o_orderpriority, count(1) as order_count]\n" + 612 " └─ GroupBy\n" + 613 " ├─ SelectedExprs(COUNT(1), orders.o_orderpriority)\n" + 614 " ├─ Grouping(orders.o_orderpriority)\n" + 615 " └─ Project\n" + 616 " ├─ columns: [orders.O_ORDERKEY, orders.O_CUSTKEY, orders.O_ORDERSTATUS, orders.O_TOTALPRICE, orders.O_ORDERDATE, orders.O_ORDERPRIORITY, orders.O_CLERK, orders.O_SHIPPRIORITY, orders.O_COMMENT]\n" + 617 " └─ MergeJoin\n" + 618 " ├─ cmp: (orders.o_orderkey = lineitem.l_orderkey)\n" + 619 " ├─ Filter\n" + 620 " │ ├─ ((orders.o_orderdate >= '1993-07-01') AND (orders.o_orderdate < 1993-10-01 00:00:00 +0000 UTC))\n" + 621 " │ └─ IndexedTableAccess(orders)\n" + 622 " │ ├─ index: [orders.O_ORDERKEY]\n" + 623 " │ └─ filters: [{[NULL, ∞)}]\n" + 624 " └─ Project\n" + 625 " ├─ columns: [lineitem.l_orderkey]\n" + 626 " └─ Filter\n" + 627 " ├─ (lineitem.l_commitdate < lineitem.l_receiptdate)\n" + 628 " └─ IndexedTableAccess(lineitem)\n" + 629 " ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 630 " ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 631 " └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 632 "", 633 }, 634 { 635 Query: ` 636 --Q5 637 select 638 n_name, 639 sum(l_extendedprice * (1 - l_discount)) as revenue 640 from 641 customer, 642 orders, 643 lineitem, 644 supplier, 645 nation, 646 region 647 where 648 c_custkey = o_custkey 649 and l_orderkey = o_orderkey 650 and l_suppkey = s_suppkey 651 and c_nationkey = s_nationkey 652 and s_nationkey = n_nationkey 653 and n_regionkey = r_regionkey 654 and r_name = 'ASIA' 655 and o_orderdate >= '1994-01-01' 656 and o_orderdate < '1994-01-01' + interval '1' year 657 group by 658 n_name 659 order by 660 revenue desc;`, 661 ExpectedPlan: "Project\n" + 662 " ├─ columns: [nation.n_name:1!null, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue]\n" + 663 " └─ Sort(sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue DESC nullsFirst)\n" + 664 " └─ Project\n" + 665 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null, nation.n_name:1!null, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue]\n" + 666 " └─ GroupBy\n" + 667 " ├─ select: SUM((lineitem.l_extendedprice:12!null * (1 (tinyint) - lineitem.l_discount:13!null))), nation.n_name:6!null\n" + 668 " ├─ group: nation.n_name:6!null\n" + 669 " └─ LookupJoin\n" + 670 " ├─ AND\n" + 671 " │ ├─ Eq\n" + 672 " │ │ ├─ customer.c_nationkey:4!null\n" + 673 " │ │ └─ supplier.s_nationkey:15!null\n" + 674 " │ └─ Eq\n" + 675 " │ ├─ supplier.s_nationkey:15!null\n" + 676 " │ └─ nation.n_nationkey:5!null\n" + 677 " ├─ LookupJoin\n" + 678 " │ ├─ LookupJoin\n" + 679 " │ │ ├─ LookupJoin\n" + 680 " │ │ │ ├─ LookupJoin\n" + 681 " │ │ │ │ ├─ Filter\n" + 682 " │ │ │ │ │ ├─ AND\n" + 683 " │ │ │ │ │ │ ├─ GreaterThanOrEqual\n" + 684 " │ │ │ │ │ │ │ ├─ orders.o_orderdate:2!null\n" + 685 " │ │ │ │ │ │ │ └─ 1994-01-01 (longtext)\n" + 686 " │ │ │ │ │ │ └─ LessThan\n" + 687 " │ │ │ │ │ │ ├─ orders.o_orderdate:2!null\n" + 688 " │ │ │ │ │ │ └─ 1995-01-01 00:00:00 +0000 UTC (datetime)\n" + 689 " │ │ │ │ │ └─ ProcessTable\n" + 690 " │ │ │ │ │ └─ Table\n" + 691 " │ │ │ │ │ ├─ name: orders\n" + 692 " │ │ │ │ │ └─ columns: [o_orderkey o_custkey o_orderdate]\n" + 693 " │ │ │ │ └─ IndexedTableAccess(customer)\n" + 694 " │ │ │ │ ├─ index: [customer.C_CUSTKEY]\n" + 695 " │ │ │ │ ├─ keys: [orders.o_custkey:1!null]\n" + 696 " │ │ │ │ ├─ colSet: (1-8)\n" + 697 " │ │ │ │ ├─ tableId: 1\n" + 698 " │ │ │ │ └─ Table\n" + 699 " │ │ │ │ ├─ name: customer\n" + 700 " │ │ │ │ └─ columns: [c_custkey c_nationkey]\n" + 701 " │ │ │ └─ IndexedTableAccess(nation)\n" + 702 " │ │ │ ├─ index: [nation.N_NATIONKEY]\n" + 703 " │ │ │ ├─ keys: [customer.c_nationkey:4!null]\n" + 704 " │ │ │ ├─ colSet: (41-44)\n" + 705 " │ │ │ ├─ tableId: 5\n" + 706 " │ │ │ └─ Table\n" + 707 " │ │ │ ├─ name: nation\n" + 708 " │ │ │ └─ columns: [n_nationkey n_name n_regionkey]\n" + 709 " │ │ └─ Filter\n" + 710 " │ │ ├─ Eq\n" + 711 " │ │ │ ├─ region.r_name:1!null\n" + 712 " │ │ │ └─ ASIA (longtext)\n" + 713 " │ │ └─ IndexedTableAccess(region)\n" + 714 " │ │ ├─ index: [region.R_REGIONKEY]\n" + 715 " │ │ ├─ keys: [nation.n_regionkey:7!null]\n" + 716 " │ │ ├─ colSet: (45-47)\n" + 717 " │ │ ├─ tableId: 6\n" + 718 " │ │ └─ Table\n" + 719 " │ │ ├─ name: region\n" + 720 " │ │ └─ columns: [r_regionkey r_name]\n" + 721 " │ └─ IndexedTableAccess(lineitem)\n" + 722 " │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 723 " │ ├─ keys: [orders.o_orderkey:0!null]\n" + 724 " │ ├─ colSet: (18-33)\n" + 725 " │ ├─ tableId: 3\n" + 726 " │ └─ Table\n" + 727 " │ ├─ name: lineitem\n" + 728 " │ └─ columns: [l_orderkey l_suppkey l_extendedprice l_discount]\n" + 729 " └─ IndexedTableAccess(supplier)\n" + 730 " ├─ index: [supplier.S_SUPPKEY]\n" + 731 " ├─ keys: [lineitem.l_suppkey:11!null]\n" + 732 " ├─ colSet: (34-40)\n" + 733 " ├─ tableId: 4\n" + 734 " └─ Table\n" + 735 " ├─ name: supplier\n" + 736 " └─ columns: [s_suppkey s_nationkey]\n" + 737 "", 738 ExpectedEstimates: "Project\n" + 739 " ├─ columns: [nation.n_name, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 740 " └─ Sort(sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue DESC)\n" + 741 " └─ Project\n" + 742 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))), nation.n_name, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 743 " └─ GroupBy\n" + 744 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), nation.n_name)\n" + 745 " ├─ Grouping(nation.n_name)\n" + 746 " └─ LookupJoin\n" + 747 " ├─ ((customer.c_nationkey = supplier.s_nationkey) AND (supplier.s_nationkey = nation.n_nationkey))\n" + 748 " ├─ LookupJoin\n" + 749 " │ ├─ LookupJoin\n" + 750 " │ │ ├─ LookupJoin\n" + 751 " │ │ │ ├─ LookupJoin\n" + 752 " │ │ │ │ ├─ Filter\n" + 753 " │ │ │ │ │ ├─ ((orders.o_orderdate >= '1994-01-01') AND (orders.o_orderdate < 1995-01-01 00:00:00 +0000 UTC))\n" + 754 " │ │ │ │ │ └─ Table\n" + 755 " │ │ │ │ │ ├─ name: orders\n" + 756 " │ │ │ │ │ └─ columns: [o_orderkey o_custkey o_orderdate]\n" + 757 " │ │ │ │ └─ IndexedTableAccess(customer)\n" + 758 " │ │ │ │ ├─ index: [customer.C_CUSTKEY]\n" + 759 " │ │ │ │ ├─ columns: [c_custkey c_nationkey]\n" + 760 " │ │ │ │ └─ keys: orders.o_custkey\n" + 761 " │ │ │ └─ IndexedTableAccess(nation)\n" + 762 " │ │ │ ├─ index: [nation.N_NATIONKEY]\n" + 763 " │ │ │ ├─ columns: [n_nationkey n_name n_regionkey]\n" + 764 " │ │ │ └─ keys: customer.c_nationkey\n" + 765 " │ │ └─ Filter\n" + 766 " │ │ ├─ (region.r_name = 'ASIA')\n" + 767 " │ │ └─ IndexedTableAccess(region)\n" + 768 " │ │ ├─ index: [region.R_REGIONKEY]\n" + 769 " │ │ ├─ columns: [r_regionkey r_name]\n" + 770 " │ │ └─ keys: nation.n_regionkey\n" + 771 " │ └─ IndexedTableAccess(lineitem)\n" + 772 " │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 773 " │ ├─ columns: [l_orderkey l_suppkey l_extendedprice l_discount]\n" + 774 " │ └─ keys: orders.o_orderkey\n" + 775 " └─ IndexedTableAccess(supplier)\n" + 776 " ├─ index: [supplier.S_SUPPKEY]\n" + 777 " ├─ columns: [s_suppkey s_nationkey]\n" + 778 " └─ keys: lineitem.l_suppkey\n" + 779 "", 780 ExpectedAnalysis: "Project\n" + 781 " ├─ columns: [nation.n_name, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 782 " └─ Sort(sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue DESC)\n" + 783 " └─ Project\n" + 784 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))), nation.n_name, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 785 " └─ GroupBy\n" + 786 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), nation.n_name)\n" + 787 " ├─ Grouping(nation.n_name)\n" + 788 " └─ LookupJoin\n" + 789 " ├─ ((customer.c_nationkey = supplier.s_nationkey) AND (supplier.s_nationkey = nation.n_nationkey))\n" + 790 " ├─ LookupJoin\n" + 791 " │ ├─ LookupJoin\n" + 792 " │ │ ├─ LookupJoin\n" + 793 " │ │ │ ├─ LookupJoin\n" + 794 " │ │ │ │ ├─ Filter\n" + 795 " │ │ │ │ │ ├─ ((orders.o_orderdate >= '1994-01-01') AND (orders.o_orderdate < 1995-01-01 00:00:00 +0000 UTC))\n" + 796 " │ │ │ │ │ └─ Table\n" + 797 " │ │ │ │ │ ├─ name: orders\n" + 798 " │ │ │ │ │ └─ columns: [o_orderkey o_custkey o_orderdate]\n" + 799 " │ │ │ │ └─ IndexedTableAccess(customer)\n" + 800 " │ │ │ │ ├─ index: [customer.C_CUSTKEY]\n" + 801 " │ │ │ │ ├─ columns: [c_custkey c_nationkey]\n" + 802 " │ │ │ │ └─ keys: orders.o_custkey\n" + 803 " │ │ │ └─ IndexedTableAccess(nation)\n" + 804 " │ │ │ ├─ index: [nation.N_NATIONKEY]\n" + 805 " │ │ │ ├─ columns: [n_nationkey n_name n_regionkey]\n" + 806 " │ │ │ └─ keys: customer.c_nationkey\n" + 807 " │ │ └─ Filter\n" + 808 " │ │ ├─ (region.r_name = 'ASIA')\n" + 809 " │ │ └─ IndexedTableAccess(region)\n" + 810 " │ │ ├─ index: [region.R_REGIONKEY]\n" + 811 " │ │ ├─ columns: [r_regionkey r_name]\n" + 812 " │ │ └─ keys: nation.n_regionkey\n" + 813 " │ └─ IndexedTableAccess(lineitem)\n" + 814 " │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 815 " │ ├─ columns: [l_orderkey l_suppkey l_extendedprice l_discount]\n" + 816 " │ └─ keys: orders.o_orderkey\n" + 817 " └─ IndexedTableAccess(supplier)\n" + 818 " ├─ index: [supplier.S_SUPPKEY]\n" + 819 " ├─ columns: [s_suppkey s_nationkey]\n" + 820 " └─ keys: lineitem.l_suppkey\n" + 821 "", 822 }, 823 { 824 Query: ` 825 --Q6 826 select 827 sum(l_extendedprice * l_discount) as revenue 828 from 829 lineitem 830 where 831 l_shipdate >= '1994-01-01' 832 and l_shipdate < '1994-01-01' + interval '1' year 833 and l_discount between .06 - 0.01 and .06 + 0.01 834 and l_quantity < 24;`, 835 ExpectedPlan: "Project\n" + 836 " ├─ columns: [sum((lineitem.l_extendedprice * lineitem.l_discount)):0!null as revenue]\n" + 837 " └─ GroupBy\n" + 838 " ├─ select: SUM((lineitem.l_extendedprice:1!null * lineitem.l_discount:2!null))\n" + 839 " ├─ group: \n" + 840 " └─ Filter\n" + 841 " ├─ AND\n" + 842 " │ ├─ AND\n" + 843 " │ │ ├─ AND\n" + 844 " │ │ │ ├─ GreaterThanOrEqual\n" + 845 " │ │ │ │ ├─ lineitem.l_shipdate:3!null\n" + 846 " │ │ │ │ └─ 1994-01-01 (longtext)\n" + 847 " │ │ │ └─ LessThan\n" + 848 " │ │ │ ├─ lineitem.l_shipdate:3!null\n" + 849 " │ │ │ └─ 1995-01-01 00:00:00 +0000 UTC (datetime)\n" + 850 " │ │ └─ AND\n" + 851 " │ │ ├─ GreaterThanOrEqual\n" + 852 " │ │ │ ├─ lineitem.l_discount:2!null\n" + 853 " │ │ │ └─ 0.05 (decimal(5,2))\n" + 854 " │ │ └─ LessThanOrEqual\n" + 855 " │ │ ├─ lineitem.l_discount:2!null\n" + 856 " │ │ └─ 0.07 (decimal(5,2))\n" + 857 " │ └─ LessThan\n" + 858 " │ ├─ lineitem.l_quantity:0!null\n" + 859 " │ └─ 24 (tinyint)\n" + 860 " └─ ProcessTable\n" + 861 " └─ Table\n" + 862 " ├─ name: lineitem\n" + 863 " └─ columns: [l_quantity l_extendedprice l_discount l_shipdate]\n" + 864 "", 865 ExpectedEstimates: "Project\n" + 866 " ├─ columns: [sum((lineitem.l_extendedprice * lineitem.l_discount)) as revenue]\n" + 867 " └─ GroupBy\n" + 868 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * lineitem.l_discount)))\n" + 869 " ├─ Grouping()\n" + 870 " └─ Filter\n" + 871 " ├─ ((((lineitem.l_shipdate >= '1994-01-01') AND (lineitem.l_shipdate < 1995-01-01 00:00:00 +0000 UTC)) AND ((lineitem.l_discount >= 0.05) AND (lineitem.l_discount <= 0.07))) AND (lineitem.l_quantity < 24))\n" + 872 " └─ Table\n" + 873 " ├─ name: lineitem\n" + 874 " └─ columns: [l_quantity l_extendedprice l_discount l_shipdate]\n" + 875 "", 876 ExpectedAnalysis: "Project\n" + 877 " ├─ columns: [sum((lineitem.l_extendedprice * lineitem.l_discount)) as revenue]\n" + 878 " └─ GroupBy\n" + 879 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * lineitem.l_discount)))\n" + 880 " ├─ Grouping()\n" + 881 " └─ Filter\n" + 882 " ├─ ((((lineitem.l_shipdate >= '1994-01-01') AND (lineitem.l_shipdate < 1995-01-01 00:00:00 +0000 UTC)) AND ((lineitem.l_discount >= 0.05) AND (lineitem.l_discount <= 0.07))) AND (lineitem.l_quantity < 24))\n" + 883 " └─ Table\n" + 884 " ├─ name: lineitem\n" + 885 " └─ columns: [l_quantity l_extendedprice l_discount l_shipdate]\n" + 886 "", 887 }, 888 { 889 Query: ` 890 --Q7 891 select 892 supp_nation, 893 cust_nation, 894 l_year, 895 sum(volume) as revenue 896 from 897 ( 898 select 899 n1.n_name as supp_nation, 900 n2.n_name as cust_nation, 901 extract(year from l_shipdate) as l_year, 902 l_extendedprice * (1 - l_discount) as volume 903 from 904 supplier, 905 lineitem, 906 orders, 907 customer, 908 nation n1, 909 nation n2 910 where 911 s_suppkey = l_suppkey 912 and o_orderkey = l_orderkey 913 and c_custkey = o_custkey 914 and s_nationkey = n1.n_nationkey 915 and c_nationkey = n2.n_nationkey 916 and ( 917 (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY') 918 or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE') 919 ) 920 and l_shipdate between '1995-01-01' and '1996-12-31' 921 ) as shipping 922 group by 923 supp_nation, 924 cust_nation, 925 l_year 926 order by 927 supp_nation, 928 cust_nation, 929 l_year;`, 930 ExpectedPlan: "Project\n" + 931 " ├─ columns: [shipping.supp_nation:1!null, shipping.cust_nation:2!null, shipping.l_year:3!null, sum(shipping.volume):0!null as revenue]\n" + 932 " └─ Sort(shipping.supp_nation:1!null ASC nullsFirst, shipping.cust_nation:2!null ASC nullsFirst, shipping.l_year:3!null ASC nullsFirst)\n" + 933 " └─ Project\n" + 934 " ├─ columns: [sum(shipping.volume):0!null, shipping.supp_nation:1!null, shipping.cust_nation:2!null, shipping.l_year:3!null, sum(shipping.volume):0!null as revenue]\n" + 935 " └─ GroupBy\n" + 936 " ├─ select: SUM(shipping.volume:3!null), shipping.supp_nation:0!null, shipping.cust_nation:1!null, shipping.l_year:2!null\n" + 937 " ├─ group: shipping.supp_nation:0!null, shipping.cust_nation:1!null, shipping.l_year:2!null\n" + 938 " └─ SubqueryAlias\n" + 939 " ├─ name: shipping\n" + 940 " ├─ outerVisibility: false\n" + 941 " ├─ isLateral: false\n" + 942 " ├─ cacheable: true\n" + 943 " ├─ colSet: (53-56)\n" + 944 " ├─ tableId: 7\n" + 945 " └─ Project\n" + 946 " ├─ columns: [n1.n_name:12!null as supp_nation, n2.n_name:14!null as cust_nation, extract('YEAR' from lineitem.l_shipdate) as l_year, (lineitem.l_extendedprice:2!null * (1 (tinyint) - lineitem.l_discount:3!null)) as volume]\n" + 947 " └─ Filter\n" + 948 " ├─ Or\n" + 949 " │ ├─ AND\n" + 950 " │ │ ├─ Eq\n" + 951 " │ │ │ ├─ n1.n_name:12!null\n" + 952 " │ │ │ └─ FRANCE (longtext)\n" + 953 " │ │ └─ Eq\n" + 954 " │ │ ├─ n2.n_name:14!null\n" + 955 " │ │ └─ GERMANY (longtext)\n" + 956 " │ └─ AND\n" + 957 " │ ├─ Eq\n" + 958 " │ │ ├─ n1.n_name:12!null\n" + 959 " │ │ └─ GERMANY (longtext)\n" + 960 " │ └─ Eq\n" + 961 " │ ├─ n2.n_name:14!null\n" + 962 " │ └─ FRANCE (longtext)\n" + 963 " └─ LookupJoin\n" + 964 " ├─ LookupJoin\n" + 965 " │ ├─ LookupJoin\n" + 966 " │ │ ├─ LookupJoin\n" + 967 " │ │ │ ├─ MergeJoin\n" + 968 " │ │ │ │ ├─ cmp: Eq\n" + 969 " │ │ │ │ │ ├─ lineitem.l_orderkey:0!null\n" + 970 " │ │ │ │ │ └─ orders.o_orderkey:5!null\n" + 971 " │ │ │ │ ├─ Filter\n" + 972 " │ │ │ │ │ ├─ AND\n" + 973 " │ │ │ │ │ │ ├─ GreaterThanOrEqual\n" + 974 " │ │ │ │ │ │ │ ├─ lineitem.l_shipdate:4!null\n" + 975 " │ │ │ │ │ │ │ └─ 1995-01-01 (longtext)\n" + 976 " │ │ │ │ │ │ └─ LessThanOrEqual\n" + 977 " │ │ │ │ │ │ ├─ lineitem.l_shipdate:4!null\n" + 978 " │ │ │ │ │ │ └─ 1996-12-31 (longtext)\n" + 979 " │ │ │ │ │ └─ IndexedTableAccess(lineitem)\n" + 980 " │ │ │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 981 " │ │ │ │ │ ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 982 " │ │ │ │ │ ├─ colSet: (8-23)\n" + 983 " │ │ │ │ │ ├─ tableId: 2\n" + 984 " │ │ │ │ │ └─ Table\n" + 985 " │ │ │ │ │ ├─ name: lineitem\n" + 986 " │ │ │ │ │ └─ columns: [l_orderkey l_suppkey l_extendedprice l_discount l_shipdate]\n" + 987 " │ │ │ │ └─ IndexedTableAccess(orders)\n" + 988 " │ │ │ │ ├─ index: [orders.O_ORDERKEY]\n" + 989 " │ │ │ │ ├─ static: [{[NULL, ∞)}]\n" + 990 " │ │ │ │ ├─ colSet: (24-32)\n" + 991 " │ │ │ │ ├─ tableId: 3\n" + 992 " │ │ │ │ └─ Table\n" + 993 " │ │ │ │ ├─ name: orders\n" + 994 " │ │ │ │ └─ columns: [o_orderkey o_custkey]\n" + 995 " │ │ │ └─ IndexedTableAccess(supplier)\n" + 996 " │ │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 997 " │ │ │ ├─ keys: [lineitem.l_suppkey:1!null]\n" + 998 " │ │ │ ├─ colSet: (1-7)\n" + 999 " │ │ │ ├─ tableId: 1\n" + 1000 " │ │ │ └─ Table\n" + 1001 " │ │ │ ├─ name: supplier\n" + 1002 " │ │ │ └─ columns: [s_suppkey s_nationkey]\n" + 1003 " │ │ └─ IndexedTableAccess(customer)\n" + 1004 " │ │ ├─ index: [customer.C_CUSTKEY]\n" + 1005 " │ │ ├─ keys: [orders.o_custkey:6!null]\n" + 1006 " │ │ ├─ colSet: (33-40)\n" + 1007 " │ │ ├─ tableId: 4\n" + 1008 " │ │ └─ Table\n" + 1009 " │ │ ├─ name: customer\n" + 1010 " │ │ └─ columns: [c_custkey c_nationkey]\n" + 1011 " │ └─ TableAlias(n1)\n" + 1012 " │ └─ IndexedTableAccess(nation)\n" + 1013 " │ ├─ index: [nation.N_NATIONKEY]\n" + 1014 " │ ├─ keys: [supplier.s_nationkey:8!null]\n" + 1015 " │ ├─ colSet: (41-44)\n" + 1016 " │ ├─ tableId: 5\n" + 1017 " │ └─ Table\n" + 1018 " │ ├─ name: nation\n" + 1019 " │ └─ columns: [n_nationkey n_name]\n" + 1020 " └─ TableAlias(n2)\n" + 1021 " └─ IndexedTableAccess(nation)\n" + 1022 " ├─ index: [nation.N_NATIONKEY]\n" + 1023 " ├─ keys: [customer.c_nationkey:10!null]\n" + 1024 " ├─ colSet: (45-48)\n" + 1025 " ├─ tableId: 6\n" + 1026 " └─ Table\n" + 1027 " ├─ name: nation\n" + 1028 " └─ columns: [n_nationkey n_name]\n" + 1029 "", 1030 ExpectedEstimates: "Project\n" + 1031 " ├─ columns: [shipping.supp_nation, shipping.cust_nation, shipping.l_year, sum(shipping.volume) as revenue]\n" + 1032 " └─ Sort(shipping.supp_nation ASC, shipping.cust_nation ASC, shipping.l_year ASC)\n" + 1033 " └─ Project\n" + 1034 " ├─ columns: [sum(shipping.volume), shipping.supp_nation, shipping.cust_nation, shipping.l_year, sum(shipping.volume) as revenue]\n" + 1035 " └─ GroupBy\n" + 1036 " ├─ SelectedExprs(SUM(shipping.volume), shipping.supp_nation, shipping.cust_nation, shipping.l_year)\n" + 1037 " ├─ Grouping(shipping.supp_nation, shipping.cust_nation, shipping.l_year)\n" + 1038 " └─ SubqueryAlias\n" + 1039 " ├─ name: shipping\n" + 1040 " ├─ outerVisibility: false\n" + 1041 " ├─ isLateral: false\n" + 1042 " ├─ cacheable: true\n" + 1043 " └─ Project\n" + 1044 " ├─ columns: [n1.n_name as supp_nation, n2.n_name as cust_nation, extract('YEAR' from lineitem.l_shipdate) as l_year, (lineitem.l_extendedprice * (1 - lineitem.l_discount)) as volume]\n" + 1045 " └─ Filter\n" + 1046 " ├─ (((n1.n_name = 'FRANCE') AND (n2.n_name = 'GERMANY')) OR ((n1.n_name = 'GERMANY') AND (n2.n_name = 'FRANCE')))\n" + 1047 " └─ LookupJoin\n" + 1048 " ├─ LookupJoin\n" + 1049 " │ ├─ LookupJoin\n" + 1050 " │ │ ├─ LookupJoin\n" + 1051 " │ │ │ ├─ MergeJoin\n" + 1052 " │ │ │ │ ├─ cmp: (lineitem.l_orderkey = orders.o_orderkey)\n" + 1053 " │ │ │ │ ├─ Filter\n" + 1054 " │ │ │ │ │ ├─ ((lineitem.l_shipdate >= '1995-01-01') AND (lineitem.l_shipdate <= '1996-12-31'))\n" + 1055 " │ │ │ │ │ └─ IndexedTableAccess(lineitem)\n" + 1056 " │ │ │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1057 " │ │ │ │ │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 1058 " │ │ │ │ │ └─ columns: [l_orderkey l_suppkey l_extendedprice l_discount l_shipdate]\n" + 1059 " │ │ │ │ └─ IndexedTableAccess(orders)\n" + 1060 " │ │ │ │ ├─ index: [orders.O_ORDERKEY]\n" + 1061 " │ │ │ │ ├─ filters: [{[NULL, ∞)}]\n" + 1062 " │ │ │ │ └─ columns: [o_orderkey o_custkey]\n" + 1063 " │ │ │ └─ IndexedTableAccess(supplier)\n" + 1064 " │ │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 1065 " │ │ │ ├─ columns: [s_suppkey s_nationkey]\n" + 1066 " │ │ │ └─ keys: lineitem.l_suppkey\n" + 1067 " │ │ └─ IndexedTableAccess(customer)\n" + 1068 " │ │ ├─ index: [customer.C_CUSTKEY]\n" + 1069 " │ │ ├─ columns: [c_custkey c_nationkey]\n" + 1070 " │ │ └─ keys: orders.o_custkey\n" + 1071 " │ └─ TableAlias(n1)\n" + 1072 " │ └─ IndexedTableAccess(nation)\n" + 1073 " │ ├─ index: [nation.N_NATIONKEY]\n" + 1074 " │ ├─ columns: [n_nationkey n_name]\n" + 1075 " │ └─ keys: supplier.s_nationkey\n" + 1076 " └─ TableAlias(n2)\n" + 1077 " └─ IndexedTableAccess(nation)\n" + 1078 " ├─ index: [nation.N_NATIONKEY]\n" + 1079 " ├─ columns: [n_nationkey n_name]\n" + 1080 " └─ keys: customer.c_nationkey\n" + 1081 "", 1082 ExpectedAnalysis: "Project\n" + 1083 " ├─ columns: [shipping.supp_nation, shipping.cust_nation, shipping.l_year, sum(shipping.volume) as revenue]\n" + 1084 " └─ Sort(shipping.supp_nation ASC, shipping.cust_nation ASC, shipping.l_year ASC)\n" + 1085 " └─ Project\n" + 1086 " ├─ columns: [sum(shipping.volume), shipping.supp_nation, shipping.cust_nation, shipping.l_year, sum(shipping.volume) as revenue]\n" + 1087 " └─ GroupBy\n" + 1088 " ├─ SelectedExprs(SUM(shipping.volume), shipping.supp_nation, shipping.cust_nation, shipping.l_year)\n" + 1089 " ├─ Grouping(shipping.supp_nation, shipping.cust_nation, shipping.l_year)\n" + 1090 " └─ SubqueryAlias\n" + 1091 " ├─ name: shipping\n" + 1092 " ├─ outerVisibility: false\n" + 1093 " ├─ isLateral: false\n" + 1094 " ├─ cacheable: true\n" + 1095 " └─ Project\n" + 1096 " ├─ columns: [n1.n_name as supp_nation, n2.n_name as cust_nation, extract('YEAR' from lineitem.l_shipdate) as l_year, (lineitem.l_extendedprice * (1 - lineitem.l_discount)) as volume]\n" + 1097 " └─ Filter\n" + 1098 " ├─ (((n1.n_name = 'FRANCE') AND (n2.n_name = 'GERMANY')) OR ((n1.n_name = 'GERMANY') AND (n2.n_name = 'FRANCE')))\n" + 1099 " └─ LookupJoin\n" + 1100 " ├─ LookupJoin\n" + 1101 " │ ├─ LookupJoin\n" + 1102 " │ │ ├─ LookupJoin\n" + 1103 " │ │ │ ├─ MergeJoin\n" + 1104 " │ │ │ │ ├─ cmp: (lineitem.l_orderkey = orders.o_orderkey)\n" + 1105 " │ │ │ │ ├─ Filter\n" + 1106 " │ │ │ │ │ ├─ ((lineitem.l_shipdate >= '1995-01-01') AND (lineitem.l_shipdate <= '1996-12-31'))\n" + 1107 " │ │ │ │ │ └─ IndexedTableAccess(lineitem)\n" + 1108 " │ │ │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1109 " │ │ │ │ │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 1110 " │ │ │ │ │ └─ columns: [l_orderkey l_suppkey l_extendedprice l_discount l_shipdate]\n" + 1111 " │ │ │ │ └─ IndexedTableAccess(orders)\n" + 1112 " │ │ │ │ ├─ index: [orders.O_ORDERKEY]\n" + 1113 " │ │ │ │ ├─ filters: [{[NULL, ∞)}]\n" + 1114 " │ │ │ │ └─ columns: [o_orderkey o_custkey]\n" + 1115 " │ │ │ └─ IndexedTableAccess(supplier)\n" + 1116 " │ │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 1117 " │ │ │ ├─ columns: [s_suppkey s_nationkey]\n" + 1118 " │ │ │ └─ keys: lineitem.l_suppkey\n" + 1119 " │ │ └─ IndexedTableAccess(customer)\n" + 1120 " │ │ ├─ index: [customer.C_CUSTKEY]\n" + 1121 " │ │ ├─ columns: [c_custkey c_nationkey]\n" + 1122 " │ │ └─ keys: orders.o_custkey\n" + 1123 " │ └─ TableAlias(n1)\n" + 1124 " │ └─ IndexedTableAccess(nation)\n" + 1125 " │ ├─ index: [nation.N_NATIONKEY]\n" + 1126 " │ ├─ columns: [n_nationkey n_name]\n" + 1127 " │ └─ keys: supplier.s_nationkey\n" + 1128 " └─ TableAlias(n2)\n" + 1129 " └─ IndexedTableAccess(nation)\n" + 1130 " ├─ index: [nation.N_NATIONKEY]\n" + 1131 " ├─ columns: [n_nationkey n_name]\n" + 1132 " └─ keys: customer.c_nationkey\n" + 1133 "", 1134 }, 1135 { 1136 Query: ` 1137 --Q8 1138 select 1139 o_year, 1140 sum(case 1141 when nation = 'BRAZIL' then volume 1142 else 0 1143 end) / sum(volume) as mkt_share 1144 from 1145 ( 1146 select 1147 extract(year from o_orderdate) as o_year, 1148 l_extendedprice * (1 - l_discount) as volume, 1149 n2.n_name as nation 1150 from 1151 part, 1152 supplier, 1153 lineitem, 1154 orders, 1155 customer, 1156 nation n1, 1157 nation n2, 1158 region 1159 where 1160 p_partkey = l_partkey 1161 and s_suppkey = l_suppkey 1162 and l_orderkey = o_orderkey 1163 and o_custkey = c_custkey 1164 and c_nationkey = n1.n_nationkey 1165 and n1.n_regionkey = r_regionkey 1166 and r_name = 'AMERICA' 1167 and s_nationkey = n2.n_nationkey 1168 and o_orderdate between '1995-01-01' and '1996-12-31' 1169 and p_type = 'ECONOMY ANODIZED STEEL' 1170 ) as all_nations 1171 group by 1172 o_year 1173 order by 1174 o_year;`, 1175 ExpectedPlan: "Project\n" + 1176 " ├─ columns: [all_nations.o_year:2!null, (sum(case when (all_nations.nation = 'brazil') then all_nations.volume else 0 end):0!null / sum(all_nations.volume):1!null) as mkt_share]\n" + 1177 " └─ Sort(all_nations.o_year:2!null ASC nullsFirst)\n" + 1178 " └─ Project\n" + 1179 " ├─ columns: [sum(case when (all_nations.nation = 'brazil') then all_nations.volume else 0 end):0!null, sum(all_nations.volume):1!null, all_nations.o_year:2!null, (sum(case when (all_nations.nation = 'brazil') then all_nations.volume else 0 end):0!null / sum(all_nations.volume):1!null) as mkt_share]\n" + 1180 " └─ GroupBy\n" + 1181 " ├─ select: SUM(CASE WHEN Eq\n" + 1182 " │ ├─ all_nations.nation:2!null\n" + 1183 " │ └─ BRAZIL (longtext)\n" + 1184 " │ THEN all_nations.volume:1!null ELSE 0 (tinyint) END), SUM(all_nations.volume:1!null), all_nations.o_year:0!null\n" + 1185 " ├─ group: all_nations.o_year:0!null\n" + 1186 " └─ SubqueryAlias\n" + 1187 " ├─ name: all_nations\n" + 1188 " ├─ outerVisibility: false\n" + 1189 " ├─ isLateral: false\n" + 1190 " ├─ cacheable: true\n" + 1191 " ├─ colSet: (64-66)\n" + 1192 " ├─ tableId: 9\n" + 1193 " └─ Project\n" + 1194 " ├─ columns: [extract('YEAR' from orders.o_orderdate) as o_year, (lineitem.l_extendedprice:12!null * (1 (tinyint) - lineitem.l_discount:13!null)) as volume, n2.n_name:19!null as nation]\n" + 1195 " └─ LookupJoin\n" + 1196 " ├─ HashJoin\n" + 1197 " │ ├─ Eq\n" + 1198 " │ │ ├─ part.p_partkey:16!null\n" + 1199 " │ │ └─ lineitem.l_partkey:10!null\n" + 1200 " │ ├─ LookupJoin\n" + 1201 " │ │ ├─ LookupJoin\n" + 1202 " │ │ │ ├─ LookupJoin\n" + 1203 " │ │ │ │ ├─ LookupJoin\n" + 1204 " │ │ │ │ │ ├─ LookupJoin\n" + 1205 " │ │ │ │ │ │ ├─ Filter\n" + 1206 " │ │ │ │ │ │ │ ├─ AND\n" + 1207 " │ │ │ │ │ │ │ │ ├─ GreaterThanOrEqual\n" + 1208 " │ │ │ │ │ │ │ │ │ ├─ orders.o_orderdate:2!null\n" + 1209 " │ │ │ │ │ │ │ │ │ └─ 1995-01-01 (longtext)\n" + 1210 " │ │ │ │ │ │ │ │ └─ LessThanOrEqual\n" + 1211 " │ │ │ │ │ │ │ │ ├─ orders.o_orderdate:2!null\n" + 1212 " │ │ │ │ │ │ │ │ └─ 1996-12-31 (longtext)\n" + 1213 " │ │ │ │ │ │ │ └─ Table\n" + 1214 " │ │ │ │ │ │ │ ├─ name: orders\n" + 1215 " │ │ │ │ │ │ │ ├─ columns: [o_orderkey o_custkey o_orderdate]\n" + 1216 " │ │ │ │ │ │ │ ├─ colSet: (33-41)\n" + 1217 " │ │ │ │ │ │ │ └─ tableId: 4\n" + 1218 " │ │ │ │ │ │ └─ IndexedTableAccess(customer)\n" + 1219 " │ │ │ │ │ │ ├─ index: [customer.C_CUSTKEY]\n" + 1220 " │ │ │ │ │ │ ├─ keys: [orders.o_custkey:1!null]\n" + 1221 " │ │ │ │ │ │ ├─ colSet: (42-49)\n" + 1222 " │ │ │ │ │ │ ├─ tableId: 5\n" + 1223 " │ │ │ │ │ │ └─ Table\n" + 1224 " │ │ │ │ │ │ ├─ name: customer\n" + 1225 " │ │ │ │ │ │ └─ columns: [c_custkey c_nationkey]\n" + 1226 " │ │ │ │ │ └─ TableAlias(n1)\n" + 1227 " │ │ │ │ │ └─ IndexedTableAccess(nation)\n" + 1228 " │ │ │ │ │ ├─ index: [nation.N_NATIONKEY]\n" + 1229 " │ │ │ │ │ ├─ keys: [customer.c_nationkey:4!null]\n" + 1230 " │ │ │ │ │ ├─ colSet: (50-53)\n" + 1231 " │ │ │ │ │ ├─ tableId: 6\n" + 1232 " │ │ │ │ │ └─ Table\n" + 1233 " │ │ │ │ │ ├─ name: nation\n" + 1234 " │ │ │ │ │ └─ columns: [n_nationkey n_regionkey]\n" + 1235 " │ │ │ │ └─ Filter\n" + 1236 " │ │ │ │ ├─ Eq\n" + 1237 " │ │ │ │ │ ├─ region.r_name:1!null\n" + 1238 " │ │ │ │ │ └─ AMERICA (longtext)\n" + 1239 " │ │ │ │ └─ IndexedTableAccess(region)\n" + 1240 " │ │ │ │ ├─ index: [region.R_REGIONKEY]\n" + 1241 " │ │ │ │ ├─ keys: [n1.n_regionkey:6!null]\n" + 1242 " │ │ │ │ ├─ colSet: (58-60)\n" + 1243 " │ │ │ │ ├─ tableId: 8\n" + 1244 " │ │ │ │ └─ Table\n" + 1245 " │ │ │ │ ├─ name: region\n" + 1246 " │ │ │ │ └─ columns: [r_regionkey r_name]\n" + 1247 " │ │ │ └─ IndexedTableAccess(lineitem)\n" + 1248 " │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1249 " │ │ │ ├─ keys: [orders.o_orderkey:0!null]\n" + 1250 " │ │ │ ├─ colSet: (17-32)\n" + 1251 " │ │ │ ├─ tableId: 3\n" + 1252 " │ │ │ └─ Table\n" + 1253 " │ │ │ ├─ name: lineitem\n" + 1254 " │ │ │ └─ columns: [l_orderkey l_partkey l_suppkey l_extendedprice l_discount]\n" + 1255 " │ │ └─ IndexedTableAccess(supplier)\n" + 1256 " │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 1257 " │ │ ├─ keys: [lineitem.l_suppkey:11!null]\n" + 1258 " │ │ ├─ colSet: (10-16)\n" + 1259 " │ │ ├─ tableId: 2\n" + 1260 " │ │ └─ Table\n" + 1261 " │ │ ├─ name: supplier\n" + 1262 " │ │ └─ columns: [s_suppkey s_nationkey]\n" + 1263 " │ └─ HashLookup\n" + 1264 " │ ├─ left-key: TUPLE(lineitem.l_partkey:10!null)\n" + 1265 " │ ├─ right-key: TUPLE(part.p_partkey:0!null)\n" + 1266 " │ └─ Filter\n" + 1267 " │ ├─ Eq\n" + 1268 " │ │ ├─ part.p_type:1!null\n" + 1269 " │ │ └─ ECONOMY ANODIZED STEEL (longtext)\n" + 1270 " │ └─ Table\n" + 1271 " │ ├─ name: part\n" + 1272 " │ ├─ columns: [p_partkey p_type]\n" + 1273 " │ ├─ colSet: (1-9)\n" + 1274 " │ └─ tableId: 1\n" + 1275 " └─ TableAlias(n2)\n" + 1276 " └─ IndexedTableAccess(nation)\n" + 1277 " ├─ index: [nation.N_NATIONKEY]\n" + 1278 " ├─ keys: [supplier.s_nationkey:15!null]\n" + 1279 " ├─ colSet: (54-57)\n" + 1280 " ├─ tableId: 7\n" + 1281 " └─ Table\n" + 1282 " ├─ name: nation\n" + 1283 " └─ columns: [n_nationkey n_name]\n" + 1284 "", 1285 ExpectedEstimates: "Project\n" + 1286 " ├─ columns: [all_nations.o_year, (sum(case when (all_nations.nation = 'brazil') then all_nations.volume else 0 end) / sum(all_nations.volume)) as mkt_share]\n" + 1287 " └─ Sort(all_nations.o_year ASC)\n" + 1288 " └─ Project\n" + 1289 " ├─ columns: [sum(case when (all_nations.nation = 'brazil') then all_nations.volume else 0 end), sum(all_nations.volume), all_nations.o_year, (sum(case when (all_nations.nation = 'brazil') then all_nations.volume else 0 end) / sum(all_nations.volume)) as mkt_share]\n" + 1290 " └─ GroupBy\n" + 1291 " ├─ SelectedExprs(SUM(CASE WHEN (all_nations.nation = 'BRAZIL') THEN all_nations.volume ELSE 0 END), SUM(all_nations.volume), all_nations.o_year)\n" + 1292 " ├─ Grouping(all_nations.o_year)\n" + 1293 " └─ SubqueryAlias\n" + 1294 " ├─ name: all_nations\n" + 1295 " ├─ outerVisibility: false\n" + 1296 " ├─ isLateral: false\n" + 1297 " ├─ cacheable: true\n" + 1298 " └─ Project\n" + 1299 " ├─ columns: [extract('YEAR' from orders.o_orderdate) as o_year, (lineitem.l_extendedprice * (1 - lineitem.l_discount)) as volume, n2.n_name as nation]\n" + 1300 " └─ LookupJoin\n" + 1301 " ├─ HashJoin\n" + 1302 " │ ├─ (part.p_partkey = lineitem.l_partkey)\n" + 1303 " │ ├─ LookupJoin\n" + 1304 " │ │ ├─ LookupJoin\n" + 1305 " │ │ │ ├─ LookupJoin\n" + 1306 " │ │ │ │ ├─ LookupJoin\n" + 1307 " │ │ │ │ │ ├─ LookupJoin\n" + 1308 " │ │ │ │ │ │ ├─ Filter\n" + 1309 " │ │ │ │ │ │ │ ├─ ((orders.o_orderdate >= '1995-01-01') AND (orders.o_orderdate <= '1996-12-31'))\n" + 1310 " │ │ │ │ │ │ │ └─ Table\n" + 1311 " │ │ │ │ │ │ │ ├─ name: orders\n" + 1312 " │ │ │ │ │ │ │ └─ columns: [o_orderkey o_custkey o_orderdate]\n" + 1313 " │ │ │ │ │ │ └─ IndexedTableAccess(customer)\n" + 1314 " │ │ │ │ │ │ ├─ index: [customer.C_CUSTKEY]\n" + 1315 " │ │ │ │ │ │ ├─ columns: [c_custkey c_nationkey]\n" + 1316 " │ │ │ │ │ │ └─ keys: orders.o_custkey\n" + 1317 " │ │ │ │ │ └─ TableAlias(n1)\n" + 1318 " │ │ │ │ │ └─ IndexedTableAccess(nation)\n" + 1319 " │ │ │ │ │ ├─ index: [nation.N_NATIONKEY]\n" + 1320 " │ │ │ │ │ ├─ columns: [n_nationkey n_regionkey]\n" + 1321 " │ │ │ │ │ └─ keys: customer.c_nationkey\n" + 1322 " │ │ │ │ └─ Filter\n" + 1323 " │ │ │ │ ├─ (region.r_name = 'AMERICA')\n" + 1324 " │ │ │ │ └─ IndexedTableAccess(region)\n" + 1325 " │ │ │ │ ├─ index: [region.R_REGIONKEY]\n" + 1326 " │ │ │ │ ├─ columns: [r_regionkey r_name]\n" + 1327 " │ │ │ │ └─ keys: n1.n_regionkey\n" + 1328 " │ │ │ └─ IndexedTableAccess(lineitem)\n" + 1329 " │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1330 " │ │ │ ├─ columns: [l_orderkey l_partkey l_suppkey l_extendedprice l_discount]\n" + 1331 " │ │ │ └─ keys: orders.o_orderkey\n" + 1332 " │ │ └─ IndexedTableAccess(supplier)\n" + 1333 " │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 1334 " │ │ ├─ columns: [s_suppkey s_nationkey]\n" + 1335 " │ │ └─ keys: lineitem.l_suppkey\n" + 1336 " │ └─ HashLookup\n" + 1337 " │ ├─ left-key: (lineitem.l_partkey)\n" + 1338 " │ ├─ right-key: (part.p_partkey)\n" + 1339 " │ └─ Filter\n" + 1340 " │ ├─ (part.p_type = 'ECONOMY ANODIZED STEEL')\n" + 1341 " │ └─ Table\n" + 1342 " │ ├─ name: part\n" + 1343 " │ └─ columns: [p_partkey p_type]\n" + 1344 " └─ TableAlias(n2)\n" + 1345 " └─ IndexedTableAccess(nation)\n" + 1346 " ├─ index: [nation.N_NATIONKEY]\n" + 1347 " ├─ columns: [n_nationkey n_name]\n" + 1348 " └─ keys: supplier.s_nationkey\n" + 1349 "", 1350 ExpectedAnalysis: "Project\n" + 1351 " ├─ columns: [all_nations.o_year, (sum(case when (all_nations.nation = 'brazil') then all_nations.volume else 0 end) / sum(all_nations.volume)) as mkt_share]\n" + 1352 " └─ Sort(all_nations.o_year ASC)\n" + 1353 " └─ Project\n" + 1354 " ├─ columns: [sum(case when (all_nations.nation = 'brazil') then all_nations.volume else 0 end), sum(all_nations.volume), all_nations.o_year, (sum(case when (all_nations.nation = 'brazil') then all_nations.volume else 0 end) / sum(all_nations.volume)) as mkt_share]\n" + 1355 " └─ GroupBy\n" + 1356 " ├─ SelectedExprs(SUM(CASE WHEN (all_nations.nation = 'BRAZIL') THEN all_nations.volume ELSE 0 END), SUM(all_nations.volume), all_nations.o_year)\n" + 1357 " ├─ Grouping(all_nations.o_year)\n" + 1358 " └─ SubqueryAlias\n" + 1359 " ├─ name: all_nations\n" + 1360 " ├─ outerVisibility: false\n" + 1361 " ├─ isLateral: false\n" + 1362 " ├─ cacheable: true\n" + 1363 " └─ Project\n" + 1364 " ├─ columns: [extract('YEAR' from orders.o_orderdate) as o_year, (lineitem.l_extendedprice * (1 - lineitem.l_discount)) as volume, n2.n_name as nation]\n" + 1365 " └─ LookupJoin\n" + 1366 " ├─ HashJoin\n" + 1367 " │ ├─ (part.p_partkey = lineitem.l_partkey)\n" + 1368 " │ ├─ LookupJoin\n" + 1369 " │ │ ├─ LookupJoin\n" + 1370 " │ │ │ ├─ LookupJoin\n" + 1371 " │ │ │ │ ├─ LookupJoin\n" + 1372 " │ │ │ │ │ ├─ LookupJoin\n" + 1373 " │ │ │ │ │ │ ├─ Filter\n" + 1374 " │ │ │ │ │ │ │ ├─ ((orders.o_orderdate >= '1995-01-01') AND (orders.o_orderdate <= '1996-12-31'))\n" + 1375 " │ │ │ │ │ │ │ └─ Table\n" + 1376 " │ │ │ │ │ │ │ ├─ name: orders\n" + 1377 " │ │ │ │ │ │ │ └─ columns: [o_orderkey o_custkey o_orderdate]\n" + 1378 " │ │ │ │ │ │ └─ IndexedTableAccess(customer)\n" + 1379 " │ │ │ │ │ │ ├─ index: [customer.C_CUSTKEY]\n" + 1380 " │ │ │ │ │ │ ├─ columns: [c_custkey c_nationkey]\n" + 1381 " │ │ │ │ │ │ └─ keys: orders.o_custkey\n" + 1382 " │ │ │ │ │ └─ TableAlias(n1)\n" + 1383 " │ │ │ │ │ └─ IndexedTableAccess(nation)\n" + 1384 " │ │ │ │ │ ├─ index: [nation.N_NATIONKEY]\n" + 1385 " │ │ │ │ │ ├─ columns: [n_nationkey n_regionkey]\n" + 1386 " │ │ │ │ │ └─ keys: customer.c_nationkey\n" + 1387 " │ │ │ │ └─ Filter\n" + 1388 " │ │ │ │ ├─ (region.r_name = 'AMERICA')\n" + 1389 " │ │ │ │ └─ IndexedTableAccess(region)\n" + 1390 " │ │ │ │ ├─ index: [region.R_REGIONKEY]\n" + 1391 " │ │ │ │ ├─ columns: [r_regionkey r_name]\n" + 1392 " │ │ │ │ └─ keys: n1.n_regionkey\n" + 1393 " │ │ │ └─ IndexedTableAccess(lineitem)\n" + 1394 " │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1395 " │ │ │ ├─ columns: [l_orderkey l_partkey l_suppkey l_extendedprice l_discount]\n" + 1396 " │ │ │ └─ keys: orders.o_orderkey\n" + 1397 " │ │ └─ IndexedTableAccess(supplier)\n" + 1398 " │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 1399 " │ │ ├─ columns: [s_suppkey s_nationkey]\n" + 1400 " │ │ └─ keys: lineitem.l_suppkey\n" + 1401 " │ └─ HashLookup\n" + 1402 " │ ├─ left-key: (lineitem.l_partkey)\n" + 1403 " │ ├─ right-key: (part.p_partkey)\n" + 1404 " │ └─ Filter\n" + 1405 " │ ├─ (part.p_type = 'ECONOMY ANODIZED STEEL')\n" + 1406 " │ └─ Table\n" + 1407 " │ ├─ name: part\n" + 1408 " │ └─ columns: [p_partkey p_type]\n" + 1409 " └─ TableAlias(n2)\n" + 1410 " └─ IndexedTableAccess(nation)\n" + 1411 " ├─ index: [nation.N_NATIONKEY]\n" + 1412 " ├─ columns: [n_nationkey n_name]\n" + 1413 " └─ keys: supplier.s_nationkey\n" + 1414 "", 1415 }, 1416 { 1417 Query: ` 1418 --Q9 1419 select 1420 nation, 1421 o_year, 1422 sum(amount) as sum_profit 1423 from 1424 ( 1425 select 1426 n_name as nation, 1427 extract(year from o_orderdate) as o_year, 1428 l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount 1429 from 1430 part, 1431 supplier, 1432 lineitem, 1433 partsupp, 1434 orders, 1435 nation 1436 where 1437 s_suppkey = l_suppkey 1438 and ps_suppkey = l_suppkey 1439 and ps_partkey = l_partkey 1440 and p_partkey = l_partkey 1441 and o_orderkey = l_orderkey 1442 and s_nationkey = n_nationkey 1443 and p_name like '%green%' 1444 ) as profit 1445 group by 1446 nation, 1447 o_year 1448 order by 1449 nation, 1450 o_year desc;`, 1451 ExpectedPlan: "Project\n" + 1452 " ├─ columns: [profit.nation:1!null, profit.o_year:2!null, sum(profit.amount):0!null as sum_profit]\n" + 1453 " └─ Sort(profit.nation:1!null ASC nullsFirst, profit.o_year:2!null DESC nullsFirst)\n" + 1454 " └─ Project\n" + 1455 " ├─ columns: [sum(profit.amount):0!null, profit.nation:1!null, profit.o_year:2!null, sum(profit.amount):0!null as sum_profit]\n" + 1456 " └─ GroupBy\n" + 1457 " ├─ select: SUM(profit.amount:2!null), profit.nation:0!null, profit.o_year:1!null\n" + 1458 " ├─ group: profit.nation:0!null, profit.o_year:1!null\n" + 1459 " └─ SubqueryAlias\n" + 1460 " ├─ name: profit\n" + 1461 " ├─ outerVisibility: false\n" + 1462 " ├─ isLateral: false\n" + 1463 " ├─ cacheable: true\n" + 1464 " ├─ colSet: (54-56)\n" + 1465 " ├─ tableId: 7\n" + 1466 " └─ Project\n" + 1467 " ├─ columns: [nation.n_name:16!null as nation, extract('YEAR' from orders.o_orderdate) as o_year, ((lineitem.l_extendedprice:4!null * (1 (tinyint) - lineitem.l_discount:5!null)) - (partsupp.ps_supplycost:12!null * lineitem.l_quantity:3!null)) as amount]\n" + 1468 " └─ LookupJoin\n" + 1469 " ├─ HashJoin\n" + 1470 " │ ├─ AND\n" + 1471 " │ │ ├─ AND\n" + 1472 " │ │ │ ├─ AND\n" + 1473 " │ │ │ │ ├─ Eq\n" + 1474 " │ │ │ │ │ ├─ supplier.s_suppkey:13!null\n" + 1475 " │ │ │ │ │ └─ lineitem.l_suppkey:2!null\n" + 1476 " │ │ │ │ └─ Eq\n" + 1477 " │ │ │ │ ├─ part.p_partkey:8!null\n" + 1478 " │ │ │ │ └─ lineitem.l_partkey:1!null\n" + 1479 " │ │ │ └─ Eq\n" + 1480 " │ │ │ ├─ partsupp.ps_suppkey:11!null\n" + 1481 " │ │ │ └─ lineitem.l_suppkey:2!null\n" + 1482 " │ │ └─ Eq\n" + 1483 " │ │ ├─ partsupp.ps_partkey:10!null\n" + 1484 " │ │ └─ lineitem.l_partkey:1!null\n" + 1485 " │ ├─ MergeJoin\n" + 1486 " │ │ ├─ cmp: Eq\n" + 1487 " │ │ │ ├─ lineitem.l_orderkey:0!null\n" + 1488 " │ │ │ └─ orders.o_orderkey:6!null\n" + 1489 " │ │ ├─ IndexedTableAccess(lineitem)\n" + 1490 " │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1491 " │ │ │ ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 1492 " │ │ │ ├─ colSet: (17-32)\n" + 1493 " │ │ │ ├─ tableId: 3\n" + 1494 " │ │ │ └─ Table\n" + 1495 " │ │ │ ├─ name: lineitem\n" + 1496 " │ │ │ └─ columns: [l_orderkey l_partkey l_suppkey l_quantity l_extendedprice l_discount]\n" + 1497 " │ │ └─ IndexedTableAccess(orders)\n" + 1498 " │ │ ├─ index: [orders.O_ORDERKEY]\n" + 1499 " │ │ ├─ static: [{[NULL, ∞)}]\n" + 1500 " │ │ ├─ colSet: (38-46)\n" + 1501 " │ │ ├─ tableId: 5\n" + 1502 " │ │ └─ Table\n" + 1503 " │ │ ├─ name: orders\n" + 1504 " │ │ └─ columns: [o_orderkey o_orderdate]\n" + 1505 " │ └─ HashLookup\n" + 1506 " │ ├─ left-key: TUPLE(lineitem.l_suppkey:2!null, lineitem.l_partkey:1!null, lineitem.l_suppkey:2!null, lineitem.l_partkey:1!null)\n" + 1507 " │ ├─ right-key: TUPLE(supplier.s_suppkey:5!null, part.p_partkey:0!null, partsupp.ps_suppkey:3!null, partsupp.ps_partkey:2!null)\n" + 1508 " │ └─ LookupJoin\n" + 1509 " │ ├─ MergeJoin\n" + 1510 " │ │ ├─ cmp: Eq\n" + 1511 " │ │ │ ├─ part.p_partkey:8!null\n" + 1512 " │ │ │ └─ partsupp.ps_partkey:10!null\n" + 1513 " │ │ ├─ Filter\n" + 1514 " │ │ │ ├─ part.p_name LIKE '%green%'\n" + 1515 " │ │ │ └─ IndexedTableAccess(part)\n" + 1516 " │ │ │ ├─ index: [part.P_PARTKEY]\n" + 1517 " │ │ │ ├─ static: [{[NULL, ∞)}]\n" + 1518 " │ │ │ ├─ colSet: (1-9)\n" + 1519 " │ │ │ ├─ tableId: 1\n" + 1520 " │ │ │ └─ Table\n" + 1521 " │ │ │ ├─ name: part\n" + 1522 " │ │ │ └─ columns: [p_partkey p_name]\n" + 1523 " │ │ └─ IndexedTableAccess(partsupp)\n" + 1524 " │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 1525 " │ │ ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 1526 " │ │ ├─ colSet: (33-37)\n" + 1527 " │ │ ├─ tableId: 4\n" + 1528 " │ │ └─ Table\n" + 1529 " │ │ ├─ name: partsupp\n" + 1530 " │ │ └─ columns: [ps_partkey ps_suppkey ps_supplycost]\n" + 1531 " │ └─ IndexedTableAccess(supplier)\n" + 1532 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 1533 " │ ├─ keys: [partsupp.ps_suppkey:11!null]\n" + 1534 " │ ├─ colSet: (10-16)\n" + 1535 " │ ├─ tableId: 2\n" + 1536 " │ └─ Table\n" + 1537 " │ ├─ name: supplier\n" + 1538 " │ └─ columns: [s_suppkey s_nationkey]\n" + 1539 " └─ IndexedTableAccess(nation)\n" + 1540 " ├─ index: [nation.N_NATIONKEY]\n" + 1541 " ├─ keys: [supplier.s_nationkey:14!null]\n" + 1542 " ├─ colSet: (47-50)\n" + 1543 " ├─ tableId: 6\n" + 1544 " └─ Table\n" + 1545 " ├─ name: nation\n" + 1546 " └─ columns: [n_nationkey n_name]\n" + 1547 "", 1548 ExpectedEstimates: "Project\n" + 1549 " ├─ columns: [profit.nation, profit.o_year, sum(profit.amount) as sum_profit]\n" + 1550 " └─ Sort(profit.nation ASC, profit.o_year DESC)\n" + 1551 " └─ Project\n" + 1552 " ├─ columns: [sum(profit.amount), profit.nation, profit.o_year, sum(profit.amount) as sum_profit]\n" + 1553 " └─ GroupBy\n" + 1554 " ├─ SelectedExprs(SUM(profit.amount), profit.nation, profit.o_year)\n" + 1555 " ├─ Grouping(profit.nation, profit.o_year)\n" + 1556 " └─ SubqueryAlias\n" + 1557 " ├─ name: profit\n" + 1558 " ├─ outerVisibility: false\n" + 1559 " ├─ isLateral: false\n" + 1560 " ├─ cacheable: true\n" + 1561 " └─ Project\n" + 1562 " ├─ columns: [nation.n_name as nation, extract('YEAR' from orders.o_orderdate) as o_year, ((lineitem.l_extendedprice * (1 - lineitem.l_discount)) - (partsupp.ps_supplycost * lineitem.l_quantity)) as amount]\n" + 1563 " └─ LookupJoin\n" + 1564 " ├─ HashJoin\n" + 1565 " │ ├─ ((((supplier.s_suppkey = lineitem.l_suppkey) AND (part.p_partkey = lineitem.l_partkey)) AND (partsupp.ps_suppkey = lineitem.l_suppkey)) AND (partsupp.ps_partkey = lineitem.l_partkey))\n" + 1566 " │ ├─ MergeJoin\n" + 1567 " │ │ ├─ cmp: (lineitem.l_orderkey = orders.o_orderkey)\n" + 1568 " │ │ ├─ IndexedTableAccess(lineitem)\n" + 1569 " │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1570 " │ │ │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 1571 " │ │ │ └─ columns: [l_orderkey l_partkey l_suppkey l_quantity l_extendedprice l_discount]\n" + 1572 " │ │ └─ IndexedTableAccess(orders)\n" + 1573 " │ │ ├─ index: [orders.O_ORDERKEY]\n" + 1574 " │ │ ├─ filters: [{[NULL, ∞)}]\n" + 1575 " │ │ └─ columns: [o_orderkey o_orderdate]\n" + 1576 " │ └─ HashLookup\n" + 1577 " │ ├─ left-key: (lineitem.l_suppkey, lineitem.l_partkey, lineitem.l_suppkey, lineitem.l_partkey)\n" + 1578 " │ ├─ right-key: (supplier.s_suppkey, part.p_partkey, partsupp.ps_suppkey, partsupp.ps_partkey)\n" + 1579 " │ └─ LookupJoin\n" + 1580 " │ ├─ MergeJoin\n" + 1581 " │ │ ├─ cmp: (part.p_partkey = partsupp.ps_partkey)\n" + 1582 " │ │ ├─ Filter\n" + 1583 " │ │ │ ├─ part.p_name LIKE '%green%'\n" + 1584 " │ │ │ └─ IndexedTableAccess(part)\n" + 1585 " │ │ │ ├─ index: [part.P_PARTKEY]\n" + 1586 " │ │ │ ├─ filters: [{[NULL, ∞)}]\n" + 1587 " │ │ │ └─ columns: [p_partkey p_name]\n" + 1588 " │ │ └─ IndexedTableAccess(partsupp)\n" + 1589 " │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 1590 " │ │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 1591 " │ │ └─ columns: [ps_partkey ps_suppkey ps_supplycost]\n" + 1592 " │ └─ IndexedTableAccess(supplier)\n" + 1593 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 1594 " │ ├─ columns: [s_suppkey s_nationkey]\n" + 1595 " │ └─ keys: partsupp.ps_suppkey\n" + 1596 " └─ IndexedTableAccess(nation)\n" + 1597 " ├─ index: [nation.N_NATIONKEY]\n" + 1598 " ├─ columns: [n_nationkey n_name]\n" + 1599 " └─ keys: supplier.s_nationkey\n" + 1600 "", 1601 ExpectedAnalysis: "Project\n" + 1602 " ├─ columns: [profit.nation, profit.o_year, sum(profit.amount) as sum_profit]\n" + 1603 " └─ Sort(profit.nation ASC, profit.o_year DESC)\n" + 1604 " └─ Project\n" + 1605 " ├─ columns: [sum(profit.amount), profit.nation, profit.o_year, sum(profit.amount) as sum_profit]\n" + 1606 " └─ GroupBy\n" + 1607 " ├─ SelectedExprs(SUM(profit.amount), profit.nation, profit.o_year)\n" + 1608 " ├─ Grouping(profit.nation, profit.o_year)\n" + 1609 " └─ SubqueryAlias\n" + 1610 " ├─ name: profit\n" + 1611 " ├─ outerVisibility: false\n" + 1612 " ├─ isLateral: false\n" + 1613 " ├─ cacheable: true\n" + 1614 " └─ Project\n" + 1615 " ├─ columns: [nation.n_name as nation, extract('YEAR' from orders.o_orderdate) as o_year, ((lineitem.l_extendedprice * (1 - lineitem.l_discount)) - (partsupp.ps_supplycost * lineitem.l_quantity)) as amount]\n" + 1616 " └─ LookupJoin\n" + 1617 " ├─ HashJoin\n" + 1618 " │ ├─ ((((supplier.s_suppkey = lineitem.l_suppkey) AND (part.p_partkey = lineitem.l_partkey)) AND (partsupp.ps_suppkey = lineitem.l_suppkey)) AND (partsupp.ps_partkey = lineitem.l_partkey))\n" + 1619 " │ ├─ MergeJoin\n" + 1620 " │ │ ├─ cmp: (lineitem.l_orderkey = orders.o_orderkey)\n" + 1621 " │ │ ├─ IndexedTableAccess(lineitem)\n" + 1622 " │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1623 " │ │ │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 1624 " │ │ │ └─ columns: [l_orderkey l_partkey l_suppkey l_quantity l_extendedprice l_discount]\n" + 1625 " │ │ └─ IndexedTableAccess(orders)\n" + 1626 " │ │ ├─ index: [orders.O_ORDERKEY]\n" + 1627 " │ │ ├─ filters: [{[NULL, ∞)}]\n" + 1628 " │ │ └─ columns: [o_orderkey o_orderdate]\n" + 1629 " │ └─ HashLookup\n" + 1630 " │ ├─ left-key: (lineitem.l_suppkey, lineitem.l_partkey, lineitem.l_suppkey, lineitem.l_partkey)\n" + 1631 " │ ├─ right-key: (supplier.s_suppkey, part.p_partkey, partsupp.ps_suppkey, partsupp.ps_partkey)\n" + 1632 " │ └─ LookupJoin\n" + 1633 " │ ├─ MergeJoin\n" + 1634 " │ │ ├─ cmp: (part.p_partkey = partsupp.ps_partkey)\n" + 1635 " │ │ ├─ Filter\n" + 1636 " │ │ │ ├─ part.p_name LIKE '%green%'\n" + 1637 " │ │ │ └─ IndexedTableAccess(part)\n" + 1638 " │ │ │ ├─ index: [part.P_PARTKEY]\n" + 1639 " │ │ │ ├─ filters: [{[NULL, ∞)}]\n" + 1640 " │ │ │ └─ columns: [p_partkey p_name]\n" + 1641 " │ │ └─ IndexedTableAccess(partsupp)\n" + 1642 " │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 1643 " │ │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 1644 " │ │ └─ columns: [ps_partkey ps_suppkey ps_supplycost]\n" + 1645 " │ └─ IndexedTableAccess(supplier)\n" + 1646 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 1647 " │ ├─ columns: [s_suppkey s_nationkey]\n" + 1648 " │ └─ keys: partsupp.ps_suppkey\n" + 1649 " └─ IndexedTableAccess(nation)\n" + 1650 " ├─ index: [nation.N_NATIONKEY]\n" + 1651 " ├─ columns: [n_nationkey n_name]\n" + 1652 " └─ keys: supplier.s_nationkey\n" + 1653 "", 1654 }, 1655 { 1656 Query: ` 1657 --Q10 1658 select 1659 c_custkey, 1660 c_name, 1661 sum(l_extendedprice * (1 - l_discount)) as revenue, 1662 c_acctbal, 1663 n_name, 1664 c_address, 1665 c_phone, 1666 c_comment 1667 from 1668 customer, 1669 orders, 1670 lineitem, 1671 nation 1672 where 1673 c_custkey = o_custkey 1674 and l_orderkey = o_orderkey 1675 and o_orderdate >= '1993-10-01' 1676 and o_orderdate < '1993-10-01' + interval '3' month 1677 and l_returnflag = 'R' 1678 and c_nationkey = n_nationkey 1679 group by 1680 c_custkey, 1681 c_name, 1682 c_acctbal, 1683 c_phone, 1684 n_name, 1685 c_address, 1686 c_comment 1687 order by 1688 revenue desc;`, 1689 ExpectedPlan: "Project\n" + 1690 " ├─ columns: [customer.c_custkey:1!null, customer.c_name:2!null, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue, customer.c_acctbal:3!null, nation.n_name:4!null, customer.c_address:5!null, customer.c_phone:6!null, customer.c_comment:7!null]\n" + 1691 " └─ Sort(sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue DESC nullsFirst)\n" + 1692 " └─ Project\n" + 1693 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null, customer.c_custkey:1!null, customer.c_name:2!null, customer.c_acctbal:3!null, nation.n_name:4!null, customer.c_address:5!null, customer.c_phone:6!null, customer.c_comment:7!null, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue]\n" + 1694 " └─ GroupBy\n" + 1695 " ├─ select: SUM((lineitem.l_extendedprice:4!null * (1 (tinyint) - lineitem.l_discount:5!null))), customer.c_custkey:7!null, customer.c_name:8!null, customer.c_acctbal:12!null, nation.n_name:15!null, customer.c_address:9!null, customer.c_phone:11!null, customer.c_comment:13!null\n" + 1696 " ├─ group: customer.c_custkey:7!null, customer.c_name:8!null, customer.c_acctbal:12!null, customer.c_phone:11!null, nation.n_name:15!null, customer.c_address:9!null, customer.c_comment:13!null\n" + 1697 " └─ LookupJoin\n" + 1698 " ├─ LookupJoin\n" + 1699 " │ ├─ MergeJoin\n" + 1700 " │ │ ├─ cmp: Eq\n" + 1701 " │ │ │ ├─ orders.o_orderkey:0!null\n" + 1702 " │ │ │ └─ lineitem.l_orderkey:3!null\n" + 1703 " │ │ ├─ Filter\n" + 1704 " │ │ │ ├─ AND\n" + 1705 " │ │ │ │ ├─ GreaterThanOrEqual\n" + 1706 " │ │ │ │ │ ├─ orders.o_orderdate:2!null\n" + 1707 " │ │ │ │ │ └─ 1993-10-01 (longtext)\n" + 1708 " │ │ │ │ └─ LessThan\n" + 1709 " │ │ │ │ ├─ orders.o_orderdate:2!null\n" + 1710 " │ │ │ │ └─ 1994-01-01 00:00:00 +0000 UTC (datetime)\n" + 1711 " │ │ │ └─ IndexedTableAccess(orders)\n" + 1712 " │ │ │ ├─ index: [orders.O_ORDERKEY]\n" + 1713 " │ │ │ ├─ static: [{[NULL, ∞)}]\n" + 1714 " │ │ │ ├─ colSet: (9-17)\n" + 1715 " │ │ │ ├─ tableId: 2\n" + 1716 " │ │ │ └─ Table\n" + 1717 " │ │ │ ├─ name: orders\n" + 1718 " │ │ │ └─ columns: [o_orderkey o_custkey o_orderdate]\n" + 1719 " │ │ └─ Filter\n" + 1720 " │ │ ├─ Eq\n" + 1721 " │ │ │ ├─ lineitem.l_returnflag:3!null\n" + 1722 " │ │ │ └─ R (longtext)\n" + 1723 " │ │ └─ IndexedTableAccess(lineitem)\n" + 1724 " │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1725 " │ │ ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 1726 " │ │ ├─ colSet: (18-33)\n" + 1727 " │ │ ├─ tableId: 3\n" + 1728 " │ │ └─ Table\n" + 1729 " │ │ ├─ name: lineitem\n" + 1730 " │ │ └─ columns: [l_orderkey l_extendedprice l_discount l_returnflag]\n" + 1731 " │ └─ IndexedTableAccess(customer)\n" + 1732 " │ ├─ index: [customer.C_CUSTKEY]\n" + 1733 " │ ├─ keys: [orders.o_custkey:1!null]\n" + 1734 " │ ├─ colSet: (1-8)\n" + 1735 " │ ├─ tableId: 1\n" + 1736 " │ └─ Table\n" + 1737 " │ ├─ name: customer\n" + 1738 " │ └─ columns: [c_custkey c_name c_address c_nationkey c_phone c_acctbal c_comment]\n" + 1739 " └─ IndexedTableAccess(nation)\n" + 1740 " ├─ index: [nation.N_NATIONKEY]\n" + 1741 " ├─ keys: [customer.c_nationkey:10!null]\n" + 1742 " ├─ colSet: (34-37)\n" + 1743 " ├─ tableId: 4\n" + 1744 " └─ Table\n" + 1745 " ├─ name: nation\n" + 1746 " └─ columns: [n_nationkey n_name]\n" + 1747 "", 1748 ExpectedEstimates: "Project\n" + 1749 " ├─ columns: [customer.c_custkey, customer.c_name, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue, customer.c_acctbal, nation.n_name, customer.c_address, customer.c_phone, customer.c_comment]\n" + 1750 " └─ Sort(sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue DESC)\n" + 1751 " └─ Project\n" + 1752 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))), customer.c_custkey, customer.c_name, customer.c_acctbal, nation.n_name, customer.c_address, customer.c_phone, customer.c_comment, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 1753 " └─ GroupBy\n" + 1754 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), customer.c_custkey, customer.c_name, customer.c_acctbal, nation.n_name, customer.c_address, customer.c_phone, customer.c_comment)\n" + 1755 " ├─ Grouping(customer.c_custkey, customer.c_name, customer.c_acctbal, customer.c_phone, nation.n_name, customer.c_address, customer.c_comment)\n" + 1756 " └─ LookupJoin\n" + 1757 " ├─ LookupJoin\n" + 1758 " │ ├─ MergeJoin\n" + 1759 " │ │ ├─ cmp: (orders.o_orderkey = lineitem.l_orderkey)\n" + 1760 " │ │ ├─ Filter\n" + 1761 " │ │ │ ├─ ((orders.o_orderdate >= '1993-10-01') AND (orders.o_orderdate < 1994-01-01 00:00:00 +0000 UTC))\n" + 1762 " │ │ │ └─ IndexedTableAccess(orders)\n" + 1763 " │ │ │ ├─ index: [orders.O_ORDERKEY]\n" + 1764 " │ │ │ ├─ filters: [{[NULL, ∞)}]\n" + 1765 " │ │ │ └─ columns: [o_orderkey o_custkey o_orderdate]\n" + 1766 " │ │ └─ Filter\n" + 1767 " │ │ ├─ (lineitem.l_returnflag = 'R')\n" + 1768 " │ │ └─ IndexedTableAccess(lineitem)\n" + 1769 " │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1770 " │ │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 1771 " │ │ └─ columns: [l_orderkey l_extendedprice l_discount l_returnflag]\n" + 1772 " │ └─ IndexedTableAccess(customer)\n" + 1773 " │ ├─ index: [customer.C_CUSTKEY]\n" + 1774 " │ ├─ columns: [c_custkey c_name c_address c_nationkey c_phone c_acctbal c_comment]\n" + 1775 " │ └─ keys: orders.o_custkey\n" + 1776 " └─ IndexedTableAccess(nation)\n" + 1777 " ├─ index: [nation.N_NATIONKEY]\n" + 1778 " ├─ columns: [n_nationkey n_name]\n" + 1779 " └─ keys: customer.c_nationkey\n" + 1780 "", 1781 ExpectedAnalysis: "Project\n" + 1782 " ├─ columns: [customer.c_custkey, customer.c_name, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue, customer.c_acctbal, nation.n_name, customer.c_address, customer.c_phone, customer.c_comment]\n" + 1783 " └─ Sort(sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue DESC)\n" + 1784 " └─ Project\n" + 1785 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))), customer.c_custkey, customer.c_name, customer.c_acctbal, nation.n_name, customer.c_address, customer.c_phone, customer.c_comment, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 1786 " └─ GroupBy\n" + 1787 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), customer.c_custkey, customer.c_name, customer.c_acctbal, nation.n_name, customer.c_address, customer.c_phone, customer.c_comment)\n" + 1788 " ├─ Grouping(customer.c_custkey, customer.c_name, customer.c_acctbal, customer.c_phone, nation.n_name, customer.c_address, customer.c_comment)\n" + 1789 " └─ LookupJoin\n" + 1790 " ├─ LookupJoin\n" + 1791 " │ ├─ MergeJoin\n" + 1792 " │ │ ├─ cmp: (orders.o_orderkey = lineitem.l_orderkey)\n" + 1793 " │ │ ├─ Filter\n" + 1794 " │ │ │ ├─ ((orders.o_orderdate >= '1993-10-01') AND (orders.o_orderdate < 1994-01-01 00:00:00 +0000 UTC))\n" + 1795 " │ │ │ └─ IndexedTableAccess(orders)\n" + 1796 " │ │ │ ├─ index: [orders.O_ORDERKEY]\n" + 1797 " │ │ │ ├─ filters: [{[NULL, ∞)}]\n" + 1798 " │ │ │ └─ columns: [o_orderkey o_custkey o_orderdate]\n" + 1799 " │ │ └─ Filter\n" + 1800 " │ │ ├─ (lineitem.l_returnflag = 'R')\n" + 1801 " │ │ └─ IndexedTableAccess(lineitem)\n" + 1802 " │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 1803 " │ │ ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 1804 " │ │ └─ columns: [l_orderkey l_extendedprice l_discount l_returnflag]\n" + 1805 " │ └─ IndexedTableAccess(customer)\n" + 1806 " │ ├─ index: [customer.C_CUSTKEY]\n" + 1807 " │ ├─ columns: [c_custkey c_name c_address c_nationkey c_phone c_acctbal c_comment]\n" + 1808 " │ └─ keys: orders.o_custkey\n" + 1809 " └─ IndexedTableAccess(nation)\n" + 1810 " ├─ index: [nation.N_NATIONKEY]\n" + 1811 " ├─ columns: [n_nationkey n_name]\n" + 1812 " └─ keys: customer.c_nationkey\n" + 1813 "", 1814 }, 1815 { 1816 Query: ` 1817 --Q11 1818 select 1819 ps_partkey, 1820 sum(ps_supplycost * ps_availqty) as value 1821 from 1822 partsupp, 1823 supplier, 1824 nation 1825 where 1826 ps_suppkey = s_suppkey 1827 and s_nationkey = n_nationkey 1828 and n_name = 'GERMANY' 1829 group by 1830 ps_partkey having 1831 sum(ps_supplycost * ps_availqty) > ( 1832 select 1833 sum(ps_supplycost * ps_availqty) * 0.0001000000 1834 from 1835 partsupp, 1836 supplier, 1837 nation 1838 where 1839 ps_suppkey = s_suppkey 1840 and s_nationkey = n_nationkey 1841 and n_name = 'GERMANY' 1842 ) 1843 order by 1844 value desc;`, 1845 ExpectedPlan: "Project\n" + 1846 " ├─ columns: [partsupp.ps_partkey:1!null, sum((partsupp.ps_supplycost * partsupp.ps_availqty)):0!null as value]\n" + 1847 " └─ Sort(sum((partsupp.ps_supplycost * partsupp.ps_availqty)):0!null as value DESC nullsFirst)\n" + 1848 " └─ Having\n" + 1849 " ├─ GreaterThan\n" + 1850 " │ ├─ sum((partsupp.ps_supplycost * partsupp.ps_availqty)):0!null\n" + 1851 " │ └─ Subquery\n" + 1852 " │ ├─ cacheable: true\n" + 1853 " │ ├─ alias-string: select sum(ps_supplycost * ps_availqty) * 0.0001000000 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'GERMANY'\n" + 1854 " │ └─ Project\n" + 1855 " │ ├─ columns: [(sum((partsupp.ps_supplycost * partsupp.ps_availqty)):0!null * 0.0001 (decimal(11,10))) as sum(ps_supplycost * ps_availqty) * 0.0001000000]\n" + 1856 " │ └─ HashJoin\n" + 1857 " │ ├─ Eq\n" + 1858 " │ │ ├─ supplier.s_nationkey:7!null\n" + 1859 " │ │ └─ nation.n_nationkey:8!null\n" + 1860 " │ ├─ LookupJoin\n" + 1861 " │ │ ├─ Table\n" + 1862 " │ │ │ ├─ name: partsupp\n" + 1863 " │ │ │ ├─ columns: [ps_suppkey]\n" + 1864 " │ │ │ ├─ colSet: (19-23)\n" + 1865 " │ │ │ └─ tableId: 4\n" + 1866 " │ │ └─ IndexedTableAccess(supplier)\n" + 1867 " │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 1868 " │ │ ├─ keys: [partsupp.ps_suppkey:5!null]\n" + 1869 " │ │ ├─ colSet: (24-30)\n" + 1870 " │ │ ├─ tableId: 5\n" + 1871 " │ │ └─ Table\n" + 1872 " │ │ ├─ name: supplier\n" + 1873 " │ │ └─ columns: [s_suppkey s_nationkey]\n" + 1874 " │ └─ HashLookup\n" + 1875 " │ ├─ left-key: TUPLE(supplier.s_nationkey:7!null)\n" + 1876 " │ ├─ right-key: TUPLE(nation.n_nationkey:5!null)\n" + 1877 " │ └─ Filter\n" + 1878 " │ ├─ Eq\n" + 1879 " │ │ ├─ nation.n_name:6!null\n" + 1880 " │ │ └─ GERMANY (longtext)\n" + 1881 " │ └─ Table\n" + 1882 " │ ├─ name: nation\n" + 1883 " │ ├─ columns: [n_nationkey n_name]\n" + 1884 " │ ├─ colSet: (31-34)\n" + 1885 " │ └─ tableId: 6\n" + 1886 " └─ Project\n" + 1887 " ├─ columns: [sum((partsupp.ps_supplycost * partsupp.ps_availqty)):0!null, partsupp.ps_partkey:1!null, partsupp.PS_SUPPLYCOST:2!null, partsupp.PS_AVAILQTY:3!null, sum((partsupp.ps_supplycost * partsupp.ps_availqty)):0!null as value]\n" + 1888 " └─ GroupBy\n" + 1889 " ├─ select: SUM((partsupp.ps_supplycost:3!null * partsupp.ps_availqty:2!null)), partsupp.ps_partkey:0!null, partsupp.PS_SUPPLYCOST:3!null, partsupp.PS_AVAILQTY:2!null\n" + 1890 " ├─ group: partsupp.ps_partkey:0!null\n" + 1891 " └─ HashJoin\n" + 1892 " ├─ Eq\n" + 1893 " │ ├─ supplier.s_nationkey:8!null\n" + 1894 " │ └─ nation.n_nationkey:12!null\n" + 1895 " ├─ LookupJoin\n" + 1896 " │ ├─ ProcessTable\n" + 1897 " │ │ └─ Table\n" + 1898 " │ │ ├─ name: partsupp\n" + 1899 " │ │ └─ columns: [ps_partkey ps_suppkey ps_availqty ps_supplycost ps_comment]\n" + 1900 " │ └─ IndexedTableAccess(supplier)\n" + 1901 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 1902 " │ ├─ keys: [partsupp.ps_suppkey:1!null]\n" + 1903 " │ ├─ colSet: (6-12)\n" + 1904 " │ ├─ tableId: 2\n" + 1905 " │ └─ Table\n" + 1906 " │ ├─ name: supplier\n" + 1907 " │ └─ columns: [s_suppkey s_name s_address s_nationkey s_phone s_acctbal s_comment]\n" + 1908 " └─ HashLookup\n" + 1909 " ├─ left-key: TUPLE(supplier.s_nationkey:8!null)\n" + 1910 " ├─ right-key: TUPLE(nation.n_nationkey:0!null)\n" + 1911 " └─ Filter\n" + 1912 " ├─ Eq\n" + 1913 " │ ├─ nation.n_name:1!null\n" + 1914 " │ └─ GERMANY (longtext)\n" + 1915 " └─ ProcessTable\n" + 1916 " └─ Table\n" + 1917 " ├─ name: nation\n" + 1918 " └─ columns: [n_nationkey n_name n_regionkey n_comment]\n" + 1919 "", 1920 ExpectedEstimates: "Project\n" + 1921 " ├─ columns: [partsupp.ps_partkey, sum((partsupp.ps_supplycost * partsupp.ps_availqty)) as value]\n" + 1922 " └─ Sort(sum((partsupp.ps_supplycost * partsupp.ps_availqty)) as value DESC)\n" + 1923 " └─ Having((sum((partsupp.ps_supplycost * partsupp.ps_availqty)) > Subquery\n" + 1924 " ├─ cacheable: true\n" + 1925 " └─ Project\n" + 1926 " ├─ columns: [(sum((partsupp.ps_supplycost * partsupp.ps_availqty)) * 0.0001000000) as sum(ps_supplycost * ps_availqty) * 0.0001000000]\n" + 1927 " └─ HashJoin\n" + 1928 " ├─ (supplier.s_nationkey = nation.n_nationkey)\n" + 1929 " ├─ LookupJoin\n" + 1930 " │ ├─ Table\n" + 1931 " │ │ ├─ name: partsupp\n" + 1932 " │ │ └─ columns: [ps_suppkey]\n" + 1933 " │ └─ IndexedTableAccess(supplier)\n" + 1934 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 1935 " │ ├─ columns: [s_suppkey s_nationkey]\n" + 1936 " │ └─ keys: partsupp.ps_suppkey\n" + 1937 " └─ HashLookup\n" + 1938 " ├─ left-key: (supplier.s_nationkey)\n" + 1939 " ├─ right-key: (nation.n_nationkey)\n" + 1940 " └─ Filter\n" + 1941 " ├─ (nation.n_name = 'GERMANY')\n" + 1942 " └─ Table\n" + 1943 " ├─ name: nation\n" + 1944 " └─ columns: [n_nationkey n_name]\n" + 1945 " ))\n" + 1946 " └─ Project\n" + 1947 " ├─ columns: [sum((partsupp.ps_supplycost * partsupp.ps_availqty)), partsupp.ps_partkey, partsupp.PS_SUPPLYCOST, partsupp.PS_AVAILQTY, sum((partsupp.ps_supplycost * partsupp.ps_availqty)) as value]\n" + 1948 " └─ GroupBy\n" + 1949 " ├─ SelectedExprs(SUM((partsupp.ps_supplycost * partsupp.ps_availqty)), partsupp.ps_partkey, partsupp.PS_SUPPLYCOST, partsupp.PS_AVAILQTY)\n" + 1950 " ├─ Grouping(partsupp.ps_partkey)\n" + 1951 " └─ HashJoin\n" + 1952 " ├─ (supplier.s_nationkey = nation.n_nationkey)\n" + 1953 " ├─ LookupJoin\n" + 1954 " │ ├─ Table\n" + 1955 " │ │ └─ name: partsupp\n" + 1956 " │ └─ IndexedTableAccess(supplier)\n" + 1957 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 1958 " │ └─ keys: partsupp.ps_suppkey\n" + 1959 " └─ HashLookup\n" + 1960 " ├─ left-key: (supplier.s_nationkey)\n" + 1961 " ├─ right-key: (nation.n_nationkey)\n" + 1962 " └─ Filter\n" + 1963 " ├─ (nation.n_name = 'GERMANY')\n" + 1964 " └─ Table\n" + 1965 " └─ name: nation\n" + 1966 "", 1967 ExpectedAnalysis: "Project\n" + 1968 " ├─ columns: [partsupp.ps_partkey, sum((partsupp.ps_supplycost * partsupp.ps_availqty)) as value]\n" + 1969 " └─ Sort(sum((partsupp.ps_supplycost * partsupp.ps_availqty)) as value DESC)\n" + 1970 " └─ Having((sum((partsupp.ps_supplycost * partsupp.ps_availqty)) > Subquery\n" + 1971 " ├─ cacheable: true\n" + 1972 " └─ Project\n" + 1973 " ├─ columns: [(sum((partsupp.ps_supplycost * partsupp.ps_availqty)) * 0.0001000000) as sum(ps_supplycost * ps_availqty) * 0.0001000000]\n" + 1974 " └─ HashJoin\n" + 1975 " ├─ (supplier.s_nationkey = nation.n_nationkey)\n" + 1976 " ├─ LookupJoin\n" + 1977 " │ ├─ Table\n" + 1978 " │ │ ├─ name: partsupp\n" + 1979 " │ │ └─ columns: [ps_suppkey]\n" + 1980 " │ └─ IndexedTableAccess(supplier)\n" + 1981 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 1982 " │ ├─ columns: [s_suppkey s_nationkey]\n" + 1983 " │ └─ keys: partsupp.ps_suppkey\n" + 1984 " └─ HashLookup\n" + 1985 " ├─ left-key: (supplier.s_nationkey)\n" + 1986 " ├─ right-key: (nation.n_nationkey)\n" + 1987 " └─ Filter\n" + 1988 " ├─ (nation.n_name = 'GERMANY')\n" + 1989 " └─ Table\n" + 1990 " ├─ name: nation\n" + 1991 " └─ columns: [n_nationkey n_name]\n" + 1992 " ))\n" + 1993 " └─ Project\n" + 1994 " ├─ columns: [sum((partsupp.ps_supplycost * partsupp.ps_availqty)), partsupp.ps_partkey, partsupp.PS_SUPPLYCOST, partsupp.PS_AVAILQTY, sum((partsupp.ps_supplycost * partsupp.ps_availqty)) as value]\n" + 1995 " └─ GroupBy\n" + 1996 " ├─ SelectedExprs(SUM((partsupp.ps_supplycost * partsupp.ps_availqty)), partsupp.ps_partkey, partsupp.PS_SUPPLYCOST, partsupp.PS_AVAILQTY)\n" + 1997 " ├─ Grouping(partsupp.ps_partkey)\n" + 1998 " └─ HashJoin\n" + 1999 " ├─ (supplier.s_nationkey = nation.n_nationkey)\n" + 2000 " ├─ LookupJoin\n" + 2001 " │ ├─ Table\n" + 2002 " │ │ └─ name: partsupp\n" + 2003 " │ └─ IndexedTableAccess(supplier)\n" + 2004 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 2005 " │ └─ keys: partsupp.ps_suppkey\n" + 2006 " └─ HashLookup\n" + 2007 " ├─ left-key: (supplier.s_nationkey)\n" + 2008 " ├─ right-key: (nation.n_nationkey)\n" + 2009 " └─ Filter\n" + 2010 " ├─ (nation.n_name = 'GERMANY')\n" + 2011 " └─ Table\n" + 2012 " └─ name: nation\n" + 2013 "", 2014 }, 2015 { 2016 Query: ` 2017 --Q12 2018 select 2019 l_shipmode, 2020 sum(case 2021 when o_orderpriority = '1-URGENT' 2022 or o_orderpriority = '2-HIGH' 2023 then 1 2024 else 0 2025 end) as high_line_count, 2026 sum(case 2027 when o_orderpriority <> '1-URGENT' 2028 and o_orderpriority <> '2-HIGH' 2029 then 1 2030 else 0 2031 end) as low_line_count 2032 from 2033 orders, 2034 lineitem 2035 where 2036 o_orderkey = l_orderkey 2037 and l_shipmode in ('MAIL', 'SHIP') 2038 and l_commitdate < l_receiptdate 2039 and l_shipdate < l_commitdate 2040 and l_receiptdate >= '1994-01-01' 2041 and l_receiptdate < '1994-01-01' + interval '1' year 2042 group by 2043 l_shipmode 2044 order by 2045 l_shipmode;`, 2046 ExpectedPlan: "Project\n" + 2047 " ├─ columns: [lineitem.l_shipmode:2!null, sum(case when ((orders.o_orderpriority = '1-urgent') or (orders.o_orderpriority = '2-high')) then 1 else 0 end):1!null as high_line_count, sum(case when ((not((orders.o_orderpriority = '1-urgent'))) and (not((orders.o_orderpriority = '2-high')))) then 1 else 0 end):0!null as low_line_count]\n" + 2048 " └─ Sort(lineitem.l_shipmode:2!null ASC nullsFirst)\n" + 2049 " └─ Project\n" + 2050 " ├─ columns: [sum(case when ((not((orders.o_orderpriority = '1-urgent'))) and (not((orders.o_orderpriority = '2-high')))) then 1 else 0 end):0!null, sum(case when ((orders.o_orderpriority = '1-urgent') or (orders.o_orderpriority = '2-high')) then 1 else 0 end):1!null, lineitem.l_shipmode:2!null, sum(case when ((orders.o_orderpriority = '1-urgent') or (orders.o_orderpriority = '2-high')) then 1 else 0 end):1!null as high_line_count, sum(case when ((not((orders.o_orderpriority = '1-urgent'))) and (not((orders.o_orderpriority = '2-high')))) then 1 else 0 end):0!null as low_line_count]\n" + 2051 " └─ GroupBy\n" + 2052 " ├─ select: SUM(CASE WHEN AND\n" + 2053 " │ ├─ NOT\n" + 2054 " │ │ └─ Eq\n" + 2055 " │ │ ├─ orders.o_orderpriority:1!null\n" + 2056 " │ │ └─ 1-URGENT (longtext)\n" + 2057 " │ └─ NOT\n" + 2058 " │ └─ Eq\n" + 2059 " │ ├─ orders.o_orderpriority:1!null\n" + 2060 " │ └─ 2-HIGH (longtext)\n" + 2061 " │ THEN 1 (tinyint) ELSE 0 (tinyint) END), SUM(CASE WHEN Or\n" + 2062 " │ ├─ Eq\n" + 2063 " │ │ ├─ orders.o_orderpriority:1!null\n" + 2064 " │ │ └─ 1-URGENT (longtext)\n" + 2065 " │ └─ Eq\n" + 2066 " │ ├─ orders.o_orderpriority:1!null\n" + 2067 " │ └─ 2-HIGH (longtext)\n" + 2068 " │ THEN 1 (tinyint) ELSE 0 (tinyint) END), lineitem.l_shipmode:6!null\n" + 2069 " ├─ group: lineitem.l_shipmode:6!null\n" + 2070 " └─ MergeJoin\n" + 2071 " ├─ cmp: Eq\n" + 2072 " │ ├─ orders.o_orderkey:0!null\n" + 2073 " │ └─ lineitem.l_orderkey:2!null\n" + 2074 " ├─ IndexedTableAccess(orders)\n" + 2075 " │ ├─ index: [orders.O_ORDERKEY]\n" + 2076 " │ ├─ static: [{[NULL, ∞)}]\n" + 2077 " │ ├─ colSet: (1-9)\n" + 2078 " │ ├─ tableId: 1\n" + 2079 " │ └─ Table\n" + 2080 " │ ├─ name: orders\n" + 2081 " │ └─ columns: [o_orderkey o_orderpriority]\n" + 2082 " └─ Filter\n" + 2083 " ├─ AND\n" + 2084 " │ ├─ AND\n" + 2085 " │ │ ├─ AND\n" + 2086 " │ │ │ ├─ AND\n" + 2087 " │ │ │ │ ├─ HashIn\n" + 2088 " │ │ │ │ │ ├─ lineitem.l_shipmode:4!null\n" + 2089 " │ │ │ │ │ └─ TUPLE(MAIL (longtext), SHIP (longtext))\n" + 2090 " │ │ │ │ └─ LessThan\n" + 2091 " │ │ │ │ ├─ lineitem.l_commitdate:2!null\n" + 2092 " │ │ │ │ └─ lineitem.l_receiptdate:3!null\n" + 2093 " │ │ │ └─ LessThan\n" + 2094 " │ │ │ ├─ lineitem.l_shipdate:1!null\n" + 2095 " │ │ │ └─ lineitem.l_commitdate:2!null\n" + 2096 " │ │ └─ GreaterThanOrEqual\n" + 2097 " │ │ ├─ lineitem.l_receiptdate:3!null\n" + 2098 " │ │ └─ 1994-01-01 (longtext)\n" + 2099 " │ └─ LessThan\n" + 2100 " │ ├─ lineitem.l_receiptdate:3!null\n" + 2101 " │ └─ 1995-01-01 00:00:00 +0000 UTC (datetime)\n" + 2102 " └─ IndexedTableAccess(lineitem)\n" + 2103 " ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 2104 " ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 2105 " ├─ colSet: (10-25)\n" + 2106 " ├─ tableId: 2\n" + 2107 " └─ Table\n" + 2108 " ├─ name: lineitem\n" + 2109 " └─ columns: [l_orderkey l_shipdate l_commitdate l_receiptdate l_shipmode]\n" + 2110 "", 2111 ExpectedEstimates: "Project\n" + 2112 " ├─ columns: [lineitem.l_shipmode, sum(case when ((orders.o_orderpriority = '1-urgent') or (orders.o_orderpriority = '2-high')) then 1 else 0 end) as high_line_count, sum(case when ((not((orders.o_orderpriority = '1-urgent'))) and (not((orders.o_orderpriority = '2-high')))) then 1 else 0 end) as low_line_count]\n" + 2113 " └─ Sort(lineitem.l_shipmode ASC)\n" + 2114 " └─ Project\n" + 2115 " ├─ columns: [sum(case when ((not((orders.o_orderpriority = '1-urgent'))) and (not((orders.o_orderpriority = '2-high')))) then 1 else 0 end), sum(case when ((orders.o_orderpriority = '1-urgent') or (orders.o_orderpriority = '2-high')) then 1 else 0 end), lineitem.l_shipmode, sum(case when ((orders.o_orderpriority = '1-urgent') or (orders.o_orderpriority = '2-high')) then 1 else 0 end) as high_line_count, sum(case when ((not((orders.o_orderpriority = '1-urgent'))) and (not((orders.o_orderpriority = '2-high')))) then 1 else 0 end) as low_line_count]\n" + 2116 " └─ GroupBy\n" + 2117 " ├─ SelectedExprs(SUM(CASE WHEN ((NOT((orders.o_orderpriority = '1-URGENT'))) AND (NOT((orders.o_orderpriority = '2-HIGH')))) THEN 1 ELSE 0 END), SUM(CASE WHEN ((orders.o_orderpriority = '1-URGENT') OR (orders.o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), lineitem.l_shipmode)\n" + 2118 " ├─ Grouping(lineitem.l_shipmode)\n" + 2119 " └─ MergeJoin\n" + 2120 " ├─ cmp: (orders.o_orderkey = lineitem.l_orderkey)\n" + 2121 " ├─ IndexedTableAccess(orders)\n" + 2122 " │ ├─ index: [orders.O_ORDERKEY]\n" + 2123 " │ ├─ filters: [{[NULL, ∞)}]\n" + 2124 " │ └─ columns: [o_orderkey o_orderpriority]\n" + 2125 " └─ Filter\n" + 2126 " ├─ (((((lineitem.l_shipmode HASH IN ('MAIL', 'SHIP')) AND (lineitem.l_commitdate < lineitem.l_receiptdate)) AND (lineitem.l_shipdate < lineitem.l_commitdate)) AND (lineitem.l_receiptdate >= '1994-01-01')) AND (lineitem.l_receiptdate < 1995-01-01 00:00:00 +0000 UTC))\n" + 2127 " └─ IndexedTableAccess(lineitem)\n" + 2128 " ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 2129 " ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 2130 " └─ columns: [l_orderkey l_shipdate l_commitdate l_receiptdate l_shipmode]\n" + 2131 "", 2132 ExpectedAnalysis: "Project\n" + 2133 " ├─ columns: [lineitem.l_shipmode, sum(case when ((orders.o_orderpriority = '1-urgent') or (orders.o_orderpriority = '2-high')) then 1 else 0 end) as high_line_count, sum(case when ((not((orders.o_orderpriority = '1-urgent'))) and (not((orders.o_orderpriority = '2-high')))) then 1 else 0 end) as low_line_count]\n" + 2134 " └─ Sort(lineitem.l_shipmode ASC)\n" + 2135 " └─ Project\n" + 2136 " ├─ columns: [sum(case when ((not((orders.o_orderpriority = '1-urgent'))) and (not((orders.o_orderpriority = '2-high')))) then 1 else 0 end), sum(case when ((orders.o_orderpriority = '1-urgent') or (orders.o_orderpriority = '2-high')) then 1 else 0 end), lineitem.l_shipmode, sum(case when ((orders.o_orderpriority = '1-urgent') or (orders.o_orderpriority = '2-high')) then 1 else 0 end) as high_line_count, sum(case when ((not((orders.o_orderpriority = '1-urgent'))) and (not((orders.o_orderpriority = '2-high')))) then 1 else 0 end) as low_line_count]\n" + 2137 " └─ GroupBy\n" + 2138 " ├─ SelectedExprs(SUM(CASE WHEN ((NOT((orders.o_orderpriority = '1-URGENT'))) AND (NOT((orders.o_orderpriority = '2-HIGH')))) THEN 1 ELSE 0 END), SUM(CASE WHEN ((orders.o_orderpriority = '1-URGENT') OR (orders.o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), lineitem.l_shipmode)\n" + 2139 " ├─ Grouping(lineitem.l_shipmode)\n" + 2140 " └─ MergeJoin\n" + 2141 " ├─ cmp: (orders.o_orderkey = lineitem.l_orderkey)\n" + 2142 " ├─ IndexedTableAccess(orders)\n" + 2143 " │ ├─ index: [orders.O_ORDERKEY]\n" + 2144 " │ ├─ filters: [{[NULL, ∞)}]\n" + 2145 " │ └─ columns: [o_orderkey o_orderpriority]\n" + 2146 " └─ Filter\n" + 2147 " ├─ (((((lineitem.l_shipmode HASH IN ('MAIL', 'SHIP')) AND (lineitem.l_commitdate < lineitem.l_receiptdate)) AND (lineitem.l_shipdate < lineitem.l_commitdate)) AND (lineitem.l_receiptdate >= '1994-01-01')) AND (lineitem.l_receiptdate < 1995-01-01 00:00:00 +0000 UTC))\n" + 2148 " └─ IndexedTableAccess(lineitem)\n" + 2149 " ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 2150 " ├─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 2151 " └─ columns: [l_orderkey l_shipdate l_commitdate l_receiptdate l_shipmode]\n" + 2152 "", 2153 }, 2154 { 2155 Query: ` 2156 --Q13 2157 select 2158 c_count, 2159 count(*) as custdist 2160 from 2161 ( 2162 select 2163 c_custkey, 2164 count(o_orderkey) 2165 from 2166 customer left outer join orders on 2167 c_custkey = o_custkey 2168 and o_comment not like '%special%requests%' 2169 group by 2170 c_custkey 2171 ) as c_orders (c_custkey, c_count) 2172 group by 2173 c_count 2174 order by 2175 custdist desc, 2176 c_count desc;`, 2177 ExpectedPlan: "Project\n" + 2178 " ├─ columns: [c_orders.c_count:1!null, count(1):0!null as custdist]\n" + 2179 " └─ Sort(count(1):0!null as custdist DESC nullsFirst, c_orders.c_count:1!null DESC nullsFirst)\n" + 2180 " └─ Project\n" + 2181 " ├─ columns: [count(1):0!null, c_orders.c_count:1!null, count(1):0!null as custdist]\n" + 2182 " └─ GroupBy\n" + 2183 " ├─ select: COUNT(1 (bigint)), c_orders.c_count:1!null\n" + 2184 " ├─ group: c_orders.c_count:1!null\n" + 2185 " └─ SubqueryAlias\n" + 2186 " ├─ name: c_orders\n" + 2187 " ├─ outerVisibility: false\n" + 2188 " ├─ isLateral: false\n" + 2189 " ├─ cacheable: true\n" + 2190 " ├─ colSet: (19,20)\n" + 2191 " ├─ tableId: 3\n" + 2192 " └─ Project\n" + 2193 " ├─ columns: [customer.c_custkey:1!null, count(orders.o_orderkey):0!null as count(o_orderkey)]\n" + 2194 " └─ GroupBy\n" + 2195 " ├─ select: COUNT(orders.o_orderkey:1!null), customer.c_custkey:0!null\n" + 2196 " ├─ group: customer.c_custkey:0!null\n" + 2197 " └─ LeftOuterJoin\n" + 2198 " ├─ AND\n" + 2199 " │ ├─ Eq\n" + 2200 " │ │ ├─ customer.c_custkey:0!null\n" + 2201 " │ │ └─ orders.o_custkey:2!null\n" + 2202 " │ └─ NOT\n" + 2203 " │ └─ orders.o_comment LIKE '%special%requests%'\n" + 2204 " ├─ Table\n" + 2205 " │ ├─ name: customer\n" + 2206 " │ ├─ columns: [c_custkey]\n" + 2207 " │ ├─ colSet: (1-8)\n" + 2208 " │ └─ tableId: 1\n" + 2209 " └─ Table\n" + 2210 " ├─ name: orders\n" + 2211 " ├─ columns: [o_orderkey o_custkey o_comment]\n" + 2212 " ├─ colSet: (9-17)\n" + 2213 " └─ tableId: 2\n" + 2214 "", 2215 ExpectedEstimates: "Project\n" + 2216 " ├─ columns: [c_orders.c_count, count(1) as custdist]\n" + 2217 " └─ Sort(count(1) as custdist DESC, c_orders.c_count DESC)\n" + 2218 " └─ Project\n" + 2219 " ├─ columns: [count(1), c_orders.c_count, count(1) as custdist]\n" + 2220 " └─ GroupBy\n" + 2221 " ├─ SelectedExprs(COUNT(1), c_orders.c_count)\n" + 2222 " ├─ Grouping(c_orders.c_count)\n" + 2223 " └─ SubqueryAlias\n" + 2224 " ├─ name: c_orders\n" + 2225 " ├─ outerVisibility: false\n" + 2226 " ├─ isLateral: false\n" + 2227 " ├─ cacheable: true\n" + 2228 " └─ Project\n" + 2229 " ├─ columns: [customer.c_custkey, count(orders.o_orderkey) as count(o_orderkey)]\n" + 2230 " └─ GroupBy\n" + 2231 " ├─ SelectedExprs(COUNT(orders.o_orderkey), customer.c_custkey)\n" + 2232 " ├─ Grouping(customer.c_custkey)\n" + 2233 " └─ LeftOuterJoin\n" + 2234 " ├─ ((customer.c_custkey = orders.o_custkey) AND (NOT(orders.o_comment LIKE '%special%requests%')))\n" + 2235 " ├─ Table\n" + 2236 " │ ├─ name: customer\n" + 2237 " │ └─ columns: [c_custkey]\n" + 2238 " └─ Table\n" + 2239 " ├─ name: orders\n" + 2240 " └─ columns: [o_orderkey o_custkey o_comment]\n" + 2241 "", 2242 ExpectedAnalysis: "Project\n" + 2243 " ├─ columns: [c_orders.c_count, count(1) as custdist]\n" + 2244 " └─ Sort(count(1) as custdist DESC, c_orders.c_count DESC)\n" + 2245 " └─ Project\n" + 2246 " ├─ columns: [count(1), c_orders.c_count, count(1) as custdist]\n" + 2247 " └─ GroupBy\n" + 2248 " ├─ SelectedExprs(COUNT(1), c_orders.c_count)\n" + 2249 " ├─ Grouping(c_orders.c_count)\n" + 2250 " └─ SubqueryAlias\n" + 2251 " ├─ name: c_orders\n" + 2252 " ├─ outerVisibility: false\n" + 2253 " ├─ isLateral: false\n" + 2254 " ├─ cacheable: true\n" + 2255 " └─ Project\n" + 2256 " ├─ columns: [customer.c_custkey, count(orders.o_orderkey) as count(o_orderkey)]\n" + 2257 " └─ GroupBy\n" + 2258 " ├─ SelectedExprs(COUNT(orders.o_orderkey), customer.c_custkey)\n" + 2259 " ├─ Grouping(customer.c_custkey)\n" + 2260 " └─ LeftOuterJoin\n" + 2261 " ├─ ((customer.c_custkey = orders.o_custkey) AND (NOT(orders.o_comment LIKE '%special%requests%')))\n" + 2262 " ├─ Table\n" + 2263 " │ ├─ name: customer\n" + 2264 " │ └─ columns: [c_custkey]\n" + 2265 " └─ Table\n" + 2266 " ├─ name: orders\n" + 2267 " └─ columns: [o_orderkey o_custkey o_comment]\n" + 2268 "", 2269 }, 2270 { 2271 Query: ` 2272 --Q14 2273 select 2274 100.00 * sum(case 2275 when p_type like 'PROMO%' 2276 then l_extendedprice * (1 - l_discount) 2277 else 0 2278 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue 2279 from 2280 lineitem, 2281 part 2282 where 2283 l_partkey = p_partkey 2284 and l_shipdate >= '1995-09-01' 2285 and l_shipdate < '1995-09-01' + interval '1' month;`, 2286 ExpectedPlan: "Project\n" + 2287 " ├─ columns: [((100 (decimal(5,2)) * sum(case when part.p_type like 'promo%' then (lineitem.l_extendedprice * (1 - lineitem.l_discount)) else 0 end):1!null) / sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null) as promo_revenue]\n" + 2288 " └─ GroupBy\n" + 2289 " ├─ select: SUM((lineitem.l_extendedprice:1!null * (1 (tinyint) - lineitem.l_discount:2!null))), SUM(CASE WHEN part.p_type LIKE 'PROMO%' THEN (lineitem.l_extendedprice:1!null * (1 (tinyint) - lineitem.l_discount:2!null)) ELSE 0 (tinyint) END)\n" + 2290 " ├─ group: \n" + 2291 " └─ LookupJoin\n" + 2292 " ├─ Filter\n" + 2293 " │ ├─ AND\n" + 2294 " │ │ ├─ GreaterThanOrEqual\n" + 2295 " │ │ │ ├─ lineitem.l_shipdate:3!null\n" + 2296 " │ │ │ └─ 1995-09-01 (longtext)\n" + 2297 " │ │ └─ LessThan\n" + 2298 " │ │ ├─ lineitem.l_shipdate:3!null\n" + 2299 " │ │ └─ 1995-10-01 00:00:00 +0000 UTC (datetime)\n" + 2300 " │ └─ ProcessTable\n" + 2301 " │ └─ Table\n" + 2302 " │ ├─ name: lineitem\n" + 2303 " │ └─ columns: [l_partkey l_extendedprice l_discount l_shipdate]\n" + 2304 " └─ IndexedTableAccess(part)\n" + 2305 " ├─ index: [part.P_PARTKEY]\n" + 2306 " ├─ keys: [lineitem.l_partkey:0!null]\n" + 2307 " ├─ colSet: (17-25)\n" + 2308 " ├─ tableId: 2\n" + 2309 " └─ Table\n" + 2310 " ├─ name: part\n" + 2311 " └─ columns: [p_partkey p_type]\n" + 2312 "", 2313 ExpectedEstimates: "Project\n" + 2314 " ├─ columns: [((100.00 * sum(case when part.p_type like 'promo%' then (lineitem.l_extendedprice * (1 - lineitem.l_discount)) else 0 end)) / sum((lineitem.l_extendedprice * (1 - lineitem.l_discount)))) as promo_revenue]\n" + 2315 " └─ GroupBy\n" + 2316 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), SUM(CASE WHEN part.p_type LIKE 'PROMO%' THEN (lineitem.l_extendedprice * (1 - lineitem.l_discount)) ELSE 0 END))\n" + 2317 " ├─ Grouping()\n" + 2318 " └─ LookupJoin\n" + 2319 " ├─ Filter\n" + 2320 " │ ├─ ((lineitem.l_shipdate >= '1995-09-01') AND (lineitem.l_shipdate < 1995-10-01 00:00:00 +0000 UTC))\n" + 2321 " │ └─ Table\n" + 2322 " │ ├─ name: lineitem\n" + 2323 " │ └─ columns: [l_partkey l_extendedprice l_discount l_shipdate]\n" + 2324 " └─ IndexedTableAccess(part)\n" + 2325 " ├─ index: [part.P_PARTKEY]\n" + 2326 " ├─ columns: [p_partkey p_type]\n" + 2327 " └─ keys: lineitem.l_partkey\n" + 2328 "", 2329 ExpectedAnalysis: "Project\n" + 2330 " ├─ columns: [((100.00 * sum(case when part.p_type like 'promo%' then (lineitem.l_extendedprice * (1 - lineitem.l_discount)) else 0 end)) / sum((lineitem.l_extendedprice * (1 - lineitem.l_discount)))) as promo_revenue]\n" + 2331 " └─ GroupBy\n" + 2332 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), SUM(CASE WHEN part.p_type LIKE 'PROMO%' THEN (lineitem.l_extendedprice * (1 - lineitem.l_discount)) ELSE 0 END))\n" + 2333 " ├─ Grouping()\n" + 2334 " └─ LookupJoin\n" + 2335 " ├─ Filter\n" + 2336 " │ ├─ ((lineitem.l_shipdate >= '1995-09-01') AND (lineitem.l_shipdate < 1995-10-01 00:00:00 +0000 UTC))\n" + 2337 " │ └─ Table\n" + 2338 " │ ├─ name: lineitem\n" + 2339 " │ └─ columns: [l_partkey l_extendedprice l_discount l_shipdate]\n" + 2340 " └─ IndexedTableAccess(part)\n" + 2341 " ├─ index: [part.P_PARTKEY]\n" + 2342 " ├─ columns: [p_partkey p_type]\n" + 2343 " └─ keys: lineitem.l_partkey\n" + 2344 "", 2345 }, 2346 { 2347 Query: ` 2348 --Q15 2349 with revenue0 (supplier_no, total_revenue) as ( 2350 select 2351 l_suppkey, 2352 sum(l_extendedprice * (1 - l_discount)) 2353 from 2354 lineitem 2355 where 2356 l_shipdate >= '1996-01-01' 2357 and l_shipdate < '1996-01-01' + interval '3' month 2358 group by 2359 l_suppkey) 2360 select 2361 s_suppkey, 2362 s_name, 2363 s_address, 2364 s_phone, 2365 total_revenue 2366 from 2367 supplier, 2368 revenue0 2369 where 2370 s_suppkey = supplier_no 2371 and total_revenue = ( 2372 select 2373 max(total_revenue) 2374 from 2375 revenue0 2376 ) 2377 order by 2378 s_suppkey;`, 2379 ExpectedPlan: "Project\n" + 2380 " ├─ columns: [supplier.s_suppkey:2!null, supplier.s_name:3!null, supplier.s_address:4!null, supplier.s_phone:6!null, revenue0.total_revenue:1!null]\n" + 2381 " └─ Sort(supplier.s_suppkey:2!null ASC nullsFirst)\n" + 2382 " └─ Filter\n" + 2383 " ├─ Eq\n" + 2384 " │ ├─ revenue0.total_revenue:1!null\n" + 2385 " │ └─ Subquery\n" + 2386 " │ ├─ cacheable: true\n" + 2387 " │ ├─ alias-string: select max(total_revenue) from revenue0\n" + 2388 " │ └─ Project\n" + 2389 " │ ├─ columns: [max(revenue0.total_revenue):9!null as max(total_revenue)]\n" + 2390 " │ └─ GroupBy\n" + 2391 " │ ├─ select: MAX(revenue0.total_revenue:1!null)\n" + 2392 " │ ├─ group: \n" + 2393 " │ └─ SubqueryAlias\n" + 2394 " │ ├─ name: revenue0\n" + 2395 " │ ├─ outerVisibility: true\n" + 2396 " │ ├─ isLateral: false\n" + 2397 " │ ├─ cacheable: true\n" + 2398 " │ ├─ colSet: (18,19)\n" + 2399 " │ ├─ tableId: 2\n" + 2400 " │ └─ Project\n" + 2401 " │ ├─ columns: [lineitem.l_suppkey:10!null, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):9!null as sum(l_extendedprice * (1 - l_discount))]\n" + 2402 " │ └─ GroupBy\n" + 2403 " │ ├─ select: SUM((lineitem.l_extendedprice:10!null * (1 (tinyint) - lineitem.l_discount:11!null))), lineitem.l_suppkey:9!null\n" + 2404 " │ ├─ group: lineitem.l_suppkey:9!null\n" + 2405 " │ └─ Filter\n" + 2406 " │ ├─ AND\n" + 2407 " │ │ ├─ GreaterThanOrEqual\n" + 2408 " │ │ │ ├─ lineitem.l_shipdate:12!null\n" + 2409 " │ │ │ └─ 1996-01-01 (longtext)\n" + 2410 " │ │ └─ LessThan\n" + 2411 " │ │ ├─ lineitem.l_shipdate:12!null\n" + 2412 " │ │ └─ 1996-04-01 00:00:00 +0000 UTC (datetime)\n" + 2413 " │ └─ Table\n" + 2414 " │ ├─ name: lineitem\n" + 2415 " │ ├─ columns: [l_suppkey l_extendedprice l_discount l_shipdate]\n" + 2416 " │ ├─ colSet: (1-16)\n" + 2417 " │ └─ tableId: 1\n" + 2418 " └─ LookupJoin\n" + 2419 " ├─ SubqueryAlias\n" + 2420 " │ ├─ name: revenue0\n" + 2421 " │ ├─ outerVisibility: false\n" + 2422 " │ ├─ isLateral: false\n" + 2423 " │ ├─ cacheable: true\n" + 2424 " │ ├─ colSet: (18,19)\n" + 2425 " │ ├─ tableId: 2\n" + 2426 " │ └─ Project\n" + 2427 " │ ├─ columns: [lineitem.l_suppkey:1!null, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as sum(l_extendedprice * (1 - l_discount))]\n" + 2428 " │ └─ GroupBy\n" + 2429 " │ ├─ select: SUM((lineitem.l_extendedprice:1!null * (1 (tinyint) - lineitem.l_discount:2!null))), lineitem.l_suppkey:0!null\n" + 2430 " │ ├─ group: lineitem.l_suppkey:0!null\n" + 2431 " │ └─ Filter\n" + 2432 " │ ├─ AND\n" + 2433 " │ │ ├─ GreaterThanOrEqual\n" + 2434 " │ │ │ ├─ lineitem.l_shipdate:3!null\n" + 2435 " │ │ │ └─ 1996-01-01 (longtext)\n" + 2436 " │ │ └─ LessThan\n" + 2437 " │ │ ├─ lineitem.l_shipdate:3!null\n" + 2438 " │ │ └─ 1996-04-01 00:00:00 +0000 UTC (datetime)\n" + 2439 " │ └─ Table\n" + 2440 " │ ├─ name: lineitem\n" + 2441 " │ ├─ columns: [l_suppkey l_extendedprice l_discount l_shipdate]\n" + 2442 " │ ├─ colSet: (1-16)\n" + 2443 " │ └─ tableId: 1\n" + 2444 " └─ IndexedTableAccess(supplier)\n" + 2445 " ├─ index: [supplier.S_SUPPKEY]\n" + 2446 " ├─ keys: [revenue0.supplier_no:0!null]\n" + 2447 " ├─ colSet: (20-26)\n" + 2448 " ├─ tableId: 4\n" + 2449 " └─ Table\n" + 2450 " ├─ name: supplier\n" + 2451 " └─ columns: [s_suppkey s_name s_address s_nationkey s_phone s_acctbal s_comment]\n" + 2452 "", 2453 ExpectedEstimates: "Project\n" + 2454 " ├─ columns: [supplier.s_suppkey, supplier.s_name, supplier.s_address, supplier.s_phone, revenue0.total_revenue]\n" + 2455 " └─ Sort(supplier.s_suppkey ASC)\n" + 2456 " └─ Filter\n" + 2457 " ├─ (revenue0.total_revenue = Subquery\n" + 2458 " │ ├─ cacheable: true\n" + 2459 " │ └─ Project\n" + 2460 " │ ├─ columns: [max(revenue0.total_revenue) as max(total_revenue)]\n" + 2461 " │ └─ GroupBy\n" + 2462 " │ ├─ SelectedExprs(MAX(revenue0.total_revenue))\n" + 2463 " │ ├─ Grouping()\n" + 2464 " │ └─ SubqueryAlias\n" + 2465 " │ ├─ name: revenue0\n" + 2466 " │ ├─ outerVisibility: true\n" + 2467 " │ ├─ isLateral: false\n" + 2468 " │ ├─ cacheable: true\n" + 2469 " │ └─ Project\n" + 2470 " │ ├─ columns: [lineitem.l_suppkey, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as sum(l_extendedprice * (1 - l_discount))]\n" + 2471 " │ └─ GroupBy\n" + 2472 " │ ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), lineitem.l_suppkey)\n" + 2473 " │ ├─ Grouping(lineitem.l_suppkey)\n" + 2474 " │ └─ Filter\n" + 2475 " │ ├─ ((lineitem.l_shipdate >= '1996-01-01') AND (lineitem.l_shipdate < 1996-04-01 00:00:00 +0000 UTC))\n" + 2476 " │ └─ Table\n" + 2477 " │ ├─ name: lineitem\n" + 2478 " │ └─ columns: [l_suppkey l_extendedprice l_discount l_shipdate]\n" + 2479 " │ )\n" + 2480 " └─ LookupJoin\n" + 2481 " ├─ SubqueryAlias\n" + 2482 " │ ├─ name: revenue0\n" + 2483 " │ ├─ outerVisibility: false\n" + 2484 " │ ├─ isLateral: false\n" + 2485 " │ ├─ cacheable: true\n" + 2486 " │ └─ Project\n" + 2487 " │ ├─ columns: [lineitem.l_suppkey, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as sum(l_extendedprice * (1 - l_discount))]\n" + 2488 " │ └─ GroupBy\n" + 2489 " │ ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), lineitem.l_suppkey)\n" + 2490 " │ ├─ Grouping(lineitem.l_suppkey)\n" + 2491 " │ └─ Filter\n" + 2492 " │ ├─ ((lineitem.l_shipdate >= '1996-01-01') AND (lineitem.l_shipdate < 1996-04-01 00:00:00 +0000 UTC))\n" + 2493 " │ └─ Table\n" + 2494 " │ ├─ name: lineitem\n" + 2495 " │ └─ columns: [l_suppkey l_extendedprice l_discount l_shipdate]\n" + 2496 " └─ IndexedTableAccess(supplier)\n" + 2497 " ├─ index: [supplier.S_SUPPKEY]\n" + 2498 " └─ keys: revenue0.supplier_no\n" + 2499 "", 2500 ExpectedAnalysis: "Project\n" + 2501 " ├─ columns: [supplier.s_suppkey, supplier.s_name, supplier.s_address, supplier.s_phone, revenue0.total_revenue]\n" + 2502 " └─ Sort(supplier.s_suppkey ASC)\n" + 2503 " └─ Filter\n" + 2504 " ├─ (revenue0.total_revenue = Subquery\n" + 2505 " │ ├─ cacheable: true\n" + 2506 " │ └─ Project\n" + 2507 " │ ├─ columns: [max(revenue0.total_revenue) as max(total_revenue)]\n" + 2508 " │ └─ GroupBy\n" + 2509 " │ ├─ SelectedExprs(MAX(revenue0.total_revenue))\n" + 2510 " │ ├─ Grouping()\n" + 2511 " │ └─ SubqueryAlias\n" + 2512 " │ ├─ name: revenue0\n" + 2513 " │ ├─ outerVisibility: true\n" + 2514 " │ ├─ isLateral: false\n" + 2515 " │ ├─ cacheable: true\n" + 2516 " │ └─ Project\n" + 2517 " │ ├─ columns: [lineitem.l_suppkey, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as sum(l_extendedprice * (1 - l_discount))]\n" + 2518 " │ └─ GroupBy\n" + 2519 " │ ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), lineitem.l_suppkey)\n" + 2520 " │ ├─ Grouping(lineitem.l_suppkey)\n" + 2521 " │ └─ Filter\n" + 2522 " │ ├─ ((lineitem.l_shipdate >= '1996-01-01') AND (lineitem.l_shipdate < 1996-04-01 00:00:00 +0000 UTC))\n" + 2523 " │ └─ Table\n" + 2524 " │ ├─ name: lineitem\n" + 2525 " │ └─ columns: [l_suppkey l_extendedprice l_discount l_shipdate]\n" + 2526 " │ )\n" + 2527 " └─ LookupJoin\n" + 2528 " ├─ SubqueryAlias\n" + 2529 " │ ├─ name: revenue0\n" + 2530 " │ ├─ outerVisibility: false\n" + 2531 " │ ├─ isLateral: false\n" + 2532 " │ ├─ cacheable: true\n" + 2533 " │ └─ Project\n" + 2534 " │ ├─ columns: [lineitem.l_suppkey, sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as sum(l_extendedprice * (1 - l_discount))]\n" + 2535 " │ └─ GroupBy\n" + 2536 " │ ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))), lineitem.l_suppkey)\n" + 2537 " │ ├─ Grouping(lineitem.l_suppkey)\n" + 2538 " │ └─ Filter\n" + 2539 " │ ├─ ((lineitem.l_shipdate >= '1996-01-01') AND (lineitem.l_shipdate < 1996-04-01 00:00:00 +0000 UTC))\n" + 2540 " │ └─ Table\n" + 2541 " │ ├─ name: lineitem\n" + 2542 " │ └─ columns: [l_suppkey l_extendedprice l_discount l_shipdate]\n" + 2543 " └─ IndexedTableAccess(supplier)\n" + 2544 " ├─ index: [supplier.S_SUPPKEY]\n" + 2545 " └─ keys: revenue0.supplier_no\n" + 2546 "", 2547 }, 2548 { 2549 Query: ` 2550 --Q16 2551 select 2552 p_brand, 2553 p_type, 2554 p_size, 2555 count(distinct ps_suppkey) as supplier_cnt 2556 from 2557 partsupp, 2558 part 2559 where 2560 p_partkey = ps_partkey 2561 and p_brand <> 'Brand#45' 2562 and p_type not like 'MEDIUM POLISHED%' 2563 and p_size in (49, 14, 23, 45, 19, 3, 36, 9) 2564 and ps_suppkey not in ( 2565 select 2566 s_suppkey 2567 from 2568 supplier 2569 where 2570 s_comment like '%Customer%Complaints%' 2571 ) 2572 group by 2573 p_brand, 2574 p_type, 2575 p_size 2576 order by 2577 supplier_cnt desc, 2578 p_brand, 2579 p_type, 2580 p_size;`, 2581 ExpectedPlan: "Project\n" + 2582 " ├─ columns: [part.p_brand:1!null, part.p_type:2!null, part.p_size:3!null, countdistinct([partsupp.ps_suppkey]):0!null as supplier_cnt]\n" + 2583 " └─ Sort(countdistinct([partsupp.ps_suppkey]):0!null as supplier_cnt DESC nullsFirst, part.p_brand:1!null ASC nullsFirst, part.p_type:2!null ASC nullsFirst, part.p_size:3!null ASC nullsFirst)\n" + 2584 " └─ Project\n" + 2585 " ├─ columns: [countdistinct([partsupp.ps_suppkey]):0!null, part.p_brand:1!null, part.p_type:2!null, part.p_size:3!null, countdistinct([partsupp.ps_suppkey]):0!null as supplier_cnt]\n" + 2586 " └─ GroupBy\n" + 2587 " ├─ select: COUNTDISTINCT([partsupp.ps_suppkey]), part.p_brand:8!null, part.p_type:9!null, part.p_size:10!null\n" + 2588 " ├─ group: part.p_brand:8!null, part.p_type:9!null, part.p_size:10!null\n" + 2589 " └─ Project\n" + 2590 " ├─ columns: [partsupp.PS_PARTKEY:0!null, partsupp.PS_SUPPKEY:1!null, partsupp.PS_AVAILQTY:2!null, partsupp.PS_SUPPLYCOST:3!null, partsupp.PS_COMMENT:4!null, part.P_PARTKEY:5!null, part.P_NAME:6!null, part.P_MFGR:7!null, part.P_BRAND:8!null, part.P_TYPE:9!null, part.P_SIZE:10!null, part.P_CONTAINER:11!null, part.P_RETAILPRICE:12!null, part.P_COMMENT:13!null]\n" + 2591 " └─ Filter\n" + 2592 " ├─ supplier.s_suppkey:14!null IS NULL\n" + 2593 " └─ LeftOuterLookupJoin\n" + 2594 " ├─ MergeJoin\n" + 2595 " │ ├─ cmp: Eq\n" + 2596 " │ │ ├─ partsupp.ps_partkey:0!null\n" + 2597 " │ │ └─ part.p_partkey:5!null\n" + 2598 " │ ├─ IndexedTableAccess(partsupp)\n" + 2599 " │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 2600 " │ │ ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 2601 " │ │ ├─ colSet: (1-5)\n" + 2602 " │ │ ├─ tableId: 1\n" + 2603 " │ │ └─ Table\n" + 2604 " │ │ ├─ name: partsupp\n" + 2605 " │ │ └─ columns: [ps_partkey ps_suppkey ps_availqty ps_supplycost ps_comment]\n" + 2606 " │ └─ Filter\n" + 2607 " │ ├─ AND\n" + 2608 " │ │ ├─ AND\n" + 2609 " │ │ │ ├─ NOT\n" + 2610 " │ │ │ │ └─ Eq\n" + 2611 " │ │ │ │ ├─ part.p_brand:3!null\n" + 2612 " │ │ │ │ └─ Brand#45 (longtext)\n" + 2613 " │ │ │ └─ Or\n" + 2614 " │ │ │ ├─ LessThan\n" + 2615 " │ │ │ │ ├─ part.p_type:4!null\n" + 2616 " │ │ │ │ └─ MEDIUM POLISHED (longtext)\n" + 2617 " │ │ │ └─ GreaterThan\n" + 2618 " │ │ │ ├─ part.p_type:4!null\n" + 2619 " │ │ │ └─ MEDIUM POLISHEDÿ (longtext)\n" + 2620 " │ │ └─ HashIn\n" + 2621 " │ │ ├─ part.p_size:5!null\n" + 2622 " │ │ └─ TUPLE(49 (tinyint), 14 (tinyint), 23 (tinyint), 45 (tinyint), 19 (tinyint), 3 (tinyint), 36 (tinyint), 9 (tinyint))\n" + 2623 " │ └─ IndexedTableAccess(part)\n" + 2624 " │ ├─ index: [part.P_PARTKEY]\n" + 2625 " │ ├─ static: [{[NULL, ∞)}]\n" + 2626 " │ ├─ colSet: (6-14)\n" + 2627 " │ ├─ tableId: 2\n" + 2628 " │ └─ Table\n" + 2629 " │ ├─ name: part\n" + 2630 " │ └─ columns: [p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment]\n" + 2631 " └─ Project\n" + 2632 " ├─ columns: [supplier.s_suppkey:0!null]\n" + 2633 " └─ Filter\n" + 2634 " ├─ supplier.s_comment LIKE '%Customer%Complaints%'\n" + 2635 " └─ IndexedTableAccess(supplier)\n" + 2636 " ├─ index: [supplier.S_SUPPKEY]\n" + 2637 " ├─ keys: [partsupp.ps_suppkey:1!null]\n" + 2638 " ├─ colSet: (15-21)\n" + 2639 " ├─ tableId: 3\n" + 2640 " └─ Table\n" + 2641 " ├─ name: supplier\n" + 2642 " └─ columns: [s_suppkey s_name s_address s_nationkey s_phone s_acctbal s_comment]\n" + 2643 "", 2644 ExpectedEstimates: "Project\n" + 2645 " ├─ columns: [part.p_brand, part.p_type, part.p_size, countdistinct([partsupp.ps_suppkey]) as supplier_cnt]\n" + 2646 " └─ Sort(countdistinct([partsupp.ps_suppkey]) as supplier_cnt DESC, part.p_brand ASC, part.p_type ASC, part.p_size ASC)\n" + 2647 " └─ Project\n" + 2648 " ├─ columns: [countdistinct([partsupp.ps_suppkey]), part.p_brand, part.p_type, part.p_size, countdistinct([partsupp.ps_suppkey]) as supplier_cnt]\n" + 2649 " └─ GroupBy\n" + 2650 " ├─ SelectedExprs(COUNTDISTINCT([partsupp.ps_suppkey]), part.p_brand, part.p_type, part.p_size)\n" + 2651 " ├─ Grouping(part.p_brand, part.p_type, part.p_size)\n" + 2652 " └─ Project\n" + 2653 " ├─ columns: [partsupp.PS_PARTKEY, partsupp.PS_SUPPKEY, partsupp.PS_AVAILQTY, partsupp.PS_SUPPLYCOST, partsupp.PS_COMMENT, part.P_PARTKEY, part.P_NAME, part.P_MFGR, part.P_BRAND, part.P_TYPE, part.P_SIZE, part.P_CONTAINER, part.P_RETAILPRICE, part.P_COMMENT]\n" + 2654 " └─ Filter\n" + 2655 " ├─ supplier.s_suppkey IS NULL\n" + 2656 " └─ LeftOuterLookupJoin\n" + 2657 " ├─ MergeJoin\n" + 2658 " │ ├─ cmp: (partsupp.ps_partkey = part.p_partkey)\n" + 2659 " │ ├─ IndexedTableAccess(partsupp)\n" + 2660 " │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 2661 " │ │ └─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 2662 " │ └─ Filter\n" + 2663 " │ ├─ (((NOT((part.p_brand = 'Brand#45'))) AND ((part.p_type < 'MEDIUM POLISHED') OR (part.p_type > 'MEDIUM POLISHEDÿ'))) AND (part.p_size HASH IN (49, 14, 23, 45, 19, 3, 36, 9)))\n" + 2664 " │ └─ IndexedTableAccess(part)\n" + 2665 " │ ├─ index: [part.P_PARTKEY]\n" + 2666 " │ └─ filters: [{[NULL, ∞)}]\n" + 2667 " └─ Project\n" + 2668 " ├─ columns: [supplier.s_suppkey]\n" + 2669 " └─ Filter\n" + 2670 " ├─ supplier.s_comment LIKE '%Customer%Complaints%'\n" + 2671 " └─ IndexedTableAccess(supplier)\n" + 2672 " ├─ index: [supplier.S_SUPPKEY]\n" + 2673 " └─ keys: partsupp.ps_suppkey\n" + 2674 "", 2675 ExpectedAnalysis: "Project\n" + 2676 " ├─ columns: [part.p_brand, part.p_type, part.p_size, countdistinct([partsupp.ps_suppkey]) as supplier_cnt]\n" + 2677 " └─ Sort(countdistinct([partsupp.ps_suppkey]) as supplier_cnt DESC, part.p_brand ASC, part.p_type ASC, part.p_size ASC)\n" + 2678 " └─ Project\n" + 2679 " ├─ columns: [countdistinct([partsupp.ps_suppkey]), part.p_brand, part.p_type, part.p_size, countdistinct([partsupp.ps_suppkey]) as supplier_cnt]\n" + 2680 " └─ GroupBy\n" + 2681 " ├─ SelectedExprs(COUNTDISTINCT([partsupp.ps_suppkey]), part.p_brand, part.p_type, part.p_size)\n" + 2682 " ├─ Grouping(part.p_brand, part.p_type, part.p_size)\n" + 2683 " └─ Project\n" + 2684 " ├─ columns: [partsupp.PS_PARTKEY, partsupp.PS_SUPPKEY, partsupp.PS_AVAILQTY, partsupp.PS_SUPPLYCOST, partsupp.PS_COMMENT, part.P_PARTKEY, part.P_NAME, part.P_MFGR, part.P_BRAND, part.P_TYPE, part.P_SIZE, part.P_CONTAINER, part.P_RETAILPRICE, part.P_COMMENT]\n" + 2685 " └─ Filter\n" + 2686 " ├─ supplier.s_suppkey IS NULL\n" + 2687 " └─ LeftOuterLookupJoin\n" + 2688 " ├─ MergeJoin\n" + 2689 " │ ├─ cmp: (partsupp.ps_partkey = part.p_partkey)\n" + 2690 " │ ├─ IndexedTableAccess(partsupp)\n" + 2691 " │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 2692 " │ │ └─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 2693 " │ └─ Filter\n" + 2694 " │ ├─ (((NOT((part.p_brand = 'Brand#45'))) AND ((part.p_type < 'MEDIUM POLISHED') OR (part.p_type > 'MEDIUM POLISHEDÿ'))) AND (part.p_size HASH IN (49, 14, 23, 45, 19, 3, 36, 9)))\n" + 2695 " │ └─ IndexedTableAccess(part)\n" + 2696 " │ ├─ index: [part.P_PARTKEY]\n" + 2697 " │ └─ filters: [{[NULL, ∞)}]\n" + 2698 " └─ Project\n" + 2699 " ├─ columns: [supplier.s_suppkey]\n" + 2700 " └─ Filter\n" + 2701 " ├─ supplier.s_comment LIKE '%Customer%Complaints%'\n" + 2702 " └─ IndexedTableAccess(supplier)\n" + 2703 " ├─ index: [supplier.S_SUPPKEY]\n" + 2704 " └─ keys: partsupp.ps_suppkey\n" + 2705 "", 2706 }, 2707 { 2708 Query: ` 2709 --Q17 2710 select 2711 sum(l_extendedprice) / 7.0 as avg_yearly 2712 from 2713 lineitem, 2714 part 2715 where 2716 p_partkey = l_partkey 2717 and p_brand = 'Brand#23' 2718 and p_container = 'MED BOX' 2719 and l_quantity < ( 2720 select 2721 0.2 * avg(l_quantity) 2722 from 2723 lineitem 2724 where 2725 l_partkey = p_partkey 2726 );`, 2727 ExpectedPlan: "Project\n" + 2728 " ├─ columns: [(sum(lineitem.l_extendedprice):0!null / 7 (decimal(2,1))) as avg_yearly]\n" + 2729 " └─ GroupBy\n" + 2730 " ├─ select: SUM(lineitem.l_extendedprice:5!null)\n" + 2731 " ├─ group: \n" + 2732 " └─ Filter\n" + 2733 " ├─ LessThan\n" + 2734 " │ ├─ lineitem.l_quantity:4!null\n" + 2735 " │ └─ Subquery\n" + 2736 " │ ├─ cacheable: false\n" + 2737 " │ ├─ alias-string: select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey\n" + 2738 " │ └─ Project\n" + 2739 " │ ├─ columns: [(0.2 (decimal(2,1)) * avg(lineitem.l_quantity):25) as 0.2 * avg(l_quantity)]\n" + 2740 " │ └─ GroupBy\n" + 2741 " │ ├─ select: AVG(lineitem.l_quantity:26!null)\n" + 2742 " │ ├─ group: \n" + 2743 " │ └─ Filter\n" + 2744 " │ ├─ Eq\n" + 2745 " │ │ ├─ lineitem.l_partkey:25!null\n" + 2746 " │ │ └─ part.p_partkey:16!null\n" + 2747 " │ └─ Table\n" + 2748 " │ ├─ name: lineitem\n" + 2749 " │ ├─ columns: [l_partkey l_quantity]\n" + 2750 " │ ├─ colSet: (26-41)\n" + 2751 " │ └─ tableId: 3\n" + 2752 " └─ HashJoin\n" + 2753 " ├─ Eq\n" + 2754 " │ ├─ part.p_partkey:16!null\n" + 2755 " │ └─ lineitem.l_partkey:1!null\n" + 2756 " ├─ ProcessTable\n" + 2757 " │ └─ Table\n" + 2758 " │ ├─ name: lineitem\n" + 2759 " │ └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 2760 " └─ HashLookup\n" + 2761 " ├─ left-key: TUPLE(lineitem.l_partkey:1!null)\n" + 2762 " ├─ right-key: TUPLE(part.p_partkey:0!null)\n" + 2763 " └─ Filter\n" + 2764 " ├─ AND\n" + 2765 " │ ├─ Eq\n" + 2766 " │ │ ├─ part.p_brand:3!null\n" + 2767 " │ │ └─ Brand#23 (longtext)\n" + 2768 " │ └─ Eq\n" + 2769 " │ ├─ part.p_container:6!null\n" + 2770 " │ └─ MED BOX (longtext)\n" + 2771 " └─ ProcessTable\n" + 2772 " └─ Table\n" + 2773 " ├─ name: part\n" + 2774 " └─ columns: [p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment]\n" + 2775 "", 2776 ExpectedEstimates: "Project\n" + 2777 " ├─ columns: [(sum(lineitem.l_extendedprice) / 7.0) as avg_yearly]\n" + 2778 " └─ GroupBy\n" + 2779 " ├─ SelectedExprs(SUM(lineitem.l_extendedprice))\n" + 2780 " ├─ Grouping()\n" + 2781 " └─ Filter\n" + 2782 " ├─ (lineitem.l_quantity < Subquery\n" + 2783 " │ ├─ cacheable: false\n" + 2784 " │ └─ Project\n" + 2785 " │ ├─ columns: [(0.2 * avg(lineitem.l_quantity)) as 0.2 * avg(l_quantity)]\n" + 2786 " │ └─ GroupBy\n" + 2787 " │ ├─ SelectedExprs(AVG(lineitem.l_quantity))\n" + 2788 " │ ├─ Grouping()\n" + 2789 " │ └─ Filter\n" + 2790 " │ ├─ (lineitem.l_partkey = part.p_partkey)\n" + 2791 " │ └─ Table\n" + 2792 " │ ├─ name: lineitem\n" + 2793 " │ └─ columns: [l_partkey l_quantity]\n" + 2794 " │ )\n" + 2795 " └─ HashJoin\n" + 2796 " ├─ (part.p_partkey = lineitem.l_partkey)\n" + 2797 " ├─ Table\n" + 2798 " │ └─ name: lineitem\n" + 2799 " └─ HashLookup\n" + 2800 " ├─ left-key: (lineitem.l_partkey)\n" + 2801 " ├─ right-key: (part.p_partkey)\n" + 2802 " └─ Filter\n" + 2803 " ├─ ((part.p_brand = 'Brand#23') AND (part.p_container = 'MED BOX'))\n" + 2804 " └─ Table\n" + 2805 " └─ name: part\n" + 2806 "", 2807 ExpectedAnalysis: "Project\n" + 2808 " ├─ columns: [(sum(lineitem.l_extendedprice) / 7.0) as avg_yearly]\n" + 2809 " └─ GroupBy\n" + 2810 " ├─ SelectedExprs(SUM(lineitem.l_extendedprice))\n" + 2811 " ├─ Grouping()\n" + 2812 " └─ Filter\n" + 2813 " ├─ (lineitem.l_quantity < Subquery\n" + 2814 " │ ├─ cacheable: false\n" + 2815 " │ └─ Project\n" + 2816 " │ ├─ columns: [(0.2 * avg(lineitem.l_quantity)) as 0.2 * avg(l_quantity)]\n" + 2817 " │ └─ GroupBy\n" + 2818 " │ ├─ SelectedExprs(AVG(lineitem.l_quantity))\n" + 2819 " │ ├─ Grouping()\n" + 2820 " │ └─ Filter\n" + 2821 " │ ├─ (lineitem.l_partkey = part.p_partkey)\n" + 2822 " │ └─ Table\n" + 2823 " │ ├─ name: lineitem\n" + 2824 " │ └─ columns: [l_partkey l_quantity]\n" + 2825 " │ )\n" + 2826 " └─ HashJoin\n" + 2827 " ├─ (part.p_partkey = lineitem.l_partkey)\n" + 2828 " ├─ Table\n" + 2829 " │ └─ name: lineitem\n" + 2830 " └─ HashLookup\n" + 2831 " ├─ left-key: (lineitem.l_partkey)\n" + 2832 " ├─ right-key: (part.p_partkey)\n" + 2833 " └─ Filter\n" + 2834 " ├─ ((part.p_brand = 'Brand#23') AND (part.p_container = 'MED BOX'))\n" + 2835 " └─ Table\n" + 2836 " └─ name: part\n" + 2837 "", 2838 }, 2839 { 2840 Query: ` 2841 --Q18 2842 select 2843 c_name, 2844 c_custkey, 2845 o_orderkey, 2846 o_orderdate, 2847 o_totalprice, 2848 sum(l_quantity) 2849 from 2850 customer, 2851 orders, 2852 lineitem 2853 where 2854 o_orderkey in ( 2855 select 2856 l_orderkey 2857 from 2858 lineitem 2859 group by 2860 l_orderkey having 2861 sum(l_quantity) > 300 2862 ) 2863 and c_custkey = o_custkey 2864 and o_orderkey = l_orderkey 2865 group by 2866 c_name, 2867 c_custkey, 2868 o_orderkey, 2869 o_orderdate, 2870 o_totalprice 2871 order by 2872 o_totalprice desc, 2873 o_orderdate;`, 2874 ExpectedPlan: "Project\n" + 2875 " ├─ columns: [customer.c_name:1!null, customer.c_custkey:2!null, orders.o_orderkey:3!null, orders.o_orderdate:4!null, orders.o_totalprice:5!null, sum(lineitem.l_quantity):0!null as sum(l_quantity)]\n" + 2876 " └─ Sort(orders.o_totalprice:5!null DESC nullsFirst, orders.o_orderdate:4!null ASC nullsFirst)\n" + 2877 " └─ GroupBy\n" + 2878 " ├─ select: SUM(lineitem.l_quantity:21!null), customer.c_name:1!null, customer.c_custkey:0!null, orders.o_orderkey:8!null, orders.o_orderdate:12!null, orders.o_totalprice:11!null\n" + 2879 " ├─ group: customer.c_name:1!null, customer.c_custkey:0!null, orders.o_orderkey:8!null, orders.o_orderdate:12!null, orders.o_totalprice:11!null\n" + 2880 " └─ SemiJoin\n" + 2881 " ├─ Eq\n" + 2882 " │ ├─ orders.o_orderkey:8!null\n" + 2883 " │ └─ lineitem_1.l_orderkey:33!null\n" + 2884 " ├─ InnerJoin\n" + 2885 " │ ├─ Eq\n" + 2886 " │ │ ├─ orders.o_orderkey:8!null\n" + 2887 " │ │ └─ lineitem.l_orderkey:17!null\n" + 2888 " │ ├─ InnerJoin\n" + 2889 " │ │ ├─ Eq\n" + 2890 " │ │ │ ├─ customer.c_custkey:0!null\n" + 2891 " │ │ │ └─ orders.o_custkey:9!null\n" + 2892 " │ │ ├─ ProcessTable\n" + 2893 " │ │ │ └─ Table\n" + 2894 " │ │ │ ├─ name: customer\n" + 2895 " │ │ │ └─ columns: [c_custkey c_name c_address c_nationkey c_phone c_acctbal c_mktsegment c_comment]\n" + 2896 " │ │ └─ ProcessTable\n" + 2897 " │ │ └─ Table\n" + 2898 " │ │ ├─ name: orders\n" + 2899 " │ │ └─ columns: [o_orderkey o_custkey o_orderstatus o_totalprice o_orderdate o_orderpriority o_clerk o_shippriority o_comment]\n" + 2900 " │ └─ ProcessTable\n" + 2901 " │ └─ Table\n" + 2902 " │ ├─ name: lineitem\n" + 2903 " │ └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 2904 " └─ Project\n" + 2905 " ├─ columns: [lineitem_1.l_orderkey:1!null]\n" + 2906 " └─ Having\n" + 2907 " ├─ GreaterThan\n" + 2908 " │ ├─ sum(lineitem.l_quantity):0!null\n" + 2909 " │ └─ 300 (smallint)\n" + 2910 " └─ GroupBy\n" + 2911 " ├─ select: SUM(lineitem_1.l_quantity:4!null), lineitem_1.l_orderkey:0!null, lineitem_1.L_QUANTITY:4!null\n" + 2912 " ├─ group: lineitem_1.l_orderkey:0!null\n" + 2913 " └─ TableAlias(lineitem_1)\n" + 2914 " └─ Table\n" + 2915 " ├─ name: lineitem\n" + 2916 " ├─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 2917 " ├─ colSet: (34-49)\n" + 2918 " └─ tableId: 4\n" + 2919 "", 2920 ExpectedEstimates: "Project\n" + 2921 " ├─ columns: [customer.c_name, customer.c_custkey, orders.o_orderkey, orders.o_orderdate, orders.o_totalprice, sum(lineitem.l_quantity) as sum(l_quantity)]\n" + 2922 " └─ Sort(orders.o_totalprice DESC, orders.o_orderdate ASC)\n" + 2923 " └─ GroupBy\n" + 2924 " ├─ SelectedExprs(SUM(lineitem.l_quantity), customer.c_name, customer.c_custkey, orders.o_orderkey, orders.o_orderdate, orders.o_totalprice)\n" + 2925 " ├─ Grouping(customer.c_name, customer.c_custkey, orders.o_orderkey, orders.o_orderdate, orders.o_totalprice)\n" + 2926 " └─ SemiJoin\n" + 2927 " ├─ (orders.o_orderkey = lineitem_1.l_orderkey)\n" + 2928 " ├─ InnerJoin\n" + 2929 " │ ├─ (orders.o_orderkey = lineitem.l_orderkey)\n" + 2930 " │ ├─ InnerJoin\n" + 2931 " │ │ ├─ (customer.c_custkey = orders.o_custkey)\n" + 2932 " │ │ ├─ Table\n" + 2933 " │ │ │ └─ name: customer\n" + 2934 " │ │ └─ Table\n" + 2935 " │ │ └─ name: orders\n" + 2936 " │ └─ Table\n" + 2937 " │ └─ name: lineitem\n" + 2938 " └─ Project\n" + 2939 " ├─ columns: [lineitem_1.l_orderkey]\n" + 2940 " └─ Having((sum(lineitem.l_quantity) > 300))\n" + 2941 " └─ GroupBy\n" + 2942 " ├─ SelectedExprs(SUM(lineitem_1.l_quantity), lineitem_1.l_orderkey, lineitem_1.L_QUANTITY)\n" + 2943 " ├─ Grouping(lineitem_1.l_orderkey)\n" + 2944 " └─ TableAlias(lineitem_1)\n" + 2945 " └─ Table\n" + 2946 " └─ name: lineitem\n" + 2947 "", 2948 ExpectedAnalysis: "Project\n" + 2949 " ├─ columns: [customer.c_name, customer.c_custkey, orders.o_orderkey, orders.o_orderdate, orders.o_totalprice, sum(lineitem.l_quantity) as sum(l_quantity)]\n" + 2950 " └─ Sort(orders.o_totalprice DESC, orders.o_orderdate ASC)\n" + 2951 " └─ GroupBy\n" + 2952 " ├─ SelectedExprs(SUM(lineitem.l_quantity), customer.c_name, customer.c_custkey, orders.o_orderkey, orders.o_orderdate, orders.o_totalprice)\n" + 2953 " ├─ Grouping(customer.c_name, customer.c_custkey, orders.o_orderkey, orders.o_orderdate, orders.o_totalprice)\n" + 2954 " └─ SemiJoin\n" + 2955 " ├─ (orders.o_orderkey = lineitem_1.l_orderkey)\n" + 2956 " ├─ InnerJoin\n" + 2957 " │ ├─ (orders.o_orderkey = lineitem.l_orderkey)\n" + 2958 " │ ├─ InnerJoin\n" + 2959 " │ │ ├─ (customer.c_custkey = orders.o_custkey)\n" + 2960 " │ │ ├─ Table\n" + 2961 " │ │ │ └─ name: customer\n" + 2962 " │ │ └─ Table\n" + 2963 " │ │ └─ name: orders\n" + 2964 " │ └─ Table\n" + 2965 " │ └─ name: lineitem\n" + 2966 " └─ Project\n" + 2967 " ├─ columns: [lineitem_1.l_orderkey]\n" + 2968 " └─ Having((sum(lineitem.l_quantity) > 300))\n" + 2969 " └─ GroupBy\n" + 2970 " ├─ SelectedExprs(SUM(lineitem_1.l_quantity), lineitem_1.l_orderkey, lineitem_1.L_QUANTITY)\n" + 2971 " ├─ Grouping(lineitem_1.l_orderkey)\n" + 2972 " └─ TableAlias(lineitem_1)\n" + 2973 " └─ Table\n" + 2974 " └─ name: lineitem\n" + 2975 "", 2976 }, 2977 { 2978 Query: ` 2979 --Q19 2980 select 2981 sum(l_extendedprice* (1 - l_discount)) as revenue 2982 from 2983 lineitem, 2984 part 2985 where 2986 ( 2987 p_partkey = l_partkey 2988 and p_brand = 'Brand#12' 2989 and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') 2990 and l_quantity >= 1 and l_quantity <= 1 + 10 2991 and p_size between 1 and 5 2992 and l_shipmode in ('AIR', 'AIR REG') 2993 and l_shipinstruct = 'DELIVER IN PERSON' 2994 ) 2995 or 2996 ( 2997 p_partkey = l_partkey 2998 and p_brand = 'Brand#23' 2999 and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') 3000 and l_quantity >= 10 and l_quantity <= 10 + 10 3001 and p_size between 1 and 10 3002 and l_shipmode in ('AIR', 'AIR REG') 3003 and l_shipinstruct = 'DELIVER IN PERSON' 3004 ) 3005 or 3006 ( 3007 p_partkey = l_partkey 3008 and p_brand = 'Brand#34' 3009 and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') 3010 and l_quantity >= 20 and l_quantity <= 20 + 10 3011 and p_size between 1 and 15 3012 and l_shipmode in ('AIR', 'AIR REG') 3013 and l_shipinstruct = 'DELIVER IN PERSON' 3014 );`, 3015 ExpectedPlan: "Project\n" + 3016 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))):0!null as revenue]\n" + 3017 " └─ GroupBy\n" + 3018 " ├─ select: SUM((lineitem.l_extendedprice:2!null * (1 (tinyint) - lineitem.l_discount:3!null)))\n" + 3019 " ├─ group: \n" + 3020 " └─ LookupJoin\n" + 3021 " ├─ Or\n" + 3022 " │ ├─ Or\n" + 3023 " │ │ ├─ AND\n" + 3024 " │ │ │ ├─ AND\n" + 3025 " │ │ │ │ ├─ AND\n" + 3026 " │ │ │ │ │ ├─ AND\n" + 3027 " │ │ │ │ │ │ ├─ AND\n" + 3028 " │ │ │ │ │ │ │ ├─ AND\n" + 3029 " │ │ │ │ │ │ │ │ ├─ AND\n" + 3030 " │ │ │ │ │ │ │ │ │ ├─ Eq\n" + 3031 " │ │ │ │ │ │ │ │ │ │ ├─ part.p_partkey:6!null\n" + 3032 " │ │ │ │ │ │ │ │ │ │ └─ lineitem.l_partkey:0!null\n" + 3033 " │ │ │ │ │ │ │ │ │ └─ Eq\n" + 3034 " │ │ │ │ │ │ │ │ │ ├─ part.p_brand:7!null\n" + 3035 " │ │ │ │ │ │ │ │ │ └─ Brand#12 (longtext)\n" + 3036 " │ │ │ │ │ │ │ │ └─ IN\n" + 3037 " │ │ │ │ │ │ │ │ ├─ left: part.p_container:9!null\n" + 3038 " │ │ │ │ │ │ │ │ └─ right: TUPLE(SM CASE (longtext), SM BOX (longtext), SM PACK (longtext), SM PKG (longtext))\n" + 3039 " │ │ │ │ │ │ │ └─ GreaterThanOrEqual\n" + 3040 " │ │ │ │ │ │ │ ├─ lineitem.l_quantity:1!null\n" + 3041 " │ │ │ │ │ │ │ └─ 1 (tinyint)\n" + 3042 " │ │ │ │ │ │ └─ LessThanOrEqual\n" + 3043 " │ │ │ │ │ │ ├─ lineitem.l_quantity:1!null\n" + 3044 " │ │ │ │ │ │ └─ 11 (bigint)\n" + 3045 " │ │ │ │ │ └─ AND\n" + 3046 " │ │ │ │ │ ├─ GreaterThanOrEqual\n" + 3047 " │ │ │ │ │ │ ├─ part.p_size:8!null\n" + 3048 " │ │ │ │ │ │ └─ 1 (tinyint)\n" + 3049 " │ │ │ │ │ └─ LessThanOrEqual\n" + 3050 " │ │ │ │ │ ├─ part.p_size:8!null\n" + 3051 " │ │ │ │ │ └─ 5 (tinyint)\n" + 3052 " │ │ │ │ └─ IN\n" + 3053 " │ │ │ │ ├─ left: lineitem.l_shipmode:5!null\n" + 3054 " │ │ │ │ └─ right: TUPLE(AIR (longtext), AIR REG (longtext))\n" + 3055 " │ │ │ └─ Eq\n" + 3056 " │ │ │ ├─ lineitem.l_shipinstruct:4!null\n" + 3057 " │ │ │ └─ DELIVER IN PERSON (longtext)\n" + 3058 " │ │ └─ AND\n" + 3059 " │ │ ├─ AND\n" + 3060 " │ │ │ ├─ AND\n" + 3061 " │ │ │ │ ├─ AND\n" + 3062 " │ │ │ │ │ ├─ AND\n" + 3063 " │ │ │ │ │ │ ├─ AND\n" + 3064 " │ │ │ │ │ │ │ ├─ AND\n" + 3065 " │ │ │ │ │ │ │ │ ├─ Eq\n" + 3066 " │ │ │ │ │ │ │ │ │ ├─ part.p_partkey:6!null\n" + 3067 " │ │ │ │ │ │ │ │ │ └─ lineitem.l_partkey:0!null\n" + 3068 " │ │ │ │ │ │ │ │ └─ Eq\n" + 3069 " │ │ │ │ │ │ │ │ ├─ part.p_brand:7!null\n" + 3070 " │ │ │ │ │ │ │ │ └─ Brand#23 (longtext)\n" + 3071 " │ │ │ │ │ │ │ └─ IN\n" + 3072 " │ │ │ │ │ │ │ ├─ left: part.p_container:9!null\n" + 3073 " │ │ │ │ │ │ │ └─ right: TUPLE(MED BAG (longtext), MED BOX (longtext), MED PKG (longtext), MED PACK (longtext))\n" + 3074 " │ │ │ │ │ │ └─ GreaterThanOrEqual\n" + 3075 " │ │ │ │ │ │ ├─ lineitem.l_quantity:1!null\n" + 3076 " │ │ │ │ │ │ └─ 10 (tinyint)\n" + 3077 " │ │ │ │ │ └─ LessThanOrEqual\n" + 3078 " │ │ │ │ │ ├─ lineitem.l_quantity:1!null\n" + 3079 " │ │ │ │ │ └─ 20 (bigint)\n" + 3080 " │ │ │ │ └─ AND\n" + 3081 " │ │ │ │ ├─ GreaterThanOrEqual\n" + 3082 " │ │ │ │ │ ├─ part.p_size:8!null\n" + 3083 " │ │ │ │ │ └─ 1 (tinyint)\n" + 3084 " │ │ │ │ └─ LessThanOrEqual\n" + 3085 " │ │ │ │ ├─ part.p_size:8!null\n" + 3086 " │ │ │ │ └─ 10 (tinyint)\n" + 3087 " │ │ │ └─ IN\n" + 3088 " │ │ │ ├─ left: lineitem.l_shipmode:5!null\n" + 3089 " │ │ │ └─ right: TUPLE(AIR (longtext), AIR REG (longtext))\n" + 3090 " │ │ └─ Eq\n" + 3091 " │ │ ├─ lineitem.l_shipinstruct:4!null\n" + 3092 " │ │ └─ DELIVER IN PERSON (longtext)\n" + 3093 " │ └─ AND\n" + 3094 " │ ├─ AND\n" + 3095 " │ │ ├─ AND\n" + 3096 " │ │ │ ├─ AND\n" + 3097 " │ │ │ │ ├─ AND\n" + 3098 " │ │ │ │ │ ├─ AND\n" + 3099 " │ │ │ │ │ │ ├─ AND\n" + 3100 " │ │ │ │ │ │ │ ├─ Eq\n" + 3101 " │ │ │ │ │ │ │ │ ├─ part.p_partkey:6!null\n" + 3102 " │ │ │ │ │ │ │ │ └─ lineitem.l_partkey:0!null\n" + 3103 " │ │ │ │ │ │ │ └─ Eq\n" + 3104 " │ │ │ │ │ │ │ ├─ part.p_brand:7!null\n" + 3105 " │ │ │ │ │ │ │ └─ Brand#34 (longtext)\n" + 3106 " │ │ │ │ │ │ └─ IN\n" + 3107 " │ │ │ │ │ │ ├─ left: part.p_container:9!null\n" + 3108 " │ │ │ │ │ │ └─ right: TUPLE(LG CASE (longtext), LG BOX (longtext), LG PACK (longtext), LG PKG (longtext))\n" + 3109 " │ │ │ │ │ └─ GreaterThanOrEqual\n" + 3110 " │ │ │ │ │ ├─ lineitem.l_quantity:1!null\n" + 3111 " │ │ │ │ │ └─ 20 (tinyint)\n" + 3112 " │ │ │ │ └─ LessThanOrEqual\n" + 3113 " │ │ │ │ ├─ lineitem.l_quantity:1!null\n" + 3114 " │ │ │ │ └─ 30 (bigint)\n" + 3115 " │ │ │ └─ AND\n" + 3116 " │ │ │ ├─ GreaterThanOrEqual\n" + 3117 " │ │ │ │ ├─ part.p_size:8!null\n" + 3118 " │ │ │ │ └─ 1 (tinyint)\n" + 3119 " │ │ │ └─ LessThanOrEqual\n" + 3120 " │ │ │ ├─ part.p_size:8!null\n" + 3121 " │ │ │ └─ 15 (tinyint)\n" + 3122 " │ │ └─ IN\n" + 3123 " │ │ ├─ left: lineitem.l_shipmode:5!null\n" + 3124 " │ │ └─ right: TUPLE(AIR (longtext), AIR REG (longtext))\n" + 3125 " │ └─ Eq\n" + 3126 " │ ├─ lineitem.l_shipinstruct:4!null\n" + 3127 " │ └─ DELIVER IN PERSON (longtext)\n" + 3128 " ├─ ProcessTable\n" + 3129 " │ └─ Table\n" + 3130 " │ ├─ name: lineitem\n" + 3131 " │ └─ columns: [l_partkey l_quantity l_extendedprice l_discount l_shipinstruct l_shipmode]\n" + 3132 " └─ Concat\n" + 3133 " ├─ IndexedTableAccess(part)\n" + 3134 " │ ├─ index: [part.P_PARTKEY]\n" + 3135 " │ ├─ keys: [lineitem.l_partkey:0!null]\n" + 3136 " │ ├─ colSet: (17-25)\n" + 3137 " │ ├─ tableId: 2\n" + 3138 " │ └─ Table\n" + 3139 " │ ├─ name: part\n" + 3140 " │ └─ columns: [p_partkey p_brand p_size p_container]\n" + 3141 " └─ Concat\n" + 3142 " ├─ IndexedTableAccess(part)\n" + 3143 " │ ├─ index: [part.P_PARTKEY]\n" + 3144 " │ ├─ keys: [lineitem.l_partkey:0!null]\n" + 3145 " │ ├─ colSet: (17-25)\n" + 3146 " │ ├─ tableId: 2\n" + 3147 " │ └─ Table\n" + 3148 " │ ├─ name: part\n" + 3149 " │ └─ columns: [p_partkey p_brand p_size p_container]\n" + 3150 " └─ IndexedTableAccess(part)\n" + 3151 " ├─ index: [part.P_PARTKEY]\n" + 3152 " ├─ keys: [lineitem.l_partkey:0!null]\n" + 3153 " ├─ colSet: (17-25)\n" + 3154 " ├─ tableId: 2\n" + 3155 " └─ Table\n" + 3156 " ├─ name: part\n" + 3157 " └─ columns: [p_partkey p_brand p_size p_container]\n" + 3158 "", 3159 ExpectedEstimates: "Project\n" + 3160 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 3161 " └─ GroupBy\n" + 3162 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))))\n" + 3163 " ├─ Grouping()\n" + 3164 " └─ LookupJoin\n" + 3165 " ├─ ((((((((((part.p_partkey = lineitem.l_partkey) AND (part.p_brand = 'Brand#12')) AND (part.p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG'))) AND (lineitem.l_quantity >= 1)) AND (lineitem.l_quantity <= 11)) AND ((part.p_size >= 1) AND (part.p_size <= 5))) AND (lineitem.l_shipmode IN ('AIR', 'AIR REG'))) AND (lineitem.l_shipinstruct = 'DELIVER IN PERSON')) OR ((((((((part.p_partkey = lineitem.l_partkey) AND (part.p_brand = 'Brand#23')) AND (part.p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK'))) AND (lineitem.l_quantity >= 10)) AND (lineitem.l_quantity <= 20)) AND ((part.p_size >= 1) AND (part.p_size <= 10))) AND (lineitem.l_shipmode IN ('AIR', 'AIR REG'))) AND (lineitem.l_shipinstruct = 'DELIVER IN PERSON'))) OR ((((((((part.p_partkey = lineitem.l_partkey) AND (part.p_brand = 'Brand#34')) AND (part.p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG'))) AND (lineitem.l_quantity >= 20)) AND (lineitem.l_quantity <= 30)) AND ((part.p_size >= 1) AND (part.p_size <= 15))) AND (lineitem.l_shipmode IN ('AIR', 'AIR REG'))) AND (lineitem.l_shipinstruct = 'DELIVER IN PERSON')))\n" + 3166 " ├─ Table\n" + 3167 " │ ├─ name: lineitem\n" + 3168 " │ └─ columns: [l_partkey l_quantity l_extendedprice l_discount l_shipinstruct l_shipmode]\n" + 3169 " └─ Concat\n" + 3170 " ├─ IndexedTableAccess(part)\n" + 3171 " │ ├─ index: [part.P_PARTKEY]\n" + 3172 " │ ├─ columns: [p_partkey p_brand p_size p_container]\n" + 3173 " │ └─ keys: lineitem.l_partkey\n" + 3174 " └─ Concat\n" + 3175 " ├─ IndexedTableAccess(part)\n" + 3176 " │ ├─ index: [part.P_PARTKEY]\n" + 3177 " │ ├─ columns: [p_partkey p_brand p_size p_container]\n" + 3178 " │ └─ keys: lineitem.l_partkey\n" + 3179 " └─ IndexedTableAccess(part)\n" + 3180 " ├─ index: [part.P_PARTKEY]\n" + 3181 " ├─ columns: [p_partkey p_brand p_size p_container]\n" + 3182 " └─ keys: lineitem.l_partkey\n" + 3183 "", 3184 ExpectedAnalysis: "Project\n" + 3185 " ├─ columns: [sum((lineitem.l_extendedprice * (1 - lineitem.l_discount))) as revenue]\n" + 3186 " └─ GroupBy\n" + 3187 " ├─ SelectedExprs(SUM((lineitem.l_extendedprice * (1 - lineitem.l_discount))))\n" + 3188 " ├─ Grouping()\n" + 3189 " └─ LookupJoin\n" + 3190 " ├─ ((((((((((part.p_partkey = lineitem.l_partkey) AND (part.p_brand = 'Brand#12')) AND (part.p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG'))) AND (lineitem.l_quantity >= 1)) AND (lineitem.l_quantity <= 11)) AND ((part.p_size >= 1) AND (part.p_size <= 5))) AND (lineitem.l_shipmode IN ('AIR', 'AIR REG'))) AND (lineitem.l_shipinstruct = 'DELIVER IN PERSON')) OR ((((((((part.p_partkey = lineitem.l_partkey) AND (part.p_brand = 'Brand#23')) AND (part.p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK'))) AND (lineitem.l_quantity >= 10)) AND (lineitem.l_quantity <= 20)) AND ((part.p_size >= 1) AND (part.p_size <= 10))) AND (lineitem.l_shipmode IN ('AIR', 'AIR REG'))) AND (lineitem.l_shipinstruct = 'DELIVER IN PERSON'))) OR ((((((((part.p_partkey = lineitem.l_partkey) AND (part.p_brand = 'Brand#34')) AND (part.p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG'))) AND (lineitem.l_quantity >= 20)) AND (lineitem.l_quantity <= 30)) AND ((part.p_size >= 1) AND (part.p_size <= 15))) AND (lineitem.l_shipmode IN ('AIR', 'AIR REG'))) AND (lineitem.l_shipinstruct = 'DELIVER IN PERSON')))\n" + 3191 " ├─ Table\n" + 3192 " │ ├─ name: lineitem\n" + 3193 " │ └─ columns: [l_partkey l_quantity l_extendedprice l_discount l_shipinstruct l_shipmode]\n" + 3194 " └─ Concat\n" + 3195 " ├─ IndexedTableAccess(part)\n" + 3196 " │ ├─ index: [part.P_PARTKEY]\n" + 3197 " │ ├─ columns: [p_partkey p_brand p_size p_container]\n" + 3198 " │ └─ keys: lineitem.l_partkey\n" + 3199 " └─ Concat\n" + 3200 " ├─ IndexedTableAccess(part)\n" + 3201 " │ ├─ index: [part.P_PARTKEY]\n" + 3202 " │ ├─ columns: [p_partkey p_brand p_size p_container]\n" + 3203 " │ └─ keys: lineitem.l_partkey\n" + 3204 " └─ IndexedTableAccess(part)\n" + 3205 " ├─ index: [part.P_PARTKEY]\n" + 3206 " ├─ columns: [p_partkey p_brand p_size p_container]\n" + 3207 " └─ keys: lineitem.l_partkey\n" + 3208 "", 3209 }, 3210 { 3211 Query: ` 3212 --Q20 3213 select 3214 s_name, 3215 s_address 3216 from 3217 supplier, 3218 nation 3219 where 3220 s_suppkey in ( 3221 select 3222 ps_suppkey 3223 from 3224 partsupp 3225 where 3226 ps_partkey in ( 3227 select 3228 p_partkey 3229 from 3230 part 3231 where 3232 p_name like 'forest%' 3233 ) 3234 and ps_availqty > ( 3235 select 3236 0.5 * sum(l_quantity) 3237 from 3238 lineitem 3239 where 3240 l_partkey = ps_partkey 3241 and l_suppkey = ps_suppkey 3242 and l_shipdate >= '1994-01-01' 3243 and l_shipdate < '1994-01-01' + interval '1' year 3244 ) 3245 ) 3246 and s_nationkey = n_nationkey 3247 and n_name = 'CANADA' 3248 order by 3249 s_name;`, 3250 ExpectedPlan: "Project\n" + 3251 " ├─ columns: [supplier.s_name:2!null, supplier.s_address:3!null]\n" + 3252 " └─ Sort(supplier.s_name:2!null ASC nullsFirst)\n" + 3253 " └─ LookupJoin\n" + 3254 " ├─ LookupJoin\n" + 3255 " │ ├─ Eq\n" + 3256 " │ │ ├─ supplier.s_suppkey:1!null\n" + 3257 " │ │ └─ partsupp.ps_suppkey:0!null\n" + 3258 " │ ├─ Distinct\n" + 3259 " │ │ └─ Project\n" + 3260 " │ │ ├─ columns: [partsupp.ps_suppkey:1!null]\n" + 3261 " │ │ └─ Filter\n" + 3262 " │ │ ├─ GreaterThan\n" + 3263 " │ │ │ ├─ partsupp.ps_availqty:2!null\n" + 3264 " │ │ │ └─ Subquery\n" + 3265 " │ │ │ ├─ cacheable: false\n" + 3266 " │ │ │ ├─ alias-string: select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= '1994-01-01' and l_shipdate < '1994-01-01' + interval '1' year\n" + 3267 " │ │ │ └─ Project\n" + 3268 " │ │ │ ├─ columns: [(0.5 (decimal(2,1)) * sum(lineitem.l_quantity):5!null) as 0.5 * sum(l_quantity)]\n" + 3269 " │ │ │ └─ GroupBy\n" + 3270 " │ │ │ ├─ select: SUM(lineitem.l_quantity:7!null)\n" + 3271 " │ │ │ ├─ group: \n" + 3272 " │ │ │ └─ Filter\n" + 3273 " │ │ │ ├─ AND\n" + 3274 " │ │ │ │ ├─ AND\n" + 3275 " │ │ │ │ │ ├─ AND\n" + 3276 " │ │ │ │ │ │ ├─ Eq\n" + 3277 " │ │ │ │ │ │ │ ├─ lineitem.l_partkey:5!null\n" + 3278 " │ │ │ │ │ │ │ └─ partsupp.ps_partkey:0!null\n" + 3279 " │ │ │ │ │ │ └─ Eq\n" + 3280 " │ │ │ │ │ │ ├─ lineitem.l_suppkey:6!null\n" + 3281 " │ │ │ │ │ │ └─ partsupp.ps_suppkey:1!null\n" + 3282 " │ │ │ │ │ └─ GreaterThanOrEqual\n" + 3283 " │ │ │ │ │ ├─ lineitem.l_shipdate:8!null\n" + 3284 " │ │ │ │ │ └─ 1994-01-01 (longtext)\n" + 3285 " │ │ │ │ └─ LessThan\n" + 3286 " │ │ │ │ ├─ lineitem.l_shipdate:8!null\n" + 3287 " │ │ │ │ └─ 1995-01-01 00:00:00 +0000 UTC (datetime)\n" + 3288 " │ │ │ └─ Table\n" + 3289 " │ │ │ ├─ name: lineitem\n" + 3290 " │ │ │ ├─ columns: [l_partkey l_suppkey l_quantity l_shipdate]\n" + 3291 " │ │ │ ├─ colSet: (26-41)\n" + 3292 " │ │ │ └─ tableId: 5\n" + 3293 " │ │ └─ Project\n" + 3294 " │ │ ├─ columns: [partsupp.PS_PARTKEY:0!null, partsupp.PS_SUPPKEY:1!null, partsupp.PS_AVAILQTY:2!null, partsupp.PS_SUPPLYCOST:3!null, partsupp.PS_COMMENT:4!null]\n" + 3295 " │ │ └─ MergeJoin\n" + 3296 " │ │ ├─ cmp: Eq\n" + 3297 " │ │ │ ├─ partsupp.ps_partkey:0!null\n" + 3298 " │ │ │ └─ part.p_partkey:5!null\n" + 3299 " │ │ ├─ IndexedTableAccess(partsupp)\n" + 3300 " │ │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 3301 " │ │ │ ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 3302 " │ │ │ ├─ colSet: (12-16)\n" + 3303 " │ │ │ ├─ tableId: 3\n" + 3304 " │ │ │ └─ Table\n" + 3305 " │ │ │ ├─ name: partsupp\n" + 3306 " │ │ │ └─ columns: [ps_partkey ps_suppkey ps_availqty ps_supplycost ps_comment]\n" + 3307 " │ │ └─ Project\n" + 3308 " │ │ ├─ columns: [part.p_partkey:0!null]\n" + 3309 " │ │ └─ Filter\n" + 3310 " │ │ ├─ AND\n" + 3311 " │ │ │ ├─ AND\n" + 3312 " │ │ │ │ ├─ AND\n" + 3313 " │ │ │ │ │ ├─ AND\n" + 3314 " │ │ │ │ │ │ ├─ AND\n" + 3315 " │ │ │ │ │ │ │ ├─ AND\n" + 3316 " │ │ │ │ │ │ │ │ ├─ AND\n" + 3317 " │ │ │ │ │ │ │ │ │ ├─ GreaterThanOrEqual\n" + 3318 " │ │ │ │ │ │ │ │ │ │ ├─ part.p_name:1!null\n" + 3319 " │ │ │ │ │ │ │ │ │ │ └─ forest (longtext)\n" + 3320 " │ │ │ │ │ │ │ │ │ └─ LessThanOrEqual\n" + 3321 " │ │ │ │ │ │ │ │ │ ├─ part.p_name:1!null\n" + 3322 " │ │ │ │ │ │ │ │ │ └─ forestÿ (longtext)\n" + 3323 " │ │ │ │ │ │ │ │ └─ GreaterThanOrEqual\n" + 3324 " │ │ │ │ │ │ │ │ ├─ part.p_name:1!null\n" + 3325 " │ │ │ │ │ │ │ │ └─ forest (longtext)\n" + 3326 " │ │ │ │ │ │ │ └─ LessThanOrEqual\n" + 3327 " │ │ │ │ │ │ │ ├─ part.p_name:1!null\n" + 3328 " │ │ │ │ │ │ │ └─ forestÿ (longtext)\n" + 3329 " │ │ │ │ │ │ └─ GreaterThanOrEqual\n" + 3330 " │ │ │ │ │ │ ├─ part.p_name:1!null\n" + 3331 " │ │ │ │ │ │ └─ forest (longtext)\n" + 3332 " │ │ │ │ │ └─ LessThanOrEqual\n" + 3333 " │ │ │ │ │ ├─ part.p_name:1!null\n" + 3334 " │ │ │ │ │ └─ forestÿ (longtext)\n" + 3335 " │ │ │ │ └─ GreaterThanOrEqual\n" + 3336 " │ │ │ │ ├─ part.p_name:1!null\n" + 3337 " │ │ │ │ └─ forest (longtext)\n" + 3338 " │ │ │ └─ LessThanOrEqual\n" + 3339 " │ │ │ ├─ part.p_name:1!null\n" + 3340 " │ │ │ └─ forestÿ (longtext)\n" + 3341 " │ │ └─ IndexedTableAccess(part)\n" + 3342 " │ │ ├─ index: [part.P_PARTKEY]\n" + 3343 " │ │ ├─ static: [{[NULL, ∞)}]\n" + 3344 " │ │ ├─ colSet: (17-25)\n" + 3345 " │ │ ├─ tableId: 4\n" + 3346 " │ │ └─ Table\n" + 3347 " │ │ ├─ name: part\n" + 3348 " │ │ └─ columns: [p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment]\n" + 3349 " │ └─ IndexedTableAccess(supplier)\n" + 3350 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 3351 " │ ├─ keys: [partsupp.ps_suppkey:0!null]\n" + 3352 " │ ├─ colSet: (1-7)\n" + 3353 " │ ├─ tableId: 1\n" + 3354 " │ └─ Table\n" + 3355 " │ ├─ name: supplier\n" + 3356 " │ └─ columns: [s_suppkey s_name s_address s_nationkey s_phone s_acctbal s_comment]\n" + 3357 " └─ Filter\n" + 3358 " ├─ Eq\n" + 3359 " │ ├─ nation.n_name:1!null\n" + 3360 " │ └─ CANADA (longtext)\n" + 3361 " └─ IndexedTableAccess(nation)\n" + 3362 " ├─ index: [nation.N_NATIONKEY]\n" + 3363 " ├─ keys: [supplier.s_nationkey:4!null]\n" + 3364 " ├─ colSet: (8-11)\n" + 3365 " ├─ tableId: 2\n" + 3366 " └─ Table\n" + 3367 " ├─ name: nation\n" + 3368 " └─ columns: [n_nationkey n_name n_regionkey n_comment]\n" + 3369 "", 3370 ExpectedEstimates: "Project\n" + 3371 " ├─ columns: [supplier.s_name, supplier.s_address]\n" + 3372 " └─ Sort(supplier.s_name ASC)\n" + 3373 " └─ LookupJoin\n" + 3374 " ├─ LookupJoin\n" + 3375 " │ ├─ (supplier.s_suppkey = partsupp.ps_suppkey)\n" + 3376 " │ ├─ Distinct\n" + 3377 " │ │ └─ Project\n" + 3378 " │ │ ├─ columns: [partsupp.ps_suppkey]\n" + 3379 " │ │ └─ Filter\n" + 3380 " │ │ ├─ (partsupp.ps_availqty > Subquery\n" + 3381 " │ │ │ ├─ cacheable: false\n" + 3382 " │ │ │ └─ Project\n" + 3383 " │ │ │ ├─ columns: [(0.5 * sum(lineitem.l_quantity)) as 0.5 * sum(l_quantity)]\n" + 3384 " │ │ │ └─ GroupBy\n" + 3385 " │ │ │ ├─ SelectedExprs(SUM(lineitem.l_quantity))\n" + 3386 " │ │ │ ├─ Grouping()\n" + 3387 " │ │ │ └─ Filter\n" + 3388 " │ │ │ ├─ ((((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey)) AND (lineitem.l_shipdate >= '1994-01-01')) AND (lineitem.l_shipdate < 1995-01-01 00:00:00 +0000 UTC))\n" + 3389 " │ │ │ └─ Table\n" + 3390 " │ │ │ ├─ name: lineitem\n" + 3391 " │ │ │ └─ columns: [l_partkey l_suppkey l_quantity l_shipdate]\n" + 3392 " │ │ │ )\n" + 3393 " │ │ └─ Project\n" + 3394 " │ │ ├─ columns: [partsupp.PS_PARTKEY, partsupp.PS_SUPPKEY, partsupp.PS_AVAILQTY, partsupp.PS_SUPPLYCOST, partsupp.PS_COMMENT]\n" + 3395 " │ │ └─ MergeJoin\n" + 3396 " │ │ ├─ cmp: (partsupp.ps_partkey = part.p_partkey)\n" + 3397 " │ │ ├─ IndexedTableAccess(partsupp)\n" + 3398 " │ │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 3399 " │ │ │ └─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 3400 " │ │ └─ Project\n" + 3401 " │ │ ├─ columns: [part.p_partkey]\n" + 3402 " │ │ └─ Filter\n" + 3403 " │ │ ├─ ((((((((part.p_name >= 'forest') AND (part.p_name <= 'forestÿ')) AND (part.p_name >= 'forest')) AND (part.p_name <= 'forestÿ')) AND (part.p_name >= 'forest')) AND (part.p_name <= 'forestÿ')) AND (part.p_name >= 'forest')) AND (part.p_name <= 'forestÿ'))\n" + 3404 " │ │ └─ IndexedTableAccess(part)\n" + 3405 " │ │ ├─ index: [part.P_PARTKEY]\n" + 3406 " │ │ └─ filters: [{[NULL, ∞)}]\n" + 3407 " │ └─ IndexedTableAccess(supplier)\n" + 3408 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 3409 " │ └─ keys: partsupp.ps_suppkey\n" + 3410 " └─ Filter\n" + 3411 " ├─ (nation.n_name = 'CANADA')\n" + 3412 " └─ IndexedTableAccess(nation)\n" + 3413 " ├─ index: [nation.N_NATIONKEY]\n" + 3414 " └─ keys: supplier.s_nationkey\n" + 3415 "", 3416 ExpectedAnalysis: "Project\n" + 3417 " ├─ columns: [supplier.s_name, supplier.s_address]\n" + 3418 " └─ Sort(supplier.s_name ASC)\n" + 3419 " └─ LookupJoin\n" + 3420 " ├─ LookupJoin\n" + 3421 " │ ├─ (supplier.s_suppkey = partsupp.ps_suppkey)\n" + 3422 " │ ├─ Distinct\n" + 3423 " │ │ └─ Project\n" + 3424 " │ │ ├─ columns: [partsupp.ps_suppkey]\n" + 3425 " │ │ └─ Filter\n" + 3426 " │ │ ├─ (partsupp.ps_availqty > Subquery\n" + 3427 " │ │ │ ├─ cacheable: false\n" + 3428 " │ │ │ └─ Project\n" + 3429 " │ │ │ ├─ columns: [(0.5 * sum(lineitem.l_quantity)) as 0.5 * sum(l_quantity)]\n" + 3430 " │ │ │ └─ GroupBy\n" + 3431 " │ │ │ ├─ SelectedExprs(SUM(lineitem.l_quantity))\n" + 3432 " │ │ │ ├─ Grouping()\n" + 3433 " │ │ │ └─ Filter\n" + 3434 " │ │ │ ├─ ((((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey)) AND (lineitem.l_shipdate >= '1994-01-01')) AND (lineitem.l_shipdate < 1995-01-01 00:00:00 +0000 UTC))\n" + 3435 " │ │ │ └─ Table\n" + 3436 " │ │ │ ├─ name: lineitem\n" + 3437 " │ │ │ └─ columns: [l_partkey l_suppkey l_quantity l_shipdate]\n" + 3438 " │ │ │ )\n" + 3439 " │ │ └─ Project\n" + 3440 " │ │ ├─ columns: [partsupp.PS_PARTKEY, partsupp.PS_SUPPKEY, partsupp.PS_AVAILQTY, partsupp.PS_SUPPLYCOST, partsupp.PS_COMMENT]\n" + 3441 " │ │ └─ MergeJoin\n" + 3442 " │ │ ├─ cmp: (partsupp.ps_partkey = part.p_partkey)\n" + 3443 " │ │ ├─ IndexedTableAccess(partsupp)\n" + 3444 " │ │ │ ├─ index: [partsupp.PS_PARTKEY,partsupp.PS_SUPPKEY]\n" + 3445 " │ │ │ └─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 3446 " │ │ └─ Project\n" + 3447 " │ │ ├─ columns: [part.p_partkey]\n" + 3448 " │ │ └─ Filter\n" + 3449 " │ │ ├─ ((((((((part.p_name >= 'forest') AND (part.p_name <= 'forestÿ')) AND (part.p_name >= 'forest')) AND (part.p_name <= 'forestÿ')) AND (part.p_name >= 'forest')) AND (part.p_name <= 'forestÿ')) AND (part.p_name >= 'forest')) AND (part.p_name <= 'forestÿ'))\n" + 3450 " │ │ └─ IndexedTableAccess(part)\n" + 3451 " │ │ ├─ index: [part.P_PARTKEY]\n" + 3452 " │ │ └─ filters: [{[NULL, ∞)}]\n" + 3453 " │ └─ IndexedTableAccess(supplier)\n" + 3454 " │ ├─ index: [supplier.S_SUPPKEY]\n" + 3455 " │ └─ keys: partsupp.ps_suppkey\n" + 3456 " └─ Filter\n" + 3457 " ├─ (nation.n_name = 'CANADA')\n" + 3458 " └─ IndexedTableAccess(nation)\n" + 3459 " ├─ index: [nation.N_NATIONKEY]\n" + 3460 " └─ keys: supplier.s_nationkey\n" + 3461 "", 3462 }, 3463 { 3464 Query: ` 3465 --Q21 3466 select 3467 s_name, 3468 count(*) as numwait 3469 from 3470 supplier, 3471 lineitem l1, 3472 orders, 3473 nation 3474 where 3475 s_suppkey = l1.l_suppkey 3476 and o_orderkey = l1.l_orderkey 3477 and o_orderstatus = 'F' 3478 and l1.l_receiptdate > l1.l_commitdate 3479 and exists ( 3480 select 3481 * 3482 from 3483 lineitem l2 3484 where 3485 l2.l_orderkey = l1.l_orderkey 3486 and l2.l_suppkey <> l1.l_suppkey 3487 ) 3488 and not exists ( 3489 select 3490 * 3491 from 3492 lineitem l3 3493 where 3494 l3.l_orderkey = l1.l_orderkey 3495 and l3.l_suppkey <> l1.l_suppkey 3496 and l3.l_receiptdate > l3.l_commitdate 3497 ) 3498 and s_nationkey = n_nationkey 3499 and n_name = 'SAUDI ARABIA' 3500 group by 3501 s_name 3502 order by 3503 numwait desc, 3504 s_name;`, 3505 ExpectedPlan: "Project\n" + 3506 " ├─ columns: [supplier.s_name:1!null, count(1):0!null as numwait]\n" + 3507 " └─ Sort(count(1):0!null as numwait DESC nullsFirst, supplier.s_name:1!null ASC nullsFirst)\n" + 3508 " └─ Project\n" + 3509 " ├─ columns: [count(1):0!null, supplier.s_name:1!null, count(1):0!null as numwait]\n" + 3510 " └─ GroupBy\n" + 3511 " ├─ select: COUNT(1 (bigint)), supplier.s_name:26!null\n" + 3512 " ├─ group: supplier.s_name:26!null\n" + 3513 " └─ SemiLookupJoin\n" + 3514 " ├─ NOT\n" + 3515 " │ └─ Eq\n" + 3516 " │ ├─ l2.l_suppkey:38!null\n" + 3517 " │ └─ l1.l_suppkey:2!null\n" + 3518 " ├─ AntiJoin\n" + 3519 " │ ├─ AND\n" + 3520 " │ │ ├─ Eq\n" + 3521 " │ │ │ ├─ l3.l_orderkey:36!null\n" + 3522 " │ │ │ └─ l1.l_orderkey:0!null\n" + 3523 " │ │ └─ NOT\n" + 3524 " │ │ └─ Eq\n" + 3525 " │ │ ├─ l3.l_suppkey:38!null\n" + 3526 " │ │ └─ l1.l_suppkey:2!null\n" + 3527 " │ ├─ LookupJoin\n" + 3528 " │ │ ├─ LookupJoin\n" + 3529 " │ │ │ ├─ MergeJoin\n" + 3530 " │ │ │ │ ├─ cmp: Eq\n" + 3531 " │ │ │ │ │ ├─ l1.l_orderkey:0!null\n" + 3532 " │ │ │ │ │ └─ orders.o_orderkey:16!null\n" + 3533 " │ │ │ │ ├─ Filter\n" + 3534 " │ │ │ │ │ ├─ GreaterThan\n" + 3535 " │ │ │ │ │ │ ├─ l1.l_receiptdate:12!null\n" + 3536 " │ │ │ │ │ │ └─ l1.l_commitdate:11!null\n" + 3537 " │ │ │ │ │ └─ TableAlias(l1)\n" + 3538 " │ │ │ │ │ └─ IndexedTableAccess(lineitem)\n" + 3539 " │ │ │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 3540 " │ │ │ │ │ ├─ static: [{[NULL, ∞), [NULL, ∞)}]\n" + 3541 " │ │ │ │ │ ├─ colSet: (8-23)\n" + 3542 " │ │ │ │ │ ├─ tableId: 2\n" + 3543 " │ │ │ │ │ └─ Table\n" + 3544 " │ │ │ │ │ ├─ name: lineitem\n" + 3545 " │ │ │ │ │ └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 3546 " │ │ │ │ └─ Filter\n" + 3547 " │ │ │ │ ├─ Eq\n" + 3548 " │ │ │ │ │ ├─ orders.o_orderstatus:2!null\n" + 3549 " │ │ │ │ │ └─ F (longtext)\n" + 3550 " │ │ │ │ └─ IndexedTableAccess(orders)\n" + 3551 " │ │ │ │ ├─ index: [orders.O_ORDERKEY]\n" + 3552 " │ │ │ │ ├─ static: [{[NULL, ∞)}]\n" + 3553 " │ │ │ │ ├─ colSet: (24-32)\n" + 3554 " │ │ │ │ ├─ tableId: 3\n" + 3555 " │ │ │ │ └─ Table\n" + 3556 " │ │ │ │ ├─ name: orders\n" + 3557 " │ │ │ │ └─ columns: [o_orderkey o_custkey o_orderstatus o_totalprice o_orderdate o_orderpriority o_clerk o_shippriority o_comment]\n" + 3558 " │ │ │ └─ IndexedTableAccess(supplier)\n" + 3559 " │ │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 3560 " │ │ │ ├─ keys: [l1.l_suppkey:2!null]\n" + 3561 " │ │ │ ├─ colSet: (1-7)\n" + 3562 " │ │ │ ├─ tableId: 1\n" + 3563 " │ │ │ └─ Table\n" + 3564 " │ │ │ ├─ name: supplier\n" + 3565 " │ │ │ └─ columns: [s_suppkey s_name s_address s_nationkey s_phone s_acctbal s_comment]\n" + 3566 " │ │ └─ Filter\n" + 3567 " │ │ ├─ Eq\n" + 3568 " │ │ │ ├─ nation.n_name:1!null\n" + 3569 " │ │ │ └─ SAUDI ARABIA (longtext)\n" + 3570 " │ │ └─ IndexedTableAccess(nation)\n" + 3571 " │ │ ├─ index: [nation.N_NATIONKEY]\n" + 3572 " │ │ ├─ keys: [supplier.s_nationkey:28!null]\n" + 3573 " │ │ ├─ colSet: (33-36)\n" + 3574 " │ │ ├─ tableId: 4\n" + 3575 " │ │ └─ Table\n" + 3576 " │ │ ├─ name: nation\n" + 3577 " │ │ └─ columns: [n_nationkey n_name n_regionkey n_comment]\n" + 3578 " │ └─ Filter\n" + 3579 " │ ├─ GreaterThan\n" + 3580 " │ │ ├─ l3.l_receiptdate:12!null\n" + 3581 " │ │ └─ l3.l_commitdate:11!null\n" + 3582 " │ └─ TableAlias(l3)\n" + 3583 " │ └─ ProcessTable\n" + 3584 " │ └─ Table\n" + 3585 " │ ├─ name: lineitem\n" + 3586 " │ └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 3587 " └─ TableAlias(l2)\n" + 3588 " └─ IndexedTableAccess(lineitem)\n" + 3589 " ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 3590 " ├─ keys: [l1.l_orderkey:0!null]\n" + 3591 " ├─ colSet: (37-52)\n" + 3592 " ├─ tableId: 5\n" + 3593 " └─ Table\n" + 3594 " ├─ name: lineitem\n" + 3595 " └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 3596 "", 3597 ExpectedEstimates: "Project\n" + 3598 " ├─ columns: [supplier.s_name, count(1) as numwait]\n" + 3599 " └─ Sort(count(1) as numwait DESC, supplier.s_name ASC)\n" + 3600 " └─ Project\n" + 3601 " ├─ columns: [count(1), supplier.s_name, count(1) as numwait]\n" + 3602 " └─ GroupBy\n" + 3603 " ├─ SelectedExprs(COUNT(1), supplier.s_name)\n" + 3604 " ├─ Grouping(supplier.s_name)\n" + 3605 " └─ SemiLookupJoin\n" + 3606 " ├─ (NOT((l2.l_suppkey = l1.l_suppkey)))\n" + 3607 " ├─ AntiJoin\n" + 3608 " │ ├─ ((l3.l_orderkey = l1.l_orderkey) AND (NOT((l3.l_suppkey = l1.l_suppkey))))\n" + 3609 " │ ├─ LookupJoin\n" + 3610 " │ │ ├─ LookupJoin\n" + 3611 " │ │ │ ├─ MergeJoin\n" + 3612 " │ │ │ │ ├─ cmp: (l1.l_orderkey = orders.o_orderkey)\n" + 3613 " │ │ │ │ ├─ Filter\n" + 3614 " │ │ │ │ │ ├─ (l1.l_receiptdate > l1.l_commitdate)\n" + 3615 " │ │ │ │ │ └─ TableAlias(l1)\n" + 3616 " │ │ │ │ │ └─ IndexedTableAccess(lineitem)\n" + 3617 " │ │ │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 3618 " │ │ │ │ │ └─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 3619 " │ │ │ │ └─ Filter\n" + 3620 " │ │ │ │ ├─ (orders.o_orderstatus = 'F')\n" + 3621 " │ │ │ │ └─ IndexedTableAccess(orders)\n" + 3622 " │ │ │ │ ├─ index: [orders.O_ORDERKEY]\n" + 3623 " │ │ │ │ └─ filters: [{[NULL, ∞)}]\n" + 3624 " │ │ │ └─ IndexedTableAccess(supplier)\n" + 3625 " │ │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 3626 " │ │ │ └─ keys: l1.l_suppkey\n" + 3627 " │ │ └─ Filter\n" + 3628 " │ │ ├─ (nation.n_name = 'SAUDI ARABIA')\n" + 3629 " │ │ └─ IndexedTableAccess(nation)\n" + 3630 " │ │ ├─ index: [nation.N_NATIONKEY]\n" + 3631 " │ │ └─ keys: supplier.s_nationkey\n" + 3632 " │ └─ Filter\n" + 3633 " │ ├─ (l3.l_receiptdate > l3.l_commitdate)\n" + 3634 " │ └─ TableAlias(l3)\n" + 3635 " │ └─ Table\n" + 3636 " │ ├─ name: lineitem\n" + 3637 " │ └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 3638 " └─ TableAlias(l2)\n" + 3639 " └─ IndexedTableAccess(lineitem)\n" + 3640 " ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 3641 " ├─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 3642 " └─ keys: l1.l_orderkey\n" + 3643 "", 3644 ExpectedAnalysis: "Project\n" + 3645 " ├─ columns: [supplier.s_name, count(1) as numwait]\n" + 3646 " └─ Sort(count(1) as numwait DESC, supplier.s_name ASC)\n" + 3647 " └─ Project\n" + 3648 " ├─ columns: [count(1), supplier.s_name, count(1) as numwait]\n" + 3649 " └─ GroupBy\n" + 3650 " ├─ SelectedExprs(COUNT(1), supplier.s_name)\n" + 3651 " ├─ Grouping(supplier.s_name)\n" + 3652 " └─ SemiLookupJoin\n" + 3653 " ├─ (NOT((l2.l_suppkey = l1.l_suppkey)))\n" + 3654 " ├─ AntiJoin\n" + 3655 " │ ├─ ((l3.l_orderkey = l1.l_orderkey) AND (NOT((l3.l_suppkey = l1.l_suppkey))))\n" + 3656 " │ ├─ LookupJoin\n" + 3657 " │ │ ├─ LookupJoin\n" + 3658 " │ │ │ ├─ MergeJoin\n" + 3659 " │ │ │ │ ├─ cmp: (l1.l_orderkey = orders.o_orderkey)\n" + 3660 " │ │ │ │ ├─ Filter\n" + 3661 " │ │ │ │ │ ├─ (l1.l_receiptdate > l1.l_commitdate)\n" + 3662 " │ │ │ │ │ └─ TableAlias(l1)\n" + 3663 " │ │ │ │ │ └─ IndexedTableAccess(lineitem)\n" + 3664 " │ │ │ │ │ ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 3665 " │ │ │ │ │ └─ filters: [{[NULL, ∞), [NULL, ∞)}]\n" + 3666 " │ │ │ │ └─ Filter\n" + 3667 " │ │ │ │ ├─ (orders.o_orderstatus = 'F')\n" + 3668 " │ │ │ │ └─ IndexedTableAccess(orders)\n" + 3669 " │ │ │ │ ├─ index: [orders.O_ORDERKEY]\n" + 3670 " │ │ │ │ └─ filters: [{[NULL, ∞)}]\n" + 3671 " │ │ │ └─ IndexedTableAccess(supplier)\n" + 3672 " │ │ │ ├─ index: [supplier.S_SUPPKEY]\n" + 3673 " │ │ │ └─ keys: l1.l_suppkey\n" + 3674 " │ │ └─ Filter\n" + 3675 " │ │ ├─ (nation.n_name = 'SAUDI ARABIA')\n" + 3676 " │ │ └─ IndexedTableAccess(nation)\n" + 3677 " │ │ ├─ index: [nation.N_NATIONKEY]\n" + 3678 " │ │ └─ keys: supplier.s_nationkey\n" + 3679 " │ └─ Filter\n" + 3680 " │ ├─ (l3.l_receiptdate > l3.l_commitdate)\n" + 3681 " │ └─ TableAlias(l3)\n" + 3682 " │ └─ Table\n" + 3683 " │ ├─ name: lineitem\n" + 3684 " │ └─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 3685 " └─ TableAlias(l2)\n" + 3686 " └─ IndexedTableAccess(lineitem)\n" + 3687 " ├─ index: [lineitem.L_ORDERKEY,lineitem.L_LINENUMBER]\n" + 3688 " ├─ columns: [l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate l_receiptdate l_shipinstruct l_shipmode l_comment]\n" + 3689 " └─ keys: l1.l_orderkey\n" + 3690 "", 3691 }, 3692 { 3693 Query: ` 3694 --Q22 3695 select 3696 cntrycode, 3697 count(*) as numcust, 3698 sum(c_acctbal) as totacctbal 3699 from 3700 ( 3701 select 3702 substring(c_phone from 1 for 2) as cntrycode, 3703 c_acctbal 3704 from 3705 customer 3706 where 3707 substring(c_phone from 1 for 2) in 3708 ('13', '31', '23', '29', '30', '18', '17') 3709 and c_acctbal > ( 3710 select 3711 avg(c_acctbal) 3712 from 3713 customer 3714 where 3715 c_acctbal > 0.00 3716 and substring(c_phone from 1 for 2) in 3717 ('13', '31', '23', '29', '30', '18', '17') 3718 ) 3719 and not exists ( 3720 select 3721 * 3722 from 3723 orders 3724 where 3725 o_custkey = c_custkey 3726 ) 3727 ) as custsale 3728 group by 3729 cntrycode 3730 order by 3731 cntrycode;`, 3732 ExpectedPlan: "Project\n" + 3733 " ├─ columns: [custsale.cntrycode:2!null, count(1):0!null as numcust, sum(custsale.c_acctbal):1!null as totacctbal]\n" + 3734 " └─ Sort(custsale.cntrycode:2!null ASC nullsFirst)\n" + 3735 " └─ Project\n" + 3736 " ├─ columns: [count(1):0!null, sum(custsale.c_acctbal):1!null, custsale.cntrycode:2!null, count(1):0!null as numcust, sum(custsale.c_acctbal):1!null as totacctbal]\n" + 3737 " └─ GroupBy\n" + 3738 " ├─ select: COUNT(1 (bigint)), SUM(custsale.c_acctbal:1!null), custsale.cntrycode:0!null\n" + 3739 " ├─ group: custsale.cntrycode:0!null\n" + 3740 " └─ SubqueryAlias\n" + 3741 " ├─ name: custsale\n" + 3742 " ├─ outerVisibility: false\n" + 3743 " ├─ isLateral: false\n" + 3744 " ├─ cacheable: true\n" + 3745 " ├─ colSet: (28,29)\n" + 3746 " ├─ tableId: 4\n" + 3747 " └─ Project\n" + 3748 " ├─ columns: [SUBSTRING(customer.c_phone, 1, 2) as cntrycode, customer.c_acctbal:5!null]\n" + 3749 " └─ Filter\n" + 3750 " ├─ GreaterThan\n" + 3751 " │ ├─ customer.c_acctbal:5!null\n" + 3752 " │ └─ Subquery\n" + 3753 " │ ├─ cacheable: true\n" + 3754 " │ ├─ alias-string: select avg(c_acctbal) from customer where c_acctbal > 0.00 and substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')\n" + 3755 " │ └─ Project\n" + 3756 " │ ├─ columns: [avg(customer.c_acctbal):8 as avg(c_acctbal)]\n" + 3757 " │ └─ GroupBy\n" + 3758 " │ ├─ select: AVG(customer.c_acctbal:9!null)\n" + 3759 " │ ├─ group: \n" + 3760 " │ └─ Filter\n" + 3761 " │ ├─ AND\n" + 3762 " │ │ ├─ GreaterThan\n" + 3763 " │ │ │ ├─ customer.c_acctbal:9!null\n" + 3764 " │ │ │ └─ 0 (decimal(3,2))\n" + 3765 " │ │ └─ HashIn\n" + 3766 " │ │ ├─ SUBSTRING(customer.c_phone, 1, 2)\n" + 3767 " │ │ └─ TUPLE(13 (longtext), 31 (longtext), 23 (longtext), 29 (longtext), 30 (longtext), 18 (longtext), 17 (longtext))\n" + 3768 " │ └─ Table\n" + 3769 " │ ├─ name: customer\n" + 3770 " │ ├─ columns: [c_phone c_acctbal]\n" + 3771 " │ ├─ colSet: (9-16)\n" + 3772 " │ └─ tableId: 2\n" + 3773 " └─ Project\n" + 3774 " ├─ columns: [customer.C_CUSTKEY:0!null, customer.C_NAME:1!null, customer.C_ADDRESS:2!null, customer.C_NATIONKEY:3!null, customer.C_PHONE:4!null, customer.C_ACCTBAL:5!null, customer.C_MKTSEGMENT:6!null, customer.C_COMMENT:7!null]\n" + 3775 " └─ Filter\n" + 3776 " ├─ orders.o_custkey:8!null IS NULL\n" + 3777 " └─ LeftOuterHashJoinExcludeNulls\n" + 3778 " ├─ Eq\n" + 3779 " │ ├─ orders.o_custkey:8!null\n" + 3780 " │ └─ customer.c_custkey:0!null\n" + 3781 " ├─ Filter\n" + 3782 " │ ├─ HashIn\n" + 3783 " │ │ ├─ SUBSTRING(customer.c_phone, 1, 2)\n" + 3784 " │ │ └─ TUPLE(13 (longtext), 31 (longtext), 23 (longtext), 29 (longtext), 30 (longtext), 18 (longtext), 17 (longtext))\n" + 3785 " │ └─ Table\n" + 3786 " │ ├─ name: customer\n" + 3787 " │ ├─ columns: [c_custkey c_name c_address c_nationkey c_phone c_acctbal c_mktsegment c_comment]\n" + 3788 " │ ├─ colSet: (1-8)\n" + 3789 " │ └─ tableId: 1\n" + 3790 " └─ HashLookup\n" + 3791 " ├─ left-key: TUPLE(customer.c_custkey:0!null)\n" + 3792 " ├─ right-key: TUPLE(orders.o_custkey:0!null)\n" + 3793 " └─ Project\n" + 3794 " ├─ columns: [orders.o_custkey:1!null]\n" + 3795 " └─ Table\n" + 3796 " ├─ name: orders\n" + 3797 " ├─ columns: [o_orderkey o_custkey o_orderstatus o_totalprice o_orderdate o_orderpriority o_clerk o_shippriority o_comment]\n" + 3798 " ├─ colSet: (18-26)\n" + 3799 " └─ tableId: 3\n" + 3800 "", 3801 ExpectedEstimates: "Project\n" + 3802 " ├─ columns: [custsale.cntrycode, count(1) as numcust, sum(custsale.c_acctbal) as totacctbal]\n" + 3803 " └─ Sort(custsale.cntrycode ASC)\n" + 3804 " └─ Project\n" + 3805 " ├─ columns: [count(1), sum(custsale.c_acctbal), custsale.cntrycode, count(1) as numcust, sum(custsale.c_acctbal) as totacctbal]\n" + 3806 " └─ GroupBy\n" + 3807 " ├─ SelectedExprs(COUNT(1), SUM(custsale.c_acctbal), custsale.cntrycode)\n" + 3808 " ├─ Grouping(custsale.cntrycode)\n" + 3809 " └─ SubqueryAlias\n" + 3810 " ├─ name: custsale\n" + 3811 " ├─ outerVisibility: false\n" + 3812 " ├─ isLateral: false\n" + 3813 " ├─ cacheable: true\n" + 3814 " └─ Project\n" + 3815 " ├─ columns: [SUBSTRING(customer.c_phone, 1, 2) as cntrycode, customer.c_acctbal]\n" + 3816 " └─ Filter\n" + 3817 " ├─ (customer.c_acctbal > Subquery\n" + 3818 " │ ├─ cacheable: true\n" + 3819 " │ └─ Project\n" + 3820 " │ ├─ columns: [avg(customer.c_acctbal) as avg(c_acctbal)]\n" + 3821 " │ └─ GroupBy\n" + 3822 " │ ├─ SelectedExprs(AVG(customer.c_acctbal))\n" + 3823 " │ ├─ Grouping()\n" + 3824 " │ └─ Filter\n" + 3825 " │ ├─ ((customer.c_acctbal > 0.00) AND (SUBSTRING(customer.c_phone, 1, 2) HASH IN ('13', '31', '23', '29', '30', '18', '17')))\n" + 3826 " │ └─ Table\n" + 3827 " │ ├─ name: customer\n" + 3828 " │ └─ columns: [c_phone c_acctbal]\n" + 3829 " │ )\n" + 3830 " └─ Project\n" + 3831 " ├─ columns: [customer.C_CUSTKEY, customer.C_NAME, customer.C_ADDRESS, customer.C_NATIONKEY, customer.C_PHONE, customer.C_ACCTBAL, customer.C_MKTSEGMENT, customer.C_COMMENT]\n" + 3832 " └─ Filter\n" + 3833 " ├─ orders.o_custkey IS NULL\n" + 3834 " └─ LeftOuterHashJoinExcludeNulls\n" + 3835 " ├─ (orders.o_custkey = customer.c_custkey)\n" + 3836 " ├─ Filter\n" + 3837 " │ ├─ (SUBSTRING(customer.c_phone, 1, 2) HASH IN ('13', '31', '23', '29', '30', '18', '17'))\n" + 3838 " │ └─ Table\n" + 3839 " │ └─ name: customer\n" + 3840 " └─ HashLookup\n" + 3841 " ├─ left-key: (customer.c_custkey)\n" + 3842 " ├─ right-key: (orders.o_custkey)\n" + 3843 " └─ Project\n" + 3844 " ├─ columns: [orders.o_custkey]\n" + 3845 " └─ Table\n" + 3846 " ├─ name: orders\n" + 3847 " └─ columns: [o_orderkey o_custkey o_orderstatus o_totalprice o_orderdate o_orderpriority o_clerk o_shippriority o_comment]\n" + 3848 "", 3849 ExpectedAnalysis: "Project\n" + 3850 " ├─ columns: [custsale.cntrycode, count(1) as numcust, sum(custsale.c_acctbal) as totacctbal]\n" + 3851 " └─ Sort(custsale.cntrycode ASC)\n" + 3852 " └─ Project\n" + 3853 " ├─ columns: [count(1), sum(custsale.c_acctbal), custsale.cntrycode, count(1) as numcust, sum(custsale.c_acctbal) as totacctbal]\n" + 3854 " └─ GroupBy\n" + 3855 " ├─ SelectedExprs(COUNT(1), SUM(custsale.c_acctbal), custsale.cntrycode)\n" + 3856 " ├─ Grouping(custsale.cntrycode)\n" + 3857 " └─ SubqueryAlias\n" + 3858 " ├─ name: custsale\n" + 3859 " ├─ outerVisibility: false\n" + 3860 " ├─ isLateral: false\n" + 3861 " ├─ cacheable: true\n" + 3862 " └─ Project\n" + 3863 " ├─ columns: [SUBSTRING(customer.c_phone, 1, 2) as cntrycode, customer.c_acctbal]\n" + 3864 " └─ Filter\n" + 3865 " ├─ (customer.c_acctbal > Subquery\n" + 3866 " │ ├─ cacheable: true\n" + 3867 " │ └─ Project\n" + 3868 " │ ├─ columns: [avg(customer.c_acctbal) as avg(c_acctbal)]\n" + 3869 " │ └─ GroupBy\n" + 3870 " │ ├─ SelectedExprs(AVG(customer.c_acctbal))\n" + 3871 " │ ├─ Grouping()\n" + 3872 " │ └─ Filter\n" + 3873 " │ ├─ ((customer.c_acctbal > 0.00) AND (SUBSTRING(customer.c_phone, 1, 2) HASH IN ('13', '31', '23', '29', '30', '18', '17')))\n" + 3874 " │ └─ Table\n" + 3875 " │ ├─ name: customer\n" + 3876 " │ └─ columns: [c_phone c_acctbal]\n" + 3877 " │ )\n" + 3878 " └─ Project\n" + 3879 " ├─ columns: [customer.C_CUSTKEY, customer.C_NAME, customer.C_ADDRESS, customer.C_NATIONKEY, customer.C_PHONE, customer.C_ACCTBAL, customer.C_MKTSEGMENT, customer.C_COMMENT]\n" + 3880 " └─ Filter\n" + 3881 " ├─ orders.o_custkey IS NULL\n" + 3882 " └─ LeftOuterHashJoinExcludeNulls\n" + 3883 " ├─ (orders.o_custkey = customer.c_custkey)\n" + 3884 " ├─ Filter\n" + 3885 " │ ├─ (SUBSTRING(customer.c_phone, 1, 2) HASH IN ('13', '31', '23', '29', '30', '18', '17'))\n" + 3886 " │ └─ Table\n" + 3887 " │ └─ name: customer\n" + 3888 " └─ HashLookup\n" + 3889 " ├─ left-key: (customer.c_custkey)\n" + 3890 " ├─ right-key: (orders.o_custkey)\n" + 3891 " └─ Project\n" + 3892 " ├─ columns: [orders.o_custkey]\n" + 3893 " └─ Table\n" + 3894 " ├─ name: orders\n" + 3895 " └─ columns: [o_orderkey o_custkey o_orderstatus o_totalprice o_orderdate o_orderpriority o_clerk o_shippriority o_comment]\n" + 3896 "", 3897 }, 3898 }