훈동

JPA @Query에서 비트 연산자 (Bitwise AND) 사용하기, 트러블슈팅

donghoony

TL;DR

@Query 어노테이션이 붙은 친구들은 HQL/JPQL로 작성한다. Hibernate 6.0.0 버전부터 $\texttt{BITAND}$ 함수가 추가돼 이를 사용할 수 있다. 단, $\texttt{BITAND}$ 내부에 우리가 함수 인자로 던져주는 값이 들어있다면, 해당 값을 아래와 같이 적절히 캐스팅해 주어야 한다.

@Query("SELECT s FROM Recipe r " +
       "WHERE BITAND(r.cookwares, CAST(:cookwares as long)) =:cookwares")
List<Recipe> findAllRecipesByCookwaresContaining(Long cookwares);

비트 연산

교내에서 진행했던 해커톤에서 비트 연산한 값을 기준으로 데이터베이스에 쿼리를 보내야 할 일이 생겼다. 간단한 요리 레시피들을 필터검색할 때, 특정 조리도구들을 포함한 레시피들을 알아내야 했다.

조리도구의 양이 많지 않았어서 조리도구 하나하나를 비트로 하는 $\texttt{long}$ 타입의 값을 두어 이를 해결하고자 했다. 예를 들어, 전자레인지, 프라이팬, 냄비 등 조리도구들을 이진수로 나타냈을 때 각 비트에 대응하도록 하는 것이다. $\texttt{001}$이라면 냄비만 사용한 레시피이고, $\texttt{110}$이라면 전자레인지와 프라이팬을 사용하는 레시피라는 의미이다.

이렇게 두면 쿼리는 아래와 같이 굉장히 간단한 방법으로 날릴 수 있다. 만약 내가 전자레인지를 포함하는 레시피들을 가져오고 싶다면 다음과 같이 작성하면 된다. 전자레인지는 오른쪽에서 세 번째 비트에 해당하므로, 10진수로 나타내면 $\texttt{4}$이다.

SELECT * FROM RECIPE WHERE RECIPE.COOKWARE & 4 = 4;

즉, 쿼리로 보내는 조리기구들이 모두 포함되어 있어야 하므로, 레시피의 조리기구와 비트 AND 연산 ($\&$)을 했을 때 쿼리로 보낸 조리기구 비트가 모두 $\texttt{1}$이면 된다. 일반화하자면 아래와 같다.

SELECT * FROM RECIPE 
WHERE RECIPE.COOKWARE & {COOKWARE_IN_BITS} = {COOKWARE_IN_BITS};

이 포스트에서는 내가 이 문제를 해결하기 위해 떠난 여정을 기록한다. 무박2일 해커톤 일정 중 22시부터 04시까지 머리를 싸매가며 힘겹게 해결한 문제라서, 블로그에 공유하고자 한다.

사고의 흐름

이 사고의 흐름은 다소 심신이 미약한 상태에서 벌어진 일로… 다시는 이런 일이 발생하면 안 되겠다 싶어서 부끄럽지만 작성하게 되었습니다. 너그럽게 봐주시면 감사하겠습니다 🙌🏻

1. @Query 어노테이션에서 & 연산을 사용해 보자!

위 SQL 식은 얼마나 직관적인가! 그냥 해당 식을 @Query 어노테이션에 꽂아넣으면 될 것 같다. 처음에 시도했던 방법은 위 SQL 식을 JPQL스럽게 짜 넣어보는 것이다. 지금 생각해보면 아무 원리도 모른 채 무지성으로 넣었던 코드이다. ‘내 의도대로 실행될 것인가?’ 에 대한 머릿속의 질문은 내려둔 채 ‘돌아가면 됐지~’ 라는 마인드로 작성했던 것 같아서 더 무섭다.

@Query("SELECT s FROM Recipe r " +
       "WHERE r.cookwares & :cookwares =:cookwares")
List<Recipe> findAllRecipesByCookwaresContaining(Long cookwares);

그리고 테스트를 돌려 보면 쿼리를 파싱하지도 못하는 모습을 보인다. 애초에 IDE에서도 해당 쿼리 메시지에 &를 지원하지 않아 빨간 줄이 그어져 있다.

2. 네이티브 쿼리로 작성해 보자!
@Query(value = 
    "SELECT * from Recipe " +
    "WHERE Recipe.cookwares & :cookwares =:cookwares", nativeQuery = true)
List<Recipe> findAllNativeQuery(Long cookwares);

네이티브 쿼리는 SQL문을 그대로 옮겨다쓴 거니까 그대로 되겠지? 라는 생각에서 시작되었다. 하지만 단순히 레시피 조리기구만 필터링하는 게 아니라, 이외의 것들도 꽤 많아서 지저분해지기 시작했다.

이보다 간편하게 작성하는 법을 결국 찾지 못했다. 돌고 돌아 네이티브 쿼리를 시도해보았지만, 해결할 수 없었다. JPQL/HQL과 같이 파싱을 하지 못하고 에러를 뱉었다. 이는 테스트 서버에서 실행중인 H2 서버에서 &를 지원하지 않기 때문이었다.

3. H2가 알아들을 수 있도록 function을 만들고 등록하자!

그럼 H2가 지원할 수 있도록 함수를 만들어주면 어떨까? 라는 생각이 따라붙었다. 하지만 우리 서버는 테스트할 때에만 H2를 사용하지, 실제 개발 서버에서는 MySQL이 작동하고있어 차이가 있었다.. 이 링크에서는 MySQL을 위해 사용자 지정 함수를 등록하는 방법이 나와 있었는데, 이를 따라가다 보니 Deprecated된 함수들을 몇몇 확인할 수 있었다.

우선 MySQL8Dialect가 Deprecated되어 MySQLDialect를 사용해야 했던 게 첫 번째 난관이었고, 이를 눈감고 사용하더라도 registerFunction이라는 함수가 존재하지 않았다는 게 두 번째였다. 그래서 열심히 구글링한 결과로 함수를 등록하는 방법을 찾았다. 이는 아래와 같다.

1. 우선 JPQL 함수에서 Dialect를 통해 MySQL형으로 알아들을 수 있도록, 나만의 Dialect를 만들어 준다.

public class MyDialect extends MySQLDialect {
    @Override
    public void initializeFunctionRegistry(FunctionContributions functionContributions){
        super.initializeFunctionRegistry(functionContributions);
        functionContributions.getFunctionRegistry().registerPattern(
                // 함수의 이름을 설정한다.
                "COOKWARE_CONTAINS",
                // 함수가 어떻게 동작하는지 패턴으로 작성한다. 
                // ?1, ?2와 같이 매핑할 수 있다.
                "((?1&?2)=?2)",
                // 이 함수가 리턴하는 타입을 자바 형태로 작성한다.
                functionContributions.getTypeConfiguration()
                        .getBasicTypeForJavaType(Boolean.class)
        );
    }
}

2. application.yml에서 해당 dialect를 사용하도록 설정해 준다.
3. 기도를 하면서 테스트가 통과하기를 빈다.

그런데 이렇게 해도 결국 테스트 DB는 H2로 돌아가고 있기 때문에, 알아듣지 못하는 것은 매한가지였다. url 뒤에 붙이는 MODE=MySQL;은 어느 정도 MySQL과 호환되게끔 보여주는 의미이지, DB 드라이버가 H2이기 때문에 한계가 있었고, 작동하지 않았다.

4. 테스트 DB를 Mysql로 설정하자 🙄

여기부터 살짝 어지러워지는데, 테스트 DB를 H2에서 MySQL로 옮길 이유가 전혀 없음에도 불구하고 당장 급한 불을 끄기 위해서 MySQL로 넘어가기 시작했다. 기존에 개발 서버가 사용하는 DB 옆에 다른 테이블을 만들어서 해당 테이블에서 실험했다.

어찌저찌 바꾸고 나니 쿼리가 잘 돌아간다! 박수를 치고 땅을 딛고 일어나면서 커밋하려는 찰나에, 회귀테스트를 진행해보지 않았다는 게 머릿속을 스쳤다. 전체 테스트를 다시 돌려 두고 손을 모으고 있었는데, 아니나다를까 H2에서만 지원하는 여러 문법들이 MySQL에서는 지원하지 않아 기본적인 레포지토리 테스트들이 터져나가고 있었다. 테스트를 통과하지 못하는 것은 Github CI를 통과하지 못하며, 이는 곧 개발 브랜치로의 병합도 불가능하다는 것을 의미했다.

다 온 것 같았지만 실상은 한 발자국도 앞으로 전진하지 못했다. 결국 테스트 DB는 H2였고, 우리는 H2가 알아듣게 짜야 했으며, 개발서버인 MySQL또한 적용될 수 있도록 쿼리를 짜야 했다. 🤦‍♂️

5. 구글께서 QueryDSL을 사용하면 쓸 수 있다고 한다!

지칠 대로 지친 나는 기왕 다중필터인 쿼리, QueryDSL을 한 번 찍어보기로 한다. 하지만 이 경우에도 매한가지였다. QueryDSL을 아무것도 모르면서 좋지 않은 버릇으로 시간을 실컷 낭비했다. 세팅하는 데 오랜 시간이 걸렸는데 다시 알아보니 근본적인 문제는 MySQL과 H2 사이 간극과 JPQL에서 비트 연산을 지원하지 않았기 때문이리라.

