Error attempting to get column 'username' from result set. Cause: java.sql.SQLFeatureNotSupportedException:

[Leo-zd]

resource

ShardingSphere  5.3.0  => 5.3.2
Reason for upgrade: implements ShardingSphereDriverURLProvider, which implements customized fetching and decrypting of yaml configuration content.

error message

Error attempting to get column 'username' from result set.  Cause: java.sql.SQLFeatureNotSupportedException: Can not get index from column label `username`.
; Can not get index from column label `username`.; nested exception is java.sql.SQLFeatureNotSupportedException: Can not get index from column label `username`.
org.springframework.dao.InvalidDataAccessApiUsageException: Error attempting to get column 'username' from result set.  Cause: java.sql.SQLFeatureNotSupportedException: Can not get index from column label `username`.
; Can not get index from column label `username`.; nested exception is java.sql.SQLFeatureNotSupportedException: Can not get index from column label `username`.
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:96)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy115.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:121)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:85)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
	at com.sun.proxy.$Proxy154.queryPage(Unknown Source)

config

spring:
    # ShardingSphereDriver配置
    datasource:
        driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
        url: jdbc:shardingsphere:classpath:sharding.yml

# 基础配置
# JDBC 逻辑库名称。在集群模式中，使用该参数来联通 ShardingSphere-JDBC 与 ShardingSphere-Proxy。
# 默认值：logic_db
databaseName: logic_im_db

# 运行模式配置,不配置则默认单机模式 单机,集群
mode:
    # 运行模式类型。可选配置：Standalone、Cluster
    type: Standalone
    repository:
        # 持久化仓库类型 如果你选择集群方式，那么可以使用zookeeper、nacos等注册中心
        type: JDBC

# 数据源集合
dataSources:
    write_ds:
        driverClassName: com.mysql.cj.jdbc.Driver
        dataSourceClassName: com.zaxxer.hikari.HikariDataSource
        jdbcUrl: jdbc:mysql://xxx.xxx.xx:3306/xxx?useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&rewriteBatchedStatements=true&failOverReadOnly=false&allowMultiQueries=true
        username: root
        password: 123456
        initializationFailTimeout: 1
        validationTimeout: 5000
        maxLifetime: 1800000
        leakDetectionThreshold: 0
        minimumIdle: 1
        idleTimeout: 60000
        maximumPoolSize: 20
        connectionTimeout: 30000
        poolName: HikariPool-write_ds
        ConnectionTestQuery: SELECT 1
    read_ds_0:
        driverClassName: com.mysql.cj.jdbc.Driver
        dataSourceClassName: com.zaxxer.hikari.HikariDataSource
        username: read
        password: 123456
        jdbcUrl: jdbc:mysql://xxx.xxx.xx:3306/xxx?useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&rewriteBatchedStatements=true&failOverReadOnly=false&allowMultiQueries=true
        initializationFailTimeout: 1
        validationTimeout: 5000
        maxLifetime: 1800000
        leakDetectionThreshold: 0
        minimumIdle: 1
        idleTimeout: 60000
        maximumPoolSize: 20
        connectionTimeout: 30000
        poolName: HikariPool-read_ds_0
        ConnectionTestQuery: SELECT 1
    read_ds_1:
        driverClassName: com.mysql.cj.jdbc.Driver
        dataSourceClassName: com.zaxxer.hikari.HikariDataSource
        username: read
        password: 123456
        jdbcUrl: jdbc:mysql://xxx.xxx.xx:3306/xxx?useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&rewriteBatchedStatements=true&failOverReadOnly=false&allowMultiQueries=true
        initializationFailTimeout: 1
        validationTimeout: 5000
        maxLifetime: 1800000
        leakDetectionThreshold: 0
        minimumIdle: 1
        idleTimeout: 60000
        maximumPoolSize: 20
        connectionTimeout: 30000
        poolName: HikariPool-read_ds_0
        ConnectionTestQuery: SELECT 1

