| Bug #32875 | wrong result by using index | ||
|---|---|---|---|
| Submitted: | 30 Nov 2007 12:49 | Modified: | 30 Nov 2007 13:00 |
| Reporter: | Sadao Hiratsuka (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.50 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Correlated Subquery, INDEX, wrong result | ||
[30 Nov 2007 13:00]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
c:\dev>5.0\bin\mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.52-nt Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database db9;
Query OK, 1 row affected (0.00 sec)
mysql> use db9
Database changed
mysql> create table gtest (c1 int, c2 int, c3 int, c4 int);
Query OK, 0 rows affected (0.16 sec)
mysql> insert into gtest (c1, c2, c3) values (1, 3, 7);
Query OK, 1 row affected (0.00 sec)
mysql> insert into gtest (c1, c2, c3) values (2, 4, 7);
Query OK, 1 row affected (0.00 sec)
mysql> insert into gtest (c1, c2, c3) values (2, 4, 9);
Query OK, 1 row affected (0.00 sec)
mysql> insert into gtest (c1, c2, c3) values (1, 1, 9);
Query OK, 1 row affected (0.00 sec)
mysql> insert into gtest (c1, c2, c3) values (3, 7, 11);
Query OK, 1 row affected (0.00 sec)
mysql> update gtest set c4 = c3 - c2;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select c1, c2, c3, c4
-> from gtest t1
-> where (t1.c1, t1.c4) in
-> (
-> select c1, min(c4)
-> from gtest t2
-> where t1.c1 = t2.c1
-> group by c1
-> )
-> ;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 1 | 3 | 7 | 4 |
| 2 | 4 | 7 | 3 |
| 3 | 7 | 11 | 4 |
+------+------+------+------+
3 rows in set (0.01 sec)
mysql>
mysql> create index idx01 on gtest (c1, c4);
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> select c1, c2, c3, c4
-> from gtest t1
-> where (t1.c1, t1.c4) in
-> (
-> select c1, min(c4)
-> from gtest t2
-> where t1.c1 = t2.c1
-> group by c1
-> )
-> ;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 1 | 3 | 7 | 4 |
| 2 | 4 | 7 | 3 |
| 3 | 7 | 11 | 4 |
+------+------+------+------+
3 rows in set (0.01 sec)
mysql>
Description: I got a wrong result by the following situation. * using correlated subquery * using min() function * using index How to repeat: mysql> create table gtest (c1 int, c2 int, c3 int, c4 int); mysql> insert into gtest (c1, c2, c3) values (1, 3, 7); mysql> insert into gtest (c1, c2, c3) values (2, 4, 7); mysql> insert into gtest (c1, c2, c3) values (2, 4, 9); mysql> insert into gtest (c1, c2, c3) values (1, 1, 9); mysql> insert into gtest (c1, c2, c3) values (3, 7, 11); mysql> update gtest set c4 = c3 - c2; mysql> commit; mysql> select c1, c2, c3, c4 -> from gtest t1 -> where (t1.c1, t1.c4) in -> ( -> select c1, min(c4) -> from gtest t2 -> where t1.c1 = t2.c1 -> group by c1 -> ) -> ; +------+------+------+------+ | c1 | c2 | c3 | c4 | +------+------+------+------+ | 1 | 3 | 7 | 4 | | 2 | 4 | 7 | 3 | | 3 | 7 | 11 | 4 | +------+------+------+------+ 3 rows in set (0.09 sec) mysql> create index idx01 on gtest (c1, c4); mysql> select c1, c2, c3, c4 -> from gtest t1 -> where (t1.c1, t1.c4) in -> ( -> select c1, min(c4) -> from gtest t2 -> where t1.c1 = t2.c1 -> group by c1 -> ) -> ; +------+------+------+------+ | c1 | c2 | c3 | c4 | +------+------+------+------+ | 1 | 3 | 7 | 4 | | 2 | 4 | 7 | 3 | ***** c1:3 is missing ! ***** +------+------+------+------+ 2 rows in set (0.03 sec) mysql> drop index idx01 on gtest; mysql> select c1, c2, c3, c4 -> from gtest t1 -> where (t1.c1, t1.c4) in -> ( -> select c1, min(c4) -> from gtest t2 -> where t1.c1 = t2.c1 -> group by c1 -> ) -> ; +------+------+------+------+ | c1 | c2 | c3 | c4 | +------+------+------+------+ | 1 | 3 | 7 | 4 | | 2 | 4 | 7 | 3 | | 3 | 7 | 11 | 4 | ***** fixed ***** +------+------+------+------+ 3 rows in set (0.02 sec) Suggested fix: I think Optimizer can't treat this query propery.