Bug #68424 | Queries with nested select distinct queries sometimes return incorrect results | ||
---|---|---|---|
Submitted: | 19 Feb 2013 6:59 | Modified: | 19 Apr 2013 4:47 |
Reporter: | Bryan Turner | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.6.10 | OS: | Linux (Xubuntu 12.10 x64) |
Assigned to: | CPU Architecture: | Any | |
Tags: | regression |
[19 Feb 2013 6:59]
Bryan Turner
[19 Feb 2013 6:59]
Bryan Turner
Table dump as a standalone file
Attachment: tables.dmp (application/octet-stream, text), 4.56 KiB.
[19 Feb 2013 10:04]
MySQL Verification Team
Hello Bryan, Thank you for the report. Verified as described on reported version. Thanks, Umesh
[20 Feb 2013 9:39]
Roy Lyseng
Hi Bryan, We will fix this problem as soon as possible. Please try this workaround while you wait: set optimizer_switch='loosescan=off';
[21 Feb 2013 4:33]
Bryan Turner
mysql> set optimizer_switch='loosescan=off'; Query OK, 0 rows affected (0.00 sec) Still the same query results, though.
[21 Feb 2013 14:51]
Guilhem Bichot
Hello Bryan. Thanks for helping us! One more question: could you please run the three queries below in sequence, and post their output? set optimizer_switch='loosescan=off'; explain select id, name from oops_user where id in (select distinct user_id from oops_perm where perm_id in (9, 5, 6, 7) and project_id is null and group_name is null and user_id is not null) order by name; select id, name from oops_user where id in (select distinct user_id from oops_perm where perm_id in (9, 5, 6, 7) and project_id is null and group_name is null and user_id is not null) order by name;
[25 Feb 2013 4:00]
Bryan Turner
Foremost, it appears I need to retract my previous statement that the workaround did not help. It appears that it does, but only for the mysql command line session where it was run. (I had tried to verify it previously by running that command from mysql and then connecting to it remotely; that use case still does not work). With that said, here's the requested output: mysql> set optimizer_switch='loosescan=off'; Query OK, 0 rows affected (0.00 sec) mysql> explain select id, name from oops_user where id in (select distinct user_id -> from oops_perm where perm_id in (9, 5, 6, 7) and project_id is null and -> group_name is null and user_id is not null) order by name; +----+-------------+-----------+-------+-----------------------------------------------------+--------------+---------+-------------------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+-----------------------------------------------------+--------------+---------+-------------------+------+------------------------------------+ | 1 | SIMPLE | oops_user | index | PRIMARY | uk_user_name | 767 | NULL | 9 | Using where; Using index | | 1 | SIMPLE | oops_perm | ref | fk_perm_project,fk_perm_user,granted_perm_weight_fk | fk_perm_user | 5 | oops.oops_user.id | 1 | Using where; FirstMatch(oops_user) | +----+-------------+-----------+-------+-----------------------------------------------------+--------------+---------+-------------------+------+------------------------------------+ 2 rows in set (0.01 sec) mysql> select id, name from oops_user where id in (select distinct user_id -> from oops_perm where perm_id in (9, 5, 6, 7) and project_id is null and -> group_name is null and user_id is not null) order by name; +----+-----------+ | id | name | +----+-----------+ | 2 | admin | | 1 | sysadmin | | 9 | xlicensed | +----+-----------+ 3 rows in set (0.00 sec) As soon as I restart mysql, though: mysql> select id, name from oops_user where id in (select distinct user_id -> from oops_perm where perm_id in (9, 5, 6, 7) and project_id is null and -> group_name is null and user_id is not null) order by name; +----+-----------+ | id | name | +----+-----------+ | 2 | admin | | 1 | sysadmin | | 1 | sysadmin | | 9 | xlicensed | | 9 | xlicensed | +----+-----------+ 5 rows in set (0.00 sec) Is there a way to apply this setting more permanently? Then I could check it with the tests that helped me discover it.
[25 Feb 2013 8:17]
Guilhem Bichot
Hello Bryan. Thanks for having double-checked! Your results are consistent with what I had found, I had tracked it to be a bug in the "semi-join LooseScan" algorithm. Indeed, when you do set optimizer_switch='loosescan=off'; it is equivalent to set session optimizer_switch='loosescan=off'; which affects only the current session. If you want to affect all sessions, you have to possibilities: 1) set global optimizer_switch='loosescan=off'; this will affect all new sessions until the server is restarted. 2) start the server with --optimizer_switch=loosescan=off (or add that to your MySQL server's configuration file, without '--') then this will affect all sessions. For your testing, this may be the simplest alternative. More details about SET: http://dev.mysql.com/doc/refman/5.6/en/mysqld-server.html
[5 Mar 2013 12:49]
Erlend Dahl
A fix for this will be part of the upcoming 5.6.11 and 5.7.1 releases.
[19 Apr 2013 4:47]
Bryan Turner
Verified in the new 5.6.11 release. Thanks for finding and fixing this issue!