with 절 서브쿼리 변수 캐싱 문제

Question

mysql 8.0의 실행 결과는 예상처럼 잘 되지만 마리아db에서는 변수를 캐싱하지 않고 계속 증가 시키므로 원하는 결과가 나오지 않습니다.DB Fiddle - SQL Database Playground (db-fiddle.com)CREATE TABLE test ( id INT, ex DATE, code CHAR(2) ); INSERT INTO test (id,ex,code) VALUES (1,'2000-01-01','A'),(1,'2000-01-02','B'),(1,'2000-01-03','C'); WITH arg AS ( SELECT @num:=0 ), tmp1 AS ( SELECT @num:=@num+1 idx, test.* FROM arg,test WHERE ex<'2001-01-01' ORDER BY id LIMIT 20 ) SELECT * FROM tmp1 a LEFT JOIN tmp1 b ON b.id=a.id AND b.idx=a.idx-1;idxidexcodeidxidexcode112000-01-01Anullnullnullnull212000-01-02B112000-01-01A312000-01-03C212000-01-02Bmariadb 버전은 제공하지 않아서 실행 결과를 보여줄 수 없지만 설명 하는 것이 그렇게 어려운 것은 아닌 것 같습니다.a 별칭을 준 테이블은 idx 가 1,2,3으로 나오며 b 별칭을 준 테이블은 idx가 4,5,6으로 식별됩니다.따라서 마리아db에서는 다음처럼 카운트를 빼주는 방식으로 써야 하는데요.WITH arg AS ( SELECT @num:=0 ), tmp1 AS ( SELECT @num:=@num+1 idx, test.* FROM arg,test WHERE ex<'2001-01-01' ORDER BY id LIMIT 20 ) SELECT * FROM tmp1 a LEFT JOIN tmp1 b ON b.id=a.id AND b.idx-(SELECT COUNT(*) FROM tmp1)=a.idx-1;그렇다면 mysql은 WITH 절에서 변수를 캐싱하며 mariadb는 WITH 절에서 변수를 캐싱하지 않는 것 같아요.음 ..혹시 모르는 것이니 좀 더 확인을 해보면 좋지 않겠나요.mysql에 쓴 쿼리를 그대로 mariadb에 집어넣으면 이렇게 ..idxidexcodeidxidexcode112000-01-01Anullnullnullnull212000-01-02Bnullnullnullnull312000-01-03Cnullnullnullnull진짜 혹시 모르는 것이므로 콘솔에 접속하여 직접 쿼리를 실행 해보면 ..MariaDB [example]> WITH arg AS ( SELECT @num:=0 ), tmp1 AS ( SELECT @num:=@num+1 idx, test.* FROM arg,test WHERE ex<'2001-01-01' ORDER BY id LIMIT 20 ) SELECT * FROM tmp1 a LEFT JOIN tmp1 b ON b.id=a.id AND b.idx=a.idx-1; +------+------+------------+------+------+------+------+------+ | idx | id | ex | code | idx | id | ex | code | +------+------+------------+------+------+------+------+------+ | 1 | 1 | 2000-01-01 | A | NULL | NULL | NULL | NULL | | 2 | 1 | 2000-01-02 | B | NULL | NULL | NULL | NULL | | 3 | 1 | 2000-01-03 | C | NULL | NULL | NULL | NULL | +------+------+------------+------+------+------+------+------+ 3 rows in set (0.001 sec)수정된 쿼리를 mariadb에 집어넣으면 이렇게 ..idxidexcodeidxidexcode112000-01-01Anullnullnullnull212000-01-02B412000-01-01A312000-01-03C512000-01-02BMariaDB [example]> WITH arg AS ( SELECT @num:=0 ), tmp1 AS ( SELECT @num:=@num+1 idx, test.* FROM arg,test WHERE ex<'2001-01-01' ORDER BY id LIMIT 20 ) SELECT * FROM tmp1 a LEFT JOIN tmp1 b ON b.id=a.id AND b.idx-(SELECT COUNT(*) FROM tmp1)=a.idx-1; +------+------+------------+------+------+------+------------+------+ | idx | id | ex | code | idx | id | ex | code | +------+------+------------+------+------+------+------------+------+ | 1 | 1 | 2000-01-01 | A | NULL | NULL | NULL | NULL | | 2 | 1 | 2000-01-02 | B | 4 | 1 | 2000-01-01 | A | | 3 | 1 | 2000-01-03 | C | 5 | 1 | 2000-01-02 | B | +------+------+------------+------+------+------+------------+------+ 3 rows in set (0.001 sec)에잇 .. 이것은 남한테 줘야 하는 프로그램에 마리아db를 안 쓰면 해결될 문제도 아닌 것 같습니다.어떤 웹호스팅 서비스는 마리아db를 사용 중일 것이고 WITH 절에 사용한 변수가 캐싱될 것이라고 예상한 코드는 버그가 나겠어요.결국 .. 항상 같진 않으므로 주의 하는 것이 좋지 않곘나요.Q: mysql은 with 절에서 증분 변수를 캐싱하지만 mariadb는 with 절에서 증분 변수를 캐싱하지 않는 원인이 무엇일까MySQL과 MariaDB는 둘 다 관계형 데이터베이스 시스템이며, MariaDB는 MySQL의 포크(fork)로 시작되었습니다. 그러나 두 시스템은 약간의 차이가 있을 수 있습니다. WITH 절 내부에서 증분 변수를 캐싱하지 않는 이유는 MySQL과 MariaDB가 서로 다른 최적화 및 구현 전략을 가지고 있기 때문입니다. 이러한 차이로 인해 변수 캐싱 동작에도 차이가 생길 수 있습니다. MariaDB에서 WITH 절 내부에서 변수를 사용할 때, 변수는 캐싱되지 않고 매번 새로운 값을 할당받게 됩니다. 이는 MariaDB가 쿼리의 각 부분을 별개의 임시 테이블로 처리하는 방식과 관련이 있습니다. WITH 절이 임시 테이블로 처리되면서 변수도 임시 테이블의 컬럼으로 취급되어 매번 새로운 값을 할당받게 됩니다. 반면에 MySQL은 WITH 절 내부에서 변수를 캐싱하여 동일한 값을 재사용하는 경우가 있습니다. 이는 MySQL의 최적화 방식과 관련이 있으며, 쿼리 실행 계획을 최적화하여 성능을 향상시키는 데 목적이 있습니다. 따라서, WITH 절 내부에서 증분 변수를 캐싱하는지 여부는 데이터베이스 시스템의 구현과 최적화 전략에 따라 달라질 수 있습니다. MySQL과 MariaDB는 비슷한 기반이지만 세부적인 동작 방식에 차이가 있을 수 있으므로, 각 데이터베이스 시스템의 공식 문서나 소스 코드를 참조하여 구체적인 동작 방식을 확인하는 것이 좋습니다.

with 절 서브쿼리 변수 캐싱 문제

0개의 답변

Q&A 목록