[MDEV-12373] WHERE broke order in 10.2 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.4, 10.2(EOL)
Fix Version/s: N/A, 10.2.6
Component/s: Optimizer
Labels:
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

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2017-04-24 21:06 - edited

Thanks for the report and test case.

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'

AUTO_INCREMENT=1047

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);

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

) a, (SELECT @row := 0) r) t

WHERE row <> order_number

DROP TABLE sales_documents;

Expected result
row order_number
15 51

Actual result
row order_number
16 1
18 2
20 3
22 4
24 5
26 6
28 7
30 8
32 9
34 10
36 11
38 12
40 51

The change was introduced in 10.2 by this commit:

commit 3b40f78eded4e529bacf09c9c28d4749b9b66812

Author: Igor Babaev

Date:   Sun Sep 4 20:11:58 2016 -0700

    Fixed a flaw in the implementation of condition push-down

    for materialized views and derived tables: there were no

    push-down if the view was defined as union of selects

    without aggregation. Added test cases with such unions.

Elena Stepanova added a comment - 2017-04-24 21:06 - edited Thanks for the report and test case. 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' AUTO_INCREMENT=1047 ; 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); 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 ) a, ( SELECT @row := 0) r) t WHERE row <> order_number ; DROP TABLE sales_documents; Expected result row order_number 15 51 Actual result row order_number 16 1 18 2 20 3 22 4 24 5 26 6 28 7 30 8 32 9 34 10 36 11 38 12 40 51 The change was introduced in 10.2 by this commit: commit 3b40f78eded4e529bacf09c9c28d4749b9b66812 Author: Igor Babaev Date: Sun Sep 4 20:11:58 2016 -0700 Fixed a flaw in the implementation of condition push-down for materialized views and derived tables: there were no push-down if the view was defined as union of selects without aggregation. Added test cases with such unions.

Sergei Golubchik added a comment - 2017-04-25 13:35

Let's start from the fact that the "expected result"

row	order_number

15	51

is incorrect. If you remove the WHERE clause, you will see that there is no such row in the subquery result, there are only 14 rows, and the correct result is

row	order_number

14	51

This is also the result you'll get in 10.1.

Now, recent 10.2. The result of the query (from the last test case) is, strictly speaking, undefined. The query assigns user variables and uses these values in the same query. The manual says:

It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined.

And this is exactly the issue here. @row is incremented every time optimizer evaluates the row of the subquery. But when optimizer will do it, is undefined. With all different optimizer techniques that we have, you cannot know when @row will be incremented.

You need to use an SQL standard compliant solution that will always return stable and predictable result. For example,

SELECT * FROM (

  SELECT ROW_NUMBER() OVER (ORDER BY order_number) 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) a) t1

  WHERE row <> order_number;

This returns the correct result of

row	order_number

14	51

Sergei Golubchik added a comment - 2017-04-25 13:35 Let's start from the fact that the "expected result" row order_number 15 51 is incorrect. If you remove the WHERE clause, you will see that there is no such row in the subquery result, there are only 14 rows, and the correct result is row order_number 14 51 This is also the result you'll get in 10.1. Now, recent 10.2. The result of the query (from the last test case) is, strictly speaking, undefined. The query assigns user variables and uses these values in the same query. The manual says : It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined. And this is exactly the issue here. @row is incremented every time optimizer evaluates the row of the subquery. But when optimizer will do it, is undefined. With all different optimizer techniques that we have, you cannot know when @row will be incremented. You need to use an SQL standard compliant solution that will always return stable and predictable result. For example, SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY order_number) 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) a) t1 WHERE row <> order_number; This returns the correct result of row order_number 14 51

Igor Babaev (Inactive) added a comment - 2017-04-25 17:18

I agree that this is not a bug.
Anyway I decided to disallow condition pushdown into derived tables with side effects.

Igor Babaev (Inactive) added a comment - 2017-04-25 17:18 I agree that this is not a bug. Anyway I decided to disallow condition pushdown into derived tables with side effects.

Igor Babaev (Inactive) added a comment - 2017-04-25 17:22

The patch that guarantees the same behaviour as in 10.1 for derived tables with side effects was pushed into the 10.2 tree. The condition pushdown introduced in 10.2 was disallowed for such tables.

Igor Babaev (Inactive) added a comment - 2017-04-25 17:22 The patch that guarantees the same behaviour as in 10.1 for derived tables with side effects was pushed into the 10.2 tree. The condition pushdown introduced in 10.2 was disallowed for such tables.

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Dmitry

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-03-27 15:14

Updated:: 2017-04-25 17:22

Resolved:: 2017-04-25 17:22

Git Integration

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

MariaDB Server