[GitHub] [shardingsphere] RaigorJiang opened a new issue, #24678: SQL rewriting exception in sharding scenario

["RaigorJiang (via GitHub)" <gi...@apache.org>]

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: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] Lijian500 commented on issue #24678: SQL rewriting exception in sharding scenario

["Lijian500 (via GitHub)" <gi...@apache.org>]

Lijian500 commented on issue #24678:
URL: https://github.com/apache/shardingsphere/issues/24678#issuecomment-1681541213

   I also encountered this problem, I expect to deal with it in that version, thank you


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] github-actions[bot] commented on issue #24678: SQL rewriting exception in sharding scenario

["github-actions[bot] (via GitHub)" <gi...@apache.org>]

github-actions[bot] commented on issue #24678:
URL: https://github.com/apache/shardingsphere/issues/24678#issuecomment-1741427278

   There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] RaigorJiang commented on issue #24678: SQL rewriting exception in sharding scenario

["RaigorJiang (via GitHub)" <gi...@apache.org>]

RaigorJiang commented on issue #24678:
URL: https://github.com/apache/shardingsphere/issues/24678#issuecomment-1475539884

   Hi @tuichenchuxin , could you please check it?


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org