[DRILL-5845] Columns returned by select with "ORDER BY" and "LIMIT" clauses are not in correct order.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.11.0
• Fix Version/s: 1.12.0
• Component/s: Query Planning & Optimization
• Labels: ready-to-commit
• Assignee: Vitalii Diravka
• Reporter: Vitalii Diravka
• Reviewer: Arina Ielchiieva
• Created: 05/Oct/17 16:17
• Updated: 04/Dec/17 10:53
• Resolved: 24/Oct/17 10:07

Description

Column order is proper for queries with only one clause: ORDER BY or LIMIT. For queries with both these clauses column order isn't preserved.

Test case for reproduce:

0: jdbc:drill:zk=local> select * from cp.`tpch/nation.parquet` limit 1;
+--------------+----------+--------------+------------------------------------------------------+
| n_nationkey  |  n_name  | n_regionkey  |                      n_comment                       |
+--------------+----------+--------------+------------------------------------------------------+
| 0            | ALGERIA  | 0            |  haggle. carefully final deposits detect slyly agai  |
+--------------+----------+--------------+------------------------------------------------------+
1 row selected (0.181 seconds)
0: jdbc:drill:zk=local> select * from cp.`tpch/nation.parquet` order by n_name limit 1;
+------------------------------------------------------+----------+--------------+--------------+
|                      n_comment                       |  n_name  | n_nationkey  | n_regionkey  |
+------------------------------------------------------+----------+--------------+--------------+
|  haggle. carefully final deposits detect slyly agai  | ALGERIA  | 0            | 0            |
+------------------------------------------------------+----------+--------------+--------------+
1 row selected (0.154 seconds)

For json files the column ordering is not preserved as well:

select * from cp.`employee.json` limit 1;
select * from cp.`employee.json` order by full_name limit 1;

Perhaps the wrong operator for sorting is used.

Issue Links

• is related to: DRILL-1499 Different column order could appear in the result set for a schema-less select * query, even there are no changing schemas.
• is related to: DRILL-5822 The query with "SELECT *" with "ORDER BY" clause and `planner.slice_target`=1 doesn't preserve column order
• links to: GitHub Pull Request #1000