데이터베이스 질문과 답변
-> Restrictions imposed on recursive definitions are violated 'with절 명칭'
위 오류가 나는데 구글링해봐도 안나와서 질문드립니다.
mysql/mariadb에서는 with절을 재귀돌시
RECURSIVE를 붙여야 되는데
그 RECURSIVE with절안에 with절명칭은 INNER JOIN 해서 사용할경우
위 에러가 나는데 위의 에러명이 어떨때 나타난 경우인지 몰라서 질문드립니다.
재귀쿼리가 사용될 수 있는 제약사항을 어긴 경우일 듯 합니다.
작성하신 오류 쿼리를 올려주세요..
UPDATE TEST_TB A INNER JOIN (
WITH RECURSIVE T_SAMPLE AS (
SELECT MAIN_CODE AS TOP_NUM, MAIN_CODE, 1 AS MAIN_LV,
RIGHT(CONCAT('000', ROW_NUMBER() OVER (ORDER BY MAIN_CODE)), 2) AS NNO
FROM TEST_TB
WHERE KEY_NO = '1' AND UP_MAIN_CODE IS NULL
UNION ALL
SELECT
B.TOP_NUM, A.MAIN_CODE, B.MAIN_LV + 1 AS MAIN_LV,
CONCAT(C.NNO, RIGHT(CONCAT('000', ROW_NUMBER() OVER (PARTITION BY A.UP_MAIN_CODE ORDER BY A.MAIN_CODE)), 3)) AS NNO
FROM TEST_TB AS A
INNER JOIN T_SAMPLE B
ON A.UP_MAIN_CODE = B.MAIN_CODE
WHERE
A.KEY_NO = '1'
)
SELECT * FROM T_SAMPLE
) B ON A.MAIN_CODE = B.MAIN_CODE
SET
A.TOP_MAIN_CODE = B.TOP_NUM,
A.MAIN_LV = B.MAIN_LV,
A.NNO = B.NNO
WHERE
A.KEY_NO = '1';
-> 실무에서 쓰고있어서 테이블, 컬럼명은 보안상 제가 임의로 바꾸었습니다.
위 쿼리를 돌렸을때
Restrictions imposed on recursive definitions are violated for table 'T_SAMPLE' 에러가 발생하였습니다.
T_SAMPLE가 WITH절 명칭입니다.
row_number 를 쓰서서 그런 듯 하네요.
마리아db에서는 ROW_NUMBER()를 못쓰나요? 그럼 대신써야되는 함수가 있는건지...
MYSQL / MARIADB에서도 ROW_NUMBER()지원되서 잘되는데 위의 UNION ALL아래 WITH절과 조인한 SELECT문에서는 왜 ROW_nUMBER()가 안되는지 모르겠습니다...
저 ROW_NUMBER() 안에 PARTITION BY 절에 쓰인 A.UP_MAIN_CODE 컬럼 데이터가
NULL값이 포함되어있는데 혹시 NULL에대한 처리를 안해줘서 그런건가요?
PARTITION BY에 쓰인 컬럼을 NULL처리 하는 방법을 알고싶습니다.
MySQL Server Blog에 보면 RECURSIVE 문장 안에서 집계함수 사용이 안된다는군요.
a recursive SELECT mustn’t contain GROUP BY, aggregate functions
(like SUM), ORDER BY, LIMIT, DISTINCT (this rule doesn’t apply to the non-recursive/anchor/seed SELECT)
Mariadb knowledge base에도 댓글이 있네요. 문서상에는 명시된 곳을 못찾았습니다.
It took a long time to find out that aggregate functions cannot be used in the recursive portion of a CTE.
https://mariadb.com/kb/en/recursive-common-table-expressions-overview/
아래처럼 SUM 함수만 넣어도 안되네요..
MariaDB [test]> WITH RECURSIVE my_cte AS
-> (
-> SELECT 1 AS n, 1 s1
-> UNION ALL
-> SELECT 1+n,sum(n) FROM my_cte WHERE n<10 # <- recursive SELECT
-> )
-> SELECT * FROM my_cte;
ERROR 4008 (HY000): Restrictions imposed on recursive definitions are violated for table 'my_cte'
![MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs), Part Two – how to generate series - MySQL Server Blog](https://mysqlserverteam.com/wp-content/uploads/2018/01/MySQL_Icon_256x256.png){kind=icon}
집계함수를 못쓰면 ROW_NUMBER() 비슷하게 쓸수있는 집계함수 아닌게 있을까요..
MySQL 이라서 안되는게 아니라
재귀 쿼리안에 들어가면 안되는게 들어가서 그래요.
재귀쿼리에 대한 제약상항으로 MySQL 뿐 아니라 약간의 차이는 있겠지만 대부분 DB 에 공통되는 제약일 것입니다.
아 그렇군요; 성급한 판단이었습니다..지적 감사합니다.
재귀 쿼리 없이 row_number 만 사용하여 따로 업데이트 한번 하고,
업데이트된 결과를 이용해 다시 재귀쿼리 시도해 보세요.
-- row_number 을 recursive 밖으로 빼봤습니니다. (emp 테이블로 테스트)
WITH t1 AS
(
SELECT deptno
, empno
, mgr
, CAST(LPAD(ROW_NUMBER() OVER(PARTITION BY mgr ORDER BY empno), 3, '0') AS VARCHAR(99)) rn
FROM emp
)
, t3 AS
(
WITH RECURSIVE t2 AS
(
SELECT deptno
, empno
, mgr
, empno top_num
, 1 lv
, rn nno
FROM t1
WHERE mgr IS NULL
AND deptno = 10
UNION ALL
SELECT a.deptno
, a.empno
, a.mgr
, b.top_num
, b.lv + 1 lv
, CONCAT(b.nno, a.rn) nno
FROM t1 a
INNER JOIN t2 b
WHERE a.mgr = b.empno
AND a.deptno = b.deptno
)
SELECT *
FROM t2
)
SELECT *
FROM t3
;
https://rampart81.github.io/post/mysql_get_row_position/
위 블로그 참고해서 다른방법으로 ROW_NUMBER()안쓰고 순위매기기 처리하니깐 잘되네요 감사합니다.
count(*)로 해도 count(*) over (partition by 컬럼 order by 컬럼) 처리를 넣으며 집계함수이므로 에러가 발생하네요.
ROW_NUMBER() OVER(PARTITION BY 컬럼 ORDER BY 컬럼) 같은 집계함수 안쓰고
사용할수있는방법이 있는지 궁금합니다.
방법을 알려 드렸는데. 방법을 또 물으시면?....
아 따로 집계함수쓴것을 WITH절로 빼라고 하셨는데
아래 쿼리 같은경우
WITH절로 따로 빼게되면
안에 INNER JOIN으로 RECURSIVE처리된 T_SAMPLE테이블이 존재하지 않는다고 나오는데
올려주신 예시처럼
t1 명칭으로 따로 집계함수 포함된것을 뺄때
저같은 경우 재귀호출 with절 명칭인 T_SAMPLE 까지 가져가게되어서
그런경우 T_SAMPLE테이블이 없다고 나와
궁금하여 질문드립니다.
->
SELECT
B.TOP_NUM, A.MAIN_CODE, B.MAIN_LV + 1 AS MAIN_LV,
CONCAT(C.NNO, RIGHT(CONCAT('000', ROW_NUMBER() OVER (PARTITION BY A.UP_MAIN_CODE ORDER BY A.MAIN_CODE)), 3)) AS NNO
FROM TEST_TB AS A
INNER JOIN T_SAMPLE B
ON A.UP_MAIN_CODE = B.MAIN_CODE
WHERE
A.KEY_NO = '1'
글쎄요?
위에 제가 작성한 샘플은 직접 테스트 해서 결과 확인한 샘플입니다.
제 쿼리의 작성 스타일을 참고하시기 바랍니다.
t_sample 부분이 상단 with 절에 들어가면 안되죠.
가지고 있던 유사한 테이블로 테스트 했습니다.
테이블명 컬럼명만 해당 이름으로 바꾸시면 됩니다.
WITH t1 AS
(
SELECT key_no
, main_code
, up_main_code
, CAST(LPAD(
ROW_NUMBER() OVER(PARTITION BY key_no, up_main_code ORDER BY main_code)
, 3, '0') AS VARCHAR(99)) rn
FROM test_tb
)
, t3 AS
(
WITH RECURSIVE t2 AS
(
SELECT key_no
, main_code
, up_main_code
, main_code top_num
, 1 main_lv
, rn nno
FROM t1
WHERE up_main_code IS NULL
AND key_no = '1'
UNION ALL
SELECT a.key_no
, a.main_code
, a.up_main_code
, b.top_num
, b.main_lv + 1 main_lv
, CONCAT(b.nno, a.rn) nno
FROM t1 a
INNER JOIN t2 b
WHERE a.up_main_code = b.main_code
AND a.key_no = b.key_no
)
SELECT *
FROM t2
)
SELECT *
FROM t3
;
안녕하세요.
어제 참고하라신 쿼리를 적용해서 테스트를 해보았는데
RECURSIVE t2 WITH절안에 union all아래있는 SELECT문에서
b.main_lv + 1 main_lv에 의해서 재귀를 돌면서
main_lv가 증가되면 CONCAT(b.nno, a.rn) nno에 의해서
예를들어 main_lv가 1인경우 001인것이
2가 되는 경우 재귀를 돌게 되므로 CONCAT(b.nno, a.rn)에 의해서
001001 이렇게 되어야 하는데 001로 그대로 조회되서
t3 로 with recursive를 감싸서 select문을 조회해서 그런지
재귀가 적용이 안되는것을 보면
t3를 다시 recursive처리를 해야되는건지 궁금하여 질문드립니다.
concat 을 했는데도 자리수가 증가하지 않았다면?
cast 를 통해 자리수를 늘려주지 않았을 것으로 생각되네요.
CAST는 문자열 결합이 아닌데 CONCAT처럼 결합기능을 가진건가요? 형변환으로 알고있는데 궁금하여 질문드립니다.
네 형변환 함수입니다.
형변환해서 자리수 늘려주라는 것입니다.
3자리 -> 99자리
001, 002, 003을 재귀돌아서
기존 001에 001을 002에 002를 003에 003을 concat하는 식으로
001001
002002
003003...
나오게 하는건데
concat가 안먹히는것이면
t3로 감싼것을 다시 재귀를 돌려야되는거지 궁금하여 질문드립니다.
이상하네요?
왜? 같은 값끼리 붙여야 하죠?
제가 드린 쿼리는 제가 가진 유사한 테이블 emp로 테스트 후에 드린 쿼리입니다.
적용하신 쿼리가 제 쿼리와 뭔가 다른 부분이 있다던가 할 듯 한데?
마지막 질문을 보면 뭘 하고자 하는지 의심이 갑니다.
왜? 같은 값끼리 붙여야 하는지?
cast 함수를 제가 드린 쿼리대로 사용하신게 맞는지?
제가 드린 쿼리를 참조하여 적용하신 쿼리를 보여주세요.
main_lv별로 순번을 구분하기위해서
lv가 1인경우 001, 002, 003 인데
그걸재귀를 돌아서 lv가 증가해서 2일경우
CONCAT써서 001에 001결합하는식으로
lv가 2인경우에는 001001, 002002, 003003 이렇게 하는식으로
재귀를 돌려고 합니다.
저게 오라클에서는 되는데
mysql/mariadb환경에서는 recursive안에서 집계함수쓸경우 오류가 발생해서질문을 드린것이었습니다.ㅠㅠㅠ
계속 같은 질문 반복이네요?
그러면 계속 같은 답변 반복해야 할까요?
정상 실행되는거 확인한 쿼리 올려드린 건데요?
) 버튼을 클릭하여 작성 하시면 됩니다.