SQL containing "count()" and "group by", expected to return null, actually returned a rows with data 0

[Library-cll]

Bug Report

Execute the following sql.
select field_1, field_2, count(1) from table_1 where field_1 in (xxxx, xxxxx) group by field_1
When no data row meets the condition, null should be returned, but the row with data 0 is returned.

Which version of ShardingSphere did you use?

ShardingSphere-JDBC 5.5.0

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC 5.5.0

Expected behavior

Return empty

Actual behavior

Return 0

Reason analyze (If you can)

In this issue: #4680, the bug where count returns null is fixed. However, when goupby exists in sql, null should be returned instead of 0.

In line 157, count is assigned a value, causing all SQL statements containing count to return one row of data.

shardingsphere/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/GroupByMemoryMergedResult.java

Lines 142 to 162 in fc0a225

	private List<MemoryQueryResultRow> getMemoryResultSetRows(final SelectStatementContext selectStatementContext,
	final Map<GroupByValue, MemoryQueryResultRow> dataMap, final List<Boolean> valueCaseSensitive) {
	if (dataMap.isEmpty()) {
	Object[] data = generateReturnData(selectStatementContext);
	return Arrays.stream(data).anyMatch(Objects::nonNull) ? Collections.singletonList(new MemoryQueryResultRow(data)) : Collections.emptyList();
	}
	List<MemoryQueryResultRow> result = new ArrayList<>(dataMap.values());
	result.sort(new GroupByRowComparator(selectStatementContext, valueCaseSensitive));
	return result;
	}
	private Object[] generateReturnData(final SelectStatementContext selectStatementContext) {
	List<Projection> projections = new LinkedList<>(selectStatementContext.getProjectionsContext().getExpandProjections());
	Object[] result = new Object[projections.size()];
	for (int i = 0; i < projections.size(); i++) {
	if (projections.get(i) instanceof AggregationProjection && AggregationType.COUNT == ((AggregationProjection) projections.get(i)).getType()) {
	result[i] = 0;
	}
	}
	return result;
	}

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

select field_1, field_2, count(1) from table_1 where field_1 in (xxxx, xxxxx) group by field_1

[strongduanmu]

Hi @Library-cll, can you test with master branch? I fixed this bug in #33449

[Library-cll]

Thanks for the reply.
It looks like it was fixed in this #33380
now the result is correct.

When can I use the new release version in my project?

[strongduanmu]

Considering that 5.5.1 was released not long ago, it may take another 2-3 months.

[strongduanmu]

Fixed by #33380