SELECT MIN(a), b
FROM t1
WHERE a > ( SELECT a FROM t2 WHERE a = 0 )
GROUP BY b;

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       PRIMARY t1      range   NULL    b       10      NULL    10      Using where; Using index for group-by
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       Using where

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

CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
CREATE TABLE t2 (c int) ;
INSERT INTO t2 VALUES (0),(1);
SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;

MIN(a)  b
1       0

SELECT MIN(a), b
FROM t1
WHERE a > 0
GROUP BY b;

MIN(a)  b
1       0
9       99

bzr version-info:

date: 2011-08-11 22:34:41 -0700
build-date: 2011-08-12 12:20:36 +0300
revno: 3151
branch-nick: maria-5.3