You may consider looking into Coral [1] which is based on Calcite, but
extends it with the translation requirements in mind, and as first class
problems.

[1] https://github.com/linkedin/coral

Thanks,
Walaa.


On Wed, Nov 27, 2024 at 9:20 AM Mihai Budiu <mbu...@gmail.com> wrote:

> I think that the task of translation between SQL dialects is hopeless.
> Consider just the type DECIMAL:
>
>
>   *
> Each dialect supports a different range of DECIMAL types, specified by
> scale and precision.
>   *
> Some dialects support negative scales, and some support scales > precision.
>   *
> Some dialects, like Postgres have infinite values in DECIMAL. This means
> that DECIMAL arithmetic can never cause a runtime error in Postgres, unlike
> all other dialects.
>   *
> The rounding rules for converting between DECIMAL and other types differ
> between dialects.
>   *
> The typing rules for DECIMAL arithmetic differ between dialects: what is
> the type of a/b when a and b are DECIMALs?
>   *
> The implicit typing of literals is different between dialects: is 1.0e0 a
> DECIMAL or some floating point value?
>
> The conclusion is that a SQL dialect can express many programs that cannot
> be expressed at all in other SQL dialects. What is a translator supposed to
> do with such programs?
>
> These are a set of problems for just one type, but when you consider the
> full set of types supported, the problem is exponentially larger.
>
> Just look at the differences between functions in dialects:
> https://calcite.apache.org/docs/reference.html Calcite has 4 versions of
> CONCAT_WS, with subtle semantic differences.
>
> Are you aware of any tool, anywhere that translates between 2 fixed SQL
> dialects while preserving the program's exact behavior in all cases?
> Building a tool for all possible dialects is again exponentially harder.
>
> Since we have established that exact semantics-preserving transformation
> of SQL programs between dialects is impossible, you need to set up a goal
> for the translation effort. What do you really hope to achieve? Then we can
> discuss about how it can be done.
>
> Mihai
>
> ________________________________
> From: Yanjing Wang <zhuangzixiao...@gmail.com>
> Sent: Wednesday, November 27, 2024 1:02 AM
> To: dev@calcite.apache.org <dev@calcite.apache.org>
> Subject: Re: [DISCUSS] Calcite as SQL translator, and dialect testing
>
> Hello, this discussion has been ongoing for a week. Let's move it forward.
> Does anyone else have any suggestions?
>
> Yanjing Wang <zhuangzixiao...@gmail.com> 于2024年11月19日周二 14:47写道:
>
> > 1. RelToSqlConverterTest
> > the class name implies tests conversion from RelNode to SQL, but now its
> > RelNode comes from different dialects with target sql. it is difficult
> for
> > me to understand the test case
> >
> > @Test void testNullCollation() {
> >   final String query = "select * from \"product\" order by
> \"brand_name\"";
> >   final String expected = "SELECT *\n"
> >       + "FROM \"foodmart\".\"product\"\n"
> >       + "ORDER BY \"brand_name\"";
> >   final String sparkExpected = "SELECT *\n"
> >       + "FROM `foodmart`.`product`\n"
> >       + "ORDER BY `brand_name` NULLS LAST";
> >   sql(query)
> >       .withPresto().ok(expected)
> >       .withSpark().ok(sparkExpected);
> > }
> >
> >
> > Why does the spark sql have 'NULLS LAST' in the end? the information is
> > missing if we don't add source rel or source dialect.
> >
> > 2. Dialect-to-dialect translation
> > I think it's necessary, dialect translation and materialized view
> > substitution are common in big data domain, it would be beneficial to
> make
> > Calcite more user-friendly for these scenarios.
> > Could we create end-to-end test cases that start with the source SQL of
> > one dialect and end with the target SQL of another (or the same) dialect?
> > We could also include user-defined materialized views in the process and
> > perform result comparison.
> >
> > Julian Hyde <jhyde.apa...@gmail.com> 于2024年11月19日周二 07:21写道:
> >
> >> A recent case, https://issues.apache.org/jira/browse/CALCITE-6693, "Add
> >> Source SQL Dialect to RelToSqlConverterTest”, implies that people are
> using
> >> Calcite to translate SQL from dialect to another. The test wanted to
> test
> >> translating a SQL string from Presto to Redshift. I pushed back on that
> >> case (and its PR) because that test is for translating RelNode to a SQL
> >> dialect, not about handling source dialects.
> >>
> >> Dialect-to-dialect translation is undoubtedly something that people do
> >> with Calcite. I think we should recognize that fact, and document how
> >> someone can use Calcite as a translator. When we have documented it, we
> can
> >> also add some tests.
> >>
> >> I am also worried about our dialect tests in general. The surface area
> to
> >> be tested is huge, and the tests are added haphazardly, so while many
> cases
> >> are tested there is a much greater set of cases that are not tested.
> >> Consider, for example, how testSelectQueryWithGroupByEmpty [1] tests
> >> against MySQL, Presto, StarRocks but not against BigQuery, Snowflake or
> >> Postgres. If we want our SQL dialect support to be high quality, we
> have to
> >> find a way to improve the coverage of our tests. I logged
> >> https://issues.apache.org/jira/browse/CALCITE-5529 with some ideas but
> I
> >> need help implement it.
> >>
> >> Julian
> >>
> >> [1]
> >>
> https://github.com/apache/calcite/blob/f2ec11fe7e23ecf2db903bc02c40609242993aad/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L577
> >>
> >>
> >>
>

Reply via email to