Sql dialect specific operator unparsing

[Chris Baynes <ch...@contiamo.com>]

Currently there appears to be only one way for a sql operator to be
unparsed.
This is a problem for operations that look different depending on the
dialect currently used.

A good example is "FLOOR(datetime to timeUnit)"

In postgres this is: date_trunc('year', my_datetime)
In hsqldb: TRUNC ( my_datetime, 'YYYY' )
In oracle: TRUNC(my_datetime, 'YEAR')
In mysql: There's no direct equivalent in mysql

Right now if this would break if executed against a jdbc datasource, since
the sql generated just contains the "FLOOR(...".

I'm sure there must be other sql functions that also require similar
dialect switching.

I think it would be nice to be able to override the sql operator unparse
method on a dialect basis.
Right now the sql writer takes a dialect argument, but that's really just
for quoting and small formatting differences. The unparse method of every
operator could take a dialect argument, but I think there's probably a
better way.

Has anyone had any similar ideas and know how this could be approached?

Re: Sql dialect specific operator unparsing

[Julian Hyde <jh...@apache.org>]

I think that using the dialect inside the writer would be ideal. I see the AS operator already does this[1].

Julian

[1] https://insight.io/github.com/apache/calcite/blob/a11d14054e9c1d2ce22f60e11536f1885faaae7c/core/src/main/java/org/apache/calcite/sql/SqlAsOperator.java?line=79 



> On May 19, 2017, at 6:21 AM, Chris Baynes <ch...@contiamo.com> wrote:
> 
> Currently there appears to be only one way for a sql operator to be
> unparsed.
> This is a problem for operations that look different depending on the
> dialect currently used.
> 
> A good example is "FLOOR(datetime to timeUnit)"
> 
> In postgres this is: date_trunc('year', my_datetime)
> In hsqldb: TRUNC ( my_datetime, 'YYYY' )
> In oracle: TRUNC(my_datetime, 'YEAR')
> In mysql: There's no direct equivalent in mysql
> 
> Right now if this would break if executed against a jdbc datasource, since
> the sql generated just contains the "FLOOR(...".
> 
> I'm sure there must be other sql functions that also require similar
> dialect switching.
> 
> I think it would be nice to be able to override the sql operator unparse
> method on a dialect basis.
> Right now the sql writer takes a dialect argument, but that's really just
> for quoting and small formatting differences. The unparse method of every
> operator could take a dialect argument, but I think there's probably a
> better way.
> 
> Has anyone had any similar ideas and know how this could be approached?