[
https://issues.apache.org/jira/browse/CALCITE-5164?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17541134#comment-17541134
]
Julian Hyde commented on CALCITE-5164:
--------------------------------------
As we all know, Standard SQL allows "typename characterLiteral" for many types
(e.g. "DATE '2022-05-23'").
But PostgreSQL also allows "typename(expression)" for many types, for example
"DATE('2022-' || '05-23')". "typename (expression)" is syntactic sugar for to
"CAST(value AS type)", as described in [Postgres 4.9.2 Type
casts|https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS].
The section notes a few limitations:
{quote}
It is also possible to specify a type cast using a function-like syntax:
typename ( expression )
However, this only works for types whose names are also valid as function
names. For example, double precision cannot be used this way, but the
equivalent float8 can. Also, the names interval, time, and timestamp can only
be used in this fashion if they are double-quoted, because of syntactic
conflicts. Therefore, the use of the function-like cast syntax leads to
inconsistencies and should probably be avoided.
{quote}
Would it make sense for Calcite to have similar 'constructor functions' for
types including {{{}BOOLEAN{}}}, {{{}DATE{}}}, {{{}TIME{}}}, {{{}TIMESTAMP{}}},
{{{}INTERVAL{}}}, {{{}ARRAY{}}}, {{{}MULTISET{}}}, {{{}TINYINT{}}},
{{{}SMALLINT{}}}, {{{}INTEGER{}}}, {{{}BIGINT{}}}, {{{}FLOAT{}}}, {{{}REAL{}}},
{{{}DOUBLE{}}}, {{{}GEOMETRY{}}} applied to character expressions. (Have I
missed any?)
{{{}INTERVAL{}}}, {{{}ARRAY{}}}, {{{}MULTISET{}}}, {{{}TIMESTAMP{}}},
{{{}TIME{}}}, {{DECIMAL}} would perhaps only work on literals, not expressions,
because the type (e.g. {{{}INTEGER ARRAY{}}}, {{{}TIMESTAMP(3){}}}) must be
deduced by parsing the string.
We should also support the two-argument form of {{{}TIMESTAMP{}}}.
> Planner#parser can't parse TIMESTAMP() function
> -----------------------------------------------
>
> Key: CALCITE-5164
> URL: https://issues.apache.org/jira/browse/CALCITE-5164
> Project: Calcite
> Issue Type: Bug
> Components: babel
> Affects Versions: 1.30.0
> Reporter: Jiajun Xie
> Assignee: Jiajun Xie
> Priority: Minor
>
> Both core and babel will parse fail.
> {code:java}
> FrameworkConfig coreConfig = Frameworks.newConfigBuilder().build();
> Planner corePlanner = Frameworks.getPlanner(coreConfig);
> corePlanner.parse("SELECT TIMESTAMP('2022-05-21 08:00:00'");
> // Caused by: org.apache.calcite.sql.parser.babel.ParseException:
> Incorrect syntax near the keyword 'TIMESTAMP' at line 1, column 8.
> FrameworkConfig babelConfig = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.Config.DEFAULT.withParserFactory(
> SqlBabelParserImpl.FACTORY))
> .build();
> Planner babelPlanner = Frameworks.getPlanner(babelConfig);
> babelPlanner.parse("SELECT TIMESTAMP('2022-05-21 08:00:00'");
> // Caused by: org.apache.calcite.sql.parser.babel.ParseException:
> Incorrect syntax near the keyword 'TIMESTAMP' at line 1, column 8
> {code}
> Here are some databases that support TIMESTAMP function.
> - MySQL:
> [https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestamp]
> {code:java}
> select timestamp('2022-05-21 08:00:00')
> // result
> timestamp('2022-05-21 08:00:00')
> 2022-05-21 08:00:00
> {code}
> - Derby: [https://docs.oracle.com/javadb/10.6.2.1/ref/rreftimestampfunc.html]
> Also, here are some databases that not support TIMESTAMP function:
> - Oracle:
> [https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm]
> {code:java}
> select timestamp('2022-05-21 08:00:00')
> //ORA-00923: FROM keyword not found where expected
> {code}
> - SQL Server:
> [https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver15]
> {code:java}
> select timestamp('2022-05-21 08:00:00')
> // Msg 195 Level 15 State 10 Line 1
> // 'timestamp' is not a recognized built-in function name.{code}
> Is it necessary for us to support it in babel module?
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
