MySQL Bugs: #16603: Row subquery gives different result when index is added

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:	None
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

Description:
When the row subquery is used without any index on the table, it result is as expected. But if the index is added to the column used in GROUP BY clause, the query returns unexpected result

How to repeat:
Here is the output of the queries:

mysql> desc demo;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | YES  |     | NULL    |       |
| login  | datetime    | YES  |     | NULL    |       |
| logout | datetime    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from demo;
+------+---------------------+---------------------+
| name | login               | logout              |
+------+---------------------+---------------------+
| 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 |
+------+---------------------+---------------------+
11 rows in set (0.00 sec)

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.00 sec)

mysql> alter table demo add index(name);
Query OK, 11 rows affected (0.20 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> desc demo;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | YES  | MUL | NULL    |       |
| login  | datetime    | YES  |     | NULL    |       |
| logout | datetime    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

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.03 sec)

mysql>

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)

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,

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

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

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)