[
https://issues.apache.org/jira/browse/CALCITE-7096?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18007847#comment-18007847
]
Yu Xu commented on CALCITE-7096:
--------------------------------
OK, I see you added an alias method to doris. It doesn't matter whether it is
merged or not. Considering the compatibility between the new and old versions,
it is best to follow the PR, because both the new and old versions of Doris
support dayofyear and dayofweek.
> 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)
