Changeset: 93ad4be3fdda for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=93ad4be3fdda Modified Files: sql/benchmarks/tpch/Tests/20-explain.stable.out sql/benchmarks/tpch/Tests/20-plan.stable.out Branch: Oct2014 Log Message:
approved output diffs (174 lines): diff --git a/sql/benchmarks/tpch/Tests/20-explain.stable.out b/sql/benchmarks/tpch/Tests/20-explain.stable.out --- a/sql/benchmarks/tpch/Tests/20-explain.stable.out +++ b/sql/benchmarks/tpch/Tests/20-explain.stable.out @@ -94,12 +94,12 @@ function user.s2_1{autoCommit=true}(A0:s X_55:bat[:oid,:int] := algebra.leftfetchjoinPath(X_43,X_33,X_54); (X_56,r1_59) := algebra.join(X_31,X_55); X_58 := algebra.tinter(X_32,X_56); - X_282 := algebra.leftfetchjoin(X_58,X_23); + X_278 := algebra.leftfetchjoin(X_58,X_23); X_59 := sql.bind_idxbat(X_8,"sys","partsupp","partsupp_ps_partkey_ps_suppkey_pkey",0); (X_62,r1_65) := sql.bind_idxbat(X_8,"sys","partsupp","partsupp_ps_partkey_ps_suppkey_pkey",2); X_64 := sql.bind_idxbat(X_8,"sys","partsupp","partsupp_ps_partkey_ps_suppkey_pkey",1); X_65 := sql.delta(X_59,X_62,r1_65,X_64); - X_66:bat[:oid,:wrd] := algebra.leftfetchjoin(X_282,X_65); + X_66:bat[:oid,:wrd] := algebra.leftfetchjoin(X_278,X_65); X_67:bat[:oid,:oid] := sql.tid(X_8,"sys","lineitem"); X_69 := sql.bind(X_8,"sys","lineitem","l_shipdate",0); (X_71,r1_75) := sql.bind(X_8,"sys","lineitem","l_shipdate",2); @@ -108,17 +108,17 @@ function user.s2_1{autoCommit=true}(A0:s X_75 := algebra.leftfetchjoin(X_67,X_74); X_76 := mtime.addmonths(A3,A4); X_77 := algebra.subselect(X_75,A2,X_76,true,false,false); - X_283 := algebra.leftfetchjoin(X_77,X_67); + X_279 := algebra.leftfetchjoin(X_77,X_67); X_80 := sql.bind(X_8,"sys","lineitem","l_suppkey",0); (X_84,r1_91) := sql.bind(X_8,"sys","lineitem","l_suppkey",2); X_87 := sql.bind(X_8,"sys","lineitem","l_suppkey",1); X_89 := sql.delta(X_80,X_84,r1_91,X_87); - X_90:bat[:oid,:int] := algebra.leftfetchjoin(X_283,X_89); + X_90:bat[:oid,:int] := algebra.leftfetchjoin(X_279,X_89); X_91 := sql.bind(X_8,"sys","lineitem","l_partkey",0); (X_93,r1_101) := sql.bind(X_8,"sys","lineitem","l_partkey",2); X_95 := sql.bind(X_8,"sys","lineitem","l_partkey",1); X_96 := sql.delta(X_91,X_93,r1_101,X_95); - X_97:bat[:oid,:int] := algebra.leftfetchjoin(X_283,X_96); + X_97:bat[:oid,:int] := algebra.leftfetchjoin(X_279,X_96); (X_98,r1_107,r2_107) := group.subgroup(X_97); (X_101,r1_110,r2_110) := group.subgroupdone(X_90,X_98); X_104 := algebra.leftfetchjoin(r1_110,X_97); @@ -127,7 +127,7 @@ function user.s2_1{autoCommit=true}(A0:s (X_154,r1_176) := sql.bind(X_8,"sys","lineitem","l_quantity",2); X_157 := sql.bind(X_8,"sys","lineitem","l_quantity",1); X_159 := sql.delta(X_149,X_154,r1_176,X_157); - X_160:bat[:oid,:int] := algebra.leftfetchjoin(X_283,X_159); + X_160:bat[:oid,:int] := algebra.leftfetchjoin(X_279,X_159); X_161:bat[:oid,:lng] := aggr.subsum(X_160,X_101,r1_110,true,true); X_105:bat[:oid,:wrd] := mal.manifold("mkey","hash",X_104); X_110:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_105,22,X_109); @@ -141,7 +141,7 @@ function user.s2_1{autoCommit=true}(A0:s (X_125,r1_139) := sql.bind(X_8,"sys","partsupp","ps_suppkey",2); X_128 := sql.bind(X_8,"sys","partsupp","ps_suppkey",1); X_130 := sql.delta(X_120,X_125,r1_139,X_128); - X_131:bat[:oid,:int] := algebra.leftfetchjoin(X_282,X_130); + X_131:bat[:oid,:int] := algebra.leftfetchjoin(X_278,X_130); X_132 := algebra.leftfetchjoin(X_119,X_131); X_133 := algebra.leftfetchjoin(X_117,r1_121); X_134 := algebra.leftfetchjoin(X_133,X_109); @@ -152,7 +152,7 @@ function user.s2_1{autoCommit=true}(A0:s (X_140,r1_156) := sql.bind(X_8,"sys","partsupp","ps_availqty",2); X_142 := sql.bind(X_8,"sys","partsupp","ps_availqty",1); X_143 := sql.delta(X_138,X_140,r1_156,X_142); - X_144:bat[:oid,:int] := algebra.leftfetchjoinPath(X_137,X_282,X_143); + X_144:bat[:oid,:int] := algebra.leftfetchjoinPath(X_137,X_278,X_143); X_145:bat[:oid,:lng] := batcalc.lng(X_144,19,2); X_147 := calc.lng(1,A1,11,1); X_163:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_136,X_133,X_161); @@ -161,47 +161,45 @@ function user.s2_1{autoCommit=true}(A0:s X_166:bat[:oid,:bit] := batcalc.>(X_145,X_165); X_167 := algebra.subselect(X_166,true,true,true,true,false); X_169:bat[:oid,:int] := algebra.leftfetchjoinPath(X_167,X_137,X_131); - (X_170,r1_196,r2_196) := group.subgroupdone(X_169); - X_173 := algebra.leftfetchjoin(r1_196,X_169); - (X_174,r1_200) := algebra.join(X_21,X_173); - X_176 := algebra.tinter(X_22,X_174); - X_284 := algebra.leftfetchjoin(X_176,X_9); - X_177 := sql.bind(X_8,"sys","supplier","s_nationkey",0); - (X_180,r1_206) := sql.bind(X_8,"sys","supplier","s_nationkey",2); - X_183 := sql.bind(X_8,"sys","supplier","s_nationkey",1); - X_184 := sql.delta(X_177,X_180,r1_206,X_183); - X_185:bat[:oid,:int] := algebra.leftfetchjoin(X_284,X_184); - X_186:bat[:oid,:oid] := sql.tid(X_8,"sys","nation"); - X_188 := sql.bind(X_8,"sys","nation","n_name",0); - (X_191,r1_218) := sql.bind(X_8,"sys","nation","n_name",2); - X_194 := sql.bind(X_8,"sys","nation","n_name",1); - X_196 := sql.delta(X_188,X_191,r1_218,X_194); - X_197 := algebra.leftfetchjoin(X_186,X_196); - X_198 := algebra.subselect(X_197,A5,A5,true,true,false); - X_201 := sql.bind(X_8,"sys","nation","n_nationkey",0); - (X_203,r1_231) := sql.bind(X_8,"sys","nation","n_nationkey",2); - X_205 := sql.bind(X_8,"sys","nation","n_nationkey",1); - X_206 := sql.delta(X_201,X_203,r1_231,X_205); - X_207:bat[:oid,:int] := algebra.leftfetchjoinPath(X_198,X_186,X_206); - (X_208,r1_237) := algebra.join(X_185,X_207); - X_285 := algebra.leftfetchjoin(X_208,X_176); - X_210 := sql.bind(X_8,"sys","supplier","s_name",0); - (X_213,r1_242) := sql.bind(X_8,"sys","supplier","s_name",2); - X_216 := sql.bind(X_8,"sys","supplier","s_name",1); - X_217 := sql.delta(X_210,X_213,r1_242,X_216); - X_218:bat[:oid,:str] := algebra.leftfetchjoinPath(X_285,X_9,X_217); - (X_219,r1_250,r2_250) := algebra.subsort(X_218,false,false); - X_222 := algebra.leftfetchjoin(r1_250,X_218); - X_223 := sql.bind(X_8,"sys","supplier","s_address",0); - (X_226,r1_257) := sql.bind(X_8,"sys","supplier","s_address",2); - X_229 := sql.bind(X_8,"sys","supplier","s_address",1); - X_231 := sql.delta(X_223,X_226,r1_257,X_229); - X_232:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_250,X_285,X_9,X_231); - X_233 := sql.resultSet(2,1,X_222); - sql.rsColumn(X_233,"sys.supplier","s_name","varchar",25,0,X_222); - sql.rsColumn(X_233,"sys.supplier","s_address","varchar",40,0,X_232); - X_240 := io.stdout(); - sql.exportResult(X_240,X_233); + (X_170,r1_196) := algebra.join(X_21,X_169); + X_172 := algebra.tinter(X_22,X_170); + X_280 := algebra.leftfetchjoin(X_172,X_9); + X_173 := sql.bind(X_8,"sys","supplier","s_nationkey",0); + (X_176,r1_202) := sql.bind(X_8,"sys","supplier","s_nationkey",2); + X_178 := sql.bind(X_8,"sys","supplier","s_nationkey",1); + X_180 := sql.delta(X_173,X_176,r1_202,X_178); + X_181:bat[:oid,:int] := algebra.leftfetchjoin(X_280,X_180); + X_182:bat[:oid,:oid] := sql.tid(X_8,"sys","nation"); + X_184 := sql.bind(X_8,"sys","nation","n_name",0); + (X_187,r1_214) := sql.bind(X_8,"sys","nation","n_name",2); + X_190 := sql.bind(X_8,"sys","nation","n_name",1); + X_192 := sql.delta(X_184,X_187,r1_214,X_190); + X_193 := algebra.leftfetchjoin(X_182,X_192); + X_194 := algebra.subselect(X_193,A5,A5,true,true,false); + X_196 := sql.bind(X_8,"sys","nation","n_nationkey",0); + (X_198,r1_226) := sql.bind(X_8,"sys","nation","n_nationkey",2); + X_200 := sql.bind(X_8,"sys","nation","n_nationkey",1); + X_201 := sql.delta(X_196,X_198,r1_226,X_200); + X_202:bat[:oid,:int] := algebra.leftfetchjoinPath(X_194,X_182,X_201); + (X_203,r1_232) := algebra.join(X_181,X_202); + X_281 := algebra.leftfetchjoin(X_203,X_172); + X_205 := sql.bind(X_8,"sys","supplier","s_name",0); + (X_208,r1_237) := sql.bind(X_8,"sys","supplier","s_name",2); + X_211 := sql.bind(X_8,"sys","supplier","s_name",1); + X_212 := sql.delta(X_205,X_208,r1_237,X_211); + X_213:bat[:oid,:str] := algebra.leftfetchjoinPath(X_281,X_9,X_212); + (X_214,r1_245,r2_245) := algebra.subsort(X_213,false,false); + X_218 := algebra.leftfetchjoin(r1_245,X_213); + X_219 := sql.bind(X_8,"sys","supplier","s_address",0); + (X_222,r1_253) := sql.bind(X_8,"sys","supplier","s_address",2); + X_225 := sql.bind(X_8,"sys","supplier","s_address",1); + X_227 := sql.delta(X_219,X_222,r1_253,X_225); + X_228:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_245,X_281,X_9,X_227); + X_229 := sql.resultSet(2,1,X_218); + sql.rsColumn(X_229,"sys.supplier","s_name","varchar",25,0,X_218); + sql.rsColumn(X_229,"sys.supplier","s_address","varchar",40,0,X_228); + X_236 := io.stdout(); + sql.exportResult(X_236,X_229); end s2_1; # querylog.define("explain select\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 \\'forest%\\'\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 >= date \\'1994-01-01\\'\n\t\t\t\t\tand l_shipdate < date \\'1994-01-01\\' + interval \\'1\\' year\n\t\t\t)\n\t)\n\tand s_nationkey = n_nationkey\n\tand n_name = \\'CANADA\\'\norder by\n\ts_name;","sequential_pipe") diff --git a/sql/benchmarks/tpch/Tests/20-plan.stable.out b/sql/benchmarks/tpch/Tests/20-plan.stable.out --- a/sql/benchmarks/tpch/Tests/20-plan.stable.out +++ b/sql/benchmarks/tpch/Tests/20-plan.stable.out @@ -68,7 +68,7 @@ project ( | | join ( | | | semijoin ( | | | | table(sys.supplier) [ supplier.s_suppkey NOT NULL HASHCOL , supplier.s_name NOT NULL, supplier.s_address NOT NULL, supplier.s_nationkey NOT NULL ] COUNT , -| | | | group by ( +| | | | project ( | | | | | select ( | | | | | | project ( | | | | | | | join ( @@ -88,7 +88,7 @@ project ( | | | | | | | ) [ partsupp.ps_partkey NOT NULL HASHCOL = L16.l_partkey NOT NULL HASHCOL , partsupp.ps_suppkey NOT NULL = L16.l_suppkey NOT NULL HASHCOL ] | | | | | | ) [ partsupp.ps_suppkey NOT NULL, partsupp.ps_availqty NOT NULL, sys.sql_mul(decimal(11,1)[decimal(2,1) "5"], decimal(11,1)[L12.L12 NOT NULL] as L12.L12) as L13.L13 ] | | | | | ) [ decimal(19,2)[partsupp.ps_availqty NOT NULL] > L13.L13 ] -| | | | ) [ partsupp.ps_suppkey NOT NULL as L15.L14 ] [ L15.L14 NOT NULL ] +| | | | ) [ partsupp.ps_suppkey NOT NULL as L15.L14 ] | | | ) [ supplier.s_suppkey NOT NULL HASHCOL = L15.L14 NOT NULL ], | | | select ( | | | | table(sys.nation) [ nation.n_nationkey NOT NULL HASHCOL , nation.n_name NOT NULL ] COUNT _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list