[jira] [Comment Edited] (CALCITE-5307) Quoting of functions interferes with MySQL execution

["Dirk (Jira)" <ji...@apache.org>]

    [ 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)