Details
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
Gliffy Diagrams
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
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
See also a comment by Patryk Pomykalski (https://mariadb.atlassian.net/browse/MDEV-536?focusedCommentId=14500&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14500):
Looks like this change caused it:
http://bazaar.launchpad.net/~mysql/mysql-server/5.5/revision/3827
I've debugged it. The reason that the query produces 3 rows instead of 2 is as follows:
- The WHERE clause matches 2 rows.
- range access can be (and is) constructed from the condition
date < '2012-12-12 12:12:12'
which is satisfied by 3 rows.
Execution proceeds as follows:
crate_sort_index() is invoked.
It uses filesort, which uses quick select and the WHERE clause to produce a sorted set of two rows.
Then "main" join execution starts. It is supposed to read what create_sort_index() and filesort() have produced. Instead it uses the quick select. Note that the WHERE clause was already applied in create_sort_index(), so it is not applied now. This is how we get extra rows ( and also we get them in the wrong order).
The problem is that quick select is not correctly stashed away at the end of create_sort_index() call.
Fix pushed into 10.0-serg (let's see what buildbot shows)
not repeatable in 5.5.30
This bug is still reproducible on current 5.3 (5.3.12+), although it might be not so important to fix it there.
Re: different result for a query using subquery between 5.5.25 and 5.5.27
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');
INSERT INTO `stock` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
INSERT INTO `stock` VALUES (2088,'2012-03-01 00:00:00','2013-01-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;
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).