START TRANSACTION; auto commit mode: off EXPLAIN select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#24' and p_container = 'SM JAR' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey ); +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +=============================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:bte,A1:str,A2:str,A3:dbl):void; | | X_109:void := querylog.define("explain\n\n\nselect\n\tsum(l_extendedprice) / 7.0 as avg_yearly\nfrom\n\tlineitem,\n\tpart\nwhere\n\tp_partkey = l_partkey\n\tand p_brand = \\'Brand#24\\'\n\tand p_container = \\'SM JAR\\'\n\tand l_quantity < (\n\t\tselect\n\t\t\t0.2 * avg(l_quantity)\n\t\tfrom\n\t\t\tlineitem\n\t\twhere\n\t\t\tl_partkey = p_partkey\n\t);","default_pipe",72); | | barrier X_148 := language.dataflow(); | | X_5 := sql.mvc(); | | X_6:bat[:oid,:oid] := sql.tid(X_5,"sys","lineitem"); | | X_9:bat[:oid,:int] := sql.bind(X_5,"sys","lineitem","l_partkey",0); | | (X_12,r1_12) := sql.bind(X_5,"sys","lineitem","l_partkey",2); | | X_15:bat[:oid,:int] := sql.bind(X_5,"sys","lineitem","l_partkey",1); | | X_17 := sql.projectdelta(X_6,X_9,X_12,r1_12,X_15); | | X_20:bat[:oid,:str] := sql.bind(X_5,"sys","part","p_container",0); | | X_25:bat[:oid,:str] := sql.bind(X_5,"sys","part","p_brand",0); | | X_18:bat[:oid,:oid] := sql.tid(X_5,"sys","part"); | | X_131 := algebra.subselect(X_25,X_18,A1,A1,true,false,false); | | (X_27,r1_31) := sql.bind(X_5,"sys","part","p_brand",2); | | X_132 := algebra.subselect(r1_31,nil:bat[:oid,:oid],A1,A1,true,false,false); | | X_29:bat[:oid,:str] := sql.bind(X_5,"sys","part","p_brand",1); | | X_134 := algebra.subselect(X_29,X_18,A1,A1,true,false,false); | | X_30 := sql.subdelta(X_131,X_18,X_27,X_132,X_134); | | X_135 := algebra.subselect(X_20,X_30,A2,A2,true,false,false); | | (X_22,r1_23) := sql.bind(X_5,"sys","part","p_container",2); | | X_136 := algebra.subselect(r1_23,nil:bat[:oid,:oid],A2,A2,true,false,false); | | X_24:bat[:oid,:str] := sql.bind(X_5,"sys","part","p_container",1); | | X_138 := algebra.subselect(X_24,X_30,A2,A2,true,false,false); | | X_33 := sql.subdelta(X_135,X_30,X_22,X_136,X_138); | | X_34:bat[:oid,:int] := sql.bind(X_5,"sys","part","p_partkey",0); | | (X_38,r1_45) := sql.bind(X_5,"sys","part","p_partkey",2); | | X_41:bat[:oid,:int] := sql.bind(X_5,"sys","part","p_partkey",1); | | X_43 := sql.projectdelta(X_33,X_34,X_38,r1_45,X_41); | | (X_45,r1_62) := algebra.subjoin(X_43,X_17,nil:BAT,nil:BAT,false,nil:lng); | | X_49 := algebra.leftfetchjoin(X_45,X_43); | | (X_50,r1_67) := algebra.subjoin(X_17,X_49,nil:BAT,nil:BAT,false,nil:lng); | | X_52:bat[:oid,:int] := sql.bind(X_5,"sys","lineitem","l_orderkey",0); | | (X_54,r1_71) := sql.bind(X_5,"sys","lineitem","l_orderkey",2); | | X_56:bat[:oid,:int] := sql.bind(X_5,"sys","lineitem","l_orderkey",1); | | X_57 := sql.projectdelta(X_6,X_52,X_54,r1_71,X_56); | | X_58 := algebra.leftfetchjoin(r1_62,X_57); | | X_59:bat[:oid,:oid] := batcalc.identity(X_58); | | X_60 := algebra.leftfetchjoin(r1_67,X_59); | | (X_61,r1_81,r2_81) := group.subgroupdone(X_60); | | X_64:bat[:oid,:lng] := sql.bind(X_5,"sys","lineitem","l_quantity",0); | | (X_68,r1_88) := sql.bind(X_5,"sys","lineitem","l_quantity",2); | | X_72:bat[:oid,:lng] := sql.bind(X_5,"sys","lineitem","l_quantity",1); | | X_74 := sql.projectdelta(X_6,X_64,X_68,r1_88,X_72); | | X_75:bat[:oid,:lng] := algebra.leftfetchjoinPath(r1_81,r1_67,r1_62,X_74); | | X_77 := algebra.leftfetchjoin(X_50,X_74); | | X_78:bat[:oid,:dbl] := batcalc.dbl(2,X_77); | | X_79:bat[:oid,:dbl] := aggr.subavg(X_78,X_61,r1_81,true,true); | | X_85:bat[:oid,:lng] := sql.bind(X_5,"sys","lineitem","l_extendedprice",0); | | (X_89,r1_123) := sql.bind(X_5,"sys","lineitem","l_extendedprice",2); | | X_93:bat[:oid,:lng] := sql.bind(X_5,"sys","lineitem","l_extendedprice",1); | | X_95 := sql.projectdelta(X_6,X_85,X_89,r1_123,X_93); | | X_76:bat[:oid,:dbl] := batcalc.dbl(2,X_75); | | X_81:bat[:oid,:dbl] := batcalc.*(A3,X_79); | | X_82:bat[:oid,:bit] := batcalc.<(X_76,X_81); | | X_83 := algebra.subselect(X_82,true,true,true,true,false); | | X_96:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_83,r1_81,r1_67,r1_62,X_95); | | X_97:hge := aggr.sum(X_96); | | X_98 := calc.hge(2,X_97,39,5); | | X_101 := calc.hge(1,A0,39,2); | | X_102:hge := calc./(X_98,X_101); | | language.pass(X_18); | | language.pass(X_30); | | language.pass(X_43); | | language.pass(X_17); | | language.pass(X_74); | | language.pass(X_6); | | language.pass(r1_81); | | language.pass(r1_67); | | language.pass(r1_62); | | exit X_148; | | sql.resultSet("sys.L3","avg_yearly","decimal",39,3,10,X_102); | | end user.s1_1; | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 72 tuples COMMIT; auto commit mode: on