Changeset: 186c1856eadb for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=186c1856eadb Added Files: sql/benchmarks/tpch/Tests/one.test.in Modified Files: sql/benchmarks/tpch/Tests/All Branch: mtest Log Message:
tpch default all in one test diffs (truncated from 1084 to 300 lines): diff --git a/sql/benchmarks/tpch/Tests/All b/sql/benchmarks/tpch/Tests/All --- a/sql/benchmarks/tpch/Tests/All +++ b/sql/benchmarks/tpch/Tests/All @@ -1,33 +1,1 @@ -create -check0 -load -check1 -01 -02 -03 -04 -05 -06 -#06a -07 -#07a -08 -#08a -09 -#09b -#09c -10 -11 -12 -13 -#13a -14 -15 -16 -17 -18 -19 -20 -21 -22 -lowcardinality +one diff --git a/sql/benchmarks/tpch/Tests/one.test.in b/sql/benchmarks/tpch/Tests/one.test.in new file mode 100644 --- /dev/null +++ b/sql/benchmarks/tpch/Tests/one.test.in @@ -0,0 +1,1041 @@ +statement ok +START TRANSACTION + +statement ok +CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL, + R_NAME CHAR(25) NOT NULL, + R_COMMENT VARCHAR(152), + PRIMARY KEY (R_REGIONKEY)) + +statement ok +CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL, + N_NAME CHAR(25) NOT NULL, + N_REGIONKEY INTEGER NOT NULL, + N_COMMENT VARCHAR(152), + PRIMARY KEY (N_NATIONKEY), + FOREIGN KEY (N_REGIONKEY) references REGION) + +statement ok +CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL, + P_NAME VARCHAR(55) NOT NULL, + P_MFGR CHAR(25) NOT NULL, + P_BRAND CHAR(10) NOT NULL, + P_TYPE VARCHAR(25) NOT NULL, + P_SIZE INTEGER NOT NULL, + P_CONTAINER CHAR(10) NOT NULL, + P_RETAILPRICE DECIMAL(15,2) NOT NULL, + P_COMMENT VARCHAR(23) NOT NULL, + PRIMARY KEY (P_PARTKEY) ) + +statement ok +CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL, + S_NAME CHAR(25) NOT NULL, + S_ADDRESS VARCHAR(40) NOT NULL, + S_NATIONKEY INTEGER NOT NULL, + S_PHONE CHAR(15) NOT NULL, + S_ACCTBAL DECIMAL(15,2) NOT NULL, + S_COMMENT VARCHAR(101) NOT NULL, + PRIMARY KEY (S_SUPPKEY), + FOREIGN KEY (S_NATIONKEY) references NATION) + +statement ok +CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL, + PS_SUPPKEY INTEGER NOT NULL, + PS_AVAILQTY INTEGER NOT NULL, + PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, + PS_COMMENT VARCHAR(199) NOT NULL, + PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY), + FOREIGN KEY (PS_SUPPKEY) references SUPPLIER, + FOREIGN KEY (PS_PARTKEY) references PART ) + +statement ok +CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL, + C_NAME VARCHAR(25) NOT NULL, + C_ADDRESS VARCHAR(40) NOT NULL, + C_NATIONKEY INTEGER NOT NULL, + C_PHONE CHAR(15) NOT NULL, + C_ACCTBAL DECIMAL(15,2) NOT NULL, + C_MKTSEGMENT CHAR(10) NOT NULL, + C_COMMENT VARCHAR(117) NOT NULL, + PRIMARY KEY (C_CUSTKEY), + FOREIGN KEY (C_NATIONKEY) references NATION) + +statement ok +CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL, + O_CUSTKEY INTEGER NOT NULL, + O_ORDERSTATUS CHAR(1) NOT NULL, + O_TOTALPRICE DECIMAL(15,2) NOT NULL, + O_ORDERDATE DATE NOT NULL, + O_ORDERPRIORITY CHAR(15) NOT NULL, + O_CLERK CHAR(15) NOT NULL, + O_SHIPPRIORITY INTEGER NOT NULL, + O_COMMENT VARCHAR(79) NOT NULL, + PRIMARY KEY (O_ORDERKEY), + FOREIGN KEY (O_CUSTKEY) references CUSTOMER) + +statement ok +CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL, + L_PARTKEY INTEGER NOT NULL, + L_SUPPKEY INTEGER NOT NULL, + L_LINENUMBER INTEGER NOT NULL, + L_QUANTITY DECIMAL(15,2) NOT NULL, + L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, + L_DISCOUNT DECIMAL(15,2) NOT NULL, + L_TAX DECIMAL(15,2) NOT NULL, + L_RETURNFLAG CHAR(1) NOT NULL, + L_LINESTATUS CHAR(1) NOT NULL, + L_SHIPDATE DATE NOT NULL, + L_COMMITDATE DATE NOT NULL, + L_RECEIPTDATE DATE NOT NULL, + L_SHIPINSTRUCT CHAR(25) NOT NULL, + L_SHIPMODE CHAR(10) NOT NULL, + L_COMMENT VARCHAR(44) NOT NULL, + PRIMARY KEY (L_ORDERKEY,L_LINENUMBER), + FOREIGN KEY (L_ORDERKEY) references ORDERS, + FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references PARTSUPP) + +statement ok +COMMIT + +query ITTITRTT rowsort +select * from customer +---- + +query ITIT rowsort +select * from nation +---- + +query IITRTTTIT rowsort +select * from orders +---- + +query IIIRT rowsort +select * from partsupp +---- + +query ITTTTITRT rowsort +select * from part +---- + +query ITT rowsort +select * from region +---- + +query ITTITRT rowsort +select * from supplier +---- + +query IIIIRRRRTTTTTTTT rowsort +select * from lineitem +---- + +statement ok +START TRANSACTION + +statement ok +COPY 5 RECORDS INTO region from E'$QTSTSRCBASE/sql/benchmarks/tpch/SF-0.01/region.tbl' USING DELIMITERS '|', E'|\n' + +statement ok +COPY 25 RECORDS INTO nation from E'$QTSTSRCBASE/sql/benchmarks/tpch/SF-0.01/nation.tbl' USING DELIMITERS '|', E'|\n' + +statement ok +COPY 100 RECORDS INTO supplier from E'$QTSTSRCBASE/sql/benchmarks/tpch/SF-0.01/supplier.tbl' USING DELIMITERS '|', E'|\n' + +statement ok +COPY 1500 RECORDS INTO customer from E'$QTSTSRCBASE/sql/benchmarks/tpch/SF-0.01/customer.tbl' USING DELIMITERS '|', E'|\n' + +statement ok +COPY 2000 RECORDS INTO part from E'$QTSTSRCBASE/sql/benchmarks/tpch/SF-0.01/part.tbl' USING DELIMITERS '|', E'|\n' + +statement ok +COPY 8000 RECORDS INTO partsupp from E'$QTSTSRCBASE/sql/benchmarks/tpch/SF-0.01/partsupp.tbl' USING DELIMITERS '|', E'|\n' + +statement ok +COPY 15000 RECORDS INTO orders from E'$QTSTSRCBASE/sql/benchmarks/tpch/SF-0.01/orders.tbl' USING DELIMITERS '|', E'|\n' + +statement ok +COPY 70000 RECORDS INTO lineitem from E'$QTSTSRCBASE/sql/benchmarks/tpch/SF-0.01/lineitem.tbl' USING DELIMITERS '|', E'|\n' + +statement ok +COMMIT + +query I rowsort +select count(*) from customer +---- +1500 + +query I rowsort +select count(*) from nation +---- +25 + +query I rowsort +select count(*) from orders +---- +15000 + +query I rowsort +select count(*) from partsupp +---- +8000 + +query I rowsort +select count(*) from part +---- +2000 + +query I rowsort +select count(*) from region +---- +5 + +query I rowsort +select count(*) from supplier +---- +100 + +query I rowsort +select count(*) from lineitem +---- +60175 + +query ITT rowsort +select * from region order by r_regionkey limit 9 +---- +15 values hashing to 8fe4ac6c9d3a0c3fa61683fe0a58b4c2 + +query ITIT rowsort +select * from nation order by n_nationkey limit 9 +---- +36 values hashing to d1210adfcf46c083f97818fd2a44744c + +query ITTITRT rowsort +select * from supplier order by s_suppkey limit 9 +---- +63 values hashing to 0376bc5d5aa0430263e54747bab6a79f + +query ITTITRTT rowsort +select * from customer order by c_custkey limit 9 +---- +72 values hashing to e63573178f338644bf53d9bc54763aba + +query ITTTTITRT rowsort +select * from part order by p_partkey limit 9 +---- +81 values hashing to ce9be78ce56584254906028a4eb7bc37 + +query IIIRT rowsort +select * from partsupp order by ps_partkey, ps_suppkey limit 9 +---- +45 values hashing to 67cf5106a464cc6417724118e75dccb7 + +query IITRTTTIT rowsort +select * from orders order by o_orderkey limit 9 +---- +81 values hashing to 618840147d6f43b9f017635da99d9c01 + +query IIIIRRRRTTTTTTTT rowsort +select * from lineitem order by l_orderkey, l_linenumber limit 9 +---- +144 values hashing to 5b3482da1afab8d411eeb26ffee69dd0 + +query TTRRRRRRRI rowsort +select + l_returnflag, + l_linestatus, + sum(l_quantity) as sum_qty, + sum(l_extendedprice) as sum_base_price, + sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, + sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, + avg(l_quantity) as avg_qty, + avg(l_extendedprice) as avg_price, + avg(l_discount) as avg_disc, + count(*) as count_order +from + lineitem +where + l_shipdate <= date '1998-12-01' - interval '90' day (3) _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
