left join+order by cause NullPointerException

[somnuscq]

`2020-11-05 14:24:00.698 INFO 10272 --- [ main] o.a.s.core.log.ConfigurationLogger : ShardingRuleConfiguration: bindingTables:

• basic_bominfo tables: basic_bominfo: actualDataNodes: erpds$->{0}.basicbominfo$->{2018..2020} logicTable: basic_bominfo tableStrategy: standard: preciseAlgorithmClassName: com.ctl.mes.service.erp.shardingsphere.ShardingAlgorithm shardingColumn: updated_date`

Error querying database. Cause: java.lang.NullPointerException

The error may exist in file [G:\chenqi\阿里云\spring-cloud-alibaba-ctlmes\ctlmes-service-erp\target\classes\mapper\BasicBomInfoMapper.xml]

The error may involve com.ctl.mes.service.erp.mapper.BasicBomInfoMapper.list

The error occurred while handling results

SQL: select t.,i. from basic_bominfo as t left join basic_iteminfo as i on t.ITEM_ID = i.ITEM_ID order by t.UPDATED_DATE desc limit ?,?

Only the table 'basic_bominfo' is sharding.

[somnuscq]

org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.1.1

[somnuscq]

SQL: select t.*,i.* from basic_bominfo as t left join basic_iteminfo as i on t.ITEM_ID = i.ITEM_ID order by t.UPDATED_DATE desc limit ?,?

[somnuscq]

