Inconsistent result type inference of built-in functions

[victor-denisenko]

SQLDelight Version

2.0.2

SQLDelight Dialect

SQLite (Android target Sdk 34, Kotlin 1.9.23)

Describe the Bug

Some of built-in SQL functions and CASE statement ignore type of origin column and use type of second value in generated class. In the code below - value '0' with type INTEGER (retrieved as Long).

CREATE TABLE example (
    intValue1 INTEGER AS kotlin.Int,
    intValue2 INTEGER AS kotlin.Int NOT NULL
);

1. ifnull() or coalesce():

getAll_ifnull:
SELECT 
    ifnull(intValue1, 0) AS intValue1, 
    ifnull(0, intValue1) AS intValue1_1, 
    ifnull(intValue2, 0) AS intValue2,
    ifnull(0, intValue2) AS intValue2_1
FROM example;

// Generated class:
public data class GetAll_ifnull(
    public val intValue1: Long,
    public val intValue1_1: Long,
    public val intValue2: Long,
    public val intValue2_1: Long,
)

// Expected:
public data class GetAll_ifnull(
    public val intValue1: Long,   // (Int or Long): Result type upcasting to Long
    public val intValue1_1: Long, // Long, '0' is Long and first arg
    public val intValue2: Int,    // Int, intValue2 is Int and NOT NULL
    public val intValue2_1: Long, // Long, '0' is Long and first arg
)

2. CASE NOT NULL:

getAll_CASE_NOTNULL:
SELECT
    CASE WHEN intValue1 IS NOT NULL THEN intValue1 ELSE 0 END AS intValue1,
    CASE WHEN intValue1 IS NOT NULL THEN 0 ELSE intValue1 END AS intValue1_1,
    CASE WHEN intValue2 IS NOT NULL THEN intValue2 ELSE 0 END AS intValue2,
    CASE WHEN intValue2 IS NOT NULL THEN 0 ELSE intValue2 END AS intValue2_1
FROM example;

// Generated class:
public data class GetAll_CASE_NOTNULL(
    public val intValue1: Int?,
    public val intValue1_1: Long,
    public val intValue2: Int,
    public val intValue2_1: Long,
)

// Expected:
public data class GetAll_CASE_NOTNULL(
    public val intValue1: Long,    // (Int or Long): Result type upcasting to Long
    public val intValue1_1: Long?, // (Long or Int?): Result type upcasting to Long?
    public val intValue2: Int,     // Int, intValue2 is Int and NOT NULL
    public val intValue2_1: Long,  // Long, '0' is Long and intValue2 is NOT NULL
)

3. CASE NULL:

getAll_CASE_NULL:
SELECT
    CASE WHEN intValue1 IS NULL THEN 0 ELSE intValue1 END AS intValue1,
    CASE WHEN intValue1 IS NULL THEN intValue1 ELSE 0 END AS intValue1_1,
    CASE WHEN intValue2 IS NULL THEN 0 ELSE intValue2 END AS intValue2,
    CASE WHEN intValue2 IS NULL THEN intValue2 ELSE 0 END AS intValue2_1
FROM example;

// Generated class:
public data class GetAll_CASE_NULL(
    public val intValue1: Long,
    public val intValue1_1: Int?,
    public val intValue2: Long,
    public val intValue2_1: Int,
)

// Expected:
public data class GetAll_CASE_NULL(
    public val intValue1: Long,    // (Long or Int): Result type upcasting to Long
    public val intValue1_1: Long?, // (Int? or Long): Result type upcasting to Long?
    public val intValue2: Int,     // Int, intValue2 is Int and NOT NULL
    public val intValue2_1: Long,  // Long, '0' is Long and intValue2 is NOT NULL
)

4. Some attempt to cast type (CAST AS doesn't work with Kotlin types), as example:

/**
* Fix returned type for 'ifnull', 'coalesce' and CASE
*/
CREATE TABLE typeFix (
    intValue_defaut INTEGER AS kotlin.Int DEFAULT 0 NOT NULL
);

INSERT INTO typeFix DEFAULT VALUES;

getAll_IntFix:
SELECT
    ifnull(intValue1, typeFix.intValue_defaut) AS intValue1,
    ifnull(typeFix.intValue_defaut, intValue1) AS intValue1_1,
    ifnull(intValue2, typeFix.intValue_defaut) AS intValue2,
    ifnull(typeFix.intValue_defaut, intValue2) AS intValue2_1
FROM example, typeFix;

// Generated class:
public data class GetAll_IntFix(
    public val intValue1: Int,
    public val intValue1_1: Int,
    public val intValue2: Int,
    public val intValue2_1: Int,
)

Stacktrace

No response