[MDEV-614] LP:1050806 - different result for a query using subquery between 5.5.25 and 5.5.27 Created: 2012-09-14  Updated: 2014-04-07  Resolved: 2013-02-28

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 5.5.30

Type: Bug Priority: Minor
Reporter: Kazuhiko (Inactive) Assignee: Sergei Golubchik
Resolution: Cannot Reproduce Votes: 0
Labels: Launchpad, upstream

Attachments: XML File LPexportBug1050806.xml    
Issue Links:
Duplicate
duplicates MDEV-536 LP:1050806 - different result for a q... Closed

 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



 Comments   
Comment by Elena Stepanova [ 2012-09-17 ]

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;

  1. Expected result:
  1. node_uid date mirror_date result
  2. 2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0
  3. 2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0
  1. Actual result:
  1. node_uid date mirror_date result
  2. 2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0
  3. 2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0
  4. 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).

Comment by Elena Stepanova [ 2012-09-17 ]

Re: different result for a query using subquery between 5.5.25 and 5.5.27
Also filed in JIRA as https://mariadb.atlassian.net/browse/MDEV-536

Comment by Kazuhiko (Inactive) [ 2012-09-17 ]

Re: different result for a query using subquery between 5.5.25 and 5.5.27
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

Comment by Elena Stepanova [ 2012-09-17 ]

Re: different result for a query using subquery between 5.5.25 and 5.5.27
Also filed as http://bugs.mysql.com/bug.php?id=66845

Comment by Rasmus Johansson (Inactive) [ 2012-09-17 ]

Launchpad bug id: 1050806

Comment by Elena Stepanova [ 2012-10-24 ]

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

Comment by Sergei Petrunia [ 2012-11-04 ]

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.

Comment by Sergei Petrunia [ 2012-11-04 ]

Fix pushed into 10.0-serg (let's see what buildbot shows)

Comment by Sergei Golubchik [ 2013-02-28 ]

not repeatable in 5.5.30

Comment by Elena Stepanova [ 2014-04-07 ]

This bug is still reproducible on current 5.3 (5.3.12+), although it might be not so important to fix it there.

Generated at Thu Feb 08 06:30:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.