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', '[email protected]', '12345678900', '2017-08-08'), (11, 'lisi', '222222', '[email protected]', '12345678901', '2017-08-08'), (12, 'wangwu', '333333', '[email protected]', '12345678902', '2017-08-08'), (13, 'zhaoliu', '444444', '[email protected]', '12345678903', '2017-08-08'), (14, 'zhuqi', '555555', '[email protected]', '12345678904', '2017-08-08'), (15, 'liba', '666666', '[email protected]', '12345678905', '2017-08-08'), (16, 'wangjiu', '777777', '[email protected]', '12345678906', '2017-08-08'), (17, 'zhuda', '888888', '[email protected]', '12345678907', '2017-08-08'), (18, 'suner', '999999', '[email protected]', '12345678908', '2017-08-08'), (19, 'zhousan', '123456', '[email protected]', '12345678909', '2017-08-08'), (20, 'tom', '234567', '[email protected]', '12345678910', '2017-08-08'), (21, 'kobe', '345678', '[email protected]', '12345678911', '2017-08-08'), (22, 'jerry', '456789', '[email protected]', '12345678912', '2017-08-08'), (23, 'james', '567890', '[email protected]', '12345678913', '2017-08-08'), (24, 'wade', '012345', '[email protected]', '12345678914', '2017-08-08'), (25, 'rose', '000000', '[email protected]', '12345678915', '2017-08-08'), (26, 'bosh', '111222', '[email protected]', '12345678916', '2017-08-08'), (27, 'jack', '222333', '[email protected]', '12345678917', '2017-08-08'), (28, 'jordan', '333444', '[email protected]', '12345678918', '2017-08-08'), (29, 'julie', '444555', '[email protected]', '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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
