Compatibility with other SQL dialects

[Julian Hyde <jh...@apache.org>]

Crazy as it may sound, not everyone has a copy of the SQL standard to read before they go to bed, nor even wants to write standard SQL. Most people learned their SQL against another database, maybe MySQL or Oracle, and got used to the quirks of that dialect, and its the set of built-in operators. They would like to write Oracle SQL and have it run against Calcite unchanged.

As a framework for building databases, Calcite should be meeting those folks at half-way. Here’s how I think we should do it. We already have pluggable policies for whether the parser is case-sensitive and how to quote identifiers. There have been discussions[1] about how to make the type system pluggable. Now I’d like to start filling out the set of functions and operators we support.

I logged https://issues.apache.org/jira/browse/CALCITE-1066 <https://issues.apache.org/jira/browse/CALCITE-1066>, “Add an extension operator table with Oracle-specific operators”. The idea is that if someone logs into Calcite with “fun=oracle” as a parameter on their JDBC connect string, they will get the standard operators PLUS a collection of Oracle-specific operators, such as DECODE, NVL, LTRIM, GREATEST. We’ll keep standard SQL and its operator set as the core, but make it easy to enable an extended set of operators.

We should also support “fun=mysql”, “fun=“postgresql” and so forth for other databases.

“fun” is short for “function table”, but speaking of fun, implementing built-in functions might be a nice project if you are a new Calcite contributor. Operators such as LPAD, RADIANS, INSTR should be pretty easy. Take a look at CALCITE-1066 to see what operators are left, and add comments to that case if you want to work on some operators.

Julian

[1] https://mail-archives.apache.org/mod_mbox/calcite-dev/201601.mbox/%3CCABMBZ%3Dnr4yA-mBPuCavAc0TzQEdFh6qg115Ei3K2Qf3_5y0ewQ%40mail.gmail.com%3E <https://mail-archives.apache.org/mod_mbox/calcite-dev/201601.mbox/%3CCABMBZ=nr4yA-mBPuCavAc0TzQEdFh6qg115Ei3K2Qf3_5y0ewQ@mail.gmail.com%3E>