different result for a query using subquery between 5.5.25 and 5.5.27

Bug #1050806 reported by Kazuhiko on 2012-09-14
10
This bug affects 2 people
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://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) wrote :

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) wrote :
tags: added: upstream wrong-result
Changed in maria:
milestone: none → 5.5
Kazuhiko (kazuhiko) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.