本文记录了在使用sharding-sphere分表查询时，遇到COUNT(DISTINCT)去重导致的空指针异常，通过分析源码发现问题在于查询结果处理过程中对null值的判断。解决办法是在SQL中加入条件排除null值，确保查询结果的完整性。
)
项目环境
| 组件 | 版本 |
|---|---|
| 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值
扫一扫
1564
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
