Query on 100scale TPCH crash

[Shining]

Hardware: Q8200 8G Memeory 1T HardDisk
OS: CentOS 5.6 x86_64
InfiniDB: 2.2.2 x86_64

TPC-H 100scale Data
------------------------------------------------------------
select count(1) from lineitem;
+-----------+
| count(1) |
+-----------+
| 600037902 |
+-----------+
1 row in set (15.34 sec)
------------------------------------------------------------

------------------------------------------------------------
mysql> 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 '114' day group by l_returnflag,l_linestatus order by l_returnflag,l_linestatus;

ERROR 122 (HY000): There was an internal error encountered in the Calpont Engine while processing this query. The query was cancelled. You may resubmit it if you like. The error was aggregation data overflow.
------------------------------------------------------------
Calpont.xml in attach

[Robert Adams (radams-calpont)]

The error you are getting is an aggregation error.

The 90 day interval will run with decimal scale of 3. See below...
You can find more information on the decimal scale setting in Chapter 6
of the Syntax Guide.

mysql> set infinidb_use_decimal_scale = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> set infinidb_decimal_scale=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%infinidb%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| infinidb_compression_type | 1 |
| infinidb_decimal_scale | 3 |
| infinidb_ordered_only | OFF |
| infinidb_string_scan_threshold | 10 |
| infinidb_use_decimal_scale | ON |
| infinidb_vtable_mode | 1 |
+--------------------------------+-------+
6 rows in set (0.00 sec)

mysql> 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 GROUP BY L_RETURNFLAG, L_LINESTATUS ORDER BY L_RETURNFLAG, L_LINESTATUS;
+--------------+--------------+---------------+-------------------+---------------------+-----------------------+-----------+--------------+----------+-------------+
| L_RETURNFLAG | L_LINESTATUS | SUM_QTY | SUM_BASE_PRICE | SUM_DISC_PRICE | SUM_CHARGE | AVG_QTY | AVG_PRICE | AVG_DISC | COUNT_ORDER |
+--------------+--------------+---------------+-------------------+---------------------+-----------------------+-----------+--------------+----------+-------------+
| A | F | 3775127758.00 | 5660776097194.45 | 5377736402290.3890 | 5592847435349.483000 | 25.499000 | 38236.117000 | 0.050000 | 148047881 |
| N | F | 98553062.00 | 147771098385.98 | 140384966071.7980 | 145999793184.472000 | 25.502000 | 38237.199000 | 0.050000 | 3864590 |
| N | O | 7436302976.00 | 11150725681373.59 | 10593195316317.2250 | 11016932259657.305000 | 25.500000 | 38237.228000 | 0.050000 | 291619617 |
| R | F | 3775724970.00 | 5661603032745.34 | 5378513568019.4760 | 5593662258498.719000 | 25.500000 | 38236.697000 | 0.050000 | 148067261 |
+--------------+--------------+---------------+-------------------+---------------------+-----------------------+-----------+--------------+----------+-------------+
4 rows in set (2 min 1.60 sec)

[Shining (nshi-nb)]

Some other queries still can not get result:

