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