When using Single Query Loading, mysqlType is interpreted as ENUM(247)

[sjh836]

The top screenshot doesn't use Single Query Loading, while the bottom does.

CREATE TABLE `car` (
  `car_no` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` ENUM('SEDAN','SUV','VAN','TRUCK') NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (`car_no`)
);

@Table("car")
@Getter
@ToString
public class CarEntity {
	@Id
	private int carNo;
	private CarType type;
	@CreatedDate
	private LocalDateTime createdAt;

	@PersistenceCreator
	public CarEntity(int carNo, CarType type, LocalDateTime createdAt) {
		this.carNo = carNo;
		this.type = type;
		this.createdAt = createdAt;
	}
}

public enum CarType {
	SEDAN,
	SUV,
	VAN,
	TRUCK,
}

Single Query Loading interprets the MySQL type enum directly, resulting in the following error.

As of the current date, I'm using the latest version: 3.5.4.

org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [SELECT case when rn_car_1 = rn THEN c_type_4 else null end as c_type_4, case when rn_car_1 = rn THEN c_created_at_5 else null end as c_created_at_5, c_car_no_3 FROM (SELECT c_type_4, c_created_at_5, rn_car_1, c_car_no_3, rn_car_1 AS rn FROM (SELECT 1 AS rn_car_1, 1 AS rc_car_2, `car`.`car_no` AS c_car_no_3, `car`.`type` AS c_type_4, `car`.`created_at` AS c_created_at_5 FROM `car` WHERE `car`.`car_no` = ?) t_car_6) main ORDER BY c_car_no_3, rn]; Unknown type '247' in column '0' of '3' in binary-encoded result set.

	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:124)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:178)
	at org.springframework.data.jdbc.core.convert.AggregateReader.doFind(AggregateReader.java:167)
	at org.springframework.data.jdbc.core.convert.AggregateReader.findOne(AggregateReader.java:115)
	at org.springframework.data.jdbc.core.convert.AggregateReader.findById(AggregateReader.java:102)
	at org.springframework.data.jdbc.core.convert.SingleQueryDataAccessStrategy.findById(SingleQueryDataAccessStrategy.java:53)
	at org.springframework.data.jdbc.core.convert.SingleQueryFallbackDataAccessStrategy.findById(SingleQueryFallbackDataAccessStrategy.java:61)
	at org.springframework.data.jdbc.core.JdbcAggregateTemplate.findById(JdbcAggregateTemplate.java:290)
	at org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findById(SimpleJdbcRepository.java:80)
	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:359)
	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)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
	at jdk.proxy1/jdk.proxy1.$Proxy89.findById(Unknown Source)
	at com.myproject.infra.jdbc.car.CarReadJdbcRepositoryTest.test(CarReadJdbcRepositoryTest.java:34)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
Caused by: java.sql.SQLException: Unknown type '247' in column '0' of '3' in binary-encoded result set.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:114)
	at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:612)
	at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:320)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1056)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:732)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
	... 34 more

[schauder]

Sorry, I wasn't able to reproduce the issue based on the information you provided.

Please provide a Minimimal Reproducable Example, preferable as a Github repository. Make sure to include the database, either as an in memory database or if that is not possible using Testcontainers.

Also: what exactly are the screenshots showing?

[sjh836]

This is my jdbc configuration.

	@Bean
	public JdbcMappingContext jdbcMappingContext(Optional<NamingStrategy> namingStrategy,
		JdbcCustomConversions customConversions, RelationalManagedTypes jdbcManagedTypes) {
		JdbcMappingContext jdbcMappingContext = super.jdbcMappingContext(namingStrategy, customConversions,
			jdbcManagedTypes);
		/**
		 * With Single Query Loading, even if a main entity has N subentities related to it, they are loaded with a single query.
		 * @see https://github.com/spring-projects/spring-data-relational/issues/1445
		 *
		 * Limited support in version 3.2.0-M2
		 * @see https://docs.spring.io/spring-data/relational/reference/jdbc/entity-persistence.html#jdbc.loading-aggregates
		 * Only available with CrudRepository's findAll, findAll, and findById methods.
		 * Only supports up to one subentity in a 1:N relationship.
		 */
		jdbcMappingContext.setSingleQueryLoadingEnabled(true);
		return jdbcMappingContext;
	}

This junit test succeeds if SingleQueryLoading is not used, and fails if it is.

@SpringBootTest
@Transactional
public class CarReadJdbcRepositoryTest {
	@Autowired
	private CarJdbcRepository carJdbcRepository;

	@Test
	@DisplayName("When using SingleQueryLoading, you cannot use mysql enum type....T-T")
	void singleQueryLoading_bug() {
		// given
		final CarCreateForm form = CarCreateForm.builder().type(CarType.SUV).build();
		long key = carJdbcRepository.insert(form);

		// when
		CarEntity result = carJdbcRepository.findById((int)key).get();

		// then
		System.out.println(result);
		assertNotNull(result);
		assertEquals(key, result.getCarNo());
		/**
		 * @see https://github.com/spring-projects/spring-data-relational/issues/2154
		 *
		 * Spring Data JDBC cannot convert MySQL types to Strings in SingleQueryLoading mode due to column name manipulation, subqueries, etc.
		 * 1. Therefore, either avoid using enums at all, use varchars, and manually convert Entity -> VO, or
		 * 2. Avoid using SingleQueryLoading altogether.
		 */
	}
}

• jdbcMappingContext.setSingleQueryLoadingEnabled(true);
  • 
  • 
• jdbcMappingContext.setSingleQueryLoadingEnabled(false);
  • 
  • 

---

I've been debugging this issue hard, and when a field in a Java object is an enum (car.type in my case), the MySQL type is mapped differently when using the function (CHAR=254 vs ENUM=247).