[jira] [Created] (DRILL-2953) Group By + Order By query results are not ordered.

["Khurram Faraaz (JIRA)" <ji...@apache.org>]

Khurram Faraaz created DRILL-2953:
-------------------------------------

             Summary: Group By + Order By query results are not ordered.
                 Key: DRILL-2953
                 URL: https://issues.apache.org/jira/browse/DRILL-2953
             Project: Apache Drill
          Issue Type: Bug
          Components: Query Planning & Optimization
    Affects Versions: 0.9.0
         Environment: 10833d2cae9f5312cf0e31f8c9f3f8a9dcdc0c45 | Commit 0.9.0 release version. | 03.05.2015 @ 14:56:56 EDT
            Reporter: Khurram Faraaz
            Assignee: Jinfeng Ni
            Priority: Critical


Group by + order by query does not return results in correct order. Sort is performed before the aggregation is done, which should not be the case.
Test was performed on 4 node cluster on CentOS.

{code}
0: jdbc:drill:> select cast(columns[0] as int) c1 from `testWindow.csv` t2 where t2.columns[0] is not null group by columns[0] order by columns[0];
+------------+
|     c1     |
+------------+
| 10         |
| 100        |
| 113        |
| 119        |
| 2          |
| 50         |
| 55         |
| 57         |
| 61         |
| 67         |
| 89         |
+------------+
11 rows selected (0.218 seconds)
{code}

Explain plan for that query that returns wrong results.
{code}
0: jdbc:drill:> explain plan for select cast(columns[0] as int) c1 from `testWindow.csv` t2 where t2.columns[0] is not null group by columns[0] order by columns[0];
+------------+------------+
|    text    |    json    |
+------------+------------+
| 00-00    Screen
00-01      Project(c1=[$0])
00-02        Project(c1=[CAST($0):INTEGER], EXPR$1=[$0])
00-03          StreamAgg(group=[{0}])
00-04            Sort(sort0=[$0], dir0=[ASC])
00-05              Filter(condition=[IS NOT NULL($0)])
00-06                Project(ITEM=[ITEM($0, 0)])
00-07                  Scan(groupscan=[EasyGroupScan [selectionRoot=/tmp/testWindow.csv, numFiles=1, columns=[`columns`[0]], files=[maprfs:/tmp/testWindow.csv]]])
{code} 

Incorrect results , not in order.
{code}
0: jdbc:drill:> select cast(columns[0] as int) from `testWindow.csv` t2 where t2.columns[0] is not null group by columns[0] order by columns[0];
+------------+
|   EXPR$0   |
+------------+
| 10         |
| 100        |
| 113        |
| 119        |
| 2          |
| 50         |
| 55         |
| 57         |
| 61         |
| 67         |
| 89         |
+------------+
11 rows selected (0.214 seconds)
{code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)