[
https://issues.apache.org/jira/browse/CALCITE-5307?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17616025#comment-17616025
]
Dirk edited comment on CALCITE-5307 at 10/11/22 6:45 PM:
---------------------------------------------------------
I found a workaround to the quoting in the hints. If I override the getSyntax
to return "FUNCTION_ID" , then the code in SqlUtil.unparseSqlIdentifierSyntax
won't add quotes.
Does this make sense as a solution?
{quote}{{ SqlFunction maxExecTime = new SqlFunction("MAX_EXECUTION_TIME",
SqlKind.SET_OPTION,}}
{{ ReturnTypes.INTEGER, null,}}
{{ OperandTypes.INTEGER, SqlFunctionCategory.SYSTEM)}}{{ \{
@Override public SqlSyntax getSyntax() { return
SqlSyntax.FUNCTION_ID; }}}{{ };}}{quote}
was (Author: JIRAUSER294299):
I found a workaround to the quoting in the hints. If I override the getSyntax
to return "FUNCTION_ID" , then the code in SqlUtil.unparseSqlIdentifierSyntax
won't add quotes.
Does this make sense as a solution?
{quote} SqlFunction maxExecTime = new SqlFunction("MAX_EXECUTION_TIME",
SqlKind.SET_OPTION,
ReturnTypes.INTEGER, null,
OperandTypes.INTEGER, SqlFunctionCategory.SYSTEM){
@Override public SqlSyntax getSyntax()
\{ return SqlSyntax.FUNCTION_ID; }
};
{quote}
> 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)
