Hi all! Calcite can parse queries using a couple of dozens built-in SQL dialects [1]. But as I can see it doesn't mean that Calcite's PG or Oracle dialects are fully compliant with original PG or Oracle dialects. Calcite's dialect contains only the main features of the original SQL dialect like default nulls direction, quoting chars or support for some built-in functions like "::" for PG or "TO_BASE64" for MySql [2].
IMO the main challenge here is to get to the understanding of the limitations for each Calcite's dialect. If we claim that we have a full support for PG dialect but then it turns out that we do not support most or some of the basic PG functions, it will not be very good. So at first we need to decide which dialects we are going to support and then define all limitations for each chosen dialect in our documentation. It's a lot of work. We need to start from the one or two dialects and then add other if needed. >From my point of view the SQL dialect should be configured once at the cluster start. I can not imagine the situations where client application might want to switch dialect when running. If you know such examples, I would be glad if you share them. Anyway, Calcite supports dialect switch on per-query granularity, so we always can add dialect switching on each granularity level: - per query - per session - per node - cluster wide [1] https://github.com/apache/calcite/blob/34af9ec1f6066d78a9d8534d8c2531724ac6b127/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L1233 [2] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java -- Kind Regards Roman Kondakov On 28.12.2019 11:08, Seliverstov Igor wrote: > Forgot to mention, it's better to set chosen dialect per session, for > example by a command > > "alter session sed dialect=ORACLE" > > Or a part of connection url. > > Global configuration parameter isn't a good choice. > > сб, 28 дек. 2019 г., 11:02 Seliverstov Igor <gvvinbl...@gmail.com>: > >> I guess you are thinking about transparent migration from Oracle for >> example. >> >> From user perspective it's really cool, but we will be forced to maintain >> all these dialects and fully test them. Also I heard about several >> inconsistencies between how it should and how it actually works. All these >> issues become ours if we declare several dialects support. >> >> So, I think multi dialects support will bring more troubles than benefits. >> >> Regards, >> Igor >> >> сб, 28 дек. 2019 г., 0:42 Denis Magda <dma...@apache.org>: >> >>> Igor, >>> >>> When you are saying that we should not allow the dialect changing, are you >>> referring to changes in runtime when a node is already up-and-running? >>> Generally, it will be more than enough if a dialect can be set statically >>> and globally -- the user selects the dialect for the entire cluster via a >>> configuration parameter and starts the nodes after that. >>> >>> - >>> Denis >>> >>> >>> On Fri, Dec 27, 2019 at 7:05 AM Seliverstov Igor <gvvinbl...@gmail.com> >>> wrote: >>> >>>> Denis, >>>> >>>>> Is it true that Calcite can parse MySQL, Oracle >>>> >>>> Yes, it can. >>>> >>>>> Do I need to select a dialect globally or can it be set on a per-query >>>>> level? >>>> >>>> Technically a parser, a validator, a planner, other components are >>> created >>>> and configured for each query call (because they are stateful). So you >>> may >>>> configure it per query, or hardcode a desired dialect, or get it from >>>> configuration - it’s up to you, but we expect parser configuration to be >>>> (more or less) static, it is a part of initial framework configuration. >>> I >>>> think we should not allow such parameters (as a dialect) changing. >>>> >>>> Regards, >>>> Igor >>>> >>>>> 27 дек. 2019 г., в 07:47, Denis Magda <dma...@apache.org> написал(а): >>>>> >>>>> Igor S., Roman and others who involved in Calcite prototyping, >>>>> >>>>> Is it true that Calcite can parse MySQL, Oracle, ANSI-99 and other >>>> dialects >>>>> as suggested by this page? >>>>> >>>> >>> https://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformanceEnum.html >>>>> >>>>> Do I need to select a dialect globally or can it be set on a per-query >>>>> level? >>>>> >>>>> - >>>>> Denis >>>> >>>> >>> >> >
