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

WHERE broke order in 10.2

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.4, 10.2(EOL)
    • N/A, 10.2.6
    • Optimizer
    • None

    Description

      Follow query work differently in 10.1 and 10.2.4:

                  SELECT * FROM 
                  (SELECT @row := @row + 1 as row, a.* from (
                      SELECT t.order_number
                      FROM sales_documents t
                      WHERE 
                          t.type = 2 AND 
                          `t`.`date` >= '2017-01-01' AND
                          `t`.`date` <= '2017-12-31' AND
                          `t`.`order_number` is not NULL AND
                          t.generated = 1 
                      GROUP BY t.order_number
                      ORDER BY order_number ASC
                  ) a, (SELECT @row := 0) r) t
                  WHERE row <> order_number
      

      What it does: This query should fetch natural numbers that omitted in order_number. This query should help find "empty space" in order_number list. Order numbers should be row of natural numbers from 1 to N and results set if we sort data by order_number should be list or natural numbers from 1 to MAX(order_number).

      So, in 10.1 it works correctly and fetch "empty space" but in 10.2 after adding WHERE row <> order_number it broke result set.

      Schema of this table:

      CREATE TABLE `sales_documents` (
      	`id` INT(11) NOT NULL AUTO_INCREMENT,
      	`sale_id` INT(11) NULL DEFAULT NULL,
      	`type` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
      	`data` TEXT NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
      	`date` DATE NULL DEFAULT NULL,
      	`order_number` INT(11) UNSIGNED NULL DEFAULT NULL,
      	`created_at` INT(11) NULL DEFAULT NULL,
      	`updated_at` INT(11) NULL DEFAULT NULL,
      	`generated` TINYINT(1) NOT NULL DEFAULT '0',
      	`synced_at` INT(11) NOT NULL DEFAULT '0',
      	`sum` DECIMAL(13,2) NOT NULL DEFAULT '0',
      	PRIMARY KEY (`id`)
      )
      COLLATE='utf8_unicode_ci'
      ENGINE=InnoDB
      AUTO_INCREMENT=1047
      ;
      

      Data for test:

      INSERT INTO `sales_documents` (`id`, `sale_id`, `type`, `order_number`, `data`, `created_at`, `updated_at`, `date`, `generated`, `synced_at`, `sum`) VALUES
      	(555, 165, 3, 5, '{}', 1486538300, 1486722835, '2017-02-17', 0, 1486538313, 2320.00),
      	(556, 165, 2, 3, '{}', 1486538304, 1486563125, '2017-02-08', 1, 1486538302, 2320.00),
      	(557, 158, 2, 2, '{}', 1486538661, 1486538661, '2017-02-08', 0, 1486538660, 2320.00),
      	(558, 171, 1, 3, '{}', 1486539104, 1488203405, '2017-02-08', 1, 1486539102, 23230.00),
      	(559, 171, 2, 5, '{}', 1486549233, 1487146010, '2017-02-08', 1, 1486549225, 37690.00),
      	(560, 172, 1, 1, '{}', 1486658260, 1488203409, '2017-02-09', 1, 1486658256, 40312.00),
      	(561, 172, 2, 1, '{}', 1486711997, 1486711997, '2017-02-10', 1, 1486711996, 40312.00),
      	(562, 172, 3, 1, '{}', 1486712104, 1486721395, '2017-02-10', 1, 1486712101, 40312.00),
      	(563, 171, 3, 2, '{}', 1486712953, 1486720244, '2017-02-10', 1, 1486712910, 23230.00),
      	(564, 170, 1, 2, '{}', 1486715948, 1488203410, '2017-02-10', 1, 1486715930, 28873.00),
      	(565, 170, 3, 3, '{}', 1486716782, 1486717426, '2017-02-10', 1, 1486716779, 61948.00),
      	(566, 166, 3, 4, '{}', 1486720947, 1486720947, '2017-02-10', 1, 1486720945, 4640.00),
      	(567, 167, 3, 5, '{}', 1486722741, 1486722783, '2017-02-26', 0, 1486722738, 14755.00),
      	(568, 165, 1, 4, '{}', 1486722849, 1486722849, '2017-02-10', 0, 1486722846, 2320.00),
      	(569, 173, 2, 2, '{}', 1486723073, 1487071275, '2017-02-10', 1, 1486723071, 14282.00),
      	(570, 173, 1, 4, '{}', 1486723100, 1488203412, '2017-02-10', 1, 1486723099, 14282.00),
      	(571, 167, 2, 4, '{}', 1486730859, 1486730859, '2017-02-10', 1, 1486730856, 18655.00),
      	(572, 167, 1, 5, '{}', 1486730883, 1488203412, '2017-02-10', 1, 1486730877, 18655.00),
      	(573, 174, 2, 51, '{}', 1486731622, 1487060259, '2017-02-10', 1, 1486731620, 7140.00),
      	(574, 174, 3, 5, '{}', 1486993472, 1486993472, '2017-02-13', 1, 1488216147, 28020.00),
      	(575, 174, 1, 6, '{}', 1486993530, 1488203412, '2017-02-13', 1, 1486993505, 7140.00),
      	(576, 173, 3, 6, '{}', 1487071425, 1487071425, '2017-02-14', 0, 1487071422, 14282.00),
      	(577, 178, 2, 6, '{}', 1487327372, 1487327372, '2017-02-17', 1, 1487327370, 12321.00),
      	(578, 177, 2, 7, '{}', 1487327394, 1487327394, '2017-02-17', 0, 1487327391, 4270.00),
      	(579, 182, 3, 6, '{}', 1487750589, 1487751693, '2017-02-22', 1, 1487751688, 4270.00),
      	(580, 182, 2, 7, '{}', 1487750601, 1487750663, '2017-02-22', 1, 1487750598, 4270.00),
      	(581, 182, 1, 7, '{}', 1487750694, 1488203412, '2017-02-22', 1, 1487750692, 4270.00),
      	(582, 185, 3, 7, '{}', 1487774051, 1487774051, '2017-02-22', 0, 1487774043, 8913.00),
      	(583, 184, 3, 7, '{}', 1487774071, 1487774235, '2017-02-22', 0, 1487774093, 3285.00),
      	(584, 184, 2, 8, '{}', 1487774074, 1487774074, '2017-02-22', 0, 1487774073, 3285.00),
      	(585, 184, 1, 8, '{}', 1487774081, 1487774081, '2017-02-22', 0, 1487774075, 3285.00),
      	(586, 193, 2, 8, '{}', 1487955294, 1487955318, '2017-02-24', 0, 1487955311, 4270.00),
      	(587, 193, 1, 8, '{}', 1487955324, 1487955324, '2017-02-24', 0, 1487955320, 4270.00),
      	(588, 193, 3, 7, '{}', 1487955341, 1487955341, '2017-02-24', 0, 1487955325, 4270.00),
      	(589, 186, 1, 8, '{}', 1487957291, 1487957464, '2017-02-24', 0, 1487957459, 6960.00),
      	(590, 186, 2, 8, '{}', 1487957308, 1487957468, '2017-02-24', 0, 1487957465, 6960.00),
      	(591, 186, 3, 7, '{}', 1487957312, 1487957473, '2017-02-24', 0, 1487957469, 6960.00),
      	(592, 194, 1, 8, '{}', 1488193293, 1488203412, '2017-02-27', 1, 1488193280, 2320.00),
      	(593, 194, 2, 8, '{}', 1488193304, 1488193304, '2017-02-27', 1, 1488193303, 2320.00),
      	(594, 210, 1, 9, '{}', 1488198896, 1488198896, '2017-02-27', 0, 1488198885, 4270.00),
      	(595, 210, 2, 12, '{}', 1488198901, 1488198901, '2017-02-27', 1, 1488532585, 4270.00),
      	(596, 210, 3, 10, '{}', 1488198904, 1488198904, '2017-02-27', 1, 1488532565, 4270.00),
      	(597, 209, 2, 9, '{}', 1488200016, 1488450772, '2017-02-27', 1, 1488450449, 4270.00),
      	(598, 209, 1, 9, '{}', 1488200020, 1488200063, '2017-02-27', 1, 1488200017, 4271.00),
      	(599, 209, 3, 7, '{}', 1488200053, 1488200053, '2017-02-27', 0, 1488200021, 4271.00),
      	(600, 211, 2, 10, '{}', 1488216265, 1489402027, '2017-02-27', 1, 1488216264, 2320.00),
      	(601, 211, 3, 7, '{}', 1488216281, 1488216281, '2017-02-27', 1, 1488216276, 2320.00),
      	(602, 211, 1, 10, '{}', 1488216283, 1488216283, '2017-02-27', 1, 1488216282, 2320.00),
      	(603, 198, 2, 11, '{}', 1488280125, 1488280125, '2017-02-28', 0, 1488280095, 4270.00),
      	(604, 198, 1, 11, '{}', 1488280160, 1488280160, '2017-02-28', 0, 1488280126, 4270.00),
      	(605, 198, 3, 8, '{}', 1488280440, 1488280440, '2017-02-28', 0, 1488280435, 4270.00),
      	(606, 212, 1, 12, '{}', 1488286301, 1489402168, '2017-02-28', 1, 1488286295, 13825.00),
      	(607, 212, 3, 8, '{}', 1488289644, 1488289690, '2017-02-28', 1, 1488289642, 25295.00),
      	(608, 212, 2, 13, '{}', 1488290350, 1488290431, '2017-02-28', 1, 1488290347, 13133.75),
      	(609, 213, 1, 11, '{}', 1488529470, 1488529470, '2017-03-03', 1, 1488529461, 5660.00),
      	(610, 213, 2, 11, '{}', 1488529484, 1488529484, '2017-03-03', 1, 1488529479, 5660.00),
      	(611, 213, 3, 9, '{}', 1488529493, 1488529493, '2017-03-03', 1, 1488529489, 5660.00),
      	(612, 197, 2, 13, '{}', 1489400715, 1489400715, '2017-03-13', 0, 1489398959, 4270.00),
      	(613, 219, 3, 11, '{}', 1490084337, 1490181958, '2017-03-21', 1, 1490084334, 73526.00),
      	(614, 216, 3, 11, '{}', 1490085757, 1490086717, '2017-03-21', 0, 1490085755, 5377.00);
      

      Attachments

        Activity

          People

            igor Igor Babaev
            korvinko Dmitry
            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.