| Bug #112108 | VIEW reference in a SELECT subquery returns different results between 5.7 & 8.0 | ||
|---|---|---|---|
| Submitted: | 18 Aug 2023 7:13 | Modified: | 22 Aug 2023 7:18 |
| Reporter: | IVAN HO | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.33, 8.0.34 | OS: | Red Hat (8) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | view subquery | ||
[18 Aug 2023 7:14]
IVAN HO
test case
Attachment: testcase.sql (application/octet-stream, text), 3.09 KiB.
[18 Aug 2023 7:15]
IVAN HO
Test Case mysql 5.7.39 result
Attachment: testcase 5.7.39 result.sql (application/octet-stream, text), 1.62 KiB.
[18 Aug 2023 7:15]
IVAN HO
Test Case mysql 8.0.33 result
Attachment: testcase 8.0.33 result.sql (application/octet-stream, text), 1.68 KiB.
[21 Aug 2023 8:37]
MySQL Verification Team
Hello IVAN HO, Thank you for the report and test case. regards, Umesh
[22 Aug 2023 7:18]
Roy Lyseng
Posted by developer: We are closing this as not a bug because the view is non-deterministic. The selected expression pos.LEVEL may take different values, based on choices of the optimizer. Hence, due to the optimizer improving between releases, it is impossible to guarantee consistent results. We recommend that the view is rewritten without non-deterministic elements and that ONLY_FULL_GROUP_BY is enabled in SQL mode. It may also be an idea to avoid the use of ORDER BY in the view definition, since it is generally ignored when used in a query.
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 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 5.7.39 +----------+-----------+ | staff_id | office_id | +----------+-----------+ | 5000003 | 9999 | +----------+-----------+ 1 row in set (0.00 sec) # 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 5.7.39 +----------+-----------+ | 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 5.7.39 & 8.0.33.