SQLHint and HintManager behave inconsistently

RaigorJiang commented 2 hours ago

Bug Report

Which version of ShardingSphere did you use?

5.5.2-SNAPSHOT 53d9616d18b5de1cd5f8e3bec00a3ea81dc47d02

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

ShardingSphere-Proxy and ShardingSphere-JDBC

Expected behavior

SQLHint can transparently execute SQL, and HintManager can also achieve the same effect.

Actual behavior

In the sharding scenario, query a sharding table node on a specified storage unit. The query is successful when SQLHint is used, but when use HintManager to do same operation, the message "Table or view does not exist" is displayed.

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

Create database in proxy

CREATE DATABASE sharding_db;
USE sharding_db;

Register storage units

REGISTER STORAGE UNIT ds_study_dev_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_study_dev_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_study_dev_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_study_dev_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)
);

Create sharding table


CREATE SHARDING TABLE RULE t_membership_order (
DATANODES("ds_study_dev_${0}.t_membership_order_${0..3},ds_study_dev_${1}.t_membership_order_${4..7},ds_study_dev_${2}.t_membership_order_${8..11},ds_study_dev_${3}.t_membership_order_${12..15}"),
DATABASE_STRATEGY(
    TYPE="STANDARD",
    SHARDING_COLUMN=order_id,
    SHARDING_ALGORITHM(
        TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_study_dev_${(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_membership_order_${order_id % 16}","allow-range-query-with-inline-sharding"=true)))
     )
);

CREATE TABLE t_membership_order ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, order_id BIGINT(20) UNSIGNED NOT NULL COMMENT 'order id', PRIMARY KEY (id) );


4. Create a JDBC application and config ShardingSphere-JDBC in cluster mode
...
5. Execute SQL in JDBC with SQLHint
![img_v3_02ft_8de22466-7a0f-4651-a7a7-47e7a5fd5a5g](https://github.com/user-attachments/assets/ff3d3007-4c60-4ba9-aaf8-e8b603c7f383)

6. Execute SQL in JDBC with HintManager
![img_v3_02ft_f8fea5b7-b2e0-487a-9d44-ace66af9d03g](https://github.com/user-attachments/assets/b5b7af89-916b-4c83-9bb7-f2cad9ccc56d)

strongduanmu commented 2 hours ago

@RaigorJiang Thank you for your feedback. This is a bug for sql bind, HintManager also need to skip sql bind.

RaigorJiang commented 2 hours ago

@RaigorJiang Thank you for your feedback. This is a bug for sql bind, HintManager also need to skip sql bind.

@strongduanmu Thank you!

apache / shardingsphere