apache / apache/shardingsphere

SQL rewriting exception in sharding scenario

Open
#24,678 2 comments 0 reactions 1 assignee Claimed by @tuichenchuxin View on GitHub
in: kernel type: bug
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
9h 55m
Merged PRs (30d)
311

Description

## 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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.