Bug #20954 avg(keyval) retuns 0.38 but max(keyval) returns an empty set
Submitted: 10 Jul 2006 22:57 Modified: 9 Aug 2006 20:00
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1, 5.0, 5.1 OS:
Assigned to: Timour Katchaounov CPU Architecture:Any

[10 Jul 2006 22:57] Konstantin Osipov
Description:
Looks like the server chooses wrong access method when evaluating max() under certain circumstances.

This was reported on internals@ MySQL list.

How to repeat:
DROP TABLE IF EXISTS nodes;
CREATE TABLE nodes (
  id int(11) NOT NULL,
  keyval float default NULL,
  child_id int(11) default NULL,
  child_accum double default NULL,
  UNIQUE KEY keyval (keyval),
  UNIQUE KEY child_id (child_id),
  UNIQUE KEY id_2 (id,keyval),
  KEY id (id)
);

INSERT INTO `nodes` VALUES (3,0.3762,NULL,NULL),(3,0.3845,NULL,NULL),
(11,0.7941,NULL,NULL),(2,0.6158,NULL,NULL);

select avg(keyval) from nodes where keyval <= 0.6158;
select max(keyval) from nodes where keyval <= 0.6158;

The result:

mysql> DROP TABLE IF EXISTS nodes;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE nodes (
    ->   id int(11) NOT NULL,
    ->   keyval float default NULL,
    ->   child_id int(11) default NULL,
    ->   child_accum double default NULL,
    ->   UNIQUE KEY keyval (keyval),
    ->   UNIQUE KEY child_id (child_id),
    ->   UNIQUE KEY id_2 (id,keyval),
    ->   KEY id (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> INSERT INTO `nodes` VALUES (3,0.3762,NULL,NULL),(3,0.3845,NULL,NULL),
    -> (11,0.7941,NULL,NULL),(2,0.6158,NULL,NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> select avg(keyval) from nodes where keyval <= 0.6158;
+------------------+
| avg(keyval)      |
+------------------+
| 0.38034999370575 | 
+------------------+
1 row in set (0.00 sec)

mysql> select max(keyval) from nodes where keyval <= 0.6158;
Empty set (0.00 sec)

mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.0.24-valgrind-max-debug | 
+---------------------------+
1 row in set (0.00 sec)

Suggested fix:
The explain suggests that the bug is in the optimizer:

mysql> explain select max(keyval) from nodes where keyval <= 0.6158;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away | 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

mysql> explain select avg(keyval) from nodes where keyval <= 0.6158;
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
|  1 | SIMPLE      | nodes | index | keyval        | keyval | 5       | NULL |    4 | Using where; Using index | 
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
[11 Jul 2006 10:09] Hartmut Holzgraefe
works fine in 4.0, broken in 4.1 and above
[24 Jul 2006 21:26] Bugs System
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/9521
[25 Jul 2006 19:32] Bugs System
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/9548
[2 Aug 2006 18:53] Evgeny Potemkin
The problem was in that opt_sum_query() replaced MIN/MAX functions
with the corresponding constant found in a key, but due to imprecise
representation of float numbers, when evaluating the where clause,
this comparison failed.

Fixed in 4.1.22, 5.0.25, 5.1.12
[9 Aug 2006 20:00] Paul DuBois
Noted in 4.1.22, 5.0.25, 5.1.12 changelogs.