Major bugs！dynamic actual-data-nodes in join query is invalid ！

[StarHuzy]

Bug Report

I used a single database sharding tables use local transaction mode
I have configured some tables

• t_member
• t_order
• t_order_time
• t_product

What I designed is Automatically split tableStrategy by tenant_id
this is my configuration

1. I customized the allocation calculation class

@Component
@Slf4j
public class DataShardingAlgorithm implements StandardShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
        return collection;
    }

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        StringBuilder resultTableName = new StringBuilder();
        String logicTableName = preciseShardingValue.getLogicTableName();
        // 拼接的tenantId，格式为 表名_{tenant_id}
        resultTableName.append(logicTableName).append("_").append(preciseShardingValue.getValue());
        String newTableName = resultTableName.toString().toLowerCase();
        if (!collection.contains(newTableName)) {
            // 动态新增节点
            ShardingAlgorithmTool.copyTable(logicTableName,newTableName);
            collection.add(newTableName);
        }
        System.out.println("collection = " + collection);
        return newTableName;
    }
    @Override
    public String getType() {
        return null;
    }
    @Override
    public void init(Properties properties) {
    }
}

2. sharding.yaml

dataSources:
  testApp:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/app_test
    username: root
    password: 123456
rules:
  - !TRANSACTION
    defaultType: XA
    providerType: Atomikos
  - !SHARDING
    tables:
      t_member: # 分表，逻辑表名
        # 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
        actualDataNodes: testApp.t_member
        tableStrategy: # 配置分表策略
          standard: # 用于单分片键的标准分片场景
            shardingColumn: tenant_id
            shardingAlgorithmName: real-data-inline
      t_product: # 分表，逻辑表名
        # 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
        actualDataNodes: testApp.t_product
        tableStrategy: # 配置分表策略
          standard: # 用于单分片键的标准分片场景
            shardingColumn: tenant_id
            shardingAlgorithmName: real-data-inline
      t_order: # 分表，逻辑表名
        # 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
        actualDataNodes: testApp.t_order
        tableStrategy: # 配置分表策略
          standard: # 用于单分片键的标准分片场景
            shardingColumn: tenant_id
            shardingAlgorithmName: real-data-inline
      t_order_item: # 分表，逻辑表名
        # 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
        actualDataNodes: testApp.t_order_item
        tableStrategy: # 配置分表策略
          standard: # 用于单分片键的标准分片场景
            shardingColumn: tenant_id
            shardingAlgorithmName: real-data-inline
    # 分片算法配置
    shardingAlgorithms:
      real-data-inline: # 分片算法名称
        type: CLASS_BASED #自定义策略
        props:
          strategy: standard
          # 包名+类名
          algorithmClassName: com.hcyl.cloud.test.utils.DataShardingAlgorithm
    bindingTables:
      - t_member,t_product,t_order,t_order_item
props:
  sql-show: true
mode:
  type: Standalone

Which version of ShardingSphere did you use?

shardingsphere-jdbc 5.3.2 + JDK 17 + spring-boot 2.7.14

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

1. I will add, delete, modify, and check a single table
2. I will JOIN QUERY with two tables

Actual behavior

1. SQL execution of a single table without any exceptions
   

actualDataNodes Automatically loaded

2. I will JOIN QUERY with two tables with An exception has occurred

I query tenant_id = 2
By viewing the print below ，actualDataNodeshas been dynamically loaded

collection = [t_order_item, t_order_item_2]
2023-10-09 10:52:30.803 ERROR 11916 --- [io-9500-exec-10] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: 
### Error querying database.  Cause: java.sql.SQLException: Actual table `testApp.t_order_item_2` is not in table rule configuration.
### The error may exist in file [E:\hc-cloud\hc_cloud_app_test\target\classes\mapper\OrderMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT o.order_no, SUM(i.price * i.count) AS amount         FROM t_order o JOIN t_order_item i ON o.order_no = i.order_no         where o.tenant_id = ? and i.tenant_id = ?         GROUP BY o.order_no                       limit ?,?
### Cause: java.sql.SQLException: Actual table `testApp.t_order_item_2` is not in table rule configuration.
; Actual table `testApp.t_order_item_2` is not in table rule configuration.; nested exception is java.sql.SQLException: Actual table `testApp.t_order_item_2` is not in table rule configuration.] with root cause

java.sql.SQLException: Actual table `testApp.t_order_item_2` is not in table rule configuration.
	at org.apache.shardingsphere.infra.util.exception.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:62) ~[shardingsphere-infra-util-5.3.2.jar:5.3.2]
	at org.apache.shardingsphere.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:51) ~[shardingsphere-dialect-exception-core-5.3.2.jar:5.3.2]
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:448) ~[shardingsphere-jdbc-core-5.3.2.jar:5.3.2]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.10.jar:3.5.10]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring

But if I start configuring it

After execution, there will be no exceptions
But this actualDataNodes is the same as the previous method

Reason analyze (If you can)

[linghengqian]

• I believe this is a duplicate of what you asked dynamic actual-data-nodes refresh in join query is invalid #28704 . Looking forward to your further reply in another issue. Refer to dynamic actual-data-nodes refresh in join query is invalid #28704 (comment) .

[github-actions]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[github-actions]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[linghengqian]

• There is no response from the creator of the issue, so I think there is no point in continuing to open this issue.