Closed
Description
Issue Description:
During the creation of the Epinions database in Oracle Database 19c/23c, we encounter the following error on using benchmarks/epinions/ddl-oracle.sql :
[INFO ] 2023-07-13 06:27:28,950 [main] com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 06:27:29,016 [main] com.oltpbenchmark.DBWorkload main (406) - Creating new EPINIONS database...
[ERROR] 2023-07-13 06:27:35,886 [main] com.oltpbenchmark.DBWorkload main (411) - Unexpected error when creating benchmark database tables.
java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:122)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1199)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1819)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1471)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2504)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2459)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:327)
at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:125)
at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:82)
at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:71)
at com.oltpbenchmark.api.BenchmarkModule.createDatabase(BenchmarkModule.java:234)
at com.oltpbenchmark.api.BenchmarkModule.createDatabase(BenchmarkModule.java:213)
at com.oltpbenchmark.DBWorkload.runCreator(DBWorkload.java:628)
at com.oltpbenchmark.DBWorkload.main(DBWorkload.java:407)
Caused by: Error : 902, Position : 122, Sql = CREATE TABLE useracct ( u_id int NOT NULL, name varchar(128) NOT NULL, email varchar(128) NOT NULL, creation_date datetime DEFAULT NULL, PRIMARY KEY (u_id) ), OriginalSql = CREATE TABLE useracct ( u_id int NOT NULL, name varchar(128) NOT NULL, email varchar(128) NOT NULL, creation_date datetime DEFAULT NULL, PRIMARY KEY (u_id) ), Error Msg = ORA-00902: invalid datatype
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
... 19 more
Description:
To address this issue, the benchmarks/epinions/ddl-oracle.sq script is updated to resolve the invalid datatypes for Oracle SQL. Additionally, the reserved keyword "comment" is enclosed in double quotes to escape it and differentiate it from the reserved keyword. However, using quoted table names creates an issue when validating whether the tables have been created or not (getCatalog()). Therefore, the table names are unquoted in the script to ensure proper table validation/identification.
-- (benchmarks/epinions/ddl-oracle.sql)
-- Epinions DDL for Oracle DDL
-- Drop all tables
BEGIN EXECUTE IMMEDIATE 'DROP TABLE review'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE review_rating'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE trust'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE item'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE useracct'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
-- create tables
CREATE TABLE useracct (
u_id number(11,0) NOT NULL,
name varchar(128) NOT NULL,
email varchar(128) NOT NULL,
creation_date date DEFAULT NULL,
PRIMARY KEY (u_id)
);
CREATE TABLE item (
i_id number(11,0) NOT NULL,
title varchar(128) NOT NULL,
description varchar(512) DEFAULT NULL,
creation_date date DEFAULT NULL,
PRIMARY KEY (i_id)
);
CREATE TABLE review (
a_id number(11,0) NOT NULL,
u_id number(11,0) NOT NULL REFERENCES useracct (u_id),
i_id number(11,0) NOT NULL REFERENCES item (i_id),
rating number(11,0) DEFAULT NULL,
rank number(11,0) DEFAULT NULL,
"comment" varchar(256) DEFAULT NULL,
creation_date date DEFAULT NULL
);
CREATE TABLE review_rating (
u_id number(11,0) NOT NULL REFERENCES useracct (u_id),
a_id number(11,0) NOT NULL,
rating number(11,0) NOT NULL,
status number(11,0) NOT NULL,
creation_date date DEFAULT NULL,
last_mod_date date DEFAULT NULL,
type number(11,0) DEFAULT NULL,
vertical_id number(11,0) DEFAULT NULL
);
CREATE TABLE trust (
source_u_id number(11,0) NOT NULL REFERENCES useracct (u_id),
target_u_id number(11,0) NOT NULL REFERENCES useracct (u_id),
trust number(11,0) NOT NULL,
creation_date date DEFAULT NULL
);
-- create indexes
CREATE INDEX IDX_REVIEW_RATING_UID ON review_rating (u_id);
CREATE INDEX IDX_REVIEW_RATING_AID ON review_rating (a_id);
CREATE INDEX IDX_TRUST_SID ON trust (source_u_id);
CREATE INDEX IDX_TRUST_TID ON trust (target_u_id);
CREATE INDEX IDX_RATING_UID ON review (u_id);
CREATE INDEX IDX_RATING_AID ON review (a_id);
CREATE INDEX IDX_RATING_IID ON review (i_id);Since we have removed the double quotes from the table names in the benchmarks/epinions/ddl-oracle.sql, We have to do the same for the tables used in the benchmarks/epinions/dialect-oracle.xml file for proper execution of the epinions benchmark. After making these two changes the epinions benchmark runs fine. Here’s the updated dialect file given below :
(benchmarks/epinions/dialect-oracle.xml)
<?xml version="1.0"?>
<dialects>
<dialect type="ORACLE">
<procedure name="GetAverageRatingByTrustedUser">
<statement name="getAverageRating">
SELECT avg(rating) FROM review r, trust t WHERE r.u_id=t.target_u_id AND r.i_id=? AND t.source_u_id=?
</statement>
</procedure>
<procedure name="GetItemAverageRating">
<statement name="getAverageRating">
SELECT avg(rating) FROM review r WHERE r.i_id=?
</statement>
</procedure>
<procedure name="GetItemReviewsByTrustedUser">
<statement name="getReview">
SELECT * FROM review r WHERE r.i_id=? ORDER BY creation_date DESC
</statement>
<statement name="getTrust">
SELECT * FROM trust t WHERE t.source_u_id=?
</statement>
</procedure>
<procedure name="GetReviewItemById">
<statement name="getReviewItem">
SELECT * FROM review r, item i WHERE i.i_id = r.i_id and r.i_id=? AND ROWNUM <= 10
ORDER BY rating DESC, r.creation_date DESC
</statement>
</procedure>
<procedure name="GetReviewsByUser">
<statement name="getReviewUser">
SELECT * FROM review r, useracct u WHERE u.u_id = r.u_id AND r.u_id=? AND ROWNUM <= 10
ORDER BY rating DESC, r.creation_date DESC
</statement>
</procedure>
<procedure name="UpdateItemTitle">
<statement name="updateItem">
UPDATE item SET title = ? WHERE i_id=?
</statement>
</procedure>
<procedure name="UpdateReviewRating">
<statement name="updateReview">
UPDATE review SET rating = ? WHERE i_id=? AND u_id=?
</statement>
</procedure>
<procedure name="UpdateTrustRating">
<statement name="updateTrust">
UPDATE trust SET trust = ? WHERE source_u_id=? AND target_u_id=?
</statement>
</procedure>
<procedure name="UpdateUserName">
<statement name="updateUser">
UPDATE useracct SET name = ? WHERE u_id=?
</statement>
</procedure>
</dialect>
</dialects>