I need to implement pagination and this issue has been bugging me for hours now.

you should probably use two separate queries anyway since with mysql 8 the

SQL_CALC_FOUND_ROWS

and

FOUND_ROWS()

are deprecated, see https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows i assume something like

transaction {
    val condition = with(SqlExpressionBuilder) {
        <some-condition>
    }
    val results = YourTable.select { condition and id > 100 }.limit(100)
    val totalCount = YourTable.select { condition }.count()
}

should do the trick