START TRANSACTION; auto commit mode: off EXPLAIN select o_orderpriority, count(*) as order_count from orders where o_orderdate >= date '1995-09-01' and o_orderdate < date '1995-09-01' + interval '3' month and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:date,A1:date,A2:int):void; | | X_113:void := querylog.define("explain\n\n\nselect\n\to_orderpriority,\n\tcount(*) as order_count\nfrom\n\torders\nwhere\n\to_orderdate >= date \\'1995-09-01\\'\n\tand o_orderdate < date \\'1995-09-01\\' + interval \\'3\\' month\n\tand exists (\n\t\tselect\n\t\t\t*\n\t\tfrom\n\t\t\tlineitem\n\t\twhere\n\t\t\tl_orderkey = o_orderkey\n\t\t\tand l_commitdate < l_receiptdate\n\t)\ngroup by\n\to_orderpriority\norder by\n\to_orderpriority;","default_pipe",76); | | barrier X_143 := language.dataflow(); | | X_87 := bat.new(nil:oid,nil:str); | | X_95 := bat.append(X_87,"sys.orders"); | | X_104 := bat.append(X_95,"sys.L1"); | | X_90 := bat.new(nil:oid,nil:str); | | X_97 := bat.append(X_90,"o_orderpriority"); | | X_106 := bat.append(X_97,"order_count"); | | X_91 := bat.new(nil:oid,nil:str); | | X_98 := bat.append(X_91,"char"); | | X_108 := bat.append(X_98,"wrd"); | | X_92 := bat.new(nil:oid,nil:int); | | X_100 := bat.append(X_92,15); | | X_110 := bat.append(X_100,64); | | X_94 := bat.new(nil:oid,nil:int); | | X_102 := bat.append(X_94,0); | | X_112 := bat.append(X_102,0); | | X_4 := sql.mvc(); | | X_5:bat[:oid,:oid] := sql.tid(X_4,"sys","orders"); | | X_8:bat[:oid,:date] := sql.bind(X_4,"sys","orders","o_orderdate",0); | | (X_11,r1_11) := sql.bind(X_4,"sys","orders","o_orderdate",2); | | X_14:bat[:oid,:date] := sql.bind(X_4,"sys","orders","o_orderdate",1); | | X_16 := sql.delta(X_8,X_11,r1_11,X_14); | | X_17 := algebra.leftfetchjoin(X_5,X_16); | | X_18:date := mtime.addmonths(A1,A2); | | X_19 := algebra.subselect(X_17,A0,X_18,true,false,false); | | X_22:bat[:oid,:int] := sql.bind(X_4,"sys","orders","o_orderkey",0); | | (X_24,r1_27) := sql.bind(X_4,"sys","orders","o_orderkey",2); | | X_26:bat[:oid,:int] := sql.bind(X_4,"sys","orders","o_orderkey",1); | | X_27 := sql.delta(X_22,X_24,r1_27,X_26); | | X_28:bat[:oid,:int] := algebra.leftfetchjoinPath(X_19,X_5,X_27); | | X_29 := bat.mirror(X_28); | | X_30:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem"); | | X_32:bat[:oid,:date] := sql.bind(X_4,"sys","lineitem","l_commitdate",0); | | (X_34,r1_38) := sql.bind(X_4,"sys","lineitem","l_commitdate",2); | | X_37:bat[:oid,:date] := sql.bind(X_4,"sys","lineitem","l_commitdate",1); | | X_38 := sql.delta(X_32,X_34,r1_38,X_37); | | X_39 := algebra.leftfetchjoin(X_30,X_38); | | X_40:bat[:oid,:date] := sql.bind(X_4,"sys","lineitem","l_receiptdate",0); | | (X_43,r1_47) := sql.bind(X_4,"sys","lineitem","l_receiptdate",2); | | X_46:bat[:oid,:date] := sql.bind(X_4,"sys","lineitem","l_receiptdate",1); | | X_48 := sql.delta(X_40,X_43,r1_47,X_46); | | X_49 := algebra.leftfetchjoin(X_30,X_48); | | X_50:bat[:oid,:bit] := batcalc.<(X_39,X_49); | | X_51 := algebra.subselect(X_50,true,true,true,true,false); | | X_54:bat[:oid,:int] := sql.bind(X_4,"sys","lineitem","l_orderkey",0); | | (X_56,r1_62) := sql.bind(X_4,"sys","lineitem","l_orderkey",2); | | X_58:bat[:oid,:int] := sql.bind(X_4,"sys","lineitem","l_orderkey",1); | | X_59 := sql.delta(X_54,X_56,r1_62,X_58); | | X_60:bat[:oid,:int] := algebra.leftfetchjoinPath(X_51,X_30,X_59); | | (X_61,r1_68) := algebra.subjoin(X_28,X_60,nil:BAT,nil:BAT,false,nil:lng); | | X_65 := algebra.tinter(X_29,X_61); | | X_66:bat[:oid,:str] := sql.bind(X_4,"sys","orders","o_orderpriority",0); | | (X_70,r1_77) := sql.bind(X_4,"sys","orders","o_orderpriority",2); | | X_72:bat[:oid,:str] := sql.bind(X_4,"sys","orders","o_orderpriority",1); | | X_73 := sql.delta(X_66,X_70,r1_77,X_72); | | X_74:bat[:oid,:str] := algebra.leftfetchjoinPath(X_65,X_19,X_5,X_73); | | (X_75,r1_84,r2_84) := group.subgroupdone(X_74); | | X_78 := algebra.leftfetchjoin(r1_84,X_74); | | X_83:bat[:oid,:wrd] := aggr.subcount(X_75,X_75,r1_84,false); | | (X_79,r1_88,r2_88) := algebra.subsort(X_78,false,false); | | X_82 := algebra.leftfetchjoin(r1_88,X_78); | | X_85 := algebra.leftfetchjoin(r1_88,X_83); | | language.pass(X_30); | | language.pass(X_28); | | language.pass(X_19); | | language.pass(X_5); | | language.pass(X_74); | | language.pass(X_75); | | language.pass(r1_84); | | language.pass(X_78); | | language.pass(r1_88); | | exit X_143; | | sql.resultSet(X_104,X_106,X_108,X_110,X_112,X_82,X_85); | | end user.s1_1; | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 76 tuples COMMIT; auto commit mode: on