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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
