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 <mbu...@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 <dev@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 <louis.h...@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; >
