Complex insert statement does not compile

[melbic]

Dialect: SQLite

failing sql:

WITH new(number, modificationDate, firstName, lastName, languageId, telephoneNumber1, email1, street, city, zipCode, countryId) AS ( VALUES (?,?,?,?,?,?,?,?,?,?,?) )
 INSERT OR REPLACE INTO customer (localId, number, modificationDate, firstName, lastName, languageId, telephoneNumber1, email1, street, city, zipCode, countryId)
 SELECT old.localId, new.number, new.modificationDate, new.firstName, new.lastName, new.languageId, new.telephoneNumber1, new.email1, new.street, new.city, new.zipCode, new.countryId
 FROM new LEFT JOIN customer AS old ON new.number = old.number;

The above statements doe not compile in sqldelight with the error: Cannot bind unknown types or null. Running it as a SQL statement works fine.

Scheme:

import java.time.LocalDateTime;
import ch.dreipol.app.database.extensions.CustomerID;


CREATE TABLE customer (
localId INTEGER AS CustomerID NOT NULL PRIMARY KEY AUTOINCREMENT,
number TEXT,
modificationDate TEXT AS LocalDateTime,
firstName TEXT,
lastName TEXT,
languageId TEXT,
telephoneNumber1 TEXT,
email1 TEXT,

street TEXT,
city TEXT,
zipCode TEXT,
countryId TEXT
);

CREATE INDEX customer_number ON customer(number);
CREATE INDEX customer_lastName ON customer(lastName);

Helper classes:

import com.squareup.sqldelight.ColumnAdapter
import java.time.LocalDateTime

class LocalDateTimeAdapter : ColumnAdapter<LocalDateTime, String> {
    override fun decode(databaseValue: String): LocalDateTime {
        return LocalDateTime.parse(databaseValue)
    }

    override fun encode(value: LocalDateTime): String {
        return value.toString()
    }
}

typealias CustomerID = Long

[AlecKazakova]

Can you also include the customer schema, then I can drop this in as a unit test

…

On Mon, May 10, 2021 at 4:58 AM Samuel Bichsel ***@***.***> wrote: Dialect: SQLite failing sql: WITH new(number, modificationDate, firstName, lastName, languageId, telephoneNumber1, email1, street, city, zipCode, countryId) AS ( VALUES (?,?,?,?,?,?,?,?,?,?,?) ) INSERT OR REPLACE INTO customer (localId, number, modificationDate, firstName, lastName, languageId, telephoneNumber1, email1, street, city, zipCode, countryId) SELECT old.localId, new.number, new.modificationDate, new.firstName, new.lastName, new.languageId, new.telephoneNumber1, new.email1, new.street, new.city, new.zipCode, new.countryId FROM new LEFT JOIN customer AS old ON new.number = old.number; The above statements doe not compile in sqldelight with the error: Cannot bind unknown types or null. Running it as a SQL statement works fine. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#262>, or unsubscribe <https:/notifications/unsubscribe-auth/AAMZBQA2RHWI5AVIRT47AYDTM6NZXANCNFSM44QUYGPA> .

[melbic]

Sure. I added the scheme and the used helper classes.