java.lang.NullPointerException: null
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitiveFromTables(OrderByValue.java:75) ~[sharding-core-merge-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitive(OrderByValue.java:65) ~[sharding-core-merge-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.<init>(OrderByValue.java:58) ~[sharding-core-merge-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByStreamMergedResult.orderResultSetsToQueue(OrderByStreamMergedResult.java:56) ~[sharding-core-merge-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByStreamMergedResult.<init>(OrderByStreamMergedResult.java:50) ~[sharding-core-merge-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.build(ShardingDQLResultMerger.java:85) ~[sharding-core-merge-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:63) ~[sharding-core-merge-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.underlying.merge.MergeEntry.merge(MergeEntry.java:85) ~[shardingsphere-merge-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.underlying.merge.MergeEntry.process(MergeEntry.java:75) ~[shardingsphere-merge-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.underlying.pluggble.merge.MergeEngine.merge(MergeEngine.java:61) ~[shardingsphere-pluggable-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.mergeQuery(ShardingPreparedStatement.java:190) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
    at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.getResultSet(ShardingPreparedStatement.java:158) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getFirstResultSet(DefaultResultSetHandler.java:239) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:189) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.4.6.jar:3.4.6]
    at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:108) ~[pagehelper-5.1.8.jar:na]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.4.6.jar:3.4.6]
    at com.sun.proxy.$Proxy166.query(Unknown Source) ~[na:na]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.6.jar:3.4.6]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.2.jar:1.3.2]
    at com.sun.proxy.$Proxy123.selectList(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) ~[mybatis-spring-1.3.2.jar:1.3.2]
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76) ~[mybatis-3.4.6.jar:3.4.6]
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[mybatis-3.4.6.jar:3.4.6]
    at com.sun.proxy.$Proxy124.list(Unknown Source) ~[na:na]

[kimmking]

basic_iteminfo is a sharding table?

[somnuscq]

basic_iteminfo is a sharding table?

basic_iteminfo is not a sharding table!

[strongduanmu]

@somnuscq Can you provide the structure of the basic_bominfo and basic_iteminfo table?

[terrymanu]

Closed because of no response anymore.

[somnuscq]

`SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;

---

-- Table structure for basic_bominfo_2018

---

DROP TABLE IF EXISTS basic_bominfo_2018; CREATE TABLE basic_bominfo_2018 ( BOM_ID decimal(12, 0) NOT NULL COMMENT '主键ID', HEADER_ID decimal(12, 0) NULL DEFAULT NULL, BOM_HEADER_ID decimal(12, 0) NULL DEFAULT NULL, ROW_ID decimal(65, 30) NULL DEFAULT NULL, ITEM_ID decimal(12, 0) NULL DEFAULT NULL COMMENT '零件ID', ITEM_CODE varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '零件编号', QUANTITY decimal(65, 30) NULL DEFAULT NULL COMMENT '数量', ERP_OP_CODE decimal(12, 0) NULL DEFAULT NULL COMMENT '工位编号', CREATED_DATE datetime(0) NULL DEFAULT NULL COMMENT '创建时间', UPDATED_DATE datetime(0) NULL DEFAULT NULL COMMENT '更新时间', DATA_STATUS varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态', ATTRIBUTE1 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE2 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE3 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE4 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE5 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PLID varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '生产线ID', ISTASK varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否生成任务', ISKEY varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否关重件', INTEGRATE_TIME varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '同步时间', PRIMARY KEY (BOM_ID) USING BTREE, UNIQUE INDEX UNIQUE_KEY_01(BOM_ID) USING BTREE, INDEX SHARDING_KEY_01(UPDATED_DATE) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

-- Table structure for basic_bominfo_2019

---

DROP TABLE IF EXISTS basic_bominfo_2019; CREATE TABLE basic_bominfo_2019 ( BOM_ID decimal(12, 0) NOT NULL COMMENT '主键ID', HEADER_ID decimal(12, 0) NULL DEFAULT NULL, BOM_HEADER_ID decimal(12, 0) NULL DEFAULT NULL, ROW_ID decimal(65, 30) NULL DEFAULT NULL, ITEM_ID decimal(12, 0) NULL DEFAULT NULL COMMENT '零件ID', ITEM_CODE varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '零件编号', QUANTITY decimal(65, 30) NULL DEFAULT NULL COMMENT '数量', ERP_OP_CODE decimal(12, 0) NULL DEFAULT NULL COMMENT '工位编号', CREATED_DATE datetime(0) NULL DEFAULT NULL COMMENT '创建时间', UPDATED_DATE datetime(0) NULL DEFAULT NULL COMMENT '更新时间', DATA_STATUS varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态', ATTRIBUTE1 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE2 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE3 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE4 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE5 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PLID varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '生产线ID', ISTASK varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否生成任务', ISKEY varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否关重件', INTEGRATE_TIME varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '同步时间', PRIMARY KEY (BOM_ID) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

-- Table structure for basic_bominfo_2020

---

DROP TABLE IF EXISTS basic_bominfo_2020; CREATE TABLE basic_bominfo_2020 ( BOM_ID decimal(12, 0) NOT NULL COMMENT '主键ID', HEADER_ID decimal(12, 0) NULL DEFAULT NULL, BOM_HEADER_ID decimal(12, 0) NULL DEFAULT NULL, ROW_ID decimal(65, 30) NULL DEFAULT NULL, ITEM_ID decimal(12, 0) NULL DEFAULT NULL COMMENT '零件ID', ITEM_CODE varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '零件编号', QUANTITY decimal(65, 30) NULL DEFAULT NULL COMMENT '数量', ERP_OP_CODE decimal(12, 0) NULL DEFAULT NULL COMMENT '工位编号', CREATED_DATE datetime(0) NULL DEFAULT NULL COMMENT '创建时间', UPDATED_DATE datetime(0) NULL DEFAULT NULL COMMENT '更新时间', DATA_STATUS varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态', ATTRIBUTE1 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE2 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE3 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE4 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ATTRIBUTE5 varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PLID varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '生产线ID', ISTASK varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否生成任务', ISKEY varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否关重件', INTEGRATE_TIME varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '同步时间', PRIMARY KEY (BOM_ID) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

-- Table structure for basic_iteminfo

---

DROP TABLE IF EXISTS basic_iteminfo; CREATE TABLE basic_iteminfo ( ITEM_ID varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键ID', ITEM_CODE varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料编号', DRAWING_CODE varchar(240) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料图号', ITEM_NAME varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称', ISGZJ decimal(1, 0) NULL DEFAULT NULL COMMENT '是否关重件（0：否；1：是）', LAST_UPDATE_DATE datetime(0) NULL DEFAULT NULL COMMENT '最后修改时间', LAST_UPDATE_BY varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '最后修改人CODE', STATUS varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态', IS_CHANGED decimal(1, 0) NULL DEFAULT NULL COMMENT '预留字段', CREATED_BY varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人ID', CREATED_DATE datetime(0) NULL DEFAULT NULL COMMENT '创建日期', PRIMARY KEY (ITEM_ID) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;`

[CheerwayRen]

@kimmking @strongduanmu We have the same problem with the same version，when using left join and order by mng_sale_amount_month is common table , mng_sale_amount_day is a sharding table!

sql is: SELECT msam.MALL_ID as mallId FROM mng_sale_amount_month msam LEFT JOIN mng_sale_amount_day msad ON msad.MONTH_ID = msam.ID WHERE msad.SALE_YMD in ('20201202','20210101') order by msam.MALL_ID; table mng_sale_amount_month scheme:

---

DROP TABLE IF EXISTS mng_sale_amount_month; CREATE TABLE mng_sale_amount_month ( ID bigint NOT NULL, MALL_ID bigint NOT NULL, PRIMARY KEY (ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

table mng_sale_amount_dayscheme:

---

DROP TABLE IF EXISTS mng_sale_amount_day; CREATE TABLE mng_sale_amount_day ( ID bigint NOT NULL COMMENT '主键', MONTH_ID bigint DEFAULT NULL COMMENT '月销售额ID', SALE_YMD varchar(8) DEFAULT NULL COMMENT '年月日' )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; sharding rule is by SALE_YMD Please take a look. If you need any other information, please contact me

[strongduanmu]

@CheerwayRen Can you try the latest 5.0.0-alpha version?

[CheerwayRen]

@CheerwayRen Can you try the latest 5.0.0-alpha version?

OK, I'll try later

[CheerwayRen]

@CheerwayRen Can you try the latest 5.0.0-alpha version? @strongduanmu I've switched to the new version 5.0.0-alpha , and use SPI do sharding , but there will still be this problem

[strongduanmu]

@CheerwayRen Thank you very much for your feedback, can you provide the configuration text? I will investigate this issue later.

[CheerwayRen]

The query statement is the same as before

@Bean
@Primary
public DataSource multipleDataSource(@Qualifier("base") DataSource base, @Qualifier("aliyun") DataSource aliyun) throws SQLException {
    Map<String, DataSource> targetDataSources = new HashMap<>(2);
    targetDataSources.put(DataSourceEnum.BASE.getValue(), base);
    targetDataSources.put(DataSourceEnum.ALIYUN.getValue(), aliyun);
    // 配置 day_report 表规则
    ShardingTableRuleConfiguration dayReportRuleConfiguration = new ShardingTableRuleConfiguration("mng_sale_amount_day", "base.mng_sale_amount_day_$->{2011..2030}0$->{1..9},base.mng_sale_amount_day_$->{2011..2030}1$->{0..2},aliyun.mng_sale_amount_day_$->{2011..2030}0$->{1..9},aliyun.mng_sale_amount_day_$->{2011..2030}1$->{0..2}");

    // 配置分表规则  第一个精确，第二个范围
    dayReportRuleConfiguration.setTableShardingStrategy(new StandardShardingStrategyConfiguration("SALE_YMD", "dayReportTableShardingAlgorithm"));

    // Sharding全局配置
    //多表的话,就配置多个就好了
    ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
    shardingRuleConfiguration.getTables().add(dayReportRuleConfiguration);
    //分库规则配置

    shardingRuleConfiguration.getShardingAlgorithms().put("dayReportTableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("dayReportRule", new Properties()));
    // 创建数据源
    DataSource dataSource = ShardingSphereDataSourceFactory.createDataSource(targetDataSources, Collections.singleton(shardingRuleConfiguration), new Properties());
    return dataSource;
}

---

@Slf4j
public class DayReportPreciseShardingAlgorithm implements StandardShardingAlgorithm<String> {

private static String month_format = "yyyyMM";

private Properties props = new Properties();

//精准匹配  in 和 =
@Override
public String doSharding(Collection<String> tableNames, PreciseShardingValue<String> shardingValue) {
    Date reportDay = DateUtil.parse(shardingValue.getValue(), "yyyyMMdd");
    String reportMonth = DateUtil.format(reportDay, month_format);
    return "mng_sale_amount_day_" + reportMonth;
}

//范围匹配  between
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
    Range<String> reportDayRange = rangeShardingValue.getValueRange();
    //最小值
    String lowerDate = reportDayRange.lowerEndpoint();
    if (StringUtils.isEmpty(lowerDate)) {
        //数据从2011年开始
        lowerDate = "201101";
    } else {
        lowerDate = lowerDate.substring(0, 6);
    }
    //最大值
    String upperDate = reportDayRange.upperEndpoint();
    if (StringUtils.isEmpty(upperDate)) {
        //数据到当前时间结束
        upperDate = DateUtil.format(new Date(), month_format);
    } else {
        upperDate = upperDate.substring(0, 6);
    }
    return getMonthTableEnums("mng_sale_amount_day_", DateUtil.parse(lowerDate, month_format), DateUtil.parse(upperDate, month_format));
}

private List<String> getMonthTableEnums(String table, DateTime start, DateTime end) {
    List<String> list = new ArrayList<>();
    while (!start.isAfter(end)) {
        list.add(table + DateUtil.format(start, month_format));
        start.offset(DateField.MONTH, 1);
    }
    return list;
}

@Override
public void init() {
    log.info("init day report rule");
}

@Override
public String getType() {
    return "dayReportRule";
}

@Override
public Properties getProps() {
    return props;
}

@Override
public void setProps(Properties props) {
    this.props = props;
}

}

Do you need any more information？

[strongduanmu]

@CheerwayRen Thank you. 👍

[strongduanmu]

@CheerwayRen I copied your code for testing, but some classes cannot be imported. Then I tested it with the master branch, and it didn't reproduce the problem. Can you try to test the master branch? Or submit your demo to the github repository.

mysql> SELECT msam.MALL_ID as mallId FROM mng_sale_amount_month msam LEFT JOIN mng_sale_amount_day msad ON msad.MONTH_ID = msam.ID WHERE msad.SALE_YMD in ('20201202','20210101') order by msam.MALL_ID;
Empty set (0.51 sec)

[CheerwayRen]

@CheerwayRen I copied your code for testing, but some classes cannot be imported. Then I tested it with the master branch, and it didn't reproduce the problem. Can you try to test the master branch? Or submit your demo to the github repository.

mysql> SELECT msam.MALL_ID as mallId FROM mng_sale_amount_month msam LEFT JOIN mng_sale_amount_day msad ON msad.MONTH_ID = msam.ID WHERE msad.SALE_YMD in ('20201202','20210101') order by msam.MALL_ID;
Empty set (0.51 sec)

If I have time this Saturday, I will put the demo in GitHub

[CheerwayRen]

@CheerwayRen I copied your code for testing, but some classes cannot be imported. Then I tested it with the master branch, and it didn't reproduce the problem. Can you try to test the master branch? Or submit your demo to the github repository.

mysql> SELECT msam.MALL_ID as mallId FROM mng_sale_amount_month msam LEFT JOIN mng_sale_amount_day msad ON msad.MONTH_ID = msam.ID WHERE msad.SALE_YMD in ('20201202','20210101') order by msam.MALL_ID;
Empty set (0.51 sec)

You try this and see if you can reproduce it，SQL script is also in it

https://github.com/CheerwayRen/sharding-demo.git

[CheerwayRen]

@CheerwayRen I copied your code for testing, but some classes cannot be imported. Then I tested it with the master branch, and it didn't reproduce the problem. Can you try to test the master branch? Or submit your demo to the github repository.

mysql> SELECT msam.MALL_ID as mallId FROM mng_sale_amount_month msam LEFT JOIN mng_sale_amount_day msad ON msad.MONTH_ID = msam.ID WHERE msad.SALE_YMD in ('20201202','20210101') order by msam.MALL_ID;
Empty set (0.51 sec)

Hello, what is the latest progress in this issue? Our project reconstruction needs to introduce sharding-jdbc

[strongduanmu]

@CheerwayRen Can you modify this demo with 5.0.0-alpha? Version 4.1.1 will no longer be maintained.

[CheerwayRen]

@CheerwayRen Can you modify this demo with 5.0.0-alpha? Version 4.1.1 will no longer be maintained.

OK, you can switch to branch version5.0.0 or click the link below. This problem can still be repeated. Please confirm it as soon as possible. Thank you! https://github.com/CheerwayRen/sharding-demo/tree/version5.0.0

[strongduanmu]

@CheerwayRen Thank you very much for your demo. Which database is the mng_sale_amount_month table in? base or aliyun?

[CheerwayRen]

@CheerwayRen Thank you very much for your demo. Which database is the mng_sale_amount_month table in? base or aliyun?

There are no database shardingRules configured here，You can use the default base ，I have both configured the same database in base and aliyun

@CheerwayRen Thank you very much for your demo. Which database is the mng_sale_amount_month table in? base or aliyun?

[strongduanmu]

@CheerwayRen If mng_sale_amount_month is not a broadcast table, then the left join statement may have a cross-database join scenario, which is not supported in the alpha version.

[CheerwayRen]

@CheerwayRen If mng_sale_amount_month is not a broadcast table, then the left join statement may have a cross-database join scenario, which is not supported in the alpha version.

There is no rule to configure the sub database. All queries go to the default database. There should be no case you said. You can try to remove the configuration of a database, or I'll go back to update the demo in the evening

[strongduanmu]

@CheerwayRen I tried the demo, the 5.0.0-alpha version has this problem, the latest 5.0.0-beta has been fixed, please upgrade the version.

[CheerwayRen]

@CheerwayRen I tried the demo, the 5.0.0-alpha version has this problem, the latest 5.0.0-beta has been fixed, please upgrade the version.

OK, I'll try to upgrade to see if there is any more question, thank you

[CheerwayRen]

@CheerwayRen I tried the demo, the 5.0.0-alpha version has this problem, the latest 5.0.0-beta has been fixed, please upgrade the version.

Hello, after I upgrade the version, the previous problem has been solved, but when MySQL configures sql mode is only full group by, there is a problem with SQL itself, but the framework throws array out of bounds exception. When I modify sql_ mode to support the wrong group query, this exception will still be thrown: the sql is SELECT msam.MALL_ID AS mallId, msad.SALE_YMD as saleYmd FROM mng_sale_amount_month msam LEFT JOIN mng_sale_amount_day msad ON msad.MONTH_ID = msam.ID WHERE msad.SALE_YMD IN ('20201201', '20210101') group by MALL_ID order by MALL_ID;

[strongduanmu]

@CheerwayRen This exception is caused by the two tables crossing databases and using calcite processing. The latest master branch optimizes this exception.

If you can guarantee that the two tables are in the same database, the exception will not occur.

[strongduanmu]

Fixed in master branch.