Can calcite translate Oracle SQL to MySQL

[Louis Hust <lo...@gmail.com>]

Hi all,

Can calcite translate oracle sql to mysql ?

Such as oracle: select * from t1 whre rownum < 10
To  mysql: select * from t1 limit 9;

Re: Can calcite translate Oracle SQL to MySQL

[Walaa Eldin Moustafa <wa...@gmail.com>]

Calcite can do this with some additional effort and Coral
<https://github.com/linkedin/coral> leverages Calcite to do exactly that,
although it currently supports Hive, Spark, and Trino conversions. There
are public APIs to extend to other dialects on the input and output sides.
There is also a UI to play with the translations and visualize them:
https://github.com/linkedin/coral#coral-service-ui. The intermediate
representation is optionally at AST level (SqlNode) or Logical Plan level
(RelNode).

Thanks,
Walaa.


On Mon, Nov 27, 2023 at 12:02 PM Gavin Ray <ra...@gmail.com> wrote:

> If you're looking for a "translate X-to-Y dialect of SQL" tool, jOOQ does
> this fairly well.
>
> What you have to do is parse the string of SQL into a jOOQ query AST, then
> render it using a different SQL dialect.
> You can experiment with the results of this online here:
>
> Format, pretty print, and translate your SQL from one dialect to another
> (jooq.org) <https://www.jooq.org/translate/>
>
> jOOQ requires a paid license for commercial SQL dialects like Oracle and
> MS-SQL, though.
>
> Hope this helps.
>
> On Sun, Nov 26, 2023 at 4:29 PM Julian Hyde <jh...@apache.org> wrote:
>
> > Creating a high-quality Oracle to MySQL translator is a large task.
> > Calcite doesn't do it. In fact there is only one way to do it: spend a
> > considerable effort writing a large suite of compliance tests. Once
> > you have built that suite, getting the translator to pass that suite
> > is relatively easy.
> >
> > I do believe that Calcite is a very good basis for that translator. We
> > have decomposed Calcite's functionality into areas such as lexical
> > differences (backticks versus double quotes around identifiers),
> > differences in type system (what does the TIMESTAMP type mean?),
> > differences in operator set (is there an NVL function?), and
> > differences in code generator (what syntax for LIMIT-OFFSET does this
> > DBMS support?). The net result is that when you fix a bug in your
> > Oracle-to-MySQL translator, you likely fix a bug in my
> > Oracle-to-Postgres translator, or at least you don't make it worse.
> >
> > Therefore we can pool our efforts. You just need to write that test
> > suite, because no one is going to write it for you.
> >
> > Julian
> >
> >
> > On Fri, Nov 24, 2023 at 3:05 PM Mihai Budiu <mb...@gmail.com> wrote:
> > >
> > > This is a complicated question.
> > >
> > > Calcite is a framework with many components which you can assemble to
> > build various translators, including source-to-source translators. But
> some
> > components make program transformations that assume a certain semantics,
> > which may not coincide with either Oracle or MySQL. For example, Calcite
> > assumes that division with zero will cause a runtime exception, while
> MySQL
> > and Oracle both assume that it returns NULL. This is part of the type
> > inference rules of Calcite, and it's a fairly basic component.
> > >
> > > So if you use Calcite for compiling arithmetic expressions with
> division
> > of non-nullable arguments, the result is non-nullable in Calcite, but
> > nullable in either of these frameworks. Moreover, if you use Calcite's
> > optimizer to simplify expressions that divide by 0, you may produce
> > programs that give you different results. (There are ways you can
> override
> > this behavior of Calcite if you really want to)
> > >
> > > This is just one example, but the problem with SQL dialects is that
> they
> > have very subtle different semantics, so it may be actually very
> difficult
> > to translate a program from one database dialect to another while
> > preserving bit-exact results. I am assuming that this is the goal:
> produce
> > the same results given the same input data and queries. If this is not
> the
> > goal, you have to specify what relationship you expect between the
> original
> > program and the translated program.
> > >
> > > There may be a nice well-behaved subset of SQL on which this is
> > possible, but I am very skeptical, since the SQL standard does not even
> > specify the meaning of very basic operations, like how rounding is done
> > when converting decimals to integers.
> > >
> > > Mihai
> > >
> > > ________________________________
> > > From: Julian Hyde <jh...@apache.org>
> > > Sent: Friday, November 24, 2023 1:39 PM
> > > To: dev@calcite.apache.org <de...@calcite.apache.org>
> > > Subject: Re: Can calcite translate Oracle SQL to MySQL
> > >
> > > In general, yes. But I don't think there's any code to handle Oracle's
> > > ROWNUM system column. Please log a jira case requesting to translate
> > > ROWNUM.
> > >
> > > Cases https://issues.apache.org/jira/browse/CALCITE-5723 and
> > > https://issues.apache.org/jira/browse/CALCITE-1786 are related, but
> > > not what you are asking for.
> > >
> > > On Fri, Nov 24, 2023 at 12:40 AM Louis Hust <lo...@gmail.com>
> > wrote:
> > > >
> > > > Hi all,
> > > >
> > > > Can calcite translate oracle sql to mysql ?
> > > >
> > > > Such as oracle: select * from t1 whre rownum < 10
> > > > To  mysql: select * from t1 limit 9;
> >
>

