Grouping Statement: Cannot bind argument at index N+1 because the index is out of range. The statement has N parameters.

[littledot]

Runtime Environment
SQLDelight version: 1.4.0
Application OS: Android

Describe the bug
I'm trying to convert a separate update + insert queries into a single upsert query using the Grouping Statement feature.

Consider the following schema:

CREATE TABLE Bug (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    col0 TEXT NOT NULL,
    col1 TEXT NOT NULL,
    col2 INTEGER AS Boolean NOT NULL DEFAULT 0,
    col3 INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

CREATE UNIQUE INDEX Bug__col0_col1 ON Bug(col0, col1);

upsert {
  UPDATE Bug
  SET col2 = :col2,
      col3 = :col3
  WHERE col0 = :col0
    AND col1 = :col1
  ;

  INSERT OR IGNORE INTO Bug(col0, col1, col2)
  VALUES (:col0, :col1, :col2)
  ;
}

When running the following query like so

        val driver = AndroidSqliteDriver(MyCoolStore.Schema, this, null)
        val db = MyCoolStore(driver)
        db.bugQueries.upsert(
            col0 = "a",
            col1 = "b",
            col2 = true,
            col3 = 123
        )

SqlDelight throws up:

     Caused by: java.lang.IllegalArgumentException: Cannot bind argument at index 5 because the index is out of range.  The statement has 4 parameters.
        at android.database.sqlite.SQLiteProgram.bind(SQLiteProgram.java:212)
        at android.database.sqlite.SQLiteProgram.bindString(SQLiteProgram.java:166)
        at androidx.sqlite.db.framework.FrameworkSQLiteProgram.bindString(FrameworkSQLiteProgram.java:50)
        at com.squareup.sqldelight.android.AndroidPreparedStatement.bindString(AndroidSqliteDriver.kt:189)
        at com.littledot.androidbugreport.app.BugQueriesImpl$upsert$1.invoke(MyCoolStoreImpl.kt:118)
        at com.littledot.androidbugreport.app.BugQueriesImpl$upsert$1.invoke(MyCoolStoreImpl.kt:92)
        at com.squareup.sqldelight.android.AndroidSqliteDriver.execute(AndroidSqliteDriver.kt:120)
        at com.squareup.sqldelight.android.AndroidSqliteDriver.execute(AndroidSqliteDriver.kt:136)
        at com.littledot.androidbugreport.app.BugQueriesImpl.upsert(MyCoolStoreImpl.kt:102)
        at com.littledot.androidbugreport.MainActivity.onCreate(MainActivity.kt:17)

BugQueriesImpl.upsert:

private class BugQueriesImpl(
  private val database: MyCoolStoreImpl,
  private val driver: SqlDriver
) : TransacterImpl(driver), BugQueries {
  override fun upsert(
    col2: Boolean,
    col3: Long,
    col0: String,
    col1: String
  ) {
    driver.execute(852179207, """
    |UPDATE Bug
    |  SET col2 = ?,
    |      col3 = ?
    |  WHERE col0 = ?
    |    AND col1 = ?
    |  ;
    |
    |  INSERT OR IGNORE INTO Bug(col0, col1, col2)
    |  VALUES (?, ?, ?)
    |  ;
    """.trimMargin(), 7) {
      bindLong(1, if (col2) 1L else 0L)
      bindLong(2, col3)
      bindString(3, col0)
      bindString(4, col1)
      bindString(5, col0)
      bindString(6, col1)
      bindLong(7, if (col2) 1L else 0L)
    }
  }
}

Repro: Run project https://gitlab.com/littledot/android-bug-report

[AlecKazakova]

is this using the android driver or jdbc sqlite driver?

[littledot]

It's using the Android driver.

        at com.squareup.sqldelight.android.AndroidSqliteDriver.execute(AndroidSqliteDriver.kt:120)
        at com.squareup.sqldelight.android.AndroidSqliteDriver.execute(AndroidSqliteDriver.kt:136)
        at com.littledot.androidbugreport.app.BugQueriesImpl.upsert(MyCoolStoreImpl.kt:102)
        at com.littledot.androidbugreport.MainActivity.onCreate(MainActivity.kt:17)

[R4md4c]

Facing the same problem with the JVM driver though (haven't tried with the Android one).

CREATE TABLE Bug (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    `serverId` TEXT NOT NULL
);

selectAll:
SELECT * FROM Bug;

upsert {
   UPDATE Bug
   SET serverId = :serverId
   WHERE id = :id;

   INSERT OR IGNORE INTO Bug(
     id, serverId
   )
   VALUES
     (
        :id, :serverId
     );
}

Test case

internal class BugQueriesTest : Spek({

    lateinit var bugQueries: BugQueries
    describe("BugQueries") {
        beforeEachTest {
            bugQueries = createBugDatabase().bugQueries
            bugQueries.upsert(id = 1, serverId = "")
        }

        it("bug should exist") {
            bugQueries.selectAll().executeAsOneOrNull() shouldBeEqualTo  Bug(id = 1, serverId = "")
        }
    }
})

Getting exception:

2
java.lang.ArrayIndexOutOfBoundsException: 2
	at org.sqlite.core.CorePreparedStatement.batch(CorePreparedStatement.java:121)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.setLong(JDBC3PreparedStatement.java:331)
	at com.squareup.sqldelight.sqlite.driver.SqliteJdbcPreparedStatement.bindLong(JdbcDriver.kt:123)
        .....
        at com.example.BugQueriesImpl.upsert(BugDatabaseImpl.kt:65)
        at com.example.BugQueriesTest$1$1$1.invoke(BugQueriesTest.kt:18)

Generated upsert method:

override fun upsert(serverId: String, id: Long) {
    driver.execute(15499350, """
    |UPDATE Bug
    |   SET serverId = ?
    |   WHERE id = ?;
    |
    |   INSERT OR IGNORE INTO Bug(
    |     id, serverId
    |   )
    |   VALUES
    |     (
    |        ?, ?
    |     );
    """.trimMargin(), 4) {
      bindString(1, serverId)
      bindLong(2, id)
      bindLong(3, id)
      bindString(4, serverId)
    }
    notifyQueries(15499350, {database.bugQueries.selectAll})
  }