[MDEV-36410] Descending Indexes with primary key column,Using intersect indexs. the filesort got wrong - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.8.1, 10.11.10, 10.11
Fix Version/s: 10.11
Component/s: Optimizer, Storage Engine - InnoDB
Labels:
- wrong_result

Description

CREATE TABLE `temp_table` (

  `id` bigint(20) NOT NULL,

  `title` varchar(255) NOT NULL,

  `status` tinyint(4) DEFAULT 0,

  `country_code` varchar(5) DEFAULT NULL,

  `create_time` timestamp NOT NULL DEFAULT current_timestamp(),

  `update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),

  KEY `idx_status` (`status`),

  KEY `idx_country_code_status_id` (`country_code`,`status`,`id` DESC)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO temp_table(id,title,status,country_code) VALUES (5, 'abc', 1, 'NG'), (6, 'abc', 1, 'NG'), (7, 'abc', 1, 'NG'), (8, 'abc', 1, 'NG'), (9, 'abc', 1, 'NG'), (10, 'abc', 1, 'NG'), (11, 'abc', 1, 'NG'), (12, 'abc', 1, 'NG'), (13, 'abc', 1, 'NG'), (14, 'abc', 1, 'NG'), (15, 'abc', 1, 'NG'), (16, 'abc', 1, 'NG'), (17, 'abc', 1, 'NG'), (18, 'abc', 1, 'NG'), (19, 'abc', 1, 'NG'), (20, 'abc', 1, 'NG'), (21, 'abc', 1, 'NG'), (22, 'abc', 1, 'NG'), (23, 'abc', 1, 'NG'), (24, 'abc', 1, 'NG'), (25, 'abc', 1, 'NG'), (26, 'abc', 1, 'NG'), (27, 'abc', 1, 'NG'), (28, 'abc', 4, 'NG'), (29, 'abc', 4, 'NG'), (30, 'abc', 1, 'NG'), (31, 'abc', 1, 'CM'), (32, 'abc', 1, 'CM'), (33, 'abc', 1, 'NG'), (34, 'abc', 1, 'NG'), (35, 'abc', 1, 'NG'), (36, 'abc', 1, 'NG'), (37, 'abc', 1, 'NG'), (38, 'abc', 1, 'NG'), (39, 'abc', 1, 'NG'), (40, 'abc', 1, 'NG'), (41, 'abc', 1, 'NG'), (42, 'abc', 1, 'NG'), (43, 'abc', 1, 'NG'), (44, 'abc', 1, 'NG'), (45, 'abc', 1, 'NG'), (46, 'abc', 1, 'CM'), (47, 'abc', 4, 'CM'), (48, 'abc', -1, 'NG'), (49, 'abc', 1, 'NG'), (50, 'abc', -1, 'NG'), (51, 'abc', 1, 'NG'), (52, 'abc', 1, 'NG'), (53, 'abc', -1, 'NG'), (54, 'abc', 1, 'CM'), (55, 'abc', 1, 'CM'), (56, 'abc', 1, 'CM'), (57, 'abc', 1, 'NG'), (58, 'abc', -1, 'NG'), (59, 'abc', 1, 'PH'), (60, 'abc', 1, 'CM'), (61, 'abc', 1, 'NG'), (62, 'abc', -1, 'NG'), (63, 'abc', -1, 'NG'), (64, 'abc', 1, 'CM'), (65, 'abc', 1, 'NG'), (66, 'abc', 1, 'NG'), (67, 'abc', 1, 'NG'), (68, 'abc', 1, 'NG'), (69, 'abc', -1, 'NG'), (70, 'abc', 1, 'NG'), (71, 'abc', 1, 'NG'), (72, 'abc', 1, 'NG'), (73, 'abc', 1, 'NG'), (74, 'abc', 1, 'NG'), (75, 'abc', 1, 'CM'), (76, 'abc', 1, 'CM'), (77, 'abc', 1, 'CM'), (78, 'abc', 1, 'CM'), (79, 'abc', 1, 'CM'), (80, 'abc', 1, 'NG'), (81, 'abc', -1, 'NG'), (82, 'abc', 1, 'NG'), (83, 'abc', 1, 'NG'), (84, 'abc', 1, 'NG'), (85, 'abc', 1, 'NG'), (86, 'abc', 1, 'NG'), (87, 'abc', 1, 'NG'), (88, 'abc', 1, 'NG'), (89, 'abc', 1, 'NG'), (90, 'abc', 1, 'NG'), (91, 'abc', 1, 'NG'), (92, 'abc', 1, 'NG'), (93, 'abc', -1, 'NG'), (94, 'abc', 1, 'NG'), (95, 'abc', 1, 'NG'), (96, 'abc', 1, 'NG'), (97, 'abc', 1, 'NG'), (98, 'abc', 1, 'NG'), (99, 'abc', 1, 'NG'), (100, 'abc', 1, 'NG'), (101, 'abc', 1, 'NG'), (102, 'abc', 1, 'NG'), (103, 'abc', 1, 'NG'), (104, 'abc', 1, 'NG'), (105, 'abc', 1, 'NG'), (106, 'abc', 1, 'NG'), (107, 'abc', 1, 'NG'), (108, 'abc', 1, 'NG'), (109, 'abc', 1, 'NG'), (110, 'abc', 1, 'NG'), (111, 'abc', 1, 'NG'), (112, 'abc', 1, 'NG'), (113, 'abc', 1, 'NG'), (114, 'abc', 1, 'NG'), (115, 'abc', 1, 'NG'), (116, 'abc', 1, 'NG'), (117, 'abc', 1, 'NG'), (118, 'abc', 1, 'NG'), (119, 'abc', 1, 'NG'), (120, 'abc', 1, 'NG'), (121, 'abc', 1, 'NG'), (122, 'abc', 1, 'NG'), (123, 'abc', 1, 'NG'), (124, 'abc', 1, 'NG'), (125, 'abc', 1, 'NG'), (126, 'abc', 1, 'NG'), (127, 'abc', 1, 'NG'), (128, 'abc', 1, 'NG'), (129, 'abc', 1, 'NG'), (130, 'abc', 1, 'NG'), (131, 'abc', -1, 'OT'), (132, 'abc', 1, 'NG'), (133, 'abc', 1, 'NG'), (134, 'abc', 1, 'NG'), (135, 'abc', 1, 'NG'), (136, 'abc', 2, 'OT'), (137, 'abc', 2, 'OT'), (138, 'abc', 1, 'NG'), (139, 'abc', 1, 'NG'), (140, 'abc', 1, 'NG'), (141, 'abc', 1, 'NG'), (142, 'abc', 1, 'NG'), (143, 'abc', 1, 'NG'), (144, 'abc', 1, 'NG'), (145, 'abc', 1, 'NG'), (146, 'abc', 1, 'NG'), (147, 'abc', 1, 'NG'), (148, 'abc', 1, 'NG'), (149, 'abc', 1, 'NG'), (150, 'abc', 1, 'CM'), (151, 'abc', 1, 'NG'), (152, 'abc', 1, 'NG'), (153, 'abc', 1, 'NG'), (154, 'abc', 1, 'NG'), (155, 'abc', 1, 'NG'), (156, 'abc', 4, 'CM'), (157, 'abc', 1, 'NG'), (158, 'abc', 1, 'NG'), (159, 'abc', 1, 'ML'), (160, 'abc', -1, 'OT'), (161, 'abc', -1, 'OT'), (162, 'abc', 2, 'OT'), (163, 'abc', 2, 'OT'), (164, 'abc', 1, 'OT'), (165, 'abc', 1, 'OT'), (166, 'abc', 1, 'NG'), (167, 'abc', 1, 'CM'), (168, 'abc', 1, 'NG'), (169, 'abc', 2, 'CM'), (170, 'abc', 1, 'NG'), (171, 'abc', 1, 'NG'), (172, 'abc', 1, 'DZ'), (173, 'abc', 0, 'UG'), (174, 'abc', 0, 'UG'), (175, 'abc', 1, 'CM'), (176, 'abc', 1, 'ZM'), (177, 'abc', 0, 'NG'), (178, 'abc', -1, 'OT'), (179, 'abc', 1, 'UG'), (180, 'abc', 0, 'OT'), (181, 'abc', 0, 'OT'), (182, 'abc', 0, 'OT'), (183, 'abc', 1, 'OT'), (184, 'abc', 1, 'UG'), (185, 'abc', -1, 'UG'), (186, 'abc', 1, 'UG'), (187, 'abc', 1, 'OT'), (188, 'abc', 1, 'NG'), (189, 'abc', 4, 'UG'), (190, 'abc', 1, 'NG'), (191, 'abc', 1, 'NG'), (192, 'abc', 1, 'UG'), (193, 'abc', 1, 'NG'), (194, 'abc', 1, 'NG'), (195, 'abc', -1, 'NG'), (196, 'abc', 1, 'NG'), (197, 'abc', 2, 'NG'), (198, 'abc', -1, 'NG'), (199, 'abc', 1, 'NG'), (200, 'abc', -1, 'NG'), (201, 'abc', 1, 'NG'), (202, 'abc', 0, 'NG'), (203, 'abc', 1, 'NG'), (204, 'abc', 2, 'UG'), (205, 'abc', -1, 'UG'), (206, 'abc', -1, 'NG'), (207, 'abc', -1, 'NG'), (208, 'abc', 1, 'NG'), (209, 'abc', 4, 'UG'), (210, 'abc', 1, 'NG'), (211, 'abc', 1, 'UG'), (212, 'abc', 1, 'NG'), (213, 'abc', 1, 'CM'), (214, 'abc', 1, 'CM'), (215, 'abc', 1, 'KE'), (216, 'abc', 1, 'ZM'), (217, 'abc', 1, 'NG'), (218, 'abc', 1, 'NG'), (219, 'abc', 1, 'UG'), (220, 'abc', 1, 'UG'), (221, 'abc', 1, 'UG'), (222, 'abc', 1, 'NG'), (223, 'abc', 1, 'UG'), (224, 'abc', 1, 'UG'), (225, 'abc', 1, 'NG'), (226, 'abc', 1, 'UG'), (227, 'abc', 1, 'NG'), (228, 'abc', 1, 'NG'), (229, 'abc', 1, 'NG'), (230, 'abc', 1, 'NG'), (231, 'abc', 1, 'NG'), (232, 'abc', 1, 'UG'), (233, 'abc', -1, 'NG'), (234, 'abc', 1, 'UG'), (235, 'abc', 1, 'NG'), (236, 'abc', 1, 'NG'), (237, 'abc', -1, 'NG'), (238, 'abc', 1, 'NG'), (239, 'abc', 1, 'CM'), (240, 'abc', 1, 'NG'), (241, 'abc', 1, 'NG'), (242, 'abc', 1, 'NG'), (243, 'abc', 1, 'OT'), (244, 'abc', 1, 'OT'), (245, 'abc', -1, 'NG'), (246, 'abc', 1, 'UG'), (247, 'abc', 4, 'UG'), (248, 'abc', 1, 'UG'), (249, 'abc', -1, 'NG'), (250, 'abc', 1, 'NG'), (251, 'abc', 1, 'CM'), (252, 'abc', 1, 'NG'), (253, 'abc', 2, 'UG'), (254, 'abc', 1, 'ZM'), (255, 'abc', 2, 'UG'), (256, 'abc', 1, 'UG'), (257, 'abc', 1, 'NG'), (258, 'abc', 1, 'NG'), (259, 'abc', 1, 'UG'), (260, 'abc', 0, 'UG'), (261, 'abc', 0, 'NG'), (262, 'abc', 0, 'NG'), (263, 'abc', 1, 'NG'), (264, 'abc', 1, 'NG'), (265, 'abc', 1, 'NG'), (266, 'abc', 1, 'UG'), (267, 'abc', 1, 'NG'), (268, 'abc', 1, 'NG'), (269, 'abc', 4, 'NG'), (270, 'abc', 1, 'NG'), (271, 'abc', 1, 'NG'), (272, 'abc', 1, 'NG'), (273, 'abc', 1, 'NG'), (274, 'abc', 1, 'NG'), (275, 'abc', 1, 'NG'), (276, 'abc', 1, 'NG'), (277, 'abc', 1, 'NG'), (278, 'abc', 1, 'NG'), (279, 'abc', 1, 'CM'), (280, 'abc', -1, 'NG'), (281, 'abc', -1, 'NG'), (282, 'abc', 1, 'NG'), (283, 'abc', 1, 'NG'), (284, 'abc', -1, 'NG'), (285, 'abc', 1, 'NG'), (286, 'abc', 1, 'NG'), (287, 'abc', 1, 'NG'), (288, 'abc', -1, 'NG'), (289, 'abc', 1, 'NG'), (290, 'abc', 1, 'NG'), (291, 'abc', 1, 'NG'), (292, 'abc', 1, 'NG'), (293, 'abc', 1, 'UG'), (294, 'abc', 1, 'NG'), (295, 'abc', 1, 'NG'), (296, 'abc', 1, 'NG'), (297, 'abc', 1, 'NG'), (298, 'abc', 1, 'NG'), (299, 'abc', -1, 'NG'), (300, 'abc', 1, 'NG'), (301, 'abc', 1, 'NG'), (302, 'abc', 1, 'NG'), (303, 'abc', 1, 'UG'), (304, 'abc', 1, 'NG'), (305, 'abc', -1, 'NG'), (306, 'abc', 1, 'NG'), (307, 'abc', 1, 'NG'), (308, 'abc', 1, 'NG'), (309, 'abc', 1, 'NG'), (310, 'abc', -1, 'NG'), (311, 'abc', 1, 'NG'), (312, 'abc', 1, 'NG'), (313, 'abc', 1, 'NG'), (314, 'abc', 1, 'NG'), (315, 'abc', -1, 'NG'), (316, 'abc', -1, 'NG'), (317, 'abc', 1, 'NG'), (318, 'abc', 1, 'NG'), (319, 'abc', 1, 'UG'), (320, 'abc', 1, 'UG'), (321, 'abc', 1, 'UG'), (322, 'abc', 1, 'UG'), (323, 'abc', 1, 'UG'), (324, 'abc', 1, 'UG'), (325, 'abc', 1, 'NG'), (326, 'abc', 1, 'NG'), (327, 'abc', 1, 'NG'), (328, 'abc', 1, 'NG'), (329, 'abc', 1, 'NG'), (330, 'abc', 1, 'UG'), (331, 'abc', 1, 'NG'), (332, 'abc', -1, 'NG'), (333, 'abc', 1, 'NG'), (334, 'abc', 1, 'ZM'), (335, 'abc', 1, 'NG'), (336, 'abc', -1, 'NG'), (337, 'abc', 1, 'NG'), (338, 'abc', 1, 'NG'), (339, 'abc', 1, 'NG'), (340, 'abc', 1, 'NG'), (341, 'abc', 1, 'NG'), (342, 'abc', 4, 'NG'), (343, 'abc', 1, 'NG'), (344, 'abc', 1, 'NG'), (345, 'abc', 4, 'NG'), (346, 'abc', -1, 'NG'), (347, 'abc', -1, 'NG'), (348, 'abc', -1, 'NG'), (349, 'abc', -1, 'NG'), (350, 'abc', 1, 'NG'), (351, 'abc', 1, 'NG'), (352, 'abc', 1, 'NG'), (353, 'abc', 1, 'NG'), (354, 'abc', 1, 'NG'), (355, 'abc', 1, 'NG'), (356, 'abc', -1, 'NG'), (357, 'abc', -1, 'NG'), (358, 'abc', 1, 'NG'), (359, 'abc', 1, 'NG'), (360, 'abc', 1, 'UG'), (361, 'abc', 1, 'UG'), (362, 'abc', 1, 'UG'), (363, 'abc', 1, 'UG'), (364, 'abc', 1, 'NG'), (365, 'abc', 1, 'NG'), (366, 'abc', 1, 'UG'), (367, 'abc', 1, 'UG'), (368, 'abc', 1, 'NG'), (369, 'abc', 1, 'NG'), (370, 'abc', 1, 'NG'), (371, 'abc', 1, 'NG'), (372, 'abc', 1, 'NG'), (373, 'abc', 1, 'NG'), (374, 'abc', 1, 'NG'), (375, 'abc', 1, 'UG'), (376, 'abc', 1, 'NG'), (377, 'abc', 1, 'NG'), (378, 'abc', -1, 'NG'), (379, 'abc', -1, 'NG'), (380, 'abc', 1, 'UG'), (381, 'abc', 1, 'NG'), (382, 'abc', 1, 'UG'), (383, 'abc', 1, 'UG'), (384, 'abc', 1, 'UG'), (385, 'abc', 1, 'UG'), (386, 'abc', 1, 'UG'), (387, 'abc', 1, 'NG'), (388, 'abc', 1, 'UG'), (389, 'abc', 1, 'UG'), (390, 'abc', 1, 'UG'), (391, 'abc', 1, 'UG'), (392, 'abc', 1, 'UG'), (393, 'abc', 1, 'NG'), (394, 'abc', 1, 'NG'), (395, 'abc', 1, 'UG'), (396, 'abc', 1, 'NG'), (397, 'abc', 1, 'NG'), (398, 'abc', 1, 'UG'), (399, 'abc', 1, 'UG'), (400, 'abc', 1, 'UG'), (401, 'abc', 1, 'UG'), (402, 'abc', 1, 'NG'), (403, 'abc', 1, 'NG'), (404, 'abc', 1, 'TZ'), (405, 'abc', 1, 'TZ'), (406, 'abc', 1, 'NG'), (407, 'abc', 1, 'UG'), (408, 'abc', 1, 'NG'), (409, 'abc', 1, 'NG'), (410, 'abc', 1, 'UG'), (411, 'abc', 1, 'UG'), (412, 'abc', 1, 'NG'), (413, 'abc', 1, 'UG'), (414, 'abc', 1, 'ET'), (415, 'abc', 1, 'UG'), (416, 'abc', 1, 'UG'), (417, 'abc', 1, 'NG'), (418, 'abc', -1, 'NG'), (419, 'abc', 1, 'UG'), (420, 'abc', 1, 'UG'), (421, 'abc', 1, 'UG'), (422, 'abc', 1, 'UG'), (423, 'abc', 1, 'UG'), (424, 'abc', 1, 'UG'), (425, 'abc', 1, 'NG'), (426, 'abc', 0, 'NG'), (427, 'abc', 1, 'UG'), (428, 'abc', 1, 'UG'), (429, 'abc', 1, 'UG'), (430, 'abc', 1, 'UG'), (431, 'abc', 1, 'UG'), (432, 'abc', 1, 'NG'), (433, 'abc', 1, 'UG'), (434, 'abc', 1, 'UG'), (435, 'abc', 1, 'UG'), (436, 'abc', 1, 'UG'), (437, 'abc', 1, 'UG'), (438, 'abc', 1, 'UG'), (439, 'abc', 1, 'UG'), (440, 'abc', 1, 'UG'), (441, 'abc', 1, 'UG'), (442, 'abc', 1, 'NG'), (443, 'abc', 1, 'UG'), (444, 'abc', 1, 'UG'), (445, 'abc', 1, 'UG'), (446, 'abc', 1, 'UG'), (447, 'abc', 1, 'UG'), (448, 'abc', 1, 'UG'), (449, 'abc', 1, 'UG'), (450, 'abc', 1, 'UG'), (451, 'abc', 1, 'UG'), (452, 'abc', 1, 'UG'), (453, 'abc', 1, 'UG'), (454, 'abc', 1, 'UG'), (455, 'abc', 1, 'UG'), (456, 'abc', 1, 'UG'), (457, 'abc', 1, 'UG'), (458, 'abc', 1, 'UG'), (459, 'abc', 1, 'UG'), (460, 'abc', 1, 'UG'), (461, 'abc', 1, 'UG'), (462, 'abc', 1, 'UG'), (463, 'abc', 1, 'UG'), (464, 'abc', 1, 'UG'), (465, 'abc', 1, 'UG'), (466, 'abc', 1, 'UG'), (467, 'abc', 1, 'UG'), (468, 'abc', 1, 'UG'), (469, 'abc', 1, 'UG'), (470, 'abc', 1, 'UG'), (471, 'abc', 1, 'UG'), (472, 'abc', 1, 'UG'), (473, 'abc', 1, 'UG'), (474, 'abc', 1, 'UG'), (475, 'abc', 1, 'UG'), (476, 'abc', 1, 'NG'), (477, 'abc', 1, 'UG'), (478, 'abc', 1, 'NG'), (479, 'abc', 1, 'UG'), (480, 'abc', 1, 'UG'), (481, 'abc', 1, 'UG'), (482, 'abc', 1, 'UG'), (483, 'abc', 1, 'UG'), (484, 'abc', 1, 'UG'), (485, 'abc', 1, 'UG'), (486, 'abc', 1, 'UG'), (487, 'abc', 1, 'UG'), (488, 'abc', 1, 'UG'), (489, 'abc', 4, 'UG'), (490, 'abc', 0, 'NG'), (491, 'abc', 1, 'NG'), (492, 'abc', 1, 'NG'), (493, 'abc', 1, 'UG'), (494, 'abc', 4, 'UG'), (495, 'abc', 1, 'UG'), (496, 'abc', 4, 'UG'), (497, 'abc', 4, 'UG'), (498, 'abc', 1, 'NG'), (499, 'abc', 1, 'UG'), (500, 'abc', 1, 'UG'), (501, 'abc', 0, 'CM'), (502, 'abc', 0, 'CM'), (503, 'abc', 1, 'NG'), (504, 'abc', 0, 'CM'), (505, 'abc', 0, 'CM'), (506, 'abc', 1, 'CM'), (507, 'abc', 1, 'UG'), (508, 'abc', 1, 'NG'), (509, 'abc', 0, 'UG'), (510, 'abc', 0, 'UG'), (511, 'abc', 1, 'UG'), (512, 'abc', 1, 'NG'), (513, 'abc', 1, 'NG'), (514, 'abc', 1, 'NG'), (515, 'abc', 1, 'UG'), (516, 'abc', 1, 'NG'), (517, 'abc', 1, 'UG'), (518, 'abc', 1, 'UG'), (519, 'abc', 1, 'UG'), (520, 'abc', 1, 'NG'), (521, 'abc', 1, 'UG'), (522, 'abc', 1, 'UG'), (523, 'abc', 1, 'UG'), (524, 'abc', 1, 'OT'), (525, 'abc', 1, 'UG'), (526, 'abc', 1, 'OT'), (527, 'abc', 1, 'UG'), (528, 'abc', 1, 'UG'), (529, 'abc', 1, 'UG'), (530, 'abc', 1, 'UG'), (531, 'abc', 1, 'UG'), (532, 'abc', 1, 'UG'), (533, 'abc', 1, 'NG'), (534, 'abc', 1, 'NG'), (535, 'abc', 1, 'UG'), (536, 'abc', 1, 'NG'), (537, 'abc', 1, 'NG'), (538, 'abc', 1, 'UG'), (539, 'abc', 1, 'UG'), (540, 'abc', 1, 'UG'), (541, 'abc', 1, 'NG'), (542, 'abc', 1, 'NG'), (543, 'abc', 1, 'NG'), (544, 'abc', 1, 'UG'), (545, 'abc', 1, 'NG'), (546, 'abc', 1, 'UG'), (547, 'abc', 1, 'NG'), (548, 'abc', 1, 'NG'), (549, 'abc', 1, 'NG'), (550, 'abc', 1, 'NG'), (551, 'abc', 1, 'NG'), (552, 'abc', 1, 'NG'), (553, 'abc', 1, 'NG'), (554, 'abc', 1, 'NG'), (555, 'abc', 1, 'NG'), (556, 'abc', 1, 'NG'), (557, 'abc', 1, 'NG'), (558, 'abc', 1, 'NG'), (559, 'abc', 1, 'NG'), (560, 'abc', 1, 'NG'), (561, 'abc', 1, 'NG'), (562, 'abc', 1, 'NG'), (563, 'abc', 1, 'UG'), (564, 'abc', 1, 'NG'), (565, 'abc', 1, 'NG'), (566, 'abc', 1, 'CM'), (567, 'abc', 1, 'UG'), (568, 'abc', 1, 'UG'), (569, 'abc', 1, 'UG'), (570, 'abc', 1, 'NG'), (571, 'abc', 1, 'NG'), (572, 'abc', 1, 'UG'), (573, 'abc', 1, 'UG'), (574, 'abc', 1, 'UG'), (575, 'abc', 1, 'UG'), (576, 'abc', 1, 'UG'), (577, 'abc', 1, 'NG'), (578, 'abc', 1, 'UG'), (579, 'abc', 1, 'NG'), (580, 'abc', 1, 'NG'), (581, 'abc', 1, 'UG'), (582, 'abc', 1, 'UG'), (583, 'abc', 1, 'UG'), (584, 'abc', 1, 'UG'), (585, 'abc', 1, 'UG'), (586, 'abc', 1, 'UG'), (587, 'abc', 1, 'UG'), (588, 'abc', 1, 'UG'), (589, 'abc', 1, 'UG'), (590, 'abc', 1, 'UG'), (591, 'abc', 1, 'UG'), (592, 'abc', 1, 'UG'), (593, 'abc', 1, 'UG'), (594, 'abc', 1, 'UG'), (595, 'abc', 1, 'UG'), (596, 'abc', 1, 'UG'), (597, 'abc', 1, 'UG'), (598, 'abc', 1, 'UG'), (599, 'abc', 1, 'UG'), (600, 'abc', -1, 'UG'), (601, 'abc', -1, 'UG'), (602, 'abc', 1, 'UG'), (603, 'abc', 1, 'UG'), (604, 'abc', 1, 'UG'), (605, 'abc', 1, 'UG'), (606, 'abc', 1, 'UG'), (607, 'abc', 1, 'UG'), (608, 'abc', 1, 'UG'), (609, 'abc', 1, 'UG'), (610, 'abc', 1, 'UG'), (611, 'abc', 1, 'UG'), (612, 'abc', 1, 'UG'), (613, 'abc', 1, 'UG'), (614, 'abc', 1, 'UG'), (615, 'abc', 1, 'UG'), (616, 'abc', 1, 'UG'), (617, 'abc', 1, 'UG'), (618, 'abc', 1, 'UG'), (619, 'abc', 1, 'UG'), (620, 'abc', 1, 'UG'), (621, 'abc', 1, 'UG'), (622, 'abc', 1, 'UG'), (623, 'abc', 0, 'UG'), (624, 'abc', 1, 'UG'), (625, 'abc', 1, 'UG'), (626, 'abc', 1, 'UG'), (627, 'abc', -1, 'UG'), (628, 'abc', 1, 'UG'), (629, 'abc', 1, 'UG'), (630, 'abc', -1, 'UG'), (631, 'abc', -1, 'UG'), (632, 'abc', -1, 'UG'), (633, 'abc', -1, 'UG'), (634, 'abc', -1, 'UG'), (635, 'abc', -1, 'UG'), (636, 'abc', -1, 'UG'), (637, 'abc', -1, 'UG'), (638, 'abc', -1, 'UG'), (639, 'abc', 1, 'UG'), (640, 'abc', 1, 'UG'), (641, 'abc', -1, 'UG'), (642, 'abc', -1, 'UG'), (643, 'abc', 1, 'UG'), (644, 'abc', 1, 'UG'), (645, 'abc', 1, 'UG'), (646, 'abc', 1, 'UG'), (647, 'abc', 1, 'UG'), (648, 'abc', 1, 'NG'), (649, 'abc', 1, 'UG'), (650, 'abc', -1, 'UG'), (651, 'abc', 1, 'UG'), (652, 'abc', -1, 'NG'), (653, 'abc', -1, 'NG'), (654, 'abc', 1, 'UG'), (655, 'abc', 4, 'NG'), (656, 'abc', 1, 'UG'), (657, 'abc', 1, 'NG'), (658, 'abc', 1, 'NG'), (659, 'abc', 1, 'NG'), (660, 'abc', 1, 'NG'), (661, 'abc', 1, 'NG'), (662, 'abc', 1, 'UG'), (663, 'abc', 1, 'NG'), (664, 'abc', 1, 'NG'), (665, 'abc', 1, 'UG'), (666, 'abc', 1, 'PK'), (667, 'abc', 1, 'UG'), (668, 'abc', 1, 'NG'), (669, 'abc', 1, 'UG'), (670, 'abc', 1, 'NG'), (671, 'abc', 1, 'UG'), (672, 'abc', 1, 'NG'), (673, 'abc', 1, 'NG'), (674, 'abc', 1, 'UG'), (675, 'abc', 1, 'NG'), (676, 'abc', 1, 'UG'), (677, 'abc', 1, 'UG'), (678, 'abc', 1, 'UG'), (679, 'abc', 1, 'UG'), (680, 'abc', 1, 'UG'), (681, 'abc', 1, 'UG'), (682, 'abc', 1, 'UG'), (683, 'abc', 1, 'UG'), (684, 'abc', 3, 'UG'), (685, 'abc', 1, 'UG'), (686, 'abc', 1, 'UG'), (687, 'abc', 1, 'UG'), (688, 'abc', 1, 'NG'), (689, 'abc', -1, 'NG'), (690, 'abc', 1, 'UG'), (691, 'abc', 1, 'UG'), (692, 'abc', 1, 'UG'), (693, 'abc', 1, 'UG'), (694, 'abc', 1, 'UG'), (695, 'abc', 1, 'UG'), (696, 'abc', 1, 'UG'), (697, 'abc', 1, 'NG'), (698, 'abc', 1, 'NG'), (699, 'abc', 1, 'UG'), (700, 'abc', 1, 'NG'), (701, 'abc', 1, 'UG'), (702, 'abc', 1, 'NG'), (703, 'abc', 4, 'NG'), (704, 'abc', 1, 'UG'), (705, 'abc', 1, 'UG'), (706, 'abc', 1, 'NG'), (707, 'abc', 1, 'NG'), (708, 'abc', 1, 'NG'), (709, 'abc', 1, 'NG'), (710, 'abc', 1, 'UG'), (711, 'abc', 1, 'UG'), (712, 'abc', 1, 'NG'), (713, 'abc', 1, 'NG'), (714, 'abc', 1, 'NG'), (715, 'abc', 1, 'UG'), (716, 'abc', 0, 'NG'), (717, 'abc', 0, 'NG'), (718, 'abc', 1, 'UG'), (719, 'abc', 1, 'UG'), (720, 'abc', -1, 'NG'), (721, 'abc', 0, 'NG'), (722, 'abc', -1, 'NG'), (723, 'abc', 1, 'UG'), (724, 'abc', 1, 'UG'), (725, 'abc', 1, 'UG'), (726, 'abc', 1, 'UG'), (727, 'abc', 1, 'UG'), (728, 'abc', 4, 'UG'), (729, 'abc', -1, 'UG'), (730, 'abc', -1, 'UG'), (731, 'abc', 1, 'NG'), (732, 'abc', 1, 'NG'), (733, 'abc', 1, 'UG'), (734, 'abc', 1, 'UG'), (735, 'abc', 1, 'UG'), (736, 'abc', 1, 'UG'), (737, 'abc', 0, 'NG'), (738, 'abc', -1, 'UG'), (739, 'abc', -1, 'UG'), (740, 'abc', 1, 'NG'), (741, 'abc', 1, 'NG'), (742, 'abc', 1, 'CN'), (743, 'abc', 1, 'PK'), (744, 'abc', 1, 'PK'), (745, 'abc', 1, 'PK'), (746, 'abc', -1, 'NG'), (747, 'abc', 4, 'NG'), (748, 'abc', 1, 'NG'), (749, 'abc', 1, 'UG'), (750, 'abc', 1, 'NG'), (751, 'abc', 1, 'UG'), (752, 'abc', 1, 'UG'), (753, 'abc', 1, 'NG'), (754, 'abc', 1, 'NG'), (755, 'abc', 1, 'NG'), (756, 'abc', 1, 'NG'), (757, 'abc', 1, 'NG'), (758, 'abc', 1, 'NG'), (759, 'abc', 1, 'NG'), (760, 'abc', 1, 'NG'), (761, 'abc', 1, 'NG'), (762, 'abc', 2, 'UG'), (763, 'abc', 1, 'UG'), (764, 'abc', -1, 'UG'), (765, 'abc', 0, 'UG'), (766, 'abc', 1, 'UG'), (767, 'abc', 1, 'UG'), (768, 'abc', 1, 'UG'), (769, 'abc', 1, 'UG'), (770, 'abc', 1, 'UG'), (771, 'abc', 0, 'UG'), (772, 'abc', 2, 'UG'), (773, 'abc', 1, 'NG'), (774, 'abc', 1, 'NG'), (775, 'abc', 1, 'NG'), (776, 'abc', 2, 'NG'), (777, 'abc', 1, 'UG'), (778, 'abc', 1, 'UG'), (779, 'abc', 1, 'NG'), (780, 'abc', 1, 'NG'), (781, 'abc', 1, 'NG'), (782, 'abc', 1, 'NG'), (783, 'abc', 1, 'NG'), (784, 'abc', 1, 'NG'), (785, 'abc', 1, 'NG'), (786, 'abc', 1, 'UG'), (787, 'abc', 1, 'UG'), (788, 'abc', 1, 'UG'), (789, 'abc', 1, 'NG'), (790, 'abc', 1, 'UG'), (791, 'abc', 1, 'UG'), (792, 'abc', 1, 'UG'), (793, 'abc', 1, 'UG'), (794, 'abc', 4, 'UG'), (795, 'abc', 2, 'NG'), (796, 'abc', 2, 'NG'), (797, 'abc', 1, 'NG'), (798, 'abc', 1, 'NG'), (799, 'abc', 1, 'NG'), (800, 'abc', 2, 'NG'), (801, 'abc', 1, 'UG'), (802, 'abc', 1, 'NG'), (803, 'abc', 1, 'UG'), (804, 'abc', -1, 'NG'), (805, 'abc', 1, 'UG'), (806, 'abc', 1, 'UG'), (807, 'abc', 0, 'NG'), (808, 'abc', 1, 'UG'), (809, 'abc', 4, 'UG'), (810, 'abc', 1, 'NG'), (811, 'abc', 1, 'OT'), (812, 'abc', 1, 'OT'), (813, 'abc', 1, 'OT'), (814, 'abc', 1, 'OT'), (815, 'abc', -1, 'UG'), (816, 'abc', -1, 'UG'), (817, 'abc', 0, 'UG'), (818, 'abc', 0, 'UG'), (819, 'abc', 2, 'UG'), (820, 'abc', 2, 'UG'), (821, 'abc', 4, 'UG'), (822, 'abc', -1, 'UG'), (823, 'abc', 1, 'NG'), (824, 'abc', 1, 'NG'), (825, 'abc', 1, 'NG'), (826, 'abc', 1, 'NG'), (827, 'abc', -1, 'UG'), (828, 'abc', -1, 'UG'), (829, 'abc', 1, 'NG'), (830, 'abc', 1, 'UG'), (831, 'abc', 1, 'NG'), (832, 'abc', 1, 'NG'), (833, 'abc', 1, 'NG'), (834, 'abc', 1, 'NG'), (835, 'abc', 1, 'UG'), (836, 'abc', 1, 'UG'), (837, 'abc', 1, 'UG'), (838, 'abc', 1, 'UG'), (839, 'abc', 0, 'UG'), (840, 'abc', 0, 'UG'), (841, 'abc', 0, 'UG'), (842, 'abc', 1, 'NG'), (843, 'abc', 1, 'NG'), (844, 'abc', 1, 'NG'), (845, 'abc', 1, 'NG'), (846, 'abc', 1, 'NG'), (847, 'abc', 1, 'NG'), (848, 'abc', 1, 'NG'), (849, 'abc', 1, 'NG'), (850, 'abc', 1, 'NG'), (851, 'abc', 1, 'NG'), (852, 'abc', 1, 'NG'), (853, 'abc', 1, 'NG'), (854, 'abc', 1, 'NG');

## true：have 282 lines

select * from temp_table where country_code ='UG' and `status` =1;

## wrong: have only one line

select * from temp_table where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20;

## wrong: have only one line

select * from temp_table where country_code ='UG' and `status` =1 ORDER BY id desc LIMIT 20;

## true：have 20 lines

select * from temp_table where country_code ='UG' and `status` =1 ORDER BY id asc LIMIT 20;

## true：have 20 lines

select * from temp_table force index(idx_status) where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20;

## true：have 20 lines

select * from temp_table force index(idx_country_code_status_id) where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20;

Attachments

Issue Links

is caused by

MDEV-26996 Support descending indexes in the range optimizer

Closed

Activity

Ascending order - Click to sort in descending order

rank added a comment - 1 week ago

if I do not use Descending Indexes with primary key column, just use Descending Indexes with normal column，the select lines are true.

alter table temp_table drop index idx_country_code_status_id,add index idx_country_code_status_create_time(`country_code`,`status`,`create_time` DESC);

the under of select sql，all have 20 lines

select * from temp_table where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20;

select * from temp_table where country_code ='UG' and `status` =1 ORDER BY id desc LIMIT 20;

select * from temp_table where country_code ='UG' and `status` =1 ORDER BY id asc LIMIT 20;

select * from temp_table force index(idx_status) where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20;

rank added a comment - 1 week ago if I do not use Descending Indexes with primary key column, just use Descending Indexes with normal column，the select lines are true. alter table temp_table drop index idx_country_code_status_id,add index idx_country_code_status_create_time(`country_code`,`status`,`create_time` DESC); the under of select sql，all have 20 lines select * from temp_table where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20; select * from temp_table where country_code ='UG' and `status` =1 ORDER BY id desc LIMIT 20; select * from temp_table where country_code ='UG' and `status` =1 ORDER BY id asc LIMIT 20; select * from temp_table force index(idx_status) where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20;

Alice Sherepa added a comment - 1 week ago - edited

Thank you for the report!
Please use as a temporary workaround SET optimizer_switch='index_merge=on';

I repeated as described on 10.11,
10.5 and 10.6 returned expected results;
11.4,11.8 returned the expected result, but the plan is different, so I am not sure that the bug does not affect it.

10.11 33a462e0b18b4fb2fa4e254aac3e00447a831411
MariaDB [test]> select * from temp_table where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20;
+-----+-------+--------+--------------+---------------------+---------------------+
\| id \| title \| status \| country_code \| create_time \| update_time \|
+-----+-------+--------+--------------+---------------------+---------------------+
\| 838 \| abc \| 1 \| UG \| 2025-03-27 14:51:52 \| 2025-03-27 14:51:52 \|
+-----+-------+--------+--------------+---------------------+---------------------+
1 row in set (0,010 sec)

MariaDB [test]> explain extended select * from temp_table where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20;
+------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+
\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| filtered \| Extra \|
+------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+
\| 1 \| SIMPLE \| temp_table \| index_merge \| idx_status,idx_country_code_status_id \| idx_status,idx_country_code_status_id \| 2,25 \| NULL \| 282 \| 100.00 \| Using intersect(idx_status,idx_country_code_status_id); Using where; Using filesort \|
+------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+
1 row in set, 1 warning (0,005 sec)

11.8 cc831f16c82f00d3531e09c2f5c59eadc0abb0d7
MariaDB [test]> explain extended select * from temp_table where country_code ='UG' and `status` =1 ORDER BY id desc LIMIT 20;
+------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+-----------------------+
\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| filtered \| Extra \|
+------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+-----------------------+
\| 1 \| SIMPLE \| temp_table \| range \| idx_status,idx_country_code_status_id \| idx_country_code_status_id \| 25 \| NULL \| 282 \| 100.00 \| Using index condition \|
+------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0,005 sec)

Note (Code 1003): select `test`.`temp_table`.`id` AS `id`,`test`.`temp_table`.`title` AS `title`,`test`.`temp_table`.`status` AS `status`,`test`.`temp_table`.`country_code` AS `country_code`,`test`.`temp_table`.`create_time` AS `create_time`,`test`.`temp_table`.`update_time` AS `update_time` from `test`.`temp_table` where `test`.`temp_table`.`status` = 1 and `test`.`temp_table`.`country_code` = 'UG' order by `test`.`temp_table`.`id` desc limit 20
MariaDB [test]> explain extended select * from temp_table where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20;
+------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+---------------------------------------+
\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| filtered \| Extra \|
+------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+---------------------------------------+
\| 1 \| SIMPLE \| temp_table \| range \| idx_status,idx_country_code_status_id \| idx_country_code_status_id \| 25 \| NULL \| 282 \| 100.00 \| Using index condition; Using filesort \|
+------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0,005 sec)

Note (Code 1003): select `test`.`temp_table`.`id` AS `id`,`test`.`temp_table`.`title` AS `title`,`test`.`temp_table`.`status` AS `status`,`test`.`temp_table`.`country_code` AS `country_code`,`test`.`temp_table`.`create_time` AS `create_time`,`test`.`temp_table`.`update_time` AS `update_time` from `test`.`temp_table` where `test`.`temp_table`.`status` = 1 and `test`.`temp_table`.`country_code` = 'UG' order by `test`.`temp_table`.`create_time` desc limit 20

is repeatable after commit 791146b9d23e24b628a013d11ca1ff0e52504578
Author: Sergei Petrunia <psergey@askmonty.org>
Date: Tue Dec 14 01:47:01 2021 +0300

~~MDEV-26996~~ Support descending indexes in the range optimizer

Alice Sherepa added a comment - 1 week ago - edited Thank you for the report! Please use as a temporary workaround SET optimizer_switch='index_merge=on'; I repeated as described on 10.11, 10.5 and 10.6 returned expected results; 11.4,11.8 returned the expected result, but the plan is different, so I am not sure that the bug does not affect it. 10.11 33a462e0b18b4fb2fa4e254aac3e00447a831411 MariaDB [test]> select * from temp_table where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20; +-----+-------+--------+--------------+---------------------+---------------------+ | id | title | status | country_code | create_time | update_time | +-----+-------+--------+--------------+---------------------+---------------------+ | 838 | abc | 1 | UG | 2025-03-27 14:51:52 | 2025-03-27 14:51:52 | +-----+-------+--------+--------------+---------------------+---------------------+ 1 row in set (0,010 sec) MariaDB [test]> explain extended select * from temp_table where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20; +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+ | 1 | SIMPLE | temp_table | index_merge | idx_status,idx_country_code_status_id | idx_status,idx_country_code_status_id | 2,25 | NULL | 282 | 100.00 | Using intersect(idx_status,idx_country_code_status_id); Using where; Using filesort | +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0,005 sec) 11.8 cc831f16c82f00d3531e09c2f5c59eadc0abb0d7 MariaDB [test]> explain extended select * from temp_table where country_code ='UG' and `status` =1 ORDER BY id desc LIMIT 20; +------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | temp_table | range | idx_status,idx_country_code_status_id | idx_country_code_status_id | 25 | NULL | 282 | 100.00 | Using index condition | +------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0,005 sec) Note (Code 1003): select `test`.`temp_table`.`id` AS `id`,`test`.`temp_table`.`title` AS `title`,`test`.`temp_table`.`status` AS `status`,`test`.`temp_table`.`country_code` AS `country_code`,`test`.`temp_table`.`create_time` AS `create_time`,`test`.`temp_table`.`update_time` AS `update_time` from `test`.`temp_table` where `test`.`temp_table`.`status` = 1 and `test`.`temp_table`.`country_code` = 'UG' order by `test`.`temp_table`.`id` desc limit 20 MariaDB [test]> explain extended select * from temp_table where country_code ='UG' and `status` =1 ORDER BY create_time desc LIMIT 20; +------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | temp_table | range | idx_status,idx_country_code_status_id | idx_country_code_status_id | 25 | NULL | 282 | 100.00 | Using index condition; Using filesort | +------+-------------+------------+-------+---------------------------------------+----------------------------+---------+------+------+----------+---------------------------------------+ 1 row in set, 1 warning (0,005 sec) Note (Code 1003): select `test`.`temp_table`.`id` AS `id`,`test`.`temp_table`.`title` AS `title`,`test`.`temp_table`.`status` AS `status`,`test`.`temp_table`.`country_code` AS `country_code`,`test`.`temp_table`.`create_time` AS `create_time`,`test`.`temp_table`.`update_time` AS `update_time` from `test`.`temp_table` where `test`.`temp_table`.`status` = 1 and `test`.`temp_table`.`country_code` = 'UG' order by `test`.`temp_table`.`create_time` desc limit 20 is repeatable after commit 791146b9d23e24b628a013d11ca1ff0e52504578 Author: Sergei Petrunia <psergey@askmonty.org> Date: Tue Dec 14 01:47:01 2021 +0300 MDEV-26996 Support descending indexes in the range optimizer

People

Assignee:: Sergei Petrunia

Reporter:: rank

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 1 week ago 08:39

Updated:: 1 week ago 15:24

Git Integration

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

MariaDB Server