GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

InnoDB returning NULL on SUM(column)

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • InnoDB returning NULL on SUM(column)

    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.

    Code:
    #
    #: 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):

    Code:
    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!!
    WB with #MySQL, www.wagnerbianchi.com

  • #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.

    Code:
    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!!
    Last edited by wagnerbianchi; 07-28-2014, 10:36 AM.
    WB with #MySQL, www.wagnerbianchi.com

    Comment

    Working...
    X