spring-projects
Saving a `null` Postgres enum results in a varchar value
Hi, I have an issue with enums using spring-data-jdbc.
I have a Java enum type:
public enum Sentiment {
POSITIVE,
NEGATIVE,
NEUTRAL
}
And a corresponding enum type on one of my tables:
insights=> \dT+ sentiment
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+-----------+---------------+------+----------+-------------+-------------------+-------------
public | sentiment | sentiment | 4 | POSITIVE+| owner | |
| | | | NEGATIVE+| | |
| | | | NEUTRAL | | |
(1 row)
I have this Entity (see the Sentiment field):
@Table("responses")
public record Response(
@Id UUID id,
UUID roundId,
UUID cardTemplateId,
UUID cardId,
UUID answerOptionTemplateId,
UUID answerOptionId,
String text,
Sentiment sentiment) {}
If I call save on the repository:
responseRepository.save(new RoundResponse(Id, roundId, UUID.randomUUID(), UUID.randomUUID(), UUID.randomUUID(), UUID.randomUUID(), "text", null)); // sentiment is null
I get the error:
Caused by: org.postgresql.util.PSQLException: ERROR: column "sentiment" is of type sentiment but expression is of type character varying
I have some Converters for handling the enums but because the value is null it doesn't reach these converters. It seems to me that the spring-data-jdbc library must be at some point trying to convert the null value to a varchar? (maybe by default?)
This is not what I would expect, I would expect if the value I am inserting is null and my enum type postgres field is nullable then null should be used rather than varchar :)
Let me know if you need any more information, thank you!
I think it isn't Spring Data JDBC that tries to convert something here. Instead it seems to tell Postgres, this null value is a VARCHAR` and Postgres is overwhelmed by the task to convert it to an enum ...
Yeah I debugged into the JdbcTemplate and could see it was trying to insert with sqlType = 12 = varchar. Not sure at what point spring-data-jdbc is telling Postgres is is a varchar though
@schauder Do you think this is a bug? It seems like the MappingJdbcConverter doesn't recognise the type so converts it to a String.
Should there be some function further up the call stack that handles nulls whereby if the value is null then don't try and type it and insert as a null in the DB?
Yes, I do think this is a bug. Could you provide a full reproducer, preferable as github repo?
@schauder Thank you :) here is an app that reproduces it https://github.com/mattmcc-attest/animal
Hi, everyone! @schauder Are you sure that this is a bug? I wrote some tests in this repo like this
@Test
void enumParameterIsNotNullReturnCorrectSqlTypeFromConverter() {
WithEnumEntity entity = new WithEnumEntity(23L, DummyEnum.ONE);
SqlParametersFactory sqlParametersFactory = createSqlParametersFactoryWithConverters(
singletonList(WritingEnumConverter.INSTANCE));
SqlIdentifierParameterSource sqlParameterSource = sqlParametersFactory.forInsert(entity,
WithEnumEntity.class, Identifier.empty(), IdValueSource.PROVIDED);
assertThat(sqlParameterSource.getValue("id")).isEqualTo(23L);
assertThat(sqlParameterSource.getValue("dummy_enum")).isEqualTo(DummyEnum.ONE.name());
assertThat(sqlParameterSource.getSqlType("dummy_enum")).isEqualTo(1111);
}
@Test
void enumParameterIsNullReturnCorrectSqlTypeFromConverter() {
WithEnumEntity entity = new WithEnumEntity(23L, null);
SqlParametersFactory sqlParametersFactory = createSqlParametersFactoryWithConverters(
singletonList(WritingEnumConverter.INSTANCE));
SqlIdentifierParameterSource sqlParameterSource = sqlParametersFactory.forInsert(entity,
WithEnumEntity.class, Identifier.empty(), IdValueSource.PROVIDED);
assertThat(sqlParameterSource.getValue("id")).isEqualTo(23L);
assertThat(sqlParameterSource.getSqlType("dummy_enum")).isEqualTo(1111);
assertThat(sqlParameterSource.getValue("dummy_enum")).isNull();
}
@WritingConverter
enum WritingEnumConverter implements Converter<DummyEnum, JdbcValue> {
INSTANCE;
@Override
public JdbcValue convert(DummyEnum source) {
return JdbcValue.of(source.name().toUpperCase(), JDBCType.OTHER);
}
}
The first test is correct because inside converters we have type information by which can find custom converter WritingEnumConverter and convert it.
The second test is incorrect because we don't know what type information for value we have. But even if we do this, for example like this (addConvertedNullableProperty is my custom method)
if (value == null) {
TypeInformation<?> typeInformation = property.getTypeInformation();
addConvertedNullableProperty(parameters, property, typeInformation, paramName);
} else {
addConvertedPropertyValue(parameters, property, value, paramName);
}
And added some new logic where we can find a specific converter, as a result of this convert null object -> null JdbcValue.
Also in the method's documentation by Converter#convert method source must be not null. Hence, MappingJdbcConverter will choose default converter by target type (is a String)
As a new approach, I can suggest that you set the default JDBC Type.NULL for null values. I tested this approach on https://github.com/mattmcc-attest/animal the repositories and animal were saved correctly.
I can push changes and a few tests so that you can see this approach :)
Ran into this issue again, is there a known workaround?
Previously I added a sort of NULL option to the enum but now that I am using the database I am not a fan of this, lots of rows have a value now where I would prefer a null. For the most recent problem, I plan to make my column TEXT rather than use enums, perhaps with a check constraint. Again this isn't ideal but means I can have null values in that column
Faced the same issue and made the following workaround.
Created the annotation:
@Documented
@Retention(RUNTIME)
@Target(value = { FIELD })
public @interface ExplicitSqlType {
JDBCType value();
}
Made a custom converter:
static class CustomJdbcConverter extends MappingJdbcConverter {
CustomJdbcConverter(
RelationalMappingContext context,
RelationResolver relationResolver, CustomConversions conversions,
JdbcTypeFactory typeFactory
) {
super(context, relationResolver, conversions, typeFactory);
}
@NonNull
@Override
public SQLType getTargetSqlType(@NonNull RelationalPersistentProperty property) {
ExplicitSqlType annotation = property.findAnnotation(ExplicitSqlType.class);
if (annotation != null) return annotation.value();
return super.getTargetSqlType(property);
}
}
Registered it in my custom JdbcConfiguration:
@RequiredArgsConstructor
@Configuration(proxyBeanMethods = false)
public class JdbcConfiguration extends AbstractJdbcConfiguration {
private final ApplicationContext applicationContext;
//From SpringBootJdbcConfiguration
@NonNull
@Override
protected Set<Class<?>> getInitialEntitySet() throws ClassNotFoundException {
return new EntityScanner(applicationContext).scan(Table.class);
}
@Bean
@NonNull
@Override
public JdbcConverter jdbcConverter(
@NonNull JdbcMappingContext mappingContext, @NonNull NamedParameterJdbcOperations operations,
@Lazy @NonNull RelationResolver relationResolver, @NonNull JdbcCustomConversions conversions, @NonNull Dialect dialect
) {
//From AbstractJdbcConfiguration
org.springframework.data.jdbc.core.dialect.JdbcArrayColumns arrayColumns = dialect instanceof JdbcDialect jd
? jd.getArraySupport()
: JdbcArrayColumns.DefaultSupport.INSTANCE;
DefaultJdbcTypeFactory jdbcTypeFactory = new DefaultJdbcTypeFactory(operations.getJdbcOperations(), arrayColumns);
return new CustomJdbcConverter(mappingContext, relationResolver, conversions, jdbcTypeFactory);
}
}
Marked my nullable enum fields with the annotation:
@ExplicitSqlType(JDBCType.OTHER)
private OrdinalNumber number;
Also with such approach WritingConverter is no longer needed for enums.
I have exactly the same problem. I have a custom WritingConverter for the enum that uses JDBCType.Other, but it's not invoked on null instances for some reason.
As a workaround on Postgres I'm using
CREATE CAST (varchar AS my_enum) WITH INOUT AS IMPLICIT;
this makes WritingConverter redundant
