You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/01/21 07:09:56 UTC
[GitHub] [shardingsphere] tristaZero opened a new issue #9117: [SQL federation] Add unit test for single table joining sharding table
tristaZero opened a new issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117
Hi,
#8284 is still underway. This issue is to add a case in `CalcitePrepareStatementTest` and `CalciteStatementTest` to verify that a `join` SQL with single tables and sharding tables can work well.
The SQL case is like `select sharding_table.*, single_table.*` from sharding_table, single_table where single_table.id = sharding_table.id. This is just an example, and you can add unit tests with any cases you like. : )
Indeed, it is needed to do some modification on `calcite_data_0.sql`, `calcite_data_1.sql`, `config-calcite.yaml`.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-764437426
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-765250409
Hi @lmhmhl ,
Thanks for your debugging, which help me learn the context of your test. 👍
Could you modify your test sql as the following ones?
```sql
// First one,
select t_order_calcite.*, t_order_item_calcite_sharding.* from t_order_calcite, t_order_item_calcite_sharding where t_order_calcite.order_id = t_order_item_calcite_sharding.item_id
// Second one,
select t_order_calcite.*, t_order_item_calcite_sharding.* from t_order_calcite o , t_order_item_calcite_sharding i where t_order_calcite.order_id = t_order_item_calcite_sharding.item_id
```
Let us figure out whether `Calcite` does not support table alias. I will try to get the answer in Calcite community synchronously.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-765250409
Hi @lmhmhl ,
Thanks for your debugging, which help me learn the context of your test. 👍
Could you modify your test sql as the following ones?
```sql
// First one,
select t_order_calcite.*, t_order_item_calcite_sharding.* from t_order_calcite, t_order_item_calcite_sharding where t_order_calcite.order_id = t_order_item_calcite_sharding.item_id
// Second one,
select o.*, i.* from t_order_calcite o , t_order_item_calcite_sharding i where to.order_id = i.item_id
```
I tried the two of these and no exception here. BTW, I guess your join SQL is incorrect, isn't it? (Two table` join`)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl edited a comment on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl edited a comment on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-764609816
Hi @tristaZero .
I tested the code, some unexpected error happened as follows:
The most usage info is
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'item_id' not found in table 'I'.
```
java.sql.SQLException: Error while preparing statement [select o.*, i.* from t_order_calcite o, t_order_item_calcite_sharding i where o.order_id = ? and i.item_id = ?]
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:220)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:202)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:93)
at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)
at org.apache.shardingsphere.infra.optimize.execute.CalciteJDBCExecutor.execute(CalciteJDBCExecutor.java:85)
at org.apache.shardingsphere.infra.optimize.execute.CalciteJDBCExecutor.executeQuery(CalciteJDBCExecutor.java:68)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQueryByCalcite(ShardingSpherePreparedStatement.java:214)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery0(ShardingSpherePreparedStatement.java:200)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:178)
at org.apache.shardingsphere.driver.jdbc.core.statement.CalcitePrepareStatementTest.assertQueryWithCalciteInSingleAndShardingTable(CalcitePrepareStatementTest.java:61)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 100 to line 1, column 106: Column 'item_id' not found in table 'i'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:883)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:868)
at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5043)
at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:439)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6015)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6000)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:320)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:879)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6033)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:879)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6033)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
at org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:5609)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4123)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3464)
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1067)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1041)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1016)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:724)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:567)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:208)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:642)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:508)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:478)
at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:213)
... 34 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'item_id' not found in table 'i'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:560)
... 73 more
Process finished with exit code 255
```
I added a sharding table named `t_order_item_calcite_sharding` in database1, and config the sharding rule in the yaml file.
SQL statement for creating table and query statement is as follows:
`CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
`
`"select o.*, i.* from t_order_calcite o, t_order_item_calcite_sharding i where o.order_id = ? and i.item_id = ?"`
config-calcite.yaml` is as follows:
```
rules:
- !SHARDING
tables:
t_order_item_calcite_sharding:
actualDataNodes: jdbc_1.t_order_item_calcite_sharding_${0..1}
tableStrategy:
standard:
shardingColumn: item_id
shardingAlgorithmName: table_inline
shardingAlgorithms:
table_inline:
type: INLINE
props:
algorithm-expression: t_order_item_calcite_sharding_${item_id % 2}
```
I have debugged the code and taken a few screenshots of some of the execution results,
I don't know what went wrong in my test, Is there anything wrong with the SQL statement？
<img width="1357" alt="Screen Shot 2021-01-21 at 8 19 12 PM" src="https://user-images.githubusercontent.com/24718258/105350399-1245a980-5c26-11eb-82a8-64014bee2a3f.png">
<img width="577" alt="Screen Shot 2021-01-21 at 8 00 33 PM" src="https://user-images.githubusercontent.com/24718258/105351785-fc38e880-5c27-11eb-8318-3a8143e196c2.png">
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-765416471
Hi @tristaZero .
Honestly, I am in puzzlement now, I checked my code and found that something may be wrong in my yaml file. Does the sharding table should be created like follows? However, when I changed my table creating statements in this way, some error happened
`java.lang.RuntimeException: org.h2.jdbc.JdbcSQLException: Table "t_order_item_calcite_sharding" not found; SQL statement
DELETE FROM t_order_item_calcite_sharding [42102-196]`, I doubt I may write something wrong in my previous `initTable()` method, but I am not sure the method should be written in this way as follows, I don't know how you make the test success, are u using my test code from my last PR? I am a little bit confused, can you give me some advice?
```
CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_0 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_1 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
```
```
public void initTable() {
try {
ShardingSphereConnection conn = dataSource.getConnection();
RunScript.execute(conn.getConnection("jdbc_0"), new InputStreamReader(Objects.requireNonNull(AbstractSQLTest.class.getClassLoader().getResourceAsStream("calcite_data_0.sql"))));
RunScript.execute(conn.getConnection("jdbc_1"), new InputStreamReader(Objects.requireNonNull(AbstractSQLTest.class.getClassLoader().getResourceAsStream("calcite_data_1.sql"))));
conn.close();
} catch (final SQLException ex) {
throw new RuntimeException(ex);
}
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl edited a comment on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl edited a comment on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-766568745
> Hi,
>
> Could you show me the SQLs in `jdbc_init_calcite_1.sql` and `calcite_data_1.sql`? If I have a correct understanding of your sharding table configuration.
>
> The SQLs in `jdbc_init_calcite_1.sql` look like the following ones,
>
> ```sql
> CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_0 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
> CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_1 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
> ```
>
> And some SQLs are likely to appear in `calcite_data_1.sql`
>
> ```sql
> DELETE FROM t_order_item_calcite_sharding_0;
> DELETE FROM t_order_item_calcite_sharding_1;
> INSERT INTO t_order_item_calcite_sharding_0...
> INSERT INTO t_order_item_calcite_sharding_1...
> ```
Hi @tristaZero .
Thanks for your comment, you do prompt me, I checked my code of `calcite_data_1.sql` and found that I referred to the `jdbc_data.sql` before, which are not clearly appointed which actual table to route when do delete and insert operation. I have a question, why do we need to appoint which sharding table to insert or delete in Calcate test? however, in Shariding Sphere statement test there are not appointed any actual table, but only use logic table.
`jdbc_data.sql` is as follows:
```
DELETE FROM t_order;
DELETE FROM t_order_item;
DELETE FROM t_order_auto;
DELETE FROM t_order_item_auto;
DELETE FROM t_config;
INSERT INTO t_order VALUES(1000, 10, 'init');
INSERT INTO t_order VALUES(1001, 10, 'init');
INSERT INTO t_order VALUES(1100, 11, 'init');
INSERT INTO t_order VALUES(1101, 11, 'init');
INSERT INTO t_order_item VALUES(100000, 1000, 10, 'init');
INSERT INTO t_order_item VALUES(100001, 1000, 10, 'init');
INSERT INTO t_order_item VALUES(100100, 1001, 10, 'init');
INSERT INTO t_order_item VALUES(100101, 1001, 10, 'init');
INSERT INTO t_order_item VALUES(110000, 1100, 11, 'init');
INSERT INTO t_order_item VALUES(110001, 1100, 11, 'init');
INSERT INTO t_order_item VALUES(110100, 1101, 11, 'init');
INSERT INTO t_order_item VALUES(110101, 1101, 11, 'init');
INSERT INTO t_order_auto VALUES(1000, 10, 'init');
INSERT INTO t_order_auto VALUES(1100, 11, 'init');
INSERT INTO t_order_item_auto VALUES(100000, 1000, 10, 'init');
INSERT INTO t_order_item_auto VALUES(100100, 1001, 10, 'init');
INSERT INTO t_order_item_auto VALUES(110000, 1100, 11, 'init');
INSERT INTO t_order_item_auto VALUES(110100, 1101, 11, 'init');
INSERT INTO t_config VALUES(1, 'init');
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl edited a comment on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl edited a comment on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-764609816
Hi @tristaZero .
I tested the code, some unexpected error happened as follows:
The most usage info is
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'item_id' not found in table 'I'.
```
java.sql.SQLException: Error while preparing statement [select o.*, i.* from t_order_calcite o, t_order_item_calcite_sharding i where o.order_id = ? and i.item_id = ?]
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:220)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:202)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:93)
at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)
at org.apache.shardingsphere.infra.optimize.execute.CalciteJDBCExecutor.execute(CalciteJDBCExecutor.java:85)
at org.apache.shardingsphere.infra.optimize.execute.CalciteJDBCExecutor.executeQuery(CalciteJDBCExecutor.java:68)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQueryByCalcite(ShardingSpherePreparedStatement.java:214)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery0(ShardingSpherePreparedStatement.java:200)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:178)
at org.apache.shardingsphere.driver.jdbc.core.statement.CalcitePrepareStatementTest.assertQueryWithCalciteInSingleAndShardingTable(CalcitePrepareStatementTest.java:61)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 100 to line 1, column 106: Column 'item_id' not found in table 'i'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:883)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:868)
at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5043)
at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:439)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6015)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6000)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:320)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:879)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6033)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:879)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6033)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
at org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:5609)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4123)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3464)
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1067)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1041)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1016)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:724)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:567)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:208)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:642)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:508)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:478)
at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:213)
... 34 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'item_id' not found in table 'i'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:560)
... 73 more
Process finished with exit code 255
```
I added a sharding table named `t_order_item_calcite_sharding` in database1, and config the sharding rule in the yaml file.
SQL statement for creating table and query statement is as follows:
`CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
`
`"select o.*, i.* from t_order_calcite o, t_order_item_calcite_sharding i where o.order_id = ? and i.item_id = ?"`
config-calcite.yaml` is as follows:
```
rules:
- !SHARDING
tables:
t_order_item_calcite_sharding:
actualDataNodes: jdbc_1.t_order_item_calcite_sharding_${0..1}
tableStrategy:
standard:
shardingColumn: item_id
shardingAlgorithmName: table_inline
shardingAlgorithms:
table_inline:
type: INLINE
props:
algorithm-expression: t_order_item_calcite_sharding_${item_id % 2}
```
I have debugged the code and taken a few screenshots of some of the execution results,
I don't know what went wrong in my test, Is there anything wrong with the SQL statement？
<img width="1357" alt="Screen Shot 2021-01-21 at 8 19 12 PM" src="https://user-images.githubusercontent.com/24718258/105350399-1245a980-5c26-11eb-82a8-64014bee2a3f.png">
<img width="577" alt="Screen Shot 2021-01-21 at 8 00 33 PM" src="https://user-images.githubusercontent.com/24718258/105351785-fc38e880-5c27-11eb-8318-3a8143e196c2.png">
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl edited a comment on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl edited a comment on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-766568745
> Hi,
>
> Could you show me the SQLs in `jdbc_init_calcite_1.sql` and `calcite_data_1.sql`? If I have a correct understanding of your sharding table configuration.
>
> The SQLs in `jdbc_init_calcite_1.sql` look like the following ones,
>
> ```sql
> CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_0 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
> CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_1 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
> ```
>
> And some SQLs are likely to appear in `calcite_data_1.sql`
>
> ```sql
> DELETE FROM t_order_item_calcite_sharding_0;
> DELETE FROM t_order_item_calcite_sharding_1;
> INSERT INTO t_order_item_calcite_sharding_0...
> INSERT INTO t_order_item_calcite_sharding_1...
> ```
Hi @tristaZero .
Thanks for your comment, you do prompt me, I checked my code of `calcite_data_1.sql` and found that I referred to the `jdbc_data.sql` before, which are not clearly appointed which actual table to route when do delete and insert operation. I have a question, why do we need to appoint which sharding table to insert or delete in Calcate test? however, in Shariding Sphere statement test there are not appointed any actual table, but only use logic table.
`jdbc_data.sql` is as follows:
```
DELETE FROM t_order;
DELETE FROM t_order_item;
DELETE FROM t_order_auto;
DELETE FROM t_order_item_auto;
DELETE FROM t_config;
INSERT INTO t_order VALUES(1000, 10, 'init');
INSERT INTO t_order VALUES(1001, 10, 'init');
INSERT INTO t_order VALUES(1100, 11, 'init');
INSERT INTO t_order VALUES(1101, 11, 'init');
INSERT INTO t_order_item VALUES(100000, 1000, 10, 'init');
INSERT INTO t_order_item VALUES(100001, 1000, 10, 'init');
INSERT INTO t_order_item VALUES(100100, 1001, 10, 'init');
INSERT INTO t_order_item VALUES(100101, 1001, 10, 'init');
INSERT INTO t_order_item VALUES(110000, 1100, 11, 'init');
INSERT INTO t_order_item VALUES(110001, 1100, 11, 'init');
INSERT INTO t_order_item VALUES(110100, 1101, 11, 'init');
INSERT INTO t_order_item VALUES(110101, 1101, 11, 'init');
INSERT INTO t_order_auto VALUES(1000, 10, 'init');
INSERT INTO t_order_auto VALUES(1100, 11, 'init');
INSERT INTO t_order_item_auto VALUES(100000, 1000, 10, 'init');
INSERT INTO t_order_item_auto VALUES(100100, 1001, 10, 'init');
INSERT INTO t_order_item_auto VALUES(110000, 1100, 11, 'init');
INSERT INTO t_order_item_auto VALUES(110100, 1101, 11, 'init');
INSERT INTO t_config VALUES(1, 'init');
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-764434396
Hi @lmhmhl ,
Would you like to take charge of this one?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-764628537
<img width="1240" alt="Screen Shot 2021-01-21 at 8 57 54 PM" src="https://user-images.githubusercontent.com/24718258/105354468-8f275200-5c2b-11eb-8591-d58bc72460c7.png">
<img width="1003" alt="Screen Shot 2021-01-21 at 8 54 09 PM" src="https://user-images.githubusercontent.com/24718258/105354835-17a5f280-5c2c-11eb-8521-b916b53d34c3.png">
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-765250409
Hi @lmhmhl ,
Thanks for your debugging, which help me learn the context of your test. 👍
Could you modify your test sql as the following ones?
```sql
// First one,
select t_order_calcite.*, t_order_item_calcite_sharding.* from t_order_calcite, t_order_item_calcite_sharding where t_order_calcite.order_id = t_order_item_calcite_sharding.item_id
// Second one,
select o.*, i.* from t_order_calcite o , t_order_item_calcite_sharding i where to.order_id = i.item_id
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-766360429
Hi,
Could you show me the SQLs in `jdbc_init_calcite_1.sql` and `calcite_data_1.sql`? If I have a correct understanding of your sharding table configuration.
The SQLs in `jdbc_init_calcite_1.sql` look like the following ones,
```sql
CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_0 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_1 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
```
And some SQLs are likely to appear in `calcite_data_1.sql`
```sql
DELETE FROM t_order_item_calcite_sharding_0;
DELETE FROM t_order_item_calcite_sharding_1;
INSERT INTO t_order_item_calcite_sharding_0...
INSERT INTO t_order_item_calcite_sharding_1...
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-782998385
Hi @lmhmhl ,
This issue is done, right?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-765250409
Hi @lmhmhl ,
Thanks for your debugging, which help me learn the context of your test. 👍
Could you modify your test sql as the following ones?
```sql
// First one,
select t_order_calcite.*, t_order_item_calcite_sharding.* from t_order_calcite, t_order_item_calcite_sharding where t_order_calcite.order_id = ? and t_order_item_calcite_sharding.item_id = ?
// Second one,
select t_order_calcite.*, t_order_item_calcite_sharding.* from t_order_calcite o , t_order_item_calcite_sharding i where t_order_calcite.order_id = ? and t_order_item_calcite_sharding.item_id = ?
```
Let us figure out whether `Calcite` does not support table alias. I will try to get the answer in Calcite community synchronously.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-766568745
> Hi,
>
> Could you show me the SQLs in `jdbc_init_calcite_1.sql` and `calcite_data_1.sql`? If I have a correct understanding of your sharding table configuration.
>
> The SQLs in `jdbc_init_calcite_1.sql` look like the following ones,
>
> ```sql
> CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_0 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
> CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_1 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
> ```
>
> And some SQLs are likely to appear in `calcite_data_1.sql`
>
> ```sql
> DELETE FROM t_order_item_calcite_sharding_0;
> DELETE FROM t_order_item_calcite_sharding_1;
> INSERT INTO t_order_item_calcite_sharding_0...
> INSERT INTO t_order_item_calcite_sharding_1...
> ```
Hi @tristaZero .
Thanks for your comment, you do prompt me, I checked my code of `calcite_data_1.sql` and found that I referred to the `jdbc_data.sql` before, which are not clearly appointed which actual table to route when do delete and insert operation. I have a question, why do we need to appoint which sharding table to insert or delete in Calcate test? however, in Shariding Sphere statement test are not appointed any actual table, but only use logic table.
`jdbc_data.sql` is as follows:
```
DELETE FROM t_order;
DELETE FROM t_order_item;
DELETE FROM t_order_auto;
DELETE FROM t_order_item_auto;
DELETE FROM t_config;
INSERT INTO t_order VALUES(1000, 10, 'init');
INSERT INTO t_order VALUES(1001, 10, 'init');
INSERT INTO t_order VALUES(1100, 11, 'init');
INSERT INTO t_order VALUES(1101, 11, 'init');
INSERT INTO t_order_item VALUES(100000, 1000, 10, 'init');
INSERT INTO t_order_item VALUES(100001, 1000, 10, 'init');
INSERT INTO t_order_item VALUES(100100, 1001, 10, 'init');
INSERT INTO t_order_item VALUES(100101, 1001, 10, 'init');
INSERT INTO t_order_item VALUES(110000, 1100, 11, 'init');
INSERT INTO t_order_item VALUES(110001, 1100, 11, 'init');
INSERT INTO t_order_item VALUES(110100, 1101, 11, 'init');
INSERT INTO t_order_item VALUES(110101, 1101, 11, 'init');
INSERT INTO t_order_auto VALUES(1000, 10, 'init');
INSERT INTO t_order_auto VALUES(1100, 11, 'init');
INSERT INTO t_order_item_auto VALUES(100000, 1000, 10, 'init');
INSERT INTO t_order_item_auto VALUES(100100, 1001, 10, 'init');
INSERT INTO t_order_item_auto VALUES(110000, 1100, 11, 'init');
INSERT INTO t_order_item_auto VALUES(110100, 1101, 11, 'init');
INSERT INTO t_config VALUES(1, 'init');
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl edited a comment on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl edited a comment on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-765416471
Hi @tristaZero .
Honestly, I am in puzzlement now, I checked my code and found that something may be wrong in my `jdbc_init_calcite_1.sql` file. Does the sharding table should be created like follows? However, when I changed my table creating statements in this way, some error happened
`java.lang.RuntimeException: org.h2.jdbc.JdbcSQLException: Table "t_order_item_calcite_sharding" not found; SQL statement
DELETE FROM t_order_item_calcite_sharding [42102-196]`, I doubt I may write something wrong in my previous `initTable()` method, but I am not sure the method should be written in this way as follows, I don't know how you make the test success, are u using my test code from my last PR? I am a little bit confused, can you give me some advice?
```
CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_0 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_1 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
```
```
public void initTable() {
try {
ShardingSphereConnection conn = dataSource.getConnection();
RunScript.execute(conn.getConnection("jdbc_0"), new InputStreamReader(Objects.requireNonNull(AbstractSQLTest.class.getClassLoader().getResourceAsStream("calcite_data_0.sql"))));
RunScript.execute(conn.getConnection("jdbc_1"), new InputStreamReader(Objects.requireNonNull(AbstractSQLTest.class.getClassLoader().getResourceAsStream("calcite_data_1.sql"))));
conn.close();
} catch (final SQLException ex) {
throw new RuntimeException(ex);
}
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-766568745
> Hi,
>
> Could you show me the SQLs in `jdbc_init_calcite_1.sql` and `calcite_data_1.sql`? If I have a correct understanding of your sharding table configuration.
>
> The SQLs in `jdbc_init_calcite_1.sql` look like the following ones,
>
> ```sql
> CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_0 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
> CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_1 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
> ```
>
> And some SQLs are likely to appear in `calcite_data_1.sql`
>
> ```sql
> DELETE FROM t_order_item_calcite_sharding_0;
> DELETE FROM t_order_item_calcite_sharding_1;
> INSERT INTO t_order_item_calcite_sharding_0...
> INSERT INTO t_order_item_calcite_sharding_1...
> ```
Hi @tristaZero .
Thanks for your comment, you do prompt me, I checked my code of `calcite_data_1.sql` and found that I referred to the `jdbc_data.sql` before, which are not clearly appointed which actual table to route when do delete and insert operation. I have a question, why do we need to appoint which sharding table to insert or delete in Calcate test? however, in Shariding Sphere statement test are not appointed any actual table, but only use logic table.
`jdbc_data.sql` is as follows:
```
DELETE FROM t_order;
DELETE FROM t_order_item;
DELETE FROM t_order_auto;
DELETE FROM t_order_item_auto;
DELETE FROM t_config;
INSERT INTO t_order VALUES(1000, 10, 'init');
INSERT INTO t_order VALUES(1001, 10, 'init');
INSERT INTO t_order VALUES(1100, 11, 'init');
INSERT INTO t_order VALUES(1101, 11, 'init');
INSERT INTO t_order_item VALUES(100000, 1000, 10, 'init');
INSERT INTO t_order_item VALUES(100001, 1000, 10, 'init');
INSERT INTO t_order_item VALUES(100100, 1001, 10, 'init');
INSERT INTO t_order_item VALUES(100101, 1001, 10, 'init');
INSERT INTO t_order_item VALUES(110000, 1100, 11, 'init');
INSERT INTO t_order_item VALUES(110001, 1100, 11, 'init');
INSERT INTO t_order_item VALUES(110100, 1101, 11, 'init');
INSERT INTO t_order_item VALUES(110101, 1101, 11, 'init');
INSERT INTO t_order_auto VALUES(1000, 10, 'init');
INSERT INTO t_order_auto VALUES(1100, 11, 'init');
INSERT INTO t_order_item_auto VALUES(100000, 1000, 10, 'init');
INSERT INTO t_order_item_auto VALUES(100100, 1001, 10, 'init');
INSERT INTO t_order_item_auto VALUES(110000, 1100, 11, 'init');
INSERT INTO t_order_item_auto VALUES(110100, 1101, 11, 'init');
INSERT INTO t_config VALUES(1, 'init');
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-764609816
Hi @tristaZero .
I tested the code, some unexpected error happened as follows:
The most usage info is
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'item_id' not found in table 'I'.
```
java.sql.SQLException: Error while preparing statement [select o.*, i.* from t_order_calcite o, t_order_item_calcite_sharding i where o.order_id = ? and i.item_id = ?]
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:220)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:202)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:93)
at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)
at org.apache.shardingsphere.infra.optimize.execute.CalciteJDBCExecutor.execute(CalciteJDBCExecutor.java:85)
at org.apache.shardingsphere.infra.optimize.execute.CalciteJDBCExecutor.executeQuery(CalciteJDBCExecutor.java:68)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQueryByCalcite(ShardingSpherePreparedStatement.java:214)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery0(ShardingSpherePreparedStatement.java:200)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:178)
at org.apache.shardingsphere.driver.jdbc.core.statement.CalcitePrepareStatementTest.assertQueryWithCalciteInSingleAndShardingTable(CalcitePrepareStatementTest.java:61)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 100 to line 1, column 106: Column 'item_id' not found in table 'i'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:883)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:868)
at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5043)
at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:439)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6015)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6000)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:320)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:879)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6033)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:879)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6033)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50)
at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
at org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:5609)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4123)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3464)
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1067)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1041)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1016)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:724)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:567)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:208)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:642)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:508)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:478)
at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:213)
... 34 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'item_id' not found in table 'i'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:560)
... 73 more
Process finished with exit code 255
```
I added a sharding table named `t_order_item_calcite_sharding` in database1, and config the sharding rule in the yaml file.
`config-calcite.yaml` is as follows:
```
rules:
- !SHARDING
tables:
t_order_item_calcite_sharding:
actualDataNodes: jdbc_1.t_order_item_calcite_sharding_${0..1}
tableStrategy:
standard:
shardingColumn: item_id
shardingAlgorithmName: table_inline
shardingAlgorithms:
table_inline:
type: INLINE
props:
algorithm-expression: t_order_item_calcite_sharding_${item_id % 2}
```
I have debugged the code and taken a few screenshots of some of the execution results:
<img width="1357" alt="Screen Shot 2021-01-21 at 8 19 12 PM" src="https://user-images.githubusercontent.com/24718258/105350399-1245a980-5c26-11eb-82a8-64014bee2a3f.png">
I don't know what went wrong in my test, Is there anything wrong with the SQL statement？
`CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
`
`"select o.*, i.* from t_order_calcite o, t_order_item_calcite_sharding i where o.order_id = ? and i.item_id = ?"`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-764434396
Hi @lmhmhl ,
Would you like to take charge of this one?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lmhmhl commented on issue #9117: [SQL federation] Add unit test for single table joining sharding table
Posted by GitBox <gi...@apache.org>.
lmhmhl commented on issue #9117:
URL: https://github.com/apache/shardingsphere/issues/9117#issuecomment-764437426
> Hi @lmhmhl ,
>
> Would you like to take charge of this one?
I'd like to do this. Thx.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org