[CALCITE-2978] sorting not applied in subqueries

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Invalid
• Affects Version/s: 1.19.0
• Fix Version/s: 1.21.0
• Assignee: Danny Chen
• Reporter: Pressenna
• Created: 03/Apr/19 12:18
• Updated: 24/Jan/24 07:01
• Resolved: 15/Jul/19 09:30

Description

CREATE TABLE test (id INT, val INT);
INSERT INTO test VALUES (1,1);
INSERT INTO test VALUES (2,2);
INSERT INTO test VALUES (3,3);
INSERT INTO test VALUES (4,4);


SELECT id FROM (SELECT id, val FROM test ORDER BY val DESC);
 

Looks like CALCITE-2798 removes the sorting in sub-queries too aggressively.

Update:
I might be wrong here and jumped the gun too early.
Looks like SQL does not dictate that the outer query has to retain any order of the inner query.
The sort is applied if a LIMIT is specified in the inner query, to reduce the inner result correctly.

Happy to close as invalid.

Issue Links

• Blocked: CALCITE-3738 Missing LogicalSort for INSERT statement
• is caused by: CALCITE-2798 Optimizer should remove ORDER BY in sub-query, provided it has no LIMIT or OFFSET
• relates to: CALCITE-6063 If ARRAY subquery has ORDER BY (without LIMIT), rows are not sorted