cyrilou242 opened a new issue #8326:
URL: https://github.com/apache/pinot/issues/8326
## Problem observed:
Using `DATETIMECONVERT` udf with multiple days granularity (eg `5:DAYS`)
1. can break
2. returns incorrect values
## 1. Break
## How to reproduce:
dataset:
| dateCol |
|---|
| 20210201 |
| 20210202 |
| 20210203 |
```sql
Select
DATETIMECONVERT(dateCol,
'1:DAYS:SIMPLE_DATE_FORMAT:yyyyMMdd',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss',
'5:DAYS') // problem cause by this
```
Will throw:
```
[
{
"message":
"QueryExecutionError:\norg.joda.time.IllegalFieldValueException: Value 0 for
dayOfMonth must be in the range [1,28]\n\tat
org.joda.time.field.FieldUtils.verifyValueBounds(FieldUtils.java:259)\n\tat
org.joda.time.field.PreciseDurationDateTimeField.set(PreciseDurationDateTimeField.java:79)\n\tat
org.joda.time.DateTime.withDayOfMonth(DateTime.java:1782)\n\tat
org.apache.pinot.core.operator.transform.transformer.datetime.BaseDateTimeTransformer.lambda$new$4(BaseDateTimeTransformer.java:83)",
"errorCode": 200
}
]
```
Happens for days smaller than the granularity rounding. Happens for `DAYS`
rounding only.
Should be reproducible in code by implementing a test with a granularity of
`5:DAYS` and an input date with day smaller than 5. See cause below.
## 2. Returns incorrect values
Days bucket are incorrect.
For instance, with `5:DAYS` granularity, `2020-03-10` is rounded to
`2020-03-10`.
Because days start at one, rounding buckets for days are [1-5], [6-10],
etc...
`2020-03-10` should be rounded to `2020-03-06`.
## Cause:
In Joda, millis/seconds/minutes/hours values start at 0. days values start
at 1. This is not managed in the implementations.
https://github.com/apache/pinot/blob/ce2c367bffb988107d3baa3ca804ba955042ce59/pinot-core/src/main/java/org/apache/pinot/core/operator/transform/transformer/datetime/BaseDateTimeTransformer.java#L83
https://github.com/apache/pinot/blob/90d11365ae80ce2a232b1338326589cc3c00bbe7/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/DateTimeFunctions.java#L706
Tests are testing with day 25 so the problem is not catched.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
