MySQL Bugs: #24714: Mathematical SELECT queries no longer return consistent results

Bug #24714	Mathematical SELECT queries no longer return consistent results
Submitted:	30 Nov 2006 7:30	Modified:	20 Aug 2007 15:40
Reporter:	Joe Clarke	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	4.1.22, 4.1 BK	OS:	Linux (Linux, FreeBSD)
Assigned to:		CPU Architecture:	Any

Description:
Given the following schema:

Table : workgroups
+-------------+--------------+------+-----+-------------------+-------+
| Field       | Type         | Null | Key | Default           | Extra |
+-------------+--------------+------+-----+-------------------+-------+
| name        | varchar(100) |      | PRI |                   |       |
| last_update | timestamp    | YES  |     | CURRENT_TIMESTAMP |       |
| theater     | varchar(8)   |      | MUL |                   |       |
+-------------+--------------+------+-----+-------------------+-------+

Table : rat2
+------------------------+--------------+------+-----+---------+-------+
| Field                  | Type         | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+-------+
| srnumber               | bigint(20)   |      | PRI | 0       |       |
| pe_moves               | int(11)      | YES  |     | NULL    |       |
| wrkgrp                 | varchar(100) |      | MUL |         |       |
+------------------------+--------------+------+-----+---------+-------+

Assume that there is a workgroup named "RTP-NMS" with a theater of "RTP" and in this workgroup there are 4216 rows in the rat2 table.  Of these 4216 rows, 335 of them have a pe_moves value > 0.  The percent of pe_moves > 0 compared to total rows is 7.95%.

Under MySQL 4.1.21, the following query correctly produced 7.95 for the RTP theater:

SELECT workgroups.theater AS TName,((SELECT COUNT(rat2.pe_moves) FROM rat2, workgroups WHERE rat2.pe_moves > 0 AND rat2.wrkgrp = workgroups.name AND workgroups.theater = TName) / COUNT(rat2.srnumber)) * 100 AS Escalations FROM rat2, workgroups WHERE workgroups.name = rat2.wrkgrp GROUP BY TName ORDER BY TName ASC;

+-------+-------------+
| TName | Escalations |
+-------+-------------+
| RTP   |        7.95 |
+-------+-------------+

Under MySQL 4.1.22, this same query produces the value 0.00.  However, this query will produce the correct value for the RTP theater (note: here I am explicitly selecting data for the RTP theater):

SELECT workgroups.theater AS TName,((SELECT COUNT(rat2.pe_moves) FROM rat2, workgroups WHERE rat2.pe_moves > 0 AND rat2.wrkgrp = workgroups.name AND workgroups.theater = TName) / COUNT(rat2.srnumber)) * 100 AS Escalations FROM rat2, workgroups WHERE workgroups.name = rat2.wrkgrp AND workgroups.theater = 'RTP' GROUP BY TName ORDER BY TName ASC;

So will this one (note: the COUNT() is replaced by another sub-select):

SELECT workgroups.theater AS TName,((SELECT COUNT(rat2.pe_moves) FROM rat2, workgroups WHERE rat2.pe_moves > 0 AND rat2.wrkgrp = workgroups.name AND workgroups.theater = TName) / (SELECT COUNT(rat2.srnumber) FROM rat2, workgroups WHERE rat2.wrkgrp = workgroups.name AND workgroups.theater = TName)) * 100 AS Escalations FROM rat2, workgroups WHERE workgroups.name = rat2.wrkgrp GROUP BY TName ORDER BY TName ASC;

How to repeat:
See above.

Suggested fix:
Downgrade the server to 4.1.22, or use one of the workaround queries mentioned above.

Thank you for a problem report. Please, send SHOW CREATE TABLE and SHOWS TABLE STATUS results for both tables. Send the results of EXPLAIN from 4.1.21 and 4.1.22 for both queries.

I will have to wait until tonight to get the 4.1.22 output, but here is the requested output from 4.1.21 (working):

