[
https://issues.apache.org/jira/browse/CALCITE-2928?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16795601#comment-16795601
]
Danny Chan commented on CALCITE-2928:
-------------------------------------
[~julianhyde] I found these docs:
1. MYSQL [1]
2. ORACLE [2] [3]
Oracle is case-sensitive for database internal, but for unquoted identifier
Oracle will change them to uppercase silently, this is also what Calcite's
default behavior.
So for users, identifiers like <userName> and <USERNAME> are the same, which is
case-insensitive.
Now the default behavior for Calcite is in-consistent for ListOperatorTable,
the unquoted identifier is parsed to all uppercase but ListOperatorTable look
up operators case-sensitively. So if a use register a function with lowerCase
name, it will not work anymore.
[1] https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html
[2] https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements008.htm
[3]
https://stackoverflow.com/questions/7425153/reason-why-oracle-is-case-sensitive
> Make UDF lookup default to case insensitive
> -------------------------------------------
>
> Key: CALCITE-2928
> URL: https://issues.apache.org/jira/browse/CALCITE-2928
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.19.0
> Reporter: Danny Chan
> Assignee: Danny Chan
> Priority: Major
> Fix For: 1.19.0
>
>
> Now for Calcite, we make default parser config unquotedCasing to
> Lex.ORACLE.unquotedCasing(to uppercase)[1], and caseSensitive to
> Lex.ORACLE.caseSensitive(case sensitive true).
> So if we have a UDAF named my_func and query with sql like:
> {code:java}
> select f0, my_func(f1) from table1 group by f0;
> {code}
> We would got a unparsed sql:
> {code:java}
> SELECT F0, MY_FUNC(F1) FROM TABLE1 GROUP BY F0;
> {code}
> For CalciteCatalogReader we hard code the function look up to case sensitive
> true[2],
> For ListSqlOperatorTable we make the operator name lookup case sensitive
> true[3].
> For ReflectiveSqlOperatorTable, we make built-in operators
> case-insensitively[4].
> For most of the cases, we use ListSqlOperatorTable to register our UDFs[5]
> chained with SqlStdOperatorTable(which composite a ChainedSqlOperatorTable),
> which finally passed to CalciteCatalogReader for validation.
> So there are some questions i have:
> 1. Why we make built-in operators look up case-insensitively while
> ListSqlOperatorTable(for UDFs) case-sensitively, with default unquotedCasing
> of TO_UPPERCASE.
> 2. What is the usage of CalciteCatalogReader#lookupOperatorOverloads i only
> saw it used in a unit test LookupOperatorOverloadsTest.
> It seems that make UDF look up case-sensitively does not make any sense,
> users will never distinguish their function with just word cases. And i
> checked also MYSQL, ORACLE, POSTGRES, their UDFs are all registered
> case-insensitively.
> [1]
> https://github.com/apache/calcite/blob/ffca956be03a99cd11e440d652b09674aaa727e6/core/src/main/java/org/apache/calcite/sql/parser/SqlParser.java#L231
> [2]
> https://github.com/apache/calcite/blob/ffca956be03a99cd11e440d652b09674aaa727e6/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java#L166
> [3]
> https://github.com/apache/calcite/blob/ffca956be03a99cd11e440d652b09674aaa727e6/core/src/main/java/org/apache/calcite/sql/util/ListSqlOperatorTable.java#L63
> [4]
> https://github.com/apache/calcite/blob/ffca956be03a99cd11e440d652b09674aaa727e6/core/src/main/java/org/apache/calcite/sql/util/ReflectiveSqlOperatorTable.java#L103
> [5]
> https://github.com/apache/calcite/blob/ffca956be03a99cd11e440d652b09674aaa727e6/core/src/test/java/org/apache/calcite/test/MockSqlOperatorTable.java#L46
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
