[
https://issues.apache.org/jira/browse/CALCITE-1164?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vladimir Sitnikov updated CALCITE-1164:
---------------------------------------
Description:
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:
{code}
TypedValue o = parameterValues.get(i);
preparedStatement.setObject(i + 1, o.toJdbc(calendar));
{code}
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.
h5. Reasons why "explicit sql type" is important
h6. Calling the proper function
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.
h6. Performance
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.
was:
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:
{code}
TypedValue o = parameterValues.get(i);
preparedStatement.setObject(i + 1, o.toJdbc(calendar));
{code}
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.
h5. Reasons why "explicit sql type" is important
h6. asdf
> Use setObject(int, Object, int) when binding parameters
> -------------------------------------------------------
>
> Key: CALCITE-1164
> URL: https://issues.apache.org/jira/browse/CALCITE-1164
> Project: Calcite
> Issue Type: Improvement
> Components: avatica
> Reporter: Josh Elser
> Priority: Minor
> Fix For: 1.8.0
>
>
> 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:
> {code}
> TypedValue o = parameterValues.get(i);
> preparedStatement.setObject(i + 1, o.toJdbc(calendar));
> {code}
> 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.
> h5. Reasons why "explicit sql type" is important
> h6. Calling the proper function
> 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.
> h6. Performance
> 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.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
