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)