"java.sql.SQLException: Column index out of range" occurs during a non-shard table subquery.

[atlkay]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.2.1

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

ShardingSphere-JDBC

Expected behavior

For tables with no sharding rules set, the framework should not affect their query statements.

Actual behavior

There are some tables in my project that do not have a split table rule set. When I write a query involving these tables, an exception occurs. When I remove the pom dependency of shardingjdbc, the query will not report an error, so I think this problem may be related to the version.

Reason analyze (If you can)

I tried a few things, but I couldn't figure out why.

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

sql:

SELECT en.* FROM evaluation_norm en LEFT JOIN evaluation_type et ON en.STARLEVEL_TYPE_CODE = et.TYPE_CODE LEFT JOIN ( SELECT DISTINCT em.STARLEVEL_TYPE_CODE, em.STARLEVEL_CODE, em.NORM_CODE FROM evaluation_norm em LEFT JOIN evaluation_type ee ON em.STARLEVEL_TYPE_CODE = ee.TYPE_CODE WHERE 1 = 1 AND ee.TYPE_STATUS = '1' AND em.STARLEVEL_STATUS != '0' ) b ON en.STARLEVEL_TYPE_CODE = b.STARLEVEL_TYPE_CODE AND en.STARLEVEL_CODE = b.STARLEVEL_CODE AND en.NORM_CODE = b.NORM_CODE WHERE 1 = 1 AND et.TYPE_STATUS = '1' AND en.STARLEVEL_STATUS != '0' AND en.STARLEVEL_TYPE_CODE = b.STARLEVEL_TYPE_CODE AND en.STARLEVEL_CODE = b.STARLEVEL_CODE AND en.NORM_CODE = b.NORM_CODE AND en.NORM_VERSION = ( SELECT max( CONVERT ( nn.NORM_VERSION, DECIMAL ( 7, 5 ))) FROM evaluation_norm nn LEFT JOIN evaluation_type tt ON nn.STARLEVEL_TYPE_CODE = tt.TYPE_CODE WHERE 1 = 1 AND tt.TYPE_STATUS = '1' AND nn.STARLEVEL_STATUS != '0' AND nn.STARLEVEL_TYPE_CODE = en.STARLEVEL_TYPE_CODE AND nn.STARLEVEL_CODE = en.STARLEVEL_CODE AND nn.NORM_CODE = en.NORM_CODE ) ORDER BY en.STARLEVEL_TYPE_CODE, en.STARLEVEL_CODE, en.NORM_CODE LIMIT 1

sharding rule configuration:

spring: autoconfigure: exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure shardingsphere:

datasource:
  names: ds0
  ds0:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: ${hcp.datasource.dev.url}
    username: ${hcp.datasource.dev.username}
    password: ${hcp.datasource.dev.password}

rules:
  sharding:

    tables:

      t_sp_apprise:
        actual-data-nodes: ds0.t_sp_apprise_${2023}_q${1..4}
        table-strategy:
          standard:
            sharding-column: assess_time
            sharding-algorithm-name: quarter-strategy-sharding-algorithm

      up_apprise:
        actual-data-nodes: ds0.up_apprise_${2023}_q${1..4}
        table-strategy:
          standard:
            sharding-column: ASSESS_TIME
            sharding-algorithm-name: quarter-strategy-sharding-algorithm

      hcp_bj:
        actual-data-nodes: ds0.hcp_bj_${2023}_q${1..4}
        table-strategy:
          standard:
            sharding-column: creat_time
            sharding-algorithm-name: quarter-strategy-sharding-algorithm

      pjyz_record:
        actual-data-nodes: ds0.pjyz_record_${2023}_q${1..4}
        table-strategy:
            standard:
              sharding-column: create_time
              sharding-algorithm-name: quarter-strategy-sharding-algorithm

    sharding-algorithms:

      quarter-strategy-sharding-algorithm:
        type: CLASS_BASED
        props:
          strategy: standard

          algorithmClassName: com.vingsoft.hcp.shardingsphere.QuarterStrategyShardingAlgorithm
props:
  sql-show: true

---

exception :

org.springframework.dao.TransientDataAccessResourceException:

Error querying database. Cause: java.sql.SQLException: Column index out of range.

The error may exist in file [E:\hljhcpwfw\hcp-cloud-business\vingsoft-service\hcp-management\target\classes\com\vingsoft\hcp\mapper\NormMapper.xml]

The error may involve com.vingsoft.hcp.mapper.NormMapper.selectNormPage2

The error occurred while handling results

SQL: SELECT en.* FROM evaluation_norm en LEFT JOIN evaluation_type et ON en.STARLEVEL_TYPE_CODE = et.TYPE_CODE LEFT JOIN (SELECT DISTINCT em.STARLEVEL_TYPE_CODE, em.STARLEVEL_CODE, em.NORM_CODE FROM evaluation_norm em LEFT JOIN evaluation_type ee ON em.STARLEVEL_TYPE_CODE = ee.TYPE_CODE WHERE 1 = 1 AND ee.TYPE_STATUS = '1' AND em.STARLEVEL_STATUS != '0') b ON en.STARLEVEL_TYPE_CODE = b.STARLEVEL_TYPE_CODE AND en.STARLEVEL_CODE = b.STARLEVEL_CODE AND en.NORM_CODE = b.NORM_CODE WHERE 1 = 1 AND et.TYPE_STATUS = '1' AND en.STARLEVEL_STATUS != '0' AND en.STARLEVEL_TYPE_CODE = b.STARLEVEL_TYPE_CODE AND en.STARLEVEL_CODE = b.STARLEVEL_CODE AND en.NORM_CODE = b.NORM_CODE AND en.NORM_VERSION = (SELECT max(CONVERT(nn.NORM_VERSION, DECIMAL(7, 5))) FROM evaluation_norm nn LEFT JOIN evaluation_type tt ON nn.STARLEVEL_TYPE_CODE = tt.TYPE_CODE WHERE 1 = 1 AND tt.TYPE_STATUS = '1' AND nn.STARLEVEL_STATUS != '0' AND nn.STARLEVEL_TYPE_CODE = en.STARLEVEL_TYPE_CODE AND nn.STARLEVEL_CODE = en.STARLEVEL_CODE AND nn.NORM_CODE = en.NORM_CODE) ORDER BY en.STARLEVEL_TYPE_CODE, en.STARLEVEL_CODE, en.NORM_CODE LIMIT ?

Cause: java.sql.SQLException: Column index out of range.

; Column index out of range.; nested exception is java.sql.SQLException: Column index out of range.

---

Example codes for reproduce this issue (such as a github link).

Just a mybatisplus paging query, the sql statement is what I described above。

[strongduanmu]

Hi @atlkay, can you try the latest 5.4.1 version?

[atlkay]

I changed "select * "to "select a,b,c...." In this form, the sql does not throw an error, which is a strange problem.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[floatinghua]

Hi @atlkay, can you try the latest 5.4.1 version?

I can't get the version from https://mvnrepository.com/

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[terrymanu]

The markdown format in your issue is incorrect. To facilitate reading, could you please revise it?

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.