Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-614

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

    XMLWordPrintable

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

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              kazuhiko Kazuhiko (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.