sqldelight / sqldelight/sql-psi

Complex insert statement does not compile

Open
#262 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

sqlite
Dominant language
Kotlin
Stars
104
Forks
35
Avg merge
1h 11m
Merged PRs (30d)
14

Description

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

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the reported SQLite statement with the supplied customer schema, adapters, and bind parameters. Trace where the SQL compiler reports “Cannot bind unknown types or null”; done means this statement compiles without that error while retaining correct parameter typing.

Written by the indexing model from the issue text.

Assessment

Tech stack
kotlin, sqlite
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.