[
https://issues.apache.org/jira/browse/CALCITE-5307?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17614052#comment-17614052
]
Jiajun Xie commented on CALCITE-5307:
-------------------------------------
Not all function names will be quoted, there some conditions:
{code:java}
// 1. If the identifier is quoted or is component, unparse as normal.
// 2. If the identifier comes from a sql function, lookup in the
// standard sql operator table to see if the function is a builtin,
// unparse without quoting for builtins.
// 3. If the identifier does not come from a function(resolved as a
SqlIdentifier),
// look up in the standard sql operator table to see if it is a function
// with empty argument list, e.g. LOCALTIME, we should not quote
// such identifier cause quoted `LOCALTIME` always represents a sql identifier.
{code}
You can put `ADDDATE` into your SqlOperatorTable to avoid quotation marks.
---
If you can accept all column name not be quoted, you can refer to the following
email
https://lists.apache.org/[email protected]:2022-5:SqlOperatorTable%20custom
> Quoting of functions interferes with MySQL execution
> ----------------------------------------------------
>
> Key: CALCITE-5307
> URL: https://issues.apache.org/jira/browse/CALCITE-5307
> Project: Calcite
> Issue Type: Bug
> Components: babel
> Environment: calcite 1.32.0
> MySQL 5.7.14
> Reporter: Dirk
> Priority: Major
>
> When parsing a SQL statement with a function call and then translating this
> back to SQL, calcite puts the functions name in quotes.
> For example with this code snippet
> {quote} String sql = "SELECT ADDDATE('2008-01-02', 31)";
> SqlParser sqlParser = SqlParser.create(sql, SqlParser.config());
> SqlSelect sqlSelect = (SqlSelect) sqlParser.parseQuery();
> System.out.println("MysqlSqlDialect SQL with default config: " +
> sqlSelect.toSqlString(MysqlSqlDialect.DEFAULT));
> System.out.println("PostgresqlSqlDialect SQL with default config: " +
> sqlSelect.toSqlString(PostgresqlSqlDialect.DEFAULT));
> {quote}
> The output is
> {quote}MysqlSqlDialect SQL with default config: SELECT
> `ADDDATE`('2008-01-02', 31)
> PostgresqlSqlDialect SQL with default config: SELECT "ADDDATE"('2008-01-02',
> 31)
> {quote}
> Showing the ADDDATE function quoted in the dialect that is specified. In
> MySQL this is backticks.
> However executing this on MySQL gives us:
> {quote}mysql> SELECT ADDDATE('2008-01-02', 31);
> +---------------------------+
> | ADDDATE('2008-01-02', 31) |
> +---------------------------+
> | 2008-02-02 |
> +---------------------------+
> 1 row in set (0.00 sec)
> mysql> SELECT `ADDDATE`('2008-01-02', 31);
> ERROR 1046 (3D000): No database selected
> {quote}
> This is because in MySQL ADDDATE is an intrinsic function (it is part of the
> MySQL grammar ( e..g
> https://github.com/twitter-forks/mysql/blob/master/sql/sql_yacc.yy ) which
> does not allow for quoting.
> There are probably 2 dozen MySQL functions that cannot be used because of
> this. In some cases there are workarounds, but in other cases there are not.
> This also applies to hints like "SELECT /*+ MAX_EXECUTION_TIME(200) */" which
> get quoted and then ignored by MySQL.
> Would it be possible to change the quoting behavior to not add any quotes for
> functions and hints if the parser input SQL does not contain it?
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
