Trying to capture some discussion from a recent pull request: https://github.com/apache/calcite/pull/209#issuecomment-195025402
In a few places (such as https://github.com/apache/calcite/blob/master/avatica/server/src/main/java/org/apache/calcite/avatica/jdbc/JdbcMeta.java#L795-L800), we perform:
Vladimir stated that this is ambiguous (stored procedures differing by argument list and differentiating between the actual type when the value is null) and would be remedied by passing along the desired type when setting the object.
We may also have to invoke setNull explicitly? This is unclear to me.
Consider database has two functions that differ in type of argument only.
For instance compute(varchar), compute(numeric), and compute(user_defined_struct)
Which one should be executed if calling with just preparedStatement.setObject(i, null)?
There is not enough information for the database to choose between varchar and numeric function.
Execution plan depends on the types of bind parameters. For instance, in PostgreSQL, you must tell all the datatypes of the bind variables right in PREPARE message.
That basically means, if you flip between datatypes, you have to use different prepared statement IDs.
If just String val = ...; ps.setObject(1, val) is used, then for non-null it can result in String execution plan, while for null it can flip to unknown.
Same for batched statement execution. PostgreSQL just cannot handle datatype flips right in the middle of the batch. It is handled in the pgjdbc driver, so it cuts batch in several sub batches, so it becomes less efficient.