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

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

    XMLWordPrintableJSON

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. DRILL-3605 Wrong results - Lead(char-column)

          • Major - Major loss of function.
          • Closed

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: