sharding-sphere distinct 踩坑记录

)

项目环境

组件	版本
Spring boot	1.5.19
sharding-sphere	4.0.0-RC2

踩坑记录

报表统计中使用 distinct 关键字去重一个分表查询结果，结果sql 出现空指针异常
异常堆栈信息

Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.NullPointerException
### The error may exist in com/sw/member/service/mapper/userlogin/LoginRecordMapper.xml
### The error may involve com.sw.member.service.mapper.userlogin.LoginRecordMapper.getStartUpUserAmount
### The error occurred while handling results
### SQL: SELECT       COUNT(DISTINCT user_id) as startUpUserAmount     FROM       login_record     WHERE       platform = 'sw'                                             and login_time < date_sub(?, INTERVAL -1 DAY)
### Cause: java.lang.NullPointerException
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 115 common frames omitted
Caused by: java.lang.NullPointerException: null
	at org.apache.shardingsphere.core.execute.sql.execute.row.QueryRow.isEqualPartly(QueryRow.java:69)
	at org.apache.shardingsphere.core.execute.sql.execute.row.QueryRow.isEqual(QueryRow.java:64)
	at org.apache.shardingsphere.core.execute.sql.execute.row.QueryRow.equals(QueryRow.java:57)
	at java.util.HashMap.putVal(HashMap.java:634)
	at java.util.HashMap.put(HashMap.java:611)
	at java.util.HashSet.add(HashSet.java:219)
	at org.apache.shardingsphere.core.execute.sql.execute.result.DistinctQueryResult.fill(DistinctQueryResult.java:87)
	at org.apache.shardingsphere.core.execute.sql.execute.result.DistinctQueryResult.getResultData(DistinctQueryResult.java:76)
	at org.apache.shardingsphere.core.execute.sql.execute.result.DistinctQueryResult.<init>(DistinctQueryResult.java:63)
	at org.apache.shardingsphere.core.execute.sql.execute.result.AggregationDistinctQueryResult.<init>(AggregationDistinctQueryResult.java:51)
	at org.apache.shardingsphere.core.merge.dql.DQLMergeEngine.getRealQueryResults(DQLMergeEngine.java:82)
	at org.apache.shardingsphere.core.merge.dql.DQLMergeEngine.<init>(DQLMergeEngine.java:71)
	at org.apache.shardingsphere.core.merge.MergeEngineFactory.newInstance(MergeEngineFactory.java:58)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.getResultSet(ShardingPreparedStatement.java:139)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:69)
	at com.sun.proxy.$Proxy315.getResultSet(Unknown Source)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getFirstResultSet(DefaultResultSetHandler.java:235)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:185)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	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:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
	at com.sw.member.service.aop.SqlInterceptor.intercept(SqlInterceptor.java:73)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy313.query(Unknown Source)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:143)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy313.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
	... 122 common frames omitted

排查步骤

1、原有sql

 SELECT
      COUNT(DISTINCT user_id) as startUpUserAmount
    FROM
      login_record
    WHERE
      platform = 'sw'
    AND
      login_time < date_sub('2020-11-17', INTERVAL -1 DAY)

login_record 为分表 分表规则 使用手机号最后一位数字分为 login_record0~login_record9 十张表

2、实际执行sql

	SELECT
      DISTINCT user_id as startUpUserAmount
    FROM
      login_record9
    WHERE
      platform = 'sw' 
    AND 
	  login_time < date_sub('2020-11-17', INTERVAL -1 DAY)

3、分析

经对比 实际执行sql是查询十张表中未去重结果组装返回，而且报错信息中空指针异常提示出现位置为The error occurred while handling results 处理结果时出现异常，跟踪查询源码分析
查询结果处理类为org.apache.shardingsphere.core.execute.sql.execute.result.DistinctQueryResult

package org.apache.shardingsphere.core.execute.sql.execute.result;

import com.google.common.base.Function;
import com.google.common.collect.Iterators;
import com.google.common.collect.Lists;
import java.beans.ConstructorProperties;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.ObjectOutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import org.apache.shardingsphere.core.execute.sql.execute.row.QueryRow;

public class DistinctQueryResult implements QueryResult {
    private final QueryResultMetaData queryResultMetaData;
    private final Iterator<QueryRow> resultData;
    private QueryRow currentRow;

    public DistinctQueryResult(Collection<QueryResult> queryResults, List<String> distinctColumnLabels) throws SQLException {
        QueryResult firstQueryResult = (QueryResult)queryResults.iterator().next();
        this.queryResultMetaData = firstQueryResult.getQueryResultMetaData();
        this.resultData = this.getResultData(queryResults, distinctColumnLabels);
    }

    private Iterator<QueryRow> getResultData(Collection<QueryResult> queryResults, List<String> distinctColumnLabels) throws SQLException {
        Set<QueryRow> result = new LinkedHashSet();
        List<Integer> distinctColumnIndexes = Lists.transform(distinctColumnLabels, new Function<String, Integer>() {
            public Integer apply(String input) {
                return DistinctQueryResult.this.getColumnIndex(input);
            }
        });
        Iterator var5 = queryResults.iterator();

        while(var5.hasNext()) {
            QueryResult each = (QueryResult)var5.next();
            this.fill(result, each, distinctColumnIndexes);
        }

        return result.iterator();
    }

