START TRANSACTION; auto commit mode: off EXPLAIN select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from part, supplier, partsupp, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 6 and p_type like '%COPPER' and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and ps_supplycost = ( select min(ps_supplycost) from partsupp, supplier, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +============================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:int,A1:str,A2:str,A3:str):void; | | X_356:void := querylog.define("explain\n\n\nselect\n\ts_acctbal,\n\ts_name,\n\tn_name,\n\tp_partkey,\n\tp_mfgr,\n\ts_address,\n\ts_phone,\n\ts_comment\nfrom\n\tpart,\n\tsupplier,\n\tpartsupp,\n\tnation,\n\tregion\nwhere\n\tp_partkey = ps_partkey\n\tand s_suppkey = ps_suppkey\n\tand p_size = 6\n\tand p_type like \\'%COPPER\\'\n\tand s_nationkey = n_nationkey\n\tand n_regionkey = r_regionkey\n\tand r_name = \\'ASIA\\'\n\tand ps_supplycost = (\n\t\tselect\n\t\t\tmin(ps_supplycost)\n\t\tfrom\n\t\t\tpartsupp,\n\t\t\tsupplier,\n\t\t\tnation,\n\t\t\tregion\n\t\twhere\n\t\t\tp_partkey = ps_partkey\n\t\t\tand s_suppkey = ps_suppkey\n\t\t\tand s_nationkey = n_nationkey\n\t\t\tand n_regionkey = r_regionkey\n\t\t\tand r_name = \\'ASIA\\'\n\t)\norder by\n\ts_acctbal desc,\n\tn_name,\n\ts_name,\n\tp_partkey\nlimit 100;","default_pipe",240); | | barrier X_445 := language.dataflow(); | | X_282 := bat.new(nil:oid,nil:str); | | X_290 := bat.append(X_282,"sys.supplier"); | | X_300 := bat.append(X_290,"sys.supplier"); | | X_309 := bat.append(X_300,"sys.nation"); | | X_316 := bat.append(X_309,"sys.part"); | | X_325 := bat.append(X_316,"sys.part"); | | X_331 := bat.append(X_325,"sys.supplier"); | | X_340 := bat.append(X_331,"sys.supplier"); | | X_349 := bat.append(X_340,"sys.supplier"); | | X_285 := bat.new(nil:oid,nil:str); | | X_292 := bat.append(X_285,"s_acctbal"); | | X_301 := bat.append(X_292,"s_name"); | | X_311 := bat.append(X_301,"n_name"); | | X_318 := bat.append(X_311,"p_partkey"); | | X_326 := bat.append(X_318,"p_mfgr"); | | X_333 := bat.append(X_326,"s_address"); | | X_341 := bat.append(X_333,"s_phone"); | | X_350 := bat.append(X_341,"s_comment"); | | X_286 := bat.new(nil:oid,nil:str); | | X_294 := bat.append(X_286,"decimal"); | | X_303 := bat.append(X_294,"char"); | | X_313 := bat.append(X_303,"char"); | | X_320 := bat.append(X_313,"int"); | | X_328 := bat.append(X_320,"char"); | | X_335 := bat.append(X_328,"varchar"); | | X_343 := bat.append(X_335,"char"); | | X_352 := bat.append(X_343,"varchar"); | | X_287 := bat.new(nil:oid,nil:int); | | X_296 := bat.append(X_287,15); | | X_305 := bat.append(X_296,25); | | X_314 := bat.append(X_305,25); | | X_322 := bat.append(X_314,32); | | X_329 := bat.append(X_322,25); | | X_337 := bat.append(X_329,40); | | X_345 := bat.append(X_337,15); | | X_353 := bat.append(X_345,101); | | X_289 := bat.new(nil:oid,nil:int); | | X_298 := bat.append(X_289,2); | | X_307 := bat.append(X_298,0); | | X_315 := bat.append(X_307,0); | | X_324 := bat.append(X_315,0); | | X_330 := bat.append(X_324,0); | | X_339 := bat.append(X_330,0); | | X_347 := bat.append(X_339,0); | | X_355 := bat.append(X_347,0); | | X_5 := sql.mvc(); | | X_6:bat[:oid,:oid] := sql.tid(X_5,"sys","nation"); | | X_9:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","nation","nation_regionkey",0); | | (X_12,r1_12) := sql.bind_idxbat(X_5,"sys","nation","nation_regionkey",2); | | X_15:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","nation","nation_regionkey",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","region","r_name",0); | | X_18:bat[:oid,:oid] := sql.tid(X_5,"sys","region"); | | X_393 := algebra.subselect(X_20,X_18,A3,A3,true,false,false); | | (X_22,r1_23) := sql.bind(X_5,"sys","region","r_name",2); | | X_394 := algebra.subselect(r1_23,nil:bat[:oid,:oid],A3,A3,true,false,false); | | X_24:bat[:oid,:str] := sql.bind(X_5,"sys","region","r_name",1); | | X_396 := algebra.subselect(X_24,X_18,A3,A3,true,false,false); | | X_25 := sql.subdelta(X_393,X_18,X_22,X_394,X_396); | | X_28 := X_25; | | (X_29,r1_33) := algebra.subjoin(X_17,X_28,nil:BAT,nil:BAT,false,nil:lng); | | X_33 := algebra.leftfetchjoin(X_29,X_6); | | X_34:bat[:oid,:oid] := sql.tid(X_5,"sys","supplier"); | | X_36:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","supplier","supplier_nationkey",0); | | (X_39,r1_43) := sql.bind_idxbat(X_5,"sys","supplier","supplier_nationkey",2); | | X_41:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","supplier","supplier_nationkey",1); | | X_42 := sql.projectdelta(X_34,X_36,X_39,r1_43,X_41); | | (X_43,r1_48) := algebra.subjoin(X_33,X_42,nil:BAT,nil:BAT,false,nil:lng); | | X_45 := algebra.leftfetchjoin(r1_48,X_34); | | X_46:bat[:oid,:oid] := sql.tid(X_5,"sys","partsupp"); | | X_48:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","partsupp","partsupp_suppkey",0); | | (X_51,r1_56) := sql.bind_idxbat(X_5,"sys","partsupp","partsupp_suppkey",2); | | X_54:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","partsupp","partsupp_suppkey",1); | | X_56 := sql.projectdelta(X_46,X_48,X_51,r1_56,X_54); | | (X_57,r1_63) := algebra.subjoin(X_45,X_56,nil:BAT,nil:BAT,false,nil:lng); | | X_60:bat[:oid,:int] := sql.bind(X_5,"sys","partsupp","ps_partkey",0); | | (X_62,r1_68) := sql.bind(X_5,"sys","partsupp","ps_partkey",2); | | X_64:bat[:oid,:int] := sql.bind(X_5,"sys","partsupp","ps_partkey",1); | | X_65 := sql.projectdelta(X_46,X_60,X_62,r1_68,X_64); | | X_66 := algebra.leftfetchjoin(r1_63,X_65); | | X_68:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","partsupp","partsupp_partkey",0); | | (X_70,r1_78) := sql.bind_idxbat(X_5,"sys","partsupp","partsupp_partkey",2); | | X_72:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","partsupp","partsupp_partkey",1); | | X_73 := sql.projectdelta(X_46,X_68,X_70,r1_78,X_72); | | X_76:bat[:oid,:str] := sql.bind(X_5,"sys","part","p_type",0); | | X_82:bat[:oid,:int] := sql.bind(X_5,"sys","part","p_size",0); | | X_74:bat[:oid,:oid] := sql.tid(X_5,"sys","part"); | | X_397 := algebra.subselect(X_82,X_74,A0,A0,true,false,false); | | (X_85,r1_98) := sql.bind(X_5,"sys","part","p_size",2); | | X_398 := algebra.subselect(r1_98,nil:bat[:oid,:oid],A0,A0,true,false,false); | | X_88:bat[:oid,:int] := sql.bind(X_5,"sys","part","p_size",1); | | X_400 := algebra.subselect(X_88,X_74,A0,A0,true,false,false); | | X_90 := sql.subdelta(X_397,X_74,X_85,X_398,X_400); | | X_401 := algebra.likesubselect(X_76,X_90,A1,"",false); | | (X_78,r1_87) := sql.bind(X_5,"sys","part","p_type",2); | | X_402 := algebra.likesubselect(r1_87,nil:bat[:oid,:oid],A1,"",false); | | X_80:bat[:oid,:str] := sql.bind(X_5,"sys","part","p_type",1); | | X_404 := algebra.likesubselect(X_80,X_90,A1,"",false); | | X_93 := sql.subdelta(X_401,X_90,X_78,X_402,X_404); | | X_94 := X_93; | | (X_95,r1_111) := algebra.subjoin(X_73,X_94,nil:BAT,nil:BAT,false,nil:lng); | | X_100 := algebra.leftfetchjoin(X_95,X_56); | | X_101:bat[:oid,:oid] := sql.tid(X_5,"sys","supplier"); | | (X_103,r1_127) := algebra.subjoin(X_100,X_101,nil:BAT,nil:BAT,false,nil:lng); | | X_105:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","supplier","supplier_nationkey",0); | | (X_108,r1_132) := sql.bind_idxbat(X_5,"sys","supplier","supplier_nationkey",2); | | X_111:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","supplier","supplier_nationkey",1); | | X_113 := sql.projectdelta(X_101,X_105,X_108,r1_132,X_111); | | X_114 := algebra.leftfetchjoin(r1_127,X_113); | | X_115:bat[:oid,:oid] := sql.tid(X_5,"sys","nation"); | | (X_117,r1_142) := algebra.subjoin(X_114,X_115,nil:BAT,nil:BAT,false,nil:lng); | | X_120:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","nation","nation_regionkey",0); | | (X_122,r1_147) := sql.bind_idxbat(X_5,"sys","nation","nation_regionkey",2); | | X_125:bat[:oid,:oid] := sql.bind_idxbat(X_5,"sys","nation","nation_regionkey",1); | | X_126 := sql.projectdelta(X_115,X_120,X_122,r1_147,X_125); | | X_127 := algebra.leftfetchjoin(r1_142,X_126); | | X_130:bat[:oid,:str] := sql.bind(X_5,"sys","region","r_name",0); | | X_128:bat[:oid,:oid] := sql.tid(X_5,"sys","region"); | | X_405 := algebra.subselect(X_130,X_128,A2,A2,true,false,false); | | (X_132,r1_158) := sql.bind(X_5,"sys","region","r_name",2); | | X_406 := algebra.subselect(r1_158,nil:bat[:oid,:oid],A2,A2,true,false,false); | | X_134:bat[:oid,:str] := sql.bind(X_5,"sys","region","r_name",1); | | X_408 := algebra.subselect(X_134,X_128,A2,A2,true,false,false); | | X_135 := sql.subdelta(X_405,X_128,X_132,X_406,X_408); | | X_137 := X_135; | | (X_138,r1_167) := algebra.subjoin(X_127,X_137,nil:BAT,nil:BAT,false,nil:lng); | | X_142:bat[:oid,:int] := sql.bind(X_5,"sys","part","p_partkey",0); | | (X_146,r1_175) := sql.bind(X_5,"sys","part","p_partkey",2); | | X_149:bat[:oid,:int] := sql.bind(X_5,"sys","part","p_partkey",1); | | X_151 := sql.projectdelta(X_93,X_142,X_146,r1_175,X_149); | | X_152:bat[:oid,:int] := algebra.leftfetchjoinPath(X_138,X_117,X_103,r1_111,X_151); | | (X_153,r1_187) := algebra.subjoin(X_66,X_152,nil:BAT,nil:BAT,false,nil:lng); | | X_156:bat[:oid,:oid] := batcalc.identity(X_152); | | X_157 := algebra.leftfetchjoin(r1_187,X_156); | | (X_158,r1_194,r2_194) := group.subgroupdone(X_157); | | X_161:bat[:oid,:lng] := sql.bind(X_5,"sys","partsupp","ps_supplycost",0); | | (X_165,r1_201) := sql.bind(X_5,"sys","partsupp","ps_supplycost",2); | | X_167:bat[:oid,:lng] := sql.bind(X_5,"sys","partsupp","ps_supplycost",1); | | X_168 := sql.projectdelta(X_46,X_161,X_165,r1_201,X_167); | | X_169:bat[:oid,:lng] := algebra.leftfetchjoinPath(r1_194,r1_187,X_138,X_117,X_103,X_95,X_168); | | X_173:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_153,r1_63,X_168); | | X_174:bat[:oid,:lng] := aggr.submin(X_173,X_158,r1_194,true); | | X_176:bat[:oid,:bit] := batcalc.==(X_169,X_174); | | X_177 := algebra.subselect(X_176,true,true,true,true,false); | | X_179:bat[:oid,:int] := algebra.leftfetchjoinPath(X_177,r1_194,r1_187,X_152); | | X_180:bat[:oid,:str] := sql.bind(X_5,"sys","supplier","s_name",0); | | (X_184,r1_237) := sql.bind(X_5,"sys","supplier","s_name",2); | | X_186:bat[:oid,:str] := sql.bind(X_5,"sys","supplier","s_name",1); | | X_187 := sql.projectdelta(X_101,X_180,X_184,r1_237,X_186); | | X_188:bat[:oid,:str] := algebra.leftfetchjoinPath(X_177,r1_194,r1_187,X_138,X_117,r1_127,X_187); | | X_189:bat[:oid,:str] := sql.bind(X_5,"sys","nation","n_name",0); | | (X_193,r1_252) := sql.bind(X_5,"sys","nation","n_name",2); | | X_196:bat[:oid,:str] := sql.bind(X_5,"sys","nation","n_name",1); | | X_198 := sql.projectdelta(X_115,X_189,X_193,r1_252,X_196); | | X_199:bat[:oid,:str] := algebra.leftfetchjoinPath(X_177,r1_194,r1_187,X_138,r1_142,X_198); | | X_200:bat[:oid,:lng] := sql.bind(X_5,"sys","supplier","s_acctbal",0); | | (X_202,r1_266) := sql.bind(X_5,"sys","supplier","s_acctbal",2); | | X_206:bat[:oid,:lng] := sql.bind(X_5,"sys","supplier","s_acctbal",1); | | X_207 := sql.projectdelta(X_101,X_200,X_202,r1_266,X_206); | | X_208:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_177,r1_194,r1_187,X_138,X_117,r1_127,X_207); | | X_242:bat[:oid,:str] := sql.bind(X_5,"sys","part","p_mfgr",0); | | (X_247,r1_336) := sql.bind(X_5,"sys","part","p_mfgr",2); | | X_250:bat[:oid,:str] := sql.bind(X_5,"sys","part","p_mfgr",1); | | X_251 := sql.projectdelta(X_93,X_242,X_247,r1_336,X_250); | | (X_209,r1_284) := algebra.firstn(X_208,100:wrd,false,false); | | (X_212,r1_289) := algebra.firstn(X_199,X_209,r1_284,100:wrd,true,false); | | (X_215,r1_294) := algebra.firstn(X_188,X_212,r1_289,100:wrd,true,false); | | X_217 := algebra.firstn(X_179,X_215,r1_294,100:wrd,true,false); | | X_218 := algebra.leftfetchjoin(X_217,X_179); | | X_219 := algebra.leftfetchjoin(X_217,X_188); | | X_220 := algebra.leftfetchjoin(X_217,X_199); | | X_221 := algebra.leftfetchjoin(X_217,X_208); | | (X_222,r1_303,r2_303) := algebra.subsort(X_221,true,false); | | (X_225,r1_306,r2_306) := algebra.subsort(X_220,r1_303,r2_303,false,false); | | (X_228,r1_309,r2_309) := algebra.subsort(X_219,r1_306,r2_306,false,false); | | (X_231,r1_312,r2_312) := algebra.subsort(X_218,r1_309,r2_309,false,false); | | X_234 := algebra.leftfetchjoin(r1_312,X_221); | | X_237 := algebra.subslice(X_234,0,99:wrd); | | X_252:bat[:oid,:str] := algebra.leftfetchjoinPath(X_237,r1_312,X_217,X_177,r1_194,r1_187,X_138,X_117,X_103,r1_111,X_251); | | X_253:bat[:oid,:str] := sql.bind(X_5,"sys","supplier","s_address",0); | | (X_256,r1_356) := sql.bind(X_5,"sys","supplier","s_address",2); | | X_258:bat[:oid,:str] := sql.bind(X_5,"sys","supplier","s_address",1); | | X_260 := sql.projectdelta(X_101,X_253,X_256,r1_356,X_258); | | X_261:bat[:oid,:str] := algebra.leftfetchjoinPath(X_237,r1_312,X_217,X_177,r1_194,r1_187,X_138,X_117,r1_127,X_260); | | X_262:bat[:oid,:str] := sql.bind(X_5,"sys","supplier","s_phone",0); | | (X_266,r1_375) := sql.bind(X_5,"sys","supplier","s_phone",2); | | X_269:bat[:oid,:str] := sql.bind(X_5,"sys","supplier","s_phone",1); | | X_270 := sql.projectdelta(X_101,X_262,X_266,r1_375,X_269); | | X_271:bat[:oid,:str] := algebra.leftfetchjoinPath(X_237,r1_312,X_217,X_177,r1_194,r1_187,X_138,X_117,r1_127,X_270); | | X_272:bat[:oid,:str] := sql.bind(X_5,"sys","supplier","s_comment",0); | | (X_275,r1_393) := sql.bind(X_5,"sys","supplier","s_comment",2); | | X_277:bat[:oid,:str] := sql.bind(X_5,"sys","supplier","s_comment",1); | | X_279 := sql.projectdelta(X_101,X_272,X_275,r1_393,X_277); | | X_280:bat[:oid,:str] := algebra.leftfetchjoinPath(X_237,r1_312,X_217,X_177,r1_194,r1_187,X_138,X_117,r1_127,X_279); | | X_238 := algebra.leftfetchjoin(X_237,X_234); | | X_239:bat[:oid,:str] := algebra.leftfetchjoinPath(X_237,r1_312,X_219); | | X_240:bat[:oid,:str] := algebra.leftfetchjoinPath(X_237,r1_312,X_220); | | X_241:bat[:oid,:int] := algebra.leftfetchjoinPath(X_237,r1_312,X_218); | | language.pass(X_18); | | language.pass(X_6); | | language.pass(X_34); | | language.pass(X_74); | | language.pass(X_90); | | language.pass(X_56); | | language.pass(X_128); | | language.pass(X_46); | | language.pass(X_95); | | language.pass(r1_63); | | language.pass(X_168); | | language.pass(X_152); | | language.pass(X_115); | | language.pass(r1_142); | | language.pass(X_93); | | language.pass(X_179); | | language.pass(X_188); | | language.pass(X_199); | | language.pass(X_208); | | language.pass(X_221); | | language.pass(X_103); | | language.pass(r1_111); | | language.pass(X_101); | | language.pass(X_217); | | language.pass(X_177); | | language.pass(r1_194); | | language.pass(r1_187); | | language.pass(X_138); | | language.pass(X_117); | | language.pass(r1_127); | | language.pass(X_234); | | language.pass(X_219); | | language.pass(X_220); | | language.pass(X_237); | | language.pass(r1_312); | | language.pass(X_218); | | exit X_445; | | sql.resultSet(X_349,X_350,X_352,X_353,X_355,X_238,X_239,X_240,X_241,X_252,X_261,X_271,X_280); | | end user.s1_1; | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 240 tuples COMMIT; auto commit mode: on