MySQL Bugs: #45386: Wrong query result with MIN function in field list, WHERE and GROUP BY clause

Bug #45386	Wrong query result with MIN function in field list, WHERE and GROUP BY clause
Submitted:	9 Jun 2009 3:52	Modified:	29 Sep 2009 14:44
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.81, 5.0, 5.1, 6.0 bzr	OS:	Solaris
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
mysql> create table t ( a int unsigned not null, b int not null, c date not null
, index ( a, b, c ) );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values ( 1, 2, 0 ), ( 1, 2, 0 ), ( 1, 2, '2009-02-01' ), (
1, 2, '2009-02-01' );
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select a, b, min( c ) from t where c <> 0 group by a, b;
+---+---+------------+
| a | b | min( c )   |
+---+---+------------+
| 1 | 2 | 2009-02-01 | <<<<<< Right
+---+---+------------+
1 row in set (0.00 sec)

mysql> insert into t values ( 1, 2, 0 ), ( 1, 2, 0 ), ( 1, 2, '2009-02-01' ), (
1, 2, '2009-02-01' );
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select a, b, min( c ) from t where c <> 0 group by a, b;
Empty set (0.00 sec) <<<<<< Wrong!!!

How to repeat:
See Description

Suggested fix:
Exclude column 'c' from index

Thank you for the report.

Verified as described.

the proposed fix

Attachment: diff.diff (application/octet-stream, text), 2.06 KiB.

Code review:
-------------------------------------------------------------------

The problem:
------------
The bug can be seen with gdb even with the simpler query:
SELECT a, MIN(b) FROM t WHERE b < 0 GROUP BY a;
The only reason here we get the correct result is because the server re-applies
the where clause after index lookup.

The problem is that the following condition inside method
QUICK_GROUP_MIN_MAX_SELECT::next_min_in_range()

((cur_range->flag & NEAR_MAX) && (cmp_res == -1) || (cmp_res <= 0))

Has an incorrect second term, which should be instead:
(cur_range->flag & NEAR_MAX) && (cmp_res <= 0).

The fix:
--------

The fix inverts the meaning of the above condition to the correct inverse
condition, where it states when the key is *outside* of the range. This is OK.

However the fix is incomplete, since it doesn't fix the symmetric method
that handles MAX:
QUICK_GROUP_MIN_MAX_SELECT::next_max_in_range()

Recommendations:
----------------
- extend the fix to  QUICK_GROUP_MIN_MAX_SELECT::next_max_in_range(),
- add test cases for MAX,
- add 2 extra test cases (for MIN and MAX) with 3 different groups in
  ohe table, not just 1,
- add a comment above both conditions for MIN and MAX, such as:
  /*
    The key is outside the range if
    (key == const and open interval) or (key > const)
  */
- add an overall comment to the patch.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76178

2770 Georgi Kodinov	2009-06-12
      Bug #45386: Wrong query result with MIN function in field list, 
      WHERE and GROUP BY clause
      
      Loose index scan may use range conditions on the argument of 
      the MIN/MAX aggregate functions to find the beginning/end of 
      the interval that satisfies the range conditions in a single go.
      These range conditions may have open or closed minimum/maximum 
      values. When the comparison returns 0 (equal) the code should 
      check the type of the min/max values of the current interval 
      and accept or reject the row based on whether the limit is 
      open or not.
      There was a wrong composite condition on checking this and it was
      not working in all cases.
      Fixed by simplifying the conditions and reversing the logic.
     @ mysql-test/r/group_min_max.result
        Bug #45386: test case
     @ mysql-test/t/group_min_max.test
        Bug #45386: test case
     @ sql/opt_range.cc
        Bug #45386: fix the check whether to use the value if on the
        interval boundry

Pushed into 5.0.84 (revid:gkodinov@mysql.com-20090616082753-kwe0l8uoictxhojf) (version source revid:joro@sun.com-20090612123855-qbcr0a0948hpfd7i) (merge vers: 5.0.83) (pib:6)

Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:joro@sun.com-20090612135848-p6kllijtgw8w5e55) (merge vers: 5.1.36) (pib:6)

Noted in 5.0.84, 5.1.36 changelogs.

The combination of MIN() or MAX() in the select list with WHERE and
GROUP BY clauses could lead to incorrect results.

Setting report to NDI pending push into 5.4.x.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Setting report to NDI pending push into 5.4.x.

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Setting report to NDI pending push into 5.4.x.

Pushed in mysql-trunk 5.4.5-beta

Noted in 5.4.5 changelog.

Retagged from 5.4.5 to 5.5.0.