6. 속는 셈 치고 H2가 지원하는 함수인 bitand()를 사용한다.

다른 백엔드 팀원이 H2에 존재하는 BITAND 함수를 소개해주었었다. JPQL과 H2는 문법적으로는 연관이 없음을 알고 있었고, H2에서 이 함수를 지원한다고 해서 JPQL에서 이 함수를 지원한다는 것은 별개의 문제라고 머릿속으로 계산이 끝났기 때문에 넘겨들었다. 하지만 이미 삽을 들고 우물을 판 지 네다섯시간이 흘렀고, 프로젝트의 진전은 없었다. 울며 겨자 먹기로 H2 함수를 별 기대 없이 작성했다.

@Query("SELECT r FROM Recipe r " +
        "WHERE BITAND(r.cookwares, :cookwares) =:cookwares")
List<Recipe> findAllByBitAnd(Long cookwares);

결과는 조금 달랐다. 성공하지는 않았지만 지금까지 본 적 없던 오류가 튀어나왔다. 오류 메시지를 보아하니 내가 해결할 수 있어 보였다.

Statement를 작성하지 못했는데, data type을 유추하지 못해서 발생하는 상황이었다. 이 글에서 알 수 있듯, 해당 파라미터를 캐스팅하면 문제를 해결할 수 있다고 한다.

@Query("SELECT r FROM Recipe r " +
        "WHERE BITAND(r.cookwares, CAST(:cookwares as long)) =:cookwares")
List<Recipe> findAllByBitAnd(Long cookwares);

일단 돌아간다. 이후에 준비할 일들이 많아서 준비해둔 여러 테스트를 통과하는 것을 보고 우선 됐다고 가정하고 나머지 코드를 짰다. 무한 스크롤을 구현해야 했지만 offset으로 날리는 것으로 간단하게 만들어 두었다. 실제로도 잘 동작하는 것을 보고 안도했다. 그렇게 된 게 새벽 3시 51분이었다.

감격의 PR이 있기 전까지는 팀원의 솔로 플레이 캐리로 멱살을 잡힌 채 프로젝트가 이어나가고 있었기에, 나도 드디어 내 할 일을 할 수 있겠다 싶어서 안도했다.

아쉽게 해커톤 성적은 예선에서 그쳤지만, 팀원 모두가 즐겼고, 모든 기능을 구현했음에 의의를 두고 잘 마무리했던 대회였다.

왜 될까?

해커톤 기간에는 정확히 따져볼 겨를이 없기도 했고, 일단 되니까 사용했다. 하지만 지금 생각해보니 JPQL에서 어떻게 저 함수가 구현되어있는지 궁금했다. 구글에도 찾아보았지만 Bitwise operation을 하는 경우는 찾을 수 없었고, Repository Proxy가 돌아가는 걸 디버깅하면서 모르는 것을 하나하나 찾아갔다.

HQL과 JPQL

나는 지금까지 JPQL을 작성했다고 생각했고, 실제로도 그게 (아마) 맞았을 것이다. 하지만 실제 Repository가 String 쿼리를 분석하는 Parser는 HQLParser가 들어가 있었다.
HqlQueryParser라는 클래스도 있었고, JpqlQueryParser라는 클래스도 있었기에 분명 두가지가 다를 것이라고 생각하고 열심히 레퍼런스 검색을 이어나갔다. HQL은 Hibernate Query Language로, 하이버네이트와 관련이 깊을 것이라고 생각해 관련해서 검색해 보았다.

그러다가 발견된 하이버네이트 공식문서. 이렇게 적혀 있었다.

The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.

JPQL는 HQL의 부분집합이라, JPQL로 쓰여진 것은 모두 HQL이지만 역은 성립하지 않는다고 한다. 그럼 해당 쿼리를 HQL라고 생각해도 파싱해도 되기는 하는데, 굳이 저렇게 하는 이유는 무엇일까 싶었다.

스프링 공식 문서에서 갈피를 잡을 수 있었다.

If you have a JPA query (isNative=false) and Hibernate is on the classpath, it will use our new HQL parser. If Hibernate is NOT on the classpath, it will fallback to the somewhat limited JPQL parser. (Limited by specification, not our implementation.)

nativeQuery가 아닌 JPA Query가 있고, classpath에 Hibernate가 있다면 HQL Parser를 이용한다고 한다. 그렇지 않으면 한정된 JPQL을 이용한다고 한다. 어차피 HQL이 더 큰 범위이고, JPQL 또한 파싱할 수 있으니 HQL로 하는 게 나은 선택이긴 하다고 생각한다.

