Impossible to provide nested List/Array type in `DbType`

[Jolanrensen]

Only today I found out DataFrame JDBC does some limited auto-conversion of java.sql.Array to kotlin.Array<T>, however, trying to leverage that for my own DbType proved to be impossible.

In convertSqlTypeToKType:

• If I give the type java.sql.Array, it gets read like java.sql.Array, as expected
• If I give it the type kotlin.Array<*>, it gets read like kotlin.Array<*>
• If I give it the type kotlin.Array<Any?>, it gets read like kotlin.Array<Any?>, auto-conversion, interesting
• If I give it the type kotlin.Array<String?> (or some other T), it gets read as java.sql.Array again?

This means I have no way to define the type of data in the array, even though my database provides this information.

The last case is caused by this check here. In Kotlin the classifier of an array is not equal to itself. It can be solved by writing it like if ((kotlinTypesForSqlColumns[index]!!.classifier as KClass<*>).qualifiedName == Array::class.qualifiedName).

Still, doing that fix does not solve the problem, because handleArrayValues() tries to infer the nested type using reflection, by getting the componentType of the Java class. This only works for primitive arrays, not when the array is an Object[], which is often the case.

Probably solved best in combination with #1266, because all conversions could better be done in a single place.

[Jolanrensen]

Additionally, in notebooks, creating table in DuckDB without type-map like this:

CREATE TABLE IF NOT EXISTS test_table (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    age INTEGER,
    salary DOUBLE,
    hire_date DATE,
    pets VARCHAR[],
    map MAP(INT, VARCHAR),
)

causes:

Error compiling code: Unresolved reference: T
@DataSchema
interface _DataFrameType {
    val age: Int
    val hire_date: java.util.Date
    val id: Int
    val map: Any
    val name: String
    val pets: Array<T>
    val salary: Double
}