START TRANSACTION; auto commit mode: off EXPLAIN select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1993-10-01' and o_orderdate < date '1993-10-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +=========================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:bte,A1:date,A2:date,A3:int,A4:str):void; | | X_264:void := querylog.define("explain\n\n\nselect\n\tc_custkey,\n\tc_name,\n\tsum(l_extendedprice * (1 - l_discount)) as revenue,\n\tc_acctbal,\n\tn_name,\n\tc_address,\n\tc_phone,\n\tc_comment\nfrom\n\tcustomer,\n\torders,\n\tlineitem,\n\tnation\nwhere\n\tc_custkey = o_custkey\n\tand l_orderkey = o_orderkey\n\tand o_orderdate >= date \\'1993-10-01\\'\n\tand o_orderdate < date \\'1993-10-01\\' + interval \\'3\\' month\n\tand l_returnflag = \\'R\\'\n\tand c_nationkey = n_nationkey\ngroup by\n\tc_custkey,\n\tc_name,\n\tc_acctbal,\n\tc_phone,\n\tn_name,\n\tc_address,\n\tc_comment\norder by\n\trevenue desc\nlimit 20;","default_pipe",177); | | barrier X_330 := language.dataflow(); | | X_189 := bat.new(nil:oid,nil:str); | | X_197 := bat.append(X_189,"sys.customer"); | | X_207 := bat.append(X_197,"sys.customer"); | | X_215 := bat.append(X_207,"sys.L1"); | | X_225 := bat.append(X_215,"sys.customer"); | | X_233 := bat.append(X_225,"sys.nation"); | | X_242 := bat.append(X_233,"sys.customer"); | | X_251 := bat.append(X_242,"sys.customer"); | | X_257 := bat.append(X_251,"sys.customer"); | | X_192 := bat.new(nil:oid,nil:str); | | X_199 := bat.append(X_192,"c_custkey"); | | X_208 := bat.append(X_199,"c_name"); | | X_217 := bat.append(X_208,"revenue"); | | X_226 := bat.append(X_217,"c_acctbal"); | | X_235 := bat.append(X_226,"n_name"); | | X_244 := bat.append(X_235,"c_address"); | | X_252 := bat.append(X_244,"c_phone"); | | X_258 := bat.append(X_252,"c_comment"); | | X_193 := bat.new(nil:oid,nil:str); | | X_201 := bat.append(X_193,"int"); | | X_210 := bat.append(X_201,"varchar"); | | X_219 := bat.append(X_210,"decimal"); | | X_228 := bat.append(X_219,"decimal"); | | X_237 := bat.append(X_228,"char"); | | X_246 := bat.append(X_237,"varchar"); | | X_254 := bat.append(X_246,"char"); | | X_260 := bat.append(X_254,"varchar"); | | X_194 := bat.new(nil:oid,nil:int); | | X_203 := bat.append(X_194,32); | | X_212 := bat.append(X_203,25); | | X_221 := bat.append(X_212,39); | | X_229 := bat.append(X_221,15); | | X_239 := bat.append(X_229,25); | | X_248 := bat.append(X_239,40); | | X_255 := bat.append(X_248,15); | | X_261 := bat.append(X_255,117); | | X_196 := bat.new(nil:oid,nil:int); | | X_205 := bat.append(X_196,0); | | X_214 := bat.append(X_205,0); | | X_223 := bat.append(X_214,4); | | X_231 := bat.append(X_223,2); | | X_240 := bat.append(X_231,0); | | X_250 := bat.append(X_240,0); | | X_256 := bat.append(X_250,0); | | X_263 := bat.append(X_256,0); | | X_6 := sql.mvc(); | | X_10:bat[:oid,:str] := sql.bind(X_6,"sys","lineitem","l_returnflag",0); | | X_7:bat[:oid,:oid] := sql.tid(X_6,"sys","lineitem"); | | X_299 := algebra.subselect(X_10,X_7,A4,A4,true,false,false); | | (X_13,r1_13) := sql.bind(X_6,"sys","lineitem","l_returnflag",2); | | X_300 := algebra.subselect(r1_13,nil:bat[:oid,:oid],A4,A4,true,false,false); | | X_16:bat[:oid,:str] := sql.bind(X_6,"sys","lineitem","l_returnflag",1); | | X_302 := algebra.subselect(X_16,X_7,A4,A4,true,false,false); | | X_18 := sql.subdelta(X_299,X_7,X_13,X_300,X_302); | | X_21:bat[:oid,:int] := sql.bind(X_6,"sys","lineitem","l_orderkey",0); | | (X_23,r1_26) := sql.bind(X_6,"sys","lineitem","l_orderkey",2); | | X_25:bat[:oid,:int] := sql.bind(X_6,"sys","lineitem","l_orderkey",1); | | X_26 := sql.projectdelta(X_18,X_21,X_23,r1_26,X_25); | | X_29:bat[:oid,:date] := sql.bind(X_6,"sys","orders","o_orderdate",0); | | X_27:bat[:oid,:oid] := sql.tid(X_6,"sys","orders"); | | X_34:date := mtime.addmonths(A2,A3); | | X_303 := algebra.subselect(X_29,X_27,A1,X_34,true,false,false); | | (X_31,r1_36) := sql.bind(X_6,"sys","orders","o_orderdate",2); | | X_304 := algebra.subselect(r1_36,nil:bat[:oid,:oid],A1,X_34,true,false,false); | | X_33:bat[:oid,:date] := sql.bind(X_6,"sys","orders","o_orderdate",1); | | X_306 := algebra.subselect(X_33,X_27,A1,X_34,true,false,false); | | X_35 := sql.subdelta(X_303,X_27,X_31,X_304,X_306); | | X_36:bat[:oid,:int] := sql.bind(X_6,"sys","orders","o_orderkey",0); | | (X_40,r1_50) := sql.bind(X_6,"sys","orders","o_orderkey",2); | | X_43:bat[:oid,:int] := sql.bind(X_6,"sys","orders","o_orderkey",1); | | X_45 := sql.projectdelta(X_35,X_36,X_40,r1_50,X_43); | | (X_46,r1_58) := algebra.subjoin(X_26,X_45,nil:BAT,nil:BAT,false,nil:lng); | | X_51:bat[:oid,:oid] := sql.bind_idxbat(X_6,"sys","orders","order_custkey",0); | | (X_53,r1_65) := sql.bind_idxbat(X_6,"sys","orders","order_custkey",2); | | X_56:bat[:oid,:oid] := sql.bind_idxbat(X_6,"sys","orders","order_custkey",1); | | X_57 := sql.projectdelta(X_35,X_51,X_53,r1_65,X_56); | | X_58 := algebra.leftfetchjoin(r1_58,X_57); | | X_59:bat[:oid,:oid] := sql.tid(X_6,"sys","customer"); | | (X_61,r1_75) := algebra.subjoin(X_58,X_59,nil:BAT,nil:BAT,false,nil:lng); | | X_63:bat[:oid,:oid] := sql.bind_idxbat(X_6,"sys","customer","customer_nationkey",0); | | (X_65,r1_79) := sql.bind_idxbat(X_6,"sys","customer","customer_nationkey",2); | | X_68:bat[:oid,:oid] := sql.bind_idxbat(X_6,"sys","customer","customer_nationkey",1); | | X_69 := sql.projectdelta(X_59,X_63,X_65,r1_79,X_68); | | X_70 := algebra.leftfetchjoin(r1_75,X_69); | | X_71:bat[:oid,:oid] := sql.tid(X_6,"sys","nation"); | | (X_73,r1_88) := algebra.subjoin(X_70,X_71,nil:BAT,nil:BAT,false,nil:lng); | | X_75:bat[:oid,:str] := sql.bind(X_6,"sys","nation","n_name",0); | | (X_78,r1_93) := sql.bind(X_6,"sys","nation","n_name",2); | | X_81:bat[:oid,:str] := sql.bind(X_6,"sys","nation","n_name",1); | | X_83 := sql.projectdelta(X_71,X_75,X_78,r1_93,X_81); | | X_84 := algebra.leftfetchjoin(r1_88,X_83); | | X_85:bat[:oid,:int] := sql.bind(X_6,"sys","customer","c_custkey",0); | | (X_87,r1_103) := sql.bind(X_6,"sys","customer","c_custkey",2); | | X_89:bat[:oid,:int] := sql.bind(X_6,"sys","customer","c_custkey",1); | | X_90 := sql.projectdelta(X_59,X_85,X_87,r1_103,X_89); | | X_91:bat[:oid,:int] := algebra.leftfetchjoinPath(X_73,r1_75,X_90); | | (X_92,r1_110,r2_110) := group.subgroup(X_91); | | (X_95,r1_113,r2_113) := group.subgroupdone(X_84,X_92); | | X_98 := algebra.leftfetchjoin(r1_113,X_91); | | X_105:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_extendedprice",0); | | (X_109,r1_135) := sql.bind(X_6,"sys","lineitem","l_extendedprice",2); | | X_112:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_extendedprice",1); | | X_114 := sql.projectdelta(X_18,X_105,X_109,r1_135,X_112); | | X_115:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_73,X_61,X_46,X_114); | | X_116 := calc.lng(A0,15,2); | | X_118:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_discount",0); | | (X_121,r1_156) := sql.bind(X_6,"sys","lineitem","l_discount",2); | | X_123:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_discount",1); | | X_124 := sql.projectdelta(X_18,X_118,X_121,r1_156,X_123); | | X_125:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_73,X_61,X_46,X_124); | | X_126:bat[:oid,:lng] := batcalc.-(X_116,X_125); | | X_127:bat[:oid,:hge] := batcalc.*(X_115,X_126); | | X_128:bat[:oid,:hge] := aggr.subsum(X_127,X_95,r1_113,true,true); | | (X_100,r1_126) := algebra.subjoin(X_98,X_90,nil:BAT,nil:BAT,false,nil:lng); | | X_130 := algebra.leftfetchjoin(X_100,X_128); | | X_132 := algebra.firstn(X_130,20:wrd,false,false); | | X_134 := algebra.leftfetchjoin(X_132,X_130); | | (X_135,r1_180,r2_180) := algebra.subsort(X_134,true,false); | | X_138:bat[:oid,:int] := algebra.leftfetchjoinPath(r1_180,X_132,X_100,X_98); | | X_140 := algebra.subslice(X_138,0,19:wrd); | | X_161:bat[:oid,:str] := algebra.leftfetchjoinPath(X_140,r1_180,X_132,X_100,r1_113,X_84); | | X_141 := algebra.leftfetchjoin(X_140,X_138); | | X_142:bat[:oid,:str] := sql.bind(X_6,"sys","customer","c_name",0); | | (X_147,r1_198) := sql.bind(X_6,"sys","customer","c_name",2); | | X_150:bat[:oid,:str] := sql.bind(X_6,"sys","customer","c_name",1); | | X_151 := sql.projectdelta(X_59,X_142,X_147,r1_198,X_150); | | X_152:bat[:oid,:str] := algebra.leftfetchjoinPath(X_140,r1_180,X_132,r1_126,X_151); | | X_153:bat[:oid,:hge] := algebra.leftfetchjoinPath(X_140,r1_180,X_134); | | X_154:bat[:oid,:lng] := sql.bind(X_6,"sys","customer","c_acctbal",0); | | (X_156,r1_212) := sql.bind(X_6,"sys","customer","c_acctbal",2); | | X_158:bat[:oid,:lng] := sql.bind(X_6,"sys","customer","c_acctbal",1); | | X_159 := sql.projectdelta(X_59,X_154,X_156,r1_212,X_158); | | X_160:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_140,r1_180,X_132,r1_126,X_159); | | X_162:bat[:oid,:str] := sql.bind(X_6,"sys","customer","c_address",0); | | (X_167,r1_231) := sql.bind(X_6,"sys","customer","c_address",2); | | X_170:bat[:oid,:str] := sql.bind(X_6,"sys","customer","c_address",1); | | X_172 := sql.projectdelta(X_59,X_162,X_167,r1_231,X_170); | | X_173:bat[:oid,:str] := algebra.leftfetchjoinPath(X_140,r1_180,X_132,r1_126,X_172); | | X_174:bat[:oid,:str] := sql.bind(X_6,"sys","customer","c_phone",0); | | (X_176,r1_244) := sql.bind(X_6,"sys","customer","c_phone",2); | | X_178:bat[:oid,:str] := sql.bind(X_6,"sys","customer","c_phone",1); | | X_179 := sql.projectdelta(X_59,X_174,X_176,r1_244,X_178); | | X_180:bat[:oid,:str] := algebra.leftfetchjoinPath(X_140,r1_180,X_132,r1_126,X_179); | | X_181:bat[:oid,:str] := sql.bind(X_6,"sys","customer","c_comment",0); | | (X_183,r1_255) := sql.bind(X_6,"sys","customer","c_comment",2); | | X_185:bat[:oid,:str] := sql.bind(X_6,"sys","customer","c_comment",1); | | X_186 := sql.projectdelta(X_59,X_181,X_183,r1_255,X_185); | | X_187:bat[:oid,:str] := algebra.leftfetchjoinPath(X_140,r1_180,X_132,r1_126,X_186); | | language.pass(X_7); | | language.pass(X_27); | | language.pass(X_35); | | language.pass(X_71); | | language.pass(r1_75); | | language.pass(X_91); | | language.pass(X_18); | | language.pass(X_73); | | language.pass(X_61); | | language.pass(X_46); | | language.pass(X_90); | | language.pass(X_130); | | language.pass(X_98); | | language.pass(X_100); | | language.pass(r1_113); | | language.pass(X_84); | | language.pass(X_138); | | language.pass(X_134); | | language.pass(X_59); | | language.pass(X_140); | | language.pass(r1_180); | | language.pass(X_132); | | language.pass(r1_126); | | exit X_330; | | sql.resultSet(X_257,X_258,X_260,X_261,X_263,X_141,X_152,X_153,X_160,X_161,X_173,X_180,X_187); | | end user.s1_1; | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 177 tuples COMMIT; auto commit mode: on