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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> > > > Sent: Friday, November 24, 2023 1:39 PM > > > To: [email protected] <[email protected]> > > > 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 <[email protected]> > > 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; > > >