    private void fill(Set<QueryRow> resultData, QueryResult queryResult, List<Integer> distinctColumnIndexes) throws SQLException {
        while(queryResult.next()) {
            List<Object> rowData = new ArrayList(queryResult.getColumnCount());

            for(int columnIndex = 1; columnIndex <= queryResult.getColumnCount(); ++columnIndex) {
                rowData.add(queryResult.getValue(columnIndex, Object.class));
            }

            resultData.add(new QueryRow(rowData, distinctColumnIndexes));
        }

    }

    public List<DistinctQueryResult> divide() {
        return Lists.newArrayList(Iterators.transform(this.resultData, new Function<QueryRow, DistinctQueryResult>() {
            public DistinctQueryResult apply(QueryRow row) {
                Set<QueryRow> resultData = new LinkedHashSet();
                resultData.add(row);
                return new DistinctQueryResult(DistinctQueryResult.this.queryResultMetaData, resultData.iterator());
            }
        }));
    }

    public final boolean next() {
        if (this.resultData.hasNext()) {
            this.currentRow = (QueryRow)this.resultData.next();
            return true;
        } else {
            this.currentRow = null;
            return false;
        }
    }

    public Object getValue(int columnIndex, Class<?> type) {
        return this.currentRow.getColumnValue(columnIndex);
    }

    public Object getValue(String columnLabel, Class<?> type) {
        return this.currentRow.getColumnValue(this.getColumnIndex(columnLabel));
    }

    public Object getCalendarValue(int columnIndex, Class<?> type, Calendar calendar) {
        return this.currentRow.getColumnValue(columnIndex);
    }

    public Object getCalendarValue(String columnLabel, Class<?> type, Calendar calendar) {
        return this.currentRow.getColumnValue(this.getColumnIndex(columnLabel));
    }

    public InputStream getInputStream(int columnIndex, String type) {
        return this.getInputStream(this.currentRow.getColumnValue(columnIndex));
    }

    public InputStream getInputStream(String columnLabel, String type) {
        return this.getInputStream(this.currentRow.getColumnValue(this.getColumnIndex(columnLabel)));
    }

    protected InputStream getInputStream(Object value) {
        try {
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            ObjectOutputStream objectOutputStream = new ObjectOutputStream(byteArrayOutputStream);
            objectOutputStream.writeObject(value);
            objectOutputStream.flush();
            objectOutputStream.close();
            return new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
        } catch (Throwable var4) {
            throw var4;
        }
    }

    public boolean wasNull() {
        return null == this.currentRow;
    }

    public boolean isCaseSensitive(int columnIndex) throws SQLException {
        return this.queryResultMetaData.isCaseSensitive(columnIndex);
    }

    public int getColumnCount() throws SQLException {
        return this.queryResultMetaData.getColumnCount();
    }

    public String getColumnLabel(int columnIndex) throws SQLException {
        String columnLabel = this.queryResultMetaData.getColumnLabel(columnIndex);
        if (null != columnLabel) {
            return columnLabel;
        } else {
            throw new SQLException("Column index out of range", "9999");
        }
    }

    protected Integer getColumnIndex(String columnLabel) {
        return this.queryResultMetaData.getColumnIndex(columnLabel);
    }

    @ConstructorProperties({"queryResultMetaData", "resultData"})
    public DistinctQueryResult(QueryResultMetaData queryResultMetaData, Iterator<QueryRow> resultData) {
        this.queryResultMetaData = queryResultMetaData;
        this.resultData = resultData;
    }

    protected Iterator<QueryRow> getResultData() {
        return this.resultData;
    }

    protected QueryRow getCurrentRow() {
        return this.currentRow;
    }

    public QueryResultMetaData getQueryResultMetaData() {
        return this.queryResultMetaData;
    }
}

进一步跟踪实际处理方法getResultData

    private Iterator<QueryRow> getResultData(Collection<QueryResult> queryResults, List<String> distinctColumnLabels) throws SQLException {
        Set<QueryRow> result = new LinkedHashSet();
        List<Integer> distinctColumnIndexes = Lists.transform(distinctColumnLabels, new Function<String, Integer>() {
            public Integer apply(String input) {
                return DistinctQueryResult.this.getColumnIndex(input);
            }
        });
        Iterator var5 = queryResults.iterator();

        while(var5.hasNext()) {
            QueryResult each = (QueryResult)var5.next();
            this.fill(result, each, distinctColumnIndexes);
        }

        return result.iterator();
    }

可以看到对于结果的去重 sharding-sphere使用了一个LinkedHashSet 以存储去重后的结果
进一步跟踪set 增加元素方法 fill

private void fill(Set<QueryRow> resultData, QueryResult queryResult, List<Integer> distinctColumnIndexes) throws SQLException {
        while(queryResult.next()) {
            List<Object> rowData = new ArrayList(queryResult.getColumnCount());

            for(int columnIndex = 1; columnIndex <= queryResult.getColumnCount(); ++columnIndex) {
                rowData.add(queryResult.getValue(columnIndex, Object.class));
            }

            resultData.add(new QueryRow(rowData, distinctColumnIndexes));
        }

    }

