상태 컬럼의 빈 값에도 뜻이 있었다

정산 상태를 세는데 합계가 안 맞았다. 상태별로 세면 전체보다 적었다.

Open Table of contents

증상 — 상태별 합계와 전체의 차
원인 — NULL과의 비교
NULL이 무엇인지 물었다
조치 — 값을 만들어 나눴다
변경 내용 — 기본값과 NOT NULL
조회하는 쪽을 다시 봤다
정리

증상 — 상태별 합계와 전체의 차

어느 상태에도 안 들어가는 행이 있다는 뜻으로 읽혀서 상태를 통째로 묶어 세어 봤다.

SELECT settle_state, COUNT(*) FROM orders WHERE reg_date >= ? GROUP BY settle_state;

DONE     38,204
HOLD        882
FAIL        120
NULL      2,676

NULL 이 2,676건 있었고 이 수가 합계의 차와 같았다.

상태별 조회는 각 값을 WHERE settle_state = ? 로 넣고 있었다. 그 조건에 NULL 인 행은 어느 값으로도 걸리지 않는다.

원인 — NULL과의 비교

빠진 것을 한 번에 세려고 부정 조건으로도 써 봤다.

SELECT COUNT(*) FROM orders WHERE settle_state != 'DONE';

NULL != 'DONE' 은 참이 아니라 알 수 없음이어서 이 조건에도 안 걸린다.

이 성질이 까다로운 것은 어디에서도 오류가 안 나기 때문이다. 조회는 정상으로 끝나고 결과도 나오는데 일부가 빠진 채로 나온다.

값을 잡으려면 IS NULL 을 써야 한다. 등호로도 부등호로도 안 잡히는 값이 있다는 것을 알고 있어야 빠진 행을 찾을 수 있었다.

NULL이 무엇인지 물었다

NULL 인 행을 날짜순으로 열어 봤다.

SELECT no, reg_date, state FROM orders WHERE settle_state IS NULL ORDER BY reg_date LIMIT 5;

두 종류가 섞여 있었다.

2017-09-26  아직 정산 대상이 안 된 것 (결제 후 3일 미만)
2016-03-11  정산 컬럼이 생기기 전에 들어온 것

하나는 아직이고 하나는 해당 없음이었다.

둘 다 NULL 이라 구분이 안 됐는데 뜻은 정반대다. 앞의 것은 며칠 뒤에 값이 정해지고 뒤의 것은 영영 정해지지 않는다.

값을 채우려면 이 둘을 가르는 기준이 있어야 했다. 기준은 그 컬럼이 언제 생겼느냐였다.

조치 — 값을 만들어 나눴다

기준 날짜를 찾으려고 스키마 변경 이력을 뒤졌는데 기록이 없었다. 그래서 옛 백업의 스키마를 직접 봤다.

$ zcat backup/order_20160531.sql.gz | grep -A2 settle_state
(없음)
$ zcat backup/order_20160630.sql.gz | grep -A2 settle_state
`settle_state` varchar(20) DEFAULT NULL,

5월 백업에는 없고 6월 백업에는 있으니 그 사이에 생긴 것이다.

그 날짜를 경계로 두 무더기에 다른 값을 넣었다.

UPDATE orders SET settle_state = 'PENDING'
WHERE settle_state IS NULL AND reg_date >= '2016-06-01';

UPDATE orders SET settle_state = 'NOT_APPLICABLE'
WHERE settle_state IS NULL AND reg_date < '2016-06-01';

컬럼이 생기기 전 것은 해당 없음으로 두고 그 뒤 것은 대기로 뒀다.

백업 파일이 남아 있지 않았으면 기준을 정할 근거가 없었다. 변경 이력을 안 남긴 값을 뒤늦게 되찾는 방법이 백업뿐이라는 것이 이번에 걸린 부분이었다.

변경 내용 — 기본값과 NOT NULL

앞으로 NULL 이 안 들어오게 컬럼을 바꿨다.

ALTER TABLE orders MODIFY settle_state varchar(20) NOT NULL DEFAULT 'PENDING';

새로 들어오는 행은 PENDING 으로 시작하고 이후에 값이 바뀐다.

NOT NULL 을 걸기 전에 남은 NULL 이 없는지 먼저 셌다.

SELECT COUNT(*) FROM orders WHERE settle_state IS NULL;   -- 0

하나라도 남아 있으면 ALTER 가 실패한다. 앞의 UPDATE 두 줄이 전부 덮었는지를 여기서 확인한 셈이다.

조회하는 쪽을 다시 봤다

NULL 이 없어지면 그 컬럼을 읽는 코드의 결과가 달라진다.

$ grep -rn "settle_state" --include=*.php src/ | grep -v "IS NULL"

if ($order->settle_state !== 'DONE') { ... }

전에는 NULL 이 이 조건에서 어떻게 걸리는지가 언어와 비교 방식에 따라 달랐다. 이제는 PENDING 이 들어와 분명하게 걸린다.

걸린 세 자리를 하나씩 봤더니 둘은 의도대로였고 하나는 고쳐야 했다. 같은 문제가 다른 컬럼에도 있는지 스키마 쪽에서도 훑었다.

SELECT column_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'order' AND table_name = 'orders' AND is_nullable = 'YES';

settle_state      YES   NULL
cancel_reason     YES   NULL
delivery_state    YES   NULL
memo              YES   NULL

넷 중 cancel_reason 과 memo 는 NULL 이 맞다. 취소를 안 했으면 사유가 없는 것이 정상이다.

delivery_state 는 상태 컬럼이라 같은 문제가 있어서 함께 고쳤다. 상태를 담는 컬럼과 값이 있을 수도 없을 수도 있는 컬럼을 갈라 두니 어디를 의심할지가 정해졌다.

마지막으로 값마다 뜻을 컬럼 주석에 적었다.

ALTER TABLE orders MODIFY settle_state varchar(20) NOT NULL DEFAULT 'PENDING'
  COMMENT 'PENDING:정산대기 DONE:완료 HOLD:보류 FAIL:실패 NOT_APPLICABLE:대상아님(2016-06 이전)';

NOT_APPLICABLE 이 왜 있는지까지 적어야 다음 사람이 그 값을 보고 의아해하지 않는다. 별도 문서에 적는 것보다 스키마에 붙어 있는 쪽이 덜 낡는다.

정리

상태 컬럼의 NULL 은 조회에서 조용히 빠진다
= 로도 != 로도 안 잡히고 IS NULL 을 써야 한다
오류가 안 나므로 빠진 것을 알아채기 어렵다
NULL 이 아직인지 해당 없음인지 물어본다
둘이 섞여 있을 수 있고 컬럼이 생긴 날이 기준이 된다
이력이 없으면 옛 백업의 스키마를 본다
값을 채운 뒤 기본값을 주고 NOT NULL 로 바꾼다
걸기 전에 남은 NULL 이 0인지 센다
NULL 이 없어지면 읽는 코드의 결과가 달라지므로 다시 본다
상태 컬럼과 있을 수도 없을 수도 있는 컬럼을 가른다
값마다 뜻을 컬럼 주석에 적는다