[jira] [Created] (CALCITE-5729) Add BYTE_LENGTH function (enabled in BigQuery library)
Dan Zou created CALCITE-5729: Summary: Add BYTE_LENGTH function (enabled in BigQuery library) Key: CALCITE-5729 URL: https://issues.apache.org/jira/browse/CALCITE-5729 Project: Calcite Issue Type: New Feature Reporter: Dan Zou Assignee: Dan Zou BYTE_LENGTH(value): Returns the length of the STRING or BYTES value in BYTES, regardless of whether the type of the value is STRING or BYTES. For more details: [BYTE_LENGTH|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#byte_length] -- This message was sent by Atlassian Jira (v8.20.10#820010)
Re: [ANNOUNCE] New committer: Tanner Clary
Congratulations, Tanner! Best, Dan Zou > 2023年5月29日 03:26，Julian Hyde 写道： > > Welcome to the community, Tanner! You've earned it. > > On Sun, May 28, 2023 at 5:11 AM Jacky Lau wrote: >> >> Congratulations, Tanner! >> >> Best regards, >> Jacky >> >> Jiajun Xie 于2023年5月27日周六 11:28写道： >> >>> Congratulations and welcome, Tanner! >>> >>> On Sat, 27 May 2023 at 02:46, Sergey Nuyanzin wrote: >>> Congratulations, Tanner! On Fri, May 26, 2023 at 8:40 PM Bertil Chapuis >>> wrote: > Congratulations and welcome! > > Bertil > >> On 26 May 2023, at 18:58, Tanner Clary >>> .INVALID> > wrote: >> >> Hey everyone, >> >> Thank you all for the warm welcome! I have really enjoyed >>> contributing to >> the project so far and look forward to continuing to do so. >> >> About me: I graduated in May 2022 and since then I've been working on >> Looker @ Google. I'm eager to learn more about other areas of the project >> and hopefully collaborate with many of you along the way. >> >> Thanks again, >> Tanner Clary >> >> >> On Fri, May 26, 2023 at 5:12 AM Michael Mior >>> wrote: >> >>> Congratulations and welcome Tanner! >>> -- >>> Michael Mior >>> mm...@apache.org >>> >>> >>> On Fri, May 26, 2023 at 5:05 AM Stamatis Zampetakis < zabe...@gmail.com> >>> wrote: >>> Apache Calcite's Project Management Committee (PMC) has invited Tanner Clary to become a committer, and we are pleased to announce that >>> they have accepted. In less than 5 months, Tanner has introduced, enabled, and tested over 30 SQL functions in Calcite. They have been a driving force in improving the BigQuery dialect and by now an expert in library and parser changes. Tanner, welcome, thank you for your contributions, and we look forward to your further interactions with the community! If you wish, >>> please feel free to tell us more about yourself and what you are working >>> on. As your first commit, please add yourself to the contributors list [1] and the community page will re-generate [2]. Stamatis (on behalf of the Apache Calcite PMC) [1] > >>> https://github.com/apache/calcite/blob/main/site/_data/contributors.yml [2] https://calcite.apache.org/community/#project-members >>> > > -- Best regards, Sergey >>>
[jira] [Created] (CALCITE-5728) Add ARRAY_TO_STRING function (enabled in BigQuery library)
Dan Zou created CALCITE-5728: Summary: Add ARRAY_TO_STRING function (enabled in BigQuery library) Key: CALCITE-5728 URL: https://issues.apache.org/jira/browse/CALCITE-5728 Project: Calcite Issue Type: New Feature Reporter: Dan Zou Assignee: Dan Zou ARRAY_TO_STRING: Returns a concatenation of the elements in array_expression as a STRING. For more details: [ARRAY_TO_STRING|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#array_to_string] -- This message was sent by Atlassian Jira (v8.20.10#820010)
Re: [ANNOUNCE] New committer: Tanner Clary
Welcome to the community, Tanner! You've earned it. On Sun, May 28, 2023 at 5:11 AM Jacky Lau wrote: > > Congratulations, Tanner! > > Best regards, > Jacky > > Jiajun Xie 于2023年5月27日周六 11:28写道： > > > Congratulations and welcome, Tanner! > > > > On Sat, 27 May 2023 at 02:46, Sergey Nuyanzin wrote: > > > > > Congratulations, Tanner! > > > > > > On Fri, May 26, 2023 at 8:40 PM Bertil Chapuis > > wrote: > > > > > > > Congratulations and welcome! > > > > > > > > Bertil > > > > > > > > > On 26 May 2023, at 18:58, Tanner Clary > > .INVALID> > > > > wrote: > > > > > > > > > > Hey everyone, > > > > > > > > > > Thank you all for the warm welcome! I have really enjoyed > > contributing > > > to > > > > > the project so far and look forward to continuing to do so. > > > > > > > > > > About me: I graduated in May 2022 and since then I've been working on > > > > > Looker @ Google. I'm eager to learn more about other areas of the > > > project > > > > > and hopefully collaborate with many of you along the way. > > > > > > > > > > Thanks again, > > > > > Tanner Clary > > > > > > > > > > > > > > > On Fri, May 26, 2023 at 5:12 AM Michael Mior > > wrote: > > > > > > > > > >> Congratulations and welcome Tanner! > > > > >> -- > > > > >> Michael Mior > > > > >> mm...@apache.org > > > > >> > > > > >> > > > > >> On Fri, May 26, 2023 at 5:05 AM Stamatis Zampetakis < > > > zabe...@gmail.com> > > > > >> wrote: > > > > >> > > > > >>> Apache Calcite's Project Management Committee (PMC) has invited > > > Tanner > > > > >>> Clary to become a committer, and we are pleased to announce that > > they > > > > >>> have accepted. > > > > >>> > > > > >>> In less than 5 months, Tanner has introduced, enabled, and tested > > > over > > > > >>> 30 SQL functions in Calcite. They have been a driving force in > > > > >>> improving the BigQuery dialect and by now an expert in library and > > > > >>> parser changes. > > > > >>> > > > > >>> Tanner, welcome, thank you for your contributions, and we look > > > forward > > > > >>> to your further interactions with the community! If you wish, > > please > > > > >>> feel free to tell us more about yourself and what you are working > > on. > > > > >>> > > > > >>> As your first commit, please add yourself to the contributors list > > > [1] > > > > >>> and the community page will re-generate [2]. > > > > >>> > > > > >>> Stamatis (on behalf of the Apache Calcite PMC) > > > > >>> > > > > >>> [1] > > > > >>> > > > > > > https://github.com/apache/calcite/blob/main/site/_data/contributors.yml > > > > >>> > > > > >>> [2] https://calcite.apache.org/community/#project-members > > > > >>> > > > > >> > > > > > > > > > > > > > > -- > > > Best regards, > > > Sergey > > > > >
Re: Add new functions in existing dialects
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 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 <
> soumyamy...@gmail.com> 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.
>
>
Re: Implement a filter rule that adds a predicate
Soumyadeep, To me, your approach is the simplest one. If you want to try a planner rule, then you must consider several things, which will be more complicated: - The rule only matches a pattern, e.g., a Filter on top of Aggregate. This means that if your RelNode tree has multiple matches, you cannot easily distinguish them (I assume you only want to match the outer most one). - After you have done the work in RelNode level, then you need to transform it to SqlNode again to get SQL, that means you need to use `RelToSqlConverter`. Soumyadeep Mukhopadhyay 于2023年5月27日周六 18:48写道： > Hello all, > > *I was trying to implement a filter rule which can add an extra predicate*, > for example: > *look for GROUP BY clause in a query, check if HAVING is present, if not > add HAVING COUNT(*)>120.* > > *I was trying to do the following: * > https://gist.github.com/Soumyadeep-github/47c1131bf02149af995d8e3bccb3ee67 > > *My approach in this gist *: > get a SqlNode (parsed query) ==> visit each SqlCall node recursively ==> > find SqlSelect (since there's getHaving and setHaving) ==> create a new > dummy query and extract having from the query ==> > set Having for the current query at hand via the dummy query. > > *I was wondering if* the same could be achieved in a planner rule. It may > not be a good idea but, for the sake of trying it out, do you think it is > possible? If yes, could you please give me a rough road map. > > Thank you for your time and patience! > > With regards, > Soumyadeep Mukhopadhyay. > -- Best, Benchao Li
Re: [ANNOUNCE] New committer: Tanner Clary
Congratulations, Tanner! Best regards, Jacky Jiajun Xie 于2023年5月27日周六 11:28写道： > Congratulations and welcome, Tanner! > > On Sat, 27 May 2023 at 02:46, Sergey Nuyanzin wrote: > > > Congratulations, Tanner! > > > > On Fri, May 26, 2023 at 8:40 PM Bertil Chapuis > wrote: > > > > > Congratulations and welcome! > > > > > > Bertil > > > > > > > On 26 May 2023, at 18:58, Tanner Clary > .INVALID> > > > wrote: > > > > > > > > Hey everyone, > > > > > > > > Thank you all for the warm welcome! I have really enjoyed > contributing > > to > > > > the project so far and look forward to continuing to do so. > > > > > > > > About me: I graduated in May 2022 and since then I've been working on > > > > Looker @ Google. I'm eager to learn more about other areas of the > > project > > > > and hopefully collaborate with many of you along the way. > > > > > > > > Thanks again, > > > > Tanner Clary > > > > > > > > > > > > On Fri, May 26, 2023 at 5:12 AM Michael Mior > wrote: > > > > > > > >> Congratulations and welcome Tanner! > > > >> -- > > > >> Michael Mior > > > >> mm...@apache.org > > > >> > > > >> > > > >> On Fri, May 26, 2023 at 5:05 AM Stamatis Zampetakis < > > zabe...@gmail.com> > > > >> wrote: > > > >> > > > >>> Apache Calcite's Project Management Committee (PMC) has invited > > Tanner > > > >>> Clary to become a committer, and we are pleased to announce that > they > > > >>> have accepted. > > > >>> > > > >>> In less than 5 months, Tanner has introduced, enabled, and tested > > over > > > >>> 30 SQL functions in Calcite. They have been a driving force in > > > >>> improving the BigQuery dialect and by now an expert in library and > > > >>> parser changes. > > > >>> > > > >>> Tanner, welcome, thank you for your contributions, and we look > > forward > > > >>> to your further interactions with the community! If you wish, > please > > > >>> feel free to tell us more about yourself and what you are working > on. > > > >>> > > > >>> As your first commit, please add yourself to the contributors list > > [1] > > > >>> and the community page will re-generate [2]. > > > >>> > > > >>> Stamatis (on behalf of the Apache Calcite PMC) > > > >>> > > > >>> [1] > > > >>> > > > > https://github.com/apache/calcite/blob/main/site/_data/contributors.yml > > > >>> > > > >>> [2] https://calcite.apache.org/community/#project-members > > > >>> > > > >> > > > > > > > > > > -- > > Best regards, > > Sergey > > >
