Bug #40037 Incorrect result from MIN/MAX when no GROUP, and independent subquery predicate
Submitted: 15 Oct 2008 9:34 Modified: 20 Oct 2011 3:46
Reporter: Timour Katchaounov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.x, 6.x OS:Any
Assigned to: CPU Architecture:Any
Tags: MAX, min, subquery

[15 Oct 2008 9:34] Timour Katchaounov
Description:
According to Sect. 11.12.1. GROUP BY (Aggregate) Functions in the MySQL
Reference manual:

* "MAX() returns NULL if there were no matching rows."
* "MIN() returns NULL if there were no matching rows."

However, if a query without a GROUP clause, selects MIN/MAX, and
* the where clause has a subquery predicate that compares a constant with
  the subquery result,
* and the outer table has an index covering the argument of MIN/MAX,
then the query returns no result instead of NULL.

How to repeat:
create table t1 (a1 int key);
create table t2 (b1 int);

insert into t1 values (5);

select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
-- Returns empty set instead of NULL

-- In addition in 6.0 only
set @@optimizer_switch='no_semijoin,no_materialization';
select min(a1) from t1 where 7 in (select b1 from t2);

Suggested fix:
The problem is related to an old shortcut optimization where MIN/MAX
functions without GROUP BY are substituted by a constant if there is
a suitable index. The relevant code path is:

JOIN::optimize()
{
  ...
  opt_sum_func() // detects that the MIN/MAX function can be computed by
                    an index, and that the WHERE clause doesn't depend on
                    any table in the FROM clause.
  zero_result_cause= "Select tables optimized away";
  tables_list= 0; // All tables resolved
  ...
  return
}

JOIN::exec()
{
  ...
  if (!tables_list && (tables || !select_lex->with_sum_func))
  {                                           // Only test of functions
    if (... conds->val_int() ...)
      ...
      	error=(int) result->send_eof();
        send_records= 0;
  }
  ...
  if (zero_result_cause)
    return_zero_rows(...)
    return;
}
[15 Oct 2008 9:36] Timour Katchaounov
When testing the fix for this bug, please verify specifically the test
case for BUG#39756, and generally verify the correct behavior with 6.0
and subquery materialization.
[20 Jun 2010 7:33] Guilhem Bichot
this is visible in the diff between
r/subquery_mat.result and r/subquery_mat_all.result (the latter is correct):
@@ -1116,18 +1116,18 @@
 min(a1)
 explain select min(a1) from t1 where 7 in (select b1 from t2);
 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1      PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
-2      SUBQUERY        t2      system  NULL    NULL    NULL    NULL    0       const row not found
+1      PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
 select min(a1) from t1 where 7 in (select b1 from t2);
 min(a1)
+NULL
 drop table t1,t2;
[16 Aug 2010 6:37] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:19] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Oct 2011 3:46] Paul DuBois
Noted in 5.6.4 changelog.

Subqueries could return incorrect results when materialization was enabled.