Postgres, DSL Approach: primary key with custom names beside id breaks (reproducible test included)

[amirulzin]

Dependencies:

implementation 'org.jetbrains.exposed:exposed:0.10.2'
testImplementation group: 'com.opentable.components', name: 'otj-pg-embedded', version: '0.11.4'

Reproducible test case:

import com.opentable.db.postgres.junit.EmbeddedPostgresRules
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.SchemaUtils.create
import org.jetbrains.exposed.sql.StdOutSqlLogger
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.transactions.transaction
import org.junit.Rule
import org.junit.Test


class SQLGeneratorTest {

  @get:Rule
  val pg = EmbeddedPostgresRules.singleInstance();

  @Test
  fun validateDatabaseCreated() {
    val database = Database.connect(pg.embeddedPostgres.postgresDatabase)
    transaction(database) {
      logger.addLogger(StdOutSqlLogger)
      create(Pets, BrokenPets)
    }
    transaction {
      logger.addLogger(StdOutSqlLogger)
      Pets.insert {
        it[name] = "Cathy"
      }
    }
    /* Broken */
    transaction {
      logger.addLogger(StdOutSqlLogger)
      BrokenPets.insert {
        it[name] = "Wolfy"
      }
    }
  }
}

object Pets : Table("ValidPets") {
  val id = long("id").autoIncrement().primaryKey()
  val name = varchar("name", 100)
}

object BrokenPets : Table("BrokenPets") {
  val id = long("petsId").autoIncrement().primaryKey() 
  val name = varchar("name", 100)
}

Logs:

SQL: CREATE TABLE IF NOT EXISTS validpets (id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL)
SQL: CREATE TABLE IF NOT EXISTS brokenpets ("petsId" BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL)
SQL: INSERT INTO validpets (name) VALUES ('Cathy')

org.postgresql.util.PSQLException: ERROR: column ""petsId"" does not exist
  Hint: Perhaps you meant to reference the column "brokenpets.petsId".
  Position: 53

	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
	at org.jetbrains.exposed.sql.statements.InsertStatement.execInsertFunction(InsertStatement.kt:85)
	at org.jetbrains.exposed.sql.statements.InsertStatement.executeInternal(InsertStatement.kt:94)
	at org.jetbrains.exposed.sql.statements.InsertStatement.executeInternal(InsertStatement.kt:11)
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:55)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:111)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:105)
	at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:27)
	at org.jetbrains.exposed.sql.QueriesKt.insert(Queries.kt:44)
	at com.sample.SQLGeneratorTest$validateDatabaseCreated$3.invoke(SQLGeneratorTest.kt:34)
	at com.sample.SQLGeneratorTest$validateDatabaseCreated$3.invoke(SQLGeneratorTest.kt:14)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:101)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:72)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:55)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:55)
	at com.sample.SQLGeneratorTest.validateDatabaseCreated(SQLGeneratorTest.kt:32)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at org.junit.rules.RunRules.evaluate(RunRules.java:20)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

Also there's an inconsistent behavior where some columns are created with "columnName" while another column in the same table is simply created as columnName.

[amirulzin]

Apparently using column name in the underscored form pets_id instead of petsId passes and does not fail the insertion:

object BrokenPets : Table("BrokenPets") {
  val id = long("pets_id").autoIncrement().primaryKey()
  val name = varchar("name", 100)
}

Relevant logs of the test passing:

SQL: CREATE TABLE IF NOT EXISTS validpets (id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL)
SQL: CREATE TABLE IF NOT EXISTS brokenpets (pets_id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL)
SQL: INSERT INTO validpets (name) VALUES ('Cathy')
SQL: INSERT INTO brokenpets (name) VALUES ('Wolfy')

However, the inconsistent behavior where columns created with "columnName" should not fail and should not originally cause the error : column ""petsId"" does not exist.

Following Postgres syntax lexical:

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named “select”, whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.

Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands.

And some certain Postgres only behavior:

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

I guess Postgres users will not get this error if they do follow a_column_name format for primary keys.

[amirulzin]

For other columns, the below passes though:

object BrokenPets : Table("BrokenPets") {
  val id = long("pets_id").autoIncrement().primaryKey()
  val name = varchar("validName", 100)
}

Logs: 
SQL: CREATE TABLE IF NOT EXISTS brokenpets (pets_id BIGSERIAL PRIMARY KEY, "validName" VARCHAR(100) NOT NULL)
SQL: INSERT INTO brokenpets ("validName") VALUES ('Wolfy')

It seems only primary keys denoted in camelCase produced the original bug.

[Tapac]

The problem is that Postgres jdbc-driver additionally quotes "quoted" column name provided in RETURNING part of insert query.

Exposed quotes validName and petsId because we want to save the original case of column names and as you mentioned above:

Quoting an identifier also makes it case-sensitive

I will investigate the possibility to remove quotation from RETURNING.

[Tapac]

Fixed in master, while waiting for a fix in a driver.