LP:967242 - Wrong result (extra rows, not grouped) with JOIN, AND in ON condition, multi-part key, GROUP BY, OR in WHERE, MyISAM or Aria

The following query

SELECT t2_1.b
FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
WHERE
( SELECT COUNT FROM t2 ) IS NOT NULL
OR a = t2_1.c
GROUP BY t2_1.b;

on the test data returns multiple rows for some values of t2_1.b, which should not be happening because of GROUP BY.

bzr version-info
revision-id: <email address hidden>
date: 2012-03-28 13:58:14 +0300
build-date: 2012-03-28 19:36:15 +0400
revno: 3481

Notes:
Could not move ON condition under WHERE, the problem goes away.
In the test data t1 contains one row, could not add more – the problem goes away.
Reproducible with the provided test case on MyISAM or Aria tables, but not InnoDB.
Reproducible with standard optimizer_switch as well as with all OFFs (except for in_to_exists or materialization, as one of them has to be ON to get the query executed).
Reproducible on MariaDB 5.3.5, MariaDB 5.5 revno 3353.
Not reproducible on MariaDB 5.2 revno 3126.
Not reproducible on MySQL 5.1.60, MySQL 5.5 revno 3737, MySQL trunk revno 3706.

EXPLAIN with minimal optimizer switch – all OFFs except for in_to_exists (wrong result):

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2_1 index c c 9 NULL 3 100.00 Using where; Using index
1 PRIMARY t2_2 ref c c 9 test.t2_1.c,test.t2_1.b 2 100.00Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t2_1`.`b` AS `b` from `test`.`t1` join `test`.`t2` `t2_1` join `test`.`t2` `t2_2` where ((`test`.`t2_2`.`c` = `test`.`t2_1`.`c`) and (`test`.`t2_2`.`b` = `test`.`t2_1`.`b`) and (((select count(0) from `test`.`t2`) is not null) or (`test`.`t2_1`.`c` = 'x'))) group by `test`.`t2_1`.`b`

Minimal optimizer_switch: in_to_exists=on
Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

SET optimizer_switch = 'in_to_exists=on';

CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('x');

CREATE TABLE t2 ( b INT, c VARCHAR(1), KEY (c, b) ) ENGINE=MyISAM;
INSERT INTO t2 VALUES
(4, 'd'),(8, 'g'),(3, 'x'),(3, 'f'),
(0, 'p'),(3, 'j'),(8, 'c');

SELECT t2_1.b
FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2
ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c )
WHERE
( SELECT COUNT FROM t2 ) IS NOT NULL
OR a = t2_1.c
GROUP BY t2_1.b;

1. End of test case

1. Expected result:
2. b
3. –
4. 0
5. 3
6. 4
7. 8

1. Actual result:
2. b
3. –
4. 8
5. 4
6. 3
7. 8
8. 3
9. 0
10. 3