select * is not supported for associated query

Yule-Momoko commented 11 months ago

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 commented 11 months ago

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

Yule-Momoko commented 11 months ago

@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 commented 11 months ago

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

github-actions[bot] commented 10 months ago

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

apache / shardingsphere