[DRILL-3605] Wrong results - Lead(char-column)

• 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 22:56
• Updated: 01/Sep/15 23:36
• Resolved: 05/Aug/15 15:38

Description

col2 is of type char(2) in the parquet file.
Results returned by Drill

0: jdbc:drill:schema=dfs.tmp> select col2, lead(col2) over (partition by col2 order by col0) lead_col0 from `fewRowsAllData.parquet`;
+------+-----------+
| col2 | lead_col0 |
+------+-----------+
| AK | null |
| AZ | AZCACO |
| AZ | null |
| CA | null |
| CO | COCODEGAGAGA |
| CO | CODEGAGAGAGAHI |
| CO | null |
| DE | null |
| GA | GAGAGAHIIAIAIAIAININ |
| GA | GAGAHIIAIAIAIAININININ |
| GA | GAHIIAIAIAIAININININKSLA |
| GA | null |
| HI | null |
| IA | IAIAIAININININKSLAMAMAMAMDMDME |
| IA | IAIAININININKSLAMAMAMAMDMDMEMEMI |
| IA | IAININININKSLAMAMAMAMDMDMEMEMIMNMN |
| IA | null |
| IN | INININKSLAMAMAMAMDMDMEMEMIMNMNMNMNMNMN |
| IN | ININKSLAMAMAMAMDMDMEMEMIMNMNMNMNMNMNMOMO |
| IN | INKSLAMAMAMAMDMDMEMEMIMNMNMNMNMNMNMOMOMONC |
| IN | null |
| KS | null |
| LA | null |
| MA | MAMAMDMDMEMEMIMNMNMNMNMNMNMOMOMONCNDNDNENHNHNHNYNY |
| MA | MAMDMDMEMEMIMNMNMNMNMNMNMOMOMONCNDNDNENHNHNHNYNYNYOH |
| MA | null |
| MD | MDMEMEMIMNMNMNMNMNMNMOMOMONCNDNDNENHNHNHNYNYNYOHOHORORPA |
| MD | null |
| ME | MEMIMNMNMNMNMNMNMOMOMONCNDNDNENHNHNHNYNYNYOHOHORORPARIRIRIRI |
| ME | null |
| MI | null |
| MN | MNMNMNMNMNMOMOMONCNDNDNENHNHNHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUT |
| MN | MNMNMNMNMOMOMONCNDNDNENHNHNHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUT |
| MN | MNMNMNMOMOMONCNDNDNENHNHNHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVT |
| MN | MNMNMOMOMONCNDNDNENHNHNHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVT |
| MN | MNMOMOMONCNDNDNENHNHNHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWI |
| MN | null |
| MO | MOMONCNDNDNENHNHNHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWY |
| MO | MONCNDNDNENHNHNHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| MO | null |
| NC | null |
| ND | NDNENHNHNHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| ND | null |
| NE | null |
| NH | NHNHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| NH | NHNYNYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| NH | null |
| NY | NYNYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| NY | NYOHOHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| NY | null |
| OH | OHORORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| OH | null |
| OR | ORPARIRIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| OR | null |
| PA | null |
| RI | RIRIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| RI | RIRISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| RI | RISCSCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| RI | null |
| SC | SCSDSDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| SC | null |
| SD | SDSDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| SD | SDUTUTUTVTVTVTVTWIWIWVWYWYWYWY |
| SD | null |
| UT | UTUTVTVTVTVTWIWIWVWYWYWYWY |
| UT | UTVTVTVTVTWIWIWVWYWYWYWY |
| UT | null |
| VT | VTVTVTWIWIWVWYWYWYWY |
| VT | VTVTWIWIWVWYWYWYWY |
| VT | VTWIWIWVWYWYWYWY |
| VT | null |
| WI | WIWVWYWYWYWY |
| WI | null |
| WV | null |
| WY | WYWYWY |
| WY | WYWY |
| WY | WY |
| WY | null |
+------+-----------+
78 rows selected (0.307 seconds)

Results returned by Postgres.

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

Issue Links

• is related to: DRILL-3606 Wrong results - Lead(char-column) without PARTITION BY clause