Can't insert an entity with @GeneratedValue in a SINGLE table

[jpenalver10]

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?

Version 5.5.2

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

ShardingSphere-JDBC

Expected behavior

The entity should be saved in the table defined as single table.

Actual behavior

An error is thrown indicating the generated key (long) can't be obtained due to a NumberFormatException.

Caused by: java.lang.NumberFormatException: For input string: "{my-event-as-string}"
	at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:67)
	at java.base/java.lang.Long.parseLong(Long.java:709)
	at java.base/java.lang.Long.parseLong(Long.java:832)
	at org.apache.shardingsphere.driver.jdbc.core.resultset.GeneratedKeysResultSet.getLong(GeneratedKeysResultSet.java:145)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java)
	at org.hibernate.type.descriptor.jdbc.BigIntJdbcType$2.doExtract(BigIntJdbcType.java:79)
	at org.hibernate.type.descriptor.jdbc.BasicExtractor.extract(BasicExtractor.java:44)
	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.getCurrentRowValue(JdbcValuesResultSetImpl.java:387)
	at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.getJdbcValue(RowProcessingStateStandardImpl.java:152)
	at org.hibernate.sql.results.graph.basic.BasicResultAssembler.extractRawValue(BasicResultAssembler.java:54)
	at org.hibernate.sql.results.graph.basic.BasicResultAssembler.assemble(BasicResultAssembler.java:60)
	at org.hibernate.sql.results.internal.StandardRowReader.readRow(StandardRowReader.java:155)
	at org.hibernate.sql.results.spi.ListResultsConsumer.read(ListResultsConsumer.java:249)
	at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:201)
	at org.hibernate.generator.values.internal.GeneratedValuesHelper.readGeneratedValues(GeneratedValuesHelper.java:211)
	at org.hibernate.generator.values.internal.GeneratedValuesHelper.getGeneratedValues(GeneratedValuesHelper.java:102)
	at org.hibernate.id.insert.GetGeneratedKeysDelegate.performMutation(GetGeneratedKeysDelegate.java:121)
	at org.hibernate.engine.jdbc.mutation.internal.MutationExecutorSingleNonBatched.performNonBatchedOperations(MutationExecutorSingleNonBatched.java:47)
	at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.execute(AbstractMutationExecutor.java:55)
	at org.hibernate.persister.entity.mutation.InsertCoordinatorStandard.doStaticInserts(InsertCoordinatorStandard.java:194)
	at org.hibernate.persister.entity.mutation.InsertCoordinatorStandard.coordinateInsert(InsertCoordinatorStandard.java:132)
	at org.hibernate.persister.entity.mutation.InsertCoordinatorStandard.insert(InsertCoordinatorStandard.java:95)
	at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:85)
	at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:682)
	at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:293)
	at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:274)
	at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:324)
	at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:394)
	at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:308)
	at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:224)
	at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:136)
	at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:177)
	at org.hibernate.event.internal.DefaultPersistEventListener.persist(DefaultPersistEventListener.java:95)
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:79)
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:55)
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127)
	at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:761)
	at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:745)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:320)
	at jdk.proxy2/jdk.proxy2.$Proxy252.persist(Unknown Source)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:654)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:360)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:277)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:515)
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:284)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:734)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:174)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:149)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)

Reason analyze (If you can)

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

I'm using SpringBoot 3.5.6.

shardingsphere.yml

mode:
  type: Standalone
  repository:
    type: JDBC
dataSources:
  main:
    #TODO: Hikari settings can be set here if needed
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5433/postgres?reWriteBatchedInserts=true
    username: postgres
    password: postgres
  shard-1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5434/postgres?reWriteBatchedInserts=true
    username: postgres
    password: postgres
  shard-2:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5435/postgres?reWriteBatchedInserts=true
    username: postgres
    password: postgres

rules:
  - !SINGLE
    tables:
      - main.outbox_dm_tables.*
      - main.shardconfig
    defaultDataSource: main
   
  - !SHARDING
    defaultTableStrategy:
      none:
    tables:
      tenant_info:
        actualDataNodes: main.tenant_info,shard-1.tenant_info,shard-2.tenant_info
        databaseStrategy:
          standard:
            shardingColumn: tenantId
            shardingAlgorithmName: tenant-to-database

    shardingAlgorithms:
      tenant-to-database:
        type: CLASS_BASED
        props:
          strategy: STANDARD
          algorithmClassName: com.roche.infinity.qcadvancedservice.config.dbsharding.TenantShardingAlgorithm
