Bug #96549 | MySQL query optimizer rewrites query and gets different results than MySQL5.5 | ||
---|---|---|---|
Submitted: | 15 Aug 2019 10:59 | Modified: | 28 Aug 2019 12:23 |
Reporter: | lalit Choudhary | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.6,5.7,8.0, 5.6.45, 5.7.27, 8.0.17 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[15 Aug 2019 10:59]
lalit Choudhary
[15 Aug 2019 10:59]
lalit Choudhary
load data
Attachment: bug_dump_mysql.sql (application/sql, text), 6.41 KiB.
[19 Aug 2019 5:47]
MySQL Verification Team
Hello Lalit, Thank you for the report and test case. Verified as described. regards, Umesh
[19 Aug 2019 5:48]
MySQL Verification Team
Test results - 8.0.17, 5.7.27, 5.6.45 and 5.5.62
Attachment: 96549.results (application/octet-stream, text), 40.92 KiB.
[28 Aug 2019 12:23]
Roy Lyseng
Hi lalit, we do not consider this to be a bug. The reason is that the subquery as presented here is non-deterministic, because it selects a column that is not part of the GROUP BY columns. If you specify only the subquery, you will see the message "Expression #1 of SELECT list is not in GROUP BY clause and contains non-aggregated column 'test.spf.id'". In the case of non-deterministic subqueries, the result of the complete query may differ. We do not provide a way to eliminate removal of GROUP BY clauses in such cases, but there is a workaround you can use if you are sure this is the result that you want. It involves using the ANY_VALUE() function and wrapping the subquery in a CTE (Common Table Expression): with q as (SELECT any_value(spf.id) spf_id FROM test.staff_preferred_facility_test spf JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id JOIN test.account_user_test fu ON fu.id = sp.staff_id JOIN test.account_test f ON fu.account_id = f.id WHERE f.account_name='Test_Account' GROUP BY spf.facility_id, sp.id ) SELECT now() date_created, NULL date_undo, spf.created_by_id created_by_id, spf.date_created staff_preferred_facility_date_created, spf.date_modified date_modified, spf.facility_id facility_id, spf.id spf_id, spf.modified_by_id modified_by_id, spf.staff_preferences_id staff_preferences_id, spf.default_facility default_facility FROM test.staff_preferred_facility_test spf JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id JOIN test.account_user_test fu ON fu.id = sp.staff_id JOIN test.account_test f ON fu.account_id = f.id WHERE f.account_name='Test_Account' AND spf.id NOT IN (SELECT spf_id from q); The workaround seems to be correct for 8.0. In 5.6 and 5.6, you should be able to replace the CTE with a derived table.