[jira] [Comment Edited] (CALCITE-5910) Add REGEXP_EXTRACT function (enabled in BigQuery library)

["Mihai Budiu (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17752187#comment-17752187 ] 

Mihai Budiu edited comment on CALCITE-5910 at 8/8/23 9:58 PM:
--------------------------------------------------------------

First, let me make the disclaimer that I am still relatively new to Calcite, so what I say below may be wrong. There may be some additional complications that I do not even know about.

I have contemplated the problem of regular expressions myself and I have not reached a happy conclusion. The difficulty is that there are *many* languages for describing regular expressions. They tend to have a large common area, but then they differ on corner cases, as the other comment points out. SQL has LIKE, but also the more complex SIMILAR. On top of that databases add their own regex dialects, for example Postgres has Posix regular expressions: https://www.postgresql.org/docs/15/functions-matching.html

Calcite already has RLIKE, and a bunch of REGEX functions for various dialects. RLIKE is *defined* to use the Java regex library, and that's very convenient for Calcite.

As I see it, these functions can be evaluated in two places:
- at compilation, using the PROJECT_REDUCE_EXPRESSIONS optimizer rule, when the plan is optimized
- at runtime, using whatever runtime you are using

These two evaluation methods better give the same results for constant expressions, otherwise users may have very unpleasant surprises.

If you need 100% compatibility with a certain dialect of regexes, I think you have two choices:
- use a Java library that faithfully implements the regex library
- prevent the compiler from ever evaluating such expressions at compile-time. There are least two ways you can do that: (1) treat the function like a user-defined function, or (2) claim it is non-deterministic

If you will always execute the Calcite-optimized code on your backend (e.g., BigQuery), maybe you can get away without implementing the functions in Java.

If you want to use Calcite as a runtime I believe that you have only one choice left, and you better pray that there is a well-tested Java library implementing your functions.


was (Author: JIRAUSER295926):
First, let me make the disclaimer that I am still relatively new to Calcite, so what I say below may be wrong. There may be some additional complications that I do not even know about.

I have contemplated the problem of regular expressions myself and I have not reached a happy conclusion. The difficulty is that there are *many* languages for describing regular expressions. They tend to have a large common area, but then they differ on corner cases, as the other comments points out. SQL has LIKE, but also the more complex SIMILAR. On top of that databases add their own regex dialects, for example Postgres has Posix regular expressions: https://www.postgresql.org/docs/15/functions-matching.html

Calcite already has RLIKE, and a bunch of REGEX functions for various dialects. RLIKE is *defined* to use the Java regex library, and that's very convenient for Calcite.

As I see it, these functions can be evaluated in two places:
- at compilation, using the PROJECT_REDUCE_EXPRESSIONS optimizer rule, when the plan is optimized
- at runtime, using whatever runtime you are using

These two evaluation methods better give the same results for constant expressions, otherwise users may have very unpleasant surprises.

If you need 100% compatibility with a certain dialect of regexes, I think you have two choices:
- use a Java library that faithfully implements the regex library
- prevent the compiler from ever evaluating such expressions at compile-time. There are least two ways you can do that: (1) treat the function like a user-defined function, or (2) claim it is non-deterministic

If you will always execute the Calcite-optimized code on your backend (e.g., BigQuery), maybe you can get away without implementing the functions in Java.

If you want to use Calcite as a runtime I believe that you have only one choice left, and you better pray that there is a well-tested Java library implementing your functions.

> Add REGEXP_EXTRACT function (enabled in BigQuery library)
> ---------------------------------------------------------
>
>                 Key: CALCITE-5910
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5910
>             Project: Calcite
>          Issue Type: Task
>            Reporter: Jerin John
>            Assignee: Jerin John
>            Priority: Major
>              Labels: pull-request-available
>
> Add support for [REGEXP_EXTRACT|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_extract] function from BigQuery.
> *{{REGEXP_EXTRACT(value, regexp[, position[, occurrence]])}}*
> Returns the substring in {{value}} that matches the regular expression {{{}regexp{}}}. Returns {{NULL}} if there is no match.
>  * If the regular expression contains a capturing group ({{{}(...){}}}), and there is a match for that capturing group, that match is returned. If there are multiple matches for a capturing group, the last match is returned.
>  * If {{position}} is specified, the search starts at this position in {{{}value{}}}, otherwise it starts at the beginning of {{{}value{}}}.
>  * If {{occurrence}} is specified, the search returns a specific occurrence of the {{regexp}} in {{{}value{}}}, otherwise returns the first match.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)