START TRANSACTION; auto commit mode: off EXPLAIN select 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue from lineitem, part where l_partkey = p_partkey and l_shipdate >= date '1994-02-01' and l_shipdate < date '1994-02-01' + interval '1' month; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +=============================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:sht,A1:str,A2:bte,A3:bte,A4:bte,A5:date,A6:date,A7:int):void; | | X_103:void := querylog.define("explain\n\n\nselect\n\t100.00 * sum(case\n\t\twhen p_type like \\'PROMO%\\'\n\t\t\tthen l_extendedprice * (1 - l_discount)\n\t\telse 0\n\tend) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\nfrom\n\tlineitem,\n\tpart\nwhere\n\tl_partkey = p_partkey\n\tand l_shipdate >= date \\'1994-02-01\\'\n\tand l_shipdate < date \\'1994-02-01\\' + interval \\'1\\' month;","default_pipe",64); | | barrier X_135 := language.dataflow(); | | X_9 := sql.mvc(); | | X_13:bat[:oid,:date] := sql.bind(X_9,"sys","lineitem","l_shipdate",0); | | X_10:bat[:oid,:oid] := sql.tid(X_9,"sys","lineitem"); | | X_21:date := mtime.addmonths(A6,A7); | | X_124 := algebra.subselect(X_13,X_10,A5,X_21,true,false,false); | | (X_16,r1_17) := sql.bind(X_9,"sys","lineitem","l_shipdate",2); | | X_125 := algebra.subselect(r1_17,nil:bat[:oid,:oid],A5,X_21,true,false,false); | | X_19:bat[:oid,:date] := sql.bind(X_9,"sys","lineitem","l_shipdate",1); | | X_127 := algebra.subselect(X_19,X_10,A5,X_21,true,false,false); | | X_22 := sql.subdelta(X_124,X_10,X_16,X_125,X_127); | | X_25:bat[:oid,:int] := sql.bind(X_9,"sys","lineitem","l_partkey",0); | | (X_27,r1_33) := sql.bind(X_9,"sys","lineitem","l_partkey",2); | | X_29:bat[:oid,:int] := sql.bind(X_9,"sys","lineitem","l_partkey",1); | | X_30 := sql.projectdelta(X_22,X_25,X_27,r1_33,X_29); | | X_31:bat[:oid,:oid] := sql.tid(X_9,"sys","part"); | | X_33:bat[:oid,:int] := sql.bind(X_9,"sys","part","p_partkey",0); | | (X_35,r1_43) := sql.bind(X_9,"sys","part","p_partkey",2); | | X_38:bat[:oid,:int] := sql.bind(X_9,"sys","part","p_partkey",1); | | X_39 := sql.projectdelta(X_31,X_33,X_35,r1_43,X_38); | | (X_40,r1_49) := algebra.subjoin(X_30,X_39,nil:BAT,nil:BAT,false,nil:lng); | | X_44:bat[:oid,:str] := sql.bind(X_9,"sys","part","p_type",0); | | (X_47,r1_56) := sql.bind(X_9,"sys","part","p_type",2); | | X_50:bat[:oid,:str] := sql.bind(X_9,"sys","part","p_type",1); | | X_52 := sql.projectdelta(X_31,X_44,X_47,r1_56,X_50); | | X_53 := algebra.leftfetchjoin(r1_49,X_52); | | X_54:bat[:oid,:bit] := batalgebra.like(X_53,A1); | | X_55:bat[:oid,:bit] := batcalc.isnil(X_54); | | X_57:bat[:oid,:bit] := batcalc.ifthenelse(X_55,false,X_54); | | X_58:bat[:oid,:lng] := sql.bind(X_9,"sys","lineitem","l_extendedprice",0); | | (X_62,r1_81) := sql.bind(X_9,"sys","lineitem","l_extendedprice",2); | | X_64:bat[:oid,:lng] := sql.bind(X_9,"sys","lineitem","l_extendedprice",1); | | X_65 := sql.projectdelta(X_22,X_58,X_62,r1_81,X_64); | | X_66 := algebra.leftfetchjoin(X_40,X_65); | | X_69 := calc.lng(A2,15,2); | | X_71:bat[:oid,:lng] := sql.bind(X_9,"sys","lineitem","l_discount",0); | | (X_74,r1_98) := sql.bind(X_9,"sys","lineitem","l_discount",2); | | X_76:bat[:oid,:lng] := sql.bind(X_9,"sys","lineitem","l_discount",1); | | X_78 := sql.projectdelta(X_22,X_71,X_74,r1_98,X_76); | | X_79 := algebra.leftfetchjoin(X_40,X_78); | | X_80:bat[:oid,:lng] := batcalc.-(X_69,X_79); | | X_81:bat[:oid,:hge] := batcalc.*(X_66,X_80); | | X_82 := calc.hge(A3,33,4); | | X_84:bat[:oid,:hge] := batcalc.ifthenelse(X_57,X_81,X_82); | | X_85:hge := aggr.sum(X_84); | | X_86:hge := calc.*(A0,X_85); | | X_87 := calc.hge(5,X_86,39,9); | | X_92 := calc.lng(A4,15,2); | | X_94:bat[:oid,:lng] := batcalc.-(X_92,X_79); | | X_95:bat[:oid,:hge] := batcalc.*(X_66,X_94); | | X_96:hge := aggr.sum(X_95); | | X_97:hge := calc./(X_87,X_96); | | language.pass(X_10); | | language.pass(X_31); | | language.pass(X_54); | | language.pass(X_22); | | language.pass(X_40); | | language.pass(X_79); | | language.pass(X_66); | | exit X_135; | | sql.resultSet(".L2","promo_revenue","decimal",39,5,10,X_97); | | end user.s1_1; | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 64 tuples COMMIT; auto commit mode: on