different result for a query using subquery between 5.5.25 and 5.5.27
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
New
|
Undecided
|
Unassigned |
Bug Description
The purpose of the query below is :
Calculate the exact time available between 2 dates.
First, sort all stock rows by ascending start date.
Then, walk through them, and increase or not the result, depending on the current and previous rows.
If a row has a negative value, the result is not increased.
If a row has a positive value, the result is increased, if:
- no other intersecting positive interval had already been added,
- it does not intersect with a negative interval.
The result of this query with MariaDB 5.5.25 is :
+------
| result | total_quantity | from_date | to_date |
+------
| 0 | 0 | 2012-09-12 08:54:17 | 2012-09-12 20:54:17 |
+------
but the result with MariaDB 5.5.27 is :
+------
| result | total_quantity | from_date | to_date |
+------
| 43200 | 43200 | 2012-09-12 08:54:17 | 2012-09-12 20:54:17 |
+------
Indeed, it uses ORDER BY in the inner query, thus the result is anyway unexpectable, like we already discussed on https:/
1) if we remove 'LIMIT 1' from the query
The result with 5.5.25 is :
+------
| result | total_quantity | from_date | to_date |
+------
| 0 | 0 | 2012-09-12 08:54:17 | 2012-09-12 20:54:17 |
| 0 | 0 | 2012-09-12 08:54:17 | 2012-09-12 20:54:17 |
+------
but the result with 5.5.27 is :
+------
| result | total_quantity | from_date | to_date |
+------
| 43200 | 43200 | 2012-09-12 08:54:17 | 2012-09-12 20:54:17 |
| 86400 | 86400 | 2012-09-12 08:54:17 | 2012-09-12 20:54:17 |
| 129600 | 129600 | 2012-09-12 08:54:17 | 2012-09-12 20:54:17 |
+------
even though the result of subquery is only TWO rows !
2) if we remove 'LIMIT 1' from the query and remove INSERT lines whose node_uid is 2088, that should never be matched in the subquery :
the result with 5.5.27 is :
+------
| result | total_quantity | from_date | to_date |
+------
| 43200 | 43200 | 2012-09-12 08:54:17 | 2012-09-12 20:54:17 |
| 86400 | 86400 | 2012-09-12 08:54:17 | 2012-09-12 20:54:17 |
+------
And disabling derived_merge optimiser has not impact for these tests.
Even though the query is not well written, I wonder if 'outer query returns 3 rows where subquery returns 2 rows' can happen or not.
==== test.sql ====
DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock` (
`uid` bigint(20) unsigned NOT NULL,
`order_id` tinyint(3) unsigned NOT NULL,
`node_uid` bigint(20) unsigned DEFAULT NULL,
`section_uid` bigint(20) unsigned DEFAULT NULL,
`payment_uid` bigint(20) unsigned DEFAULT NULL,
`function_uid` bigint(20) unsigned DEFAULT NULL,
`project_uid` bigint(20) unsigned DEFAULT NULL,
`mirror_
`mirror_node_uid` bigint(20) unsigned DEFAULT NULL,
`resource_uid` bigint(20) unsigned DEFAULT NULL,
`quantity` double DEFAULT NULL,
`is_cancellation` tinyint(1) DEFAULT NULL,
`date` datetime DEFAULT NULL,
`mirror_date` datetime DEFAULT NULL,
`total_price` double DEFAULT NULL,
`portal_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`simulation_
PRIMARY KEY (`uid`,`order_id`),
KEY `quantity` (`quantity`),
KEY `section_uid` (`section_uid`),
KEY `mirror_
KEY `mirror_node_uid` (`mirror_
KEY `node_uid` (`node_uid`),
KEY `payment_uid` (`payment_uid`),
KEY `function_uid` (`function_uid`),
KEY `project_uid` (`project_uid`),
KEY `resource_uid` (`resource_uid`),
KEY `simulation_state` (`simulation_
KEY `resource_node_uid` (`resource_
KEY `resource_
KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=
INSERT INTO `stock` VALUES (2080,0,
INSERT INTO `stock` VALUES (2082,0,
INSERT INTO `stock` VALUES (2082,1,
INSERT INTO `stock` VALUES (2082,2,
INSERT INTO `stock` VALUES (2086,0,
INSERT INTO `stock` VALUES (2086,1,
INSERT INTO `stock` VALUES (2086,2,
SET @result := 0,
@current_
@current_
@countable := -1;
SELECT
@result :=
IF(@countable <= 0,
@result,
@result + TIME_TO_
CASE WHEN @result < 0 THEN
0
ELSE
0 + @result
END AS total_quantity,
'2012-09-12 08:54:17' AS from_date,
'2012-09-12 20:54:17' AS to_date
FROM (
SELECT
@date := GREATEST(date, '2012-09-12 08:54:17') AS current_c_date,
@mirror_date := LEAST('2012-09-12 20:54:17', mirror_date) AS current_
@next_countable :=
IF(@date >= @current_stop_date,
quantity,
quantity,
)) AS next_countable,
@next_start_date :=
IF(@date >= @current_stop_date,
@date,
IF(quantity * @countable < 0,
@next_stop_date :=
IF((@date >= @current_stop_date) OR (@mirror_date >= @current_
@result :=
IF((@date < @current_
@result,
IF(@date >= @current_stop_date,
@result + TIME_TO_
@result + TIME_TO_
@countable := @next_countable AS countable,
@current_
@current_
FROM
stock
WHERE
(date < '2012-09-12 20:54:17')
AND
(mirror_date >= '2012-09-12 08:54:17')
AND
node_uid in (2085, 2084)
AND
simulation_state in (
, 'started'
, 'stopped'
, 'invoiced'
, 'confirmed'
, 'getting_ready'
, 'ready'
)
AND
portal_type in (
'Group Presence Period'
, 'Leave Request Period'
, 'Presence Request Period'
)
ORDER BY date ASC, mirror_date ASC) AS calculated_result LIMIT 1
Hi,
Indeed, there is an extra row in the result set.
Here is a simplified test case:
DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock` (
`node_uid` bigint(20) unsigned DEFAULT NULL,
`date` datetime DEFAULT NULL,
`mirror_date` datetime DEFAULT NULL,
KEY `date` (`date`)
) ENGINE=MyISAM;
INSERT INTO `stock` VALUES (2085,'2012-01-01 00:00:00' ,'2013- 01-01 00:00:00'); ,'2013- 01-01 00:00:00'); ,'2013- 01-01 00:00:00');
INSERT INTO `stock` VALUES (2084,'2012-02-01 00:00:00'
INSERT INTO `stock` VALUES (2088,'2012-03-01 00:00:00'
SELECT * FROM (
SELECT node_uid, date, mirror_date, @result := 0 AS result
FROM stock
WHERE date < '2012-12-12 12:12:12'
AND node_uid in (2085, 2084)
ORDER BY mirror_date ASC
) AS calculated_result;
# Expected result:
# node_uid date mirror_date result
# 2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0
# 2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0
# Actual result:
# node_uid date mirror_date result
# 2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0
# 2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0
# 2088 2012-03-01 00:00:00 2013-01-01 00:00:00 0
The problem is also reproducible on upstream 5.5.27. Have you already filed it at bugs.mysql.com, or are you planning to do so?
(If not, I can do it, I just don't want to produce duplicates).