[
https://issues.apache.org/jira/browse/CALCITE-7096?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18007834#comment-18007834
]
Zhen Chen commented on CALCITE-7096:
------------------------------------
I submitted a [PR|https://github.com/apache/doris/pull/53415] to Doris. If this
PR is merged, Doris can support DOY and DOW.
> Invalid unparse for EXTRACT in StarRocks/Doris
> ----------------------------------------------
>
> Key: CALCITE-7096
> URL: https://issues.apache.org/jira/browse/CALCITE-7096
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.40.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.41.0
>
>
> starrocks sql:
> {code:java}
> select EXTRACT(HOUR FROM DATETIME '2023-12-01 00:00:00') {code}
> {code:java}
> com.starrocks.common.AnalysisException: Getting syntax error. Detail message:
> Parse Error : unknown type DATETIME.
> at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:477)
> ~[starrocks-fe.jar:?]
> at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:778)
> ~[starrocks-fe.jar:?]
> at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:1072)
> ~[starrocks-fe.jar:?]
> at
> com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69)
> ~[starrocks-fe.jar:?]
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
> ~[?:?]
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
> ~[?:?]
> at java.lang.Thread.run(Thread.java:833) ~[?:?]
> {code}
>
> not need any time unit such as date/datetime/timestamp
> {code:java}
> select EXTRACT(HOUR FROM '2023-12-01 12:00:00'){code}
> result is ok:
> {code:java}
> 12
> {code}
>
> in Doris also had problem:
> both StarRocks and Doris not support such sql
> {code:java}
> SELECT EXTRACT(DOY FROM '2023-12-01');
> SELECT EXTRACT(DOW FROM '2023-12-01'); {code}
> would error as:
> {code:java}
> com.starrocks.common.AnalysisException: Getting syntax error at line 1,
> column 71. Detail message: Unexpected input 'SELECT', the most similar input
> is {a legal identifier}.
> at
> com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:477)
> ~[starrocks-fe.jar:?]
> at
> com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:778)
> ~[starrocks-fe.jar:?]
> at
> com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:1072)
> ~[starrocks-fe.jar:?]
> at
> com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69)
> ~[starrocks-fe.jar:?]
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
> ~[?:?]
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
> ~[?:?]
> at java.lang.Thread.run(Thread.java:829) ~[?:?] {code}
> should convert to:
> {code:java}
> SELECT EXTRACT(DAYOFYEAR FROM '2023-12-01');
> SELECT EXTRACT(DAYOFWEEK FROM '2023-12-01'); {code}
> result is as expected:
> {code:java}
> 335 {code}
> {code:java}
> 6
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
