| Bug #112159 | VIEW reference in a SELECT subquery returns different results from using TABLE | ||
|---|---|---|---|
| Submitted: | 23 Aug 2023 9:03 | Modified: | 23 Aug 2023 13:29 |
| Reporter: | IVAN HO | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S7 (Test Cases) |
| Version: | 8.0.33 | OS: | Red Hat (RHEL 8.8) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | VIEW | ||
[23 Aug 2023 9:04]
IVAN HO
Test Case
Attachment: testcase.sql (application/octet-stream, text), 3.09 KiB.
[23 Aug 2023 9:05]
IVAN HO
Test Case Result
Attachment: testcase result.sql (application/octet-stream, text), 1.68 KiB.
[23 Aug 2023 13:29]
MySQL Verification Team
Hi Mr. HO, Thank you for your bug report. First of all, you are not using only_full_group_by , which is now mandatory. That is a first reason why you have different results. Next, your original table and the view have totally different structures: Table Create Table tb_office CREATE TABLE `tb_office` (\n `OFFICE_ID` varchar(10) NOT NULL,\n `LEVEL_OFFICE_ID` varchar(10) DEFAULT NULL,\n `LEVEL` int DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table tb_office_hierachy CREATE TABLE `tb_office_hierachy` (\n `office_id` varchar(10) NOT NULL,\n `OFFICE_HIERACHY` text,\n `CALCULATED_LEVEL` bigint NOT NULL DEFAULT '0',\n `LEVEL` int DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Hence, using different tables and expecting the same result is very unlikely. Next, contents of the first table and contents of the view are totally different: OFFICE_ID LEVEL_OFFICE_ID LEVEL 1 1 1 1101 1 2 1201 1 2 1102 1101 3 1103 1101 3 1104 1101 3 1202 1201 3 1203 1201 3 1204 1201 3 2 2 1 2101 2 2 2201 2 2 2301 2 2 2102 2101 3 2103 2101 3 2104 2101 3 2202 2201 3 2203 2201 3 2204 2201 3 2302 2301 3 2303 2301 3 2304 2301 3 2305 2301 3 9999 1 1 9999 2 2 9999 2101 3 9999 9999 4 office_id OFFICE_HIERACHY CALCULATED_LEVEL LEVEL 9999 #9999# 1 4 1102 #1101# 1 3 1103 #1101# 1 3 1104 #1101# 1 3 1202 #1201# 1 3 1203 #1201# 1 3 1204 #1201# 1 3 2102 #2101# 1 3 2103 #2101# 1 3 2104 #2101# 1 3 2202 #2201# 1 3 2203 #2201# 1 3 2204 #2201# 1 3 2302 #2301# 1 3 2303 #2301# 1 3 2304 #2301# 1 3 2305 #2301# 1 3 9999 #2101# 1 3 1101 #1# 1 2 1201 #1# 1 2 2101 #2# 1 2 2201 #2# 1 2 2301 #2# 1 2 9999 #2# 1 2 1 #1# 1 1 2 #2# 1 1 9999 #1# 1 1 Hence, this is not a bug.
Description: - SQL-1 return correct results - SQL-2 return wrong results - Difference between SQL-1 & SQL-2 is line 5. In line 5, SQL-1 used a physical test table 'tb_office_hierachy', while SQL-2 used a VIEW 'vw_office_hierachy'. - Test Table 'tb_office_hierachy' is created for test purpose. It contains same contents as VIEW 'vw_office_hierachy'. - Testing environment is static, there is no data changes. # SQL-1 (Expected Result when using table) select staff.staff_id, cugd.office_id from tb_user_profile ugp inner join tb_user_data cugd on (ugp.profile_id = cugd.profile_id) inner join tb_staff staff on ( staff.office_id IN ( SELECT office_id FROM tb_office_hierachy WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%')) or (cugd.office_id IS NULL) ) where ugp.profile_id = 2000003 ; # SQL-2 (Unexpected Result when using view) select staff.staff_id, cugd.office_id from tb_user_profile ugp inner join tb_user_data cugd on (ugp.profile_id = cugd.profile_id) inner join tb_staff staff on ( staff.office_id IN ( SELECT office_id FROM vw_office_hierachy WHERE OFFICE_HIERACHY LIKE CONCAT('%#', cugd.office_id, '#%')) or (cugd.office_id IS NULL) ) where ugp.profile_id = 2000003 ; # SQL-1 output on MySQL 8.0.33 +----------+-----------+ | staff_id | office_id | +----------+-----------+ | 5000003 | 9999 | +----------+-----------+ 1 row in set (0.01 sec) # SQL-2 output on MySQL 8.0.33 +----------+-----------+ | staff_id | office_id | +----------+-----------+ | 5000001 | 9999 | | 5000002 | 9999 | | 5000003 | 9999 | +----------+-----------+ 3 rows in set (0.00 sec) How to repeat: Run "testcase.sql" on MySQL 8.0.33.