jooqduckdb

jOOQ dynamic aggregated types

I consider jOOQ over DuckDB over Parquet files. The type of parquet columns are not know before hand. Let's consider some column may be an integer or a double.

I want to SUM over given column, receiving an int/long/BigInteger if the column has int-like type, or a float/double/BigDecimal if the column has float-like type.

The following code always returns BigDecimal, which is fine on kD which holds doubles, but not fine with kI which holds int.

I would like to output type to be based on DuckDB output (which is HUGEINT), while it seems to rely on the type provided in the query (which is defaulted to BigDecimal).

My situation is synthetized by:

@Test
public void testAggregate() throws SQLException {
    DuckDBConnection c = (DuckDBConnection) DriverManager.getConnection("jdbc:duckdb:");
    DSLContext dslContext = DSL.using(c, SQLDialect.DUCKDB);

    String tableName = "someTable";

    dslContext.createTable(tableName).column("kI", SQLDataType.INTEGER).column("kD", SQLDataType.DOUBLE).execute();
    dslContext.insertInto(DSL.table(tableName), DSL.field("kI"), DSL.field("kD")).values(123, 12.34).execute();
    dslContext.insertInto(DSL.table(tableName), DSL.field("kI"), DSL.field("kD")).values(234, 23.45).execute();

    try (Statement statement = c.createStatement()) {
        statement.execute("SELECT SUM(kI), SUM(kD) FROM someTable");
        ResultSet resultSet = statement.getResultSet();
        if (resultSet.next()) {
            // Prints `class java.math.BigInteger`
            System.out.println(resultSet.getObject(1).getClass());
            // Prints `class Double`
            System.out.println(resultSet.getObject(2).getClass());
        }
    }

    // We need to proceed over `kI` and `kD` without knowing their type before hand
    onFieldName(dslContext, tableName, "kI");
    onFieldName(dslContext, tableName, "kD");

    onFieldNameCoerceBigInteger(dslContext, tableName, "kI");
    onFieldNameCoerceBigInteger(dslContext, tableName, "kD");

    // Fails with `Not supported by dialect : Type class java.lang.Number is not supported in dialect null`
    onFieldNameCoerceNumber(dslContext, tableName, "kI");
    onFieldNameCoerceNumber(dslContext, tableName, "kD");
}

private void onFieldName(DSLContext dslContext, String tableName, String fieldName) {
    Field<Number> field = (Field) DSL.field(fieldName);
    SelectJoinStep<Record1<BigDecimal>> queryInteger = dslContext.select(DSL.sum(field)).from(DSL.table(tableName));

    queryInteger.stream().findAny().ifPresent(row -> {
        // prints `class java.math.BigDecimal`
        System.out.println(row.get(0).getClass());
    });
}

private void onFieldNameCoerceBigInteger(DSLContext dslContext, String tableName, String fieldName) {
    Field<Number> field = (Field) DSL.field(fieldName);
    SelectJoinStep<Record1<BigInteger>> queryInteger =
            dslContext.select(DSL.sum(field).coerce(BigInteger.class)).from(DSL.table(tableName));

    queryInteger.stream().findAny().ifPresent(row -> {
        // prints `class java.math.BigDecimal`
        System.out.println(row.get(0).getClass());
    });
}

private void onFieldNameCoerceNumber(DSLContext dslContext, String tableName, String fieldName) {
    Field<Number> field = (Field) DSL.field(fieldName);
    SelectJoinStep<Record1<Number>> queryInteger =
            dslContext.select(DSL.sum(field).coerce(Number.class)).from(DSL.table(tableName));

    queryInteger.stream().findAny().ifPresent(row -> {
        // prints `class java.math.BigDecimal`
        System.out.println(row.get(0).getClass());
    });
}

Relates with:

Solution

  • You could just use DSL.aggregate() and inherit the argument data type like this:

    aggregate(systemName("sum"), kI.getDataType(), kI);

    That would delegate data type resolution again to the JDBC driver / database, rather than jOOQ hard-wiring it to BigDecimal