The "INSERT INTO ... SELECT " syntax of openGauss has some problems.

[peilinqian]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

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: 9dd0d39
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).

[github-actions]

Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.

[strongduanmu]

This issue has not been finished, so I will reopen it.