########################################################################################################################


# create testing schema
drop database if exists testcase3 ;
create database testcase3 ;
use testcase3 ;


# create and load source tables
source /tmp/testcase_loaddata.sql


# create view and experiment table
drop view if exists v9 ;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v9` AS 
select `pos`.`C10` AS `C10`,group_concat(concat('#',`pos`.`C12`,'#') order by `pos`.`C13` ASC separator ',') AS `C10_HIERACHY`,
count(0) AS `CALCULATED_LEVEL` 
from `T5` `pos` 
group by `pos`.`C10` 
order by 3 desc ;


drop table if exists T9 ;
create table T9 select * FROM v9 ;


# test case 1 using view
SELECT DISTINCT ugp.C01, ugm.C04, staff.C08, staff.C09, staff.C11, staff.C10  FROM T1 ugp 
INNER JOIN T2 ugm ON 
( ugm.C02 <= CURRENT_DATE () AND IFNULL( ugm.C03, '3000-01-01' ) >= CURRENT_DATE () AND ugm.C01 = ugp.C01 ) 
INNER JOIN T3 cugd ON 
( cugd.C04 = ugm.C04 AND cugd.C06 = 'M01' AND cugd.C07 IN ('1', 'Y')) 
INNER JOIN T4 staff ON 
(
( staff.C08 = cugd.C08 OR cugd.C08 IS NULL ) AND 
( staff.C09 = cugd.C09 OR cugd.C09 IS NULL ) AND 
( staff.C10 IN ( SELECT C10 FROM v9 WHERE C10_HIERACHY LIKE CONCAT('%#', cugd.C10, '#%'))  or (cugd.C10 IS NULL) ) AND 
( staff.C11 = cugd.C05 OR cugd.C05 IS NULL )) 
AND ugp.C01 = 5000044
;


# test case 2 using experiment table
SELECT DISTINCT ugp.C01, ugm.C04, staff.C08, staff.C09, staff.C11, staff.C10  FROM T1 ugp 
INNER JOIN T2 ugm ON 
( ugm.C02 <= CURRENT_DATE () AND IFNULL( ugm.C03, '3000-01-01' ) >= CURRENT_DATE () AND ugm.C01 = ugp.C01 ) 
INNER JOIN T3 cugd ON 
( cugd.C04 = ugm.C04 AND cugd.C06 = 'M01' AND cugd.C07 IN ('1', 'Y')) 
INNER JOIN T4 staff ON 
(
( staff.C08 = cugd.C08 OR cugd.C08 IS NULL ) AND 
( staff.C09 = cugd.C09 OR cugd.C09 IS NULL ) AND 
( staff.C10 IN ( SELECT C10 FROM T9 WHERE C10_HIERACHY LIKE CONCAT('%#', cugd.C10, '#%'))  or (cugd.C10 IS NULL) ) AND 
( staff.C11 = cugd.C05 OR cugd.C05 IS NULL )) 
AND ugp.C01 = 5000044
;


########################################################################################################################