[jira] [Commented] (CALCITE-1168) Add descibe statement functionality
[
https://issues.apache.org/jira/browse/CALCITE-1168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15210970#comment-15210970
]
Arina Ielchiieva commented on CALCITE-1168:
---
1. describe table statement usually doesn't use TABLE keyword:
{noformat}
DESCRIBE table_name:
{noformat}
2. What syntax is expected from DESCRIBE query?
> Add descibe statement functionality
> ---
>
> Key: CALCITE-1168
> URL: https://issues.apache.org/jira/browse/CALCITE-1168
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Arina Ielchiieva
>Assignee: Julian Hyde
>
> Add DESCIBE
> statement so it can support the following:
> {noformat}
> DESCRIBE [SCHEMA | DATABASE] name;
> DESCRIBE table_name;
> {noformat}
> Example, [usage in MySql|http://dev.mysql.com/doc/refman/5.7/en/explain.html]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (CALCITE-1168) Add descibe statement functionality
[
https://issues.apache.org/jira/browse/CALCITE-1168?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Arina Ielchiieva updated CALCITE-1168:
--
Description:
Add DESCIBE
statement so it can support the following:
{noformat}
DESCRIBE [SCHEMA | DATABASE] name;
DESCRIBE table_name;
{noformat}
Example, [usage in MySql|http://dev.mysql.com/doc/refman/5.7/en/explain.html]
was:
Extend EXPLAIN statement so it can support the following:
{noformat}
[EXPLAIN | DESCRIBE] [SCHEMA | DATABASE] name;
[EXPLAIN | DESCRIBE] table_name;
{noformat}
Example, [usage in MySql|http://dev.mysql.com/doc/refman/5.7/en/explain.html]
> Add descibe statement functionality
> ---
>
> Key: CALCITE-1168
> URL: https://issues.apache.org/jira/browse/CALCITE-1168
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Arina Ielchiieva
>Assignee: Julian Hyde
>
> Add DESCIBE
> statement so it can support the following:
> {noformat}
> DESCRIBE [SCHEMA | DATABASE] name;
> DESCRIBE table_name;
> {noformat}
> Example, [usage in MySql|http://dev.mysql.com/doc/refman/5.7/en/explain.html]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (CALCITE-1168) Add descibe statement functionality
[
https://issues.apache.org/jira/browse/CALCITE-1168?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Arina Ielchiieva updated CALCITE-1168:
--
Summary: Add descibe statement functionality (was: Extend EXPLAIN
statement)
> Add descibe statement functionality
> ---
>
> Key: CALCITE-1168
> URL: https://issues.apache.org/jira/browse/CALCITE-1168
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Arina Ielchiieva
>Assignee: Julian Hyde
>
> Extend EXPLAIN statement so it can support the following:
> {noformat}
> [EXPLAIN | DESCRIBE] [SCHEMA | DATABASE] name;
> [EXPLAIN | DESCRIBE] table_name;
> {noformat}
> Example, [usage in MySql|http://dev.mysql.com/doc/refman/5.7/en/explain.html]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1163) Avatica sub-site logo leads to Calcite site instead of Avatica's
[
https://issues.apache.org/jira/browse/CALCITE-1163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15210829#comment-15210829
]
Josh Elser commented on CALCITE-1163:
-
bq. I might be wrong, but I think other pages on the Avatica site still have
the problem.
Augh! You're right. I only copied in the root index.html. Will fix.
> Avatica sub-site logo leads to Calcite site instead of Avatica's
>
>
> Key: CALCITE-1163
> URL: https://issues.apache.org/jira/browse/CALCITE-1163
> Project: Calcite
> Issue Type: Improvement
> Components: site
>Reporter: Alexander Reshetov
>Assignee: Alexander Reshetov
>Priority: Trivial
> Labels: avatica
> Fix For: avatica-1.8.0
>
> Attachments: fix_avatica_site_logo_link.patch
>
>
> Avatica logo (top left corner) on Avatica's sub-site
> https://calcite.apache.org/avatica/ leads to Calcite's main site.
> Which should lead to the Avatica's instead, just like Home navigation link.
> Here is proposed patch
> {noformat}
> diff --git a/avatica/site/_includes/header.html
> b/avatica/site/_includes/header.html
> index 8205804..cff1a33 100644
> --- a/avatica/site/_includes/header.html
> +++ b/avatica/site/_includes/header.html
> @@ -5,7 +5,7 @@
>
>
>
> -
> +
>Apache Calcite Avatica
> height="140" alt="Calcite Logo">
>
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1167) OVERLAPS doesnt swap operands
[ https://issues.apache.org/jira/browse/CALCITE-1167?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15210811#comment-15210811 ] Julian Hyde commented on CALCITE-1167: -- Just to clarify: Does the SQL standard say that OVERLAPS should swap operands? > OVERLAPS doesnt swap operands > - > > Key: CALCITE-1167 > URL: https://issues.apache.org/jira/browse/CALCITE-1167 > Project: Calcite > Issue Type: Bug >Reporter: Serge Harnyk >Assignee: Julian Hyde >Priority: Minor > > If second date in interval is earlier than first OVERLAPS doesnt swap them. > Example: > select 1 from "TEST"."tdt" where (date '1999-12-01' , date '2001-12-31' ) > overlaps ( date '2001-01-01' , date '2002-11-11' ); > ++ > | EXPR$0 | > ++ > | 1 | > | 1 | > | 1 | > | 1 | > ++ > select 1 from "TEST"."tdt" where ( date '2001-12-31', date '1999-12-01' ) > overlaps ( date '2001-01-01' , date '2002-11-11' ); > ++ > | EXPR$0 | > ++ -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CALCITE-1163) Avatica sub-site logo leads to Calcite site instead of Avatica's
[
https://issues.apache.org/jira/browse/CALCITE-1163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15210808#comment-15210808
]
Julian Hyde commented on CALCITE-1163:
--
[~elserj], did you deploy the new site? I might be wrong, but I think other
pages on the Avatica site still have the problem.
> Avatica sub-site logo leads to Calcite site instead of Avatica's
>
>
> Key: CALCITE-1163
> URL: https://issues.apache.org/jira/browse/CALCITE-1163
> Project: Calcite
> Issue Type: Improvement
> Components: site
>Reporter: Alexander Reshetov
>Assignee: Alexander Reshetov
>Priority: Trivial
> Labels: avatica
> Fix For: avatica-1.8.0
>
> Attachments: fix_avatica_site_logo_link.patch
>
>
> Avatica logo (top left corner) on Avatica's sub-site
> https://calcite.apache.org/avatica/ leads to Calcite's main site.
> Which should lead to the Avatica's instead, just like Home navigation link.
> Here is proposed patch
> {noformat}
> diff --git a/avatica/site/_includes/header.html
> b/avatica/site/_includes/header.html
> index 8205804..cff1a33 100644
> --- a/avatica/site/_includes/header.html
> +++ b/avatica/site/_includes/header.html
> @@ -5,7 +5,7 @@
>
>
>
> -
> +
>Apache Calcite Avatica
> height="140" alt="Calcite Logo">
>
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (CALCITE-1165) VolcanoCost comparing floating point numbers might cause problems in a few places
[
https://issues.apache.org/jira/browse/CALCITE-1165?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1165:
-
Summary: VolcanoCost comparing floating point numbers might cause problems
in a few places (was: VocanoCost comparing floating point numbers might cause
problems in a few places)
> VolcanoCost comparing floating point numbers might cause problems in a few
> places
> -
>
> Key: CALCITE-1165
> URL: https://issues.apache.org/jira/browse/CALCITE-1165
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.7.0
>Reporter: Maryann Xue
>Assignee: Julian Hyde
>Priority: Minor
>
> The floating number comparison can be inaccurate and can break some logic in
> VolcanoPlanner. For example,
> 1) VolcanoPlanner.validate():
> {code}
> for (RelNode rel : subset.getRels()) {
> RelOptCost relCost = getCost(rel, mq);
> if (relCost.isLt(subset.bestCost)) {
> throw new AssertionError(
> "rel [" + rel.getDescription()
> + "] has lower cost " + relCost
> + " than best cost " + subset.bestCost
> + " of subset [" + subset.getDescription() + "]");
> }
> }
> {code}
> 2) VolcanoPlanner.getCost(RelNode, RelMetadataQuery)
> {code}
> RelOptCost cost = mq.getNonCumulativeCost(rel);
> if (!zeroCost.isLt(cost)) {
> // cost must be positive, so nudge it
> cost = costFactory.makeTinyCost();
> }
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1169) Memory leak caused by CALCITE-604 and CALCITE-1147
[ https://issues.apache.org/jira/browse/CALCITE-1169?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15210789#comment-15210789 ] Julian Hyde commented on CALCITE-1169: -- [~maryannxue], Can you confirm that this this issue occurs after http://git-wip-us.apache.org/repos/asf/calcite/commit/94f8837c (which fixed CALCITE-1147) but not before it? > Memory leak caused by CALCITE-604 and CALCITE-1147 > -- > > Key: CALCITE-1169 > URL: https://issues.apache.org/jira/browse/CALCITE-1169 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.7.0 >Reporter: Maryann Xue >Assignee: Julian Hyde > > It might be easier to reproduce this with an IDE environment. If I try > running CalciteIT in Calcite-Phoenix in eclipse, it would usually crash after > the first 3 - 4 test cases because of OOM. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Resolved] (CALCITE-1061) RemoteMetaTest#testRemoteStatementInsert's use of hsqldb isn't guarded
[ https://issues.apache.org/jira/browse/CALCITE-1061?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Josh Elser resolved CALCITE-1061. - Resolution: Fixed Fixed in https://git1-us-west.apache.org/repos/asf?p=calcite.git;a=commit;h=0b855b180406cd2b055966f4c832eba46b1c91ee > RemoteMetaTest#testRemoteStatementInsert's use of hsqldb isn't guarded > -- > > Key: CALCITE-1061 > URL: https://issues.apache.org/jira/browse/CALCITE-1061 > Project: Calcite > Issue Type: Bug > Components: avatica >Affects Versions: 1.6.0, 1.5.0 >Reporter: Josh Elser >Assignee: Josh Elser >Priority: Minor > Fix For: avatica-1.8.0 > > > I noticed this while looking into some unit test failures. > RemoteMetaTest#testRemoteStatementInsert doesn't include the locking around > the use of hsqldb. We've noticed that we get really weird errors out of > hsqldb when multiple test runners are concurrently using it. > I don't think we've had any reports of this test failing on Apache lists, but > it would be nice to fix. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (CALCITE-1169) Memory leak caused by CALCITE-604 and CALCITE-1147
Maryann Xue created CALCITE-1169: Summary: Memory leak caused by CALCITE-604 and CALCITE-1147 Key: CALCITE-1169 URL: https://issues.apache.org/jira/browse/CALCITE-1169 Project: Calcite Issue Type: Bug Affects Versions: 1.7.0 Reporter: Maryann Xue Assignee: Julian Hyde It might be easier to reproduce this with an IDE environment. If I try running CalciteIT in Calcite-Phoenix in eclipse, it would usually crash after the first 3 - 4 test cases because of OOM. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CALCITE-1128) Support addBatch()/executeBatch() in remote driver
[ https://issues.apache.org/jira/browse/CALCITE-1128?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15210756#comment-15210756 ] Josh Elser commented on CALCITE-1128: - Threw in a quick addendum in https://git1-us-west.apache.org/repos/asf?p=calcite.git;a=commit;h=48d8ebf57fcb9890854dcdec4c26a4bd8ff26a9c. Missed a compiler warning and fixed (what could potentially) be a thrown NPE. > Support addBatch()/executeBatch() in remote driver > -- > > Key: CALCITE-1128 > URL: https://issues.apache.org/jira/browse/CALCITE-1128 > Project: Calcite > Issue Type: Improvement > Components: avatica >Reporter: Josh Elser >Assignee: Josh Elser >Priority: Critical > Fix For: avatica-1.8.0 > > > JDBC has some "batch-oriented" APIs which should help in the heavy-write > workloads. These should help ammortize the RPC cost of repeated > PrepareAndExecuteRequests and ExecuteRequests. > Through some of the recent performance work, CALCITE-1091, I think we're > getting close to the limit of what is possible given our current server > implementation (both in terms of HTTP-based clients and deserialization in > POJOs). The thought is that we will get some better throughput if we reduce > the total number of RPCs. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Resolved] (CALCITE-1159) Support Kerberos-authenticated clients using SPNEGO
[ https://issues.apache.org/jira/browse/CALCITE-1159?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Josh Elser resolved CALCITE-1159. - Resolution: Fixed Fixed in https://git1-us-west.apache.org/repos/asf?p=calcite.git;a=commit;h=406372f1274a6a0c9fe2b471ce6d65669e798633 > Support Kerberos-authenticated clients using SPNEGO > --- > > Key: CALCITE-1159 > URL: https://issues.apache.org/jira/browse/CALCITE-1159 > Project: Calcite > Issue Type: Sub-task > Components: avatica >Reporter: Josh Elser >Assignee: Josh Elser > Fix For: avatica-1.8.0 > > > Given the number of downstream users of Avatica in the Hadoop "ecosystem", > and the prevalence of Kerberos for authentication, it makes sense to offer > that as an authentication mechanism for Avatica. > I have a working unit test already for this, but am working through some > implementation details. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (CALCITE-1168) Extend EXPLAIN statement
[
https://issues.apache.org/jira/browse/CALCITE-1168?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Arina Ielchiieva updated CALCITE-1168:
--
Description:
Extend EXPLAIN statement so it can support the following:
{noformat}
[EXPLAIN | DESCRIBE] [SCHEMA | DATABASE] name;
[EXPLAIN | DESCRIBE] table_name;
{noformat}
Example, [usage in MySql|http://dev.mysql.com/doc/refman/5.7/en/explain.html]
was:
Extend EXPLAIN statement so it can support the following statements:
{noformat}
[EXPLAIN | DESCRIBE] [SCHEMA | DATABASE] name;
[EXPLAIN | DESCRIBE] table_name;
{noformat}
Example, [usage in MySql|http://dev.mysql.com/doc/refman/5.7/en/explain.html]
> Extend EXPLAIN statement
>
>
> Key: CALCITE-1168
> URL: https://issues.apache.org/jira/browse/CALCITE-1168
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Arina Ielchiieva
>Assignee: Julian Hyde
>
> Extend EXPLAIN statement so it can support the following:
> {noformat}
> [EXPLAIN | DESCRIBE] [SCHEMA | DATABASE] name;
> [EXPLAIN | DESCRIBE] table_name;
> {noformat}
> Example, [usage in MySql|http://dev.mysql.com/doc/refman/5.7/en/explain.html]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (CALCITE-1168) Extend EXPLAIN statement
Arina Ielchiieva created CALCITE-1168:
-
Summary: Extend EXPLAIN statement
Key: CALCITE-1168
URL: https://issues.apache.org/jira/browse/CALCITE-1168
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Arina Ielchiieva
Assignee: Julian Hyde
Extend EXPLAIN statement so it can support the following statements:
{noformat}
[EXPLAIN | DESCRIBE] [SCHEMA | DATABASE] name;
[EXPLAIN | DESCRIBE] table_name;
{noformat}
Example, [usage in MySql|http://dev.mysql.com/doc/refman/5.7/en/explain.html]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1148) Trait conversion broken for RelTraits other than Convention
[ https://issues.apache.org/jira/browse/CALCITE-1148?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15210543#comment-15210543 ] Julian Hyde commented on CALCITE-1148: -- Cc [~maryannxue]. > Trait conversion broken for RelTraits other than Convention > --- > > Key: CALCITE-1148 > URL: https://issues.apache.org/jira/browse/CALCITE-1148 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: MinJi Kim >Assignee: Julian Hyde > > RelTraits (e.g. RelCollationTrait and RelDistributionTrait) fail to convert > in calcite core. Convention is handled specially at the root node (with > VolcanoPlanner.ensureRootConverters()), but this assumes that convention > conversions are not necessary underneath the root, which may not necessarily > be true. In order for RelTrait conversions to work, there is a need to have > converters (via AbstractConverters which use RelTraitDef.convert()). > In a previous commit (b312031), AbstractConverters were added too > aggressively. For example, even if RelTraitDef.canConvert() returns false, > AbstractConverters were superfluously added. As a result, many plans would > take a lot longer to plan (as pointed by 3b55c35). But removing the > AbstractConverters means that RelTrait conversions are not handled properly > in calcite. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (CALCITE-1164) Use setObject(int, Object, int) when binding parameters
[
https://issues.apache.org/jira/browse/CALCITE-1164?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Commented] (CALCITE-1164) Use setObject(int, Object, int) when binding parameters
[
https://issues.apache.org/jira/browse/CALCITE-1164?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15209914#comment-15209914
]
Vladimir Sitnikov commented on CALCITE-1164:
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)
[jira] [Comment Edited] (CALCITE-1164) Use setObject(int, Object, int) when binding parameters
[
https://issues.apache.org/jira/browse/CALCITE-1164?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15209510#comment-15209510
]
Vladimir Sitnikov edited comment on CALCITE-1164 at 3/24/16 7:12 AM:
-
-Do you think the following query should fail? It looks awkward-
was (Author: vladimirsitnikov):
Do you think the following query should fail? It looks awkward
{code:java}
@Test
public void testWithInsideWhereExists() throws SQLException {
Connection con = CalciteAssert.hr()
.connect();
PreparedStatement ps =
con.prepareStatement("select * from \"hr\".\"depts\" where 'a' = ?");
ps.setObject(1, "a");
ps.execute();
}
{code}
{noformat}
private final java.util.List relClasses;
public final org.apache.calcite.rel.metadata.RelMdCollation provider0;
public GeneratedMetadataHandler_Collation(java.util.List relClasses,
org.apache.calcite.rel.metadata.RelMdCollation provider0) {
this.relClasses = relClasses;
this.provider0 = provider0;
}
public org.apache.calcite.rel.metadata.MetadataDef getDef() {
return org.apache.calcite.rel.metadata.BuiltInMetadata$Collation.DEF;
}
public com.google.common.collect.ImmutableList collations(
org.apache.calcite.rel.RelNode r,
org.apache.calcite.rel.metadata.RelMetadataQuery mq) {
final java.util.List key =
org.apache.calcite.runtime.FlatLists.of(org.apache.calcite.rel.metadata.BuiltInMetadata$Collation.DEF,
r);
final Object v = mq.map.get(key);
if (v != null) {
if (v == org.apache.calcite.rel.metadata.NullSentinel.ACTIVE) {
throw org.apache.calcite.rel.metadata.CyclicMetadataException.INSTANCE;
}
return (com.google.common.collect.ImmutableList) v;
}
mq.map.put(key,org.apache.calcite.rel.metadata.NullSentinel.ACTIVE);
try {
final com.google.common.collect.ImmutableList x = collations_(r, mq);
mq.map.put(key, x);
return x;
} catch
(org.apache.calcite.rel.metadata.JaninoRelMetadataProvider$NoHandler e) {
mq.map.remove(key);
throw e;
}
}
private com.google.common.collect.ImmutableList collations_(
org.apache.calcite.rel.RelNode r,
org.apache.calcite.rel.metadata.RelMetadataQuery mq) {
switch (relClasses.indexOf(r.getClass())) {
default:
return provider0.collations((org.apache.calcite.rel.RelNode) r, mq);
case 2:
return provider0.collations((org.apache.calcite.plan.volcano.RelSubset)
r, mq);
case 3:
return collations(((org.apache.calcite.plan.hep.HepRelVertex)
r).getCurrentRel(), mq);
case 10:
case 25:
case 34:
return provider0.collations((org.apache.calcite.rel.core.Filter) r, mq);
case 14:
case 26:
case 38:
return provider0.collations((org.apache.calcite.rel.core.Project) r, mq);
case 15:
case 39:
return provider0.collations((org.apache.calcite.rel.core.Sort) r, mq);
case 18:
case 28:
case 42:
return provider0.collations((org.apache.calcite.rel.core.TableScan) r,
mq);
case 20:
case 44:
return provider0.collations((org.apache.calcite.rel.core.Values) r, mq);
case 21:
case 45:
return provider0.collations((org.apache.calcite.rel.core.Window) r, mq);
case -1:
throw new
org.apache.calcite.rel.metadata.JaninoRelMetadataProvider$NoHandler(r.getClass());
}
}
private final java.util.List relClasses;
public final org.apache.calcite.rel.metadata.RelMdPercentageOriginalRows
provider0;
public GeneratedMetadataHandler_NonCumulativeCost(java.util.List relClasses,
org.apache.calcite.rel.metadata.RelMdPercentageOriginalRows provider0) {
this.relClasses = relClasses;
this.provider0 = provider0;
}
public org.apache.calcite.rel.metadata.MetadataDef getDef() {
return
org.apache.calcite.rel.metadata.BuiltInMetadata$NonCumulativeCost.DEF;
}
public org.apache.calcite.plan.RelOptCost getNonCumulativeCost(
org.apache.calcite.rel.RelNode r,
org.apache.calcite.rel.metadata.RelMetadataQuery mq) {
final java.util.List key =
org.apache.calcite.runtime.FlatLists.of(org.apache.calcite.rel.metadata.BuiltInMetadata$NonCumulativeCost.DEF,
r);
final Object v = mq.map.get(key);
if (v != null) {
if (v == org.apache.calcite.rel.metadata.NullSentinel.ACTIVE) {
throw org.apache.calcite.rel.metadata.CyclicMetadataException.INSTANCE;
}
return (org.apache.calcite.plan.RelOptCost) v;
}
mq.map.put(key,org.apache.calcite.rel.metadata.NullSentinel.ACTIVE);
try {
final org.apache.calcite.plan.RelOptCost x = getNonCumulativeCost_(r, mq);
mq.map.put(key, x);
return x;
} catch
(org.apache.calcite.rel.metadata.JaninoRelMetadataProvider$NoHandler e) {
mq.map.remove(key);
throw e;
}
}
private org.apache.calcite.plan.RelOptCost getNonCumulativeCost_(
