hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alexander Pivovarov" <apivova...@gmail.com>
Subject Re: Review Request 32489: HIVE-9518 Implement MONTHS_BETWEEN aligned with Oracle one
Date Fri, 27 Mar 2015 00:39:44 GMT


> On March 26, 2015, 6:14 p.m., Mohit Sabharwal wrote:
> > ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFMonthsBetween.java,
line 50
> > <https://reviews.apache.org/r/32489/diff/2/?file=906532#file906532line50>
> >
> >     Consider the example where date1 is 1997-02-28 and date2 is 1996-10-30. In this
case the monBtw will be -8, i.e. negative, correct ? But the doc states it should be positive.

> >     
> >     Also, I don't see such a case addressed in the unit tests.
> >     
> >     Also, could you explain/add comment about why last day of the month is significant.
Shouldn't we be computing denominator based on getAcutalMaximum of the two dates instead of
a fixed "31" or is that the Oracle behaviour ?
> 
> Alexander Pivovarov wrote:
>     date1 1997-02-28 is later than date2 1996-10-30 and the result is positive (Hive:
3.93548387, Oracle: 3.93548)
>     
>     if we swap 1997-02-28 and 1996-10-30 than 
>     date1 1996-10-30 is earlier than date2 1997-02-28 and the result is negative (Hive:
-3.93548387, Oracle: -3.93548)
>     
>     Oracle query:
>     select months_between('28-FEB-1997', '30-OCT-1996'), months_between('30-OCT-1996','28-FEB-1997')
from dual;
>     
>     Hive query:
>     select months_between('1997-02-28', '1996-10-30'), months_between('1996-10-30','1997-02-28');
>     
>     Oracle doc says that Oracle calculates the fractional portion of the result based
on a 31-day month.
>     
>     Found one bug. Oracle considers the difference in time components date1 and date2
but Hive skips date1 and date2 time part.
>     e.g.
>     Oracle
>     select months_between(to_date('1997-02-28 10:30:00', 'YYYY-MM-DD HH24:mi:ss'), '30-OCT-1996')
from dual;
>     3.9496
>     
>     Hive:
>     select months_between('1997-02-28 10:30:00', '1996-10-30');
>     OK
>     3.93548387
>     
>     I'll fix it.

actually oracle returns 3.94959677419354838709677419354838709677 for 
select cast(months_between(to_date('1997-02-28 10:30:00', 'YYYY-MM-DD HH24:mi:ss'), '30-OCT-1996')
as varchar(100)) from dual;


- Alexander


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/32489/#review77920
-----------------------------------------------------------


On March 26, 2015, 7:37 a.m., Alexander Pivovarov wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/32489/
> -----------------------------------------------------------
> 
> (Updated March 26, 2015, 7:37 a.m.)
> 
> 
> Review request for hive and Jason Dere.
> 
> 
> Bugs: HIVE-9518
>     https://issues.apache.org/jira/browse/HIVE-9518
> 
> 
> Repository: hive-git
> 
> 
> Description
> -------
> 
> HIVE-9518 Implement MONTHS_BETWEEN aligned with Oracle one
> 
> 
> Diffs
> -----
> 
>   ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 2ba7adf2e6fc8a184c9d1619619dff2f0870528b

>   ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFMonthsBetween.java PRE-CREATION

>   ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFMonthsBetween.java
PRE-CREATION 
>   ql/src/test/queries/clientpositive/udf_months_between.q PRE-CREATION 
>   ql/src/test/results/clientpositive/show_functions.q.out 81abeb9be4fd47724be544c7bc8da8b25fcd6e75

>   ql/src/test/results/clientpositive/udf_months_between.q.out PRE-CREATION 
> 
> Diff: https://reviews.apache.org/r/32489/diff/
> 
> 
> Testing
> -------
> 
> 
> Thanks,
> 
> Alexander Pivovarov
> 
>


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message