paul-rogers opened a new issue, #12682: URL: https://github.com/apache/druid/issues/12682
### Affected Version Latest `master` version. ### Background According to the [JDBC tutorial](https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html): > There are three different kinds of statements: > > Statement: Used to implement simple SQL statements with no parameters. > PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters. See Using Prepared Statements for more information. > CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters. See Stored Procedures for more information. From [`Statement` Javadoc](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html): > The object used for executing a static SQL statement and returning the results it produces. > > By default, only one ResultSet object per Statement object can be open at the same time. Then [for prepared statements](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html): > If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead. > > The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first. > > Although you can use PreparedStatement objects for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. Examples of this are in the following sections. ### Problem Description Druid, however, does not follow the above protocols. Instead, Druid seems to follow a protocol something like this: * Each statement can be used only once: executed and then discarded. * The same statement kind is used for the "regular" and prepared use cases, with just an adjustment in flow to handle prepare. There are two problems with Druid's do-your-own-thing approach: * We do not follow the JDBC standards as we are obligated to do if we claim JDBC support. * Prepared statements are virtually useless as they don't support the intended "prepare once, execute many" use case. One has to prepare the query for each new set of parameters, which is exactly the problem that `PreparedStatement` was designed to address. This can easily be seen by adding a simple test case to `DruidAvaticaHandlerTest`: ```java @Test public void testExecuteTwice() throws Exception { PreparedStatement statement = superuserClient.prepareStatement("SELECT COUNT(*) AS cnt FROM sys.servers WHERE servers.host = ?"); statement.setString(1, "dummy"); Assert.assertEquals( ImmutableList.of( ImmutableMap.of("cnt", 1L) ), getRows(statement.executeQuery()) ); statement.setString(1, "dummy"); Assert.assertEquals( ImmutableList.of( ImmutableMap.of("cnt", 1L) ), getRows(statement.executeQuery()). // Fails here ); } ``` The above should work according to the JDBC docs. However, in Druid, the test fails on the second execution. ### Solution The [single-pass planner](https://github.com/apache/druid/pull/12636) PR has been expanded to fix the above issues so that we cleanly identify the paths through the planner, and which uses cases do an do not require parameter values. With that fix, the above test passes and the Druid driver follows the JDBC standards (at least in this one area.) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
