Dirk created CALCITE-5307:
-----------------------------
Summary: 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
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)
