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 |
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
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 | +----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+