mingqing6364 opened a new issue #11520: URL: https://github.com/apache/shardingsphere/issues/11520
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? Apache ShardingSphere - Version: 5.0.0-beta ( Release Date: Jun 19th, 2021 ) https://www.apache.org/dyn/closer.cgi/shardingsphere/5.0.0-beta/apache-shardingsphere-5.0.0-beta-shardingsphere-proxy-bin.tar.gz ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior return all columns ### Actual behavior just return one column ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. ``` mysql> SELECT -> s.*, s.v2021 - s.v2020 v_add -> FROM -> ( -> SELECT -> a.id, ANY_VALUE(a.str) str, -> SUM(IF(b.y = 2020, b.v, 0)) v2020, -> SUM(IF(b.y = 2021, b.v, 0)) v2021 -> FROM -> a INNER JOIN b ON a.id = b.aid -> GROUP BY -> a.id -> ) s; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.92 sec) mysql> SELECT -> s.id, s.str, s.v2020, s.v2021, -> s.v2021 - s.v2020 v_add -> FROM -> ( -> SELECT -> a.id, ANY_VALUE(a.str) str, -> SUM(IF(b.y = 2020, b.v, 0)) v2020, -> SUM(IF(b.y = 2021, b.v, 0)) v2021 -> FROM -> a INNER JOIN b ON a.id = b.aid -> GROUP BY -> a.id -> ) s; ERROR 1054 (42S22): Unknown column 's.str_encrypt' in 'field list' mysql> SELECT -> s.id, s.str1, s.v2020, s.v2021, -> s.v2021 - s.v2020 v_add -> FROM -> ( -> SELECT -> a.id, ANY_VALUE(a.str) str1, -> SUM(IF(b.y = 2020, b.v, 0)) v2020, -> SUM(IF(b.y = 2021, b.v, 0)) v2021 -> FROM -> a INNER JOIN b ON a.id = b.aid -> GROUP BY -> a.id -> ) s; +----+------+-------+-------+-------+ | id | str1 | v2020 | v2021 | v_add | +----+------+-------+-------+-------+ | 1 | a | 2 | 4 | 2 | | 2 | b | 2 | 4 | 2 | | 3 | c | 2 | 4 | 2 | +----+------+-------+-------+-------+ 3 rows in set (0.00 sec) mysql> SELECT -> s.*, -> s.v2021 - s.v2020 v_add -> FROM -> ( -> SELECT -> a.id, ANY_VALUE(a.str) str1, -> SUM(IF(b.y = 2020, b.v, 0)) v2020, -> SUM(IF(b.y = 2021, b.v, 0)) v2021 -> FROM -> a INNER JOIN b ON a.id = b.aid -> GROUP BY -> a.id -> ) s; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.01 sec) ``` By the way, MySQL variables is not working. ``` mysql> SELECT -> a.id, a.str, @v2020:=b.v2020 v2020, @v2021:=b.v2021 v2021, -> @v2021-@v2020 v_add, b.*, a.*, 123 v -> FROM -> a INNER JOIN ( -> SELECT -> b.aid, -> SUM(IF(b.y = 2020, b.v, 0)) v2020, -> SUM(IF(b.y = 2021, b.v, 0)) v2021 -> FROM -> b -> GROUP BY -> b.aid -> ) b ON a.id = b.aid, -> (SELECT @v2020:=0, @v2021:=0) v; ERROR 1235 (42000): This version of ShardingProxy doesn't yet support this SQL. 'You have an error in your SQL syntax' ``` ### Example codes for reproduce this issue (such as a github link). MySQL DDL： ``` CREATE TABLE a ( id int UNSIGNED NOT NULL AUTO_INCREMENT, str varchar(20) NOT NULL, str_encrypt varchar(100) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE b ( id int UNSIGNED NOT NULL AUTO_INCREMENT, aid int UNSIGNED NOT NULL, y int UNSIGNED NOT NULL, v int UNSIGNED NOT NULL, PRIMARY KEY (id) ); INSERT INTO a (str) VALUES ('a'), ('b'), ('c'); INSERT INTO b (aid, y, v) VALUES (1, 2021, 1), (1, 2020, 2), (1, 2021, 3), (2, 2021, 1), (2, 2020, 2), (2, 2021, 3), (3, 2021, 1), (3, 2020, 2), (3, 2021, 3); ``` sharding-proxy config ``` schemaName: encrypt_db dataSource: url: jdbc:mysql://127.0.0.1:3306/test_db?serverTimezone=UTC&useSSL=false username: root password: 123 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 maintenanceIntervalMilliseconds: 30000 rules: - !ENCRYPT encryptors: md5_encryptor: type: MD5 tables: a: columns: str: plainColumn: str cipherColumn: str_encrypt encryptorName: md5_encryptor ``` -- 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]
