[SQL] MYSQL vs MariaDB

통합검색 고도화 진행 후 반영 중에 있었던 일이다

 

개발서버에서 테스트를 해보고 반영을 해봤는데.. 운영서버에서는 SQL 에러로 입구 컷을 당했다

에러내용은 다음과 같았다

Unknown column 'menu_topname' in 'window partition by'

 

구글링을 해봐도.. 별짓을 다 해봐도 안돼서 도움을 요청했는데, 원인은 원칙적으로 oracle이나 mysql 같은 DBMS에서는 SELECT 구문에 alias로 선언한 변수를 그룹 함수인 partition by , order by에 바로 쓸 수 없다는 것이었다.

 

우리 개발서버는 MariaDB라고 하는데 이 MySQL이나 MariaDB 같은 DBMS는 커스터마이징이 꽤 돼있어서 쓸수 없는

쿼리도 사용할 수 있게 만들어 놓은거라고 하더라.. 아무튼 그래서 기존의 쿼리에서 그룹 함수에서 사용할 수 없는 alias 컬럼은 서브쿼리로 한 층 더빼서 구조를 변경했다

 

MYSQL Query

SELECT    
	bd_idx,    
    m.menu_type,
    m.menu_topname,
    ROW_NUMBER() OVER(PARTITION BY m.menu_topname order by m.menu_searchorder asc, IFNULL(tb.regdt , mc.regdt ) desc) ranIndex,    
    COUNT(1) OVER(PARTITION BY m.menu_topname) as menu_count,       
    m.menu_code,      
    m.menu_pcode,      
    m.menu_pfullname,    
    m.menu_name,   
    IFNULL(tb.bd_title, m.menu_name ) as title,  
    IFNULL(tb.bd_content, mc.mc_memo ) as content, 
    tb.bd_thumb1,   
    tb.bd_thumb1_alt, 
    m.menu_name,   
    IFNULL(tb.regdt , mc.regdt ) as regdt,   
    m.menu_searchorder as menu_fullordert,    
    bd_readnum 
FROM 
	(
		SELECT
			menu_type,
			menu_subtype,
			SUBSTRING_INDEX(SUBSTRING_INDEX(menu_pfullname, '>', 3), '>', -1) as menu_topname,
			menu_code,
			menu_pcode,
			menu_pfullname,
			menu_name,
			menu_searchorder,
			menu_sitecode,
			delyn
		FROM
			T_MENU
	) m   
	left JOIN T_BOARD tb 
		ON 
			m.menu_subtype = tb.bc_id 
			AND tb.delyn = 'N' 
			and m.menu_type = '3' 
			AND (tb.bd_title like concat('%', '창의', '%') OR tb.bd_content_text like concat('%', '창의' , '%'))      
			AND tb.bc_id NOT in ('minwon', 'clean', 'promotionENG', 'engGallery', 'inforSquareBoard')                                          
	left join t_menu_contents mc 
		on 
			m.menu_code = mc.menu_code      
			and m.menu_type = '1'      
			and mc_idx in (
					select max(mc_idx) as mc_idx 
                    from t_menu_contents mc 
                    where mc.delyn = 'N' 
                    and mc.mc_memo like concat('%', '창의' , '%') 
                    group by menu_code
			)   
where 
	m.delyn = 'N'    
    and m.menu_sitecode = 'web'    
    and (mc.mc_memo is not null or tb.bd_title is not null)        
    order by m.menu_searchorder asc, IFNULL(tb.regdt , mc.regdt ) desc;

MariaDB Query

SELECT 
	m.menu_type,
	ROW_NUMBER() OVER(PARTITION BY menu_fullordert order by regdt desc) ranIndex,
	SUBSTRING_INDEX(SUBSTRING_INDEX(m.menu_pfullname, '>', 3), '>', -1) as  menu_topname,
	COUNT(1) OVER(PARTITION BY menu_topname) as menu_count,
    m.menu_code,
    m.menu_pcode,
    m.menu_pfullname,
    m.menu_name,
	IFNULL(tb.bd_title, m.menu_name ) as title,
	IFNULL(tb.bd_content, mc.mc_memo ) as content,
	tb.bd_thumb1,
	tb.bd_thumb1_alt,	
	IFNULL(tb.regdt , mc.regdt ) as regdt,
	RPAD
	(
		CONCAT
		(
			LPAD((select da.menu_order from t_menu da where da.menu_code = SUBSTRING_INDEX(SUBSTRING_INDEX(m.menu_pfullcode, '>', 3), '>', -1)), 2, '0')
		),
		8, '0'
	) as menu_fullordert,
   bd_readnum
FROM 
	T_MENU m 
	left JOIN T_BOARD tb
		ON 
			m.menu_subtype = tb.bc_id 
			AND tb.delyn = 'N'
			and m.menu_type = '3'
			AND (tb.bd_title like concat('%', '창의', '%')
			OR tb.bd_content_text like concat('%', '창의' , '%'))
			AND tb.bc_id NOT in ('minwon', 'clean', 'promotionENG', 'engGallery', 'inforSquareBoard')
	left join t_menu_contents mc
		on
			m.menu_code = mc.menu_code 
			and m.menu_type = '1'
			and mc_idx in (
					select max(mc_idx) as mc_idx
					from t_menu_contents mc 
					where mc.delyn = 'N' 
					and mc.mc_memo like concat('%', '창의' , '%')
					group by menu_code
			)
where 
	m.delyn = 'N'
	and m.menu_sitecode = 'web'
	and (mc.mc_memo is not null or tb.bd_title is not null)
	order by menu_fullordert asc, regdt desc