error based

I Think.. 2014.12.29 20:12

@_@ 더이상 짱구 굴려봐야 안될거 같아서 guessing으로 알아낸것 적어둡니다. 능력자분들 해결좀.

환경 : mysql(ver : 5.1.41-community), root 권한
쿼리 : SELECT concat(floor(rand(0)*K)a FROM (SELECT 1 UNION... ...SELECT J)b GROUP BY a HAVING min(0);

여기서 K와 J는 양의 정수

random표

보는법 : *K(최소 필요 컬럼갯수, duplication이 일어나는값)
floor(rand(0)*K)의 리턴값

*2(3,1)
0,1,1,0,1,1,0,0,1,1,1,0,1,1,1,0,1,0,0,0,1,1,1,0,1,1,0,0,0,0,1,0,0,1,1,0,1,1,0,0,0,1,1,1,0,0,1,0,0,0,0,0,0,0,0,1,0,0,1,0,0,0,0,1,0,1,0,0,0,0,0,0,1,0,1,1,1,1,1,0,1,1,0,0,1,0,0,1,1,1,1,1,1,1,0,1,1,0,0,1,0,0,0,0,1,1,1,1,0,0,0,0,1,0,1,1,0,0,1,0,1,0,0,1,1

*3(5,2)
0,1,1,0,2,2,0,1,2,2,2,1,2,1,2,1,2,0,1,1,1,2,1,0,2,2,1,0,0,1,1,0,0,1,1,0,2,2,0,1,0,2,2,1,0,1,2,1,0,0,0,0,1,1,0,2,1,0,2,0,0,0,0,1,0,1,0,0,1,0,1,0,2,0,1,1,2,2,1,1,2,2,1,1,1,0,0,1,2,2,2,1,1,2,0,1,2,0,1,1,0,0,0,1,2,1,2,2,0,0,0,1,2,0,1,2,0,1,2,1,1,0,0,1,2

*4(3,2)
0,2,2,1,2,2,1,1,3,3,3,1,3,2,3,1,3,0,1,1,2,3,2,1,2,2,1,0,0,1,2,0,0,2,2,1,2,2,1,1,0,3,2,2,0,1,3,1,0,0,1,1,1,1,0,3,1,0,2,0,0,1,1,2,1,2,0,0,1,1,1,0,3,0,2,2,2,3,2,1,3,2,1,1,2,0,0,2,2,3,3,2,2,3,0,2,3,0,1,2,0,0,1,1,3,2,3,2,0,1,0,1,3,0,2,3,0,1,2,1,2,0,0,2,3

*5(3,3)
0,3,3,1,3,3,1,1,4,3,4,1,4,2,3,2,4,1,2,1,2,4,2,1,3,3,2,0,0,2,3,0,0,3,3,1,3,3,1,1,0,4,3,3,0,2,4,1,0,0,1,1,2,2,0,4,1,0,3,0,0,1,1,3,1,3,1,1,1,1,1,0,4,1,3,2,3,4,3,1,4,3,2,2,3,0,0,3,3,4,4,2,2,4,0,3,4,0,2,3,0,0,1,1,4,2,4,3,0,1,0,1,4,0,3,4,0,1,3,1,3,1,1,2,4

*6(6,4)
0,3,3,1,4,4,1,2,5,4,5,2,5,3,4,2,5,1,2,2,3,5,3,1,4,4,2,0,0,2,3,0,0,3,3,1,4,4,1,2,0,5,4,3,0,2,5,2,0,0,1,1,2,2,0,5,2,1,4,0,0,1,1,3,1,3,1,1,2,1,2,0,5,1,3,3,4,5,3,2,5,4,2,2,3,0,0,3,4,4,4,3,3,5,0,3,4,1,2,3,0,0,1,2,5,3,5,4,0,1,0,2,5,0,3,5,0,2,4,2,3,1,1,3,4

*7(6,5)
1,4,4,2,5,4,2,2,6,5,6,2,6,3,5,3,6,1,2,2,4,6,4,2,5,5,3,0,1,2,4,0,0,4,4,2,5,4,2,2,0,6,4,4,0,2,6,2,0,0,2,1,3,3,0,6,2,1,5,0,0,2,2,4,2,4,1,1,2,2,2,0,5,1,4,3,4,6,4,2,6,4,3,2,4,0,0,4,5,5,5,4,3,6,0,4,5,1,3,4,0,0,2,2,6,3,6,5,0,1,0,2,5,0,4,6,0,2,4,2,4,1,1,3,5

*8(7,2)
1,4,5,2,5,5,2,2,7,6,7,2,7,4,6,3,7,1,3,2,4,6,4,2,5,5,3,0,1,3,5,0,1,4,5,2,5,5,2,3,0,7,5,5,0,3,7,2,0,0,2,2,3,3,0,7,3,1,5,0,0,2,2,5,2,5,1,1,3,2,3,0,6,1,5,4,5,7,5,3,7,5,3,3,5,0,1,5,5,6,6,4,4,7,0,4,6,1,3,5,0,0,2,3,7,4,6,5,0,2,0,3,6,0,4,7,0,3,5,2,5,1,1,4,6

*9(6,6)
1,5,5,2,6,6,2,3,8,6,8,3,8,4,7,4,8,2,3,3,5,7,5,2,6,6,4,0,1,3,5,0,1,5,5,2,6,6,2,3,0,8,6,5,0,3,8,3,0,0,2,2,4,4,0,8,3,1,6,0,0,2,2,5,2,5,2,1,3,2,3,0,7,2,5,4,6,8,5,3,8,6,4,3,5,0,1,5,6,7,7,5,4,8,0,5,7,1,3,5,0,0,2,3,8,4,7,6,0,2,0,3,7,0,5,8,0,3,6,3,5,2,2,4,7|

*10(6,7)
1,6,6,3,7,7,2,3,9,7,9,3,9,5,7,4,9,2,4,3,5,8,5,2,7,7,4,1,1,4,6,0,1,6,6,3,7,6,2,3,0,9,6,6,0,4,9,3,0,0,3,2,4,4,0,9,3,1,7,0,0,3,3,6,3,6,2,2,3,3,3,0,8,2,6,5,7,9,6,3,9,6,4,4,6,0,1,6,7,8,8,5,5,9,0,6,8,1,4,6,0,0,3,3,9,5,8,7,0,2,0,3,8,0,6,9,0,3,6,3,6,2,2,5,8,5

*11(6,10)
1,6,7,3,8,7,3,4,10,8,10,3,9,5,8,5,10,2,4,4,6,9,6,3,7,7,5,1,1,4,7,0,1,6,6,3,8,7,3,4,0,10,7,6,0,4,10,3,0,0,3,3,5,5,0,10,4,1,7,0,1,3,3,7,3,7,2,2,4,3,4,0,9,2,7,5,7,10,7,4,10,7,5,4,7,0,1,6,8,8,8,6,5,10,0,6,8,1,4,7,0,0,3,4,10,5,9,8,1,2,0,4,9,0,6,10,0,4,7,3,7,2,2,5,8,5

*12(7,4)
1,7,7,3,8,8,3,4,11,9,11,4,10,6,9,5,11,2,4,4,7,10,7,3,8,8,5,1,1,5,7,0,1,7,7,3,8,8,3,4,0,11,8,7,0,5,11,4,0,0,3,3,5,5,0,11,4,2,8,0,1,3,3,7,3,7,2,2,4,3,4,0,10,2,7,6,8,11,7,4,11,8,5,5,7,0,1,7,8,9,9,7,6,11,1,7,9,2,5,7,0,0,3,4,11,6,10,8,1,3,0,4,10,0,7,11,0,4,8,4,7,2,2,6,9,6

*13
2,8,8,4,9,9,3,4,12,10,12,4,11,6,10,5,12,3,5,4,7,11,7,3,9,9,5,1,1,5,8,0,1,7,8,4,9,9,3,4,0,12,9,8,0,5,12,4,0,1,3,3,6,6,1,12,5,2,9,0,1,4,4,8,4,8,2,2,5,4,5,0,11,3,8,6,9,12,8,4,12,8,6,5,8,0,1,8,9,10,10,7,7,12,1,7,10,2,5,8,0,0,4,4,12,6,11,9,1,3,0,5,10,0,7,12,0,4,9,4,8,3,3,6,10,6

*14
2,8,8,4,10,9,4,5,13,10,13,4,12,7,11,6,12,3,5,5,8,12,8,4,10,10,6,1,2,5,9,0,1,8,8,4,10,9,4,5,0,13,9,8,0,5,12,5,0,1,4,3,6,6,1,12,5,2,10,0,1,4,4,9,4,8,3,2,5,4,5,0,11,3,9,7,9,13,9,5,13,9,6,5,9,0,1,8,10,11,11,8,7,13,1,8,11,2,6,9,0,0,4,5,13,7,12,10,1,3,0,5,11,0,8,13,0,5,9,4,9,3,3,7,11,7

* 추측한 내부동작

1. concat이 포함된 SELECT 구문을 컬럼으로 들고 있는다
2. FROM 구문으로 리턴될 테이블을 가지고 있는다
3. GROUP BY 구문을 통해 합친다. 이 때 1 에서 들고 있는 컬럼을 값으로 들고 있는것이 아닌 함수 원형 모습 그대로 들고 있다.
-> floor(rand(0)*K)

4. GROUP BY 구문을 통해 합칠때, 해당 컬럼을 group_key 라는 특수한 KEY의 형태로 등록함으로써 중복 입력을 방지한다

5. HAVING 절을 실행하기 위해 1에서 들고 있는 컬럼값을 조회한다. 이때 HAVING절이 실행되면서 row의값을 확인하기 위해 floor(rand(0)*K)의 값을 푼다.
-> HAVING절이 일반 상수가 아닌 함수의 리턴값이어야 하는 부분. 일반 상수 혹은 BOOLEAN 식으로 적어버리면 T/F를 먼저 판단해버리고 GROUP BY 구문으로 통해 합쳐진 테이블 내에 있는 floor(rand(0)*K) 값을 그대로 출력/비출력 으로 나누어진다. 아마 이 부분이 함수로 처리되어 있으면 floor(rand(0)*K) 값이 나중에 처리되어야 할 명령어로써 스택에 저장이 되고 결국 한번 더 풀리게 되는것 같음.
이 예시로, *11의 값을 보게 되면 6개의 row를 출력하는 테이블에서 Duplication이 난다고 적어 두었는데, 처음부터 6개의 값을 보았을때 10 이라는 값은 존재하지 않는다. 이후에 나오는 7번째 부터 12번째 값을 보면 10 이라는 숫자가 2개 존재함을 볼 수 있다.

6. HAVING절을 통해 한번 더 실행된 rand에 따라 GROUP BY된 테이블을 참조하여 INSERT 구문을 내부적으로 실행한다. 이때 만약 겹치는 key가 있다면 error.

라는것 같은데 군데군데 조금씩 의문점이 확실히 해결이 안되네요..

이 상태에서 5시간째 해매는중

'I Think..' 카테고리의 다른 글

[BoB 2nd] Scientific Research - OS, Memory, malloc  (0) 2016.02.22
error based  (1) 2014.12.29
Posted by Maid:: IzayoiSakuya