footmanFF opened a new issue #9637: URL: https://github.com/apache/shardingsphere/issues/9637
### the full test case I use https://gitee.com/footmanff/sharding-jdbc-demos/blob/master/sharding-jdbc-3.1.0-bug-test/src/main/java/com/footmanff/sharding/jdbc/sample/Test1.java ### information version：3.1.0 project：sharding-jdbc ### logic sql ``` select * from order where user_id =? // 1 select * from t_record where user_id =? // 1 ``` ### db and table I use two DB, sharding_jdbc_demo_db0 and sharding_jdbc_demo_db1： #### sharding_jdbc_demo_db0： ``` CREATE TABLE `order_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `order_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `t_record_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CREATE TABLE `t_record_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; ``` #### sharding_jdbc_demo_db1： ``` CREATE TABLE `order_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `order_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `t_record_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CREATE TABLE `t_record_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; ``` ### datasource sharding rule and table sharding rule ```java @Bean public DataSource dataSource() throws SQLException { ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); // t_record shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration1()); // order shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration2()); Map<String, Object> configMap = new HashMap<>(); Properties props = new Properties(); props.put("sql.show", "true"); return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, configMap, props); } TableRuleConfiguration getOrderTableRuleConfiguration1() { TableRuleConfiguration result = new TableRuleConfiguration(); result.setLogicTable("t_record"); result.setActualDataNodes("sharding_jdbc_demo_db${0..1}.t_record_${0..1}"); result.setKeyGeneratorColumnName("order_id"); result.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "sharding_jdbc_demo_db${user_id % 2}")); result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_record_${order_id % 2}")); return result; } TableRuleConfiguration getOrderTableRuleConfiguration2() { TableRuleConfiguration result = new TableRuleConfiguration(); result.setLogicTable("order"); result.setActualDataNodes("sharding_jdbc_demo_db${0..1}.order_${0..1}"); result.setKeyGeneratorColumnName("order_id"); result.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "sharding_jdbc_demo_db${user_id % 2}")); result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "order_${user_id % 2}")); return result; } public Map<String, DataSource> createDataSourceMap() { // 配置第 1 个数据源 DruidDataSource dataSource1 = new DruidDataSource(); dataSource1.setDriverClassName("com.mysql.jdbc.Driver"); dataSource1.setUrl("jdbc:mysql://localhost:3306/sharding_jdbc_demo_db0"); dataSource1.setUsername("root"); dataSource1.setPassword("root"); // 配置第 2 个数据源 DruidDataSource dataSource2 = new DruidDataSource(); dataSource2.setDriverClassName("com.mysql.jdbc.Driver"); dataSource2.setUrl("jdbc:mysql://localhost:3306/sharding_jdbc_demo_db1"); dataSource2.setUsername("root"); dataSource2.setPassword("root"); Map<String, DataSource> result = new HashMap<>(); result.put("sharding_jdbc_demo_db0", dataSource1); result.put("sharding_jdbc_demo_db1", dataSource2); return result; } ``` ### my test case ```java @Test public void t1() throws Exception { ApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml"); final DataSource dataSource = ctx.getBean(DataSource.class); ExecutorService executorService = Executors.newFixedThreadPool(100); int count = 1000; CountDownLatch countDownLatch = new CountDownLatch(count); for (int i = 0; i < count; i++) { final int a = i; executorService.submit(() -> { if (a % 2 == 0) { queryOrder(dataSource); } else { queryRecord(dataSource); } countDownLatch.countDown(); }); } countDownLatch.await(); } private void queryOrder(DataSource dataSource) { try { Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement("select * from order where user_id =?"); preparedStatement.setLong(1, 1L); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { long id = resultSet.getLong(1); long orderId = resultSet.getLong(2); long userId = resultSet.getLong(3); System.out.println("order( " + id + ", " + orderId + ", " + userId + ")"); } preparedStatement.close(); connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } private void queryRecord(DataSource dataSource) { try { Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement("select * from t_record where user_id =?"); preparedStatement.setLong(1, 1L); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { long id = resultSet.getLong(1); long orderId = resultSet.getLong(2); long userId = resultSet.getLong(3); System.out.println("t_record(" + id + ", " + orderId + ", " + userId + ")"); } preparedStatement.close(); connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } ``` ### Expected behavior this sql only execute on db `sharding_jdbc_demo_db1` and table `order_1` ``` select * from order where user_id =? // 1 ``` ### Actual behavior I saw actual sql execute log like this： ``` [INFO] [pool-4-thread-67] ShardingSphere-SQL - Rule Type: sharding [INFO] [pool-4-thread-43] ShardingSphere-SQL - Logic SQL: select * from order where user_id =? [INFO] [pool-4-thread-43] ShardingSphere-SQL - SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@d8f0ccc), containStar=true, firstSelectItemStartPosition=7, selectListLastPosition=9, groupByLastPosition=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null, subQueryStatements=[], subQueryConditions=[]) [INFO] [pool-4-thread-43] ShardingSphere-SQL - Actual SQL: sharding_jdbc_demo_db0 ::: select * from order_0 where user_id =? ::: [[1]] [INFO] [pool-4-thread-43] ShardingSphere-SQL - Actual SQL: sharding_jdbc_demo_db0 ::: select * from order_1 where user_id =? ::: [[1]] [INFO] [pool-4-thread-43] ShardingSphere-SQL - Actual SQL: sharding_jdbc_demo_db1 ::: select * from order_0 where user_id =? ::: [[1]] [INFO] [pool-4-thread-43] ShardingSphere-SQL - Actual SQL: sharding_jdbc_demo_db1 ::: select * from order_1 where user_id =? ::: [[1]] ``` the sql executed on all database and all table. ### my analyze i guess the io.shardingsphere.core.parsing.antlr.extractor.impl.FromWhereExtractor cause the problem. FromWhereExtractor is used as single instance. but the field called predicateSegmentExtractor is created every time execute the method called extract of FromWhereExtractor： ```java /** * From clause extractor. * * @author duhongjun */ public final class FromWhereExtractor implements OptionalSQLSegmentExtractor { private PredicateExtractor predicateSegmentExtractor; /** * Extract SQL segment from SQL AST. * * @param ancestorNode ancestor node of AST * @param rootNode root node of AST * @return SQL segment */ public Optional<FromWhereSegment> extract(final ParserRuleContext ancestorNode, final ParserRuleContext rootNode) { // ... FromWhereSegment result = new FromWhereSegment(); predicateSegmentExtractor = new PredicateExtractor(result.getTableAliases()); // ... } } ``` tableAliases(Map<String, String>) is shared by the field predicateSegmentExtractor. i guess this is the caused. the returned FromWhereSegment of method extract will wrong. the field tableName of class ColumnSegment my be wrong. this cause the wrong sql route. ---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: [email protected]