# 规则配置
rules:
    # 分片配置
    - !SHARDING
        tables:
            # 逻辑表名称
            im_user_message:
                # 由数据源名 + 表名组成（参考 Inline 语法规则）
                #                actualDataNodes: write_ds.im_user_message_${[2021, 2022, 2023, 2024, 2025]}
                actualDataNodes: write_ds.im_user_message_${[2024, 2025]}
                # 分表策略，同分库策略
                tableStrategy:
                    # 用于单分片键的标准分片场景
                    standard:
                        # 分片列名称，多个列以逗号分隔
                        shardingColumn: create_time
                        # 分片算法名称
                        shardingAlgorithmName: date_algorithms
        defaultTableStrategy:
            none:
        # 绑定表规则列表
        #        bindingTables:
        #            - t_order,t_order_item

        # 广播表规则列表（需要和分片的表进行关联的化就需要配置）
        broadcastTables:
            - im_user_account
            - im_group

        # 分片算法配置
        shardingAlgorithms:
            # 时间范围分片算法
            date_algorithms:
                type: INTERVAL
                props:
                    # 分片字段格式
                    datetime-pattern: 'yyyy-MM-dd HH:mm:ss'
                    # 范围下限，格式与 datetime-pattern 定义的时间戳格式一致
                    datetime-lower: '2022-01-01 00:00:00'
                    # 范围上限，格式与 datetime-pattern 定义的时间戳格式一致
                    datetime-upper: '2030-01-01 00:00:00'
                    # 分片数据源或真实表的后缀格式，必须遵循 Java DateTimeFormatter 的格式，必须和 datetime-interval-unit 保持一致。例如：yyyyMM
                    sharding-suffix-pattern: 'yyyy'
                    # 分片键时间间隔，超过该时间间隔将进入下一分片
                    datetime-interval-amount: 1
                    # 分片键时间间隔单位，必须遵循 Java ChronoUnit 的枚举值。例如：MONTHS
                    datetime-interval-unit: 'YEARS'

        # 分布式序列算法配置
        keyGenerators:
            # <key_generate_algorithm_name> (+): # 分布式序列算法名称
            snowflake:
                # 分布式序列算法类型
                type: SNOWFLAKE

    # 读写分离配置
    - !READWRITE_SPLITTING
        dataSources:
            # <data_source_name> (+): # 读写分离逻辑数据源名称
            readwrite_ds:
                # 读写分离类型  可选值：staticStrategy(静态读写分离)  dynamic_strategy(动态读写分离)
                staticStrategy:
                    # 写库数据源名称
                    writeDataSourceName: write_ds
                    # 读库数据源名称，多个从数据源用逗号分隔
                    readDataSourceNames:
                        - read_ds_0
                        - read_ds_1
                # 负载均衡算法名称
                loadBalancerName: alg_round
        # 负载均衡算法配置
        loadBalancers:
            # <load_balancer_name> (+): # 负载均衡算法名称
            # 轮询负载均衡算法 说明: 事务内，读请求根据 transaction-read-query-strategy 属性的配置进行路由。事务外，采用轮询策略路由到 replica。
            alg_round:
                # 负载均衡算法类型
                type: ROUND_ROBIN
                # 负载均衡算法属性配置
                props:
                    # 事务内读请求路由策略，可选值：FIXED_PRIMARY(路由到 primary)、FIXED_REPLICA(根据随机策略选择一个固定的 replica)、DYNAMIC_REPLICA(根据随机策略路由到不同的 replica)，默认值：FIXED_PRIMARY。
                    transaction-read-query-strategy: FIXED_PRIMARY
# 属性配置
# 是否打印sql
#props:
#    sql-show: true

  @Override
    public PageUtils queryPage(JSONObject params) {
        Query<MessageRecordViewEntity> query = new Query<>(MessageRecordViewEntity.class);
        IPage<MessageRecordViewEntity> page = messageRecordMapper.queryPage(query.getPage(params), query.getQueryWrapper(params));
        return new PageUtils(page);
    }

IPage<MessageRecordViewEntity> queryPage(Page<MessageRecordViewEntity> page, @Param("ew") QueryWrapper<MessageRecordViewEntity> queryWrapper);

