START TRANSACTION; auto commit mode: off EXPLAIN select s_name, s_address from supplier, nation where s_suppkey in ( select ps_suppkey from partsupp where ps_partkey in ( select p_partkey from part where p_name like 'tomato%' ) and ps_availqty > ( select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= '1997-01-01' and l_shipdate < date '1997-01-01' + interval '1' year ) ) and s_nationkey = n_nationkey +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +===================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:str,A1:bte,A2:str,A3:date,A4:int,A5:str):void; | | X_257:void := querylog.define("explain\n\n\nselect\n\ts_name,\n\ts_address\nfrom\n\tsupplier,\n\tnation\nwhere\n\ts_suppkey in (\n\t\tselect\n\t\t\tps_suppkey\n\t\tfrom\n\t\t\tpartsupp\n\t\twhere\n\t\t\tps_partkey in (\n\t\t\t\tselect\n\t\t\t\t\tp_partkey\n\t\t\t\tfrom\n\t\t\t\t\tpart\n\t\t\t\twhere\n\t\t\t\t\tp_name like \\'tomato%\\'\n\t\t\t)\n\t\t\tand ps_availqty > (\n\t\t\t\tselect\n\t\t\t\t\t0.5 * sum(l_quantity)\n\t\t\t\tfrom\n\t\t\t\t\tlineitem\n\t\t\t\twhere\n\t\t\t\t\tl_partkey = ps_partkey\n\t\t\t\t\tand l_suppkey = ps_suppkey\n\t\t\t\t\tand l_shipdate >= \\'1997-01-01\\'\n\t\t\t\t\tand l_shipdate < date \\'1997-01-01\\' + interval \\'1\\' year\n\t\t\t)\n\t)\n\tand s_nationkey = n_nationkey\n\tand n_name = \\'INDIA\\'\norder by\n\ts_name;","default_pipe",170); | | barrier X_316 := language.dataflow(); | | X_232 := bat.new(nil:oid,nil:str); | | X_240 := bat.append(X_232,"sys.supplier"); | | X_249 := bat.append(X_240,"sys.supplier"); | | X_235 := bat.new(nil:oid,nil:str); | | X_242 := bat.append(X_235,"s_name"); | | X_250 := bat.append(X_242,"s_address"); | | X_236 := bat.new(nil:oid,nil:str); | | X_244 := bat.append(X_236,"char"); | | X_251 := bat.append(X_244,"varchar"); | | X_237 := bat.new(nil:oid,nil:int); | | X_246 := bat.append(X_237,25); | | X_253 := bat.append(X_246,40); | | X_239 := bat.new(nil:oid,nil:int); | | X_248 := bat.append(X_239,0); | | X_255 := bat.append(X_248,0); | | X_7 := sql.mvc(); | | X_8:bat[:oid,:oid] := sql.tid(X_7,"sys","supplier"); | | X_11:bat[:oid,:int] := sql.bind(X_7,"sys","supplier","s_suppkey",0); | | (X_14,r1_14) := sql.bind(X_7,"sys","supplier","s_suppkey",2); | | X_17:bat[:oid,:int] := sql.bind(X_7,"sys","supplier","s_suppkey",1); | | X_19 := sql.delta(X_11,X_14,r1_14,X_17); | | X_20 := algebra.leftfetchjoin(X_8,X_19); | | X_21 := bat.mirror(X_20); | | X_22:bat[:oid,:oid] := sql.tid(X_7,"sys","partsupp"); | | X_24:bat[:oid,:int] := sql.bind(X_7,"sys","partsupp","ps_partkey",0); | | (X_26,r1_26) := sql.bind(X_7,"sys","partsupp","ps_partkey",2); | | X_28:bat[:oid,:int] := sql.bind(X_7,"sys","partsupp","ps_partkey",1); | | X_29 := sql.delta(X_24,X_26,r1_26,X_28); | | X_30 := algebra.leftfetchjoin(X_22,X_29); | | X_31 := bat.mirror(X_30); | | X_32:bat[:oid,:oid] := sql.tid(X_7,"sys","part"); | | X_34:bat[:oid,:str] := sql.bind(X_7,"sys","part","p_name",0); | | (X_36,r1_36) := sql.bind(X_7,"sys","part","p_name",2); | | X_38:bat[:oid,:str] := sql.bind(X_7,"sys","part","p_name",1); | | X_39 := sql.delta(X_34,X_36,r1_36,X_38); | | X_40 := algebra.leftfetchjoin(X_32,X_39); | | X_42 := algebra.likesubselect(X_40,nil:BAT,A0,"",false); | | X_45:bat[:oid,:int] := sql.bind(X_7,"sys","part","p_partkey",0); | | (X_49,r1_51) := sql.bind(X_7,"sys","part","p_partkey",2); | | X_52:bat[:oid,:int] := sql.bind(X_7,"sys","part","p_partkey",1); | | X_54 := sql.delta(X_45,X_49,r1_51,X_52); | | X_55:bat[:oid,:int] := algebra.leftfetchjoinPath(X_42,X_32,X_54); | | (X_56,r1_59) := algebra.subjoin(X_30,X_55,nil:BAT,nil:BAT,false,nil:lng); | | X_59 := algebra.tinter(X_31,X_56); | | X_60:bat[:oid,:wrd] := sql.bind_idxbat(X_7,"sys","partsupp","partsuppkey",0); | | (X_63,r1_66) := sql.bind_idxbat(X_7,"sys","partsupp","partsuppkey",2); | | X_65:bat[:oid,:wrd] := sql.bind_idxbat(X_7,"sys","partsupp","partsuppkey",1); | | X_66 := sql.delta(X_60,X_63,r1_66,X_65); | | X_67:bat[:oid,:wrd] := algebra.leftfetchjoinPath(X_59,X_22,X_66); | | X_68:bat[:oid,:oid] := sql.tid(X_7,"sys","lineitem"); | | X_70:bat[:oid,:date] := sql.bind(X_7,"sys","lineitem","l_shipdate",0); | | (X_72,r1_76) := sql.bind(X_7,"sys","lineitem","l_shipdate",2); | | X_74:bat[:oid,:date] := sql.bind(X_7,"sys","lineitem","l_shipdate",1); | | X_75 := sql.delta(X_70,X_72,r1_76,X_74); | | X_76 := algebra.leftfetchjoin(X_68,X_75); | | X_77 := calc.date(A2); | | X_78:date := mtime.addmonths(A3,A4); | | X_79 := algebra.subselect(X_76,X_77,X_78,true,false,false); | | X_82:bat[:oid,:int] := sql.bind(X_7,"sys","lineitem","l_partkey",0); | | (X_86,r1_93) := sql.bind(X_7,"sys","lineitem","l_partkey",2); | | X_89:bat[:oid,:int] := sql.bind(X_7,"sys","lineitem","l_partkey",1); | | X_91 := sql.delta(X_82,X_86,r1_93,X_89); | | X_92:bat[:oid,:int] := algebra.leftfetchjoinPath(X_79,X_68,X_91); | | X_93:bat[:oid,:wrd] := batmkey.hash(X_92); | | X_95:bat[:oid,:int] := sql.bind(X_7,"sys","lineitem","l_suppkey",0); | | (X_98,r1_109) := sql.bind(X_7,"sys","lineitem","l_suppkey",2); | | X_100:bat[:oid,:int] := sql.bind(X_7,"sys","lineitem","l_suppkey",1); | | X_101 := sql.delta(X_95,X_98,r1_109,X_100); | | X_102:bat[:oid,:int] := algebra.leftfetchjoinPath(X_79,X_68,X_101); | | X_103:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_93,22,X_102); | | (X_104,r1_116) := algebra.subjoin(X_67,X_103,nil:BAT,nil:BAT,false,nil:lng); | | X_108 := algebra.leftfetchjoin(X_59,X_30); | | X_109 := algebra.leftfetchjoin(X_104,X_108); | | X_110 := algebra.leftfetchjoin(r1_116,X_92); | | X_111:bat[:oid,:bit] := batcalc.==(X_109,X_110); | | X_113 := algebra.subselect(X_111,true,true,true,false,false); | | X_115 := algebra.leftfetchjoin(X_113,X_104); | | X_116:bat[:oid,:int] := sql.bind(X_7,"sys","partsupp","ps_suppkey",0); | | (X_121,r1_136) := sql.bind(X_7,"sys","partsupp","ps_suppkey",2); | | X_124:bat[:oid,:int] := sql.bind(X_7,"sys","partsupp","ps_suppkey",1); | | X_126 := sql.delta(X_116,X_121,r1_136,X_124); | | X_127:bat[:oid,:int] := algebra.leftfetchjoinPath(X_59,X_22,X_126); | | X_128 := algebra.leftfetchjoin(X_115,X_127); | | X_129 := algebra.leftfetchjoin(X_113,r1_116); | | X_130 := algebra.leftfetchjoin(X_129,X_102); | | X_131:bat[:oid,:bit] := batcalc.==(X_128,X_130); | | X_132 := algebra.subselect(X_131,true,true,true,false,false); | | X_133 := algebra.leftfetchjoin(X_132,X_115); | | X_134:bat[:oid,:oid] := batcalc.identity(X_108); | | X_135 := algebra.leftfetchjoin(X_133,X_134); | | (X_136,r1_154,r2_154) := group.subgroupdone(X_135); | | X_139:bat[:oid,:int] := sql.bind(X_7,"sys","partsupp","ps_availqty",0); | | (X_141,r1_159) := sql.bind(X_7,"sys","partsupp","ps_availqty",2); | | X_143:bat[:oid,:int] := sql.bind(X_7,"sys","partsupp","ps_availqty",1); | | X_144 := sql.delta(X_139,X_141,r1_159,X_143); | | X_145:bat[:oid,:int] := algebra.leftfetchjoinPath(r1_154,X_133,X_59,X_22,X_144); | | X_149:bat[:oid,:lng] := sql.bind(X_7,"sys","lineitem","l_quantity",0); | | (X_154,r1_179) := sql.bind(X_7,"sys","lineitem","l_quantity",2); | | X_157:bat[:oid,:lng] := sql.bind(X_7,"sys","lineitem","l_quantity",1); | | X_159 := sql.delta(X_149,X_154,r1_179,X_157); | | X_160:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_132,X_129,X_79,X_68,X_159); | | X_161:bat[:oid,:hge] := aggr.subsum(X_160,X_136,r1_154,true,true); | | X_146:bat[:oid,:hge] := batcalc.hge(X_145,39,3); | | X_163:bat[:oid,:hge] := batcalc.*(A1,X_161); | | X_164:bat[:oid,:bit] := batcalc.>(X_146,X_163); | | X_165 := algebra.subselect(X_164,true,true,true,true,false); | | X_167:bat[:oid,:int] := algebra.leftfetchjoinPath(X_165,r1_154,X_133,X_127); | | (X_168,r1_199) := algebra.subjoin(X_20,X_167,nil:BAT,nil:BAT,false,nil:lng); | | X_172 := algebra.tinter(X_21,X_168); | | X_173:bat[:oid,:int] := sql.bind(X_7,"sys","supplier","s_nationkey",0); | | (X_176,r1_207) := sql.bind(X_7,"sys","supplier","s_nationkey",2); | | X_179:bat[:oid,:int] := sql.bind(X_7,"sys","supplier","s_nationkey",1); | | X_180 := sql.delta(X_173,X_176,r1_207,X_179); | | X_181:bat[:oid,:int] := algebra.leftfetchjoinPath(X_172,X_8,X_180); | | X_182:bat[:oid,:oid] := sql.tid(X_7,"sys","nation"); | | X_184:bat[:oid,:str] := sql.bind(X_7,"sys","nation","n_name",0); | | (X_187,r1_219) := sql.bind(X_7,"sys","nation","n_name",2); | | X_190:bat[:oid,:str] := sql.bind(X_7,"sys","nation","n_name",1); | | X_192 := sql.delta(X_184,X_187,r1_219,X_190); | | X_193 := algebra.leftfetchjoin(X_182,X_192); | | X_194 := algebra.subselect(X_193,A5,A5,true,false,false); | | X_197:bat[:oid,:int] := sql.bind(X_7,"sys","nation","n_nationkey",0); | | (X_199,r1_232) := sql.bind(X_7,"sys","nation","n_nationkey",2); | | X_201:bat[:oid,:int] := sql.bind(X_7,"sys","nation","n_nationkey",1); | | X_202 := sql.delta(X_197,X_199,r1_232,X_201); | | X_203:bat[:oid,:int] := algebra.leftfetchjoinPath(X_194,X_182,X_202); | | (X_204,r1_238) := algebra.subjoin(X_181,X_203,nil:BAT,nil:BAT,false,nil:lng); | | X_208:bat[:oid,:str] := sql.bind(X_7,"sys","supplier","s_name",0); | | (X_212,r1_246) := sql.bind(X_7,"sys","supplier","s_name",2); | | X_214:bat[:oid,:str] := sql.bind(X_7,"sys","supplier","s_name",1); | | X_215 := sql.delta(X_208,X_212,r1_246,X_214); | | X_216:bat[:oid,:str] := algebra.leftfetchjoinPath(X_204,X_172,X_8,X_215); | | (X_217,r1_253,r2_253) := algebra.subsort(X_216,false,false); | | X_220 := algebra.leftfetchjoin(r1_253,X_216); | | X_221:bat[:oid,:str] := sql.bind(X_7,"sys","supplier","s_address",0); | | (X_224,r1_260) := sql.bind(X_7,"sys","supplier","s_address",2); | | X_227:bat[:oid,:str] := sql.bind(X_7,"sys","supplier","s_address",1); | | X_229 := sql.delta(X_221,X_224,r1_260,X_227); | | X_230:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_253,X_204,X_172,X_8,X_229); | | language.pass(X_32); | | language.pass(X_30); | | language.pass(X_92); | | language.pass(X_104); | | language.pass(X_113); | | language.pass(r1_116); | | language.pass(X_102); | | language.pass(X_115); | | language.pass(X_108); | | language.pass(X_59); | | language.pass(X_22); | | language.pass(X_132); | | language.pass(X_129); | | language.pass(X_79); | | language.pass(X_68); | | language.pass(r1_154); | | language.pass(X_133); | | language.pass(X_127); | | language.pass(X_20); | | language.pass(X_182); | | language.pass(X_216); | | language.pass(r1_253); | | language.pass(X_204); | | language.pass(X_172); | | language.pass(X_8); | | exit X_316; | | sql.resultSet(X_249,X_250,X_251,X_253,X_255,X_220,X_230); | | end user.s1_1; | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 170 tuples COMMIT; auto commit mode: on