START TRANSACTION; auto commit mode: off EXPLAIN select supp_nation, cust_nation, l_year, sum(volume) as revenue from ( select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'VIETNAM' and n2.n_name = 'IRAQ') +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +============================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:bte,A1:str,A2:str,A3:str,A4:str,A5:date,A6:date):void; | | X_237:void := querylog.define("explain\n\n\nselect\n\tsupp_nation,\n\tcust_nation,\n\tl_year,\n\tsum(volume) as revenue\nfrom\n\t(\n\t\tselect\n\t\t\tn1.n_name as supp_nation,\n\t\t\tn2.n_name as cust_nation,\n\t\t\textract(year from l_shipdate) as l_year,\n\t\t\tl_extendedprice * (1 - l_discount) as volume\n\t\tfrom\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\twhere\n\t\t\ts_suppkey = l_suppkey\n\t\t\tand o_orderkey = l_orderkey\n\t\t\tand c_custkey = o_custkey\n\t\t\tand s_nationkey = n1.n_nationkey\n\t\t\tand c_nationkey = n2.n_nationkey\n\t\t\tand (\n\t\t\t\t(n1.n_name = \\'VIETNAM\\' and n2.n_name = \\'IRAQ\\')\n\t\t\t\tor (n1.n_name = \\'IRAQ\\' and n2.n_name = \\'VIETNAM\\')\n\t\t\t)\n\t\t\tand l_shipdate between date \\'1995-01-01\\' and date \\'1996-12-31\\'\n\t) as shipping\ngroup by\n\tsupp_nation,\n\tcust_nation,\n\tl_year\norder by\n\tsupp_nation,\n\tcust_nation,\n\tl_year;","default_pipe",160); | | barrier X_292 := language.dataflow(); | | X_195 := bat.new(nil:oid,nil:str); | | X_203 := bat.append(X_195,"sys.shipping"); | | X_213 := bat.append(X_203,"sys.shipping"); | | X_219 := bat.append(X_213,"sys.shipping"); | | X_227 := bat.append(X_219,"sys.L1"); | | X_198 := bat.new(nil:oid,nil:str); | | X_205 := bat.append(X_198,"supp_nation"); | | X_214 := bat.append(X_205,"cust_nation"); | | X_220 := bat.append(X_214,"l_year"); | | X_229 := bat.append(X_220,"revenue"); | | X_199 := bat.new(nil:oid,nil:str); | | X_207 := bat.append(X_199,"char"); | | X_216 := bat.append(X_207,"char"); | | X_222 := bat.append(X_216,"int"); | | X_231 := bat.append(X_222,"decimal"); | | X_200 := bat.new(nil:oid,nil:int); | | X_209 := bat.append(X_200,25); | | X_217 := bat.append(X_209,25); | | X_224 := bat.append(X_217,32); | | X_233 := bat.append(X_224,39); | | X_202 := bat.new(nil:oid,nil:int); | | X_211 := bat.append(X_202,0); | | X_218 := bat.append(X_211,0); | | X_226 := bat.append(X_218,0); | | X_235 := bat.append(X_226,4); | | X_8 := sql.mvc(); | | X_9:bat[:oid,:oid] := sql.tid(X_8,"sys","orders"); | | X_12:bat[:oid,:int] := sql.bind(X_8,"sys","orders","o_orderkey",0); | | (X_15,r1_15) := sql.bind(X_8,"sys","orders","o_orderkey",2); | | X_18:bat[:oid,:int] := sql.bind(X_8,"sys","orders","o_orderkey",1); | | X_20 := sql.delta(X_12,X_15,r1_15,X_18); | | X_21 := algebra.leftfetchjoin(X_9,X_20); | | X_22:bat[:oid,:oid] := sql.tid(X_8,"sys","lineitem"); | | X_24:bat[:oid,:date] := sql.bind(X_8,"sys","lineitem","l_shipdate",0); | | (X_26,r1_26) := sql.bind(X_8,"sys","lineitem","l_shipdate",2); | | X_28:bat[:oid,:date] := sql.bind(X_8,"sys","lineitem","l_shipdate",1); | | X_29 := sql.delta(X_24,X_26,r1_26,X_28); | | X_30 := algebra.leftfetchjoin(X_22,X_29); | | X_31 := algebra.subselect(X_30,A5,A6,true,true,false); | | X_34:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_orderkey",0); | | (X_36,r1_38) := sql.bind(X_8,"sys","lineitem","l_orderkey",2); | | X_38:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_orderkey",1); | | X_39 := sql.delta(X_34,X_36,r1_38,X_38); | | X_40:bat[:oid,:int] := algebra.leftfetchjoinPath(X_31,X_22,X_39); | | (X_41,r1_44) := algebra.subjoin(X_21,X_40,nil:BAT,nil:BAT,false,nil:lng); | | X_45:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_suppkey",0); | | (X_49,r1_52) := sql.bind(X_8,"sys","lineitem","l_suppkey",2); | | X_52:bat[:oid,:int] := sql.bind(X_8,"sys","lineitem","l_suppkey",1); | | X_55 := sql.delta(X_45,X_49,r1_52,X_52); | | X_56:bat[:oid,:int] := algebra.leftfetchjoinPath(r1_44,X_31,X_22,X_55); | | X_57:bat[:oid,:oid] := sql.tid(X_8,"sys","supplier"); | | X_59:bat[:oid,:int] := sql.bind(X_8,"sys","supplier","s_suppkey",0); | | (X_61,r1_66) := sql.bind(X_8,"sys","supplier","s_suppkey",2); | | X_63:bat[:oid,:int] := sql.bind(X_8,"sys","supplier","s_suppkey",1); | | X_64 := sql.delta(X_59,X_61,r1_66,X_63); | | X_65 := algebra.leftfetchjoin(X_57,X_64); | | (X_66,r1_71) := algebra.subjoin(X_56,X_65,nil:BAT,nil:BAT,false,nil:lng); | | X_69:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","supplier","supplier_nationkey",0); | | (X_71,r1_76) := sql.bind_idxbat(X_8,"sys","supplier","supplier_nationkey",2); | | X_73:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","supplier","supplier_nationkey",1); | | X_74 := sql.delta(X_69,X_71,r1_76,X_73); | | X_75:bat[:oid,:oid] := algebra.leftfetchjoinPath(r1_71,X_57,X_74); | | X_76:bat[:oid,:oid] := sql.tid(X_8,"sys","nation"); | | X_79:bat[:oid,:str] := sql.bind(X_8,"sys","nation","n_name",0); | | (X_82,r1_88) := sql.bind(X_8,"sys","nation","n_name",2); | | X_85:bat[:oid,:str] := sql.bind(X_8,"sys","nation","n_name",1); | | X_87 := sql.delta(X_79,X_82,r1_88,X_85); | | X_88 := algebra.leftfetchjoin(X_76,X_87); | | X_89 := algebra.subselect(X_88,A1,A1,true,false,false); | | X_91 := algebra.subselect(X_88,A3,A3,true,false,false); | | X_92 := bat.mergecand(X_89,X_91); | | X_93 := algebra.leftfetchjoin(X_92,X_76); | | (X_94,r1_102) := algebra.subjoin(X_75,X_93,nil:BAT,nil:BAT,false,nil:lng); | | X_98:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","orders","order_custkey",0); | | (X_101,r1_109) := sql.bind_idxbat(X_8,"sys","orders","order_custkey",2); | | X_103:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","orders","order_custkey",1); | | X_104 := sql.delta(X_98,X_101,r1_109,X_103); | | X_105:bat[:oid,:oid] := algebra.leftfetchjoinPath(X_94,X_66,X_41,X_9,X_104); | | X_106:bat[:oid,:oid] := sql.tid(X_8,"sys","customer"); | | (X_109,r1_120) := algebra.subjoin(X_105,X_106,nil:BAT,nil:BAT,false,nil:lng); | | X_112:bat[:oid,:oid] := sql.bind_idxbat(X_8,"sys","customer","customer_nationkey",0); | | (X_115,r1_126) := 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.delta(X_112,X_115,r1_126,X_118); | | X_121:bat[:oid,:oid] := algebra.leftfetchjoinPath(r1_120,X_106,X_120); | | X_122 := algebra.subselect(X_88,A2,A2,true,false,false); | | X_124 := algebra.subselect(X_88,A4,A4,true,false,false); | | X_125 := bat.mergecand(X_122,X_124); | | X_126 := algebra.leftfetchjoin(X_125,X_76); | | (X_127,r1_150) := algebra.subjoin(X_121,X_126,nil:BAT,nil:BAT,false,nil:lng); | | X_131:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_150,X_125,X_88); | | X_132:bat[:oid,:str] := algebra.leftfetchjoinPath(X_127,X_109,r1_102,X_92,X_88); | | X_133 := algebra.subselect(X_132,A1,A1,true,false,false); | | X_135 := algebra.subselect(X_131,X_133,A2,A2,true,false,false); | | X_136 := algebra.subselect(X_132,A3,A3,true,false,false); | | X_137 := algebra.subselect(X_131,X_136,A4,A4,true,false,false); | | X_138 := bat.mergecand(X_135,X_137); | | X_139:bat[:oid,:date] := algebra.leftfetchjoinPath(X_138,X_127,X_109,X_94,X_66,r1_44,X_31,X_30); | | X_140:bat[:oid,:int] := batmtime.year(X_139); | | X_141 := algebra.leftfetchjoin(X_138,X_131); | | X_142 := algebra.leftfetchjoin(X_138,X_132); | | (X_143,r1_182,r2_182) := group.subgroup(X_142); | | (X_146,r1_185,r2_185) := group.subgroup(X_141,X_143); | | (X_149,r1_188,r2_188) := group.subgroupdone(X_140,X_146); | | X_152 := algebra.leftfetchjoin(r1_188,X_140); | | X_153 := algebra.leftfetchjoin(r1_188,X_141); | | X_154 := algebra.leftfetchjoin(r1_188,X_142); | | X_168:bat[:oid,:lng] := sql.bind(X_8,"sys","lineitem","l_extendedprice",0); | | (X_173,r1_212) := sql.bind(X_8,"sys","lineitem","l_extendedprice",2); | | X_176:bat[:oid,:lng] := sql.bind(X_8,"sys","lineitem","l_extendedprice",1); | | X_178 := sql.delta(X_168,X_173,r1_212,X_176); | | X_179:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_138,X_127,X_109,X_94,X_66,r1_44,X_31,X_22,X_178); | | X_180 := calc.lng(A0,15,2); | | X_182:bat[:oid,:lng] := sql.bind(X_8,"sys","lineitem","l_discount",0); | | (X_184,r1_235) := sql.bind(X_8,"sys","lineitem","l_discount",2); | | X_186:bat[:oid,:lng] := sql.bind(X_8,"sys","lineitem","l_discount",1); | | X_187 := sql.delta(X_182,X_184,r1_235,X_186); | | X_188:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_138,X_127,X_109,X_94,X_66,r1_44,X_31,X_22,X_187); | | X_189:bat[:oid,:lng] := batcalc.-(X_180,X_188); | | X_190:bat[:oid,:hge] := batcalc.*(X_179,X_189); | | X_191:bat[:oid,:hge] := aggr.subsum(X_190,X_149,r1_188,true,true); | | (X_155,r1_194,r2_194) := algebra.subsort(X_154,false,false); | | (X_159,r1_198,r2_198) := algebra.subsort(X_153,r1_194,r2_194,false,false); | | (X_162,r1_201,r2_201) := algebra.subsort(X_152,r1_198,r2_198,false,false); | | X_165 := algebra.leftfetchjoin(r1_201,X_154); | | X_166 := algebra.leftfetchjoin(r1_201,X_153); | | X_167 := algebra.leftfetchjoin(r1_201,X_152); | | X_193 := algebra.leftfetchjoin(r1_201,X_191); | | language.pass(X_57); | | language.pass(X_9); | | language.pass(X_106); | | language.pass(X_76); | | language.pass(X_125); | | language.pass(X_92); | | language.pass(X_88); | | language.pass(X_30); | | language.pass(X_131); | | language.pass(X_132); | | language.pass(X_140); | | language.pass(X_141); | | language.pass(X_142); | | language.pass(X_138); | | language.pass(X_127); | | language.pass(X_109); | | language.pass(X_94); | | language.pass(X_66); | | language.pass(r1_44); | | language.pass(X_31); | | language.pass(X_22); | | language.pass(r1_188); | | language.pass(X_154); | | language.pass(X_153); | | language.pass(X_152); | | language.pass(r1_201); | | exit X_292; | | sql.resultSet(X_227,X_229,X_231,X_233,X_235,X_165,X_166,X_167,X_193); | | end user.s1_1; | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 160 tuples COMMIT; auto commit mode: on