생일 데이터 집계시 이슈2

https://dbazac.tistory.com/11

생일 데이터 집계시 이슈

이 후 또 이슈가 발생;;

 

Quest : BirthDay가 varchar(10)이라서 날짜형식이 아닌 정제되지 않은 값들이 다수 존재하여

날짜형식인 값들만 필터링하고 특정날짜 이전의 값들만 조회하고 싶다.

select count(*)
  from t1
 where isdate(BirthDay) = 1
   and BirthDay not like '%000/%'
   and cast(BirthDay as date) < '2021-05-26';
-------------------------------------------------------
메시지 241, 수준 16, 상태 1, 줄 6
문자열을 날짜 및/또는 시간으로 변환하지 못했습니다.

하지만 에러 발생.

 

date로 변환이 안되는 값들을 필터링하는 조건과

date로 변환을 시도하는 구문이 동일위치에 존재하여

date로 변환이 안되는 값을 cast(BirthDay as date)시도 하여 발생.

 

먼저 date로 변환이 되는 값들판 필터링 되도록 쿼리 변경.

select count(*)
  from (
        select BirthDay
          from t1
         where isdate(BirthDay) = 1
           and BirthDay not like '%000/%'
       ) a
 where cast(BirthDay as date) < '2021-05-26'
-------------------------------------------------------
메시지 241, 수준 16, 상태 1, 줄 6
문자열을 날짜 및/또는 시간으로 변환하지 못했습니다.

하지만 또 발생;;

 

그럼 서브쿼리 내에서 변환을 한 다음에 필터링해보자.

select count(*)
  from (
        select cast(BirthDay as date) as date_BirthDay
          from t1
         where isdate(BirthDay) = 1
           and BirthDay not like '%000/%'
       ) a
 where date_BirthDay < '2021-05-26'
-------------------------------------------------------
메시지 241, 수준 16, 상태 1, 줄 6
문자열을 날짜 및/또는 시간으로 변환하지 못했습니다.

하지만 또또 발생;;

 

신기한건 서브쿼리 내부에 top N을 주면 발생하지 않는다. 심지어는 전체로우수를 넘어서는 N을 줘도 마찬가지.

select count(*)
  from (
        select top 5000000 BirthDay
          from t1
         where isdate(BirthDay) = 1
           and BirthDay not like '%000/%'
       ) a
 where cast(BirthDay as date) < '2021-05-26'
-----------
2480543
(1개 행이 영향을 받음)

 

----------------------------------------------------------------------------------------------------------

 

단순한 mysql만 너무 오래 한탓에 감을 잃었다.

또 버그인줄 오해해서 하필 서버와 디비가 다르게 잡혀있던 collation 테스트부터

오만 조사를 다 하였는데 마지막에서야 “아 플랜!” 실행계획을 까보자.

set showplan_text on

select count(*)
  from (
        select cast(BirthDay as date) as aa
          from t1
         where isdate(BirthDay) = 1
           and BirthDay not like '%000/%'
       ) a
 where aa < '2021-05-26'
-----------------------------------------------------------------
|-- ..... WHERE:(CONVERT(date,[t1].[BirthDay],0)<'2021-05-26' .......

역시 옵티마이저가 쿼리를 변형하여 cast(BirthDay as date) < '2021-05-26' 이런 형태로

가장먼저 필터링을 하고 있다.

 

그럼 top N을 준 쿼리는?

select count(*)
  from (
        select top 5000000 BirthDay
          from t1
         where isdate(BirthDay) = 1
           and BirthDay not like '%000/%'
       ) a
 where cast(BirthDay as date) < '2021-05-26'
-----------------------------------------------------------------
|--Filter(WHERE:([Expr1003]<'2021-05-26'))
    |--Parallelism(Distribute Streams, RoundRobin Partitioning)
        |--Top(TOP EXPRESSION:((5000000)))
                |--Parallelism(Gather Streams)
                    |--Filter(WHERE:([Expr1004]=(1)))
                        |--Compute Scalar(DEFINE:([Expr1003]=CONVERT(date,[t1].[BirthDay],0), [Expr1004]=isdate([t1].[BirthDay])))
                            |--Index Scan(OBJECT:([t1].[ix_2304]),  WHERE:(NOT [t1].[BirthDay] like N'%000/%'))

또 역시 top N 으로 인해 어쩔 수 없이 서브쿼리 내부를 먼저 처리한 이후 필터링 하니 문제가 없었던 것이다.

 

-----------------------------------------------------------------

 

mysql의 경우 병렬처리나 해쉬조인등이 안되서(5.8이후는 되는듯?) 이런 일이 발생하는 경우가 드물고

오라클의 경우 힌트처리를 해주면 되는데 SQLServer는 마땅한 힌트가 제공 되려나 조사 해 봐야겠다.

 

다시 봐서 반갑다 SQLServer야.. 오라클은 언제 다시 만나려나 ㅠㅠ