Hello Julian,
Thank you for your response!
I never knew about the SqlLibraryOperators class. Thanks for introducing me
to it.
If I have understood correctly what you are implying is what ever function
I am trying to use in a specific dialect, I need to verify it against an
equivalent function present in the Calcite's dialect and if I find a close
enough match then in the following way I might be able to achieve what I am
looking for.
if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) {
SqlUtil.unparseFunctionSyntax(SqlLibraryOperators.SUBSTR_ORACLE, writer,
call, false);
}
But you are also asking me to be mindful of how the function is supposed to
work as just mapping may not work and each function in each dialect might
be nuanced, and if they are I might need to implement them under
SqlLibraryOperators class.
Is that a fair understanding of your point of view? I just wanted to check
with you if we are on the same page. Thanks!
With regards,
Soumyadeep Mukhopadhyay.
On Fri, May 26, 2023 at 2:40 AM Julian Hyde <[email protected]> wrote:
> You could do something that substitutes RexCalls or SqlCalls of
> unsupported function calls with calls to supported functions.
>
> But be sure to remember that the semantics of functions does not depend on
> the target dialect. Function semantics are always in terms of Calcite’s
> dialect. This matters when two DBs have a function with the same name that
> have different semantics. Examples of this are the various SUBSTR_ORACLE,
> SUBSTR_BIG_QUERY, SUBSTR_MYSQL functions in SqlLibraryOperators, which all
> appear as “SUBSTR” in their respective DBs but have different semantics
> when length is negative or zero.
>
> Julian
>
>
> > On May 25, 2023, at 10:09 AM, Soumyadeep Mukhopadhyay <
> [email protected]> wrote:
> >
> > Hello Everyone,
> >
> > I wanted to know how one can leverage existing dialects to add new
> > functions or rename the occurrence of a specific function (or an
> operator).
> >
> > For example, a query in Spark SQL - "SELECT EXPLODE(ARRAY['1', '2'])"
> will
> > only work in Postgres if it is rewritten as "SELECT UNNEST(ARRAY['1',
> > '2'])". If I need to emulate this kind of substitution how should I
> > approach it?
> >
> > Another example that I was thinking of is, implementing functions that
> > don't exist in the current dialect like ARRAY_AGG in Snowflake. How can I
> > implement this in Calcite?
> >
> > Any suggestions would be much appreciated. Thank you for your time! :)
> >
> > With best regards,
> > Soumydeep.
>
>
