[DRILL-3638] Incorrect results LEAD(<float-type-column>)

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Invalid
• Affects Version/s: 1.2.0
• Fix Version/s: 1.2.0
• Component/s: Execution - Flow
• Labels: window_function

Environment

private-branch https://github.com/adeneche/incubator-drill/tree/new-window-funcs

• Assignee: Abdel Hakim Deneche
• Reporter: Khurram Faraaz
• Created: 13/Aug/15 01:50
• Updated: 01/Sep/15 22:33
• Resolved: 13/Aug/15 16:13

Description

Query returns different results when LEAD function is given a column of type float, compared to the results returned by Postgres for same input data.

results (2 rows) returned by Drill (input is from Parquet file)

0: jdbc:drill:schema=dfs.tmp> select col2 , lead(col2) over(partition by col7 order by col0) lead_col2 from FEWRWSPQQ_101 where col2 in (127.0 , 1.0 , 99.9 , 255.99 , 63.99 , 127.9 , 128.978);
+--------+------------+
|  col2  | lead_col2  |
+--------+------------+
| 127.0  | 1.0        |
| 1.0    | null       |
+--------+------------+
2 rows selected (0.347 seconds)

results (7 rows) returned by Postgres for same input data (csv)

postgres=# select col2 , lead(col2) over(partition by col7 order by col0) lead_col2 from FEWRWSPQQ_101 where col2 in (127.0 , 1.0 , 99.9 , 255.99 , 63.99 , 127.9 , 128.978);
  col2   | lead_col2 
---------+-----------
     127 |     63.99
   63.99 |         1
       1 |   128.978
 128.978 |          
    99.9 |    255.99
  255.99 |     127.9
   127.9 |          
(7 rows)

CTAS used to create the parquet file from CSV input

CREATE TABLE FEWRWSPQQ_101 AS (SELECT cast(columns[0] as INT) col0, cast(columns[1] as BIGINT) col1, cast(columns[2] as FLOAT) col2, cast(columns[3] as DOUBLE) col3, cast(columns[4] as TIME) col4, cast(columns[5] as TIMESTAMP) col5, cast(columns[6] as DATE) col6, cast(columns[7] as BOOLEAN) col7, cast(columns[8] as char(2)) col8, cast(columns[9] as varchar(52)) col9 from `all_types_few.csv`);
+-----------+----------------------------+
| Fragment  | Number of records written  |
+-----------+----------------------------+
| 0_0       | 22                         |
+-----------+----------------------------+
1 row selected (0.643 seconds)