tl;dr
언급해야할 내용과 교훈이 길기 때문에, 먼저 간단히 정리하면 아래의 세가지 사항으로 정리할 수 있다.
TO_DATE함수의 포맷 문자열 파라미터는 Optional이고, 데이터간 구분 문자는 ASCII 특수문자 내에서 입력 문자열/포맷 문자열 모두에서 자동으로 생략되는게 기본 값이다.날짜와 시간은 알맞은 칼럼 타입을 선택해 보관해야 한다.
부득이 하게 날짜/시간 형을 문자열로 저장하는 경우에는 반드시 입력 형식 검증에 힘쓴다.
시작하기에 앞서
Oracle은 큰 회사의 이름이자, 그 회사의 DBMS 상품명이기도 하다. 이래저래 오픈소스 진영에서 안 좋은 이야기도 많이 듣고 기존 프로젝트가 포크되어 뛰쳐나가는 여러 사례들을 만들기도 했지만, 그래도 신뢰성 혹은 유상 기술 지원 등을 이유로 이 회사의 제품을 기존까지 많이 선택해왔다.
회사에서 어느 날 갑자기 다른 DBMS를 사용하자고 이야기해도 기존까지 구현된 모든 부분을 대체하긴 힘들고(특히 ANSI SQL 범주 외의 코드들), 오픈소스 소프트웨어로 대체되었을 때 튜닝이나 트러블슈팅 등의 역량이 미진하여 쉽사리 대체하지 못하는 경우가 많은데 오늘 이야기는 그런 사유로 아직 Oracle DBMS를 사용하고 있는 레거시 서비스에서 겪은 문제이다.
사건 추적
이제 본 이야기로 돌아가면, CSV로부터 데이터를 입력받아 연계하는 메뉴가 있었다. 데이터 연계를 사람이 할 수 밖에 없는 사유가 있는 그런 메뉴인데, 사용자는 Raw 데이터로부터 스프레드시트 프로그램을 사용해 데이터를 편집한 후 CSV로 저장한 뒤 해당 메뉴를 통하여 데이터베이스에 데이터를 올리는 절차를 밟는다.
다른 데이터는 숫자 혹은 문자열로 크게 문제가 없었으나, 이 데이터의 열 2개는 날짜에 대한 데이터였다. 이전까지 이 데이터 등록 절차를 밟던 사람은 이 날짜를 YYYY-MM-DD 형식에 맞춰 잘 올려준 덕분에, SQL 쿼리 상에서 데이터 검색이 올바르게 진행되었다. 그리고 이때까지만 해도 입력 데이터 유효성 검증 코드가 마치 있는 것처럼 잘 동작했다.
문제는 전임자가 후임자에게 업무를 인수인계해주면서, 해당 기능의 날짜 형식에 대해 자세한 정보를 주지 않은 덕분에 날짜 형식을 수정하지 않고 올리는 실수를 했다. 당시 스프레드시트 프로그램에서 어떤 형식으로 등록했냐면 MM/DD/YYYY 꼴로 등록해주었다. Microsoft Excel의 경우 셀서식 메뉴를 통해 날짜 형식을 변경해줄 수 있는데, 이 절차를 후임자가 깜빡한 것.
결국 올바르지 않은(?) 날짜 형식의 데이터를 그대로 데이터베이스에 등록하게 되었는데, 더욱 심각한 건 해당 두 데이터의 데이터베이스 칼럼 타입이 DATE 형식이 아니라 그냥 VARCHAR2[1] 로 선언되어있던 것이다. 잘못된 데이터는 그냥 들어와버렸고, YYYY-MM-DD 기준으로 항상 데이터가 들어오리라 가정하고 작성한 검색 쿼리는 해당 데이터를 보여주지 못했다. 심지어 날짜 조건 검색을 돌리는 경우에는 오류가 발생되었다.
심지어 나 자신도 업무가 바뀌어 전체 코드를 살펴보고 있던 중에 생긴 문제라, 일단 오류 리포트부터 확인한 후에 SQL 쿼리를 살펴보고 데이터를 검토한 후에 해당 문제에 대한 조치를 마쳤다. 날짜 형식 맞춰서 다시 문자열로 변환하여 등록해주는 걸로 끝났기에 오래 걸리진 않았고, 재발 방지를 위해 발견 당일에 관련 사항에 대한 보고 자료 작성 후 입력 데이터에 대한 Validator를 달았다.[2]
이후에 모든 데이터에 대해 검증 작업을 수행했는데, 날짜 데이터가 다음과 같이 일관되지 않게 등록되어있었다.
- M/D/YYYY
- MM/DD/YYYY
- YYYYMMDD
- YYYY-MM-DD
당시에 해당 데이터를 날짜 범위에 맞춰 검색하는 SQL 쿼리의 조건절은 다음과 같이 작성되어있었다.
1 | SELECT ... |
Oracle에 익숙하지 않은 분들을 위해 간단히 정리하면, TO_DATE(input_string, format_string) 을 통해 문자열을 DATE 타입으로 변환하고, 이 날짜가 SYSDATE 라는 값보다 적은지 비교하는 코드이다. SYSDATE 는 System Date 에서 유래된 걸로 보이는 예약어로, 현재 DBMS 서버의 시각을 가져올 수 있다. 비교시에는 내부적으로 비교 가능한 변환하는 과정이 이루어진다.
아무튼 문제는 저 format_string 부분이다. 일단 M/D/YYYY 나 MM/DD/YYYY 형식의 경우에는 변환에 실패하여 SQL 오류가 발생하는 걸 확인했는데, 문제는 그 다음에 이어진 YYYYMMDD 이다. 데이터 배열 순서는 비슷하지만 형식이 잘못되었다. 하지만 이 데이터를 검색할 때는 오류가 발생하지 않았다. 즉 이 쿼리는 아무 문제 없이 동작한다.
1 | -- DUAL 테이블은 인라인 연산 등을 위해 Oracle 전역에서 사용 가능한 1행 1열(값은 'X' 가 들어있음)짜리 테이블이다. |
아무튼 저렇게 호출해도 DATE 형으로 무사히 변환된다. 즉, YYYYMMDD 로 들어간 데이터는 큰 무리 없이 검색된다. 이번에 발생된 오류 건은 그 이상을 넘어서서 형식이 다르기 때문에 실패한거고 이전엔 그쯤 섞여도 괜찮은 수준이라 문제가 없었던 것이었다.
몇가지 더 테스트를 해보았는데, 아래와 같은 SQL 쿼리도 문제 없이 올바르게 변환된 날짜를 반환하였다. 사용한 Oracle의 버전은 12c이다.
1 | SELECT TO_DATE('2020/04/12', 'YYYY-MM-DD') FROM DUAL; |
Oracle TO_DATE의 기묘한 동작
상식적으로 납득하기 어려운 동작에 대해 살펴보기 위해 Database SQL Reference TO_DATE 문서를 살펴보았다. 거기서 더 놀라운 사실을 알았는데 TO_DATE 에 입력한 날짜 형식 문자열은 생략 가능이란 점이다.[3] 즉, ISO 날짜 형식에 맞는 경우에 한해 아래 SQL 쿼리는 유효하다.
1 | SELECT TO_DATE('2020/04/12') FROM DUAL; |
이 날짜 형식에 대해선 Datetime Format Models 문서에서 좀 더 자세히 소개하고 있다. 하지만 구분 문자가 다른 걸로 대체되어도 큰 문제 없이 동작되거나, 아예 생략해도 괜찮다고 명시적으로 언급한 부분이 없다.
이 동작에 대한 근접한 설명이 있다. Datetime Format Models 문서 내의 Format Model Modifiers 에서 설명하는 FM 모드와 FX모드이다. FM은 Fill mode 자동으로 입력된 문자열을 최대한 현재 데이터베이스 연결 세션의 다국어 설정에 맞춰 변환하는 모드이다. FX모드는 Format eXact 모드이다. 형식이 조금이라도 다르면 포매팅에 실패한다.
문서의 설명은 다음과 같이 나와있다.
FX
Format exact. This modifier specifies exact matching for the character argument and datetime format model of a TO_DATE function:Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.
The character argument cannot have extra blanks. Without FX, Oracle ignores extra blanks.
Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeroes.
When FX is enabled, you can disable this check for leading zeroes by using the FM modifier as well.
Oracle의 TO_DATE의 포맷 문자열 검증은 FX 모드일 때만 정확히 구별하도록 동작한다. 따라서 약간 비슷한 형식들에 대해선 위대하신 Oracle 께서 알아서 변환해주신 덕분에 여태 문제 없이 운영이 되었던 것이다.
맺음말
수정 이후로 들어오는 데이터는 날짜 형식 검증을 꼼꼼히 수행하기 때문에 문제가 없지만, 이전에 등록된 데이터들은 당장 운영에 지장은 없기 때문에 가만히 두고 있다. 추후 작업과 백업/복구 계획을 세워 한번에 맞출 계획이다.
문제가 생기지 않았다면 이렇게 자세히 찾아볼 일은 없었겠지만, 문제가 생긴 덕분에 깊게 살펴볼 계기가 되었다. 소프트웨어 개발 도중에 생기는 문제는 보통 원인 없는 결과가 없다. 그러려니 하고 넘어가지 않고, 살펴보는 습관을 들이자.
그리고 시간 되는대로 사용하고 있는 소프트웨어의 공식 레퍼런스 문서를 살펴보길 바란다. 초보 개발자의 개발 도전기! 3편: 오라클 TO_DATE 함수 입문 이런 글도 당장 제품을 완성해야하는게 급하면 볼 순 있겠지만, 언젠가 여유가 찾아오면 영어 사전 뒤져가면서라도 레퍼런스 문서를 꼭 살펴보길 바란다.[4]
그리고 Data Validation 좀 잘하자.
- 1.Maria DB 기준
VARCHAR와 동일 ↩ - 2.근본적으론 해당 데이터 칼럼 타입을 바꾸는 게 맞고, 바뀐 타입에 맞춰 새롭게 처리를 하는게 옳다. 그러나, 사내 전산실의 DBA와 변환 작업에 대해 이야기하려면 시간이 오래 걸리니 우선 조치를 수행했다. ↩
- 3.해당 문서의 문법 구조 그림에서
char다음에fmt라고 되어있는 부분.fmt는 별도 화살표로 분기되어있고fmt를 지나지 않더라도 문장을 끝낼 수 있으므로 Optional이다. ↩ - 4.이 이야기를 해두는 이유는, 처음 이 소프트웨어를 작성한 사람이 그런 성향을 갖고 있지 않은 사람이었기 때문이다. ↩