[DRILL-3606] Wrong results - Lead(char-column) without PARTITION BY clause

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• 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: 04/Aug/15 23:55
• Updated: 01/Sep/15 23:03
• Resolved: 05/Aug/15 15:38

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)

Issue Links

• relates to: DRILL-3605 Wrong results - Lead(char-column)