Compatibility Issues with Oracle SQL for Voter Benchmark

[AshishVirdi]

Issue 1 : Description:

While creating Database for Voter Benchmark in Oracle Database 19c/23c, since bigint datatype is not natively supported by Oracle sql, the following error occurs :

[INFO ] 2023-07-13 01:27:58,225 [main]  com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 01:27:58,293 [main]  com.oltpbenchmark.DBWorkload main (406) - Creating new VOTER database...
[ERROR] 2023-07-13 01:28:06,440 [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 : 42, Sql = CREATE TABLE VOTES (   vote_id            bigint     NOT NULL,   phone_number       bigint     NOT NULL,   state              varchar(2) NOT NULL,   contestant_number  integer    NOT NULL REFERENCES CONTESTANTS (contestant_number),   created            timestamp  NOT NULL ), OriginalSql = CREATE TABLE VOTES (   vote_id            bigint     NOT NULL,   phone_number       bigint     NOT NULL,   state              varchar(2) NOT NULL,   contestant_number  integer    NOT NULL REFERENCES CONTESTANTS (contestant_number),   created            timestamp  NOT NULL ), Error Msg = ORA-00902: invalid datatype

Description:

We add a new oracle specific sql file i.e. benchmarks/voter/ddl-oracle.sql as it's not already present. Here phone_number will not exceed 10 digits so we are using NUMBER(11, 0) and for vote_id we use NUMBER(19, 0), since its range is considered to be equivalent to that of bigint. (For Oracle Sql)

-- Before changes
-- votes table holds every valid vote.
--   voters are not allowed to submit more than <x> votes, x is passed to client application
CREATE TABLE VOTES
(
  vote_id            bigint     NOT NULL,
  phone_number       bigint     NOT NULL,
  state              varchar(2) NOT NULL,
  contestant_number  integer    NOT NULL REFERENCES CONTESTANTS (contestant_number),
  created            timestamp  NOT NULL
);
CREATE INDEX idx_votes_phone_number ON VOTES (phone_number);

-- After changes (new file - ddl-oracle.sql)
-- votes table holds every valid vote.
--   voters are not allowed to submit more than <x> votes, x is passed to client application
CREATE TABLE VOTES
(
  vote_id            NUMBER(19, 0)     NOT NULL,
  phone_number       NUMBER(11, 0)     NOT NULL,
  state              varchar(2) NOT NULL,
  contestant_number  integer    NOT NULL REFERENCES CONTESTANTS (contestant_number),
  created            timestamp  NOT NULL
);
CREATE INDEX idx_votes_phone_number ON VOTES (phone_number);

---

Issue 2 : Description:

Create step works fine following the above resolution. But at the execute step, the following error is displayed repeatedly. This is because NOW() function is not available in Oracle sql and therefore we use SYSTIMESTAMP which is an equivalent for NOW() and this ensures that all errors are resolved and proper timestamps are stored in the votes table.

[INFO ] 2023-07-13 01:31:35,716 [main]  com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 01:31:46,743 [main]  com.oltpbenchmark.DBWorkload runWorkload (640) - Creating 10 virtual terminals...
[INFO ] 2023-07-13 01:32:19,465 [main]  com.oltpbenchmark.DBWorkload runWorkload (644) - Launching the VOTER Benchmark with 1 Phase...
[INFO ] 2023-07-13 01:32:19,476 [main]  com.oltpbenchmark.ThreadBench runRateLimitedMultiPhase (128) - PHASE START :: [Workload=VOTER] [Serial=false] [Time=300] [WarmupTime=0] [Rate=10000] [Arrival=REGULAR] [Ratios=[100.0]] [ActiveWorkers=10]
[INFO ] 2023-07-13 01:32:19,483 [main]  com.oltpbenchmark.ThreadBench runRateLimitedMultiPhase (280) - MEASURE :: Warmup complete, starting measurements.
[WARN ] 2023-07-13 01:32:21,624 [VoterWorker<000>]  com.oltpbenchmark.api.Worker doWork (449) - SQLException occurred during [com.oltpbenchmark.benchmarks.voter.procedures.Vote/01] and will not be retried... sql state [42000], error code [904].
java.sql.SQLSyntaxErrorException: ORA-00904: "NOW": invalid identifier

	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.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:162)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1240)
	at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1819)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1471)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3760)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4135)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1013)
	at com.oltpbenchmark.benchmarks.voter.procedures.Vote.run(Vote.java:129)
	at com.oltpbenchmark.benchmarks.voter.VoterWorker.executeWork(VoterWorker.java:46)
	at com.oltpbenchmark.api.Worker.doWork(Worker.java:416)
	at com.oltpbenchmark.api.Worker.run(Worker.java:282)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: Error : 904, Position : 105, Sql = INSERT INTO votes (vote_id, phone_number, state, contestant_number, created) VALUES (:1 , :2 , :3 , :4 , NOW()), OriginalSql = INSERT INTO votes (vote_id, phone_number, state, contestant_number, created) VALUES (?, ?, ?, ?, NOW()), Error Msg = ORA-00904: "NOW": invalid identifier

Description:

We create a dialect file i.e. benchmarks/voter/dialect-oracle.xml which is used to execute the corrected insertVoteStmt statement with SYSTIMESTAMP.

<?xml version="1.0"?>
<dialects>
    <dialect type="ORACLE">
        <procedure name="Vote">
            <statement name="insertVoteStmt">
                INSERT INTO votes (vote_id, phone_number, state, contestant_number, created) VALUES (?, ?, ?, ?, SYSTIMESTAMP)
            </statement>
        </procedure>
    </dialect>
</dialects>

Also remove semi-colons from all the statements inside SQLStmt() in Vote.java.
Otherwise we get this : Error Msg = ORA-00933: unexpected token at or near

-- After changes
public final SQLStmt checkContestantStmt = new SQLStmt(
          "SELECT contestant_number FROM " + TABLENAME_CONTESTANTS + " WHERE contestant_number = ?"
  );
  // Checks if the voter has exceeded their allowed number of votes
  public final SQLStmt checkVoterStmt = new SQLStmt(
          "SELECT COUNT(*) FROM " + TABLENAME_VOTES + " WHERE phone_number = ?"
  );
  // Checks an area code to retrieve the corresponding state
  public final SQLStmt checkStateStmt = new SQLStmt(
          "SELECT state FROM " + TABLENAME_LOCATIONS + " WHERE area_code = ?"
  );
  // Records a vote
  public final SQLStmt insertVoteStmt = new SQLStmt(
          "INSERT INTO " + TABLENAME_VOTES + " (vote_id, phone_number, state, contestant_number, created) " +
                  "VALUES (?, ?, ?, ?, NOW())"
  );

[apavlo]

@AshishVirdi Can you submit a PR with this fix? This is amazing!

[bpkroth]

@AshishVirdi can you please check to see if this is still relevant after #379 got merged? Thanks!