SQL rewriting exception in sharding scenario

[RaigorJiang]

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.

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

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

[RaigorJiang]

Hi @tuichenchuxin , could you please check it?

[Lijian500]

I also encountered this problem, which version will handle this bug, thank you