아무튼 다시 돌아와서, HQL 파싱을 통해서 쿼리가 처리되는 것을 알게 되었으므로, 나는 검색을 더 확장할 수 있게 되었다. 궁극적으로 HQL에는 비트 연산이 BITAND라는 함수로 존재하느냐.. 가 제일 궁금한 것이었기에, 관련 문서들을 계속 찾던 도중 Hibernate 이슈 트래커에 올라온 글을 발견했다. Oracle에는 bitand라는 함수가 존재하는데 HQL에는 없어서 이를 이식했다는 issue였고, 글을 썼던 2011년 당시에는 곧바로 받아들여지지 않은 듯 보였다.

이후에 다시 트래커에 같은 내용으로 글이 올라왔다. 이번에는 issue 형식이 아닌 Pull request 형식이었고, 글 작성자가 어떤 부분을 어떻게 수정했는지도 보였다. 그리고 익숙한 문장들이 보였는데…

위에서 보았던 그 함수다. Dialect를 만들어 짜듯이 진행했고, 이 것이 HQL에서 공식적으로 지원하게 되면서 파싱할 수 있게 된 것이었다. 해당 내용은 2018년에 올라왔고, Hibernate에서 6.0 branch에서 고쳤다고 나왔으며 그게 2022년 3월로 꽤 최근 내용이다.

이렇게 BITAND 함수가 HQL에 들어오게 되었고, H2의 문법이 아닌 것으로 결론이 났다. 결국 해커톤 때 H2 함수를 보고 짜서 됐던 것은 우연의 일치였고, 진짜 그 우연이 없었더라면 어떻게 되었을까.. 싶었다. HQL은 관련 Documentation이 적어서 함수를 찾아보기 어려웠기 때문이다. 이제 와서 생각하면 Dialect를 뜯어보는 수가 있다는 걸 알게 되지만, 처음 이 문제를 맞닥드렸을 때에는 생각할 수 없는 단계라고 본다.

아래는 MySQLDialect에 실제로 반영돼있는 함수들이다. 원래 MySQL에서는 BIT_AND라는 함수가 있는데, 해당 함수는 두 수를 AND해주는 게 아니라, Sum, Max와 같은 Aggregate function이라서 위 주석에 binary operations라고 나와있는 모습이다. Aggregate function을 가지고도 한참 왜 안되나.. 했던 3-40분이 해커톤 때 있었다… 🐛🐛🐛

마치며

물론 이 방법이 효율적이라고 생각하지도 않고, 최선의 쿼리라고 이야기할 수도 없다. 해커톤이라는 프로젝트 특성상 빠르게 개발해야 했고, 쿼리 튜닝은 뒤로 미뤄둔 채 구현에 몰두해야 했다. 보다 나은 방법이 있을 것 같고, 지금은 아니지만 후에 공부를 해보고 싶다.

밤을 새워가며 가려운 부분을 긁고 싶었지만 도무지 손이 닿지 않았던 부분을 이렇게나마 해소해서 기쁘다. JPA와 한 발짝 더 가까워진 기분이고, 특히나 하이버네이트 문서를 많이 뒤지면서 안면식도 없었던 사람과 인사한 것 같은 기분이 들었다. JPQL과 HQL의 차이점에 대해서도 조금 알게 되었고, 특히 Subset 개념이라는 점이 주목할 점인 것 같았다. 스프링에서 클래스 경로에 Hibernate가 존재한다면 해당 HQLQueryParser를 꺼내온다는 것까지, 내부 구조를 공부할 수 있었던 유익한 시간이었다.

하이버네이트 문서를 돌아다니면서 재미있는 내용도 알게 되었다. HQL의 BNF 폼은 다음과 같다. 

select_statement :: =
    [select_clause]
    from_clause
    [where_clause]
    [groupby_clause]
    [having_clause]
    [orderby_clause]

대괄호는 생략할 수 있다. 따라서 SELECT 시에는 FROM만 있어도 SELECT할 수 있다. 아래 쿼리는 HQL이지만, JPQL은 아니다. JPQL은 select_clause도 필수로 가져야 한다.

@Query("FROM Recipe") // HQL
List<Recipe> findAllRecipes();

References

https://hibernate.atlassian.net/browse/HHH-6682
https://hibernate.atlassian.net/browse/HHH-12301
https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/chapters/query/hql/HQL.html
https://spring.io/blog/2023/03/21/spring-data-jpa-introduces-query-parser
http://www.h2database.com/html/functions.html#bitand
https://logical-code.tistory.com/223

Categories