findById ignores @SQLRestriction annotation when performed in the same transaction as the save

[khaledbaghdadii]

findById ignores @SQLRestriction annotation when performed in the same transaction as the save

[quaff]

@khaledbaghdadii Your test should pass if test method annotated with @Transactional(propagation = Propagation.NOT_SUPPORTED) or call entityManager.flush();entityManager.clear(); after save, without that the entity is in persistence context and not flush to database then findById will return the managed entity not querying database and applying @SQLRestriction.

[schauder]

This is just a classical problem with JPA (not Spring Data JPA). It relies heavily on the cache und doesn't reload reload data as @quaff wrote in his comment. Of course Spring Data could apply some clear or flush magic, but that comes at the very least with a performance penalty and in many cases with surprising behaviour in other places.

Therefore there isn't much we can do about this.

[khaledbaghdadii]

This is a detailed of all the tests created and all of them pass as expected except for the findById when transactions are enabled

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Testcontainers
@ContextConfiguration(initializers = SingletonPostgresDbContainerContextInitializer.class)
class TestItem {
    @Autowired
    private ItemJpaRepository repository;

    @Test
    //This test passes
    void testFindAllById(){
        ItemEntity itemEntity = ItemEntity.builder()
                .count(1)
                .deleted(true)
                .build();
        repository.save(itemEntity);
        List<ItemEntity> itemEntities = repository.findAllById(List.of(itemEntity.getId()));
        Assertions.assertThat(itemEntities).isEmpty();
    }

    @Test
        //This test fails
    void testFindById(){
        ItemEntity itemEntity = ItemEntity.builder()
                .count(1)
                .deleted(true)
                .build();
        repository.save(itemEntity);
        Optional<ItemEntity> itemEntityOptional = repository.findById(itemEntity.getId());
        Assertions.assertThat(itemEntityOptional).isEmpty();
    }

    @Test
    @Transactional(propagation = Propagation.NEVER)
        //This test passes
    void testFindByIdNonTransactional(){
        ItemEntity itemEntity = ItemEntity.builder()
                .count(1)
                .deleted(true)
                .build();
        repository.save(itemEntity);
        Optional<ItemEntity> itemEntityOptional = repository.findById(itemEntity.getId());
        Assertions.assertThat(itemEntityOptional).isEmpty();
    }

    @Test
        //This test passes
    void existsById(){
        ItemEntity itemEntity = ItemEntity.builder()
                .count(1)
                .deleted(true)
                .build();
        repository.save(itemEntity);

        Assertions.assertThat(repository.existsById(itemEntity.getId())).isFalse();
    }

    @Test
        //This test passes
    void findByIdAndCountLessThan(){
        ItemEntity itemEntity = ItemEntity.builder()
                .count(1)
                .deleted(true)
                .build();
        repository.save(itemEntity);
        repository.deleteById(itemEntity.getId());

        Optional<ItemEntity> itemEntityOptional = repository.findByIdAndCountLessThan(itemEntity.getId(),999);
        Assertions.assertThat(itemEntityOptional).isEmpty();

    }
}

[quaff]

Have you tried using @org.hibernate.annotations.SoftDelete instead of @SQLDelete and @SQLRestriction?

[khaledbaghdadii]

This works but there are some cases where I'm using the @SQLRetriction for reasons other than soft delete, and also cases where I need the @SQLDelete(sql = "UPDATE item_entity SET deleted = true WHERE id = ?") without the @SQLRestriction to be able to fetch entities that are soft deleted if they are part of another entity that is not deleted.

I'm gonna rename the issue to match the problem with @SQLRestriction instead of specifically for soft delete cause I don't think this is the correct behaviour anyways if all other JPARepository methods are working fine except for it

[khaledbaghdadii]

I was able to make the test pass but I do belive there's a bug with the behaviour of findById since it is not cosistent with the other JPA methods

[quaff]

I was able to make the test pass but I do belive there's a bug with the behaviour of findById since it is not cosistent with the other JPA methods

findById is simply delegate to entityManager.find(domainType, id) which will find managed entity from persistence context first, if you insist on it's a bug, you should open an issue for Hibernate.

[khaledbaghdadii]

Yeah fair enough, thank you both! I'll close this issue.