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;
