Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Custom return types in SELECT and VIEWs with COALESCE #5738

Unanswered
kihaki asked this question in Q&A
Custom return types in SELECT and VIEWs with COALESCE #5738
@kihaki kihaki
May 14, 2025 · 1 comment
Return to top
Discussion options

kihaki
May 14, 2025

Suppose I have the following query

nextSortIdx:
SELECT COALESCE(MAX(sort_idx), -1) + 1
FROM   dummy_instances
WHERE  date = :date;

where sort_idx is of type Int via the primitive adapters and works fine as type Int in all generated code UNLESS I use COALESCE in a query, where it defaults back to the default type (Long on the kotlin side).

ChatGPT and some internet search suggested to use this syntax to return Int from COALESCE queries:

nextSortIdx:
SELECT COALESCE(MAX(sort_idx), -1) + 1 AS next : Int
FROM   dummy_instances
WHERE  date = :date;

but this does not compile.

Is there a way to make the SELECT return the correct type even with COALESCE?

For completeness, here is the table definition:

CREATE TABLE dummy_instances (
  id       TEXT PRIMARY KEY, 
  date     TEXT AS LocalDate NOT NULL
           REFERENCES days(date) ON DELETE CASCADE
           CHECK(date GLOB '????-??-??'),
  type_id  TEXT NOT NULL
           REFERENCES dummy_types(id) ON DELETE CASCADE,
  sort_idx INTEGER AS Int NOT NULL CHECK(sort_idx >= 0),
  UNIQUE(date, type_id)
);
You must be logged in to vote

Replies: 1 comment

Comment options

griffio
May 14, 2025
Collaborator

Sadly, I don't think there is currently an answer to coerce the compiled type to a Kotlin Int when you have an INTEGER expression

SqlDelight type adapters sort_idx INTEGER AS Int can only be defined in CREATE TABLE and not in queries.

In SqlDelight, the INTEGER data type for Sqlite is always mapped to a Kotlin LONG - see

sqldelight/sqldelight-compiler/dialect/src/main/kotlin/app/cash/sqldelight/dialect/api/PrimitiveType.kt

Line 17 in 8816a3b

INTEGER(LONG),

The problem is that as soon as the expression COALESCE(MAX(sort_idx), -1) has a INTEGER literal the expression becomes Kotlin LONG type.

There was an issue #3572 that was supposed to use the Kotlin type as the preferred type - however this doesn't appear to work when there is an expression that becomes an INTEGER

The fix #4517 doesn't appear to take account of expressions and obeys the following rules

The rules used to determine if the Kotlin type should be used:

The argument types are homogeneous
The function is defined as returning the same type as the arguments passed in
The values of the arguments aren't mutated
You must be logged in to vote
0 replies
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Category
🙏
Q&A
Labels
None yet
2 participants
@kihaki @griffio
Morty Proxy This is a proxified and sanitized view of the page, visit original site.