Closed
Description
I am experiencing a strange effect with DuckDB and Java when passing in OFFSET and LIMIT as parameters in a prepared statement.
To make it work I have to swap the assignment of the parameters compared to the expected i.e. set the desired limit as first parameter and the desired offset as second even though the order is the oposite in the prepared statement....
If I do not switch the order I get zero records in the result (because the limit seems to become zero).
What am I doing wrong here or are there some known quirks/problems with DuckDB (1.1.3) parameter mapping?
My program (somewhat simplified) looks like this:
import java.sql.*;
public class DuckDBParameterBindingTest {
private static final String JDBC_URL = "jdbc:duckdb:demo.db";
private static final String QUERY = "SELECT a, b, c, d, e FROM table ORDER BY a, b, c, d, e OFFSET ? LIMIT ?";
public static void main(String[] args) {
try (Connection connection = DriverManager.getConnection(JDBC_URL);
PreparedStatement preparedStatement = connection.prepareStatement(QUERY)) {
System.out.println("Test 1: Standard parameter binding (OFFSET=0, LIMIT=5)");
preparedStatement.setInt(1, 0); // OFFSET
preparedStatement.setInt(2, 5); // LIMIT
runQuery(preparedStatement); // Incorrect result: No rows printed
System.out.println("Test 2: Swapped parameter binding (LIMIT=5, OFFSET=0)");
preparedStatement.setInt(1, 5); // LIMIT
preparedStatement.setInt(2, 0); // OFFSET
runQuery(preparedStatement); // Correct result: 5 rows printed
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void runQuery(PreparedStatement preparedStatement) throws SQLException {
try (ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
System.out.printf("%s, %s, %d, %d, %d%n",
resultSet.getString(1),
resultSet.getString(2),
resultSet.getLong(3),
resultSet.getLong(4),
resultSet.getLong(5));
}
System.out.println("-------------------------");
}
}
}
Originally posted by @javafanboy in #14907