select * is not supported for associated query

[Yule-Momoko]

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.4.1

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

ShardingSphere-JDBC

Expected behavior

select * is supported

Actual behavior

The statements supported by version 5.0 are not supported by version 5.4.1

Reason analyze (If you can)

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

SELECT
A.FLAG,
B.*
FROM
TABLE_A A
LEFT JOIN TABLE_B B ON A.ID = B.ID
WHERE
A.CD = '123'

Caused by: java.sql.SQLFeatureNotSupportedException: Can not get index from column label ID.
at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.lambda$getIndexFromColumnLabelAndIndexMap$0(ShardingSphereResultSet.java:395)
at org.apache.shardingsphere.infra.util.exception.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41)
at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.getIndexFromColumnLabelAndIndexMap(ShardingSphereResultSet.java:395)
at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.getString(ShardingSphereResultSet.java:166)
at com.zaxxer.hikari.pool.HikariProxyResultSet.getString(HikariProxyResultSet.java)
at org.apache.ibatis.type.StringTypeHandler.getNullableResult(StringTypeHandler.java:37)
at org.apache.ibatis.type.StringTypeHandler.getNullableResult(StringTypeHandler.java:26)
at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyAutomaticMappings(DefaultResultSetHandler.java:560)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:402)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:354)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301)
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 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)

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

SELECT
A.FLAG,
B.*
FROM
TABLE_A A
LEFT JOIN TABLE_B B ON A.ID = B.ID
WHERE
A.CD = '123'

[strongduanmu]

@Yule-Momoko Can you provide your sharding configuration and table init sql?

[Yule-Momoko]

@Yule-Momoko能提供一下你的分片配置和建表sql吗？

Only read and write separation is configured：

dataSources:
  write-ds-1:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: 'jdbc:mysql://writexxxx:3306/test?serverTimezone=Asia/Shanghai&autoReconnect=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&allowMultiQueries=true'
    username: xxx
    password: xxx
  write-ds-1-read:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: 'jdbc:mysql://readxxx:3306/test?serverTimezone=Asia/Shanghai&autoReconnect=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&allowMultiQueries=true'
    username: xxx
    password: xxx


rules:
  - !READWRITE_SPLITTING
    dataSources:
      ds1:
        writeDataSourceName: write-ds-1
        readDataSourceNames:
          - write-ds-1-read
        transactionalReadQueryStrategy: PRIMARY
        loadBalancerName: round_robin

    loadBalancers:
      round_robin:
        type: ROUND_ROBIN
props:
  sql-show: true

table init：

CREATE TABLE `table_a` (
  `ID` bigint NOT NULL,
  `CD` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '代码',
  `NM` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '名称',
  `INNER_FLAG` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='';

CREATE TABLE `table_b` (
  `ID` bigint NOT NULL,
  `A_ID` bigint NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='TABLE_B';

INSERT INTO `g_master`.`table_a` (`ID`, `CD`, `NM`, `INNER_FLAG`) VALUES (1322001820620427265, '123', '测试', 0);


INSERT INTO `g_master`.`table_b` (`ID`, `A_ID`) VALUES (1325670582607421443, 1322001820620427265);

query sql：
  SELECT a.INNER_FLAG, b.*
        FROM table_a a
                 LEFT JOIN table_b b ON b.a_id = a.ID

[Yule-Momoko]

No error query：
select a.id,a.inner_flag, b.*
select a.inner_flag,b.id,n.a_id

[terrymanu]

This is a confirmed Bug.

Through code analysis, the issue occurs in the ShardingSphereResultSetUtils.createColumnLabelAndIndexMap() method. The exception is triggered when a query meets all of the following conditions:

1. Query involves enhanced tables (sharding tables, encrypt tables, etc.)
2. Uses JOIN query
3. Uses mixed column selection pattern: SELECT A.column, B.*

Root Cause

In the current implementation, when containsDerivedProjections() returns true, the system uses expansion projection mode to build column label mapping instead of using actual ResultSetMetaData. This causes some columns
(particularly from B.* expansion) to fail to map correctly to column labels, resulting in "Can not get index from column label" exception when users access columns by name.

Test Case Recommendation

To reproduce this issue, we recommend adding the following test case to test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-join.xml:

  <!-- Reproduce issue #28841: SELECT A.column, B.* pattern with enhanced tables -->
  <test-case sql="SELECT o.user_id, i.* FROM t_order o JOIN t_order_item i ON o.order_id = i.order_id WHERE o.order_id = ?" 
             db-types="MySQL,PostgreSQL,openGauss" 
             scenario-types="db,tbl,dbtbl_with_readwrite_splitting">
      <assertion parameters="1000:int" expected-data-source-name="read_dataset" />
  </test-case>

This test case simulates the user's specific scenario: selecting specific columns (o.user_id) plus wildcard columns from another table (i.*).

Fix Recommendation

Fix Direction: Improve Column Label Mapping Logic
In ShardingSphereResultSetUtils.createColumnLabelAndIndexMap(), ensure that all columns are correctly mapped in expansion projection mode:

  // Recommended approach in createColumnLabelAndIndexMapWithExpandProjections method
  // Combine with ResultSetMetaData to ensure all columns are correctly mapped
  private static Map<String, Integer> createColumnLabelAndIndexMapWithExpandProjections(
      final SelectStatementContext statementContext, final ResultSetMetaData resultSetMetaData) throws SQLException {
      // Use expansion projections, but supplement with actual column information from ResultSetMetaData
  }

Call for Volunteers

This is an important bug that affects user experience, particularly impacting:

• Scenarios using sharding tables + JOIN queries
• Scenarios using encrypt tables + JOIN queries
• Business scenarios requiring mixed selection of specific columns and wildcards

We welcome community contributor volunteers to:

1. Add e2e test cases to reproduce the issue
2. Implement the fix
3. Ensure the fix doesn't affect existing functionality

Interested contributors can leave a comment on this issue to express their participation interest. We will provide necessary guidance and support.