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

Wrong results - Lead(char-column) without PARTITION BY clause

    Details

      Description

      Window function query that does not use partition by clause in window definition and uses LEAD function returns wrong results, on developer's private branch. This issue may be related to DRILL-3605

      Results returned by Drill

      0: jdbc:drill:schema=dfs.tmp> select lead(col2) over (order by col0) lead_col0 from `fewRowsAllData.parquet`;
      +-----------+
      | lead_col0 |
      +-----------+
      | NHIN |
      | INCACO |
      | CACOSCSD |
      | COSCSDWYLA |
      | SCSDWYLAKSCO |
      | SDWYLAKSCONYNY |
      | WYLAKSCONYNYSDGA |
      | LAKSCONYNYSDGAMOIN |
      | KSCONYNYSDGAMOINMNIA |
      | CONYNYSDGAMOINMNIAGAMN |
      | NYNYSDGAMOINMNIAGAMNMNMI |
      | NYSDGAMOINMNIAGAMNMNMIRISD |
      | SDGAMOINMNIAGAMNMNMIRISDINWI |
      | GAMOINMNIAGAMNMNMIRISDINWIMAIA |
      | MOINMNIAGAMNMNMIRISDINWIMAIANDMA |
      | INMNIAGAMNMNMIRISDINWIMAIANDMARIME |
      | MNIAGAMNMNMIRISDINWIMAIANDMARIMEMNCO |
      | IAGAMNMNMIRISDINWIMAIANDMARIMEMNCOOHMO |
      | GAMNMNMIRISDINWIMAIANDMARIMEMNCOOHMOGAVT |
      | MNMNMIRISDINWIMAIANDMARIMEMNCOOHMOGAVTNDNH |
      | MNMIRISDINWIMAIANDMARIMEMNCOOHMOGAVTNDNHRIOR |
      | MIRISDINWIMAIANDMARIMEMNCOOHMOGAVTNDNHRIORNCAZ |
      | RISDINWIMAIANDMARIMEMNCOOHMOGAVTNDNHRIORNCAZORMD |
      | SDINWIMAIANDMARIMEMNCOOHMOGAVTNDNHRIORNCAZORMDHIMA |
      | INWIMAIANDMARIMEMNCOOHMOGAVTNDNHRIORNCAZORMDHIMANYUT |
      | WIMAIANDMARIMEMNCOOHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWY |
      | MAIANDMARIMEMNCOOHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWY |
      | IANDMARIMEMNCOOHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAK |
      | NDMARIMEMNCOOHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPA |
      | MARIMEMNCOOHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGA |
      | RIMEMNCOOHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVT |
      | MEMNCOOHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTIN |
      | MNCOOHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWV |
      | COOHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMN |
      | OHMOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVT |
      | MOGAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUT |
      | GAVTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVT |
      | VTNDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISC |
      | NDNHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | NHRIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | RIORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | ORNCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | NCAZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | AZORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | ORMDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | MDHIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | HIMANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | MANYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | NYUTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | UTDEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | DEWYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | WYOHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | OHWYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | WYNHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | NHAKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | AKMDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | MDPAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | PAMNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | MNGAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | GAMOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | MOVTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | VTUTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | UTINWYWVIAMNAZVTIAUTWIVTRISCME |
      | INWYWVIAMNAZVTIAUTWIVTRISCME |
      | WYWVIAMNAZVTIAUTWIVTRISCME |
      | WVIAMNAZVTIAUTWIVTRISCME |
      | IAMNAZVTIAUTWIVTRISCME |
      | MNAZVTIAUTWIVTRISCME |
      | AZVTIAUTWIVTRISCME |
      | VTIAUTWIVTRISCME |
      | IAUTWIVTRISCME |
      | UTWIVTRISCME |
      | WIVTRISCME |
      | VTRISCME |
      | RISCME |
      | SCME |
      | ME |
      | null |
      +-----------+
      78 rows selected (0.301 seconds)
      

      Results returned by Postgres

      postgres=# select lead(col2) over (order by col0) lead_col0 from tbl_alldata;
       lead_col0 
      -----------
       NH
       IN
       CA
       CO
       SC
       SD
       WY
       LA
       KS
       CO
       NY
       NY
       SD
       GA
       MO
       IN
       MN
       IA
       GA
       MN
       MN
       MI
       RI
       SD
       IN
       WI
       MA
       IA
       ND
       MA
       RI
       ME
       MN
       CO
       OH
       MO
       GA
       VT
       NH
       ND
       RI
       OR
       NC
       AZ
       OR
       MD
       HI
       MA
       NY
       UT
       DE
       WY
       OH
       WY
       NH
       AK
       MD
       PA
       MN
       GA
       MO
       VT
       UT
       IN
       WY
       WV
       IA
       MN
       AZ
       VT
       IA
       UT
       WI
       VT
       RI
       SC
       ME
       
      (78 rows)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                adeneche Deneche A. Hakim
                Reporter:
                khfaraaz Khurram Faraaz
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: