Invalid identifier and table names issue during Twitter benchmark creation in Oracle Database

[AshishVirdi]

Issue Description:

During the creation of the Twitter benchmark database in Oracle Database 19c/23c, an error occurred indicating that the column name "uid" does not exist in the "user_profiles" table. This error is accurate because the "user_profiles" table contains column named "uuid" instead of "uid". Additionally, there was an issue with double-quoted table names during validation, requiring the unquoting of table names.

[INFO ] 2023-07-13 07:29:09,274 [main]  com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 07:29:09,337 [main]  com.oltpbenchmark.DBWorkload main (406) - Creating new TWITTER database...
[ERROR] 2023-07-13 07:29:16,853 [main]  com.oltpbenchmark.DBWorkload main (411) - Unexpected error when creating benchmark database tables.
java.sql.SQLSyntaxErrorException: ORA-00904: UID@!: 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.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 : 904, Position : 73, Sql = CREATE TABLE "followers" (   f1 int NOT NULL REFERENCES "user_profiles" (uid),   f2 int NOT NULL REFERENCES "user_profiles" (uid),   CONSTRAINT follower_key PRIMARY KEY (f1,f2) ), OriginalSql = CREATE TABLE "followers" (   f1 int NOT NULL REFERENCES "user_profiles" (uid),   f2 int NOT NULL REFERENCES "user_profiles" (uid),   CONSTRAINT follower_key PRIMARY KEY (f1,f2) ), Error Msg = ORA-00904: UID@!: invalid identifier

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

Description:

To resolve the issues encountered during the creation of the Twitter benchmark database on Oracle Database 19c/23c, the following changes were made:

1. The benchmarks/twitter/ddl-oracle.sql file was fixed to address proper database schema creation or execution of the DDL script. The updated benchmarks/twitter/ddl-oracle.sql file is provided below. After applying these fixes, both the create and load steps worked without errors.

-- (benchmarks/epinions/ddl-oracle.sql)

BEGIN EXECUTE IMMEDIATE 'DROP TABLE added_tweets'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE tweets'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE user_profiles'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;

CREATE TABLE user_profiles (
  uuid int NOT NULL,
  name varchar2(255) DEFAULT NULL,
  email varchar2(255) DEFAULT NULL,
  partitionid number(11,0) DEFAULT NULL,
  partitionid2 number(3,0) DEFAULT NULL,
  followers number(11,0) DEFAULT NULL,
  CONSTRAINT uid_key PRIMARY KEY (uuid)
);
CREATE INDEX IDX_USER_FOLLOWERS ON user_profiles (followers);
CREATE INDEX IDX_USER_PARTITION ON user_profiles (partitionid);

BEGIN EXECUTE IMMEDIATE 'DROP TABLE followers'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
CREATE TABLE followers (
  f1 int NOT NULL REFERENCES user_profiles (uuid),
  f2 int NOT NULL REFERENCES user_profiles (uuid),
  CONSTRAINT follower_key PRIMARY KEY (f1,f2)
);

BEGIN EXECUTE IMMEDIATE 'DROP TABLE follows'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
CREATE TABLE follows (
  f1 int NOT NULL REFERENCES user_profiles (uuid),
  f2 int NOT NULL REFERENCES user_profiles (uuid),
  CONSTRAINT follows_key PRIMARY KEY (f1,f2)
);

-- TODO: id AUTO_INCREMENT

CREATE TABLE tweets (
  id number(19,0) NOT NULL,
  uuid int NOT NULL REFERENCES user_profiles (uuid),
  text char(140) NOT NULL,
  createdate date DEFAULT NULL,
  CONSTRAINT tweetid_key PRIMARY KEY (id)
);
CREATE INDEX IDX_TWEETS_uuid ON tweets (uuid);

CREATE TABLE added_tweets (
  id number(19,0) NOT NULL,
  uuid int NOT NULL REFERENCES user_profiles (uuid),
  text char(140) NOT NULL,
  createdate date DEFAULT NULL,
  CONSTRAINT new_tweet_id PRIMARY KEY (id)
);
CREATE INDEX IDX_ADDED_TWEETS_uuid ON added_tweets (uuid);


-- sequence
DECLARE cnt NUMBER; BEGIN SELECT count(*) INTO cnt FROM all_sequences WHERE sequence_name = 'TWEET_IDSEQ'; IF cnt > 0 THEN EXECUTE IMMEDIATE 'DROP SEQUENCE TWEET_IDSEQ'; END IF; END;;
create sequence tweet_idseq start with 1 increment by 1 nomaxvalue;

2. For the execute step, the benchmarks/twitter/dialect-oracle.xml file needed modification to use unquoted table names when executing the procedures. This change ensures that the execute step runs without errors.
   (benchmarks/twitter/dialect-oracle.xml)

<?xml version="1.0"?>
<dialects>
    <dialect type="ORACLE">
        <procedure name="GetTweet">
            <statement name="getTweet">
                SELECT * FROM tweets WHERE id = ?
            </statement>
        </procedure>
        <procedure name="GetTweetsFromFollowing">
            <statement name="getFollowing">
                SELECT f2 FROM follows WHERE f1 = ? AND ROWNUM &lt;= 20
            </statement>
            <statement name="getTweets">
                SELECT * FROM tweets WHERE uuid IN (??)
            </statement>
        </procedure>
        <procedure name="GetFollowers">
            <statement name="getFollowers">
                SELECT f2 FROM followers WHERE f1 = ? AND ROWNUM &lt;= 20
            </statement>
            <statement name="getFollowerNames">
                SELECT uuid, name FROM user_profiles WHERE uuid IN (??)
            </statement>
        </procedure>
        <procedure name="GetUserTweets">
            <statement name="getTweets">
                SELECT * FROM tweets WHERE uuid = ? AND ROWNUM &lt;= 10
            </statement>
        </procedure>
        <procedure name="InsertTweet">
            <statement name="insertTweet">
                INSERT INTO added_tweets VALUES (tweet_idseq.nextval, ?, ?, ?)
            </statement>
        </procedure>
    </dialect>
</dialects>

With these modifications, the Twitter benchmark can be successfully created and executed on Oracle Database 19c/23c without encountering the mentioned errors.

[apavlo]

@AshishVirdi Please send a PR.

[bpkroth]

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