[MDEV-26680] Order from subquery lost Created: 2021-09-25  Updated: 2021-09-26  Resolved: 2021-09-26

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.4.12, 10.6.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: M-A-X Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS7 x64


 Description   

Test case:

CREATE TABLE `_article2category` (
 
	`category_id` INT
 
);
 
	
INSERT INTO `_article2category` (`category_id`) VALUES
 
(2), (1);
 
 
 
 
 
SELECT
 
	Sub.category_id
 
FROM 
(
 
	SELECT 
		A2C.category_id
 
	FROM 
		_article2category A2C
 
	ORDER BY 
		A2C.category_id ASC
 
) Sub;
 
 
 
DROP TABLE _article2category;

Expected:
category_id
1
2

Received:
category_id
2
1

When add index by category_id works as expected

 Comments   
Comment by Daniel Black [ 2021-09-26 ]

ORDER BY in subqueries isn't supported without a LIMIT

https://mariadb.com/kb/en/subquery-limitations/

If you want an order by, it should be applied to the outer query.

Generated at Thu Feb 08 09:47:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.