Re: Can calcite translate Oracle SQL to MySQL

[Gavin Ray <ra...@gmail.com>]

If you're looking for a "translate X-to-Y dialect of SQL" tool, jOOQ does
this fairly well.

What you have to do is parse the string of SQL into a jOOQ query AST, then
render it using a different SQL dialect.
You can experiment with the results of this online here:

Format, pretty print, and translate your SQL from one dialect to another
(jooq.org) <https://www.jooq.org/translate/>

jOOQ requires a paid license for commercial SQL dialects like Oracle and
MS-SQL, though.

Hope this helps.

On Sun, Nov 26, 2023 at 4:29 PM Julian Hyde <jh...@apache.org> wrote:

> Creating a high-quality Oracle to MySQL translator is a large task.
> Calcite doesn't do it. In fact there is only one way to do it: spend a
> considerable effort writing a large suite of compliance tests. Once
> you have built that suite, getting the translator to pass that suite
> is relatively easy.
>
> I do believe that Calcite is a very good basis for that translator. We
> have decomposed Calcite's functionality into areas such as lexical
> differences (backticks versus double quotes around identifiers),
> differences in type system (what does the TIMESTAMP type mean?),
> differences in operator set (is there an NVL function?), and
> differences in code generator (what syntax for LIMIT-OFFSET does this
> DBMS support?). The net result is that when you fix a bug in your
> Oracle-to-MySQL translator, you likely fix a bug in my
> Oracle-to-Postgres translator, or at least you don't make it worse.
>
> Therefore we can pool our efforts. You just need to write that test
> suite, because no one is going to write it for you.
>
> Julian
>
>
> On Fri, Nov 24, 2023 at 3:05 PM Mihai Budiu <mb...@gmail.com> wrote:
> >
> > This is a complicated question.
> >
> > Calcite is a framework with many components which you can assemble to
> build various translators, including source-to-source translators. But some
> components make program transformations that assume a certain semantics,
> which may not coincide with either Oracle or MySQL. For example, Calcite
> assumes that division with zero will cause a runtime exception, while MySQL
> and Oracle both assume that it returns NULL. This is part of the type
> inference rules of Calcite, and it's a fairly basic component.
> >
> > So if you use Calcite for compiling arithmetic expressions with division
> of non-nullable arguments, the result is non-nullable in Calcite, but
> nullable in either of these frameworks. Moreover, if you use Calcite's
> optimizer to simplify expressions that divide by 0, you may produce
> programs that give you different results. (There are ways you can override
> this behavior of Calcite if you really want to)
> >
> > This is just one example, but the problem with SQL dialects is that they
> have very subtle different semantics, so it may be actually very difficult
> to translate a program from one database dialect to another while
> preserving bit-exact results. I am assuming that this is the goal: produce
> the same results given the same input data and queries. If this is not the
> goal, you have to specify what relationship you expect between the original
> program and the translated program.
> >
> > There may be a nice well-behaved subset of SQL on which this is
> possible, but I am very skeptical, since the SQL standard does not even
> specify the meaning of very basic operations, like how rounding is done
> when converting decimals to integers.
> >
> > Mihai
> >
> > ________________________________
> > From: Julian Hyde <jh...@apache.org>
> > Sent: Friday, November 24, 2023 1:39 PM
> > To: dev@calcite.apache.org <de...@calcite.apache.org>
> > Subject: Re: Can calcite translate Oracle SQL to MySQL
> >
> > In general, yes. But I don't think there's any code to handle Oracle's
> > ROWNUM system column. Please log a jira case requesting to translate
> > ROWNUM.
> >
> > Cases https://issues.apache.org/jira/browse/CALCITE-5723 and
> > https://issues.apache.org/jira/browse/CALCITE-1786 are related, but
> > not what you are asking for.
> >
> > On Fri, Nov 24, 2023 at 12:40 AM Louis Hust <lo...@gmail.com>
> wrote:
> > >
> > > Hi all,
> > >
> > > Can calcite translate oracle sql to mysql ?
> > >
> > > Such as oracle: select * from t1 whre rownum < 10
> > > To  mysql: select * from t1 limit 9;
>

