START TRANSACTION; auto commit mode: off EXPLAIN select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 314 ) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate LIMIT 100; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +==========================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================+ | function user.s1_1(A0:sht):void; | | X_231:void := querylog.define("explain\n\n\nselect\n\tc_name,\n\tc_custkey,\n\to_orderkey,\n\to_orderdate,\n\to_totalprice,\n\tsum(l_quantity)\nfrom\n\tcustomer,\n\torders,\n\tlineitem\nwhere\n\to_orderkey in (\n\t\tselect\n\t\t\tl_orderkey\n\t\tfrom\n\t\t\tlineitem\n\t\tgroup by\n\t\t\tl_orderkey having\n\t\t\t\tsum(l_quantity) > 314\n\t)\n\tand c_custkey = o_custkey\n\tand o_orderkey = l_orderkey\ngroup by\n\tc_name,\n\tc_custkey,\n\to_orderkey,\n\to_orderdate,\n\to_totalprice\norder by\n\to_totalprice desc,\n\to_orderdate\nlimit 100;","default_pipe",157); | | barrier X_284 := language.dataflow(); | | X_174 := bat.new(nil:oid,nil:str); | | X_182 := bat.append(X_174,"sys.customer"); | | X_192 := bat.append(X_182,"sys.customer"); | | X_200 := bat.append(X_192,"sys.orders"); | | X_207 := bat.append(X_200,"sys.orders"); | | X_214 := bat.append(X_207,"sys.orders"); | | X_223 := bat.append(X_214,"sys.L6"); | | X_177 := bat.new(nil:oid,nil:str); | | X_184 := bat.append(X_177,"c_name"); | | X_193 := bat.append(X_184,"c_custkey"); | | X_202 := bat.append(X_193,"o_orderkey"); | | X_208 := bat.append(X_202,"o_orderdate"); | | X_215 := bat.append(X_208,"o_totalprice"); | | X_225 := bat.append(X_215,"L6"); | | X_178 := bat.new(nil:oid,nil:str); | | X_186 := bat.append(X_178,"varchar"); | | X_195 := bat.append(X_186,"int"); | | X_204 := bat.append(X_195,"int"); | | X_210 := bat.append(X_204,"date"); | | X_217 := bat.append(X_210,"decimal"); | | X_227 := bat.append(X_217,"decimal"); | | X_179 := bat.new(nil:oid,nil:int); | | X_188 := bat.append(X_179,25); | | X_197 := bat.append(X_188,32); | | X_205 := bat.append(X_197,32); | | X_212 := bat.append(X_205,0); | | X_219 := bat.append(X_212,15); | | X_228 := bat.append(X_219,39); | | X_181 := bat.new(nil:oid,nil:int); | | X_190 := bat.append(X_181,0); | | X_199 := bat.append(X_190,0); | | X_206 := bat.append(X_199,0); | | X_213 := bat.append(X_206,0); | | X_221 := bat.append(X_213,2); | | X_230 := bat.append(X_221,2); | | X_2 := sql.mvc(); | | X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","orders"); | | X_6:bat[:oid,:int] := sql.bind(X_2,"sys","orders","o_orderkey",0); | | (X_9,r1_9) := sql.bind(X_2,"sys","orders","o_orderkey",2); | | X_12:bat[:oid,:int] := sql.bind(X_2,"sys","orders","o_orderkey",1); | | X_14 := sql.delta(X_6,X_9,r1_9,X_12); | | X_15 := algebra.leftfetchjoin(X_3,X_14); | | X_16 := bat.mirror(X_15); | | X_17:bat[:oid,:oid] := sql.tid(X_2,"sys","lineitem"); | | X_19:bat[:oid,:lng] := sql.bind(X_2,"sys","lineitem","l_quantity",0); | | (X_21,r1_21) := sql.bind(X_2,"sys","lineitem","l_quantity",2); | | X_23:bat[:oid,:lng] := sql.bind(X_2,"sys","lineitem","l_quantity",1); | | X_24 := sql.delta(X_19,X_21,r1_21,X_23); | | X_25 := algebra.leftfetchjoin(X_17,X_24); | | X_26:bat[:oid,:int] := sql.bind(X_2,"sys","lineitem","l_orderkey",0); | | (X_28,r1_28) := sql.bind(X_2,"sys","lineitem","l_orderkey",2); | | X_30:bat[:oid,:int] := sql.bind(X_2,"sys","lineitem","l_orderkey",1); | | X_31 := sql.delta(X_26,X_28,r1_28,X_30); | | X_32 := algebra.leftfetchjoin(X_17,X_31); | | (X_33,r1_33,r2_33) := group.subgroupdone(X_32); | | X_36:bat[:oid,:hge] := aggr.subsum(X_25,X_33,r1_33,true,true); | | X_38 := calc.hge(A0,39,2); | | X_40 := algebra.thetasubselect(X_36,X_38,">"); | | X_42:bat[:oid,:int] := algebra.leftfetchjoinPath(X_40,r1_33,X_32); | | (X_43,r1_45) := algebra.subjoin(X_15,X_42,nil:BAT,nil:BAT,false,nil:lng); | | X_48 := algebra.tinter(X_16,X_43); | | X_49 := algebra.leftfetchjoin(X_48,X_15); | | (X_54,r1_65) := algebra.subjoin(X_49,X_32,nil:BAT,nil:BAT,false,nil:lng); | | X_56:bat[:oid,:oid] := sql.bind_idxbat(X_2,"sys","orders","order_custkey",0); | | (X_59,r1_70) := sql.bind_idxbat(X_2,"sys","orders","order_custkey",2); | | X_61:bat[:oid,:oid] := sql.bind_idxbat(X_2,"sys","orders","order_custkey",1); | | X_62 := sql.delta(X_56,X_59,r1_70,X_61); | | X_63:bat[:oid,:oid] := algebra.leftfetchjoinPath(X_54,X_48,X_3,X_62); | | X_64:bat[:oid,:oid] := sql.tid(X_2,"sys","customer"); | | (X_66,r1_79) := algebra.subjoin(X_63,X_64,nil:BAT,nil:BAT,false,nil:lng); | | X_71:bat[:oid,:int] := sql.bind(X_2,"sys","customer","c_custkey",0); | | (X_73,r1_86) := sql.bind(X_2,"sys","customer","c_custkey",2); | | X_76:bat[:oid,:int] := sql.bind(X_2,"sys","customer","c_custkey",1); | | X_77 := sql.delta(X_71,X_73,r1_86,X_76); | | X_78 := algebra.leftfetchjoin(X_64,X_77); | | X_79 := algebra.leftfetchjoin(r1_79,X_78); | | X_80:bat[:oid,:int] := algebra.leftfetchjoinPath(X_66,X_54,X_49); | | (X_81,r1_95,r2_95) := group.subgroup(X_80); | | (X_84,r1_98,r2_98) := group.subgroupdone(X_79,X_81); | | X_87 := algebra.leftfetchjoin(r1_98,X_79); | | (X_91,r1_112) := algebra.subjoin(X_87,X_78,nil:BAT,nil:BAT,false,nil:lng); | | X_96:bat[:oid,:int] := algebra.leftfetchjoinPath(X_91,r1_98,X_80); | | X_158:bat[:oid,:lng] := sql.bind(X_2,"sys","lineitem","l_quantity",0); | | (X_163,r1_212) := sql.bind(X_2,"sys","lineitem","l_quantity",2); | | X_166:bat[:oid,:lng] := sql.bind(X_2,"sys","lineitem","l_quantity",1); | | X_168 := sql.delta(X_158,X_163,r1_212,X_166); | | X_169:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_66,r1_65,X_17,X_168); | | X_170:bat[:oid,:hge] := aggr.subsum(X_169,X_84,r1_98,true,true); | | X_97:bat[:oid,:oid] := sql.tid(X_2,"sys","orders"); | | X_99:bat[:oid,:int] := sql.bind(X_2,"sys","orders","o_orderkey",0); | | (X_102,r1_124) := sql.bind(X_2,"sys","orders","o_orderkey",2); | | X_104:bat[:oid,:int] := sql.bind(X_2,"sys","orders","o_orderkey",1); | | X_105 := sql.delta(X_99,X_102,r1_124,X_104); | | X_106 := algebra.leftfetchjoin(X_97,X_105); | | (X_107,r1_129) := algebra.subjoin(X_96,X_106,nil:BAT,nil:BAT,false,nil:lng); | | X_109:bat[:oid,:date] := sql.bind(X_2,"sys","orders","o_orderdate",0); | | (X_111,r1_133) := sql.bind(X_2,"sys","orders","o_orderdate",2); | | X_113:bat[:oid,:date] := sql.bind(X_2,"sys","orders","o_orderdate",1); | | X_114 := sql.delta(X_109,X_111,r1_133,X_113); | | X_115:bat[:oid,:date] := algebra.leftfetchjoinPath(r1_129,X_97,X_114); | | X_116:bat[:oid,:lng] := sql.bind(X_2,"sys","orders","o_totalprice",0); | | (X_119,r1_142) := sql.bind(X_2,"sys","orders","o_totalprice",2); | | X_122:bat[:oid,:lng] := sql.bind(X_2,"sys","orders","o_totalprice",1); | | X_124 := sql.delta(X_116,X_119,r1_142,X_122); | | X_125:bat[:oid,:lng] := algebra.leftfetchjoinPath(r1_129,X_97,X_124); | | (X_126,r1_155) := algebra.firstn(X_125,100:wrd,false,false); | | X_129 := algebra.firstn(X_115,X_126,r1_155,100:wrd,true,false); | | X_131 := algebra.leftfetchjoin(X_129,X_115); | | X_132 := algebra.leftfetchjoin(X_129,X_125); | | (X_133,r1_164,r2_164) := algebra.subsort(X_132,true,false); | | (X_136,r1_167,r2_167) := algebra.subsort(X_131,r1_164,r2_164,false,false); | | X_139:bat[:oid,:str] := sql.bind(X_2,"sys","customer","c_name",0); | | (X_144,r1_175) := sql.bind(X_2,"sys","customer","c_name",2); | | X_147:bat[:oid,:str] := sql.bind(X_2,"sys","customer","c_name",1); | | X_149 := sql.delta(X_139,X_144,r1_175,X_147); | | X_150:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_167,X_129,X_107,r1_112,X_64,X_149); | | X_152 := algebra.subslice(X_150,0,99:wrd); | | X_153 := algebra.leftfetchjoin(X_152,X_150); | | X_154:bat[:oid,:int] := algebra.leftfetchjoinPath(X_152,r1_167,X_129,X_107,X_91,X_87); | | X_155:bat[:oid,:int] := algebra.leftfetchjoinPath(X_152,r1_167,X_129,X_107,X_96); | | X_156:bat[:oid,:date] := algebra.leftfetchjoinPath(X_152,r1_167,X_131); | | X_157:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_152,r1_167,X_132); | | X_172:bat[:oid,:hge] := algebra.leftfetchjoinPath(X_152,r1_167,X_129,X_107,X_91,X_170); | | language.pass(r1_33); | | language.pass(X_15); | | language.pass(X_32); | | language.pass(X_48); | | language.pass(X_3); | | language.pass(X_54); | | language.pass(X_49); | | language.pass(X_79); | | language.pass(X_78); | | language.pass(X_80); | | language.pass(X_66); | | language.pass(X_17); | | language.pass(r1_98); | | language.pass(r1_129); | | language.pass(X_97); | | language.pass(X_115); | | language.pass(X_125); | | language.pass(X_64); | | language.pass(X_150); | | language.pass(X_87); | | language.pass(X_96); | | language.pass(X_131); | | language.pass(X_132); | | language.pass(X_152); | | language.pass(r1_167); | | language.pass(X_129); | | language.pass(X_107); | | language.pass(X_91); | | exit X_284; | | sql.resultSet(X_223,X_225,X_227,X_228,X_230,X_153,X_154,X_155,X_156,X_157,X_172); | | end user.s1_1; | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 157 tuples COMMIT; auto commit mode: on