@Query with "not in" not work with empty List parameter

개요

이번 글에서는 프로젝트 진행 중 겪었던 이슈 중 하나인 @Query에서 "NOT IN"이 빈 리스트 파라미터와 함께 동작하지 않는 문제에 대해 작성하려 합니다. 이 예외 상황을 해결하는 과정과 함께, 유사한 문제 발생 시 참고할 수 있도록 정리해 보았습니다.

문제 상황

아래는 사용자의 선호 장르의 영화 중에 사용자가 리뷰, 좋아요, 컬렉션, 평점을 남기지 않고 TOP 10에 속하지 않는 영화를 조회하는 로직입니다.

@Query("SELECT m " +
        "FROM Movie m " +
        "JOIN m.movieGenres mg " +
        "LEFT JOIN Review r ON r.movie.id = m.id AND r.user.id = :userId " +
        "LEFT JOIN MovieLikes ml ON ml.movie.id = m.id AND ml.user.id = :userId " +
        "LEFT JOIN CollectionMovie cm ON cm.movie.id = m.id " +
        "LEFT JOIN Collection c ON c.id = cm.collection.id AND c.user.id = :userId " +
        "LEFT JOIN MovieRatings mr ON mr.movie.id = m.id AND mr.user.id = :userId " +
        "WHERE m.isDeleted = false " +
        "AND mg.genre.id IN :genreIds " +
        "AND r.id IS NULL " +
        "AND ml.id IS NULL " +
        "AND c.id IS NULL " +
        "AND mr.id IS NULL " +
        "AND m.id NOT IN :top10MovieIds")
List<Movie> findMoviesByPreferredGenresAndNotInteracted(
        @Param("genreIds") List<Long> genreIds,
        @Param("userId") Long userId,
        @Param("top10MovieIds") List<Long> top10MovieIds
);

이러한 로직이 잘 작동하는지 테스트하기 위해 아래와 같이 테스트 코드를 짰고 실행했습니다.

테스트 코드

@Test
@DisplayName("사용자의 선호 장르의 영화 중에 사용자가 리뷰, 좋아요, 컬렉션, 평점을 남기지 않은 영화를 조회한다.")
void findMoviesByPreferredGenresAndNotInteracted_whenValidConditions_returnsMovies() {
    // given
    User user = createUser("username", "password", Role.ROLE_ADULT_USER, Provider.NAVER, "nickname",
            "https://profile.url");
    Genre genre1 = createGenre(GenreType.ACTION);
    Genre genre2 = createGenre(GenreType.SF);
    Movie movie1 = createMovie("영화1", "줄거리1", LocalDate.now(), 120, "https://poster1.url", FilmRatings.ALL);
    Movie movie2 = createMovie("영화2", "줄거리2", LocalDate.now(), 120, "https://poster2.url", FilmRatings.ALL);
    Movie movie3 = createMovie("영화3", "줄거리3", LocalDate.now(), 120, "https://poster3.url", FilmRatings.ALL);
    Movie movie4 = createMovie("영화4", "줄거리4", LocalDate.now(), 120, "https://poster3.url", FilmRatings.ALL);
    Movie movie5 = createMovie("영화5", "줄거리5", LocalDate.now(), 120, "https://poster3.url", FilmRatings.ALL);
    Movie movie6 = createMovie("영화5", "줄거리5", LocalDate.now(), 120, "https://poster3.url", FilmRatings.ALL);
    MovieGenre movieGenre1 = createMovieGenre(movie1, genre1);
    MovieGenre movieGenre2 = createMovieGenre(movie2, genre1);
    MovieGenre movieGenre3 = createMovieGenre(movie3, genre2);
    MovieGenre movieGenre4 = createMovieGenre(movie4, genre2);
    MovieGenre movieGenre5 = createMovieGenre(movie5, genre1);
    Review review = createReview("좋은 영화", movie1, user);
    MovieLikes movieLikes = createMovieLikes(movie2, user);
    MovieRatings movieRatings = createMovieRatings(movie3, user, BigDecimal.valueOf(3.0));
    Collection collection = createCollection("컬렉션1", "컬렉션 설명", user);
    CollectionMovie collectionMovie = createCollectionMovie(movie4, collection);

    // when
    userRepository.save(user);
    genreRepository.saveAll(List.of(genre1, genre2));
    movieRepository.saveAll(List.of(movie1, movie2, movie3, movie4, movie5, movie6));
    movieGenreRepository.saveAll(List.of(movieGenre1, movieGenre2, movieGenre3, movieGenre4, movieGenre5));
    reviewRepository.save(review);
    movieLikesRepository.save(movieLikes);
    movieRatingsRepository.save(movieRatings);
    collectionRepository.save(collection);
    collectionMovieRepository.save(collectionMovie);

    em.flush();
    em.clear();

    List<Long> genreIds = List.of(genre1.getId(), genre2.getId());
    List<Long> top10MovieIds = List.of();
    List<Movie> result = movieRepository.findMoviesByPreferredGenresAndNotInteracted(
            genreIds,
            user.getId(),
            top10MovieIds,
            null
    );

    // then
    assertThat(result)
            .hasSize(1)
            .extracting("title", "likeCounts", "plot")
            .containsExactlyInAnyOrder(
                    tuple(movie5.getTitle(), movie5.getLikeCounts(), movie5.getPlot())
            );
}

