본문으로 건너뛰기
wooncloud

SQL 조각에 컬럼 이름만 넘기면 상관 서브쿼리가 자기 자신과 비교한다

상관 서브쿼리에 한정자 없는 컬럼 이름을 넘기면 가장 안쪽 테이블의 컬럼으로 해석되어 조건이 항상 참이 됩니다. 문자열 조각으로 SQL 을 조립할 때 겪은 함정과 방어 방법입니다.

·5 min read·— views··
#postgresql#sql#subquery#code-review

"이 테넌트가 방을 하나라도 가지고 있는가"를 여러 쿼리에서 재사용하려고 EXISTS 조각을 만들어 주는 함수를 뒀습니다. 그런데 호출부 한 곳에서 필터가 조용히 꺼져 있었습니다.

조각 함수는 이렇게 생겼습니다

바깥 쿼리의 테넌트 컬럼을 인자로 받아 문자열에 끼워 넣는 함수입니다.

// col = 바깥 쿼리의 테넌트 컬럼 식
const ownsRoom = (col: string) =>
  `EXISTS (SELECT 1 FROM rooms r WHERE r.tenant_id = ${col})
   OR EXISTS (SELECT 1 FROM chats c WHERE c.tenant_id = ${col})`;

대부분의 호출부는 ownsRoom("t.tenant_id") 처럼 테이블 별칭을 붙여 넘겼습니다. 그런데 한 곳만 별칭 없는 FROM 에서 ownsRoom("tenant_id") 로 넘겼습니다.

왜 항상 참일까요

완성된 SQL 은 이렇게 됩니다.

SELECT tenant_id FROM usage_snapshots
 WHERE EXISTS (SELECT 1 FROM rooms r WHERE r.tenant_id = tenant_id)

서브쿼리 안에서 한정자 없는 tenant_id 는 가장 안쪽 스코프부터 찾습니다. rooms 에도 tenant_id 가 있으니 바깥 usage_snapshots.tenant_id 가 아니라 r.tenant_id 로 해석됩니다. 조건이 r.tenant_id = r.tenant_id 가 되는 거죠.

rooms 에 행이 하나라도 있으면 항상 참입니다. 문법 오류도 경고도 없고, 필터가 조용히 꺼질 뿐입니다.

SQL 표준의 이름 해석 규칙대로 동작한 것입니다. 상관 서브쿼리에서 바깥 컬럼을 쓰려면 한정자가 필요합니다.

테스트는 왜 못 잡았을까요

픽스처에 "방이 없는 테넌트"와 "방이 있는 다른 테넌트"가 동시에 있어야 드러나기 때문입니다. 방이 하나도 없는 DB 에서는 EXISTS 가 거짓이라 우연히 맞습니다.

게다가 조각을 조립하는 함수만 단위 테스트하면, 호출부가 넘긴 인자 모양은 검증되지 않습니다.

고친 방법

먼저 바깥 테이블에 별칭을 주고 한정자를 붙여 넘겼습니다. FROM usage_snapshots u … ownsRoom("u.tenant_id") 같은 식입니다.

그리고 조각 함수 입구에서 인자 모양을 단언했습니다. 한정자(.)가 있거나 바인드 자리표시자($1, ?)일 때만 받습니다.

function assertQualified(col: string) {
  if (!/\./.test(col) && !/^\$\d+$|^\?$/.test(col)) {
    throw new Error(`correlated column must be qualified: ${col}`);
  }
}

회귀 테스트는 방 없는 테넌트와 방 있는 다른 테넌트를 같은 픽스처에 두고, 방 없는 쪽이 걸러지는지 봅니다.

SQL 을 문자열 조각으로 조립한다면 상관 서브쿼리에 들어가는 바깥 컬럼에는 항상 한정자를 붙이는 게 안전합니다. 한정자 없는 이름은 안쪽 테이블에 같은 컬럼이 생기는 순간 의미가 바뀌니까요. 코드 리뷰에서도 "조각에 넘기는 컬럼 식에 별칭이 붙어 있는가"를 기본 확인 항목으로 둘 만합니다.

참고