Re: Can calcite translate Oracle SQL to MySQL

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

Creating a high-quality Oracle to MySQL translator is a large task.
Calcite doesn't do it. In fact there is only one way to do it: spend a
considerable effort writing a large suite of compliance tests. Once
you have built that suite, getting the translator to pass that suite
is relatively easy.

I do believe that Calcite is a very good basis for that translator. We
have decomposed Calcite's functionality into areas such as lexical
differences (backticks versus double quotes around identifiers),
differences in type system (what does the TIMESTAMP type mean?),
differences in operator set (is there an NVL function?), and
differences in code generator (what syntax for LIMIT-OFFSET does this
DBMS support?). The net result is that when you fix a bug in your
Oracle-to-MySQL translator, you likely fix a bug in my
Oracle-to-Postgres translator, or at least you don't make it worse.

Therefore we can pool our efforts. You just need to write that test
suite, because no one is going to write it for you.

Julian


On Fri, Nov 24, 2023 at 3:05 PM Mihai Budiu <mb...@gmail.com> wrote:
>
> This is a complicated question.
>
> Calcite is a framework with many components which you can assemble to build various translators, including source-to-source translators. But some components make program transformations that assume a certain semantics, which may not coincide with either Oracle or MySQL. For example, Calcite assumes that division with zero will cause a runtime exception, while MySQL and Oracle both assume that it returns NULL. This is part of the type inference rules of Calcite, and it's a fairly basic component.
>
> So if you use Calcite for compiling arithmetic expressions with division of non-nullable arguments, the result is non-nullable in Calcite, but nullable in either of these frameworks. Moreover, if you use Calcite's optimizer to simplify expressions that divide by 0, you may produce programs that give you different results. (There are ways you can override this behavior of Calcite if you really want to)
>
> This is just one example, but the problem with SQL dialects is that they have very subtle different semantics, so it may be actually very difficult to translate a program from one database dialect to another while preserving bit-exact results. I am assuming that this is the goal: produce the same results given the same input data and queries. If this is not the goal, you have to specify what relationship you expect between the original program and the translated program.
>
> There may be a nice well-behaved subset of SQL on which this is possible, but I am very skeptical, since the SQL standard does not even specify the meaning of very basic operations, like how rounding is done when converting decimals to integers.
>
> Mihai
>
> ________________________________
> From: Julian Hyde <jh...@apache.org>
> Sent: Friday, November 24, 2023 1:39 PM
> To: dev@calcite.apache.org <de...@calcite.apache.org>
> Subject: Re: Can calcite translate Oracle SQL to MySQL
>
> In general, yes. But I don't think there's any code to handle Oracle's
> ROWNUM system column. Please log a jira case requesting to translate
> ROWNUM.
>
> Cases https://issues.apache.org/jira/browse/CALCITE-5723 and
> https://issues.apache.org/jira/browse/CALCITE-1786 are related, but
> not what you are asking for.
>
> On Fri, Nov 24, 2023 at 12:40 AM Louis Hust <lo...@gmail.com> wrote:
> >
> > Hi all,
> >
> > Can calcite translate oracle sql to mysql ?
> >
> > Such as oracle: select * from t1 whre rownum < 10
> > To  mysql: select * from t1 limit 9;

