SQLServer Paging statements cause exception “Can not get index from column label ``”

[archyly]

This problem seems to have been solved in the new version #22614 but in Sqlserver its reproduce. Similar problems：#17973 ，#20352 ，#23799 There seems to be a connection between these problems。It may be related to paging sql ShardingSphere version ：5.3.1 SQL: SELECT count(0) FROM (SELECT DISTINCT t.* FROM test t) table_count

    at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.lambda$getIndexFromColumnLabelAndIndexMap$0(ShardingSphereResultSet.java:393)
    at org.apache.shardingsphere.infra.util.exception.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41)
    at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.getIndexFromColumnLabelAndIndexMap(ShardingSphereResultSet.java:393)
    at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.getLong(ShardingSphereResultSet.java:135)
    at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java)
    at sun.reflect.GeneratedMethodAccessor152.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.ResultSetLogger.invoke(ResultSetLogger.java:69)
    at com.sun.proxy.$Proxy286.getLong(Unknown Source)
    at org.apache.ibatis.type.LongTypeHandler.getNullableResult(LongTypeHandler.java:37)
    at org.apache.ibatis.type.LongTypeHandler.getNullableResult(LongTypeHandler.java:26)
    at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createPrimitiveResultObject(DefaultResultSetHandler.java:834)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:663)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:642)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:404)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:361)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:335)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:308)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:201)
    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)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at sun.reflect.GeneratedMethodAccessor138.invoke(Unknown Source)
    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:425)

shardingsphere config：

mode:
  type: Standalone
  repository:
    type: JDBC

dataSources:
  shardingmaster:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    jdbcUrl: jdbc:sqlserver://192.168.128.100:1433;databaseName=test
    username: test
    password: 'test'
props:
  sql-show: true
rules:
  - !SHARDING
    tables:
      test1:
        actualDataNodes: shardingmaster.test1_$->{2022..2023}
        tableStrategy:
          standard:
            shardingColumn: created_date
            shardingAlgorithmName: year-mod
      test2:
        actualDataNodes: shardingmaster.test2_$->{2022..2023}
        tableStrategy:
          standard:
            shardingColumn: created_date
            shardingAlgorithmName: year-mod
    shardingAlgorithms:
      year-mod:
        type: INTERVAL
        props:
          datetime-pattern: 'yyyy-MM-dd HH:mm:ss'
          datetime-upper: '2040-12-01 00:00:00'
          datetime-lower: '2022-01-01 00:00:00'
          sharding-suffix-pattern: 'yyyy'
          datetime-interval-amount: 1
          datetime-interval-unit: 'YEARS'

[wangjunfeng-wjf]

The same problem,Help me!!!!!

[xiaomizhou2]

help me!!! pagehelper and tenant plugins None of them can be used

[Blackcbears]

I tested all the 5.x's, and they all had this problem，May be LimitPaginationContextEngine error. If I have 3 parameters，such offsetSegment == 2 and rowCountSegment == 3

[strongduanmu]

Hi @Blackcbears, can you try to submit a pr to fix this problem?

[Blackcbears]

https://github.com/apache/shardingsphere/issues/17973#issuecomment-1212790893 maybe this is the answer

SELECT * FROM t_order ORDER BY order_id OFFSET 0 ROW FETCH NEXT ? ROWS ONLY

SELECT * FROM t_order ORDER BY order_id OFFSET ? ROW FETCH NEXT ? ROWS ONLY

[archyly]

@Blackcbears Part of the problem can be solved in the solution😂

[Blackcbears]

@archyly try add alias for example：SELECT COUNT(0) as number FROM t_order