0

I am doing performance testing on Apache Derby (10.14.2). I am using TPCH benchmarking for the same. I have completed the dbgen part of the TPCH and populated the database. There are 22 queries in the TPCH benchmarking queries. I am not able to convert the 1st query to suit the syntax of the Apache Derby. In the make file, I gave the DB as DB2. Since there is no Apache Derby option present there.

Query is as follows:

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 <= '1998-12-01' - interval ':1' day (3)
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

Error from the ij tool:

    ERROR 42X01: Syntax error: Encountered "\':1\'" at line 15, column 47.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.

Is there a way to generate the queries for Apache Derby in TPCH. Or tool which can covert the queries to Apache Derby.

Thanks in advance.

Curious
  • 35
  • 1
  • 8
  • I don't know Derby but in all other DBMS, you would need `'1998-12-01' - (interval '1' day) * :1` - that is multiply an interval of the base unit with your parameter –  Feb 19 '19 at 07:05
  • @a_horse_with_no_name I tried it. Not working in Derby. Thanks for your comment. – Curious Feb 19 '19 at 09:27

1 Answers1

1

You can try the TIMESTAMPADD() function:

WHERE l_shipdate <= CAST({fn TIMESTAMPADD(SQL_TSI_DAY, -1, CAST('1998-12-01 00:00:00' AS TIMESTAMP))} AS DATE)
forpas
  • 160,666
  • 10
  • 38
  • 76
  • For more related questions about the Derby timestamp functions, try: https://stackoverflow.com/a/12138416/193453 , https://stackoverflow.com/questions/23225772/derby-sql-timestamp-arithmetic-and-jdbc-escape-syntax, https://stackoverflow.com/a/27847994/193453 , https://stackoverflow.com/questions/11511805/using-timestampdiff-in-a-derby-where-clause – Bryan Pendleton Feb 19 '19 at 14:29
  • @forpas Thank for your answer. But I am getting the following error: `ERROR 42X45: CHAR is an invalid type for argument number 3 of TIMESTAMPADD.` – Curious Feb 19 '19 at 15:04
  • @Curious Try casting `'1998-12-01'` to `DATE` or `TIMESTAMP` like my edited answer. – forpas Feb 19 '19 at 16:34
  • One parenthesis `)` was missing before `}`. I edited. – forpas Feb 19 '19 at 16:40
  • I am getting the following error: `ERROR 42818: Comparisons between 'DATE' and 'TIMESTAMP' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')` – Curious Feb 19 '19 at 16:47
  • What data type is `l_shipdate`? – forpas Feb 19 '19 at 16:50
  • Datatype of `l_shipdate` is `DATE`. – Curious Feb 19 '19 at 16:53
  • @forpas Do you know how to generate the TPC-H queries for Apache Derby. I am not able to understand the readme document completely for qgen – Curious Feb 19 '19 at 16:58
  • I'm glad it worked. No I haven't dealt with nor even heard of `TPC-H queries`. – forpas Feb 19 '19 at 17:03
  • @forpas Do you not the equivalent of `datepart` in Derby – Curious Feb 21 '19 at 08:19
  • @Curious there is not 1 like datepart (I think), but there are function like YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND(). Read this: https://db.apache.org/derby/docs/10.1/ref/rrefsqlj29026.html – forpas Feb 21 '19 at 08:54