DuckDBAppender.append does not transcode from String automatically (1.3.1.0 -> 1.3.2.0)

[blacelle]

I consider a table with an INTEGER column, in which the appender write as String.

With DuckDB Java connector 1.3.1.0:

	@Test
	public void testInsertStringIntoInt_1310() throws SQLException {
		DuckDBConnection duckDbC = (DuckDBConnection) DriverManager.getConnection("jdbc:duckdb:");

		try (Statement s = duckDbC.createStatement()) {
			s.execute("CREATE TABLE t (c INTEGER)");
		}

		DuckDBAppender appender = duckDbC.createAppender("main", "t");
		appender.beginRow();
		appender.append("123");
		appender.endRow();
	}

It works correctly. Writing as String relates to #163 as it appeared append(String) was resilient to handle various types.

---

With 1.3.2.0, the code fails with:

java.sql.SQLException: Appender error, catalog: 'null', schema: 'main', table: 't', message: invalid column type, expected one of: '[DUCKDB_TYPE_VARCHAR]', actual: 'DUCKDB_TYPE_INTEGER'
	at org.duckdb.DuckDBAppender.checkColumnType(DuckDBAppender.java:1227)
	at org.duckdb.DuckDBAppender.checkCurrentColumnType(DuckDBAppender.java:1214)
	at org.duckdb.DuckDBAppender.checkCurrentColumnType(DuckDBAppender.java:1209)
	at org.duckdb.DuckDBAppender.append(DuckDBAppender.java:916)
	at eu.solven.adhoc.pivotable.app.TestAppenderStringToIntColumn.testInsertStringIntoInt_1310(TestAppenderStringToIntColumn.java:22)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)

While the new behavior seems legitimate, it is unexpected. Is it a bug or a feature ? I would expected parsing from String to many types to still be a thing for edge-cases (e.g. double[][][]).

Here an example :

	@Test
	public void testInsertStringIntoInt_1310() throws SQLException {
		DuckDBConnection conn = (DuckDBConnection) DriverManager.getConnection("jdbc:duckdb:");

		try (Statement s = conn.createStatement()) {
			s.execute("CREATE TABLE t (c INTEGER, arr INTEGER[2][2][2])");
		}

		try (DuckDBAppender appender2 = conn.createAppender("main", "t")) {
			appender2.beginRow();
			appender2.append("123");
			appender2.append("[[[1, 2], [3, 4]],[[5, 6], [7, 8]]]");
			appender2.endRow();
		}

		try (Statement s = conn.createStatement()) {
			try (ResultSet rs = s.executeQuery("SELECT * FROM t")) {
				while (rs.next()) {
					System.out.println(rs.getString(1));
					System.out.println(rs.getString(2));
				}
			}
		}
	}

In 1.3.2.0, appender2.append("123") can be turned into appender2.append(123) but I see no option for appender2.append("[[[1, 2], [3, 4]],[[5, 6], [7, 8]]]").

---

https://duckdb.org/docs/stable/clients/java.html#appender may be updated to

try (var appender = conn.createAppender("tbl")) {
    appender.beginRow().append(10).append(3.2).append("hello").endRow();
    appender.beginRow().append(20).append(-8.1).append("world").endRow();
}

given the new signatures.

[staticlibs]

@blacelle

Hi, thanks for the report! This behaviour is intentional, append() methods use strict checks that the input type matches the column type. For example append(int) will NOT work for the BIGINT column.

These checks can be relaxed for some type, but in general it is expected that values are prepared with correct types in client code.

If auto type casting is required for existing client code - the old Appender is still available through the connection.createSingleValueAppender() call.

[staticlibs]

#399 adds support for nested LISTs in Appender. While 3-dimentional ARRAYs are not supported, similar logic can be done using a LIST of 2-dimentional arrays.

Closing the issue, please re-open if needed.