sqldelight
sqldelight copied to clipboard
Inconsistent result type inference of built-in functions
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
);
- 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
)
- 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
)
- 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
)
- 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