Re: SQL dialects supported by Calcite
My humble opinion here is that we should strive for one and only dialect. I understand concerns about supporting a backward compatible dialect. But I personally do not know whether it is practical with Calcite. Regarding multiples dialects my forecast is that supporting multiple dialects close by meaning to "drop-in replacement for nothing". пн, 30 дек. 2019 г. в 16:40, Ilya Kasnacheev : > > Hello! > > I think we may eventually need at least two dialects. > > One is "H2 compatible" for users migrating from older versions of AI. We > may need to add a few tweaks there to make most of existing SQL code work. > Other is "modern" in which we will remove these tweaks to get the best, > vanilla Ignite+Calcite SQL experience. > > I don't think we need "Oracle dialect", this will probably create confusion > as well as testing burden. > > Regards, > -- > Ilya Kasnacheev > > > сб, 28 дек. 2019 г. в 11:02, Seliverstov Igor : > > > 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 : > > > > > 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 > > > 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 > > написал(а): > > > > > > > > > > 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 > > > > > > > > > > > > > -- Best regards, Ivan Pavlukhin
Re: SQL dialects supported by Calcite
Hello! I think we may eventually need at least two dialects. One is "H2 compatible" for users migrating from older versions of AI. We may need to add a few tweaks there to make most of existing SQL code work. Other is "modern" in which we will remove these tweaks to get the best, vanilla Ignite+Calcite SQL experience. I don't think we need "Oracle dialect", this will probably create confusion as well as testing burden. Regards, -- Ilya Kasnacheev сб, 28 дек. 2019 г. в 11:02, Seliverstov Igor : > 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 : > > > 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 > > 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 > написал(а): > > > > > > > > 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 > > > > > > > > >
Re: SQL dialects supported by Calcite
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 : > >> 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 : >> >>> 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 >>> 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 написал(а): > > 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 >>> >> >
Re: SQL dialects supported by Calcite
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 : > 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 : > >> 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 >> 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 написал(а): >> > > >> > > 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 >> > >> > >> >
Re: SQL dialects supported by Calcite
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 : > 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 > 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 написал(а): > > > > > > 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 > > > > >
Re: SQL dialects supported by Calcite
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 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 написал(а): > > > > 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 > >
Re: SQL dialects supported by Calcite
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 написал(а): > > 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
SQL dialects supported by Calcite
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
