shardingsphere select * is not supported for associated query

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'

Oct 23 '23 02:10 Yule-Momoko

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

Oct 23 '23 02:10 strongduanmu

@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

Oct 23 '23 03:10 Yule-Momoko

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

Oct 23 '23 05:10 Yule-Momoko

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:

Query involves enhanced tables (sharding tables, encrypt tables, etc.)
Uses JOIN query
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:

Add e2e test cases to reproduce the issue
Implement the fix
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.

Nov 14 '25 10:11 terrymanu

If we check here whether the number of results from createColumnLabelAndIndexMapWithExpandProjections is inconsistent with resultSetMetaData.getColumnCount(), and if so, generate the columnMap using resultSetMetaData instead, this should resolve the issue, right?

Nov 18 '25 08:11 chenqi14

The SQL you provided can be executed successfully, but it will fail if you use the hintManager.

Nov 20 '25 11:11 chenqi14

Thanks for the extra details.

The root cause is that in containsDerivedProjections we build the column map from expanded projections and miss the bare column labels from table.*; HintManager triggers derived projections, so it hits this branch.

Your idea makes sense: when the expanded map’s size or labels don’t match ResultSetMetaData, we should fall back to ResultSetMetaData or at least fill in the missing columns. To scope the fix, could you share the exact HintManager usage/hint statement you’re using? As a temporary workaround, please replace b.* with explicit columns.

Nov 20 '25 17:11 terrymanu

rule: success case:

result : 16:31:04.907 [main] INFO ShardingSphere-SQL - Logic SQL: select T., T.s_key s_key_new from t_order T where T.s_key = '0' 16:31:04.907 [main] INFO ShardingSphere-SQL - Actual SQL: db_shard_0 ::: select T., T.s_key s_key_new from t_order_0 T where T.s_key = '0' 16:31:08.231 [main] INFO org.apache.shardingsphere.test.it.jdbc.ComplexSQLIT - result id=1, s_key=0

fail case:

result: 16:32:44.427 [main] INFO ShardingSphere-SQL - Logic SQL: select T., T.s_key s_key_new from t_order T where T.s_key = '0' 16:32:44.428 [main] INFO ShardingSphere-SQL - Actual SQL: db_shard_0 ::: select T., T.s_key s_key_new from t_order T where T.s_key = '0'

org.opentest4j.AssertionFailedError: Unexpected exception thrown: org.opentest4j.AssertionFailedError: Unexpected exception thrown: java.sql.SQLFeatureNotSupportedException: Can not get index from column label id.

at org.junit.jupiter.api.AssertionFailureBuilder.build(AssertionFailureBuilder.java:152)
at org.junit.jupiter.api.AssertDoesNotThrow.createAssertionFailedError(AssertDoesNotThrow.java:84)
at org.junit.jupiter.api.AssertDoesNotThrow.assertDoesNotThrow(AssertDoesNotThrow.java:53)
at org.junit.jupiter.api.AssertDoesNotThrow.assertDoesNotThrow(AssertDoesNotThrow.java:36)
at org.junit.jupiter.api.Assertions.assertDoesNotThrow(Assertions.java:3164)
at org.apache.shardingsphere.test.it.jdbc.ComplexSQLIT.testColumMap(ComplexSQLIT.java:81)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)

Nov 21 '25 08:11 chenqi14