START TRANSACTION; auto commit mode: off EXPLAIN select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey ) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +==========================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_2(A0:str,A1:str):void; | | X_240:void := querylog.define("explain\n\n\nselect\n\ts_name,\n\tcount(*) as numwait\nfrom\n\tsupplier,\n\tlineitem l1,\n\torders,\n\tnation\nwhere\n\ts_suppkey = l1.l_suppkey\n\tand o_orderkey = l1.l_orderkey\n\tand o_orderstatus = \\'F\\'\n\tand l1.l_receiptdate > l1.l_commitdate\n\tand exists (\n\t\tselect\n\t\t\t*\n\t\tfrom\n\t\t\tlineitem l2\n\t\twhere\n\t\t\tl2.l_orderkey = l1.l_orderkey\n\t\t\tand l2.l_suppkey <> l1.l_suppkey\n\t)\n\tand not exists (\n\t\tselect\n\t\t\t*\n\t\tfrom\n\t\t\tlineitem l3\n\t\twhere\n\t\t\tl3.l_orderkey = l1.l_orderkey\n\t\t\tand l3.l_suppkey <> l1.l_suppkey\n\t\t\tand l3.l_receiptdate > l3.l_commitdate\n\t)\n\tand s_nationkey = n_nationkey\n\tand n_name = \\'VIETNAM\\'\ngroup by\n\ts_name\norder by\n\tnumwait desc,\n\ts_name\nlimit 100;","default_pipe",175); | | barrier X_303 := language.dataflow(); | | X_213 := bat.new(nil:oid,nil:str); | | X_221 := bat.append(X_213,"sys.supplier"); | | X_231 := bat.append(X_221,"sys.L1"); | | X_216 := bat.new(nil:oid,nil:str); | | X_223 := bat.append(X_216,"s_name"); | | X_233 := bat.append(X_223,"numwait"); | | X_217 := bat.new(nil:oid,nil:str); | | X_225 := bat.append(X_217,"char"); | | X_235 := bat.append(X_225,"wrd"); | | X_218 := bat.new(nil:oid,nil:int); | | X_227 := bat.append(X_218,25); | | X_237 := bat.append(X_227,64); | | X_220 := bat.new(nil:oid,nil:int); | | X_229 := bat.append(X_220,0); | | X_239 := bat.append(X_229,0); | | X_3 := sql.mvc(); | | X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","orders"); | | X_7:bat[:oid,:str] := sql.bind(X_3,"sys","orders","o_orderstatus",0); | | (X_10,r1_10) := sql.bind(X_3,"sys","orders","o_orderstatus",2); | | X_13:bat[:oid,:str] := sql.bind(X_3,"sys","orders","o_orderstatus",1); | | X_15 := sql.delta(X_7,X_10,r1_10,X_13); | | X_16 := algebra.leftfetchjoin(X_4,X_15); | | X_17 := algebra.subselect(X_16,A0,A0,true,false,false); | | X_20:bat[:oid,:int] := sql.bind(X_3,"sys","orders","o_orderkey",0); | | (X_22,r1_23) := sql.bind(X_3,"sys","orders","o_orderkey",2); | | X_24:bat[:oid,:int] := sql.bind(X_3,"sys","orders","o_orderkey",1); | | X_25 := sql.delta(X_20,X_22,r1_23,X_24); | | X_26:bat[:oid,:int] := algebra.leftfetchjoinPath(X_17,X_4,X_25); | | X_27:bat[:oid,:oid] := sql.tid(X_3,"sys","lineitem"); | | X_29:bat[:oid,:date] := sql.bind(X_3,"sys","lineitem","l_receiptdate",0); | | (X_31,r1_33) := sql.bind(X_3,"sys","lineitem","l_receiptdate",2); | | X_33:bat[:oid,:date] := sql.bind(X_3,"sys","lineitem","l_receiptdate",1); | | X_34 := sql.delta(X_29,X_31,r1_33,X_33); | | X_35 := algebra.leftfetchjoin(X_27,X_34); | | X_36:bat[:oid,:date] := sql.bind(X_3,"sys","lineitem","l_commitdate",0); | | (X_39,r1_41) := sql.bind(X_3,"sys","lineitem","l_commitdate",2); | | X_41:bat[:oid,:date] := sql.bind(X_3,"sys","lineitem","l_commitdate",1); | | X_42 := sql.delta(X_36,X_39,r1_41,X_41); | | X_43 := algebra.leftfetchjoin(X_27,X_42); | | X_44:bat[:oid,:bit] := batcalc.>(X_35,X_43); | | X_45 := algebra.subselect(X_44,true,true,true,true,false); | | X_46:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_orderkey",0); | | (X_49,r1_53) := sql.bind(X_3,"sys","lineitem","l_orderkey",2); | | X_52:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_orderkey",1); | | X_54 := sql.delta(X_46,X_49,r1_53,X_52); | | X_55 := algebra.leftfetchjoin(X_27,X_54); | | X_56 := algebra.leftfetchjoin(X_45,X_55); | | (X_57,r1_61) := algebra.subjoin(X_26,X_56,nil:BAT,nil:BAT,false,nil:lng); | | X_62:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_suppkey",0); | | (X_65,r1_69) := sql.bind(X_3,"sys","lineitem","l_suppkey",2); | | X_67:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_suppkey",1); | | X_69 := sql.delta(X_62,X_65,r1_69,X_67); | | X_70 := algebra.leftfetchjoin(X_27,X_69); | | X_71:bat[:oid,:int] := algebra.leftfetchjoinPath(r1_61,X_45,X_70); | | X_72:bat[:oid,:oid] := sql.tid(X_3,"sys","supplier"); | | X_74:bat[:oid,:int] := sql.bind(X_3,"sys","supplier","s_suppkey",0); | | (X_76,r1_81) := sql.bind(X_3,"sys","supplier","s_suppkey",2); | | X_78:bat[:oid,:int] := sql.bind(X_3,"sys","supplier","s_suppkey",1); | | X_79 := sql.delta(X_74,X_76,r1_81,X_78); | | X_80 := algebra.leftfetchjoin(X_72,X_79); | | (X_81,r1_86) := algebra.subjoin(X_71,X_80,nil:BAT,nil:BAT,false,nil:lng); | | X_83:bat[:oid,:oid] := sql.bind_idxbat(X_3,"sys","supplier","supplier_nationkey",0); | | (X_86,r1_91) := sql.bind_idxbat(X_3,"sys","supplier","supplier_nationkey",2); | | X_89:bat[:oid,:oid] := sql.bind_idxbat(X_3,"sys","supplier","supplier_nationkey",1); | | X_91 := sql.delta(X_83,X_86,r1_91,X_89); | | X_92:bat[:oid,:oid] := algebra.leftfetchjoinPath(r1_86,X_72,X_91); | | X_93:bat[:oid,:oid] := sql.tid(X_3,"sys","nation"); | | X_95:bat[:oid,:str] := sql.bind(X_3,"sys","nation","n_name",0); | | (X_97,r1_103) := sql.bind(X_3,"sys","nation","n_name",2); | | X_100:bat[:oid,:str] := sql.bind(X_3,"sys","nation","n_name",1); | | X_101 := sql.delta(X_95,X_97,r1_103,X_100); | | X_102 := algebra.leftfetchjoin(X_93,X_101); | | X_103 := algebra.subselect(X_102,A1,A1,true,false,false); | | X_106 := algebra.leftfetchjoin(X_103,X_93); | | (X_107,r1_114) := algebra.subjoin(X_92,X_106,nil:BAT,nil:BAT,false,nil:lng); | | X_111:bat[:oid,:int] := algebra.leftfetchjoinPath(X_107,r1_86,X_80); | | X_112 := bat.mirror(X_111); | | X_113:bat[:oid,:int] := algebra.leftfetchjoinPath(X_107,X_81,r1_61,X_56); | | (X_114,r1_136) := algebra.subjoin(X_113,X_55,nil:BAT,nil:BAT,false,nil:lng); | | X_117 := algebra.leftfetchjoin(r1_136,X_70); | | X_118:bat[:oid,:int] := algebra.leftfetchjoinPath(X_107,X_81,X_71); | | X_119 := algebra.leftfetchjoin(X_114,X_118); | | X_120:bat[:oid,:bit] := batcalc.!=(X_117,X_119); | | X_121 := algebra.subselect(X_120,true,true,true,true,false); | | X_124 := algebra.leftfetchjoin(X_121,X_114); | | X_125 := algebra.tinter(X_112,X_124); | | X_126 := algebra.leftfetchjoin(X_125,X_111); | | X_127 := bat.mirror(X_126); | | X_128 := algebra.leftfetchjoin(X_125,X_113); | | X_129:bat[:oid,:oid] := sql.tid(X_3,"sys","lineitem"); | | X_131:bat[:oid,:date] := sql.bind(X_3,"sys","lineitem","l_receiptdate",0); | | (X_134,r1_166) := sql.bind(X_3,"sys","lineitem","l_receiptdate",2); | | X_137:bat[:oid,:date] := sql.bind(X_3,"sys","lineitem","l_receiptdate",1); | | X_139 := sql.delta(X_131,X_134,r1_166,X_137); | | X_140 := algebra.leftfetchjoin(X_129,X_139); | | X_141:bat[:oid,:date] := sql.bind(X_3,"sys","lineitem","l_commitdate",0); | | (X_144,r1_176) := sql.bind(X_3,"sys","lineitem","l_commitdate",2); | | X_146:bat[:oid,:date] := sql.bind(X_3,"sys","lineitem","l_commitdate",1); | | X_147 := sql.delta(X_141,X_144,r1_176,X_146); | | X_148 := algebra.leftfetchjoin(X_129,X_147); | | X_149:bat[:oid,:bit] := batcalc.>(X_140,X_148); | | X_150 := algebra.subselect(X_149,true,true,true,true,false); | | X_151:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_orderkey",0); | | (X_153,r1_186) := sql.bind(X_3,"sys","lineitem","l_orderkey",2); | | X_155:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_orderkey",1); | | X_156 := sql.delta(X_151,X_153,r1_186,X_155); | | X_157:bat[:oid,:int] := algebra.leftfetchjoinPath(X_150,X_129,X_156); | | (X_158,r1_192) := algebra.subjoin(X_128,X_157,nil:BAT,nil:BAT,false,nil:lng); | | X_163:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_suppkey",0); | | (X_167,r1_201) := sql.bind(X_3,"sys","lineitem","l_suppkey",2); | | X_170:bat[:oid,:int] := sql.bind(X_3,"sys","lineitem","l_suppkey",1); | | X_172 := sql.delta(X_163,X_167,r1_201,X_170); | | X_173:bat[:oid,:int] := algebra.leftfetchjoinPath(r1_192,X_150,X_129,X_172); | | X_174:bat[:oid,:int] := algebra.leftfetchjoinPath(X_158,X_125,X_118); | | X_175:bat[:oid,:bit] := batcalc.!=(X_173,X_174); | | X_176 := algebra.subselect(X_175,true,true,true,true,false); | | X_178 := algebra.leftfetchjoin(X_176,X_158); | | X_179 := algebra.tdiff(X_127,X_178); | | X_180:bat[:oid,:str] := sql.bind(X_3,"sys","supplier","s_name",0); | | (X_184,r1_223) := sql.bind(X_3,"sys","supplier","s_name",2); | | X_186:bat[:oid,:str] := sql.bind(X_3,"sys","supplier","s_name",1); | | X_187 := sql.delta(X_180,X_184,r1_223,X_186); | | X_188:bat[:oid,:str] := algebra.leftfetchjoinPath(X_179,X_125,X_107,r1_86,X_72,X_187); | | (X_189,r1_232,r2_232) := group.subgroupdone(X_188); | | X_192 := algebra.leftfetchjoin(r1_232,X_188); | | X_193:bat[:oid,:wrd] := aggr.subcount(X_189,X_189,r1_232,false); | | (X_196,r1_243) := algebra.firstn(X_193,100:wrd,false,false); | | X_198 := algebra.firstn(X_192,X_196,r1_243,100:wrd,true,false); | | X_199 := algebra.leftfetchjoin(X_198,X_192); | | X_200 := algebra.leftfetchjoin(X_198,X_193); | | (X_201,r1_250,r2_250) := algebra.subsort(X_200,true,false); | | (X_205,r1_254,r2_254) := algebra.subsort(X_199,r1_250,r2_250,false,false); | | X_208 := algebra.leftfetchjoin(r1_254,X_199); | | X_209 := algebra.subslice(X_208,0,99:wrd); | | X_210 := algebra.leftfetchjoin(X_209,X_208); | | X_211:bat[:oid,:wrd] := algebra.leftfetchjoinPath(X_209,r1_254,X_200); | | language.pass(X_4); | | language.pass(X_27); | | language.pass(X_45); | | language.pass(X_93); | | language.pass(X_80); | | language.pass(r1_61); | | language.pass(X_56); | | language.pass(X_55); | | language.pass(X_70); | | language.pass(X_81); | | language.pass(X_71); | | language.pass(X_114); | | language.pass(X_111); | | language.pass(X_113); | | language.pass(X_150); | | language.pass(X_129); | | language.pass(X_118); | | language.pass(X_158); | | language.pass(X_125); | | language.pass(X_107); | | language.pass(r1_86); | | language.pass(X_72); | | language.pass(X_188); | | language.pass(X_189); | | language.pass(r1_232); | | language.pass(X_192); | | language.pass(X_198); | | language.pass(X_193); | | language.pass(X_199); | | language.pass(X_208); | | language.pass(X_209); | | language.pass(r1_254); | | language.pass(X_200); | | exit X_303; | | sql.resultSet(X_231,X_233,X_235,X_237,X_239,X_210,X_211); | | end user.s1_2; | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 175 tuples COMMIT; auto commit mode: on