可以看到该方法中 就是使用Set集合中 add 方法
方法源码这里不做展示了 网上有很多现成的源码分析 使用LinkedHashSet add 方法底层是使用LinkedHashMap 存储数据 原理
调用被添加元素的hashCode() 和 set集合中已有元素的hashCode比较是否相同
–hash值不同 直接存储
–hash值相同 调用被添加元素equals方法比较是否相同
----不相同 直接存储元素
----相同 认为是同一元素 不存储

回归正题 sharding-sphere 中排重使用的Set集合存储的元素是 org.apache.shardingsphere.core.execute.sql.execute.row.QueryRow这个类

package org.apache.shardingsphere.core.execute.sql.execute.row;

import com.google.common.base.Function;
import com.google.common.collect.Lists;
import java.beans.ConstructorProperties;
import java.util.Collections;
import java.util.List;

public final class QueryRow {
    private final List<Object> rowData;
    private final List<Integer> distinctColumnIndexes;

    public QueryRow(List<Object> rowData) {
        this(rowData, Collections.emptyList());
    }

    public Object getColumnValue(int columnIndex) {
        return this.rowData.get(columnIndex - 1);
    }

    public boolean equals(Object obj) {
        return this == obj || null != obj && this.getClass() == obj.getClass() && this.isEqual((QueryRow)obj);
    }

    private boolean isEqual(QueryRow queryRow) {
        if (this.distinctColumnIndexes.isEmpty()) {
            return this.rowData.equals(queryRow.getRowData());
        } else {
            return this.distinctColumnIndexes.equals(queryRow.getDistinctColumnIndexes()) && this.isEqualPartly(queryRow);
        }
    }

    private boolean isEqualPartly(QueryRow queryRow) {
        for(int i = 0; i < this.distinctColumnIndexes.size(); ++i) {
            if (!this.rowData.get(i).equals(queryRow.getRowData().get(i))) {
                return false;
            }
        }

        return true;
    }

    public int hashCode() {
        return this.distinctColumnIndexes.isEmpty() ? this.rowData.hashCode() : Lists.transform(this.distinctColumnIndexes, new Function<Integer, Object>() {
            public Object apply(Integer input) {
                return QueryRow.this.rowData.get(input - 1);
            }
        }).hashCode();
    }

    @ConstructorProperties({"rowData", "distinctColumnIndexes"})
    public QueryRow(List<Object> rowData, List<Integer> distinctColumnIndexes) {
        this.rowData = rowData;
        this.distinctColumnIndexes = distinctColumnIndexes;
    }

    public List<Object> getRowData() {
        return this.rowData;
    }

    public List<Integer> getDistinctColumnIndexes() {
        return this.distinctColumnIndexes;
    }
}

找到其中hashCode方法 和 equals方法 分析上边报错 我们发现空指针时出现在equals 方法中
那么重点分析equals方法

    public boolean equals(Object obj) {
        return this == obj || null != obj && this.getClass() == obj.getClass() && this.isEqual((QueryRow)obj);
    }

继续跟进该方法 找到isEqualPartly方法

    private boolean isEqualPartly(QueryRow queryRow) {
        for(int i = 0; i < this.distinctColumnIndexes.size(); ++i) {
            if (!this.rowData.get(i).equals(queryRow.getRowData().get(i))) {
                return false;
            }
        }

        return true;
    }

报错产生在 if (!this.rowData.get(i).equals(queryRow.getRowData().get(i))) {代码中
其中rowData 是 QueryRow 的一个属性 一般不可能为空 那么大概率空指针出现在rowData.get(i)中
跟踪代码找到rowData赋值位置
上述fill方法中

 List<Object> rowData = new ArrayList(queryResult.getColumnCount());

            for(int columnIndex = 1; columnIndex <= queryResult.getColumnCount(); ++columnIndex) {
                rowData.add(queryResult.getValue(columnIndex, Object.class));
            }

进行了rowData的初始化及赋值 赋值是通过queryResult进行的 继续分析 queryResult 对象值
找到fill方法调用位置

Iterator var5 = queryResults.iterator();
while(var5.hasNext()) {
	QueryResult each = (QueryResult)var5.next();
	this.fill(result, each, distinctColumnIndexes);
}

分析发现queryResult该对象是sql查询值 根据实际产生sql 直接查询是否会出现null值

结果中存在 null 值 排查结束
解决方案 在sql中 增加条件 排除结果为null 记录

改进后的sql

    SELECT
      COUNT(DISTINCT user_id) as startUpUserAmount
    FROM
      login_record
    WHERE
      platform = 'sw' AND user_id is not null   
    AND
      login_time < date_sub('2020-11-17', INTERVAL -1 DAY)

总结

使用sharding-sphere分表后 对于使用distinct排除查询结果并非在数据库层面进行的 而是将未排重结果查询出之后在代码中通过Set集合进行去重，因此 在使用distinct时 要保证 查询结果中不会出现null值