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