[CALCITE-2928] Make UDF lookup default to case insensitive

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.19.0
• Fix Version/s: 1.20.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Danny Chen
• Created: 18/Mar/19 02:13
• Updated: 24/Jun/19 15:03
• Resolved: 22/Apr/19 20:40

Description

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:

select f0, my_func(f1) from table1 group by f0;

We would got a unparsed sql:

SELECT F0, MY_FUNC(F1) FROM TABLE1 GROUP BY F0;

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

Issue Links

• Blocked: CALCITE-2960 CalciteCatalogReader use a hard-coding config to get functions
• is duplicated by: CALCITE-2960 CalciteCatalogReader use a hard-coding config to get functions
• links to: GitHub Pull Request #1137