START TRANSACTION; auto commit mode: off EXPLAIN select o_year, sum(case when nation = 'IRAQ' then volume else 0 end) / sum(volume) as mkt_share from ( select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +=======================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:str,A1:bte,A2:bte,A3:str,A4:date,A5:date,A6:str):void; | | X_252:void := querylog.define("explain\n\n\nselect\n\to_year,\n\tsum(case\n\t\twhen nation = \\'IRAQ\\' then volume\n\t\telse 0\n\tend) / sum(volume) as mkt_share\nfrom\n\t(\n\t\tselect\n\t\t\textract(year from o_orderdate) as o_year,\n\t\t\tl_extendedprice * (1 - l_discount) as volume,\n\t\t\tn2.n_name as nation\n\t\tfrom\n\t\t\tpart,\n\t\t\tsupplier,\n\t\t\tlineitem,\n\t\t\torders,\n\t\t\tcustomer,\n\t\t\tnation n1,\n\t\t\tnation n2,\n\t\t\tregion\n\t\twhere\n\t\t\tp_partkey = l_partkey\n\t\t\tand s_suppkey = l_suppkey\n\t\t\tand l_orderkey = o_orderkey\n\t\t\tand o_custkey = c_custkey\n\t\t\tand c_nationkey = n1.n_nationkey\n\t\t\tand n1.n_regionkey = r_regionkey\n\t\t\tand r_name = \\'MIDDLE EAST\\'\n\t\t\tand s_nationkey = n2.n_nationkey\n\t\t\tand o_orderdate between date \\'1995-01-01\\' and date \\'1996-12-31\\'\n\t\t\tand p_type = \\'MEDIUM BRUSHED STEEL\\'\n\t) as all_nations\ngroup by\n\to_year\norder by\n\to_year;","default_pipe",166); | | barrier X_328 := language.dataflow(); | | X_225 := bat.new(nil:oid,nil:str); | | X_233 := bat.append(X_225,"sys.all_nations"); | | X_243 := bat.append(X_233,"sys.L3"); | | X_228 := bat.new(nil:oid,nil:str); | | X_235 := bat.append(X_228,"o_year"); | | X_245 := bat.append(X_235,"mkt_share"); | | X_229 := bat.new(nil:oid,nil:str); | | X_237 := bat.append(X_229,"int"); | | X_247 := bat.append(X_237,"decimal"); | | X_230 := bat.new(nil:oid,nil:int); | | X_239 := bat.append(X_230,32); | | X_249 := bat.append(X_239,39); | | X_232 := bat.new(nil:oid,nil:int); | | X_241 := bat.append(X_232,0); | | X_250 := bat.append(X_241,4); | | X_8 := sql.mvc(); | | X_9:bat[:oid,:oid] := sql.tid(X_8,"sys","lineitem"); | | X_12:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_orderkey",0); | | (X_15,r1_15) := sql.bind(X_8,"sys","lineitem","l_orderkey",2); | | X_18:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_orderkey",1); | | X_20 := sql.projectdelta(X_9,X_12,X_15,r1_15,X_18); | | X_23:bat[:oid,:date] := sql.bind(X_8,"sys","orders","o_orderdate",0); | | X_21:bat[:oid,:oid] := sql.tid(X_8,"sys","orders"); | | X_289 := algebra.subselect(X_23,X_21,A4,A5,true,true,false); | | (X_25,r1_26) := sql.bind(X_8,"sys","orders","o_orderdate",2); | | X_290 := algebra.subselect(r1_26,nil:bat[:oid,:oid],A4,A5,true,true,false); | | X_27:bat[:oid,:date] := sql.bind(X_8,"sys","orders","o_orderdate",1); | | X_292 := algebra.subselect(X_27,X_21,A4,A5,true,true,false); | | X_28 := sql.subdelta(X_289,X_21,X_25,X_290,X_292); | | X_31:bat[:oid,:int] := sql.bind(X_8,"sys","orders","o_orderkey",0); | | (X_33,r1_38) := sql.bind(X_8,"sys","orders","o_orderkey",2); | | X_35:bat[:oid,:int] := sql.bind(X_8,"sys","orders","o_orderkey",1); | | X_36 := sql.projectdelta(X_28,X_31,X_33,r1_38,X_35); | | (X_37,r1_44) := algebra.subjoin(X_20,X_36,nil:BAT,nil:BAT,false,nil:lng); | | X_41:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_partkey",0); | | (X_46,r1_53) := sql.bind(X_8,"sys","lineitem","l_partkey",2); | | X_49:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_partkey",1); | | X_51 := sql.projectdelta(X_9,X_41,X_46,r1_53,X_49); | | X_52 := algebra.leftfetchjoin(X_37,X_51); | | X_55:bat[:oid,:str] := sql.bind(X_8,"sys","part","p_type",0); | | X_53:bat[:oid,:oid] := sql.tid(X_8,"sys","part"); | | X_293 := algebra.subselect(X_55,X_53,A6,A6,true,false,false); | | (X_57,r1_65) := sql.bind(X_8,"sys","part","p_type",2); | | X_294 := algebra.subselect(r1_65,nil:bat[:oid,:oid],A6,A6,true,false,false); | | X_59:bat[:oid,:str] := sql.bind(X_8,"sys","part","p_type",1); | | X_296 := algebra.subselect(X_59,X_53,A6,A6,true,false,false); | | X_60 := sql.subdelta(X_293,X_53,X_57,X_294,X_296); | | X_63:bat[:oid,:int] := sql.bind(X_8,"sys","part","p_partkey",0); | | (X_65,r1_76) := sql.bind(X_8,"sys","part","p_partkey",2); | | X_67:bat[:oid,:int] := sql.bind(X_8,"sys","part","p_partkey",1); | | X_68 := sql.projectdelta(X_60,X_63,X_65,r1_76,X_67); | | (X_69,r1_82) := algebra.subjoin(X_52,X_68,nil:BAT,nil:BAT,false,nil:lng); | | X_73:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","orders","order_custkey",0); | | (X_78,r1_91) := sql.bind_idxbat(X_8,"sys","orders","order_custkey",2); | | X_81:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","orders","order_custkey",1); | | X_83 := sql.projectdelta(X_28,X_73,X_78,r1_91,X_81); | | X_84:bat[:oid,:oid] := algebra.leftfetchjoinPath(X_69,r1_44,X_83); | | X_85:bat[:oid,:oid] := sql.tid(X_8,"sys","customer"); | | (X_87,r1_103) := algebra.subjoin(X_84,X_85,nil:BAT,nil:BAT,false,nil:lng); | | X_89:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_suppkey",0); | | (X_92,r1_108) := sql.bind(X_8,"sys","lineitem","l_suppkey",2); | | X_94:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_suppkey",1); | | X_95 := sql.projectdelta(X_9,X_89,X_92,r1_108,X_94); | | X_96:bat[:oid,:int] := algebra.leftfetchjoinPath(X_87,X_69,X_37,X_95); | | X_97:bat[:oid,:oid] := sql.tid(X_8,"sys","supplier"); | | X_99:bat[:oid,:int] := sql.bind(X_8,"sys","supplier","s_suppkey",0); | | (X_101,r1_120) := sql.bind(X_8,"sys","supplier","s_suppkey",2); | | X_104:bat[:oid,:int] := sql.bind(X_8,"sys","supplier","s_suppkey",1); | | X_105 := sql.projectdelta(X_97,X_99,X_101,r1_120,X_104); | | (X_106,r1_126) := algebra.subjoin(X_96,X_105,nil:BAT,nil:BAT,false,nil:lng); | | X_111:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","customer","customer_nationkey",0); | | (X_114,r1_134) := sql.bind_idxbat(X_8,"sys","customer","customer_nationkey",2); | | X_118:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","customer","customer_nationkey",1); | | X_120 := sql.projectdelta(X_85,X_111,X_114,r1_134,X_118); | | X_121:bat[:oid,:oid] := algebra.leftfetchjoinPath(X_106,r1_103,X_120); | | X_122:bat[:oid,:oid] := sql.tid(X_8,"sys","nation"); | | (X_124,r1_146) := algebra.subjoin(X_121,X_122,nil:BAT,nil:BAT,false,nil:lng); | | X_126:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","nation","nation_regionkey",0); | | (X_128,r1_150) := sql.bind_idxbat(X_8,"sys","nation","nation_regionkey",2); | | X_130:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","nation","nation_regionkey",1); | | X_131 := sql.projectdelta(X_122,X_126,X_128,r1_150,X_130); | | X_132 := algebra.leftfetchjoin(r1_146,X_131); | | X_136:bat[:oid,:str] := sql.bind(X_8,"sys","region","r_name",0); | | X_133:bat[:oid,:oid] := sql.tid(X_8,"sys","region"); | | X_297 := algebra.subselect(X_136,X_133,A3,A3,true,false,false); | | (X_138,r1_161) := sql.bind(X_8,"sys","region","r_name",2); | | X_298 := algebra.subselect(r1_161,nil:bat[:oid,:oid],A3,A3,true,false,false); | | X_140:bat[:oid,:str] := sql.bind(X_8,"sys","region","r_name",1); | | X_300 := algebra.subselect(X_140,X_133,A3,A3,true,false,false); | | X_141 := sql.subdelta(X_297,X_133,X_138,X_298,X_300); | | X_144 := X_141; | | (X_145,r1_171) := algebra.subjoin(X_132,X_144,nil:BAT,nil:BAT,false,nil:lng); | | X_149:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","supplier","supplier_nationkey",0); | | (X_153,r1_179) := sql.bind_idxbat(X_8,"sys","supplier","supplier_nationkey",2); | | X_156:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","supplier","supplier_nationkey",1); | | X_158 := sql.projectdelta(X_97,X_149,X_153,r1_179,X_156); | | X_159:bat[:oid,:oid] := algebra.leftfetchjoinPath(X_145,X_124,r1_126,X_158); | | (X_162,r1_192) := algebra.subjoin(X_159,X_122,nil:BAT,nil:BAT,false,nil:lng); | | X_164 := sql.projectdelta(X_28,X_23,X_25,r1_26,X_27); | | X_165:bat[:oid,:date] := algebra.leftfetchjoinPath(X_162,X_145,X_124,X_106,X_87,X_69,r1_44,X_164); | | X_166:bat[:oid,:int] := batmtime.year(X_165); | | (X_167,r1_205,r2_205) := group.subgroupdone(X_166); | | X_170 := algebra.leftfetchjoin(r1_205,X_166); | | X_176:bat[:oid,:str] := sql.bind(X_8,"sys","nation","n_name",0); | | (X_179,r1_217) := sql.bind(X_8,"sys","nation","n_name",2); | | X_182:bat[:oid,:str] := sql.bind(X_8,"sys","nation","n_name",1); | | X_184 := sql.projectdelta(X_122,X_176,X_179,r1_217,X_182); | | X_185 := algebra.leftfetchjoin(r1_192,X_184); | | X_186:bat[:oid,:bit] := batcalc.==(X_185,A0); | | X_187:bat[:oid,:bit] := batcalc.isnil(X_186); | | X_188:bat[:oid,:bit] := batcalc.ifthenelse(X_187,false,X_186); | | X_189:bat[:oid,:lng] := sql.bind(X_8,"sys","lineitem","l_extendedprice",0); | | (X_193,r1_240) := sql.bind(X_8,"sys","lineitem","l_extendedprice",2); | | X_195:bat[:oid,:lng] := sql.bind(X_8,"sys","lineitem","l_extendedprice",1); | | X_196 := sql.projectdelta(X_9,X_189,X_193,r1_240,X_195); | | X_197:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_162,X_145,X_124,X_106,X_87,X_69,X_37,X_196); | | X_200 := calc.lng(A2,15,2); | | X_202:bat[:oid,:lng] := sql.bind(X_8,"sys","lineitem","l_discount",0); | | (X_205,r1_262) := sql.bind(X_8,"sys","lineitem","l_discount",2); | | X_207:bat[:oid,:lng] := sql.bind(X_8,"sys","lineitem","l_discount",1); | | X_209 := sql.projectdelta(X_9,X_202,X_205,r1_262,X_207); | | X_210:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_162,X_145,X_124,X_106,X_87,X_69,X_37,X_209); | | X_211:bat[:oid,:lng] := batcalc.-(X_200,X_210); | | X_212:bat[:oid,:hge] := batcalc.*(X_197,X_211); | | X_213 := calc.hge(A1,33,4); | | X_215:bat[:oid,:hge] := batcalc.ifthenelse(X_188,X_212,X_213); | | X_216:bat[:oid,:hge] := aggr.subsum(X_215,X_167,r1_205,true,true); | | X_221:bat[:oid,:hge] := aggr.subsum(X_212,X_167,r1_205,true,true); | | (X_171,r1_209,r2_209) := algebra.subsort(X_170,false,false); | | X_175 := algebra.leftfetchjoin(r1_209,X_170); | | X_218:bat[:oid,:hge] := batcalc.hge(4,X_216,39,8); | | X_222:bat[:oid,:hge] := batcalc./(X_218,X_221); | | X_223 := algebra.leftfetchjoin(r1_209,X_222); | | language.pass(X_21); | | language.pass(X_53); | | language.pass(X_85); | | language.pass(X_133); | | language.pass(X_97); | | language.pass(X_28); | | language.pass(X_23); | | language.pass(X_25); | | language.pass(r1_26); | | language.pass(X_27); | | language.pass(r1_44); | | language.pass(X_166); | | language.pass(X_122); | | language.pass(X_186); | | language.pass(X_9); | | language.pass(X_162); | | language.pass(X_145); | | language.pass(X_124); | | language.pass(X_106); | | language.pass(X_87); | | language.pass(X_69); | | language.pass(X_37); | | language.pass(X_212); | | language.pass(X_167); | | language.pass(r1_205); | | language.pass(X_170); | | language.pass(r1_209); | | exit X_328; | | sql.resultSet(X_243,X_245,X_247,X_249,X_250,X_175,X_223); | | end user.s1_1; | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 166 tuples COMMIT; auto commit mode: on