START TRANSACTION; auto commit mode: off EXPLAIN select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%light%' +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +=======================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:bte,A1:str):void; | | X_266:void := querylog.define("explain\n\n\nselect\n\tnation,\n\to_year,\n\tsum(amount) as sum_profit\nfrom\n\t(\n\t\tselect\n\t\t\tn_name as nation,\n\t\t\textract(year from o_orderdate) as o_year,\n\t\t\tl_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount\n\t\tfrom\n\t\t\tpart,\n\t\t\tsupplier,\n\t\t\tlineitem,\n\t\t\tpartsupp,\n\t\t\torders,\n\t\t\tnation\n\t\twhere\n\t\t\ts_suppkey = l_suppkey\n\t\t\tand ps_suppkey = l_suppkey\n\t\t\tand ps_partkey = l_partkey\n\t\t\tand p_partkey = l_partkey\n\t\t\tand o_orderkey = l_orderkey\n\t\t\tand s_nationkey = n_nationkey\n\t\t\tand p_name like \\'%light%\\'\n\t) as profit\ngroup by\n\tnation,\n\to_year\norder by\n\tnation,\n\to_year desc;","default_pipe",174); | | barrier X_330 := language.dataflow(); | | X_230 := bat.new(nil:oid,nil:str); | | X_238 := bat.append(X_230,"sys.profit"); | | X_248 := bat.append(X_238,"sys.profit"); | | X_256 := bat.append(X_248,"sys.L1"); | | X_233 := bat.new(nil:oid,nil:str); | | X_240 := bat.append(X_233,"nation"); | | X_249 := bat.append(X_240,"o_year"); | | X_258 := bat.append(X_249,"sum_profit"); | | X_234 := bat.new(nil:oid,nil:str); | | X_242 := bat.append(X_234,"char"); | | X_251 := bat.append(X_242,"int"); | | X_260 := bat.append(X_251,"decimal"); | | X_235 := bat.new(nil:oid,nil:int); | | X_244 := bat.append(X_235,25); | | X_253 := bat.append(X_244,32); | | X_262 := bat.append(X_253,39); | | X_237 := bat.new(nil:oid,nil:int); | | X_246 := bat.append(X_237,0); | | X_255 := bat.append(X_246,0); | | X_264 := bat.append(X_255,4); | | X_3 := sql.mvc(); | | X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","orders"); | | X_7:bat[:oid,:int] := sql.bind(X_3,"sys","orders","o_orderkey",0); | | (X_10,r1_10) := sql.bind(X_3,"sys","orders","o_orderkey",2); | | X_13:bat[:oid,:int] := sql.bind(X_3,"sys","orders","o_orderkey",1); | | X_15 := sql.projectdelta(X_4,X_7,X_10,r1_10,X_13); | | X_16:bat[:oid,:oid] := sql.tid(X_3,"sys","lineitem"); | | X_18:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_orderkey",0); | | (X_20,r1_21) := sql.bind(X_3,"sys","lineitem","l_orderkey",2); | | X_22:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_orderkey",1); | | X_23 := sql.projectdelta(X_16,X_18,X_20,r1_21,X_22); | | (X_24,r1_26) := algebra.subjoin(X_15,X_23,nil:BAT,nil:BAT,false,nil:lng); | | X_29:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_partkey",0); | | (X_31,r1_33) := sql.bind(X_3,"sys","lineitem","l_partkey",2); | | X_33:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_partkey",1); | | X_34 := sql.projectdelta(X_16,X_29,X_31,r1_33,X_33); | | X_35 := algebra.leftfetchjoin(r1_26,X_34); | | X_36:bat[:oid,:wrd] := batmkey.hash(X_35); | | X_38:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_suppkey",0); | | (X_42,r1_48) := sql.bind(X_3,"sys","lineitem","l_suppkey",2); | | X_45:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_suppkey",1); | | X_48 := sql.projectdelta(X_16,X_38,X_42,r1_48,X_45); | | X_49 := algebra.leftfetchjoin(r1_26,X_48); | | X_50:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_36,22,X_49); | | X_51:bat[:oid,:oid] := sql.tid(X_3,"sys","partsupp"); | | X_53:bat[:oid,:wrd] := sql.bind_idxbat(X_3,"sys","partsupp","partsuppkey",0); | | (X_55,r1_62) := sql.bind_idxbat(X_3,"sys","partsupp","partsuppkey",2); | | X_57:bat[:oid,:wrd] := sql.bind_idxbat(X_3,"sys","partsupp","partsuppkey",1); | | X_58 := sql.projectdelta(X_51,X_53,X_55,r1_62,X_57); | | (X_59,r1_67) := algebra.subjoin(X_50,X_58,nil:BAT,nil:BAT,false,nil:lng); | | X_64 := algebra.leftfetchjoin(X_59,X_35); | | X_65:bat[:oid,:int] := sql.bind(X_3,"sys","partsupp","ps_partkey",0); | | (X_67,r1_75) := sql.bind(X_3,"sys","partsupp","ps_partkey",2); | | X_69:bat[:oid,:int] := sql.bind(X_3,"sys","partsupp","ps_partkey",1); | | X_71 := sql.projectdelta(X_51,X_65,X_67,r1_75,X_69); | | X_72 := algebra.leftfetchjoin(r1_67,X_71); | | X_73:bat[:oid,:bit] := batcalc.==(X_64,X_72); | | X_75 := algebra.subselect(X_73,true,true,true,false,false); | | X_76 := algebra.leftfetchjoin(X_75,X_59); | | X_77 := algebra.leftfetchjoin(X_76,X_49); | | X_78 := algebra.leftfetchjoin(X_75,r1_67); | | X_79:bat[:oid,:int] := sql.bind(X_3,"sys","partsupp","ps_suppkey",0); | | (X_83,r1_95) := sql.bind(X_3,"sys","partsupp","ps_suppkey",2); | | X_86:bat[:oid,:int] := sql.bind(X_3,"sys","partsupp","ps_suppkey",1); | | X_88 := sql.projectdelta(X_51,X_79,X_83,r1_95,X_86); | | X_89 := algebra.leftfetchjoin(X_78,X_88); | | X_90:bat[:oid,:bit] := batcalc.==(X_77,X_89); | | X_91 := algebra.subselect(X_90,true,true,true,false,false); | | X_93 := algebra.leftfetchjoin(X_91,X_76); | | X_94 := algebra.leftfetchjoin(X_93,X_35); | | X_97:bat[:oid,:str] := sql.bind(X_3,"sys","part","p_name",0); | | X_95:bat[:oid,:oid] := sql.tid(X_3,"sys","part"); | | X_301 := algebra.likesubselect(X_97,X_95,A1,"",false); | | (X_100,r1_114) := sql.bind(X_3,"sys","part","p_name",2); | | X_302 := algebra.likesubselect(r1_114,nil:bat[:oid,:oid],A1,"",false); | | X_102:bat[:oid,:str] := sql.bind(X_3,"sys","part","p_name",1); | | X_304 := algebra.likesubselect(X_102,X_95,A1,"",false); | | X_104 := sql.subdelta(X_301,X_95,X_100,X_302,X_304); | | X_106:bat[:oid,:int] := sql.bind(X_3,"sys","part","p_partkey",0); | | (X_108,r1_126) := sql.bind(X_3,"sys","part","p_partkey",2); | | X_110:bat[:oid,:int] := sql.bind(X_3,"sys","part","p_partkey",1); | | X_111 := sql.projectdelta(X_104,X_106,X_108,r1_126,X_110); | | (X_112,r1_132) := algebra.subjoin(X_94,X_111,nil:BAT,nil:BAT,false,nil:lng); | | X_115:bat[:oid,:int] := algebra.leftfetchjoinPath(X_112,X_93,X_49); | | X_116:bat[:oid,:oid] := sql.tid(X_3,"sys","supplier"); | | X_118:bat[:oid,:int] := sql.bind(X_3,"sys","supplier","s_suppkey",0); | | (X_121,r1_142) := sql.bind(X_3,"sys","supplier","s_suppkey",2); | | X_124:bat[:oid,:int] := sql.bind(X_3,"sys","supplier","s_suppkey",1); | | X_127 := sql.projectdelta(X_116,X_118,X_121,r1_142,X_124); | | (X_128,r1_150) := algebra.subjoin(X_115,X_127,nil:BAT,nil:BAT,false,nil:lng); | | X_131:bat[:oid,:oid] := sql.bind_idxbat(X_3,"sys","supplier","supplier_nationkey",0); | | (X_133,r1_155) := sql.bind_idxbat(X_3,"sys","supplier","supplier_nationkey",2); | | X_135:bat[:oid,:oid] := sql.bind_idxbat(X_3,"sys","supplier","supplier_nationkey",1); | | X_136 := sql.projectdelta(X_116,X_131,X_133,r1_155,X_135); | | X_137 := algebra.leftfetchjoin(r1_150,X_136); | | X_138:bat[:oid,:oid] := sql.tid(X_3,"sys","nation"); | | (X_140,r1_163) := algebra.subjoin(X_137,X_138,nil:BAT,nil:BAT,false,nil:lng); | | X_143:bat[:oid,:date] := sql.bind(X_3,"sys","orders","o_orderdate",0); | | (X_146,r1_169) := sql.bind(X_3,"sys","orders","o_orderdate",2); | | X_148:bat[:oid,:date] := sql.bind(X_3,"sys","orders","o_orderdate",1); | | X_149 := sql.projectdelta(X_4,X_143,X_146,r1_169,X_148); | | X_150:bat[:oid,:date] := algebra.leftfetchjoinPath(X_140,X_128,X_112,X_93,X_24,X_149); | | X_151:bat[:oid,:int] := batmtime.year(X_150); | | X_152:bat[:oid,:str] := sql.bind(X_3,"sys","nation","n_name",0); | | (X_156,r1_186) := sql.bind(X_3,"sys","nation","n_name",2); | | X_159:bat[:oid,:str] := sql.bind(X_3,"sys","nation","n_name",1); | | X_161 := sql.projectdelta(X_138,X_152,X_156,r1_186,X_159); | | X_162 := algebra.leftfetchjoin(r1_163,X_161); | | (X_163,r1_194,r2_194) := group.subgroup(X_162); | | (X_166,r1_197,r2_197) := group.subgroupdone(X_151,X_163); | | X_169 := algebra.leftfetchjoin(r1_197,X_151); | | X_170 := algebra.leftfetchjoin(r1_197,X_162); | | X_181:bat[:oid,:lng] := sql.bind(X_3,"sys","lineitem","l_extendedprice",0); | | (X_184,r1_215) := sql.bind(X_3,"sys","lineitem","l_extendedprice",2); | | X_187:bat[:oid,:lng] := sql.bind(X_3,"sys","lineitem","l_extendedprice",1); | | X_188 := sql.projectdelta(X_16,X_181,X_184,r1_215,X_187); | | X_189:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_140,X_128,X_112,X_93,r1_26,X_188); | | X_191 := calc.lng(A0,15,2); | | X_193:bat[:oid,:lng] := sql.bind(X_3,"sys","lineitem","l_discount",0); | | (X_196,r1_237) := sql.bind(X_3,"sys","lineitem","l_discount",2); | | X_198:bat[:oid,:lng] := sql.bind(X_3,"sys","lineitem","l_discount",1); | | X_200 := sql.projectdelta(X_16,X_193,X_196,r1_237,X_198); | | X_201:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_140,X_128,X_112,X_93,r1_26,X_200); | | X_202:bat[:oid,:lng] := batcalc.-(X_191,X_201); | | X_203:bat[:oid,:hge] := batcalc.*(X_189,X_202); | | X_204:bat[:oid,:lng] := sql.bind(X_3,"sys","partsupp","ps_supplycost",0); | | (X_208,r1_257) := sql.bind(X_3,"sys","partsupp","ps_supplycost",2); | | X_210:bat[:oid,:lng] := sql.bind(X_3,"sys","partsupp","ps_supplycost",1); | | X_211 := sql.projectdelta(X_51,X_204,X_208,r1_257,X_210); | | X_212:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_140,X_128,X_112,X_91,X_78,X_211); | | X_214:bat[:oid,:lng] := sql.bind(X_3,"sys","lineitem","l_quantity",0); | | (X_218,r1_275) := sql.bind(X_3,"sys","lineitem","l_quantity",2); | | X_220:bat[:oid,:lng] := sql.bind(X_3,"sys","lineitem","l_quantity",1); | | X_222 := sql.projectdelta(X_16,X_214,X_218,r1_275,X_220); | | X_223:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_140,X_128,X_112,X_93,r1_26,X_222); | | X_224:bat[:oid,:hge] := batcalc.*(X_212,X_223); | | X_225:bat[:oid,:hge] := batcalc.-(X_203,X_224); | | X_226:bat[:oid,:hge] := aggr.subsum(X_225,X_166,r1_197,true,true); | | (X_171,r1_202,r2_202) := algebra.subsort(X_170,false,false); | | (X_175,r1_206,r2_206) := algebra.subsort(X_169,r1_202,r2_202,true,false); | | X_179 := algebra.leftfetchjoin(r1_206,X_170); | | X_180 := algebra.leftfetchjoin(r1_206,X_169); | | X_228 := algebra.leftfetchjoin(r1_206,X_226); | | language.pass(X_59); | | language.pass(X_75); | | language.pass(r1_67); | | language.pass(X_76); | | language.pass(X_35); | | language.pass(X_95); | | language.pass(X_49); | | language.pass(X_116); | | language.pass(X_4); | | language.pass(X_138); | | language.pass(X_151); | | language.pass(X_162); | | language.pass(X_51); | | language.pass(X_91); | | language.pass(X_78); | | language.pass(X_16); | | language.pass(X_140); | | language.pass(X_128); | | language.pass(X_112); | | language.pass(X_93); | | language.pass(r1_26); | | language.pass(r1_197); | | language.pass(X_170); | | language.pass(X_169); | | language.pass(r1_206); | | exit X_330; | | sql.resultSet(X_256,X_258,X_260,X_262,X_264,X_179,X_180,X_228); | | end user.s1_1; | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 174 tuples COMMIT; auto commit mode: on