Database verdictdbtemp already exists Exception in SelectQueryCoordinator

[sofiazxy]

When attempting to execute the following 2 VerdictDB queries back to back: VerdictSingleResult result = verdict.sql("select count() from mydefault.lineitem_scramble"); VerdictSingleResult result1 = verdict.sql("select count() from mydefault.lineitem_scramble where l_linenumber>2");

Encountered the following error:

rg.verdictdb.exception.VerdictDBDbmsException: Issued the following query: create schema verdictdbtemp org.apache.hadoop.hive.metastore.api.AlreadyExistsException: Database verdictdbtemp already exists;

at org.verdictdb.connection.SparkConnection.executeSingle(SparkConnection.java:166)
at org.verdictdb.connection.SparkConnection.execute(SparkConnection.java:148)
at org.verdictdb.connection.CachedDbmsConnection.execute(CachedDbmsConnection.java:49)
at org.verdictdb.connection.DbmsConnection.execute(DbmsConnection.java:41)
at org.verdictdb.coordinator.SelectQueryCoordinator.process(SelectQueryCoordinator.java:128)
at org.verdictdb.coordinator.ExecutionContext.streamSelectQuery(ExecutionContext.java:400)
at org.verdictdb.coordinator.ExecutionContext.sqlSelectQuery(ExecutionContext.java:323)
at org.verdictdb.coordinator.ExecutionContext.sql(ExecutionContext.java:166)
at org.verdictdb.VerdictContext.sql(VerdictContext.java:388)
at org.verdictdb.connection.SparkQueryResultTest.testSparkQueryResultValues(SparkQueryResultTest.java:139)

[sofiazxy]

Here's the problem diagnosis: The Temporary schema gets created in SelectQueryCoordinator and problem is here:

    // create scratchpad schema if not exists
    if (!conn.getSchemas().contains(scratchpadSchema)) {
      log.info(
          String.format(
              "The schema for temporary tables (%s) does not exist; so we create it.",
              scratchpadSchema));
      CreateSchemaQuery createSchema = new CreateSchemaQuery(scratchpadSchema);
      conn.execute(createSchema);
    }```
so for Spark, 1) the check in the if statement is not working correctly; and 2) createSchema does not use `IF NOT EXISTS` hence causing the error