different result for a query using subquery between 5.5.25 and 5.5.27

[Kazuhiko]

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

[Elena Stepanova (elenst)]

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;

# 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).

[Elena Stepanova (elenst)]

Also filed in JIRA as https://mariadb.atlassian.net/browse/MDEV-536

[Kazuhiko (kazuhiko)]

Hi Elena,

Thank you very much for simplify test case!
I have not filed this issue at bugs.mysql.com. It will be appreciated if you would do it.

Kazuhiko

[Elena Stepanova (elenst)]

Also filed as http://bugs.mysql.com/bug.php?id=66845