실행결과는 아래와 같습니다.

movie1 ~ 6에서 1 ~ 4는 사용자가 리뷰, 좋아요, 평점, 컬렉션에 저장을 했고 movie6은 사용자의 선호 장르에 속하지 않는 상황입니다. 따라서 movie5가 조회돼서 실제 조회되는 개수를 1로 생각을 했지만 테스트 결과는 0인 상황이었습니다.

발생쿼리

Hibernate: 
    select
        m1_0.movie_id,
        m1_0.average_rating,
        m1_0.created_at,
        m1_0.deleted_at,
        m1_0.film_ratings,
        m1_0.is_deleted,
        m1_0.like_counts,
        m1_0.last_modified_at,
        m1_0.plot,
        m1_0.poster_url,
        m1_0.rating_counts,
        m1_0.release_date,
        m1_0.runtime,
        m1_0.title 
    from
        movie m1_0 
    join
        movie_genre m2_0 
            on m1_0.movie_id=m2_0.movie_id 
    left join
        review r1_0 
            on r1_0.movie_id=m1_0.movie_id 
            and r1_0.member_id=? 
    left join
        movie_likes m4_0 
            on m4_0.movie_id=m1_0.movie_id 
            and m4_0.member_id=? 
    left join
        collection_movie c1_0 
            on c1_0.movie_id=m1_0.movie_id 
    left join
        collection c2_0 
            on c2_0.collection_id=c1_0.collection_id 
            and c2_0.member_id=? 
    left join
        movie_ratings m7_0 
            on m7_0.movie_id=m1_0.movie_id 
            and m7_0.member_id=? 
    where
        m1_0.is_deleted=0 
        and m2_0.genre_id in (?,?) 
        and r1_0.review_id is null 
        and m4_0.movie_likes_id is null 
        and c2_0.collection_id is null 
        and m7_0.movie_ratings_id is null 
        and 1=0

문제가 무엇인지 확인하기 위해 우선 실행된 쿼리를 살펴보았습니다. where 절을 확인해 보니 조건들은 잘 들어가 있었지만, 맨 마지막에 1=0이라는 부분이 존재했습니다. 이 부분에는 원래 m.id NOT IN :top10MovieIds와 같은, top10에 속하지 않는 영화를 조회하는 NOT IN 조건이 있어야 합니다. 하지만 예상과 달리 1=0이 들어가 있어 의아했습니다. 1=0는 SQL 쿼리에서 항상 거짓(False)이 되는 조건입니다. 따라서, 이 조건이 WHERE 절에 포함되면 쿼리는 항상 결과를 반환하지 않습니다.

 

