[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

People

Assignee:: Sergei Petrunia

Reporter:: rank

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 4 days ago 08:39

Updated:: 4 days ago 15:24

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration