Exposed does not work with just-released H2Database (1.4.199)

[bberliner]

The latest version of Exposed (0.13.5) seems to work fine with the previous version of H2Database (1.4.198), but if I upgrade to the recently-released version of H2Database (1.4.199) the same exact Exposed code fails to work. I cannot determine if this is a bug in H2Database or Exposed. Here's the test I run (based on Exposed example code) that previously worked fine:

    @Test
    fun `can do simple database ops outside of module`() {
        Database.connect("jdbc:h2:mem:test;MODE=MySQL;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")

        transaction {
            SchemaUtils.create(
                Cities,
                Users
            )

            val saintPetersburgId = Cities.insert {
                it[name] = "St. Petersburg"
            } get Cities.id

            val munichId = Cities.insert {
                it[name] = "Munich"
            } get Cities.id

            Cities.insert {
                it[name] = "Prague"
            }

            Users.insert {
                it[id] = "andrey"
                it[name] = "Andrey"
                it[cityId] = saintPetersburgId
            }

            Users.insert {
                it[id] = "sergey"
                it[name] = "Sergey"
                it[cityId] = munichId
            }

            Users.insert {
                it[id] = "eugene"
                it[name] = "Eugene"
                it[cityId] = munichId
            }

            Users.insert {
                it[id] = "alex"
                it[name] = "Alex"
                it[cityId] = null
            }

            Users.insert {
                it[id] = "smth"
                it[name] = "Something"
                it[cityId] = null
            }

            Users.update({ Users.id eq "alex" }) {
                it[name] = "Alexey"
            }

            Users.deleteWhere { Users.name like "%thing" }

            println("All cities:")

            for (city in Cities.selectAll()) {
                println("${city[Cities.id]}: ${city[Cities.name]}")
            }

            println("Manual join:")
            (Users innerJoin Cities).slice(
                Users.name,
                Cities.name
            ).select {
                (Users.id.eq("andrey") or Users.name.eq("Sergey")) and
                        Users.id.eq("sergey") and Users.cityId.eq(
                    Cities.id
                )
            }.forEach {
                println("${it[Users.name]} lives in ${it[Cities.name]}")
            }

            println("Join with foreign key:")


            (Users innerJoin Cities).slice(
                Users.name,
                Users.cityId,
                Cities.name
            )
                .select { Cities.name.eq("St. Petersburg") or Users.cityId.isNull() }.forEach {
                    if (it[Users.cityId] != null) {
                        println("${it[Users.name]} lives in ${it[Cities.name]}")
                    } else {
                        println("${it[Users.name]} lives nowhere")
                    }
                }

            println("Functions and group by:")

            ((Cities innerJoin Users).slice(
                Cities.name, Users.id.count()).selectAll().groupBy(
                Cities.name
            )).forEach {
                val cityName = it[Cities.name]
                val userCount = it[Users.id.count()]

                if (userCount > 0) {
                    println("$userCount user(s) live(s) in $cityName")
                } else {
                    println("Nobody lives in $cityName")
                }
            }

            SchemaUtils.drop(
                Users,
                Cities
            )

        }
    }

And this is the error that I now see:

java.lang.IllegalStateException: CITIES."id" is not in record set

    at org.jetbrains.exposed.sql.ResultRow.getRaw(Query.kt:54)
    at org.jetbrains.exposed.sql.ResultRow.get(Query.kt:25)
    at org.jetbrains.exposed.sql.statements.InsertStatement.get(InsertStatement.kt:23)
    at com.example.database.DatabaseTest$can do simple database ops outside of module$1.invoke(DatabaseTest.kt:239)
    at com.example.database.DatabaseTest$can do simple database ops outside of module$1.invoke(DatabaseTest.kt:23)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:104)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:75)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:58)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:58)
    at com.example.database.DatabaseTest.can do simple database ops outside of module(DatabaseTest.kt:231)
    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.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:628)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:117)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:184)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:180)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:127)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
    at java.util.ArrayList.forEach(ArrayList.java:1257)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
    at java.util.ArrayList.forEach(ArrayList.java:1257)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)
    at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)
    at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:74)
    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)

[Tapac]

Fast investigation (through h2 changelog) shows that their change default behavior of driver in place of case-sensitivity for identifiers), as a workaround, you could add IGNORECASE=true; parameter to your connection string, but I will look into that problem closer.

[NagyGa1]

Or add DATABASE_TO_UPPER=false;

[StefanHub]

I got a similar error when using _insertIgnore_:

java.lang.IllegalStateException: users.id is not in record set
    at org.jetbrains.exposed.sql.ResultRow.getRaw(Query.kt:54)
    at org.jetbrains.exposed.sql.ResultRow.get(Query.kt:25)
    at org.jetbrains.exposed.sql.statements.InsertStatement.get(InsertStatement.kt:23)
    at timebox.users.UsersTableKt.insertUserIgnore(UsersTable.kt:121)
    at timebox.app.ApplicationContextKt$applicationContext$user$1$1.invoke(ApplicationContext.kt:31)
    at timebox.app.ApplicationContextKt$applicationContext$user$1$1.invoke(ApplicationContext.kt)
    at timebox.db.DatabaseKt$transacted$2$1.invoke(Database.kt:39)
    at timebox.db.DatabaseKt$transacted$2$1.invoke(Database.kt)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:104)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:75)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:58)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:58)
    at timebox.db.DatabaseKt$transacted$2.invokeSuspend(Database.kt:38)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:32)
    at kotlinx.coroutines.DispatchedTask.run(Dispatched.kt:233)
    at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:594)
    at kotlinx.coroutines.scheduling.CoroutineScheduler.access$runSafely(CoroutineScheduler.kt:60)
    at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:742)

This appears when upgrading from exposed 0.13.3 to 0.13.4
I am using mysql 5.7.25 (no driver or db changed)

[Tapac]

@StefanHub , could you please check the latest (0.13.6) version?

[StefanHub]

@Tapac , sorry for being so late - unfortunatelly it still fails. Exception is exactly the same.
What information do you need?

[mhewedy]

Can anybody edit the gs wiki https://github.com/JetBrains/Exposed/wiki/Getting-Started with this replay in order not newbies got bad impressions (I know it is not H2 issue, but still we need to make ppl happy 😄)

Or add DATABASE_TO_UPPER=false;

[Tapac]

@mhewedy , thank you for your advice. Wiki page was updated.

[StefanHub]

Unfortunatelly 1.14.1 does not solve my issue. As said above the error happens when a insertIgnore is done on a table where the row already exists.
The failing code tries to retrieve the "inserted" row's id, but that column (the id's) is not in the result:

java.lang.IllegalStateException: users.id is not in record set
    at org.jetbrains.exposed.sql.ResultRow.getRaw(Query.kt:59)
    at org.jetbrains.exposed.sql.ResultRow.get(Query.kt:24)
    at org.jetbrains.exposed.sql.statements.InsertStatement.get(InsertStatement.kt:24)
    at timebox.users.UsersTableKt.insertUserIgnore(UsersTable.kt:149)
    at timebox.users.UsersTableTest$test insert ignore user from existing user$1.invoke(UsersTableTest.kt:74)
    at timebox.users.UsersTableTest$test insert ignore user from existing user$1.invoke(UsersTableTest.kt:9)
    at timebox.test.MysqlTestTrait$transaction$1.invoke(MysqlTestTrait.kt:74)
    at timebox.test.MysqlTestTrait$transaction$1.invoke(MysqlTestTrait.kt:28)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:104)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:75)
    at timebox.test.MysqlTestTrait$DefaultImpls.transaction(MysqlTestTrait.kt:73)
    at timebox.users.UsersTableTest.transaction(UsersTableTest.kt:9)
    at timebox.users.UsersTableTest.test insert ignore user from existing user(UsersTableTest.kt:73)

The fieldIndex in getRaw() is:

  fieldIndex = {LinkedHashMap@4598}  size = 6
  {Column@4479} timebox.users.Users.initial -> {Integer@4636} 0
  {Column@4474} timebox.users.Users.lastName -> {Integer@4477} 1
  {Column@4637} timebox.users.Users.archived -> {Integer@4638} 2
  {Column@4472} timebox.users.Users.firstName -> {Integer@4639} 3
  {Column@4485} timebox.users.Users.serviceProvider -> {Integer@4640} 4
  {Column@4481} timebox.users.Users.adRef -> {Integer@4641} 5

And it does not contain Users.ID, which causes the error in getRaw().

The insertIgnore() is:

    Users
            .insertIgnore {
                if (user.id != null)
                    it[id] = user.id
                it[initial] = user.initial
                it[firstName] = user.firstName
                it[lastName] = user.lastName
                it[serviceProvider] = user.serviceProvider
                it[adRef] = user.adRef
                it[archived] = false
            } get Users.id

Maybe I am using exposed wrong?!

[Tapac]

@StefanHub, thank you for a report, I will check a case with insertIgnore as there were changes in H2 related to it. Could you specify which mode do you use with H2 - default, MySQL, other?

[StefanHub]

@Tapac MySQL 5.7 with default mode (i.e. nothing specified)

[Tapac]

@StefanHub Oh, you have a problem with MySQL? I think (based on an issue title) that you use H2 in MySQL mode and have problem with it.

[StefanHub]

@Tapac Yes, that's true. Sorry for that. Close this one and create a new one?

[Tapac]

@StefanHub, yes, please add table mapping, insertIgnore code and Mysql jdbc driver version.

[nyenugula-zealsoft]

Hi All, new to Kotlin Exposed. Having a similar type of error is not in record set.
code being primary key for Table geolocation_pos

object GeoLocationModel : IdTable<Long>("geolocation_pos") {
    val code = long("code").primaryKey()
    val createdAt = datetime("created_at").clientDefault { currentUtc() }
    val updatedAt = datetime("updated_at").nullable()
    val createdBy = varchar("created_by", 255).clientDefault { authenticatedUser() }
    val updatedBy = varchar("updated_by", 255).nullable()
    val uuid = uuid("uuid").autoGenerate().uniqueIndex()
    val latitude: Column<Double> = double("latitude")
    val longitude: Column<Double> = double("longitude")
    val pincode: Column<Long> = long("pincode")
    override val id: Column<EntityID<Long>> =  code.entityId()
}

Insertion of data for **geolocation_pos** table
 val geolocationId = GeoLocationModel.insert {
                it[code] = nextSequenceVal()
                it[latitude] = 28.550667
                it[longitude] = 77.268952
                it[pincode] = xxxxxx
            } get GeoLocationModel.id 

MySQL database is not able to save throws an error stating

java.lang.IllegalStateException: com.xxxx.xxxx.xxxx.xxx.GeoLocationModel.code is not in record set
    at org.jetbrains.exposed.sql.ResultRow.getRaw(ResultRow.kt:53) ~[exposed-core-0.21.1.jar:na]
    at org.jetbrains.exposed.sql.ResultRow.get(ResultRow.kt:18) ~[exposed-core-0.21.1.jar:na]
    at org.jetbrains.exposed.sql.statements.InsertStatement.get(InsertStatement.kt:20) ~[exposed-core-0.21.1.jar:na]

Generated SQL

INSERT INTO geolocation_pos (code, created_at, created_by, latitude, longitude, pincode, updated_at, updated_by, uuid) VALUES (106, 'xxxxxxx', 'xxx', 28.550667, 77.268952, xxxxx, NULL, NULL, 'dd6aaf69-5324-4fac-9271-5c099957e06b')

Please can anyone suggest me about this issue or point to wiki or example.