Changeset: 4d62e4202ead for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=4d62e4202ead
Modified Files:
sql/benchmarks/tpch/14.sql
sql/benchmarks/tpch/Tests/14-explain.stable.out.int128
sql/benchmarks/tpch/Tests/14-plan.stable.out.int128
Branch: default
Log Message:
Merge with Pct2014 branch.
diffs (147 lines):
diff --git a/sql/benchmarks/tpch/14.sql b/sql/benchmarks/tpch/14.sql
--- a/sql/benchmarks/tpch/14.sql
+++ b/sql/benchmarks/tpch/14.sql
@@ -1,3 +1,15 @@
-select 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 -
l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) as
promo_revenue from lineitem, part where l_partkey = p_partkey and l_shipdate >=
date '1995-09-01' and l_shipdate < date '1995-09-01' + interval '1' month;
+select
+ 100.00 * sum(case
+ when p_type like 'PROMO%'
+ then l_extendedprice * (1 - l_discount)
+ else 0
+ end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
+from
+ lineitem,
+ part
+where
+ l_partkey = p_partkey
+ and l_shipdate >= date '1995-09-01'
+ and l_shipdate < date '1995-09-01' + interval '1' month;
select * from optimizer_stats() stats;
diff --git a/sql/benchmarks/tpch/Tests/14-explain.stable.out.int128
b/sql/benchmarks/tpch/Tests/14-explain.stable.out.int128
--- a/sql/benchmarks/tpch/Tests/14-explain.stable.out.int128
+++ b/sql/benchmarks/tpch/Tests/14-explain.stable.out.int128
@@ -26,12 +26,74 @@ Ready.
# 22:53:28 >
#set optimizer = 'sequential_pipe';
-#select 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1
- l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) as
promo_revenue from lineitem, part where l_partkey = p_partkey and l_shipdate >=
date '1995-09-01' and l_shipdate < date '1995-09-01' + interval '1' month;
-% .L2 # table_name
-% promo_revenue # name
-% decimal # type
-% 41 # length
-[ 15.48 ]
+#explain select
+# 100.00 * sum(case
+# when p_type like 'PROMO%'
+# then l_extendedprice * (1 - l_discount)
+# else 0
+# end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
+#from
+# lineitem,
+# part
+#where
+# l_partkey = p_partkey
+# and l_shipdate >= date '1995-09-01'
+# and l_shipdate < date '1995-09-01' + interval '1' month;
+% .explain # table_name
+% mal # name
+% clob # type
+% 424 # length
+function
user.s2_1{autoCommit=true}(A0:sht,A1:str,A2:bte,A3:bte,A4:bte,A5:date,A6:date,A7:int):void;
+ X_94:void := querylog.define("explain select\n\t100.00 *
sum(case\n\t\twhen p_type like \\'PROMO%\\'\n\t\t\tthen l_extendedprice * (1 -
l_discount)\n\t\telse 0\n\tend) / sum(l_extendedprice * (1 - l_discount)) as
promo_revenue\nfrom\n\tlineitem,\n\tpart\nwhere\n\tl_partkey = p_partkey\n\tand
l_shipdate >= date \\'1995-09-01\\'\n\tand l_shipdate < date \\'1995-09-01\\' +
interval \\'1\\' month;","sequential_pipe",51);
+ X_10 := sql.mvc();
+ X_14:bat[:oid,:date] := sql.bind(X_10,"sys","lineitem","l_shipdate",0);
+ X_11:bat[:oid,:oid] := sql.tid(X_10,"sys","lineitem");
+ X_22 := mtime.addmonths(A6,A7);
+ X_114 := algebra.subselect(X_14,X_11,A5,X_22,true,false,false);
+ (X_17,r1_18) := sql.bind(X_10,"sys","lineitem","l_shipdate",2);
+ X_115 :=
algebra.subselect(r1_18,nil:bat[:oid,:oid],A5,X_22,true,false,false);
+ X_20:bat[:oid,:date] := sql.bind(X_10,"sys","lineitem","l_shipdate",1);
+ X_117 := algebra.subselect(X_20,X_11,A5,X_22,true,false,false);
+ X_23 := sql.subdelta(X_114,X_11,X_17,X_115,X_117);
+ X_25:bat[:oid,:oid] :=
sql.bind_idxbat(X_10,"sys","lineitem","lineitem_l_partkey_fkey",0);
+ (X_27,r1_33) :=
sql.bind_idxbat(X_10,"sys","lineitem","lineitem_l_partkey_fkey",2);
+ X_29:bat[:oid,:oid] :=
sql.bind_idxbat(X_10,"sys","lineitem","lineitem_l_partkey_fkey",1);
+ X_30 := sql.projectdelta(X_23,X_25,X_27,r1_33,X_29);
+ X_31:bat[:oid,:oid] := sql.tid(X_10,"sys","part");
+ (X_33,r1_41) := algebra.subjoin(X_30,X_31,nil:BAT,nil:BAT,false,nil:lng);
+ X_37:bat[:oid,:str] := sql.bind(X_10,"sys","part","p_type",0);
+ (X_40,r1_48) := sql.bind(X_10,"sys","part","p_type",2);
+ X_42:bat[:oid,:str] := sql.bind(X_10,"sys","part","p_type",1);
+ X_43 := sql.projectdelta(X_31,X_37,X_40,r1_48,X_42);
+ X_44 := algebra.leftfetchjoin(r1_41,X_43);
+ X_45:bat[:oid,:bit] := batalgebra.like(X_44,A1);
+ X_46:bat[:oid,:bit] := batcalc.isnil(X_45);
+ X_47:bat[:oid,:bit] := batcalc.ifthenelse(X_46,false,X_45);
+ X_48:bat[:oid,:lng] := sql.bind(X_10,"sys","lineitem","l_extendedprice",0);
+ (X_52,r1_70) := sql.bind(X_10,"sys","lineitem","l_extendedprice",2);
+ X_55:bat[:oid,:lng] := sql.bind(X_10,"sys","lineitem","l_extendedprice",1);
+ X_57 := sql.projectdelta(X_23,X_48,X_52,r1_70,X_55);
+ X_58 := algebra.leftfetchjoin(X_33,X_57);
+ X_60 := calc.lng(A2,15,2);
+ X_62:bat[:oid,:lng] := sql.bind(X_10,"sys","lineitem","l_discount",0);
+ (X_65,r1_88) := sql.bind(X_10,"sys","lineitem","l_discount",2);
+ X_67:bat[:oid,:lng] := sql.bind(X_10,"sys","lineitem","l_discount",1);
+ X_68 := sql.projectdelta(X_23,X_62,X_65,r1_88,X_67);
+ X_69 := algebra.leftfetchjoin(X_33,X_68);
+ X_70:bat[:oid,:lng] := batcalc.-(X_60,X_69);
+ X_71:bat[:oid,:hge] := batcalc.*(X_58,X_70);
+ X_72 := calc.hge(A3,33,4);
+ X_74:bat[:oid,:hge] := batcalc.ifthenelse(X_47,X_71,X_72);
+ X_75:hge := aggr.sum(X_74);
+ X_76 := calc.hge(4,X_75,37,8);
+ X_80 := calc.lng(A4,15,2);
+ X_81:bat[:oid,:lng] := batcalc.-(X_80,X_69);
+ X_82:bat[:oid,:hge] := batcalc.*(X_58,X_81);
+ X_83:hge := aggr.sum(X_82);
+ X_84 := calc./(X_76,X_83);
+ X_85 := calc.*(A0,X_84);
+ sql.exportValue(1,".L2","promo_revenue","decimal",39,2,10,X_85,"");
+end user.s2_1;
# 22:53:29 >
# 22:53:29 > "Done."
diff --git a/sql/benchmarks/tpch/Tests/14-plan.stable.out.int128
b/sql/benchmarks/tpch/Tests/14-plan.stable.out.int128
--- a/sql/benchmarks/tpch/Tests/14-plan.stable.out.int128
+++ b/sql/benchmarks/tpch/Tests/14-plan.stable.out.int128
@@ -25,12 +25,33 @@ Ready.
# 22:46:32 > "/usr/bin/python2" "14-plan.SQL.py" "14-plan"
# 22:46:32 >
-#select 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1
- l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) as
promo_revenue from lineitem, part where l_partkey = p_partkey and l_shipdate >=
date '1995-09-01' and l_shipdate < date '1995-09-01' + interval '1' month;
-% .L2 # table_name
-% promo_revenue # name
-% decimal # type
-% 41 # length
-[ 15.48 ]
+#plan select
+# 100.00 * sum(case
+# when p_type like 'PROMO%'
+# then l_extendedprice * (1 - l_discount)
+# else 0
+# end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
+#from
+# lineitem,
+# part
+#where
+# l_partkey = p_partkey
+# and l_shipdate >= date '1995-09-01'
+# and l_shipdate < date '1995-09-01' + interval '1' month;
+% .plan # table_name
+% rel # name
+% clob # type
+% 588 # length
+project (
+| group by (
+| | join (
+| | | select (
+| | | | table(sys.lineitem) [ lineitem.l_extendedprice NOT NULL,
lineitem.l_discount NOT NULL, lineitem.l_shipdate NOT NULL,
lineitem.%lineitem_l_partkey_fkey NOT NULL JOINIDX
sys.lineitem.lineitem_l_partkey_fkey ] COUNT
+| | | ) [ date "1995-09-01" <= lineitem.l_shipdate NOT NULL < sys.sql_add(date
"1995-09-01", month_interval "1") ],
+| | | table(sys.part) [ part.p_type NOT NULL, part.%TID% NOT NULL ] COUNT
+| | ) [ lineitem.%lineitem_l_partkey_fkey NOT NULL = part.%TID% NOT NULL
JOINIDX sys.lineitem.lineitem_l_partkey_fkey ]
+| ) [ ] [ sys.sum no nil
(sys.ifthenelse(sys.ifthenelse(sys.isnull(sys.like(part.p_type NOT NULL,
char[char(6) "PROMO%"])), boolean "false", sys.like(part.p_type NOT NULL,
char[char(6) "PROMO%"])), sys.sql_mul(decimal(33,2)[lineitem.l_extendedprice
NOT NULL] as lineitem.l_extendedprice, sys.sql_sub(decimal(15,2)[tinyint "1"],
lineitem.l_discount NOT NULL)), decimal(33,4)[tinyint "0"])) as L1.L1, sys.sum
no nil (sys.sql_mul(decimal(33,2)[lineitem.l_extendedprice NOT NULL] as
lineitem.l_extendedprice, sys.sql_sub(decimal(15,2)[tinyint "1"],
lineitem.l_discount NOT NULL))) as L2.L2 ]
+) [ sys.sql_mul(decimal(4,1) "1", sys.sql_div(decimal(37,8)[L1] as L1, L2)) as
L2.promo_revenue ]
# 22:46:32 >
# 22:46:32 > "Done."
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
