|
The following query (initially submitted as MDEV-765):
SELECT MIN(a), b
|
FROM t1
|
WHERE a > ( SELECT a FROM t2 WHERE a = 0 )
|
GROUP BY b;
|
does not return as many rows as if the subquery is replaced with a constant.
explain:
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
|
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=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
|
test case:
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;
|
returns:
SELECT MIN(a), b
|
FROM t1
|
WHERE a > 0
|
GROUP BY b;
|
returns:
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
|
Repeatable on maria-5.3. Not repeatable on maria-5.2. Not repeatable with other subquery operators.
To repeat on 5.5 one needs that the subquery is expensive, that is, it must examine >100 rows.
|