No result from sql: "select count(1) from t_order"

[jxch-1024]

shardingsphere version: 5.1.2 database: sqlserver

error message: please add alias for aggregate selections.

I need add alias from sql : "select count(1) as c from t_order".

[RaigorJiang]

@jxch-1024 I checked the grammar file in the master branch, it supports aliases, you can try the latest version 5.3.1

[jxch-1024]

@jxch-1024 I checked the grammar file in the master branch, it supports aliases, you can try the latest version 5.3.1

The error is not reported when antlr parses the syntax tree, but when sharding merges the result set.

The stack is as follows： Exception in thread "main" java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, databaseType=org.apache.shardingsphere.infra.database.type.dialect.SQLServerDatabaseType@126f8f24, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections at com.google.common.base.Preconditions.checkState(Preconditions.java:589) at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.setIndexForAggregationProjection(SelectStatementContext.java:230) at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.setIndexes(SelectStatementContext.java:222) at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:63) at org.apache.shardingsphere.infra.merge.MergeEngine.executeMerge(MergeEngine.java:78) at org.apache.shardingsphere.infra.merge.MergeEngine.merge(MergeEngine.java:67) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.mergeQuery(ShardingSpherePreparedStatement.java:487) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:218) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:722) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:810) at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:942) at com.luhui.shardingjdbc.demo.MainTest.main(MainTest.java:33)

[jxch-1024]

at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.setIndexes(SelectStatementContext.java:222)

The shardingsphere 5.3.1 version use h2(2.x)，my project use h2(1.x),so I can't upgrade to my project.

[RaigorJiang]

It's a pity that there is no test environment for sqlserver, so I can't investigate now.

[archyly]

It's a pity that there is no test environment for sqlserver, so I can't investigate now.

@RaigorJiang Same error in latest version 5.3.1

Caused by: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(0), alias=Optional.empty, databaseType=org.apache.shardingsphere.infra.database.type.dialect.SQLServerDatabaseType@46467955, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
	at com.google.common.base.Preconditions.checkState(Preconditions.java:590)
	at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.setIndexForAggregationProjection(SelectStatementContext.java:231)
	at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.setIndexes(SelectStatementContext.java:223)
	at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:70)
	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:213)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.getResultSet(HikariProxyPreparedStatement.java)
	at sun.reflect.GeneratedMethodAccessor150.invoke(Unknown Source)
	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.$Proxy285.getResultSet(Unknown Source)
	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 com.github.pagehelper.util.ExecutorUtil.executeAutoCount(ExecutorUtil.java:169)
	at com.github.pagehelper.PageInterceptor.count(PageInterceptor.java:197)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:140)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
	at com.sun.proxy.$Proxy284.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)

[terrymanu]

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:

        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.