Table does not exist exception occurs during table routing

[RaigorJiang]

Bug Report

Which version of ShardingSphere did you use?

5.5.1-SNAPSHOT f205426

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

ShardingSphere-Proxy

Expected behavior

Sharding routing is correct

Actual behavior

Exception occurs

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. create database

CREATE DATABASE sharding_db;
USE sharding_db;

REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"=10)
), ds_1 (
    URL="jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"=10)
), ds_2 (
    URL="jdbc:mysql://127.0.0.1:3306/demo_ds_2?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"=10)
), ds_3 (
    URL="jdbc:mysql://127.0.0.1:3306/demo_ds_3?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"=10)
);

2. create sharding table

CREATE SHARDING TABLE RULE t_order (
    DATANODES("ds_${0}.t_order_${0..3},ds_${1}.t_order_${4..7},ds_${2}.t_order_${8..11},ds_${3}.t_order_${12..15}"),
    DATABASE_STRATEGY(
        TYPE="STANDARD",
        SHARDING_COLUMN=order_id,
        SHARDING_ALGORITHM(
            TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${(order_id % 16 / 4) as int}","allow-range-query-with-inline-sharding"=true)))
        ),
    TABLE_STRATEGY(
        TYPE="STANDARD",
        SHARDING_COLUMN=order_id,
        SHARDING_ALGORITHM(
            TYPE(NAME="inline",PROPERTIES("algorithm-expression"="t_order_${order_id % 16}","allow-range-query-with-inline-sharding"=true)))
         )
);


DROP TABLE IF EXISTS t_order;
CREATE TABLE `t_order` (
        `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        `order_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'order id',
        PRIMARY KEY (`id`)
);

3. execute preview or select

preview select * from t_order WHERE order_id IN (768017);
preview select * from t_order WHERE order_id IN (400037);
preview select * from t_order WHERE order_id IN (768017,400037);

[strongduanmu]

This configuration looks very special. It uses the same sharding field order_id to shard both the database and the table, and the names of the shard tables are different in each database.

To support this special usage, we can divide it into two steps: 1. Temporary solution, remove the ShardingRouteAlgorithmException check in StandardShardingStrategy to support this scenario; 2. Long-term solution, restore the ShardingRouteAlgorithmException check and support this usage in autoTables.