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;
