Bug #16603 | Row subquery gives different result when index is added | ||
---|---|---|---|
Submitted: | 18 Jan 2006 12:31 | Modified: | 28 Feb 2006 1:05 |
Reporter: | Sid Gargate | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.0.16-nt/5.0.19/5.1.6 BK | OS: | Windows (Windows XP/Linux) |
Assigned to: | Igor Babaev | CPU Architecture: | Any |
[18 Jan 2006 12:31]
Sid Gargate
[18 Jan 2006 13:22]
MySQL Verification Team
Thank you for the bug report. This bug not affects 4.1. mysql> select version(); +------------------+ | version() | +------------------+ | 4.1.17-debug-log | +------------------+ 1 row in set (0.03 sec) mysql> create database db15; Query OK, 1 row affected (0.00 sec) mysql> use db15 Database changed mysql> Create Table demo (name varchar(20), -> login datetime, -> logout datetime) engine=MyISAM; Query OK, 0 rows affected (0.02 sec) mysql> Insert Into demo values -> ('AAA','2006-01-18 17:02:00','2006-01-18 18:03:03'), -> ('AAA','2006-01-18 17:02:10','2006-01-18 19:03:16'), -> ('AAA','2006-01-18 17:02:19','2006-01-18 17:09:25'), -> ('BBB','2006-01-18 17:02:28','2006-01-18 17:32:34'), -> ('BBB','2006-01-18 17:02:42','2006-01-18 18:32:42'), -> ('BBB','2006-01-18 17:02:51','2006-01-18 17:38:51'), -> ('AAA','2006-01-18 17:02:59','2006-01-18 19:04:05'), -> ('BBB','2006-01-18 17:03:05','2006-01-18 20:39:05'), -> ('CCC','2006-01-18 17:03:24','2006-01-18 17:33:30'), -> ('CCC','2006-01-18 17:03:32','2006-01-18 17:10:38'), -> ('AAA','2006-01-18 17:03:32','2006-01-18 17:36:33'); Query OK, 11 rows affected (0.01 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> select * from demo where (name,login) = any (select name,max(login) from -> demo group by name); +------+---------------------+---------------------+ | name | login | logout | +------+---------------------+---------------------+ | BBB | 2006-01-18 17:03:05 | 2006-01-18 20:39:05 | | CCC | 2006-01-18 17:03:32 | 2006-01-18 17:10:38 | | AAA | 2006-01-18 17:03:32 | 2006-01-18 17:36:33 | +------+---------------------+---------------------+ 3 rows in set (0.08 sec) mysql> alter table demo add index(name); Query OK, 11 rows affected (0.01 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> select * from demo where (name,login) = any (select name,max(login) from -> demo group by name); +------+---------------------+---------------------+ | name | login | logout | +------+---------------------+---------------------+ | BBB | 2006-01-18 17:03:05 | 2006-01-18 20:39:05 | | CCC | 2006-01-18 17:03:32 | 2006-01-18 17:10:38 | | AAA | 2006-01-18 17:03:32 | 2006-01-18 17:36:33 | +------+---------------------+---------------------+ 3 rows in set (0.09 sec) mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.19-debug | +--------------+ 1 row in set (0.00 sec) mysql> create database db15; Query OK, 1 row affected (0.00 sec) mysql> use db15 Database changed mysql> Create Table demo (name varchar(20), -> login datetime, -> logout datetime) engine=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> Insert Into demo values -> ('AAA','2006-01-18 17:02:00','2006-01-18 18:03:03'), -> ('AAA','2006-01-18 17:02:10','2006-01-18 19:03:16'), -> ('AAA','2006-01-18 17:02:19','2006-01-18 17:09:25'), -> ('BBB','2006-01-18 17:02:28','2006-01-18 17:32:34'), -> ('BBB','2006-01-18 17:02:42','2006-01-18 18:32:42'), -> ('BBB','2006-01-18 17:02:51','2006-01-18 17:38:51'), -> ('AAA','2006-01-18 17:02:59','2006-01-18 19:04:05'), -> ('BBB','2006-01-18 17:03:05','2006-01-18 20:39:05'), -> ('CCC','2006-01-18 17:03:24','2006-01-18 17:33:30'), -> ('CCC','2006-01-18 17:03:32','2006-01-18 17:10:38'), -> ('AAA','2006-01-18 17:03:32','2006-01-18 17:36:33'); Query OK, 11 rows affected (0.00 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> select * from demo where (name,login) = any (select name,max(login) from -> demo group by name); +------+---------------------+---------------------+ | name | login | logout | +------+---------------------+---------------------+ | BBB | 2006-01-18 17:03:05 | 2006-01-18 20:39:05 | | CCC | 2006-01-18 17:03:32 | 2006-01-18 17:10:38 | | AAA | 2006-01-18 17:03:32 | 2006-01-18 17:36:33 | +------+---------------------+---------------------+ 3 rows in set (0.05 sec) mysql> alter table demo add index(name); Query OK, 11 rows affected (0.02 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> select * from demo where (name,login) = any (select name,max(login) from -> demo group by name); +------+---------------------+---------------------+ | name | login | logout | +------+---------------------+---------------------+ | CCC | 2006-01-18 17:03:32 | 2006-01-18 17:10:38 | +------+---------------------+---------------------+ 1 row in set (0.01 sec) mysql> select version(); +-------------------+ | version() | +-------------------+ | 5.1.6-alpha-debug | +-------------------+ 1 row in set (0.03 sec) mysql> create database db15; Query OK, 1 row affected (0.01 sec) mysql> use db15 Database changed mysql> Create Table demo (name varchar(20), -> login datetime, -> logout datetime) engine=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> Insert Into demo values -> ('AAA','2006-01-18 17:02:00','2006-01-18 18:03:03'), -> ('AAA','2006-01-18 17:02:10','2006-01-18 19:03:16'), -> ('AAA','2006-01-18 17:02:19','2006-01-18 17:09:25'), -> ('BBB','2006-01-18 17:02:28','2006-01-18 17:32:34'), -> ('BBB','2006-01-18 17:02:42','2006-01-18 18:32:42'), -> ('BBB','2006-01-18 17:02:51','2006-01-18 17:38:51'), -> ('AAA','2006-01-18 17:02:59','2006-01-18 19:04:05'), -> ('BBB','2006-01-18 17:03:05','2006-01-18 20:39:05'), -> ('CCC','2006-01-18 17:03:24','2006-01-18 17:33:30'), -> ('CCC','2006-01-18 17:03:32','2006-01-18 17:10:38'), -> ('AAA','2006-01-18 17:03:32','2006-01-18 17:36:33'); Query OK, 11 rows affected (0.02 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> select * from demo where (name,login) = any (select name,max(login) from -> demo group by name); +------+---------------------+---------------------+ | name | login | logout | +------+---------------------+---------------------+ | BBB | 2006-01-18 17:03:05 | 2006-01-18 20:39:05 | | CCC | 2006-01-18 17:03:32 | 2006-01-18 17:10:38 | | AAA | 2006-01-18 17:03:32 | 2006-01-18 17:36:33 | +------+---------------------+---------------------+ 3 rows in set (0.04 sec) mysql> alter table demo add index(name); Query OK, 11 rows affected (0.03 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> select * from demo where (name,login) = any (select name,max(login) from -> demo group by name); +------+---------------------+---------------------+ | name | login | logout | +------+---------------------+---------------------+ | CCC | 2006-01-18 17:03:32 | 2006-01-18 17:10:38 | +------+---------------------+---------------------+ 1 row in set (0.01 sec)
[10 Feb 2006 4:08]
Igor Babaev
I succeeded to demonstrate the same problem with a slightly more simple test case: mysql> CREATE TABLE t1 (a varchar(5), b varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES -> ('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2), -> ('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); +------+------+ | a | b | +------+------+ | BBB | 4 | | CCC | 7 | | AAA | 8 | +------+------+ 3 rows in set (0.01 sec) mysql> EXPLAIN -> SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); +----+--------------------+-------+------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+------+---------+------+------+---------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 9 | Using where | | 2 | DEPENDENT SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 9 | Using temporary; Using filesort | +----+--------------------+-------+------+---------------+------+---------+------+------+---------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> ALTER TABLE t1 ADD INDEX(a); Query OK, 9 rows affected (0.27 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); +------+------+ | a | b | +------+------+ | CCC | 7 | +------+------+ 1 row in set (0.01 sec) mysql> EXPLAIN -> SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); +----+--------------------+-------+-------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+---------------+------+---------+------+------+----------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 9 | Using where | | 2 | DEPENDENT SUBQUERY | t1 | index | NULL | a | 8 | NULL | 9 | Using filesort | +----+--------------------+-------+-------+---------------+------+---------+------+------+----------------+ 2 rows in set (0.00 sec) Pay attention to the fact that the above two queries use different access method to get rows in the subquery,
[14 Feb 2006 2:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/2564
[20 Feb 2006 7:47]
Igor Babaev
ChangeSet 1.2048 06/02/13 18:50:06 igor@rurik.mysql.com +3 -0 Fixed bug #16603. A subquery transformation changes the HAVING clause of the embedding query if the subquery contains a GROUP BY clause. Yet the split_sum_func2 function was not applied to the modified HAVING clause. This could result in wrong answers. sql/sql_select.cc 1.394 06/02/13 18:50:00 igor@rurik.mysql.com +15 -14 Fixed bug #16603. A subquery transformation changes the HAVING clause of the embedding query if the subquery contains a GROUP BY clause. Yet the split_sum_func2 function was not applied to the modified HAVING clause. This could result in wrong answers. mysql-test/t/subselect.test 1.113 06/02/13 18:50:00 igor@rurik.mysql.com +22 -0 Added a test case for bug #16603. mysql-test/r/subselect.result 1.133 06/02/13 18:50:00 igor@rurik.mysql.com +29 -3 Added a test case for bug #16603. The fix will appear in 5.0.19 and 5.1.8
[28 Feb 2006 1:05]
Paul DuBois
Noted in 5.0.19, 5.1.7 changelogs. If the query optimizer transformed a <literal>GROUP BY</literal> clause in a subquery, it did not also transform the <literal>HAVING</literal> clause if there was one, producing incorrect results. (Bug #16603)