You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by GitBox <gi...@apache.org> on 2022/06/22 21:41:46 UTC
[GitHub] [pinot] xuhui-stripe opened a new issue, #8955: Some timezone id will cause query to fail
xuhui-stripe opened a new issue, #8955:
URL: https://github.com/apache/pinot/issues/8955
We noticed issues recently that are related to `datetime_convert`:
----
Issue1:
Following query will fail when zoneid is specified as `EST `:
```
select
cast(
date_trunc(
'month',
datetime_convert(
datetime_convert(
date_timestamp ,
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(UTC)',
'1:MILLISECONDS'
),
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(EST)',
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS'
) ,
'MILLISECONDS',
'EST'
) as timestamp
) as start_time,
lastwithtime("value", date_timestamp, 'LONG') as total,
'All' as group_name
from some_table
```
with error:
`java.lang.IllegalArgumentException: Timezone EST not supported`
But EST is clearly existed in jida time format provided in pinot [doc](https://docs.pinot.apache.org/configuration-reference/functions/timezoneminute) And if I change above timezone id to something else (like 'America/Toronto' or 'EST5EDT'), the query will work.
Could you let us know that what might caused it. (We found out this might be related to java.time where three zone ids are commented out.)
----
Issue2:
Following query will fail when zoneid is specified as `Asia/Gaza` (in format of SIMPLE_DATE_FORMAT) for datetime_convert functions:
```
select
cast(
date_trunc(
'day',
datetime_convert(
datetime_convert(
date_timestamp,
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(UTC)',
'1:MILLISECONDS'
),
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(Asia/Gaza)',
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS'
),
'MILLISECONDS',
'Asia/Gaza'
) as timestamp
) as start_time,
lastwithtime("value", date_timestamp, 'LONG') as total,
'All' as group_name
```
with error:
```
"errorCode": 200,
"message": "QueryExecutionError:\nProcessingException(errorCode:450, message:InternalError:\njava.lang.NullPointerException\n\tat org.apache.pinot.core.operator.combine.GroupByOrderByCombineOperator.mergeResults(GroupByOrderByCombineOperator.java:238)\n\tat org.apache.pinot.core.operator.combine.BaseCombineOperator.getNextBlock(BaseCombineOperator.java:120)\n\tat org.apache.pinot.core.operator.combine.BaseCombineOperator.getNextBlock(BaseCombineOperator.java:51)"
```
But `Asia/Gaza` is clearly existed in SIMPLE_DATE_FORMAT mentioned in pinot [doc](https://docs.pinot.apache.org/configuration-reference/functions/datetimeconvert). And if I change 'Asia/Gaza' to `Asia/Nicosia` the query will work. I also found a list of timezone ids, both ids existed in the list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
Could you let us know why queries are failing for some zone id keywords? Appreciate the help! Thanks.
--
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-unsubscribe@pinot.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] Jackie-Jiang commented on issue #8955: Some timezone ids will cause query to fail
Posted by GitBox <gi...@apache.org>.
Jackie-Jiang commented on issue #8955:
URL: https://github.com/apache/pinot/issues/8955#issuecomment-1164028256
For the first issue, the problem is actually within the `date_trunc` function, which takes a `zone-index.properties` file, where `EST` is commented out. Seems we are trying to match the Presto behavior for this function, and it is not consistent with the `DateTimeZone` supported by joda. cc @xiangfu0
For the second issue, the exception is thrown from the group-by combine phase. We might want to try the same query without group-by to help debugging the issue.
--
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-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] xuhui-stripe closed issue #8955: Some timezone ids will cause query to fail
Posted by GitBox <gi...@apache.org>.
xuhui-stripe closed issue #8955: Some timezone ids will cause query to fail
URL: https://github.com/apache/pinot/issues/8955
--
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-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] xuhui-stripe commented on issue #8955: Some timezone ids will cause query to fail
Posted by GitBox <gi...@apache.org>.
xuhui-stripe commented on issue #8955:
URL: https://github.com/apache/pinot/issues/8955#issuecomment-1165040279
Thanks for the response! I tried on the second issue after removed some unimportant factors, now I am getting error:
```
[
{
"errorCode": 200,
"message": "QueryExecutionError:\norg.joda.time.IllegalInstantException: Cannot parse \"2020-03-27\": Illegal instant due to time zone offset transition (Asia/Gaza)\n\tat org.joda.time.format.DateTimeParserBucket.computeMillis(DateTimeParserBucket.java:473)\n\tat org.joda.time.format.DateTimeParserBucket.doParseMillis(DateTimeParserBucket.java:182)\n\tat org.joda.time.format.DateTimeFormatter.parseMillis(DateTimeFormatter.java:826)\n\tat org.apache.pinot.core.operator.transform.transformer.datetime.BaseDateTimeTransformer.transformSDFToMillis(BaseDateTimeTransformer.java:96)"
}
]
```
For following query:
```
select
cast(
date_trunc(
'day',
datetime_convert(
datetime_convert(
date_timestamp,
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(UTC)',
'1:MILLISECONDS'
),
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(Asia/Gaza)',
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS'
),
'MILLISECONDS',
'Asia/Gaza'
) as timestamp
) as start_time
from some_table
```
If I change above query to:
```
select
cast(
date_trunc(
'day',
datetime_convert(
datetime_convert(
date_timestamp,
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(UTC)',
'1:MILLISECONDS'
),
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(Asia/Nicosia)',
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS'
),
'MILLISECONDS',
'Asia/Gaza'
) as timestamp
) as start_time
from prod_rdp_express_12h_rro_analytics_v_dev_precomputed_metrics_data
where
_viewing_merchant = 'acct_1G7L8oIrZ8DVyk6b'
order by 1
-- This arbitrarily large limit is needed because Pinot by default returns a limit of 10 entries.
limit 1000000
```
Difference is '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(**Asia/Nicosia**)', it will work.
--
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-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] xuhui-stripe commented on issue #8955: Some timezone ids will cause query to fail
Posted by GitBox <gi...@apache.org>.
xuhui-stripe commented on issue #8955:
URL: https://github.com/apache/pinot/issues/8955#issuecomment-1168997575
Just wondering is there a way to get all the timezones that are not supported/mismatched now so we can work around this? Thanks.
--
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-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] xuhui-stripe commented on issue #8955: Some timezone ids will cause query to fail
Posted by GitBox <gi...@apache.org>.
xuhui-stripe commented on issue #8955:
URL: https://github.com/apache/pinot/issues/8955#issuecomment-1223212094
We ended up add an extra layer to modify the income query to avoid having violating TZ ids for now~ Close this since the work around works for now.
--
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-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org