Bug #75266 Setting sum of decimal field on user defined variable is not working as expected
Submitted: 18 Dec 2014 16:36 Modified: 18 Dec 2014 18:17
Reporter: Mehmet Melih Karcı Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.19 OS:Any
Assigned to: CPU Architecture:Any

[18 Dec 2014 16:36] Mehmet Melih Karcı
Description:
Attached sql script is not returning same results for 5.6.19 and 5.5.35.

It works as I expected on 5.5.35

How to repeat:
DROP TABLE IF EXISTS company_sales;

CREATE TABLE `company_sales` (
  `id_company_sales` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `date` date NOT NULL,
  `points` decimal(10,2) unsigned NOT NULL,
  `cancelled` tinyint(1) DEFAULT '0',
  `cnd1` int(11) DEFAULT NULL,
  `cnd2` varchar(128) DEFAULT '',
  `cnd3` mediumint(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_company_sales`),
  UNIQUE KEY `cnd1_UNIQUE` (`cnd1`),
  KEY `index3` (`company_id`,`date`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=56919 DEFAULT CHARSET=latin1;

INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (48712,6978,64390,'0000-00-00',76.96,1,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (49093,6978,0,'2014-11-01',256.03,0,NULL,'starting',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (47286,6978,63122,'2014-11-23',84.44,1,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (48956,6978,64811,'2014-11-28',76.96,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (48962,6978,64817,'2014-11-28',84.44,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (49042,6978,64900,'2014-11-28',57.52,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (49048,6978,64905,'2014-11-28',28.76,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (49090,6978,64945,'2014-11-28',8.35,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (53416,6978,0,'2014-12-01',106.80,0,NULL,'starting',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (53415,6978,69189,'2014-12-09',26.10,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (53459,6978,69231,'2014-12-09',16.55,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (53959,6978,69701,'2014-12-10',64.15,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (57880,4605,73805,'2014-12-18',92.13,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (57879,6212,73804,'2014-12-18',21.12,0,NULL,'',0);
INSERT INTO `company_sales` (`id_company_sales`,`company_id`,`order_id`,`date`,`points`,`cancelled`,`cnd1`,`cnd2`,`cnd3`) VALUES (57878,7395,73626,'0000-00-00',39.97,1,NULL,'',0);

SELECT 
    NULL AS id_company_sales,
    NULL AS date,
    NULL AS points,
    NULL AS total
FROM DUAL WHERE
    (@total:=0) 
UNION SELECT 
    id_company_sales,
    (date),
    points,
    @total:=@total + points AS total
FROM
    company_sales cs
WHERE
        cs.company_id = 6978
        AND cs.date >= '2014-11-21'
        AND cs.date <= '2014-12-21'
        AND cs.cancelled = 0
        AND cs.order_id != 0
        AND @total < 250
ORDER BY id_company_sales DESC
LIMIT 1;
[18 Dec 2014 16:47] Peter Laursen
I can reproduce this, But you use "LIMIT 1;" and don't specify an "ORDER BY" in the query. Differerent optimizer code and optimizer settings could result in this.

So I am no sure it is a bug!

-- Peter
-- not a MySQL/Oracle person.
[18 Dec 2014 16:50] Mehmet Melih Karcı
It's ok to remove ORDER BY and LIMIT.
Result set is different anyway.
[18 Dec 2014 18:16] Miguel Solorzano
Thank you for the bug report. Only 5.6 affected not repeatable on 5.5 and 5.7:

    ->         AND @total < 250
    -> ORDER BY id_company_sales DESC
    -> LIMIT 1;
+------------------+------------+--------+--------+
| id_company_sales | date       | points | total  |
+------------------+------------+--------+--------+
|            49090 | 2014-11-28 |   8.35 | 256.03 |
+------------------+------------+--------+--------+
1 row in set (0.00 sec)

mysql 5.5 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.5.42                         |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.5.42                         |
| version_comment         | Source distribution 2014.12.02 |
| version_compile_machine | AMD64                          |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

*************************************************************

    ->         AND @total < 250
    -> ORDER BY id_company_sales DESC
    -> LIMIT 1;
+------------------+------------+--------+--------+
| id_company_sales | date       | points | total  |
+------------------+------------+--------+--------+
|            53415 | 2014-12-09 |  26.10 | 282.13 |
+------------------+------------+--------+--------+
1 row in set (0.00 sec)

mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.6.23                         |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.6.23                         |
| version_comment         | Source distribution 2014.12.02 |
| version_compile_machine | x86_64                         |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

****************************************************************
    ->         AND cs.order_id != 0
    ->         AND @total < 250
    -> ORDER BY id_company_sales DESC
    -> LIMIT 1;
+------------------+------------+--------+--------+
| id_company_sales | date       | points | total  |
+------------------+------------+--------+--------+
|            49090 | 2014-11-28 |   8.35 | 256.03 |
+------------------+------------+--------+--------+
1 row in set (0.00 sec)

mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.7.6                          |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.7.6-m16                      |
| version_comment         | Source distribution 2014.12.02 |
| version_compile_machine | x86_64                         |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

mysql 5.7 >