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;
}
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; }