You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "strongduanmu (via GitHub)" <gi...@apache.org> on 2023/06/03 05:15:52 UTC
[GitHub] [shardingsphere] strongduanmu opened a new issue, #26028: Column index out of range occurs when I exeucte subquery with encrypt feature
strongduanmu opened a new issue, #26028:
URL: https://github.com/apache/shardingsphere/issues/26028
## Bug Report
### Which version of ShardingSphere did you use?
[f93b777](https://github.com/apache/shardingsphere/commit/f93b777b8efa134cf700c680031cca2c9313a150)
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
Execute `SELECT * FROM (SELECT id, user_id, order_id, (SELECT password FROM t_user LIMIT 1) password FROM t_encrypt) temp;` successfully.
### Actual behavior
```
[ERROR] 2023-06-03 13:05:42.405 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Column index out of range.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetMetaData.getField(ResultSetMetaData.java:395)
at com.mysql.jdbc.ResultSetMetaData.getColumnName(ResultSetMetaData.java:243)
at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.metadata.JDBCQueryResultMetaData.getColumnName(JDBCQueryResultMetaData.java:47)
at org.apache.shardingsphere.proxy.backend.response.header.query.QueryHeaderBuilderEngine.getColumnName(QueryHeaderBuilderEngine.java:79)
at org.apache.shardingsphere.proxy.backend.response.header.query.QueryHeaderBuilderEngine.build(QueryHeaderBuilderEngine.java:67)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.createQueryHeader(DatabaseConnector.java:589)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.createQueryHeaders(DatabaseConnector.java:565)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.processExecuteQuery(DatabaseConnector.java:555)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.doExecute(DatabaseConnector.java:323)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.doExecute(DatabaseConnector.java:296)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.execute(DatabaseConnector.java:243)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:92)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:114)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:109)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
[INFO ] 2023-06-03 13:05:56.238 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * FROM (SELECT id, user_id, order_id, (SELECT password FROM t_user LIMIT 1) password FROM t_encrypt) temp
[INFO ] 2023-06-03 13:05:56.238 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT temp.`id`, temp.`user_cipher` AS `user_id`, temp.`order_cipher` AS `order_id` FROM (SELECT id, user_cipher, user_plain, order_cipher, (SELECT password FROM t_user LIMIT 1) password FROM t_encrypt) temp
[ERROR] 2023-06-03 13:12:24.560 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Column index out of range.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetMetaData.getField(ResultSetMetaData.java:395)
at com.mysql.jdbc.ResultSetMetaData.getColumnName(ResultSetMetaData.java:243)
at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.metadata.JDBCQueryResultMetaData.getColumnName(JDBCQueryResultMetaData.java:47)
at org.apache.shardingsphere.proxy.backend.response.header.query.QueryHeaderBuilderEngine.getColumnName(QueryHeaderBuilderEngine.java:79)
at org.apache.shardingsphere.proxy.backend.response.header.query.QueryHeaderBuilderEngine.build(QueryHeaderBuilderEngine.java:67)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.createQueryHeader(DatabaseConnector.java:589)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.createQueryHeaders(DatabaseConnector.java:565)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.processExecuteQuery(DatabaseConnector.java:555)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.doExecute(DatabaseConnector.java:323)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.doExecute(DatabaseConnector.java:296)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.execute(DatabaseConnector.java:243)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:92)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:114)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:109)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
[INFO ] 2023-06-03 13:12:36.620 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * FROM (SELECT id, user_id, order_id, (SELECT password FROM t_user LIMIT 1) password FROM t_encrypt) temp
[INFO ] 2023-06-03 13:12:36.620 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT temp.`id`, temp.`user_cipher` AS `user_id`, temp.`order_cipher` AS `order_id` FROM (SELECT id, user_cipher, user_plain, order_cipher, (SELECT password FROM t_user LIMIT 1) password FROM t_encrypt) temp
[ERROR] 2023-06-03 13:13:24.593 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Column index out of range.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetMetaData.getField(ResultSetMetaData.java:395)
at com.mysql.jdbc.ResultSetMetaData.getColumnName(ResultSetMetaData.java:243)
at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.metadata.JDBCQueryResultMetaData.getColumnName(JDBCQueryResultMetaData.java:47)
at org.apache.shardingsphere.proxy.backend.response.header.query.QueryHeaderBuilderEngine.getColumnName(QueryHeaderBuilderEngine.java:79)
at org.apache.shardingsphere.proxy.backend.response.header.query.QueryHeaderBuilderEngine.build(QueryHeaderBuilderEngine.java:67)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.createQueryHeader(DatabaseConnector.java:589)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.createQueryHeaders(DatabaseConnector.java:565)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.processExecuteQuery(DatabaseConnector.java:555)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.doExecute(DatabaseConnector.java:323)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.doExecute(DatabaseConnector.java:296)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.execute(DatabaseConnector.java:243)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:92)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:114)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:109)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
```
### Reason analyze (If you can)
Wrong sql rewrite result:
```
[INFO ] 2023-06-03 13:05:56.238 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * FROM (SELECT id, user_id, order_id, (SELECT password FROM t_user LIMIT 1) password FROM t_encrypt) temp
[INFO ] 2023-06-03 13:05:56.238 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT temp.`id`, temp.`user_cipher` AS `user_id`, temp.`order_cipher` AS `order_id` FROM (SELECT id, user_cipher, user_plain, order_cipher, (SELECT password FROM t_user LIMIT 1) password FROM t_encrypt) temp
```
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### 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] strongduanmu closed issue #26028: Column index out of range occurs when I exeucte subquery with encrypt feature
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu closed issue #26028: Column index out of range occurs when I exeucte subquery with encrypt feature
URL: https://github.com/apache/shardingsphere/issues/26028
--
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 #26028: Column index out of range occurs when I exeucte subquery with encrypt feature
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #26028:
URL: https://github.com/apache/shardingsphere/issues/26028#issuecomment-1574742325
When subquery contains encrypt column, and not config subquery alias, it will throw exception.
```
[INFO ] 2023-06-03 15:18:19.429 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * FROM (SELECT id, user_id, order_id, (SELECT user_name FROM t_user LIMIT 1) FROM t_encrypt) temp
[INFO ] 2023-06-03 15:18:19.430 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT temp.`id`, temp.`user_cipher` AS `user_id`, temp.`order_cipher` AS `order_id`, (SELECT user_name FROM t_user LIMIT 1) AS `(SELECT user_name FROM t_user LIMIT 1)` FROM (SELECT id, user_cipher, user_plain, order_cipher, (SELECT user_name_cipher AS user_name FROM t_user LIMIT 1) FROM t_encrypt) temp
```
--
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 #26028: Column index out of range occurs when I exeucte subquery with encrypt feature
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #26028:
URL: https://github.com/apache/shardingsphere/issues/26028#issuecomment-1581834603
Fixed by https://github.com/apache/shardingsphere/pull/26104
--
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 #26028: Column index out of range occurs when I exeucte subquery with encrypt feature
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #26028:
URL: https://github.com/apache/shardingsphere/issues/26028#issuecomment-1574700244
Test result:
```
mysql> SELECT * FROM (SELECT id, user_id, order_id, (SELECT password FROM t_user LIMIT 1) FROM t_encrypt) temp;
+----+---------+----------------------------------+---------------------------------------+
| id | user_id | order_id | (SELECT password FROM t_user LIMIT 1) |
+----+---------+----------------------------------+---------------------------------------+
| 1 | 123 | 289dff07669d7a23de0ef88d2f7129e7 | sdasdas |
| 2 | 222 | 310dcbbf4cce62f762a2aaa148d556bd | sdasdas |
+----+---------+----------------------------------+---------------------------------------+
2 rows in set (1 min 42.43 sec)
mysql> SELECT * FROM (SELECT id, user_id, order_id, (SELECT password FROM t_user LIMIT 1) password FROM t_encrypt) temp;
+----+---------+----------------------------------+----------+
| id | user_id | order_id | password |
+----+---------+----------------------------------+----------+
| 1 | 123 | 289dff07669d7a23de0ef88d2f7129e7 | sdasdas |
| 2 | 222 | 310dcbbf4cce62f762a2aaa148d556bd | sdasdas |
+----+---------+----------------------------------+----------+
2 rows in set (5.28 sec)
```
--
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