Saving a null Postgres enum results in a varchar value

[mattmcc-attest]

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!

[schauder]

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 ...

[mattmcc-attest]

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

[mattmcc-attest]

@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?

[schauder]

Yes, I do think this is a bug. Could you provide a full reproducer, preferable as github repo?

[mattmcc-attest]

@schauder Thank you :) here is an app that reproduces it https://github.com/mattmcc-attest/animal

[mattmcc-attest]

^ is the above useful for you? @schauder

[schauder]

Yes, I think this will do. Just need to find time to look into it.

[mattmcc-attest]

no problem, thanks for your help

[serezakorotaev]

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)

[serezakorotaev]

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 :)