InnoDB returning NULL on SUM(column)

MySQL & MariaDB Other MySQL® Questions
1

Hi Guys,

I have been having a weird case on a recently converted database from MyISAM to InnoDB. One of the converted databases has a table called workflow with a column x decimal(11,4). When I first saw the case below happening, my first action was to ALTER TABLE … ENGINE=InnoDB suspecting about some kind of problems in adjusting the column’s data type - it took a long time and didn’t fix the problem.

BTW, as I still have a MyISAM table, I can compare results. MyISAM is presenting the right SUM(x), AVG(x), COUNT(x) result while InnoDB continues presenting a NULL value.

#
#: MyISAM
#
mysql> select count(*), p,sum(x) from workflow where id = 27 group by p;
+----------+------+------------+
| count(*) | p | sum(x) |
+----------+------+------------+
| 391 | 0 | 3964.2600 |
| 84 | 1 | 1599.5300 |
| 1 | 2 | 6.6500 |
+----------+------+------------+
3 rows in set (0.00 sec)

mysql> select sum(x) from workflow where id = 27 and p = 2 group by p;
+------------+
| sum(x) |
+------------+
| 6.6500 |
+------------+
1 row in set (0.00 sec)

#
#: InnoDB
#
mysql> select count(*), p,sum(x) from workflow where id = 27 group by p;
+----------+------+------------+
| count(*) | p | sum(x) |
+----------+------+------------+
| 391 | 0 | 3964.2600 |
| 84 | 1 | 1599.5300 |
| 1 | 2 | 6.6500 |
+----------+------+------------+
3 rows in set (0.00 sec)

mysql> select sum(x) from workflow where id = 27 and p = 2 group by p;
+------------+
| sum(x) |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)

Testing some scenarios with InnoDB table, I found that using p not in (0,1) and then, forcing to return just rows that contain 2 in column p, it worked very well (as using <> as well):

mysql> select sum(x) x from workflow where id = 27 and p not in(0,1) group by p;
+--------+
| x |
+--------+
| 6.6500 |
+--------+
1 row in set (0.01 sec)

mysql> select sum(x) x from workflow where id = 27 and (p<>0 and p<>1) group by p;
+--------+
| x |
+--------+
| 6.6500 |
+--------+
1 row in set (0.01 sec)

Any comments on this case will be very welcomed, thanks guys!!

2

After checking all the query’s EXPLAIN, I saw that just one that was not stating the index to use, that was doing an intersection. Disabling the index_merge_intersection made the query to return the right value.

SET GLOBAL optimizer_switch="index_merge_intersection=off"; # solved the problem...

But the big question now is, why the intersect is not working properly in this case?

Cheers everyone!!