[MDEV-614] LP:1050806 - different result for a query using subquery between 5.5.25 and 5.5.27 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Cannot Reproduce
Affects Version/s: None
Fix Version/s: 5.5.30
Component/s: None
Labels:
- Launchpad
- upstream

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

  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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

LPexportBug1050806.xml
14 kB
2012-10-03 11:32

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

Bug #66845 Wrong result (extra row) on a FROM subquery with a variable and ORDER BY

LP:1050806 - different result for a query using subquery between 5.5.25 and 5.5.27

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration