terrymanu commented on issue #23799:
URL:
https://github.com/apache/shardingsphere/issues/23799#issuecomment-3630126895
## Problem Understanding
- Running select count(1) from t_order on SQLServer triggers “please add
alias for aggregate selections” during result merge; aggregates without aliases
cannot return results.
## Root Cause
- SQLServer JDBC returns an empty column label for aggregate columns
without aliases; ShardingSphere’s merge phase relies on column labels/aliases
to build index mappings, so an empty label causes a lookup failure and throws.
Per the SQLServer JDBC official doc,
ResultSetMetaData.getColumnLabel may be empty when no alias is present:
ResultSetMetaData.getColumnLabel
(https://learn.microsoft.com/en-us/sql/connect/jdbc/reference/resultsetmetadata-getcolumnlabel-method-sqlserver?view=sql-server-ver16).
## Analysis
- The current merge logic requires an identifiable column label. SQLServer
returns an empty label for alias-less aggregates, leading to an empty key in
columnLabelIndexMap and a failure in setIndexForAggregationProjection.
- Compatibility options (SQLServer-only to avoid impacting other
databases):
- When building columnLabelIndexMap, add fallbacks for empty labels:
first getColumnLabel, if empty getColumnName, if still empty use the projection
expression as the key. Example:
```java
String label = SQLUtils.getExactlyValue(meta.getColumnLabel(i));
if (label.isEmpty() && isSQLServer(protocolType)) {
label = SQLUtils.getExactlyValue(meta.getColumnName(i));
if (label.isEmpty()) {
label = SQLUtils.getExactlyValue(projectionExpressions.get(i
- 1));
}
}
result.put(label, i);
```
- Or, in setIndexForAggregationProjection, add a SQLServer-specific
fallback: if label lookup fails, match by projection order to set the index.
- Add tests covering SQLServer metadata returning empty labels, and scope
the change to SQLServer to avoid regressions for other databases.
## Conclusion
- This is a SQLServer compatibility gap for aggregate columns without
aliases. It can be fixed by adding SQLServer-specific label/position fallbacks
and corresponding tests. We warmly invite community contributors to submit a PR
to implement and verify this; we’re happy to help review and test.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