혹시 몰라 영화에 대한 좋아요와 별점 데이터를 저장하지 않은 상태로 테스트를 진행해 보았습니다.

이 경우, movie2와 movie3은 명확하게 조회되어야 하며, 테스트 결과 값이 2 이상이어야 합니다.

하지만 결과는 여전히 동일했고, 실제 반환된 값은 0으로 예상과 일치하지 않았습니다.

        Review review = createReview("좋은 영화", movie1, user);
//        MovieLikes movieLikes = createMovieLikes(movie2, user);
//        MovieRatings movieRatings = createMovieRatings(movie3, user, BigDecimal.valueOf(3.0));
        Collection collection = createCollection("컬렉션1", "컬렉션 설명", user);
        CollectionMovie collectionMovie = createCollectionMovie(movie4, collection);

        // when
        userRepository.save(user);
        genreRepository.saveAll(List.of(genre1, genre2));
        movieRepository.saveAll(List.of(movie1, movie2, movie3, movie4, movie5, movie6));
        movieGenreRepository.saveAll(List.of(movieGenre1, movieGenre2, movieGenre3, movieGenre4, movieGenre5));
        reviewRepository.save(review);
//        movieLikesRepository.save(movieLikes);
//        movieRatingsRepository.save(movieRatings);
        collectionRepository.save(collection);
        collectionMovieRepository.save(collectionMovie);

여기까지 실패하고 나서 List<Long> top10MovieIds = List.of(); 처럼 빈 리스트를 넣어주는 게 문제인가 라는 생각이 들었습니다. 그래서 아래와 같이 top10MovieIds에 값을 넣어서 테스트를 실행했습니다.

        List<Long> genreIds = List.of(genre1.getId(), genre2.getId());
        List<Long> top10MovieIds = List.of(movie6.getId());
        List<Movie> result = movieRepository.findMoviesByPreferredGenresAndNotInteracted(
                genreIds,
                user.getId(),
                top10MovieIds
        );

        // then
        assertThat(result)
                .hasSize(1)
                .extracting("title", "likeCounts", "plot")
                .containsExactlyInAnyOrder(
                        tuple(movie5.getTitle(), movie5.getLikeCounts(), movie5.getPlot())
                );

결과는 성공이었고 실행된 쿼리에도 NOT IN 조건이 잘 적용된 것을 확인했습니다.

JPQL NOT IN의 한계

NOT IN 조건에 빈 리스트를 넣었을 때 쿼리가 제대로 작동하지 않는 이유가 궁금해 관련 내용을 찾아보던 중, 저와 비슷한 이슈를 겪은 글을 발견했습니다. https://github.com/spring-projects/spring-data-jpa/issues/3078

 

위 GitHub 이슈는 @Query 어노테이션을 사용해 JPQL 쿼리를 작성할 때, NOT IN 조건에 빈 리스트(List.of())를 전달하면 예상과 다른 동작이 발생하는 문제에 대해 얘기하고 있습니다.

 

이슈를 겪은 당사자는 아래와 같이 JPQL 쿼리를 작성했고 excludeIds로 빈 리스트(List.of())를 전달하면 모든 데이터를 반환해야 한다고 예상했습니다.

@Query("""
    SELECT c
    FROM City c
    WHERE c.id NOT IN (:excludeIds)
""")
List<City> findCityWithExclusion1(List<String> excludeIds);

예상 동작은 빈 리스트가 전달되면 조건 NOT IN (:excludeIds)는 WHERE 1=1로 처리되어야 하며, 모든 데이터가 반환되어야 한다고 예상했지만 실제 동작은 Hibernate가 SQL로 변환하면서 WHERE 1=0 조건을 생성하여 결과가 항상 빈 리스트로 반환 됐습니다.

