[
https://issues.apache.org/jira/browse/CALCITE-5307?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17615984#comment-17615984
]
Dirk commented on CALCITE-5307:
-------------------------------
[~jiajunbernoulli] thanks for the response.
Unfortunately I can't turn off all quoting, because some column names we see
are MySQL identifiers.
I tried your suggestion to add the function to the operator table. That works
for functions, but not for hints. Here's what I've tried as a test (signatures
are not correct in all cases, but I don't think we need it for our use case):
{quote} SqlFunction addDateFunction = new SqlFunction("ADDDATE",
SqlKind.TIMESTAMP_ADD,
ReturnTypes.DATE, null,
OperandTypes.DATE, SqlFunctionCategory.TIMEDATE);
SqlFunction maxExecTime = new SqlFunction("MAX_EXECUTION_TIME",
SqlKind.SET_OPTION,
ReturnTypes.INTEGER, null,
OperandTypes.INTEGER, SqlFunctionCategory.SYSTEM);
operatorTable.register(addDateFunction);
operatorTable.register(maxExecTime);
String sql = "SELECT /*+ MAX_EXECUTION_TIME(200) */
MAX_EXECUTION_TIME(300), 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}
What I'm getting as output is this. "ADDDATE" usage looks good.
"MAX_EXECUTION_TIME" as function looks good (but this isn't how it can't be
used), but "MAX_EXECUTION_TIME" as hint is still quoted.
{quote}MysqlSqlDialect SQL with default config: SELECT
/*+ `MAX_EXECUTION_TIME`(200) */
MAX_EXECUTION_TIME(300), ADDDATE('2008-01-02', 31)
PostgresqlSqlDialect SQL with default config: SELECT
/*+ "MAX_EXECUTION_TIME"(200) */
MAX_EXECUTION_TIME(300), ADDDATE('2008-01-02', 31)
{quote}
It would be great if you can suggest a workaround for both the hint and
functions. Thanks!
> 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)
