START TRANSACTION; auto commit mode: off EXPLAIN select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval '63' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +===============================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:bte,A1:bte,A2:bte,A3:date,A4:lng):void; | | X_209:void := querylog.define("explain\n\n\nselect\n\tl_returnflag,\n\tl_linestatus,\n\tsum(l_quantity) as sum_qty,\n\tsum(l_extendedprice) as sum_base_price,\n\tsum(l_extendedprice * (1 - l_discount)) as sum_disc_price,\n\tsum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,\n\tavg(l_quantity) as avg_qty,\n\tavg(l_extendedprice) as avg_price,\n\tavg(l_discount) as avg_disc,\n\tcount(*) as count_order\nfrom\n\tlineitem\nwhere\n\tl_shipdate <= date \\'1998-12-01\\' - interval \\'63\\' day\ngroup by\n\tl_returnflag,\n\tl_linestatus\norder by\n\tl_returnflag,\n\tl_linestatus;","default_pipe",144); | | barrier X_246 := language.dataflow(); | | X_119 := bat.new(nil:oid,nil:str); | | X_127 := bat.append(X_119,"sys.lineitem"); | | X_137 := bat.append(X_127,"sys.lineitem"); | | X_143 := bat.append(X_137,"sys.L1"); | | X_153 := bat.append(X_143,"sys.L2"); | | X_160 := bat.append(X_153,"sys.L3"); | | X_168 := bat.append(X_160,"sys.L4"); | | X_176 := bat.append(X_168,"sys.L5"); | | X_186 := bat.append(X_176,"sys.L6"); | | X_193 := bat.append(X_186,"sys.L7"); | | X_200 := bat.append(X_193,"sys.L10"); | | X_122 := bat.new(nil:oid,nil:str); | | X_129 := bat.append(X_122,"l_returnflag"); | | X_138 := bat.append(X_129,"l_linestatus"); | | X_145 := bat.append(X_138,"sum_qty"); | | X_155 := bat.append(X_145,"sum_base_price"); | | X_162 := bat.append(X_155,"sum_disc_price"); | | X_170 := bat.append(X_162,"sum_charge"); | | X_178 := bat.append(X_170,"avg_qty"); | | X_188 := bat.append(X_178,"avg_price"); | | X_195 := bat.append(X_188,"avg_disc"); | | X_202 := bat.append(X_195,"count_order"); | | X_123 := bat.new(nil:oid,nil:str); | | X_131 := bat.append(X_123,"char"); | | X_140 := bat.append(X_131,"char"); | | X_147 := bat.append(X_140,"decimal"); | | X_157 := bat.append(X_147,"decimal"); | | X_164 := bat.append(X_157,"decimal"); | | X_172 := bat.append(X_164,"decimal"); | | X_180 := bat.append(X_172,"double"); | | X_190 := bat.append(X_180,"double"); | | X_197 := bat.append(X_190,"double"); | | X_204 := bat.append(X_197,"wrd"); | | X_124 := bat.new(nil:oid,nil:int); | | X_133 := bat.append(X_124,1); | | X_141 := bat.append(X_133,1); | | X_149 := bat.append(X_141,39); | | X_158 := bat.append(X_149,39); | | X_165 := bat.append(X_158,39); | | X_173 := bat.append(X_165,39); | | X_182 := bat.append(X_173,53); | | X_191 := bat.append(X_182,53); | | X_198 := bat.append(X_191,53); | | X_206 := bat.append(X_198,64); | | X_126 := bat.new(nil:oid,nil:int); | | X_135 := bat.append(X_126,0); | | X_142 := bat.append(X_135,0); | | X_151 := bat.append(X_142,2); | | X_159 := bat.append(X_151,2); | | X_166 := bat.append(X_159,4); | | X_174 := bat.append(X_166,6); | | X_184 := bat.append(X_174,0); | | X_192 := bat.append(X_184,0); | | X_199 := bat.append(X_192,0); | | X_208 := bat.append(X_199,0); | | X_6 := sql.mvc(); | | X_10:bat[:oid,:date] := sql.bind(X_6,"sys","lineitem","l_shipdate",0); | | X_7:bat[:oid,:oid] := sql.tid(X_6,"sys","lineitem"); | | X_18:date := mtime.date_sub_msec_interval(A3,A4); | | X_229 := algebra.thetasubselect(X_10,X_7,X_18,"<="); | | (X_13,r1_13) := sql.bind(X_6,"sys","lineitem","l_shipdate",2); | | X_230 := algebra.thetasubselect(r1_13,nil:bat[:oid,:oid],X_18,"<="); | | X_16:bat[:oid,:date] := sql.bind(X_6,"sys","lineitem","l_shipdate",1); | | X_232 := algebra.thetasubselect(X_16,X_7,X_18,"<="); | | X_19 := sql.subdelta(X_229,X_7,X_13,X_230,X_232); | | X_21:bat[:oid,:str] := sql.bind(X_6,"sys","lineitem","l_linestatus",0); | | (X_23,r1_27) := sql.bind(X_6,"sys","lineitem","l_linestatus",2); | | X_25:bat[:oid,:str] := sql.bind(X_6,"sys","lineitem","l_linestatus",1); | | X_26 := sql.projectdelta(X_19,X_21,X_23,r1_27,X_25); | | X_27:bat[:oid,:str] := sql.bind(X_6,"sys","lineitem","l_returnflag",0); | | (X_29,r1_35) := sql.bind(X_6,"sys","lineitem","l_returnflag",2); | | X_31:bat[:oid,:str] := sql.bind(X_6,"sys","lineitem","l_returnflag",1); | | X_32 := sql.projectdelta(X_19,X_27,X_29,r1_35,X_31); | | (X_33,r1_41,r2_41) := group.subgroup(X_32); | | (X_36,r1_44,r2_44) := group.subgroupdone(X_26,X_33); | | X_39 := algebra.leftfetchjoin(r1_44,X_26); | | X_40 := algebra.leftfetchjoin(r1_44,X_32); | | X_50:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_quantity",0); | | (X_55,r1_63) := sql.bind(X_6,"sys","lineitem","l_quantity",2); | | X_58:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_quantity",1); | | X_60 := sql.projectdelta(X_19,X_50,X_55,r1_63,X_58); | | X_61:bat[:oid,:hge] := aggr.subsum(X_60,X_36,r1_44,true,true); | | X_64:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_extendedprice",0); | | (X_66,r1_76) := sql.bind(X_6,"sys","lineitem","l_extendedprice",2); | | X_68:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_extendedprice",1); | | X_69 := sql.projectdelta(X_19,X_64,X_66,r1_76,X_68); | | X_70:bat[:oid,:hge] := aggr.subsum(X_69,X_36,r1_44,true,true); | | X_72 := calc.lng(A0,15,2); | | X_74:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_discount",0); | | (X_79,r1_96) := sql.bind(X_6,"sys","lineitem","l_discount",2); | | X_82:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_discount",1); | | X_84 := sql.projectdelta(X_19,X_74,X_79,r1_96,X_82); | | X_85:bat[:oid,:lng] := batcalc.-(X_72,X_84); | | X_86:bat[:oid,:hge] := batcalc.*(X_69,X_85); | | X_87:bat[:oid,:hge] := aggr.subsum(X_86,X_36,r1_44,true,true); | | X_90 := calc.lng(A1,15,2); | | X_91:bat[:oid,:lng] := batcalc.-(X_90,X_84); | | X_92:bat[:oid,:hge] := batcalc.*(X_69,X_91); | | X_93 := calc.lng(A2,15,2); | | X_94:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_tax",0); | | (X_96,r1_120) := sql.bind(X_6,"sys","lineitem","l_tax",2); | | X_98:bat[:oid,:lng] := sql.bind(X_6,"sys","lineitem","l_tax",1); | | X_99 := sql.projectdelta(X_19,X_94,X_96,r1_120,X_98); | | X_100:bat[:oid,:lng] := batcalc.+(X_93,X_99); | | X_101:bat[:oid,:hge] := batcalc.*(X_92,X_100); | | X_102:bat[:oid,:hge] := aggr.subsum(X_101,X_36,r1_44,true,true); | | X_104:bat[:oid,:dbl] := batcalc.dbl(2,X_60); | | X_106:bat[:oid,:dbl] := aggr.subavg(X_104,X_36,r1_44,true,true); | | X_108:bat[:oid,:dbl] := batcalc.dbl(2,X_69); | | X_109:bat[:oid,:dbl] := aggr.subavg(X_108,X_36,r1_44,true,true); | | X_111:bat[:oid,:dbl] := batcalc.dbl(2,X_84); | | X_112:bat[:oid,:dbl] := aggr.subavg(X_111,X_36,r1_44,true,true); | | X_115:bat[:oid,:wrd] := aggr.subcount(X_36,X_36,r1_44,false); | | (X_41,r1_49,r2_49) := algebra.subsort(X_40,false,false); | | (X_45,r1_53,r2_53) := algebra.subsort(X_39,r1_49,r2_49,false,false); | | X_48 := algebra.leftfetchjoin(r1_53,X_40); | | X_49 := algebra.leftfetchjoin(r1_53,X_39); | | X_63 := algebra.leftfetchjoin(r1_53,X_61); | | X_71 := algebra.leftfetchjoin(r1_53,X_70); | | X_89 := algebra.leftfetchjoin(r1_53,X_87); | | X_103 := algebra.leftfetchjoin(r1_53,X_102); | | X_107 := algebra.leftfetchjoin(r1_53,X_106); | | X_110 := algebra.leftfetchjoin(r1_53,X_109); | | X_114 := algebra.leftfetchjoin(r1_53,X_112); | | X_117 := algebra.leftfetchjoin(r1_53,X_115); | | language.pass(X_7); | | language.pass(X_26); | | language.pass(X_32); | | language.pass(X_19); | | language.pass(X_60); | | language.pass(X_69); | | language.pass(X_84); | | language.pass(X_36); | | language.pass(X_36); | | language.pass(r1_44); | | language.pass(X_40); | | language.pass(X_39); | | language.pass(r1_53); | | exit X_246; | | sql.resultSet(X_200,X_202,X_204,X_206,X_208,X_48,X_49,X_63,X_71,X_89,X_103,X_107,X_110,X_114,X_117); | | end user.s1_1; | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 144 tuples COMMIT; auto commit mode: on