| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.6.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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]
MySQL Verification Team
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 >
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;