[
https://issues.apache.org/jira/browse/CALCITE-1164?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15213080#comment-15213080
]
Vladimir Sitnikov edited comment on CALCITE-1164 at 3/26/16 3:52 PM:
---------------------------------------------------------------------
{quote} There is no reason why '<string literal> = <integer literal>' should
behave the same as '<string literal> = <bind variable>' even if <bind variable>
is subsequently given an integer value{quote}
There is at least one reason:
https://en.wikipedia.org/wiki/Principle_of_least_astonishment
Both PostgreSQL and Oracle agree on the way "<string literal> = <integer
literal>" works. Calcite is different somehow.
{quote}This describes the type conversions {quote}
Note: I'm not even touching {{setObject}} yet. I'm just trying basic stuff like
{{setInt}}.
was (Author: vladimirsitnikov):
{quote} There is no reason why '<string literal> = <integer literal>' should
behave the same as '<string literal> = <bind variable>' even if <bind variable>
is subsequently given an integer value{quote}
There is at least one reason:
https://en.wikipedia.org/wiki/Principle_of_least_astonishment
Both PostgreSQL and Oracle agree on the way "<string literal> = <integer
literal>" works. Calcite is different somehow.
{quote}This describes the type conversions {quote}
Note: I'm not even touching {{setObject}} yet. I'm just trying basics stuff
like {{setInt}}.
> 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)
