[jira] [Commented] (DRILL-2562) Order by over trimmed key, results in incorrect ordering

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

    [ https://issues.apache.org/jira/browse/DRILL-2562?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14395594#comment-14395594 ] 

Khurram Faraaz commented on DRILL-2562:
---------------------------------------

IMHOP, the query should return return the ordered results, because the trimming of whitespace characters is performed after the project is done ?

Here are different cases where I use trim function in different places in the query, and we see results.
{code}
0: jdbc:drill:> select * from `longStringInJsnData.json` where trim(key)='m';
+------------+
|    key     |
+------------+
| m          |
|           m           |
+------------+
2 rows selected (0.159 seconds)
0: jdbc:drill:> select * from `longStringInJsnData.json` where key='m';
+------------+
|    key     |
+------------+
| m          |
+------------+
1 row selected (0.126 seconds)
0: jdbc:drill:> select key from `longStringInJsnData.json` where key='m';
+------------+
|    key     |
+------------+
| m          |
+------------+
1 row selected (0.104 seconds)
0: jdbc:drill:> select key from `longStringInJsnData.json` where trim(key)='m';
+------------+
|    key     |
+------------+
| m          |
|           m           |
+------------+
2 rows selected (0.104 seconds)
0: jdbc:drill:> select key from `longStringInJsnData.json` order by trim(key);
+------------+
|    key     |
+------------+
...
36 rows selected (0.164 seconds)
0: jdbc:drill:> select trim(key) from `longStringInJsnData.json` order by trim(key);
+------------+
|   EXPR$0   |
+------------+
| Hello World! |
...
36 rows selected (0.129 seconds)
{code}

> Order by over trimmed key, results in incorrect ordering
> --------------------------------------------------------
>
>                 Key: DRILL-2562
>                 URL: https://issues.apache.org/jira/browse/DRILL-2562
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 0.8.0
>         Environment: | f658a3c513ddf7f2d1b0ad7aa1f3f65049a594fe | DRILL-2209 Insert ProjectOperator with MuxExchange | 09.03.2015 @ 01:49:18 EDT
>            Reporter: Khurram Faraaz
>            Assignee: Steven Phillips
>            Priority: Critical
>         Attachments: longStringInJsnData.json
>
>
> Input data in JSON data file has prevailing and trailing spaces for some of the values. Trimming the whitespace and then doing an order by over the trimmed results, we see the query returned results in in correct ordering. Each value is a string value. Some of the strings are very long in length (they have 1000-2049 characters in them).
> {code}
> 0: jdbc:drill:> select trim(key) from `longStringInJsnData.json` order by key;
> +------------+
> |   EXPR$0   |
> +------------+
> | p          |
> | m          |
> | a          |
> | aeiou      |
> | h          |
> | z          |
> | Hello World! |
> ...
> {code}



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