------------------------------------------------------------
select sum(l_extendedprice* (1 - l_discount)) as revenue
from lineitem, part
where (p_partkey = l_partkey and p_brand = 'Brand#14'and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')and l_quantity >= 6 and l_quantity <= 6 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON')or(p_partkey = l_partkey and p_brand = 'Brand#43'and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 15 and l_quantity <= 15 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG')and l_shipinstruct = 'DELIVER IN PERSON')or(p_partkey = l_partkey and p_brand = 'Brand#15'and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')and l_quantity >= 27 and l_quantity <= 27 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG')and l_shipinstruct = 'DELIVER IN PERSON');
--ERROR 138 (HY000): IDB-1000: 'lineitem' and 'part' are not joined.
------------------------------------------------------------

------------------------------------------------------------
--Q21--
select s_name,s_address from supplier, nation where s_suppkey in( select ps_suppkey from partsupp, ( select sum(l_quantity) as qty_sum, l_partkey, l_suppkey from lineitem where l_shipdate >= date '1996-01-01' and l_shipdate < date '1996-01-01' + interval '1' year group by l_partkey, l_suppkey ) g where g.l_partkey = ps_partkey and g.l_suppkey = ps_suppkey and ps_availqty > 0.5 * g.qty_sum and ps_partkey in ( select p_partkey from part where p_name like 'pale%' ) ) and s_nationkey = n_nationkey and n_name = 'IRAQ' order by s_name;
--ERROR 122 (HY000): IDB-2003: Aggregation/Distinct memory limit is exceeded.
------------------------------------------------------------

------------------------------------------------------------
--Q4--
select o_orderpriority,count(*) as order_count from orders where o_orderdate >= date '1996-11-01'and o_orderdate < date '1996-11-01' + interval '3 ' month and exists (select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate) group by o_orderpriority order by o_orderpriority;
--ERROR 122 (HY000): IDB-2001: Join or subselect exceeds memory limit.
------------------------------------------------------------

------------------------------------------------------------
--Q22--
select s_name, count(distinct(l1.l_orderkey+10*l1.l_linenumber)) as numwait from supplier, orders, nation, lineitem l1 left join lineitem l2 on (l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey) left join (select l3.l_orderkey,l3.l_suppkey from lineitem l3 where l3.l_receiptdate > l3.l_commitdate) l4 on (l4.l_orderkey = l1.l_orderkey and l4.l_suppkey <> l1.l_suppkey) where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and l2.l_orderkey is not null and l4.l_orderkey is null and s_nationkey = n_nationkey and n_name = 'MOROCCO' group by s_name order by numwait desc, s_name LIMIT 100;

--ERROR 122 (HY000): There was an internal error encountered in the Calpont Engine while proc...

[Shining (nshi-nb)]

My Calpont.xml configuration

[Robert Adams (radams-calpont)]

There are several tpch100 queries which you will not be able to run as they
will exceed the memory limits.
We do not focus on tpch100 testing since we use the SSB benchmarks
which is similar but with differences that make it more suited to a
majority of large database users.

Here is a modified Query 2.

select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from part, supplier, partsupp pso, nation, region
where p_partkey = pso.ps_partkey
and s_suppkey = pso.ps_suppkey
and p_size = 15
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
and pso.ps_supplycost = (select min(psi.ps_supplycost)
-- from partsupp, supplier, nation, region
-- where p_partkey = ps_partkey
-- and s_suppkey = ps_suppkey
from partsupp psi, supplier, nation, region
where pso.ps_partkey = psi.ps_partkey
and s_suppkey = psi.ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE')
order by s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100;

Query 19

SELECT
 SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE
FROM
 LINEITEM,
 PART
WHERE
 p_partkey = l_partkey and (
 (
-- P_PARTKEY = L_PARTKEY AND
  P_BRAND = 'Brand#41' AND
  P_CONTAINER IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND
  L_QUANTITY >= 3 AND L_QUANTITY <= 3 + 10 AND
  P_SIZE BETWEEN 1 AND 5 AND
  L_SHIPMODE IN ('AIR', 'AIR REG') AND
  L_SHIPINSTRUCT = 'DELIVER IN PERSON'
 )
 OR
 (
-- P_PARTKEY = L_PARTKEY AND
  P_BRAND = 'Brand#33' AND
  P_CONTAINER IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND
  L_QUANTITY >= 14 AND L_QUANTITY <= 14 + 10 AND
  P_SIZE BETWEEN 1 AND 10 AND
  L_SHIPMODE IN ('AIR', 'AIR REG') AND
  L_SHIPINSTRUCT = 'DELIVER IN PERSON'
 )
 OR
 (
-- P_PARTKEY = L_PARTKEY AND
  P_BRAND = 'Brand#45' AND
  P_CONTAINER IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND
  L_QUANTITY >= 25 AND L_QUANTITY <= 25 + 10 AND
  P_SIZE BETWEEN 1 AND 15 AND
  L_SHIPMODE IN ('AIR', 'AIR REG') AND
  L_SHIPINSTRUCT = 'DELIVER IN PERSON'
 ));