Description:
# Description
- SQL-1 & SQL-2 return correct results on 5.7.
- SQL-1 return correct results on 8.0
- SQL-2 return wrong results on 8.0
- Difference between SQL-1 & SQL-2 is line 10. In line 10, SQL-1 used a physical test table 'pim_office_hierachy_t', while SQL-2 used a VIEW 'pim_office_hierachy_v'.
- Test Table 'pim_office_hierachy_t' is created for test purpose. It contains same contents as VIEW 'pim_office_hierachy_v'.
- Testing environment is static, there is no data changes.
# SQL-1
SELECT DISTINCT ugp.USER_PROFILE_RID, ugp.GOVHRMS_ID, staff.STAFF_RID, staff.office_id FROM com_user_profile ugp
INNER JOIN com_user_group_member ugm ON
( ugm.EFFECTIVE_DATE <= CURRENT_DATE () AND IFNULL( ugm.OBSOLETE_DATE, '3000-01-01' ) >= CURRENT_DATE () AND ugm.USER_PROFILE_RID = ugp.USER_PROFILE_RID )
INNER JOIN com_user_group_data cugd ON
( cugd.USER_GROUP_PROFILE_RID = ugm.USER_GROUP_PROFILE_RID AND cugd.MODULE = 'PIM' AND cugd.allow_read IN ('1', 'Y'))
INNER JOIN pim_staff staff ON
(
( staff.grade_id = cugd.GRADE_ID OR cugd.GRADE_ID IS NULL ) AND
( staff.rank_id = cugd.rank_id OR cugd.RANK_ID IS NULL ) AND
( staff.office_id IN ( SELECT office_id FROM pim_office_hierachy_t WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%')) or (cugd.office_id IS NULL) ) AND
( staff.type_of_staff_id = cugd.STAFF_TYPE_ID OR cugd.staff_type_id IS NULL ))
AND ugp.USER_PROFILE_RID = 132317;
# SQL-2
SELECT DISTINCT ugp.USER_PROFILE_RID, ugp.GOVHRMS_ID, staff.STAFF_RID, staff.office_id FROM com_user_profile ugp
INNER JOIN com_user_group_member ugm ON
( ugm.EFFECTIVE_DATE <= CURRENT_DATE () AND IFNULL( ugm.OBSOLETE_DATE, '3000-01-01' ) >= CURRENT_DATE () AND ugm.USER_PROFILE_RID = ugp.USER_PROFILE_RID )
INNER JOIN com_user_group_data cugd ON
( cugd.USER_GROUP_PROFILE_RID = ugm.USER_GROUP_PROFILE_RID AND cugd.MODULE = 'PIM' AND cugd.allow_read IN ('1', 'Y'))
INNER JOIN pim_staff staff ON
(
( staff.grade_id = cugd.GRADE_ID OR cugd.GRADE_ID IS NULL ) AND
( staff.rank_id = cugd.rank_id OR cugd.RANK_ID IS NULL ) AND
( staff.office_id IN ( SELECT office_id FROM pim_office_hierachy_v WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%')) or (cugd.office_id IS NULL) ) AND
( staff.type_of_staff_id = cugd.STAFF_TYPE_ID OR cugd.staff_type_id IS NULL ))
AND ugp.USER_PROFILE_RID = 132317;
# VIEW
CREATE ALGORITHM=UNDEFINED DEFINER=`xxxxxxxx`@`%`
SQL SECURITY DEFINER
VIEW `pim_office_hierachy_v` AS
select `pos`.`OFFICE_ID` AS `office_id`,
group_concat(concat('#',`pos`.`LEVEL_OFFICE_ID`,'#') order by `pos`.`LEVEL` ASC separator ',') AS `OFFICE_HIERACHY`,
count(0) AS `CALCULATED_LEVEL`,
`pos`.`LEVEL` AS `LEVEL`
from `pim_office_structure` `pos`
group by `pos`.`OFFICE_ID`
order by `pos`.`LEVEL`
desc;
# Table 'pim_office_structure'
Table: pim_office_structure
Create Table: CREATE TABLE `pim_office_structure` (
`OFFICE_STRUCTURE_RID` bigint NOT NULL AUTO_INCREMENT,
`OFFICE_STRUCTURE_ID` int DEFAULT NULL,
`OFFICE_ID` varchar(10) NOT NULL,
`LEVEL_OFFICE_TITLE` varchar(500) DEFAULT NULL,
`LEVEL_OFFICE_ID` varchar(10) DEFAULT NULL,
`LEVEL` int DEFAULT NULL,
`VERSION` int NOT NULL DEFAULT '1',
`CREATE_BY` varchar(10) NOT NULL COMMENT 'xxxxxxxx',
`CREATE_DATETIME` datetime NOT NULL COMMENT 'xxxxxxxx',
`MODIFY_BY` varchar(10) NOT NULL COMMENT 'xxxxxxxx',
`MODIFY_DATETIME` datetime NOT NULL COMMENT 'xxxxxxxx',
PRIMARY KEY (`OFFICE_STRUCTURE_RID`)
) ENGINE=InnoDB AUTO_INCREMENT=xxxxx DEFAULT CHARSET=utf8mb3
# Test Table 'pim_office_hierachy_t'
Table: pim_office_hierachy_t
Create Table: CREATE TABLE `pim_office_hierachy_t` (
`office_id` varchar(10) NOT NULL,
`OFFICE_HIERACHY` text,
`CALCULATED_LEVEL` bigint NOT NULL,
`LEVEL` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
# count(*) of physical test table 'pim_office_hierachy_t'
mysql> select count(*) from pim_office_hierachy_t;
+----------+
| count(*) |
+----------+
| 241 |
+----------+
1 row in set (0.04 sec)
# count(*) of VIEW 'pim_office_hierachy_v'
mysql> select count(*) from pim_office_hierachy_v;
+----------+
| count(*) |
+----------+
| 241 |
+----------+
1 row in set (0.00 sec)
How to repeat:
Run SQL-1 & SQL-2 on 5.7 & 8.0
Description: # Description - SQL-1 & SQL-2 return correct results on 5.7. - SQL-1 return correct results on 8.0 - SQL-2 return wrong results on 8.0 - Difference between SQL-1 & SQL-2 is line 10. In line 10, SQL-1 used a physical test table 'pim_office_hierachy_t', while SQL-2 used a VIEW 'pim_office_hierachy_v'. - Test Table 'pim_office_hierachy_t' is created for test purpose. It contains same contents as VIEW 'pim_office_hierachy_v'. - Testing environment is static, there is no data changes. # SQL-1 SELECT DISTINCT ugp.USER_PROFILE_RID, ugp.GOVHRMS_ID, staff.STAFF_RID, staff.office_id FROM com_user_profile ugp INNER JOIN com_user_group_member ugm ON ( ugm.EFFECTIVE_DATE <= CURRENT_DATE () AND IFNULL( ugm.OBSOLETE_DATE, '3000-01-01' ) >= CURRENT_DATE () AND ugm.USER_PROFILE_RID = ugp.USER_PROFILE_RID ) INNER JOIN com_user_group_data cugd ON ( cugd.USER_GROUP_PROFILE_RID = ugm.USER_GROUP_PROFILE_RID AND cugd.MODULE = 'PIM' AND cugd.allow_read IN ('1', 'Y')) INNER JOIN pim_staff staff ON ( ( staff.grade_id = cugd.GRADE_ID OR cugd.GRADE_ID IS NULL ) AND ( staff.rank_id = cugd.rank_id OR cugd.RANK_ID IS NULL ) AND ( staff.office_id IN ( SELECT office_id FROM pim_office_hierachy_t WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%')) or (cugd.office_id IS NULL) ) AND ( staff.type_of_staff_id = cugd.STAFF_TYPE_ID OR cugd.staff_type_id IS NULL )) AND ugp.USER_PROFILE_RID = 132317; # SQL-2 SELECT DISTINCT ugp.USER_PROFILE_RID, ugp.GOVHRMS_ID, staff.STAFF_RID, staff.office_id FROM com_user_profile ugp INNER JOIN com_user_group_member ugm ON ( ugm.EFFECTIVE_DATE <= CURRENT_DATE () AND IFNULL( ugm.OBSOLETE_DATE, '3000-01-01' ) >= CURRENT_DATE () AND ugm.USER_PROFILE_RID = ugp.USER_PROFILE_RID ) INNER JOIN com_user_group_data cugd ON ( cugd.USER_GROUP_PROFILE_RID = ugm.USER_GROUP_PROFILE_RID AND cugd.MODULE = 'PIM' AND cugd.allow_read IN ('1', 'Y')) INNER JOIN pim_staff staff ON ( ( staff.grade_id = cugd.GRADE_ID OR cugd.GRADE_ID IS NULL ) AND ( staff.rank_id = cugd.rank_id OR cugd.RANK_ID IS NULL ) AND ( staff.office_id IN ( SELECT office_id FROM pim_office_hierachy_v WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%')) or (cugd.office_id IS NULL) ) AND ( staff.type_of_staff_id = cugd.STAFF_TYPE_ID OR cugd.staff_type_id IS NULL )) AND ugp.USER_PROFILE_RID = 132317; # VIEW CREATE ALGORITHM=UNDEFINED DEFINER=`xxxxxxxx`@`%` SQL SECURITY DEFINER VIEW `pim_office_hierachy_v` AS select `pos`.`OFFICE_ID` AS `office_id`, group_concat(concat('#',`pos`.`LEVEL_OFFICE_ID`,'#') order by `pos`.`LEVEL` ASC separator ',') AS `OFFICE_HIERACHY`, count(0) AS `CALCULATED_LEVEL`, `pos`.`LEVEL` AS `LEVEL` from `pim_office_structure` `pos` group by `pos`.`OFFICE_ID` order by `pos`.`LEVEL` desc; # Table 'pim_office_structure' Table: pim_office_structure Create Table: CREATE TABLE `pim_office_structure` ( `OFFICE_STRUCTURE_RID` bigint NOT NULL AUTO_INCREMENT, `OFFICE_STRUCTURE_ID` int DEFAULT NULL, `OFFICE_ID` varchar(10) NOT NULL, `LEVEL_OFFICE_TITLE` varchar(500) DEFAULT NULL, `LEVEL_OFFICE_ID` varchar(10) DEFAULT NULL, `LEVEL` int DEFAULT NULL, `VERSION` int NOT NULL DEFAULT '1', `CREATE_BY` varchar(10) NOT NULL COMMENT 'xxxxxxxx', `CREATE_DATETIME` datetime NOT NULL COMMENT 'xxxxxxxx', `MODIFY_BY` varchar(10) NOT NULL COMMENT 'xxxxxxxx', `MODIFY_DATETIME` datetime NOT NULL COMMENT 'xxxxxxxx', PRIMARY KEY (`OFFICE_STRUCTURE_RID`) ) ENGINE=InnoDB AUTO_INCREMENT=xxxxx DEFAULT CHARSET=utf8mb3 # Test Table 'pim_office_hierachy_t' Table: pim_office_hierachy_t Create Table: CREATE TABLE `pim_office_hierachy_t` ( `office_id` varchar(10) NOT NULL, `OFFICE_HIERACHY` text, `CALCULATED_LEVEL` bigint NOT NULL, `LEVEL` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 # count(*) of physical test table 'pim_office_hierachy_t' mysql> select count(*) from pim_office_hierachy_t; +----------+ | count(*) | +----------+ | 241 | +----------+ 1 row in set (0.04 sec) # count(*) of VIEW 'pim_office_hierachy_v' mysql> select count(*) from pim_office_hierachy_v; +----------+ | count(*) | +----------+ | 241 | +----------+ 1 row in set (0.00 sec) How to repeat: Run SQL-1 & SQL-2 on 5.7 & 8.0