[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[ https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15755154#comment-15755154 ] Julian Hyde commented on CALCITE-1527: -- I think I've fixed it in https://github.com/julianhyde/calcite/commit/f684c6edc615386e94f2570ad9876f2c8567989c. Testing now. > Support DML in the JDBC adapter > --- > > Key: CALCITE-1527 > URL: https://issues.apache.org/jira/browse/CALCITE-1527 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Reporter: Christian Tzolov >Assignee: Julian Hyde > Fix For: 1.11.0 > > > Currently the JDBC adapter does not support the DML operations: *INSERT*, > *DELETE* and *UPDATE*. > Solution needs to convert the parsed *Modify* and *Values* RelNodes into > *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding > SqlInsert, SqlUpdate and SqlDelete. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15755049#comment-15755049
]
Julian Hyde commented on CALCITE-1527:
--
I see it running the whole suite: ({{mvn test}}); {{mvn -Dtest=JdbcAdapterTest
-DfailIfNoTests=false test}} also reproduces the problem.
> Support DML in the JDBC adapter
> ---
>
> Key: CALCITE-1527
> URL: https://issues.apache.org/jira/browse/CALCITE-1527
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Christian Tzolov
>Assignee: Julian Hyde
> Fix For: 1.11.0
>
>
> Currently the JDBC adapter does not support the DML operations: *INSERT*,
> *DELETE* and *UPDATE*.
> Solution needs to convert the parsed *Modify* and *Values* RelNodes into
> *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding
> SqlInsert, SqlUpdate and SqlDelete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[ https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15754596#comment-15754596 ] Christian Tzolov commented on CALCITE-1527: --- Sure let me try to reproduce and fix this. Does it fails when your run integration test on the entire project or only on this test? We discussed the subject earlier that unlike the other JdbcAdapterTest tests the DML tests mutate the state and it seems that the test suite doesn't always insulate the DB between the tests in the suite. > Support DML in the JDBC adapter > --- > > Key: CALCITE-1527 > URL: https://issues.apache.org/jira/browse/CALCITE-1527 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Reporter: Christian Tzolov >Assignee: Julian Hyde > Fix For: 1.11.0 > > > Currently the JDBC adapter does not support the DML operations: *INSERT*, > *DELETE* and *UPDATE*. > Solution needs to convert the parsed *Modify* and *Values* RelNodes into > *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding > SqlInsert, SqlUpdate and SqlDelete. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15752963#comment-15752963
]
Julian Hyde commented on CALCITE-1527:
--
[~tzolov], The test is flapping on JDK 1.8 (stable on 1.7, for some reason). I
get intermittent failures like this:
{noformat}
Tests run: 29, Failures: 1, Errors: 0, Skipped: 0, Time elapsed: 1.083 sec <<<
FAILURE! - in org.apache.calcite.test.JdbcAdapterTest
testTableModifyDelete(org.apache.calcite.test.JdbcAdapterTest) Time elapsed:
0.097 sec <<< FAILURE!
java.lang.AssertionError:
Expected: is <1>
but: was <2>
at
org.apache.calcite.test.JdbcAdapterTest.testTableModifyDelete(JdbcAdapterTest.java:763)
{noformat}
Can you take a look, please?
> Support DML in the JDBC adapter
> ---
>
> Key: CALCITE-1527
> URL: https://issues.apache.org/jira/browse/CALCITE-1527
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Christian Tzolov
>Assignee: Julian Hyde
> Fix For: 1.11.0
>
>
> Currently the JDBC adapter does not support the DML operations: *INSERT*,
> *DELETE* and *UPDATE*.
> Solution needs to convert the parsed *Modify* and *Values* RelNodes into
> *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding
> SqlInsert, SqlUpdate and SqlDelete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15729632#comment-15729632
]
Julian Hyde commented on CALCITE-1527:
--
Regarding {{INSERT (SELECT vx As Cx...)}}. Yes, that's dropped. In my view,
{{Values}} is a core relational expression. (Every real language has literals,
right?) If a particular SQL dialect doesn't support {{VALUES}} then we can work
around it using {{SELECT ... UNION}} or something.
Regarding {{input.asSelect()}} in {{UPDATE}} and {{DELETE}}. The constructor
parameter for each requires a {{SqlSelect}} (not a {{VALUES}}, nor a {{UNION}})
because SQL {{UPDATE}} and {{DELETE}} syntax handle the source relational
expression differently than {{INSERT}} does. I propose we leave it as
{{asSelect}}. If you can find a test case that breaks this, bring it on!
> Support DML in the JDBC adapter
> ---
>
> Key: CALCITE-1527
> URL: https://issues.apache.org/jira/browse/CALCITE-1527
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Christian Tzolov
>Assignee: Julian Hyde
>
> Currently the JDBC adapter does not support the DML operations: *INSERT*,
> *DELETE* and *UPDATE*.
> Solution needs to convert the parsed *Modify* and *Values* RelNodes into
> *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding
> SqlInsert, SqlUpdate and SqlDelete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15729294#comment-15729294
]
Christian Tzolov commented on CALCITE-1527:
---
Nice! Looks like you've replaced {{visit(Values e)}} by the
{{convertValues(e)}} one. Is the conversion of {{INSERT...VALUES}} into
{{INSERT (SELECT vx As Cx...)}} case dropped?
I guess in {{RelToSqlConverter}} the line {{((SqlSelect)
input.node).getWhere()}} should be {{input.asStatement()}} instead?
> Support DML in the JDBC adapter
> ---
>
> Key: CALCITE-1527
> URL: https://issues.apache.org/jira/browse/CALCITE-1527
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Christian Tzolov
>Assignee: Julian Hyde
>
> Currently the JDBC adapter does not support the DML operations: *INSERT*,
> *DELETE* and *UPDATE*.
> Solution needs to convert the parsed *Modify* and *Values* RelNodes into
> *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding
> SqlInsert, SqlUpdate and SqlDelete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[ https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15729190#comment-15729190 ] Julian Hyde commented on CALCITE-1527: -- My changes (un-squashed) are in https://github.com/julianhyde/calcite/tree/1527-jdbc-dml. Testing now. > Support DML in the JDBC adapter > --- > > Key: CALCITE-1527 > URL: https://issues.apache.org/jira/browse/CALCITE-1527 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Reporter: Christian Tzolov >Assignee: Julian Hyde > > Currently the JDBC adapter does not support the DML operations: *INSERT*, > *DELETE* and *UPDATE*. > Solution needs to convert the parsed *Modify* and *Values* RelNodes into > *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding > SqlInsert, SqlUpdate and SqlDelete. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[ https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15728065#comment-15728065 ] Julian Hyde commented on CALCITE-1527: -- [~tzolov], Reviewing your changes now, and making some modifications (e.g. I've found a way to handle INSERT ... VALUES and INSERT ... SELECT uniformly). Should be able to commit tomorrow. > Support DML in the JDBC adapter > --- > > Key: CALCITE-1527 > URL: https://issues.apache.org/jira/browse/CALCITE-1527 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Reporter: Christian Tzolov >Assignee: Julian Hyde > > Currently the JDBC adapter does not support the DML operations: *INSERT*, > *DELETE* and *UPDATE*. > Solution needs to convert the parsed *Modify* and *Values* RelNodes into > *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding > SqlInsert, SqlUpdate and SqlDelete. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15727791#comment-15727791
]
Prasad V S commented on CALCITE-1527:
-
Imgetting below exception.
Exception in thread "main" java.sql.SQLException: Error while executing SQL
"INSERT INTO "DevRelease_1.8_98.dbo"."survey" (
"id","name" ) values (1,'nameValue')": Node [rel#9:Subset#1.ENUMERABLE.[]]
could not be implemented; planner state:
Root: rel#9:Subset#1.ENUMERABLE.[]
Original rel:
Sets:
Set#0, type: RecordType(INTEGER id, VARCHAR(30) name)
rel#2:Subset#0.NONE.[], best=null, importance=0.81
rel#0:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER
id, VARCHAR(30) name),tuples=[{ 1, 'nameValue' }]), rowcount=1.0, cumulative
cost={inf}
rel#12:Subset#0.ENUMERABLE.[], best=rel#11, importance=0.405
rel#11:EnumerableValues.ENUMERABLE.[[0, 1],
[1]](type=RecordType(INTEGER id, VARCHAR(30) name),tuples=[{ 1, 'nameValue'
}]), rowcount=1.0, cumulative cost={1.0 rows, 1.0 cpu, 0.0 io}
Set#1, type: RecordType(BIGINT ROWCOUNT)
rel#4:Subset#1.NONE.[], best=null, importance=0.9
rel#3:LogicalTableModify.NONE.[](input=rel#2:Subset#0.NONE.[],table=[DevRelease_1.8_98.dbo,
survey],operation=INSERT,updateColumnList=[],flattened=false), rowcount=1.0,
cumulative cost={inf}
rel#9:Subset#1.ENUMERABLE.[], best=null, importance=1.0
rel#10:AbstractConverter.ENUMERABLE.[](input=rel#4:Subset#1.NONE.[],convention=ENUMERABLE,sort=[]),
rowcount=1.0, cumulative cost={inf}
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
at
org.apache.calcite.avatica.AvaticaStatement.executeLargeUpdate(AvaticaStatement.java:236)
at
org.apache.calcite.avatica.AvaticaStatement.executeUpdate(AvaticaStatement.java:231)
at com.nanobi.calcite.CalciteMSSQL.main(CalciteMSSQL.java:133)
Caused by: org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node
[rel#9:Subset#1.ENUMERABLE.[]] could not be implemented; planner state:
> Support DML in the JDBC adapter
> ---
>
> Key: CALCITE-1527
> URL: https://issues.apache.org/jira/browse/CALCITE-1527
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Christian Tzolov
>Assignee: Julian Hyde
>
> Currently the JDBC adapter does not support the DML operations: *INSERT*,
> *DELETE* and *UPDATE*.
> Solution needs to convert the parsed *Modify* and *Values* RelNodes into
> *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding
> SqlInsert, SqlUpdate and SqlDelete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15726928#comment-15726928
]
Christian Tzolov commented on CALCITE-1527:
---
If we implement {{INSERT ... }} using {{visitChid(0,
)}} only, it will work for non-VALUES expression. But
for {{VALUES}} it will hit the existing
[vist(Values)|https://github.com/apache/calcite/blob/f57db602274411c683089b786d18a510ffcc432f/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L242].
Later always produces {{SELECT}} | {{SELECT ... UNION...}} for {{INSERT ...
VALUES}} and the generated SQL doesn't work on HSQLDB or Postgresql (haven't
tested it with other dialects). IMO this can be fixed if we change the
visitValues implementation to something similar to
[covertValues(Values)|https://github.com/apache/calcite/blob/f57db602274411c683089b786d18a510ffcc432f/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L387].
But because of the VALUES-OUTSIDE-INSERT cases i didn't dare changing existing
visit(Values) method. For the time being i've made it
[conditional|https://github.com/apache/calcite/blob/f57db602274411c683089b786d18a510ffcc432f/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L325]
inside the TableModify.INSERT method that delegates to covertValues(...) in
case of INSERT ... VALUES
bq. I think SQL_INSERT_VALUES_OPERATOR can go.
Do you mean to keep it or remove it? :) Did you had the chance to review the
[latest
version|https://github.com/apache/calcite/blob/f57db602274411c683089b786d18a510ffcc432f/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L309]
Regarding the tests with the last commit i've removed the @FixMethodOrder by
trying to build the test fixtures in the {{doWithConnection}} instead.
> Support DML in the JDBC adapter
> ---
>
> Key: CALCITE-1527
> URL: https://issues.apache.org/jira/browse/CALCITE-1527
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Christian Tzolov
>Assignee: Julian Hyde
>
> Currently the JDBC adapter does not support the DML operations: *INSERT*,
> *DELETE* and *UPDATE*.
> Solution needs to convert the parsed *Modify* and *Values* RelNodes into
> *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding
> SqlInsert, SqlUpdate and SqlDelete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1527) Support DML in the JDBC adapter
[
https://issues.apache.org/jira/browse/CALCITE-1527?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15724154#comment-15724154
]
Julian Hyde commented on CALCITE-1527:
--
My main concern is that you have not implemented {{INSERT ... SELECT}}, only
{{INSERT ... VALUES}}. Do I have that correct? Since Values is just a RelNode,
I wonder whether you could push all supported RelNodes to the JDBC source;
you'd get VALUES for free (so you could get rid of SQL_INSERT_VALUES_OPERATOR),
and also many variants of SELECT (whatever that dialect of SQL supports).
Except for that, the change looks very good.
A few minor notes:
* Is the {{@Ignore("CALCITE-1527")}} needed?
* How about the commented lines {{doWithConnection(new SqlInsertFunction())}}?
* Can you explain why {{@FixMethodOrder(MethodSorters.NAME_ASCENDING)}} is
needed?
* The {{if (modify.getOperation().equals(Operation.INSERT))}} block can be
converted to a switch. Can you do so.
* Can you add javadoc for {{sourceExpressionList}} in {{TableModify}} (either
the field or the constructor). Is it required for UPDATE? Is it not allowed for
INSERT and DELETE? Add a {{Preconditions.checkArgument}} call to enforce the
constraint.
> Support DML in the JDBC adapter
> ---
>
> Key: CALCITE-1527
> URL: https://issues.apache.org/jira/browse/CALCITE-1527
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Christian Tzolov
>
> Currently the JDBC adapter does not support the DML operations: *INSERT*,
> *DELETE* and *UPDATE*.
> Solution needs to convert the parsed *Modify* and *Values* RelNodes into
> *JdbcTableModify*, *JdbcValues* ... such and then in turn into corresponding
> SqlInsert, SqlUpdate and SqlDelete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