props:
  sql-show: true

MyTestEntity

@Entity
@Table(name = "mytestentity", schema = "outbox_dm_tables")
public class MyTestEntity {

    @Id
    @GeneratedValue(
            strategy = GenerationType.IDENTITY
    )
    private Long sequenceNumber;
    @Column(
            nullable = false,
            length = 65535
    )
    private String event;
    @Column(
            nullable = false
    )
    private int partitionIndex;

    public Long getSequenceNumber() {
        return sequenceNumber;
    }

    public void setSequenceNumber(Long sequenceNumber) {
        this.sequenceNumber = sequenceNumber;
    }

    public String getEvent() {
        return event;
    }

    public void setEvent(String event) {
        this.event = event;
    }

    public int getPartitionIndex() {
        return partitionIndex;
    }

    public void setPartitionIndex(int partitionIndex) {
        this.partitionIndex = partitionIndex;
    }
}

MyTestEntityRepository

@Repository
public interface MyTestEntityRepository extends CrudRepository<MyTestEntity, Long> {
}

Create table

CREATE TABLE IF NOT EXISTS outbox_dm_tables.mytestentity (
  event character varying(65535) NOT NULL,
  partitionindex integer NOT NULL,
  sequencenumber bigint NOT NULL GENERATED ALWAYS AS IDENTITY (
      SEQUENCE NAME outbox_dm_tables.mytestentity_sequencenumber_seq INCREMENT 1 START 1 MINVALUE 1 NO MAXVALUE CACHE 1
  ),
  CONSTRAINT mytestentity_pkey PRIMARY KEY (sequencenumber)
);

Inserting entity

 MyTestEntity myTestEntity = new MyTestEntity();
 myTestEntity.setEvent("{my-event-as-string}");
 myTestEntity.setPartitionIndex(1);
 myTestEntityRepository.save(myTestEntity);

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

[jpenalver10]

[terrymanu]

Problem Understanding

• In ShardingSphere-JDBC 5.5.2 with PostgreSQL, a SINGLE-table (main.outbox_dm_tables.mytestentity) insert via Spring Data JPA and @GeneratedValue(strategy = IDENTITY) fails: Hibernate throws NumberFormatException when reading the generated key; the value returned as the “generated key” is actually the business column {my-event-as-string}.
• The SINGLE rule config is standard per docs; the expected behavior is to relay the database’s identity key.

Root Cause

• In single-table scenarios without ShardingSphere key generation, GeneratedKeyContext is empty, so ShardingSphereStatement#getGeneratedKeys / ShardingSpherePreparedStatement#getGeneratedKeys fall back to iterating underlying statements’ getGeneratedKeys() and unconditionally read column 1 via resultSet.getObject(1).
• PostgreSQL returns the whole row for RETURN_GENERATED_KEYS (equivalent to RETURNING *); column 1 is the first table column event, not the identity sequencenumber. This wrong value is wrapped into GeneratedKeysResultSet and later parsed as long, causing the NumberFormatException.
• The logic assumes “generated key is always the first column” and does not locate the generated key by column name or metadata, so it breaks on PostgreSQL’s result column ordering.

Analysis

• Usage is correct; the failure stems from ShardingSphere’s generated-key aggregation logic being incompatible with PostgreSQL’s multi-column getGeneratedKeys() result.
• Proper behavior: when a generated-key column name is known (from GeneratedKeyContext or dialect GeneratedKeyOption), resolve the column index from ResultSetMetaData/labels and read that column; only fall back to index 1 if no match exists. This keeps compatibility with both “only key returned” drivers and PostgreSQL’s “whole row” behavior.
• GeneratedKeysResultSet itself only exposes values; the bug is in how values are collected. The fix should live in ShardingSphereStatement/ShardingSpherePreparedStatement when assembling generated key values, avoiding hardcoded column 1.

Conclusion

• This is a ShardingSphere-JDBC defect for PostgreSQL SINGLE-table + IDENTITY inserts, not a user configuration issue. We should fix getGeneratedKeys fallback to fetch by generated-key column name using metadata instead of always column 1, and add PostgreSQL SINGLE-table RETURN_GENERATED_KEYS regression tests to verify correct key value/type.
• We warmly invite community contributors to submit a PR with the fix and accompanying tests; your help will make the PostgreSQL path more robust for everyone.

[aviralgarg05]

I would like to solve the issue, please assign me