mysql INET_ATON/INET_NTOA support

[mcxinyu]

SQLDelight Version

2.0.1

SQLDelight Dialect

2.0.1

Describe the Bug

Execution failed for task ':service:service-open:generateMainDatabasesInterface'.
> A failure occurred while executing app.cash.sqldelight.gradle.SqlDelightTask$GenerateInterfaces
   > Failed to compile SqlDelightStmtClojureStmtListImpl(STMT_CLOJURE_STMT_LIST): [] :
     INSERT INTO open_whitelists (userId, ip)
         VALUES (?, INET_ATON(?));
         SELECT LAST_INSERT_ID();

Hello everyone, when I was writing a line of sql (mysql) statement, the above error warning appeared.
It seems that sqldelight does not support mysql's INET_ATON/INET_NTOA function.
What should I do to solve this problem?

Stacktrace

No response

[griffio]

Any functions that are not supported can be added here to the MySqlTypeResolver https://github.com/cashapp/sqldelight/blob/a38c8e72fea406989a9fa6b9f1dbd71384d6d5ec/dialects/mysql/src/main/kotlin/app/cash/sqldelight/dialects/mysql/MySqlTypeResolver.kt#L99

A work-around that may work for you, at this moment, could be to enclose the function in additional parentheses (). e.g

 INSERT INTO open_whitelists (userId, ip)
         VALUES (?, (INET_ATON(?))); 

This is valid in MySql and the SqlDelight compiler allows it as an expression without knowing the function.

[mcxinyu]

Hello @griffio. It's really frustrating. I even referred to your sqldelight-custom-dialect project, but I did get an error prompt,

Compiling with dialect com.njf2016.sqldelight.MySqlCustomDialect

.../OpenWhitelists.sq: (18, 11): Unknown function LAST_INSERT_ID
18    SELECT LAST_INSERT_ID()
                 ^^^^^^^^^^^^^^^^

Here is the implementation of this class. Do you have any suggestions?

class MySqlCustomDialect : SqlDelightDialect by MySqlDialect() {
    override fun typeResolver(parentResolver: TypeResolver): TypeResolver {
        return MySqlTypeCustomResolver(parentResolver)
    }

    class MySqlTypeCustomResolver(private val parentResolver: TypeResolver) :
        TypeResolver by MySqlTypeResolver(parentResolver) {

        override fun functionType(functionExpr: SqlFunctionExpr): IntermediateType? {
            return when (functionExpr.functionName.text.lowercase()) {
                "inet_aton" -> IntermediateType(PrimitiveType.TEXT)
                "inet_ntoa" -> IntermediateType(PrimitiveType.TEXT)
                else -> parentResolver.functionType(functionExpr)
            }
        }
    }
}

[griffio]

@mcxinyu, LAST_INSERT_ID already exists, I had a go with a MySql version - see branch https://github.com/griffio/sqldelight-custom-dialect/tree/mysql
Seems to work ok - have a look and see if anything is different

[mcxinyu]

@mcxinyu, LAST_INSERT_ID already exists, I had a go with a MySql version - see branch https://github.com/griffio/sqldelight-custom-dialect/tree/mysql Seems to work ok - have a look and see if anything is different

@griffio, Thank you so much, you gave me the correct answer.

[mcxinyu]

At this point, the issue has been resolved, but I found that the sqldelight idea plugin seems to be abnormal.