[
https://issues.apache.org/jira/browse/CALCITE-1164?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15209914#comment-15209914
]
Vladimir Sitnikov edited comment on CALCITE-1164 at 3/24/16 7:45 AM:
---------------------------------------------------------------------
PostgreSQL:
{code:sql}
select 'Y' where '01'=1; // returns Y
select 'Y' where 'a'=1; // fails
ERROR: invalid input syntax for integer: "a"
Position: 16
select 1 where 'a'=1
^
{code}
Oracle:
{code:sql}
select 'Y' from dual where '01'=1; // returns Y
select 'Y' from dual where 'a'=1; // fails
--ORA-01722: invalid number
-- Position: 27, Line: 1, Column: 28
-- SQL: select 'Y' from dual where 'a'=1
-- ^-- error here -- ORA-01722: invalid
number
{code}
Calcite:
{code:sql}
select * from "hr"."depts" where '01' = 1
Caused by: java.lang.NoSuchMethodException:
org.apache.calcite.runtime.SqlFunctions.eq(java.lang.String, int)
select * from "hr"."depts" where '01' = ?; setInt(1, 1); // NO rows returned,
no error
select * from "hr"."depts" where 'a' = ?; setInt(1, 1); // NO rows returned,
no error
{code}
Oracle & PostgreSQL do implicit conversion from string to number. Calcite is
inconsistent when it comes to "bind vs literal", and it does numeric -> String
conversion.
Is there a specification on proper way of doing implicit conversions?
was (Author: vladimirsitnikov):
PostgreSQL:
{code:sql}
select 'Y' where '01'=1; // returns Y
select 'Y' where 'a'=1; // fails
ERROR: invalid input syntax for integer: "a"
Position: 16
select 1 where 'a'=1
^
{code}
Oracle:
{code:sql}
select 'Y' from dual where '01'=1; // returns Y
select 'Y' from dual where 'a'=1; // fails
--ORA-01722: invalid number
-- Position: 27, Line: 1, Column: 28
-- SQL: select 'Y' from dual where 'a'=1
-- ^-- error here -- ORA-01722: invalid
number
{code}
Calcite:
{code:sql}
select * from "hr"."depts" where '01' = 1
Caused by: java.lang.NoSuchMethodException:
org.apache.calcite.runtime.SqlFunctions.eq(java.lang.String, int)
select * from "hr"."depts" where '01' = ?; setInt(1, 1); // NO rows returned,
no error
select * from "hr"."depts" where 'a' = ?; setInt(1, 1); // NO rows returned,
no error
{code}
Oracle & PostgreSQL do implicit conversion from number to string. Calcite is
inconsistent when it comes to "bind vs literal", and it does numeric -> String
conversion.
Is there a specification on proper way of doing implicit conversions?
> 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)
