date_part returning wrong results due to overflows #14738

Activity

1. gabotechs

   added

   bugSomething isn't workingSomething isn't working

   on Feb 18, 2025
2. gabotechs commented on Feb 18, 2025

   

   gabotechs

   on Feb 18, 2025

   ContributorAuthor

   More actions

   It can also be replicated with intervals:

   SELECT date_part('microseconds', interval '1 hour')
   -- returns -694967296, but the result should be 0
3. gabotechs commented on Feb 18, 2025

   

   gabotechs

   on Feb 18, 2025

   ContributorAuthor

   More actions

   It seems like even without integer overflows, the overall logic is wrong:

   SELECT date_part('seconds', interval '1 hour')
   -- returns 3600, but the result should be 0
   
4. Omega359 commented on Feb 18, 2025

   

   Omega359

   on Feb 18, 2025

   Contributor

   More actions

   It seems like even without integer overflows, the overall logic is wrong:

   SELECT date_part('seconds', interval '1 hour')
   -- returns 3600, but the result should be 0
   

   Hmm. I think this may be a separate issue as indeed it doesn't follow the typical pattern for that function where

   SELECT date_part('seconds', interval '1 hour 5 second');

   should return 5 (the seconds element in the interval, not the overall seconds the interval covers)
5. alamb commented on Mar 3, 2026

   

   alamb

   on Mar 3, 2026

   Contributor

   More actions

   Appears to still be a problem

   andrewlamb@Andrews-MacBook-Pro-3:~/Software/arrow-rs$ datafusion-cli
   DataFusion CLI v52.1.0
   > SELECT date_part('microsecond', timestamp '1970-01-01T00:40:00' - timestamp '1970-01-01T00:00:00');
   +------------------------------------------------------------------------------------------+
   | date_part(Utf8("microsecond"),Utf8("1970-01-01T00:40:00") - Utf8("1970-01-01T00:00:00")) |
   +------------------------------------------------------------------------------------------+
   | -1894967296                                                                              |
   +------------------------------------------------------------------------------------------+
   1 row(s) fetched.
   Elapsed 0.040 seconds.
6. buraksenn commented on Apr 13, 2026

   

   buraksenn

   on Apr 13, 2026

   Contributor

   More actions

   I gave this a look and was able to fix it by casting duration array to nanosecond and then extracting components. However, it seemed like a fragile workaround so I was not sure of opening a PR.

   Smth like this:

   fn duration_part(array: &dyn Array, unit: IntervalUnit) -> Result<ArrayRef> {
       const NANOS_PER_MINUTE: i64 = 60 * 1_000_000_000;
   
   
       let divisor = match unit {
           Second => 1_000_000_000,
           Millisecond => 1_000_000,
           Microsecond => 1_000,
           Nanosecond => 1,
       };
   
       let nanos_array = cast(array, &Duration(Nanosecond))?;
       let result = nanos_array.unary(|d| (d % NANOS_PER_MINUTE) / divisor);
   
       Ok(Arc::new(result))
   }
   

   Should this be solved in https://github.com/apache/arrow-rs? Otherwise I can open a PR with the approach above
7. alamb commented on Apr 13, 2026

   

   alamb

   on Apr 13, 2026

   Contributor

   More actions

   Should this be solved in https://github.com/apache/arrow-rs?

   Yes I think that is probably the right approach
8. Nisarg0007 commented on Sep 9, 2026

   

   Nisarg0007

   on Sep 9, 2026

   More actions

   I'd like to work on this. I reproduced the issue on v55.0.0:

   SELECT date_part(
       'microsecond',
       timestamp '1970-01-01T00:40:00'
       - timestamp '1970-01-01T00:00:00'
   );

   This currently panics in debug builds at date_part.rs:426 with attempt to multiply with overflow; release builds silently wrap and return an incorrect value.

   I also confirmed that the interval-semantics portion of this issue was fixed by #14817, so I intend to target only the remaining Duration path.

   My current plan is to perform the subsecond calculation using i64 internally while preserving the existing Int32 return type. For values outside the Int32 range, should this return an error or saturate/clamp?

   @alamb — you previously suggested the duration-part work might belong in arrow-rs. My proposed change is limited to fixing the existing i32 arithmetic in DataFusion's date_part.rs rather than introducing an upstream kernel. Would a local fix be appropriate here?

   If this approach sounds good, I'd be happy to take this issue.
9. mbutrovich commented on Oct 23, 2026

   

   mbutrovich

   on Oct 23, 2026

   Contributor

   More actions

   I also confirmed that the interval-semantics portion of this issue was fixed by #14817, so I intend to target only the remaining Duration path.

   @Nisarg0007 are you still working on this? Interval also drops the fraction past i32::MAX nanoseconds, as Duration does:

   SELECT extract(millisecond from interval '62.5 seconds'),
          extract(millisecond from arrow_cast(2500000000, 'Duration(Nanosecond)'));
   -- 2000, 2000 (expected 2500, 2500)

   As I read it, this branch treats the overflowed nanoseconds as zero.

   Would a local fix be appropriate here?

   #26676 fixes the same overflow for EXTRACT(EPOCH) in date_part.rs by computing from the i64 value. Should second, millisecond and microsecond do the same?
10. mbutrovich

    self-assigned this

    on Oct 23, 2026
11. mbutrovich

    changed the title [-]date_part returning wrong results due to overflows[/-] [+]`date_part` returning wrong results due to overflows[/+] on Oct 23, 2026
12. mbutrovich commented on Oct 24, 2026

    

    mbutrovich

    on Oct 24, 2026

    Contributor

    More actions

    #26686 removes the wrapped value and the debug panic, but it doesn't give the Postgres result for the reproducer:

    SELECT date_part('microsecond', timestamp '1970-01-01T00:40:00' - timestamp '1970-01-01T00:00:00');
    -- NULL

    In Postgres the difference is an interval, so microsecond is the seconds field within the minute (0 here). In DataFusion it is a Duration, and #26686 treats millisecond, microsecond and nanosecond of a Duration as its total length, the way arrow's date_part treats the other Duration parts. 2.4e9 microseconds don't fit in the Int32 result, so the result is NULL. That makes any Duration over about 35.8 minutes NULL for microsecond, and over about 24.8 days for millisecond.

    On the dev list, @alamb agreed that NULL is fine for now and suggested following Postgres where we can. Postgres has no Duration type, so following it here means giving Duration the interval meaning: seconds within the minute, hours within the day. That would change every Duration part, not only the sub-second ones, because arrow's date_part returns the total for hour, minute and second as well. Should Duration parts take the interval meaning, or keep the total length?