> 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
>
>
