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/30 11:07:24 UTC
[GitHub] [shardingsphere] TeslaCN opened a new issue #11589: SQL in BenchmarkSQL was not rewrote in subquery
TeslaCN opened a new issue #11589:
URL: https://github.com/apache/shardingsphere/issues/11589
## Bug Report
### Which version of ShardingSphere did you use?
master
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
Proxy
### Expected behavior
Rewrite logic table `bmsql_oorder` to actual table.
### Actual behavior
The logic table `bmsql_oorder` in subquery was not rewrote.
```
[INFO ] 2021-07-30 18:59:15.271 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT o_id, o_entry_d, o_carrier_id FROM bmsql_oorder WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? AND o_id = ( SELECT max(o_id) FROM bmsql_oorder WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? )
[INFO ] 2021-07-30 18:59:15.271 [Connection-2-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2021-07-30 18:59:15.271 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT o_id, o_entry_d, o_carrier_id FROM bmsql_oorder_0 WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? AND o_id = ( SELECT max(o_id) FROM bmsql_oorder WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? ) ::: [24, 1, 400, 24, 1, 400]
```
### Reason analyze (If you can)
### 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).
```yaml
tables:
bmsql_oorder:
actualDataNodes: ds_${0..3}.bmsql_oorder_${0..3}
databaseStrategy:
standard:
shardingColumn: o_w_id
shardingAlgorithmName: bmsql_oorder_database_inline
tableStrategy:
standard:
shardingColumn: o_c_id
shardingAlgorithmName: bmsql_oorder_table_inline
shardingAlgorithms:
bmsql_oorder_database_inline:
type: INLINE
props:
algorithm-expression: ds_${o_w_id & 3}
bmsql_oorder_table_inline:
type: INLINE
props:
algorithm-expression: bmsql_oorder_${o_c_id & 3}
```
--
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] TeslaCN commented on issue #11589: SQL in BenchmarkSQL was not rewrote in subquery
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #11589:
URL: https://github.com/apache/shardingsphere/issues/11589#issuecomment-890965332
I tried fixing this issue by the following commit
https://github.com/TeslaCN/shardingsphere/commit/9d628919ce6c4093b15fb0e708807154242fe9df
If the subquery is recognized correctly, the query will be executed by calcite and something like #10760 will occur. Even if the calcite's issue was fixed, the cost of this query will be increased.
--
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] TeslaCN commented on issue #11589: SQL in BenchmarkSQL was not rewrote in subquery
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #11589:
URL: https://github.com/apache/shardingsphere/issues/11589#issuecomment-890918041
I followed the code and found the subquery was not identified.
--
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] TeslaCN commented on issue #11589: SQL in BenchmarkSQL was not rewrote in subquery
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #11589:
URL: https://github.com/apache/shardingsphere/issues/11589#issuecomment-890683720
Proxy log
```
[ERROR] 2021-08-02 11:23:39.897 [Connection-2-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.postgresql.util.PSQLException: ERROR: relation "bmsql_oorder" does not exist
Position: 180
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:41)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:73)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:66)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:44)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:87)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:81)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:71)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:184)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:137)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:126)
at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.doExecute(ProxyLockEngine.java:103)
at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.execute(ProxyLockEngine.java:81)
at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:125)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.PostgreSQLPortal.execute(PostgreSQLPortal.java:90)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:60)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.execute.PostgreSQLComExecuteExecutor.execute(PostgreSQLComExecuteExecutor.java:56)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:98)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:72)
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)
```
--
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 #11589: SQL in BenchmarkSQL was not rewrote in subquery
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #11589:
URL: https://github.com/apache/shardingsphere/issues/11589#issuecomment-890659914
@TeslaCN Hi, how can I reproduce this problem?
--
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] TeslaCN commented on issue #11589: SQL in BenchmarkSQL was not rewrote in subquery
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #11589:
URL: https://github.com/apache/shardingsphere/issues/11589#issuecomment-920823994
I try the BenchmarkSQL again, it worked!
```
[INFO ] 2021-09-16 19:33:21.723 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT o_id, o_entry_d, o_carrier_id FROM bmsql_oorder WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? AND o_id = ( SELECT max(o_id) FROM bmsql_oorder WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? )
[INFO ] 2021-09-16 19:33:21.723 [Connection-2-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2021-09-16 19:33:21.723 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_2 ::: SELECT o_id, o_entry_d, o_carrier_id FROM bmsql_oorder_2 WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? AND o_id = ( SELECT max(o_id) FROM bmsql_oorder_2 WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? ) ::: [6, 1, 2286, 6, 1, 2286]
```
--
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] tristaZero closed issue #11589: SQL in BenchmarkSQL was not rewrote in subquery
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #11589:
URL: https://github.com/apache/shardingsphere/issues/11589
--
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