You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/07/27 02:02:24 UTC
[GitHub] [shardingsphere] mingqing6364 opened a new issue #11520: when sql from a sub-query and select *, can't return all columns
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz closed issue #11520: when sql from a sub-query and select *, can't return all columns
Posted by GitBox <gi...@apache.org>.
sandynz closed issue #11520:
URL: https://github.com/apache/shardingsphere/issues/11520
--
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] strongduanmu commented on issue #11520: when sql from a sub-query and select *, can't return all columns
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #11520:
URL: https://github.com/apache/shardingsphere/issues/11520#issuecomment-887154047
@mingqing6364 Thank you for your feedback, can you try the master branch? I think this issue has been fixed in master branch.
--
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] sandynz commented on issue #11520: when sql from a sub-query and select *, can't return all columns
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #11520:
URL: https://github.com/apache/shardingsphere/issues/11520#issuecomment-943239490
I'll close it now since no response anymore. Welcome to submit new issue if problem still exists.
--
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