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 2022/12/29 09:12:09 UTC
[GitHub] [shardingsphere] strongduanmu opened a new issue, #23163: Unknown column exception occurs when I execute subquery select with mask, encrypt, sharding configuration
strongduanmu opened a new issue, #23163:
URL: https://github.com/apache/shardingsphere/issues/23163
## Bug Report
### Which version of ShardingSphere did you use?
[2a1163f](https://github.com/apache/shardingsphere/commit/2a1163fbda4e6b54cb2dddb229fddb3358d5bf60)
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
Execute `SELECT * FROM (SELECT * FROM t_user) temp;` successfully.
### Actual behavior
```sql
[INFO ] 2022-12-29 17:07:59.092 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * FROM (SELECT * FROM t_user) temp
[INFO ] 2022-12-29 17:07:59.092 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT `t_user`.`user_id`, `t_user`.`user_name_cipher` AS `user_name`, `t_user`.`password_cipher` AS `password`, `t_user`.`email_cipher` AS `email`, `t_user`.`telephone_cipher` AS `telephone`, `t_user`.`creation_date` FROM (SELECT `t_user`.`user_id`, `t_user`.`user_name_cipher`, `t_user`.`user_name_plain`, `t_user`.`password_cipher`, `t_user`.`email_cipher`, `t_user`.`telephone_cipher`, `t_user`.`telephone_plain`, `t_user`.`creation_date` FROM t_user) temp
[INFO ] 2022-12-29 17:07:59.092 [Connection-4-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT `t_user`.`user_id`, `t_user`.`user_name_cipher` AS `user_name`, `t_user`.`password_cipher` AS `password`, `t_user`.`email_cipher` AS `email`, `t_user`.`telephone_cipher` AS `telephone`, `t_user`.`creation_date` FROM (SELECT `t_user`.`user_id`, `t_user`.`user_name_cipher`, `t_user`.`user_name_plain`, `t_user`.`password_cipher`, `t_user`.`email_cipher`, `t_user`.`telephone_cipher`, `t_user`.`telephone_plain`, `t_user`.`creation_date` FROM t_user) temp
[ERROR] 2022-12-29 17:07:59.119 [Connection-4-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't_user.user_id' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:939)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:102)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyStatementExecutorCallback.execute(ProxyStatementExecutorCallback.java:42)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:75)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:68)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:45)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:90)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:69)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:131)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:116)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:75)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:227)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.doExecute(ProxySQLExecutor.java:184)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:149)
at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:176)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:92)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
Config.
```yaml
databaseName: mask_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !MASK
tables:
t_user:
columns:
password:
maskAlgorithm: md5_mask
email:
maskAlgorithm: mask_before_special_chars_mask
telephone:
maskAlgorithm: keep_first_n_last_m_mask
maskAlgorithms:
md5_mask:
type: MD5
mask_before_special_chars_mask:
type: MASK_BEFORE_SPECIAL_CHARS
props:
special-chars: '@'
replace-char: '*'
keep_first_n_last_m_mask:
type: KEEP_FIRST_N_LAST_M
props:
first-n: 3
last-m: 4
replace-char: '*'
- !ENCRYPT
encryptors:
aes_encryptor:
type: AES
props:
aes-key-value: 123456abc
tables:
t_user:
columns:
user_name:
plainColumn: user_name_plain
cipherColumn: user_name_cipher
encryptorName: aes_encryptor
password:
cipherColumn: password_cipher
encryptorName: aes_encryptor
email:
cipherColumn: email_cipher
encryptorName: aes_encryptor
telephone:
plainColumn: telephone_plain
cipherColumn: telephone_cipher
encryptorName: aes_encryptor
- !SHARDING
tables:
t_user:
actualDataNodes: ds_${0..1}.t_user
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
```
And then execute following sql:
```sql
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (user_id INT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, telephone CHAR(50) NOT NULL, creation_date DATE NOT NULL);
INSERT INTO t_user(user_id, user_name, password, email, telephone, creation_date) values(10, 'zhangsan', '111111', 'zhangsan@gmail.com', '12345678900', '2017-08-08'),
(11, 'lisi', '222222', 'lisi@gmail.com', '12345678901', '2017-08-08'),
(12, 'wangwu', '333333', 'wangwu@gmail.com', '12345678902', '2017-08-08'),
(13, 'zhaoliu', '444444', 'zhaoliu@gmail.com', '12345678903', '2017-08-08'),
(14, 'zhuqi', '555555', 'zhuqi@gmail.com', '12345678904', '2017-08-08'),
(15, 'liba', '666666', 'liba@gmail.com', '12345678905', '2017-08-08'),
(16, 'wangjiu', '777777', 'wangjiu@gmail.com', '12345678906', '2017-08-08'),
(17, 'zhuda', '888888', 'zhuda@gmail.com', '12345678907', '2017-08-08'),
(18, 'suner', '999999', 'suner@gmail.com', '12345678908', '2017-08-08'),
(19, 'zhousan', '123456', 'zhousan@gmail.com', '12345678909', '2017-08-08'),
(20, 'tom', '234567', 'tom@gmail.com', '12345678910', '2017-08-08'),
(21, 'kobe', '345678', 'kobe@gmail.com', '12345678911', '2017-08-08'),
(22, 'jerry', '456789', 'jerry@gmail.com', '12345678912', '2017-08-08'),
(23, 'james', '567890', 'james@gmail.com', '12345678913', '2017-08-08'),
(24, 'wade', '012345', 'wade@gmail.com', '12345678914', '2017-08-08'),
(25, 'rose', '000000', 'rose@gmail.com', '12345678915', '2017-08-08'),
(26, 'bosh', '111222', 'bosh@gmail.com', '12345678916', '2017-08-08'),
(27, 'jack', '222333', 'jack@gmail.com', '12345678917', '2017-08-08'),
(28, 'jordan', '333444', 'jordan@gmail.com', '12345678918', '2017-08-08'),
(29, 'julie', '444555', 'julie@gmail.com', '12345678919', '2017-08-08');
```
And then execute `SELECT * FROM (SELECT * FROM t_user) temp;`
### Example codes for reproduce this issue (such as a github link).
--
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] taojintianxia closed issue #23163: Unknown column exception occurs when I execute subquery select with mask, encrypt, sharding configuration
Posted by GitBox <gi...@apache.org>.
taojintianxia closed issue #23163: Unknown column exception occurs when I execute subquery select with mask, encrypt, sharding configuration
URL: https://github.com/apache/shardingsphere/issues/23163
--
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