Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Cannot Reproduce
-
None
-
None
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://bugs.launchpad.net/maria/+bug/985828 . But still I feel something is strange.
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_section_uid` bigint(20) unsigned DEFAULT NULL,
|
`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_state` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
|
PRIMARY KEY (`uid`,`order_id`),
|
KEY `quantity` (`quantity`),
|
KEY `section_uid` (`section_uid`),
|
KEY `mirror_section_uid` (`mirror_section_uid`),
|
KEY `mirror_node_uid` (`mirror_node_uid`),
|
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_state`),
|
KEY `resource_node_uid` (`resource_uid`,`node_uid`),
|
KEY `resource_section_node_uid` (`resource_uid`,`section_uid`,`node_uid`,`simulation_state`),
|
KEY `date` (`date`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
|
INSERT INTO `stock` VALUES (2080,0,2085,NULL,NULL,NULL,NULL,NULL,NULL,2100,-43200,0,'2012-09-12 08:54:17','2012-09-12 20:54:17',NULL,'Leave Request Period','confirmed');
|
INSERT INTO `stock` VALUES (2082,0,2084,NULL,NULL,NULL,NULL,NULL,NULL,2100,43200,0,'2012-09-12 08:54:17','2012-09-12 20:54:17',NULL,'Group Presence Period','confirmed');
|
INSERT INTO `stock` VALUES (2082,1,2084,NULL,NULL,NULL,NULL,NULL,NULL,2100,43200,0,'2012-09-13 08:54:17','2012-09-13 20:54:17',NULL,'Group Presence Period','confirmed');
|
INSERT INTO `stock` VALUES (2082,2,2084,NULL,NULL,NULL,NULL,NULL,NULL,2100,43200,0,'2012-09-14 08:54:17','2012-09-14 20:54:17',NULL,'Group Presence Period','confirmed');
|
INSERT INTO `stock` VALUES (2086,0,2088,NULL,NULL,NULL,NULL,NULL,NULL,2100,43200,0,'2012-09-12 08:54:17','2012-09-12 20:54:17',NULL,'Group Presence Period','confirmed');
|
INSERT INTO `stock` VALUES (2086,1,2088,NULL,NULL,NULL,NULL,NULL,NULL,2100,43200,0,'2012-09-13 08:54:17','2012-09-13 20:54:17',NULL,'Group Presence Period','confirmed');
|
INSERT INTO `stock` VALUES (2086,2,2088,NULL,NULL,NULL,NULL,NULL,NULL,2100,43200,0,'2012-09-14 08:54:17','2012-09-14 20:54:17',NULL,'Group Presence Period','confirmed');
|
|
SET @result := 0,
|
@current_start_date := '2012-09-12 08:54:17',
|
@current_stop_date := '2012-09-12 08:54:17',
|
@countable := -1;
|
|
SELECT
|
@result :=
|
IF(@countable <= 0,
|
@result,
|
@result + TIME_TO_SEC(TIMEDIFF(@current_stop_date, @current_start_date))) AS result,
|
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_mirror_date,
|
@next_countable :=
|
IF(@date >= @current_stop_date,
|
quantity,
|
IF((@mirror_date >= @current_stop_date) AND (quantity * @countable < 0),
|
quantity,
|
@countable
|
)) AS next_countable,
|
@next_start_date :=
|
IF(@date >= @current_stop_date,
|
@date,
|
IF(quantity * @countable < 0,
|
IF(@countable > 0,
|
@mirror_date,
|
@current_stop_date),
|
@current_start_date)) AS next_start_date,
|
@next_stop_date :=
|
IF((@date >= @current_stop_date) OR (@mirror_date >= @current_stop_date),
|
@mirror_date,
|
@current_stop_date) AS next_stop_date,
|
@result :=
|
IF((@date < @current_start_date) OR (@countable <= 0),
|
@result,
|
IF(@date >= @current_stop_date,
|
@result + TIME_TO_SEC(TIMEDIFF(@current_stop_date, @current_start_date)),
|
@result + TIME_TO_SEC(TIMEDIFF(@date, @current_start_date)))) AS result,
|
@countable := @next_countable AS countable,
|
@current_start_date := @next_start_date AS current_start_date,
|
@current_stop_date := @next_stop_date AS current_stop_date
|
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 (
|
'delivered'
|
, '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
|
Attachments
Issue Links
- duplicates
-
MDEV-536 LP:1050806 - different result for a query using subquery between 5.5.25 and 5.5.27
- Closed
- links to