Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-3638

Incorrect results LEAD(<float-type-column>)

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    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)
      

      Attachments

        1. 0_0_0.parquet
          2 kB
          Khurram Faraaz

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            adeneche Abdel Hakim Deneche
            khfaraaz Khurram Faraaz
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment