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 double
s, 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:
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