<select id="queryPage" resultType="com.telecom.im.entity.view.MessageRecordViewEntity">
        SELECT * FROM (
        SELECT
        `a`.`id` AS `msg_id`,
        `a`.`msg_from` AS `id`,
        `b`.`username` AS `username`,
        `b`.`avatar` AS `avatar`,
        `a`.`content` AS `content`,
        `a`.`create_time` AS `timestamp`,
        `a`.`msg_to` AS `msg_to`,
        `c`.`username` AS `to_user_name`,
        `a`.`msg_type` AS `type`,
        `a`.`group_id` AS `group_id`,
        `a`.`is_read` AS `is_read`,
        `a`.`req_state` AS `req_state`,
        `a`.`system_id` AS `system_id`,
        `a`.`stars` AS `stars`,
        `a`.`tag` AS `tag`
        FROM
        `im_user_message` `a`
        LEFT JOIN `im_user_account` `b` ON `a`.`msg_from` = `b`.`id`
        LEFT JOIN `im_user_account` `c` ON `a`.`msg_to` = `c`.`id`
        ) t1
        <if test="ew != null">
            ${ew.customSqlSegment}
        </if>
    </select>

Trying to resolve

Rollback solves it, but my need to rewrite the ShardingSphereDriverURLProvider. 

[Leo-zd]

Is there a solution to this problem

[Leo-zd]

I modified it to the following format, which solves the problem, but is there any other way to solve it

   <select id="queryPage" resultType="com.telecom.im.entity.view.MessageRecordViewEntity">
        SELECT
            msg_id,
            id,
            username,
            avatar,
            content,
            timestamp,
            msg_to,
            to_user_name,
            type,
            group_id,
            is_read,
            req_state,
            system_id,
            stars,
            tag
        FROM (
            SELECT
                `a`.`id` AS `msg_id`,
                `a`.`msg_from` AS `id`,
                `b`.`username` AS `username`,
                `b`.`avatar` AS `avatar`,
                `a`.`content` AS `content`,
                `a`.`create_time` AS `timestamp`,
                `a`.`msg_to` AS `msg_to`,
                `c`.`username` AS `to_user_name`,
                `a`.`msg_type` AS `type`,
                `a`.`group_id` AS `group_id`,
                `a`.`is_read` AS `is_read`,
                `a`.`req_state` AS `req_state`,
                `a`.`system_id` AS `system_id`,
                `a`.`stars` AS `stars`,
                `a`.`tag` AS `tag`
            FROM
            `im_user_message` `a`
            LEFT JOIN `im_user_account` `b` ON `a`.`msg_from` = `b`.`id`
            LEFT JOIN `im_user_account` `c` ON `a`.`msg_to` = `c`.`id`
        ) t1
        <if test="ew != null">
            ${ew.customSqlSegment}
        </if>
    </select>

[terrymanu]

Because you have made customizations, it is difficult to analyze your issue through logs.
Can you provide reproducible code?
Please keep the code simple and only related to reproducing this issue, and remove other third-party ORM frameworks, etc.

[Leo-zd]

Well, it may take a while, I'm home from work now.

[Leo-zd]

Because you have made customizations, it is difficult to analyze your issue through logs. Can you provide reproducible code? Please keep the code simple and only related to reproducing this issue, and remove other third-party ORM frameworks, etc.

    <select id="queryPage2" resultType="com.telecom.im.entity.view.MessageRecordViewEntity">
        SELECT
            *
        FROM
            (
            SELECT
                `a`.`id` AS `msg_id`,
                `a`.`msg_from` AS `id`,
                `b`.`username` AS `username`,
                `b`.`avatar` AS `avatar`,
                `a`.`content` AS `content`,
                `a`.`create_time` AS `timestamp`,
                `a`.`msg_to` AS `msg_to`,
                `c`.`username` AS `to_user_name`,
                `a`.`msg_type` AS `type`,
                `a`.`group_id` AS `group_id`,
                `a`.`is_read` AS `is_read`,
                `a`.`req_state` AS `req_state`,
                `a`.`system_id` AS `system_id`,
                `a`.`stars` AS `stars`,
                `a`.`tag` AS `tag`
            FROM
                `im_user_message` `a`
                LEFT JOIN `im_user_account` `b` ON `a`.`msg_from` = `b`.`id`
                LEFT JOIN `im_user_account` `c` ON `a`.`msg_to` = `c`.`id`
            ) t1
            WHERE
                (system_id IN ("xxx","xxx") AND id = "fe14f88tdhs89ieda6fd8a61b1dfb")
            ORDER BY TIMESTAMP DESC
            LIMIT 11
    </select>

error msg

Error attempting to get column 'username' from result set.  Cause: java.sql.SQLFeatureNotSupportedException: Can not get index from column label `username`.\n; Can not get index from column label `username`.; nested exception is java.sql.SQLFeatureNotSupportedException: Can not get index from column label `username`.