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 2022/10/09 04:51:39 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #20087: The "INSERT INTO ... SELECT " syntax of openGauss has some problems.
peilinqian opened a new issue, #20087:
URL: https://github.com/apache/shardingsphere/issues/20087
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
we find java version: java8, full_version=1.8.0_282
ShardingSphere-5.1.3-SNAPSHOT
Commit ID: dirty-9dd0d3990c849d50c17c6dc7c92ec2d4ce0ad7e5
Commit Message: Implements openGauss version function by calcite (#19327)
Branch: 9dd0d3990c849d50c17c6dc7c92ec2d4ce0ad7e5
Build time: 2022-08-04T19:57:18+0800
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
The "insert * select" syntax of openGauss can be executed properly.
### Actual behavior
The "insert * select" syntax of openGauss has some problems.
```
order_db=> create table t_order (order_id bigint,user_id int,order_con text) ;
CREATE TABLE
order_db=> create table t_order_item (order_id bigint,user_id int,item_con text) ;
CREATE TABLE
order_db=> insert into t_order values(1,1,'order1'),(2,2,'order2'),(3,1,'order3'),(4,3,'order4');
INSERT 0 4
order_db=> insert into t_order_item values(1,1,'item2'),(2,2,'item2'),(3,1,'item1'),(4,3,'item2');
INSERT 0 4
order_db=> insert into t_order(user_id,order_con) select user_id,item_con from t_order_item ;--err
ERROR: INSERT INTO ... SELECT can not support applying keyGenerator to absent generateKeyColumn.
order_db=> insert into t_order(order_id,user_id,order_con) select order_id,user_id,item_con from t_order_item ;--err
ERROR: INSERT INTO ... SELECT can not support applying keyGenerator to absent generateKeyColumn.
order_db=> insert into t_order select * from t_order_item;
INSERT 0 4
order_db=> insert into t_order select * from t_order_item where user_id =1 and order_id=3;
INSERT 0 1
order_db=> insert into t_order select * from t_order_item where order_id=3;--err ds_0.t_order_item_1
ERROR: Insert statement does not support sharding table routing to multiple data nodes.
order_db=> insert into t_order select * from t_order_item where user_id =1;--err t_order_item_0,t_order_item_1
ERROR: Insert statement does not support sharding table routing to multiple data nodes.
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```
drop table t_order;
drop table t_order_item;
create table t_order (order_id bigint,user_id int,order_con text) ;
create table t_order_item (order_id bigint,user_id int,item_con text) ;
insert into t_order values(1,1,'order1'),(2,2,'order2'),(3,1,'order3'),(4,3,'order4');
insert into t_order_item values(1,1,'item2'),(2,2,'item2'),(3,1,'item1'),(4,3,'item2');
insert into t_order(user_id,order_con) select user_id,item_con from t_order_item ;--err
insert into t_order(order_id,user_id,order_con) select order_id,user_id,item_con from t_order_item ;--err
insert into t_order select * from t_order_item;
insert into t_order select * from t_order_item where user_id =1 and order_id=3;
insert into t_order select * from t_order_item where order_id=3;--err ds_0.t_order_item_1
insert into t_order select * from t_order_item where user_id =1;--err t_order_item_0,t_order_item_1
```
config
```
rules:
- !SHARDING
tables:
tb_one:
actualDataNodes: ds_0.tb_one
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
column: order_id
column: order_value
keyGeneratorName: snowflake
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_item_inline
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
allow-range-query-with-inline-sharding: true
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 2}
allow-range-query-with-inline-sharding: true
t_order_item_inline:
type: INLINE
props:
algorithm-expression: t_order_item_${order_id % 2}
allow-range-query-with-inline-sharding: true
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
```
### Example codes for reproduce this issue (such as a github link).
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #20087: The "INSERT INTO ... SELECT " syntax of openGauss has some problems.
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #20087:
URL: https://github.com/apache/shardingsphere/issues/20087#issuecomment-1272348881
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] closed issue #20087: The "INSERT INTO ... SELECT " syntax of openGauss has some problems.
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #20087: The "INSERT INTO ... SELECT " syntax of openGauss has some problems.
URL: https://github.com/apache/shardingsphere/issues/20087
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #20087: The "INSERT INTO ... SELECT " syntax of openGauss has some problems.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20087:
URL: https://github.com/apache/shardingsphere/issues/20087#issuecomment-1272455143
This issue has not been finished, so I will reopen it.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org