MySQL Bugs: #20792: Incorrect results from aggregate subquery

Bug #20792	Incorrect results from aggregate subquery
Submitted:	30 Jun 2006 13:20	Modified:	9 Aug 2006 19:53
Reporter:	Jack Christensen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.22/4.1BK/5.0BK/5.1BK	OS:	Linux (Linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Under certain circumstances subqueries are inaccurate, it appears that the problem
can be caused by a string not being automicatally cast to a number. However, it appears that this error also only occurs after a row is rejected by the where condition. All following rows will then be rejected.

How to repeat:
Sample table setup:

DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `a` VALUES (1),(2);

DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `c_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `b` VALUES (1,1);

DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `c` VALUES (1);

DROP TABLE IF EXISTS `d`;
CREATE TABLE `d` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `a_id` int(10) unsigned NOT NULL default '0',
  `b_id` int(10) unsigned default NULL,
  `num_to_sum` float NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `d` VALUES (1,2,1,1);

mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->   FROM d
    ->   INNER JOIN b ON d.b_id=b.id
    ->   INNER JOIN c ON b.c_id=c.id
    ->   WHERE c.id='1' AND a.id=d.a_id) > 0;
Empty set (0.00 sec)

mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->   FROM d
    ->   INNER JOIN b ON d.b_id=b.id
    ->   INNER JOIN c ON b.c_id=c.id
    ->   WHERE c.id=1 AND a.id=d.a_id) > 0;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

Only difference is c.id='1' or c.id=1. Both should return one row.

Also note that if a rejected row does not come first, the test succeeds even with the quoted number:

mysql> UPDATE a
    -> SET id=3
    -> WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->   FROM d
    ->   INNER JOIN b ON d.b_id=b.id
    ->   INNER JOIN c ON b.c_id=c.id
    ->   WHERE c.id='1' AND a.id=d.a_id) > 0;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

Thank you for the bug report.

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

mysql> 
mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->  FROM d
    ->  INNER JOIN b ON d.b_id=b.id
    ->  INNER JOIN c ON b.c_id=c.id
    ->  WHERE c.id='1' AND a.id=d.a_id) > 0;
Empty set (0.00 sec)

mysql> 
mysql> SELECT a.*
    ->  FROM a
    ->  WHERE (SELECT (SUM(d.num_to_sum))
    ->   FROM d
    ->   INNER JOIN b ON d.b_id=b.id
    ->  INNER JOIN c ON b.c_id=c.id
    ->  WHERE c.id=1 AND a.id=d.a_id) > 0;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> 
mysql> UPDATE a
    -> SET id=3
    -> WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> 
mysql>  SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    -> FROM d
    ->  INNER JOIN b ON d.b_id=b.id
    ->  INNER JOIN c ON b.c_id=c.id
    -> WHERE c.id='1' AND a.id=d.a_id) > 0;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.01 sec)

mysql> 

mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->  FROM d
    ->  INNER JOIN b ON d.b_id=b.id
    ->  INNER JOIN c ON b.c_id=c.id
    ->  WHERE c.id='1' AND a.id=d.a_id) > 0;
Empty set (0.00 sec)

mysql> alter table a engine=MyISAM;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table b engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table c engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table d engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->  FROM d
    ->  INNER JOIN b ON d.b_id=b.id
    ->  INNER JOIN c ON b.c_id=c.id
    ->  WHERE c.id='1' AND a.id=d.a_id) > 0;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.01 sec)

mysql> 

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

mysql> 
mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->  FROM d
    ->  INNER JOIN b ON d.b_id=b.id
    ->  INNER JOIN c ON b.c_id=c.id
    ->  WHERE c.id='1' AND a.id=d.a_id) > 0;
Empty set (0.01 sec)

mysql> 
mysql> alter table a engine=MyISAM;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table b engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table c engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table d engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->  FROM d
    ->  INNER JOIN b ON d.b_id=b.id
    ->  INNER JOIN c ON b.c_id=c.id
    ->  WHERE c.id='1' AND a.id=d.a_id) > 0;
+----+
| id |
+----+
|  2 | 
+----+
1 row in set (0.00 sec)

mysql> 
------------------------------------------------------------
mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->  FROM d
    ->  INNER JOIN b ON d.b_id=b.id
    ->  INNER JOIN c ON b.c_id=c.id
    ->  WHERE c.id='1' AND a.id=d.a_id) > 0;
Empty set (0.02 sec)

mysql> 
mysql> alter table a engine=MyISAM;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table b engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table c engine=MyISAM;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table d engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT a.*
    -> FROM a
    -> WHERE (SELECT (SUM(d.num_to_sum))
    ->  FROM d
    ->  INNER JOIN b ON d.b_id=b.id
    ->  INNER JOIN c ON b.c_id=c.id
    ->  WHERE c.id='1' AND a.id=d.a_id) > 0;
+----+
| id |
+----+
|  2 | 
+----+
1 row in set (0.01 sec)

mysql>

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/9365

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/9531

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/9608

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

Noted in 4.1.22, 5.0.25, 5.1.12 changelogs.