SHOW CREATE TABLE rat2;
CREATE TABLE `rat2` (
  `srnumber` bigint(20) NOT NULL default '0',
  `pe_moves` int(11) default NULL,
  `wrkgrp` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`srnumber`),
  CONSTRAINT `rat2_ibfk_1` FOREIGN KEY (`wrkgrp`) REFERENCES `workgroups` (`name`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW CREATE TABLE workgroups;
CREATE TABLE `workgroups` (
  `name` varchar(100) NOT NULL default '',
  `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `theater` varchar(8) NOT NULL default '',
  PRIMARY KEY  (`name`),
  KEY `theater_idx` (`theater`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW TABLE STATUS;
| Name       | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment
| rat2       | InnoDB |       9 | Dynamic    | 16819 |            655 |    11026432 |            NULL |     12042240 |         0 |           NULL | 2006-11-30 02:54:27 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 21504 kB; (`wrkgrp`) REFER `rat/workgroups`(`name`) ON UPDATE CASCADE |
| workgroups | InnoDB |       9 | Dynamic    |   106 |            154 |       16384 |            NULL |        16384 |         0 |           NULL | 2006-11-30 02:54:27 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 21504 kB      |

EXPLAIN SELECT workgroups.theater AS TName,((SELECT COUNT(rat2.pe_moves) FROM rat2, workgroups WHERE rat2.pe_moves > 0 AND rat2.wrkgrp = workgroups.name AND workgroups.theater = TName) / COUNT(rat2.srnumber)) * 100 AS Escalations FROM rat2, workgroups WHERE workgroups.name = rat2.wrkgrp GROUP BY TName ORDER BY TName ASC;
+----+--------------------+------------+-------+---------------------+-------------+---------+---------------------+------+--------------------------+
| id | select_type        | table      | type  | possible_keys       | key         | key_len | ref                 | rows | Extra                    |
+----+--------------------+------------+-------+---------------------+-------------+---------+---------------------+------+--------------------------+
|  1 | PRIMARY            | workgroups | index | PRIMARY             | theater_idx |       8 | NULL                |  106 | Using index              |
|  1 | PRIMARY            | rat2       | ref   | wrkgrp_idx          | wrkgrp_idx  |     100 | rat.workgroups.name |   78 | Using index              |
|  2 | DEPENDENT SUBQUERY | workgroups | ref   | PRIMARY,theater_idx | theater_idx |       8 | func                |    5 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | rat2       | ref   | wrkgrp_idx          | wrkgrp_idx  |     100 | rat.workgroups.name |   78 | Using where              |
+----+--------------------+------------+-------+---------------------+-------------+---------+---------------------+------+--------------------------+
4 rows in set (0.00 sec)

Here is more 4.1.21 output.

EXPLAIN SELECT workgroups.theater AS TName,((SELECT COUNT(rat2.pe_moves) FROM rat2, workgroups WHERE rat2.pe_moves > 0 AND rat2.wrkgrp = workgroups.name AND workgroups.theater = TName) / (SELECT COUNT(rat2.srnumber) FROM rat2, workgroups WHERE rat2.wrkgrp = workgroups.name AND workgroups.theater = TName) * 100) AS Escalations FROM rat2, workgroups WHERE workgroups.name = rat2.wrkgrp GROUP BY TName ORDER BY TName ASC;
+----+--------------------+------------+-------+---------------------+-------------+---------+---------------------+------+--------------------------+
| id | select_type        | table      | type  | possible_keys       | key         | key_len | ref                 | rows | Extra                    |
+----+--------------------+------------+-------+---------------------+-------------+---------+---------------------+------+--------------------------+
|  1 | PRIMARY            | workgroups | index | PRIMARY             | theater_idx |       8 | NULL                |  106 | Using index              |
|  1 | PRIMARY            | rat2       | ref   | wrkgrp_idx          | wrkgrp_idx  |     100 | rat.workgroups.name |   78 | Using index              |
|  3 | DEPENDENT SUBQUERY | workgroups | ref   | PRIMARY,theater_idx | theater_idx |       8 | func                |    5 | Using where; Using index |
|  3 | DEPENDENT SUBQUERY | rat2       | ref   | wrkgrp_idx          | wrkgrp_idx  |     100 | rat.workgroups.name |   78 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | workgroups | ref   | PRIMARY,theater_idx | theater_idx |       8 | func                |    5 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | rat2       | ref   | wrkgrp_idx          | wrkgrp_idx  |     100 | rat.workgroups.name |   78 | Using where              |
+----+--------------------+------------+-------+---------------------+-------------+---------+---------------------+------+--------------------------+
6 rows in set (0.00 sec)

EXPLAIN SELECT workgroups.theater AS TName,((SELECT COUNT(rat2.pe_moves) FROM rat2, workgroups WHERE rat2.pe_moves > 0 AND rat2.wrkgrp = workgroups.name AND workgroups.theater = TName) / COUNT(rat2.srnumber)) * 100 AS Escalations FROM rat2, workgroups WHERE workgroups.name = rat2.wrkgrp AND workgroups.theater = 'RTP' GROUP BY TName ORDER BY TName ASC;
+----+--------------------+------------+------+---------------------+-------------+---------+---------------------+------+--------------------------+
| id | select_type        | table      | type | possible_keys       | key         | key_len | ref                 | rows | Extra                    |
+----+--------------------+------------+------+---------------------+-------------+---------+---------------------+------+--------------------------+
|  1 | PRIMARY            | workgroups | ref  | PRIMARY,theater_idx | theater_idx |       8 | const               |   23 | Using where; Using index |
|  1 | PRIMARY            | rat2       | ref  | wrkgrp_idx          | wrkgrp_idx  |     100 | rat.workgroups.name |   78 | Using index              |
|  2 | DEPENDENT SUBQUERY | workgroups | ref  | PRIMARY,theater_idx | theater_idx |       8 | func                |    5 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | rat2       | ref  | wrkgrp_idx          | wrkgrp_idx  |     100 | rat.workgroups.name |   78 | Using where              |
+----+--------------------+------------+------+---------------------+-------------+---------+---------------------+------+--------------------------+
4 rows in set (0.00 sec)

Full test-case below:

drop table if exists rat2;drop table if exists workgroups;
CREATE TABLE `workgroups` (
  `name` varchar(100) NOT NULL default '',
  `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `theater` varchar(8) NOT NULL default '',
  PRIMARY KEY  (`name`),
  KEY `theater_idx` (`theater`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `rat2` (
  `srnumber` bigint(20) NOT NULL  auto_increment,
  `pe_moves` int(11) default NULL,
  `wrkgrp` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`srnumber`),
  CONSTRAINT `rat2_ibfk_1` FOREIGN KEY (`wrkgrp`) REFERENCES
 `workgroups` (`name`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into workgroups select 'RTP-NMS', null, 'RTP';
insert into workgroups select 'RTP-XXX', null, 'RTPX';
insert into rat2 select null, null, 'RTP-NMS';
insert into rat2 select null, null, 'RTP-NMS' from rat2; insert into rat2 select null, null, 'RTP-NMS' from rat2;
insert into rat2 select null, null, 'RTP-NMS' from rat2; insert into rat2 select null, null, 'RTP-NMS' from rat2;
insert into rat2 select null, null, 'RTP-NMS' from rat2; insert into rat2 select null, null, 'RTP-NMS' from rat2;
insert into rat2 select null, null, 'RTP-NMS' from rat2; insert into rat2 select null, null, 'RTP-NMS' from rat2;
update rat2 set pe_moves =1 where srnumber <= 35;
insert into rat2 select null, null, 'RTP-XXX';

SELECT workgroups.theater AS TName,
 (  ( SELECT COUNT(rat2.pe_moves)
      FROM rat2, workgroups
      WHERE rat2.pe_moves > 0 AND rat2.wrkgrp = workgroups.name
        AND workgroups.theater = TName
     ) / COUNT(rat2.srnumber)
 ) * 100 AS Escalations
FROM rat2, workgroups
WHERE workgroups.name = rat2.wrkgrp
GROUP BY TName;

 drop table if exists rat2;drop table if exists workgroups;

Thank you for the report.

Verified on Linux as described using last BK sources.

Severity has been decreased, because there is workaround:

SELECT workgroups.theater AS TName,
 (  ( SELECT COUNT(rat2.pe_moves)
      FROM rat2, workgroups
      WHERE rat2.pe_moves > 0 AND rat2.wrkgrp = workgroups.name
        AND workgroups.theater = TName
     ) / COUNT(rat2.srnumber)
 ) * 100 AS Escalations
FROM rat2, workgroups
WHERE workgroups.name = rat2.wrkgrp
AND workgroups.theater IN (SELECT theater FROM workgroups GROUP BY theater)
GROUP BY TName;

Only InnoDB tables are affected.

I ran some tests. It was the clause / COUNT(rat2.srnumber) that made MySQL/InnoDB to return 0.00 though the correct result is > 0.

I do not see how this could be an InnoDB bug. I am setting this as a server bug.

The workaround does not work if there is more than one theater in the workgroups table.  In that case, the bug will still be seen.  This bug is still present in all shipping versions of MySQL.  Is there any ETA on a fix?

Running the test case (attached to this bug) with MyISAM or MEMORY tables does NOT yield the bug.  Additionally, the bug only occurs when the rat2 table is InnoDB.  The workgroups table can be anything.  If the rat2 table is anything OTHER than InnoDB, the bug does not occur.

So this does appear to be a problem with InnoDB, so I have set the category back to InnoDB.  I am also increasing the severity back to critical since the workaround is not viable.

Correction.  This bug only occurs when the workgroups table is InnoDB.  rat2 can use any other engine, but if workgroups is InnoDB, then problem occurs.

The bug is probably in MySQL query execution/optimizer, though it only manifests with InnoDB type tables.

Tested again today.

Bug is not repeatable with 5.0 and 5.1 sources, but repeatable with 4.1 and InnoDB storage engine only.

This bug is due to a serious flaw in the implementation of aggregation within correlated subqueries. The flaw was fixed in 5.0 and we are not going to fix it in 4.1.