START TRANSACTION; auto commit mode: off EXPLAIN select c_count, count(*) as custdist from ( select c_custkey, count(o_orderkey) from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%unusual%packages%' group by c_custkey ) as c_orders (c_custkey, c_count) group by c_count order by custdist desc, c_count desc; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +======================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:str):void; | | X_113:void := querylog.define("explain\n\n\nselect\n\tc_count,\n\tcount(*) as custdist\nfrom\n\t(\n\t\tselect\n\t\t\tc_custkey,\n\t\t\tcount(o_orderkey)\n\t\tfrom\n\t\t\tcustomer left outer join orders on\n\t\t\t\tc_custkey = o_custkey\n\t\t\t\tand o_comment not like \\'%unusual%packages%\\'\n\t\tgroup by\n\t\t\tc_custkey\n\t) as c_orders (c_custkey, c_count)\ngroup by\n\tc_count\norder by\n\tcustdist desc,\n\tc_count desc;","default_pipe",80); | | barrier X_142 := language.dataflow(); | | X_89 := bat.new(nil:oid,nil:str); | | X_96 := bat.append(X_89,".c_orders"); | | X_106 := bat.append(X_96,".L2"); | | X_91 := bat.new(nil:oid,nil:str); | | X_98 := bat.append(X_91,"c_count"); | | X_108 := bat.append(X_98,"custdist"); | | X_92 := bat.new(nil:oid,nil:str); | | X_100 := bat.append(X_92,"wrd"); | | X_110 := bat.append(X_100,"wrd"); | | X_93 := bat.new(nil:oid,nil:int); | | X_102 := bat.append(X_93,64); | | X_111 := bat.append(X_102,64); | | X_95 := bat.new(nil:oid,nil:int); | | X_104 := bat.append(X_95,0); | | X_112 := bat.append(X_104,0); | | X_3:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_2 := sql.mvc(); | | X_6:bat[:oid,:oid] := sql.tid(X_2,"sys","customer"); | | X_9:bat[:oid,:oid] := sql.tid(X_2,"sys","orders"); | | X_11:bat[:oid,:oid] := sql.bind_idxbat(X_2,"sys","orders","order_custkey",0); | | (X_14,r1_14) := sql.bind_idxbat(X_2,"sys","orders","order_custkey",2); | | X_17:bat[:oid,:oid] := sql.bind_idxbat(X_2,"sys","orders","order_custkey",1); | | X_19 := sql.delta(X_11,X_14,r1_14,X_17); | | X_20 := algebra.leftfetchjoin(X_9,X_19); | | (X_21,r1_21) := algebra.subjoin(X_6,X_20,nil:BAT,nil:BAT,false,nil:lng); | | X_26:bat[:oid,:str] := sql.bind(X_2,"sys","orders","o_comment",0); | | (X_28,r1_28) := sql.bind(X_2,"sys","orders","o_comment",2); | | X_30:bat[:oid,:str] := sql.bind(X_2,"sys","orders","o_comment",1); | | X_31 := sql.delta(X_26,X_28,r1_28,X_30); | | X_32:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_21,X_9,X_31); | | X_34 := algebra.likesubselect(X_32,nil:BAT,A0,"",true); | | X_36:bat[:oid,:int] := sql.bind(X_2,"sys","orders","o_orderkey",0); | | (X_39,r1_43) := sql.bind(X_2,"sys","orders","o_orderkey",2); | | X_42:bat[:oid,:int] := sql.bind(X_2,"sys","orders","o_orderkey",1); | | X_43 := sql.delta(X_36,X_39,r1_43,X_42); | | X_44:bat[:oid,:int] := algebra.leftfetchjoinPath(X_34,r1_21,X_9,X_43); | | X_45 := bat.append(X_3,X_44,true); | | X_46:bat[:oid,:int] := sql.bind(X_2,"sys","customer","c_custkey",0); | | (X_50,r1_55) := sql.bind(X_2,"sys","customer","c_custkey",2); | | X_53:bat[:oid,:int] := sql.bind(X_2,"sys","customer","c_custkey",1); | | X_55 := sql.delta(X_46,X_50,r1_55,X_53); | | X_56 := algebra.leftfetchjoin(X_6,X_55); | | X_57 := bat.mirror(X_56); | | X_58 := algebra.leftfetchjoin(X_34,X_21); | | X_59 := algebra.tdiff(X_57,X_58); | | X_61 := algebra.project(X_59,nil:int); | | X_62 := bat.append(X_45,X_61,true); | | X_63:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_65 := algebra.leftfetchjoin(X_58,X_56); | | X_66 := bat.append(X_63,X_65,true); | | X_68 := algebra.leftfetchjoin(X_59,X_56); | | X_69 := bat.append(X_66,X_68,true); | | (X_70,r1_76,r2_76) := group.subgroupdone(X_69); | | X_73:bat[:oid,:wrd] := aggr.subcount(X_62,X_70,r1_76,true); | | (X_74,r1_80,r2_80) := group.subgroupdone(X_73); | | X_77 := algebra.leftfetchjoin(r1_80,X_73); | | X_78:bat[:oid,:wrd] := aggr.subcount(X_74,X_74,r1_80,false); | | (X_80,r1_86,r2_86) := algebra.subsort(X_78,true,false); | | (X_83,r1_89,r2_89) := algebra.subsort(X_77,r1_86,r2_86,true,false); | | X_86 := algebra.leftfetchjoin(r1_89,X_77); | | X_87 := algebra.leftfetchjoin(r1_89,X_78); | | language.pass(r1_21); | | language.pass(X_9); | | language.pass(X_6); | | language.pass(X_34); | | language.pass(X_58); | | language.pass(X_59); | | language.pass(X_56); | | language.pass(X_73); | | language.pass(X_74); | | language.pass(r1_80); | | language.pass(X_77); | | language.pass(r1_89); | | language.pass(X_78); | | exit X_142; | | sql.resultSet(X_106,X_108,X_110,X_111,X_112,X_86,X_87); | | end user.s1_1; | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 80 tuples COMMIT; auto commit mode: on