Bug #40136 Group by is ignored when join buffer is used for an outer join

• Submitted: 19 Oct 2008 4:33
• Modified: 22 Nov 2010 1:30
• Reporter: Igor Babaev
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 6.0-BKA
• OS: Any
• Assigned to: Sergey Petrunya
• CPU Architecture: Any

[19 Oct 2008 4:33] Igor Babaev

Description:
The execution plan erroneously omits the grouping operation when the optimizer decides to use a join buffer for an outer join operation from a query with a group by clause that groups by the primary key of the outer table. 
The grouping operation can be really omitted for such queries only when plain nested loops join algorithm is employed. When BKA join algorithm is used this omission is not valid.

How to repeat:
The following command sequence demonstrates the problem:

create table t1(a int PRIMARY KEY, b int);
insert into t1 values
  (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
create table t2 (p int, a int, INDEX i_a(a));
insert into t2 values
  (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
  (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
mysql> set join_cache_level=6;
explain
select t1.a, count(t2.p) as count
  from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
select t1.a, count(t2.p) as count
  from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;

When running these commands we have a wrong execution plan and a wrong result set for the executed query:

mysql> explain select t1.a, count(t2.p) as count from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
+----+-------------+-------+-------+---------------+---------+---------+-----------+------+--------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref       | rows | Extra                          |
+----+-------------+-------+-------+---------------+---------+---------+-----------+------+--------------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 4       | NULL      |    4 | Using index                    |
|  1 | SIMPLE      | t2    | ref   | i_a           | i_a     | 5       | test.t1.a |    2 | Using where; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+-----------+------+--------------------------------+
2 rows in set (0.00 sec)

mysql> select t1.a, count(t2.p) as count from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
+---+-------+
| a | count |
+---+-------+
| 7 |     1 |
| 3 |     1 |
| 7 |     1 |
| 1 |     1 |
| 3 |     1 |
| 2 |     0 |
| 5 |     0 |
| 6 |     0 |
| 8 |     0 |
| 9 |     0 |
+---+-------+
10 rows in set (0.00 sec)

[19 Oct 2008 20:00] 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/56552

2659 Sergey Petrunia	2008-10-16
      BUG#40136: Group by is ignored when join buffer is used for an outer join
      - Fix a typo bug in make_join_orderinfo().

[20 Oct 2008 8:25] Sergey Petrunya

Pushed into mysql-6.0-bka-preview tree

[14 Dec 2008 11:07] Bugs System

Pushed into 6.0.8-alpha  (revid:[e-mail]-20081016111957-2mjerpagh1wtm8q7) (version source revid:[e-mail]-20081016111957-2mjerpagh1wtm8q7) (pib:5)

[16 Aug 2010 6:37] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100816062819-bluwgdq8q4xysmlg) (version source revid:[e-mail]-20100816062612-enatdwnv809iw3s9) (pib:20)

[13 Nov 2010 16:18] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

[22 Nov 2010 1:30] Paul DuBois

Bug is not in any released 5.6.x version. No changelog entry needed.