opened 01:27PM - 19 Mar 23 UTC
type: bug
in: kernel
## Bug Report
### Which version of ShardingSphere did you use?
master ce2934…fa
### 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
