SiBench Compatibility Issue: 'LIMIT' Clause Not Supported in Oracle SQL

[AshishVirdi]

Issue Description:

While running the SiBench Benchmark in Oracle Database 19c/23c, the following error was being displayed repeatedly as the LIMIT clause is not supported by Oracle SQL:

[INFO ] 2023-07-13 00:38:00,148 [main]  com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 00:38:07,565 [main]  com.oltpbenchmark.DBWorkload runWorkload (640) - Creating 2 virtual terminals...
[INFO ] 2023-07-13 00:38:19,717 [main]  com.oltpbenchmark.DBWorkload runWorkload (644) - Launching the SIBENCH Benchmark with 1 Phase...
[INFO ] 2023-07-13 00:38:19,729 [main]  com.oltpbenchmark.ThreadBench runRateLimitedMultiPhase (128) - PHASE START :: [Workload=SIBENCH] [Serial=false] [Time=300] [WarmupTime=0] [Rate=unlimited] [Arrival=REGULAR] [Ratios=[50.0, 50.0]] [ActiveWorkers=2]
[INFO ] 2023-07-13 00:38:20,732 [main]  com.oltpbenchmark.ThreadBench runRateLimitedMultiPhase (280) - MEASURE :: Warmup complete, starting measurements.
[WARN ] 2023-07-13 00:38:22,153 [SIWorker<001>]  com.oltpbenchmark.api.Worker doWork (449) - SQLException occurred during [com.oltpbenchmark.benchmarks.sibench.procedures.MinRecord/01] and will not be retried... sql state [42000], error code [933].
java.sql.SQLSyntaxErrorException: ORA-00933: unexpected token at or near LIMIT

	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.executeForDescribe(T4CPreparedStatement.java:1009)
	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1270)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1148)
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1660)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1469)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3760)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3935)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1101)
	at com.oltpbenchmark.benchmarks.sibench.procedures.MinRecord.run(MinRecord.java:35)
	at com.oltpbenchmark.benchmarks.sibench.SIWorker.minRecord(SIWorker.java:63)
	at com.oltpbenchmark.benchmarks.sibench.SIWorker.executeWork(SIWorker.java:53)
	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 : 933, Position : 41, Sql = SELECT id FROM sitest ORDER BY value ASC LIMIT 1, OriginalSql = SELECT id FROM sitest ORDER BY value ASC LIMIT 1, Error Msg = ORA-00933: unexpected token at or near LIMIT

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
	... 20 more

Description:

Since the issue was only limited to minStmt of MinRecord procedure, therefore adding a dialect file i.e. benchmarks/sibench/dialect-oracle.xml resolved the issue.

<?xml version="1.0"?>
<dialects>
	<dialect type="ORACLE">
		<procedure name="MinRecord">
			<statement name="minStmt">    
			     SELECT id FROM ( SELECT id FROM sitest ORDER BY value ASC ) WHERE ROWNUM &lt;= 1         
			</statement>
		</procedure>
	</dialect>
</dialects>  

[apavlo]

@AshishVirdi Please send a PR.

[bpkroth]

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