Not the answer you need?
Register and ask your own question!

InnoDB returning NULL on SUM(column)

wagnerbianchiwagnerbianchi Remote DBACurrent User Role Patron
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!!

Comments

  • wagnerbianchiwagnerbianchi Remote DBA Current User Role Patron
    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!!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.