With group by sql, throw Cannot invoke "org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable.getColumns()" because "table" is null

[craigwu9]

Shardingsphere version: 5.3.1

I was testing with shardingsphere-jdbc-core with a sql with group by statement, and got an exception below:

Caused by: java.lang.NullPointerException: Cannot invoke "org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable.getColumns()" because "table" is null at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitiveFromTables(GroupByMemoryMergedResult.java:135) at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitive(GroupByMemoryMergedResult.java:125) at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.init(GroupByMemoryMergedResult.java:73) at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.init(GroupByMemoryMergedResult.java:53) at org.apache.shardingsphere.infra.merge.result.impl.memory.MemoryMergedResult.<init>(MemoryMergedResult.java:52) at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.<init>(GroupByMemoryMergedResult.java:56) at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.getGroupByMergedResult(ShardingDQLResultMerger.java:128) at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.build(ShardingDQLResultMerger.java:93) at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:71) at org.apache.shardingsphere.infra.merge.MergeEngine.executeMerge(MergeEngine.java:81) at org.apache.shardingsphere.infra.merge.MergeEngine.merge(MergeEngine.java:71) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.mergeQuery(ShardingSpherePreparedStatement.java:575) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.getResultSet(ShardingSpherePreparedStatement.java:521) at com.zaxxer.hikari.pool.ProxyStatement.getResultSet(ProxyStatement.java:214) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.getResultSet(HikariProxyPreparedStatement.java) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getFirstResultSet(DefaultResultSetHandler.java:244) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)

my test project is simple, below is my config file:

and my custome sharding algorithm class ( most copy from ComplexInlineShardingAlgorithm and try to support range and not to return all available table names when use range )

`
public class MonthShardingAlgorithm implements ComplexKeysShardingAlgorithm {

private static final String SHARING_COLUMNS_KEY = "sharding-columns";
private static final String ALGORITHM_EXPRESSION_KEY = "algorithm-expression";


private Properties properties;

private String algorithmExpression;

private Collection<String> shardingColumns;

private Collection<String> getShardingColumns(final Properties props) {
    String shardingColumns = props.getProperty(SHARING_COLUMNS_KEY, "");
    return shardingColumns.isEmpty() ? Collections.emptyList() : Arrays.asList(shardingColumns.split(","));
}

@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Integer> shardingValue) {
    if (!shardingValue.getColumnNameAndRangeValuesMap().isEmpty()) {
        Map<String, Range<Integer>> columnNameAndRangeValueMap =  shardingValue.getColumnNameAndRangeValuesMap();
        Collection<Map<String, Integer>> collection = new LinkedList<>();
        for (Map.Entry<String, Range<Integer>> entry : columnNameAndRangeValueMap.entrySet()) {
            Range<Integer> rangeValue = entry.getValue();
            if (!rangeValue.hasLowerBound() || !rangeValue.hasUpperBound()) {
                throw new UnsupportedSQLOperationException("no bond range query is not supported");
            }
            for(int i = rangeValue.lowerEndpoint(); i<=rangeValue.upperEndpoint(); i++) {
                Map<String, Integer> item = new HashMap<>();
                item.put(entry.getKey(), i);
                collection.add(item);
            }
        }
        return collection.stream().map(this::doSharding).collect(Collectors.toList());
    }
    Map<String, Collection<Integer>> columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
    Preconditions.checkArgument(shardingColumns.isEmpty() || shardingColumns.size() == columnNameAndShardingValuesMap.size(),
            "Complex inline need %s sharing columns, but only found %s", shardingColumns.size(), columnNameAndShardingValuesMap.size());
    Collection<Map<String, Integer>> combine = combine(columnNameAndShardingValuesMap);
    return combine.stream().map(this::doSharding).collect(Collectors.toList());
}

private String doSharding(final Map<String, Integer> shardingValues) {
    Closure<?> closure = createClosure();
    for (Map.Entry<String, Integer> entry : shardingValues.entrySet()) {
        closure.setProperty(entry.getKey(), entry.getValue());
    }
    return closure.call().toString();
}

private static Collection<Map<String, Integer>> combine(final Map<String, Collection<Integer>> map) {
    Collection<Map<String, Integer>> result = new LinkedList<>();
    for (Map.Entry<String, Collection<Integer>> entry : map.entrySet()) {
        if (result.isEmpty()) {
            for (Integer value : entry.getValue()) {
                Map<String, Integer> item = new HashMap<>();
                item.put(entry.getKey(), value);
                result.add(item);
            }
        } else {
            Collection<Map<String, Integer>> list = new LinkedList<>();
            for (Map<String, Integer> loop : result) {
                for (Integer value : entry.getValue()) {
                    Map<String, Integer> item = new HashMap<>();
                    item.put(entry.getKey(), value);
                    item.putAll(loop);
                    list.add(item);
                }
            }
            result = list;
        }
    }
    return result;
}


private Closure<?> createClosure() {
    Closure<?> result = new InlineExpressionParser(algorithmExpression).evaluateClosure().rehydrate(new Expando(), null, null);
    result.setResolveStrategy(Closure.DELEGATE_ONLY);
    return result;
}

private String getAlgorithmExpression(final Properties props) {
    String algorithmExpression = props.getProperty(ALGORITHM_EXPRESSION_KEY);
    ShardingSpherePreconditions.checkNotNull(algorithmExpression, () -> new ShardingAlgorithmInitializationException(getType(), "Inline sharding algorithm expression can not be null."));
    return InlineExpressionParser.handlePlaceHolder(algorithmExpression.trim());
}

@Override
public Properties getProps() {
    return this.properties;
}

@Override
public void init(Properties props) {
    this.properties = props;
    this.shardingColumns = getShardingColumns(props);
    this.algorithmExpression = getAlgorithmExpression(props);

}

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

}`

my logic sql (a mybatis sql)

from log I can see it has been generated two actual sql

the problem comes out when it try to merge two result. I debug the source, seems that when do the merge, the SelectStatementContext used was the logic select statement, and want to find the table columns info from schema, but the schema only contains actual tables

so the problem is because my config or my custom ShardingAlgorithm?

[sandynz]

Hi @craigwu9 , thanks for your feedback. Nice debugging.

Hi @strongduanmu , did you find similar issue before?

[zhaojinchao95]

I think you need to adjustment actualDataNodes, such asds_0. test_data_${...}.

[craigwu9]

What I want is dynamic table name, not limited by actualDataNodes, I tried to defined it, didn't find a way. I want to split table by year month (yyyymm), so the actualDataNodes will increase by time.

[linghengqian]

• The new SPI was introduced in the related PR at Make actualDataNodes expose SPI that can define expression with custom rules and add GraalVM Truffle implementation #22899 , some issue can now define List<String> via an new Row Value Expression SPI implementation.

• For this issue, you need to target the ContextManager of ShardingSphere DataSource, update actualdatanodes and refresh the algorithm instance at the same time.