peilinqian opened a new issue, #22569: URL: https://github.com/apache/shardingsphere/issues/22569
### Which version of ShardingSphere did you use? we find java version: java8, full_version=1.8.0_342, full_path=/home/peilq_sharding/bisheng-jdk1.8.0_342//bin/java ShardingSphere-5.2.2-SNAPSHOT Commit ID: dirty-753c0cee8ee6fd3db00536da55b64bc5198a3758 Commit Message: Optimize sqlFederationExecutor init logic when sqlFederationType modify dynamically (https://github.com/apache/shardingsphere/pull/22209) Branch: https://github.com/apache/shardingsphere/commit/753c0cee8ee6fd3db00536da55b64bc5198a3758 Build time: 2022-11-19T10:18:41+0800 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior In the federation scenario, after creating a view, and select the view, the result is "ERROR: relation "select_view" does not exist on dn_6001_6002_6003". ### Actual behavior In the federation scenario, after creating a view, and select the view, the result is correct . ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. step 3.1 excute with error ``` -- step1：all create view select_view as select all * from t_order where order_id>2800; select * from select_view; drop view select_view; -- step2.1：distinct create view select_view as select distinct(user_id) from t_order order by user_id; select * from select_view; drop view select_view; -- step2.2：distinct on create view select_view as select distinct on (user_id) * from t_order order by user_id,order_id; select * from select_view; drop view select_view; -- step3.1：limit create view select_view as select o.order_id,o.user_id,status,remark,merchant_name from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10; select * from select_view; drop view select_view; ``` ``` [INFO ] 2022-12-01 18:45:04.377 [Connection-305-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussDropViewStatement(super=DropViewStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), views=[SimpleTableSegment(tableName=TableNameSegment(startIndex=10, stopIndex=20, identifier=IdentifierValue(value=select_view, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)])) [INFO ] 2022-12-01 18:45:04.377 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: drop view select_view [INFO ] 2022-12-01 18:45:04.392 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Logic SQL: create view select_view as select o.order_id,o.user_id,status,remark,merchant_name from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10 [INFO ] 2022-12-01 18:45:04.392 [Connection-305-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussCreateViewStatement(super=CreateViewStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), view=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=22, identifier=IdentifierValue(value=select_view, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), viewDefinition=select o.order_id,o.user_id,status,remark,merchant_name from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10, select=OpenGaussSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=34, stopIndex=81, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=34, stopIndex=43, identifier=IdentifierValue(value=order_id, quoteChar acter=NONE), owner=Optional[OwnerSegment(startIndex=34, stopIndex=34, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)]), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=45, stopIndex=53, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=45, stopIndex=45, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)]), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=55, stopIndex=60, identifier=IdentifierValue(value=status, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=62, stopIndex=67, identifier=IdentifierValue(value=remark, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=69, stopIndex=81, identifier=IdentifierValue(value=merchant_name, quoteCharacter=NONE), owner=Opti onal.empty), alias=Optional.empty)], distinctRow=false), from=JoinTableSegment(startIndex=88, stopIndex=98, alias=Optional.empty, left=SimpleTableSegment(tableName=TableNameSegment(startIndex=88, stopIndex=98, identifier=IdentifierValue(value=t_new_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[o]), joinType=INNER, right=SimpleTableSegment(tableName=TableNameSegment(startIndex=113, stopIndex=122, identifier=IdentifierValue(value=t_merchant, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[m]), condition=null, using=[ColumnSegment(startIndex=132, stopIndex=142, identifier=IdentifierValue(value=merchant_id, quoteCharacter=NONE), owner=Optional.empty)]), where=Optional[WhereSegment(startIndex=145, stopIndex=183, expr=BinaryOperationExpression(startIndex=151, stopIndex=183, left=BinaryOperationExpression(startIndex=151, stopIndex=164, left=ColumnSegment(startIndex=151, stopIndex=159, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Opt ional[OwnerSegment(startIndex=151, stopIndex=151, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=163, stopIndex=164, literals=10), operator=>, text=o.user_id > 10), right=BinaryOperationExpression(startIndex=170, stopIndex=183, left=ColumnSegment(startIndex=170, stopIndex=178, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=170, stopIndex=170, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=182, stopIndex=183, literals=15), operator=<, text=o.user_id < 15), operator=and, text=o.user_id > 10 and o.user_id < 15))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional[OrderBySegment(startIndex=185, stopIndex=206, orderByItems=[ColumnOrderByItemSegment(super=TextOrderByItemSegment(), column=ColumnSegment(startIndex=194, stopIndex=203, identifier=IdentifierValue(value=or der_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=194, stopIndex=194, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)])), IndexOrderByItemSegment(super=OrderByItemSegment(startIndex=206, stopIndex=206, orderDirection=ASC, nullOrderDirection=ASC), columnIndex=2)])], combine=Optional.empty), limit=Optional[LimitSegment(startIndex=208, stopIndex=215, offset=Optional.empty, rowCount=Optional[NumberLiteralLimitValueSegment(super=LimitValueSegment(startIndex=214, stopIndex=215), value=10)])], lock=Optional.empty, window=Optional.empty))) [INFO ] 2022-12-01 18:45:04.392 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create view select_view as select o.order_id,o.user_id,status,remark,merchant_name from t_new_order_0 o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10 [INFO ] 2022-12-01 18:45:04.406 [HikariPool-2 connection adder] o.o.core.v3.ConnectionFactoryImpl - [5df32809-1401-46a8-9cf9-1699c6065ab1] Try to connect. IP: 10.29.180.204:16000 [INFO ] 2022-12-01 18:45:04.452 [HikariPool-2 connection adder] o.o.core.v3.ConnectionFactoryImpl - [7.212.123.28:35872/10.29.180.204:16000] Connection is established. ID: 5df32809-1401-46a8-9cf9-1699c6065ab1 [INFO ] 2022-12-01 18:45:04.458 [HikariPool-2 connection adder] o.o.core.v3.ConnectionFactoryImpl - Connect complete. ID: 5df32809-1401-46a8-9cf9-1699c6065ab1 [INFO ] 2022-12-01 18:45:05.308 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select * from select_view [INFO ] 2022-12-01 18:45:05.308 [Connection-305-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=7, projections=[ShorthandProjectionSegment(startIndex=7, stopIndex=7, owner=Optional.empty, alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=14, stopIndex=24, identifier=IdentifierValue(value=select_view, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional.empty, groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combine=Optional.empty), limit=Optional.empty, lock=Optional.empty, window=Optional.empty) [INFO ] 2022-12-01 18:45:05.308 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from select_view [ERROR] 2022-12-01 18:45:05.316 [Connection-305-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur: org.opengauss.util.PSQLException: [7.212.123.28:53188/10.29.180.204:15000] ERROR: relation "select_view" does not exist on dn_6001_6002_6003 Position: 15 at org.opengauss.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2901) at org.opengauss.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2630) at org.opengauss.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:362) at org.opengauss.jdbc.PgStatement.runQueryExecutor(PgStatement.java:562) at org.opengauss.jdbc.PgStatement.executeInternal(PgStatement.java:539) at org.opengauss.jdbc.PgStatement.execute(PgStatement.java:397) at org.opengauss.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:156) at org.opengauss.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:145) 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:43) 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:229) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.doExecute(ProxySQLExecutor.java:186) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:151) at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:131) at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.JDBCPortal.bind(JDBCPortal.java:101) at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:53) at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.PostgreSQLAggregatedCommandExecutor.execute(PostgreSQLAggregatedCommandExecutor.java:41) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:111) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78) 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) ``` ### Example codes for reproduce this issue (such as a github link). ``` --create table drop table if exists t_new_order; drop table if exists t_merchant; create table t_new_order (order_id int primary key, user_id int not null, status varchar(50) not null, merchant_id int not null, remark varchar(50), creation_date date); create table t_merchant (merchant_id int primary key, country_id int not null, merchant_name varchar(50) not null, business_code varchar(50) not null, telephone varchar(50) not null, creation_date date not null); -- t_new_order insert into t_new_order values(1000, 10, 'init', 1, 'test', '2017-07-08'); insert into t_new_order values(1001, 10, 'init', 2, 'test', '2017-07-08'); insert into t_new_order values(2000, 20, 'init', 3, 'test', '2017-08-08'); insert into t_new_order values(2001, 20, 'init', 4, 'test', '2017-08-08'); insert into t_new_order values(1100, 11, 'init', 5, 'test', '2017-08-08'); insert into t_new_order values(1101, 11, 'init', 6, 'test', '2017-08-08'); insert into t_new_order values(2100, 21, 'finish', 7, 'test', '2017-08-08'); insert into t_new_order values(2101, 21, 'finish', 8, 'test', '2017-08-08'); insert into t_new_order values(1200, 12, 'finish', 9, 'finish', '2017-08-08'); insert into t_new_order values(1201, 12, 'finish', 10, 'test22', '2017-08-18'); insert into t_new_order values(2200, 22, 'finish', 11, 'test', '2017-08-18'); insert into t_new_order values(2201, 22, 'finish', 12, 'test', '2017-08-18'); insert into t_new_order values(1300, 13, 'finish', 13, '', '2017-08-18'); insert into t_new_order values(1301, 13, 'finish', 14, 'test', '2017-08-18'); insert into t_new_order values(2300, 23, 'finish ', 15, 'test', '2017-08-18'); insert into t_new_order values(2301, 23, 'finish', 16, 'test', '2017-08-18'); insert into t_new_order values(1400, 14, 'init', 17, '', '2017-08-18'); insert into t_new_order values(1401, 14, 'init', 18, 'test', '2017-08-18'); insert into t_new_order values(2400, 24, 'init', 19, 'test', '2017-08-18'); insert into t_new_order values(2401, 24, 'init', 20, 'test', '2017-08-18'); insert into t_new_order values(1500, 15, 'init', 1, '', '2017-08-28'); insert into t_new_order values(1501, 15, 'init', 2, 'test', '2017-08-28'); insert into t_new_order values(2500, 25, 'init', 3, 'test', '2017-08-28'); insert into t_new_order values(2501, 25, 'init', 4, 'test', '2017-08-28'); insert into t_new_order values(1600, 16, 'init', 5, 'test', '2017-08-28'); insert into t_new_order values(1601, 16, 'init', 6, '', '2017-08-28'); insert into t_new_order values(2600, 26, 'init', 7, 'test', '2017-08-28'); insert into t_new_order values(2601, 26, 'init', 8); insert into t_new_order values(1700, 17, 'init', 9, 'test', '2017-08-28'); insert into t_new_order values(1701, 17, 'finish', 10, 'test', '2017-08-18'); insert into t_new_order values(2700, 27, 'finish', 11, 'test', '2017-08-18'); insert into t_new_order values(2701, 27, 'finish', 12, 'test', '2017-08-18'); insert into t_new_order values(1800, 18, 'finish', 13, 'test', '2017-08-18'); insert into t_new_order values(1801, 18, 'finish', 14); insert into t_new_order values(2800, 28, 'finish', 15, 'test', '2017-08-18'); insert into t_new_order values(2801, 28, 'finish', 16, 'test', '2017-08-18'); insert into t_new_order values(1900, 19, 'init', 17, 'test', '2017-08-18'); insert into t_new_order values(1901, 19, 'init', 18, 'test', '2017-08-18'); insert into t_new_order values(2900, 29, 'init', 19, 'test', '2017-08-18'); insert into t_new_order values(2901, 29, 'init', 20, 'test', '2017-08-18'); insert into t_new_order values(1902, 19, 'init', 17, 'test11', '2017-08-18'); insert into t_new_order values(1903, 19, 'init', 18, 'test12', '2017-08-18'); insert into t_new_order values(2902, 29, 'init', 19, 'test', '2017-08-18'); insert into t_new_order values(2903, 29, 'init', 20, 'test', '2017-08-18'); -- 插入 t_merchant 测试数据 insert into t_merchant values(1, 86, 'tencent', '86000001', '86100000001', '2017-08-08'); insert into t_merchant values(2, 86, 'haier', '86000002', '86100000002', '2017-08-08'); insert into t_merchant values(3, 86, 'huawei', '86000003', '86100000003', '2017-08-08'); insert into t_merchant values(4, 86, 'alibaba', '86000004', '86100000004', '2017-08-08'); insert into t_merchant values(5, 86, 'lenovo', '86000005', '86100000005', '2017-08-08'); insert into t_merchant values(6, 86, 'moutai', '86000006', '86100000006', '2017-12-08'); insert into t_merchant values(7, 86, 'baidu', '86000007', '86100000007', '2017-08-08'); insert into t_merchant values(8, 86, 'xiaomi', '86000008', '86100000008', '2017-08-08'); insert into t_merchant values(9, 86, 'vivo', '86000009', '86100000009', '2017-11-08'); insert into t_merchant values(10, 86, 'oppo', '86000010', '86100000010', '2017-08-08'); insert into t_merchant values(11, 1, 'google', '01000011', '01100000011', '2017-08-08'); insert into t_merchant values(12, 1, 'walmart', '01000012', '01100000012', '2017-08-18'); insert into t_merchant values(13, 1, 'amazon', '01000013', '01100000013', '2017-08-08'); insert into t_merchant values(14, 1, 'apple', '01000014', '01100000014', '2017-07-08'); insert into t_merchant values(15, 1, 'microsoft', '01000015', '01100000015', '2017-08-08'); insert into t_merchant values(16, 1, 'dell', '01000016', '01100000016', '2017-08-08'); insert into t_merchant values(17, 1, 'johnson', '01000017', '01100000017', '2017-08-08'); insert into t_merchant values(18, 1, 'intel', '01000018', '01100000018', '2017-06-08'); insert into t_merchant values(19, 1, 'hp', '01000019', '01100000019', '2017-08-08'); insert into t_merchant values(20, 1, 'tesla', '01000020', '01100000020', '2017-08-08'); ``` ``` schemaName: test_db dataSources: ds_0: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 260 minPoolSize: 10 password: Test@123 url: jdbc:opengauss://90.90.44.171:14000/test_db?batchMode=on username: tpccuser ds_1: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 260 minPoolSize: 10 password: Test@123 url: jdbc:opengauss://90.90.44.171:15000/test_db?batchMode=on username: tpccuser rules: - !SHARDING tables: t_user: actualDataNodes: ds_0.t_user t_product: actualDataNodes: ds_0.t_product t_merchant: actualDataNodes: ds_1.t_merchant t_product_detail: actualDataNodes: ds_1.t_product_detail t_order: actualDataNodes: ds_${0..1}.t_order databaseStrategy: standard: shardingColumn: user_id shardingAlgorithmName: database_inline t_order_item: actualDataNodes: ds_${0..1}.t_order_item databaseStrategy: standard: shardingColumn: user_id shardingAlgorithmName: database_inline t_order_item1: actualDataNodes: ds_${0..1}.t_order_item1 databaseStrategy: standard: shardingColumn: user_id shardingAlgorithmName: database_inline t_new_order: actualDataNodes: ds_${0..1}.t_new_order_${0..1} databaseStrategy: standard: shardingAlgorithmName: database_inline shardingColumn: user_id tableStrategy: standard: shardingColumn: order_id shardingAlgorithmName: table_inline bindingTables: - t_order,t_order_item broadcastTables: - t_product_category - t_country shardingAlgorithms: database_inline: type: INLINE props: algorithm-expression: ds_${user_id % 2} allow-range-query-with-inline-sharding: true table_inline: type: INLINE props: algorithm-expression: t_new_order_${order_id % 2} allow-range-query-with-inline-sharding: true mode: type: Cluster repository: type: ZooKeeper props: namespace: governance_ds server-lists: 7.212.123.28:2181 retryIntervalMilliseconds: 500 timeToLiveSeconds: 60 maxRetries: 3 operationTimeoutMilliseconds: 500 authority: users: - user: root@% password: root - user: sharding password: sharding privilege: type: ALL_PERMITTED rules: - !TRANSACTION defaultType: XA providerType: Atomikos props: sql-show: true ``` -- 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-unsubscr...@shardingsphere.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