Re: Can calcite translate Oracle SQL to MySQL

[Mihai Budiu <mb...@gmail.com>]

This is a complicated question.

Calcite is a framework with many components which you can assemble to build various translators, including source-to-source translators. But some components make program transformations that assume a certain semantics, which may not coincide with either Oracle or MySQL. For example, Calcite assumes that division with zero will cause a runtime exception, while MySQL and Oracle both assume that it returns NULL. This is part of the type inference rules of Calcite, and it's a fairly basic component.

So if you use Calcite for compiling arithmetic expressions with division of non-nullable arguments, the result is non-nullable in Calcite, but nullable in either of these frameworks. Moreover, if you use Calcite's optimizer to simplify expressions that divide by 0, you may produce programs that give you different results. (There are ways you can override this behavior of Calcite if you really want to)

This is just one example, but the problem with SQL dialects is that they have very subtle different semantics, so it may be actually very difficult to translate a program from one database dialect to another while preserving bit-exact results. I am assuming that this is the goal: produce the same results given the same input data and queries. If this is not the goal, you have to specify what relationship you expect between the original program and the translated program.

There may be a nice well-behaved subset of SQL on which this is possible, but I am very skeptical, since the SQL standard does not even specify the meaning of very basic operations, like how rounding is done when converting decimals to integers.

Mihai

________________________________
From: Julian Hyde <jh...@apache.org>
Sent: Friday, November 24, 2023 1:39 PM
To: dev@calcite.apache.org <de...@calcite.apache.org>
Subject: Re: Can calcite translate Oracle SQL to MySQL

In general, yes. But I don't think there's any code to handle Oracle's
ROWNUM system column. Please log a jira case requesting to translate
ROWNUM.

Cases https://issues.apache.org/jira/browse/CALCITE-5723 and
https://issues.apache.org/jira/browse/CALCITE-1786 are related, but
not what you are asking for.

On Fri, Nov 24, 2023 at 12:40 AM Louis Hust <lo...@gmail.com> wrote:
>
> Hi all,
>
> Can calcite translate oracle sql to mysql ?
>
> Such as oracle: select * from t1 whre rownum < 10
> To  mysql: select * from t1 limit 9;

Re: Can calcite translate Oracle SQL to MySQL

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

In general, yes. But I don't think there's any code to handle Oracle's
ROWNUM system column. Please log a jira case requesting to translate
ROWNUM.

Cases https://issues.apache.org/jira/browse/CALCITE-5723 and
https://issues.apache.org/jira/browse/CALCITE-1786 are related, but
not what you are asking for.

On Fri, Nov 24, 2023 at 12:40 AM Louis Hust <lo...@gmail.com> wrote:
>
> Hi all,
>
> Can calcite translate oracle sql to mysql ?
>
> Such as oracle: select * from t1 whre rownum < 10
> To  mysql: select * from t1 limit 9;