실제 SQL 변환

JPQL 쿼리:

SELECT c FROM City c WHERE c.id NOT IN (:excludeIds)

Hibernate가 변환한 SQL:

SELECT c1_0.id, c1_0.country_code, c1_0.label, c1_0.postal_code 
FROM city c1_0 
WHERE 1=0

이로 인해, 쿼리가 빈 결과를 반환했습니다.

spring-data-jpa 팀은 해당 이슈에 대한 원인을 아래와 같이 설명했습니다.

원인

1. Hibernate 동작 방식:
   • Hibernate는 NOT IN 조건에 빈 리스트를 전달받으면 쿼리를 WHERE 1=0으로 변환하여 결과를 항상 빈 리스트로 만듦.
   • 이는 Hibernate의 설계 의도에 따른 동작으로, 성능 최적화를 목적으로 구현된 것으로 보임.
2. JPQL과 SQL 간의 변환 문제:
   • JPQL은 추상화된 언어이며, 이를 실제 SQL로 변환하는 과정에서 Hibernate가 빈 리스트를 특수한 경우로 처리.
3. Spring Data JPA의 한계:
   • Spring Data JPA는 Hibernate 위에서 동작하므로, 이 문제는 JPA 또는 Hibernate에서 발생하는 동작과 일치.

이슈는 Hibernate에서 NOT IN 조건과 빈 리스트를 처리하는 방식의 한계에서 발생.

가장 적절한 해결책은 NOT (...)을 사용하는 대체 JPQL을 작성하거나, IS EMPTY 조건을 명시적으로 추가하는 방식.

문제 해결

결론적으로 제가 해결한 방식은 NOT IN을 쓰는 게 아니라 NOT EXISTS를 사용했습니다.

기존 코드를 아래와 같이 수정했고 테스트 코드를 실행했습니다.

@Query(""" 
                SELECT m
                FROM Movie m
                JOIN m.movieGenres mg
                LEFT JOIN Review r ON r.movie.id = m.id AND r.user.id = :userId
                LEFT JOIN MovieLikes ml ON ml.movie.id = m.id AND ml.user.id = :userId
                LEFT JOIN CollectionMovie cm ON cm.movie.id = m.id
                LEFT JOIN Collection c ON c.id = cm.collection.id AND c.user.id = :userId
                LEFT JOIN MovieRatings mr ON mr.movie.id = m.id AND mr.user.id = :userId
                WHERE m.isDeleted = false
                    AND mg.genre.id IN :genreIds
                    AND r.id IS NULL
                    AND ml.id IS NULL
                    AND c.id IS NULL
                    AND mr.id IS NULL
                    AND NOT EXISTS (
                            SELECT 1
                            FROM Movie topMovies
                            WHERE topMovies.id IN :top10MovieIds
                            AND topMovies.id = m.id
                        )
        """)
List<Movie> findMoviesByPreferredGenresAndNotInteracted(
        @Param("genreIds") List<Long> genreIds,
        @Param("userId") Long userId,
        @Param("top10MovieIds") List<Long> top10MovieIds
);

top10MovieIds에 빈값을 주고 테스트를 해보니 아래와 같이 잘 통과된 것을 확인했습니다.

그리고 값을 넣어도 테스트가 잘 통과 됐습니다.

마무리하며

이번 글에서는 앞서 설명한 예외 상황을 해결하는 과정과 함께, 유사한 문제가 발생했을 때 참고할 수 있도록 정리해 보았습니다.

Spring Data JPA를 활용하면 쿼리에 종속되지 않고 편리하게 개발할 수 있다는 장점이 있지만, Spring Data JPA가 만능은 아니며 한계가 존재한다는 것을 깨달을 수 있는 경험이었습니다.

참고

https://github.com/spring-projects/spring-data-jpa/issues/3078