RaigorJiang opened a new issue, #24678:
URL: https://github.com/apache/shardingsphere/issues/24678
## Bug Report
### Which version of ShardingSphere did you use?
master ce2934fa
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
SQL rewritten correctly
### Actual behavior
SQL rewriting error, causing `Unknown column` exception
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
```sql
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",
USER="xxx",
PASSWORD="xxx",
PROPERTIES("maximumPoolSize"=10)
),ds_1 (
URL="jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false",
USER="xxx",
PASSWORD="xxx",
PROPERTIES("maximumPoolSize"=10)
);
CREATE SHARDING TABLE RULE t_order (
STORAGE_UNITS(ds_0,ds_1),
SHARDING_COLUMN=order_id,TYPE(NAME=MOD,PROPERTIES("sharding-count"=4))
);
DROP TABLE IF EXISTS t_order;
CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`order_id`)
);
select count (distinct case user_id when 2 then 1 else 0 end) as order_count
from t_order;
```
- Error
```sql
mysql> select count (distinct case user_id when 2 then 1 else 0 end) as
order_count from t_order;
ERROR 1054 (42S22): Unknown column 'caseuser_idwhen2then1else0end' in 'field
list'
```
- Log
```
Logic SQL: select count (distinct case user_id when 2 then 1 else 0 end) as
order_count from t_order
Actual SQL: ds_0 ::: select DISTINCT caseuser_idwhen2then1else0end as
order_count from t_order_0 UNION ALL select DISTINCT
caseuser_idwhen2then1else0end as order_count from t_order_2
Actual SQL: ds_1 ::: select DISTINCT caseuser_idwhen2then1else0end as
order_count from t_order_1 UNION ALL select DISTINCT
caseuser_idwhen2then1else0end as order_count from t_order_3
```
### From
https://community.sphere-ex.com/t/topic/1342
--
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]
