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
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.x, 6.x
• OS: Any
• 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:[e-mail]-20100816062819-bluwgdq8q4xysmlg) (version source revid:[e-mail]-20100816062612-enatdwnv809iw3s9) (pib:20)

[13 Nov 2010 16:19] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-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.