[jira] [Commented] (CALCITE-3959) Implement INSTR function
[
https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17719106#comment-17719106
]
Julian Hyde commented on CALCITE-3959:
--
Final review now.
In
[julianhyde/3959-instr|https://github.com/julianhyde/calcite/tree/3959-instr] I
factored the "position" function into private "positionForwards" and
"positionBackwards" functions, and tried to simplify the 'next occurrence'
logic. The mix of 0-based and 1-based is still a mess.
> Implement INSTR function
>
>
> Key: CALCITE-3959
> URL: https://issues.apache.org/jira/browse/CALCITE-3959
> Project: Calcite
> Issue Type: Wish
>Reporter: xzh_dz
>Assignee: Joey Moore
>Priority: Major
> Labels: pull-request-available
>
> [BiqQuery|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#instr]
> and
> [Oracle|https://docs.oracle.com/cd/B13789_01/server.101/b10759/functions058.htm]
> both support functionally identical INSTR(source_value, search_value[,
> position[, occurrence]]) functions which accepts 2 (character strings or
> binary strings), 1 optional int representing position, and 1 optional int
> representing occurrence.
> Occurrence and position are assigned a default value of 1 if not specified.
> The function returns the 1-based position of the nth occurrence of the 2nd
> operand in the 1st operand where n is defined by the 4th operand. The
> function begins searching at the 1-based position specified in the 3rd
> operand.
> The function also supports negative position values, with -1 indicating the
> last character, and will search backwards from the position specified in that
> case.
> Returns 0 if:
> * No match is found.
> * If occurrence is greater than the number of matches found.
> * If position is greater than the length of source_value.
> Returns NULL if:
> * Any input argument is NULL.
> Returns an error if:
> * position is 0.
> * occurrence is 0 or negative.
> EXAMPLE: {{INSTR("abc", "bc")}} would return 2.
> EXAMPLE: {{INSTR("abcabc", "bc", 3)}} would return 5.
> EXAMPLE: {{INSTR("abcabc", "bc", -1, 1)}} would return 5.
> EXAMPLE: {{INSTR("abcabc", "bc", -1, 2)}} would return 2.
>
> MySQL also has an [INSTR|https://www.w3schools.com/sql/func_mysql_instr.asp]
> function, the functionality of which is a subset of the INSTR present in BQ
> and Oracle. MySQL INSTR only takes 2 parameters and returns the first
> occurrence of the search value in the source value.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-3959) Implement INSTR function
[
https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17710436#comment-17710436
]
Julian Hyde commented on CALCITE-3959:
--
Again, I recommend adding two arguments to POSITION and desugaring all INSTR
variants to it.
> Implement INSTR function
>
>
> Key: CALCITE-3959
> URL: https://issues.apache.org/jira/browse/CALCITE-3959
> Project: Calcite
> Issue Type: Wish
>Reporter: xzh_dz
>Assignee: Joey Moore
>Priority: Major
>
> [BiqQuery|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#instr]
> and
> [Oracle|https://docs.oracle.com/cd/B13789_01/server.101/b10759/functions058.htm]
> both support functionally identical INSTR(source_value, search_value[,
> position[, occurrence]]) functions which accepts 2 (character strings or
> binary strings), 1 optional int representing position, and 1 optional int
> representing occurrence.
> Occurrence and position are assigned a default value of 1 if not specified.
> The function returns the 1-based position of the nth occurrence of the 2nd
> operand in the 1st operand where n is defined by the 4th operand. The
> function begins searching at the 1-based position specified in the 3rd
> operand.
> The function also supports negative position values, with -1 indicating the
> last character, and will search backwards from the position specified in that
> case.
> Returns 0 if:
> * No match is found.
> * If occurrence is greater than the number of matches found.
> * If position is greater than the length of source_value.
> Returns NULL if:
> * Any input argument is NULL.
> Returns an error if:
> * position is 0.
> * occurrence is 0 or negative.
> EXAMPLE: {{INSTR("abc", "bc")}} would return 2.
> EXAMPLE: {{INSTR("abcabc", "bc", 3)}} would return 5.
> EXAMPLE: {{INSTR("abcabc", "bc", -1, 1)}} would return 5.
> EXAMPLE: {{INSTR("abcabc", "bc", -1, 2)}} would return 2.
>
> MySQL also has an [INSTR|https://www.w3schools.com/sql/func_mysql_instr.asp]
> function, the functionality of which is a subset of the INSTR present in BQ
> and Oracle. MySQL INSTR only takes 2 parameters and returns the first
> occurrence of the search value in the source value.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-3959) Implement INSTR function
[
https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17710342#comment-17710342
]
Will Noble commented on CALCITE-3959:
-
- BigQuery and Oracle have identical functions called {{INSTR}} (takes string,
substring, position, occurence)
- MySQL has a function called {{INSTR}} that's similar to BigQuery's {{STRPOS}}
or Postgres' {{POSITION}} (takes only string and substring; equivalent to the
special case of BQ's {{INSTR}} where position=1 and occurence=1).
I'm not totally clear on how to look up "standard" SQL functions (i.e. to
divide functions between {{SqlStdOperatorTable}} and {{SqlLibraryOperators}})
so I'll just assume that Postgres is basically standard SQL.
So, here we have a situation where there's a standard SQL function (called
{{POSITION}} in Postgres) that's equivalent to a special case of a more general
library-specific function (called {{INSTR}} in BigQuery). Does it generally
make sense to de-sugar the standard function into the library-specific function
in this case? It seems like this would add a little complexity to the
[{{unparseCall}}|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java#L152]
method of virtually every {{SqlDialect}}, introducing the possibility of a
runtime exception if {{occurence}} is ever anything besides a literal {{1}}
(although this runtime exception may be unavoidable if parsing in BQ and
unparsing in Postgres, for example).
Seems like it should work fine, just curious how desirable it is for code
health.
> Implement INSTR function
>
>
> Key: CALCITE-3959
> URL: https://issues.apache.org/jira/browse/CALCITE-3959
> Project: Calcite
> Issue Type: Wish
>Reporter: xzh_dz
>Assignee: Joey Moore
>Priority: Major
>
> [BiqQuery|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#instr]
> and
> [Oracle|https://docs.oracle.com/cd/B13789_01/server.101/b10759/functions058.htm]
> both support functionally identical INSTR(source_value, search_value[,
> position[, occurrence]]) functions which accepts 2 (character strings or
> binary strings), 1 optional int representing position, and 1 optional int
> representing occurrence.
> Occurrence and position are assigned a default value of 1 if not specified.
> The function returns the 1-based position of the nth occurrence of the 2nd
> operand in the 1st operand where n is defined by the 4th operand. The
> function begins searching at the 1-based position specified in the 3rd
> operand.
> The function also supports negative position values, with -1 indicating the
> last character, and will search backwards from the position specified in that
> case.
> Returns 0 if:
> * No match is found.
> * If occurrence is greater than the number of matches found.
> * If position is greater than the length of source_value.
> Returns NULL if:
> * Any input argument is NULL.
> Returns an error if:
> * position is 0.
> * occurrence is 0 or negative.
> EXAMPLE: {{INSTR("abc", "bc")}} would return 2.
> EXAMPLE: {{INSTR("abcabc", "bc", 3)}} would return 5.
> EXAMPLE: {{INSTR("abcabc", "bc", -1, 1)}} would return 5.
> EXAMPLE: {{INSTR("abcabc", "bc", -1, 2)}} would return 2.
>
> MySQL also has an [INSTR|https://www.w3schools.com/sql/func_mysql_instr.asp]
> function, the functionality of which is a subset of the INSTR present in BQ
> and Oracle. MySQL INSTR only takes 2 parameters and returns the first
> occurrence of the search value in the source value.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-3959) Implement INSTR function
[ https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17709181#comment-17709181 ] Julian Hyde commented on CALCITE-3959: -- Please fill out the description. What are its arguments? What is its behavior? What libraries should it belong to? Give an example. You can't do design until you have written down the specification. > Implement INSTR function > > > Key: CALCITE-3959 > URL: https://issues.apache.org/jira/browse/CALCITE-3959 > Project: Calcite > Issue Type: Wish >Reporter: xzh_dz >Assignee: Joey Moore >Priority: Major > > Implement INSTR function -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-3959) Implement INSTR function
[ https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17709063#comment-17709063 ] Joey Moore commented on CALCITE-3959: - I've started work on this issue and have a few questions about the best way to implement. The [INSTR|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#instr] function seems to be a superset of the functionality of [STRPOS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#strpos] and the standard SQL [Position|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/fun/SqlPositionFunction.java] function that is implemented in calcite. It can perform the same tasks as those as well as do backwards search with a negative position operand, and has an occurrence operand that doesn't appear in any of the other functions. Because of this there seem to be many ways to implement it, * desugar into position when occurrence = 1 and position > 0 * desugar Position and STRPOS into INSTR as it's the most general * implement it separately of the other functions I'm still quite new to the codebase, so I'm not sure the overreaching effects of any of these. [~julianhyde] please let me know what you think would be best, or if I've missed a more elegant solution > Implement INSTR function > > > Key: CALCITE-3959 > URL: https://issues.apache.org/jira/browse/CALCITE-3959 > Project: Calcite > Issue Type: Wish >Reporter: xzh_dz >Assignee: Joey Moore >Priority: Major > > Implement INSTR function -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-3959) Implement INSTR function
[ https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17093786#comment-17093786 ] Julian Hyde commented on CALCITE-3959: -- What libraries would this belong to? It's not standard. > Implement INSTR function > > > Key: CALCITE-3959 > URL: https://issues.apache.org/jira/browse/CALCITE-3959 > Project: Calcite > Issue Type: Wish >Reporter: xzh_dz >Priority: Major > > Implement INSTR function -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3959) Implement INSTR function
[ https://issues.apache.org/jira/browse/CALCITE-3959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17092544#comment-17092544 ] Jin Xing commented on CALCITE-3959: --- Do we also need to update reference.md ? BTW, please link the PR into this Jira. > Implement INSTR function > > > Key: CALCITE-3959 > URL: https://issues.apache.org/jira/browse/CALCITE-3959 > Project: Calcite > Issue Type: Wish >Reporter: xzh_dz >Priority: Major > > Implement INSTR function -- This message was sent by Atlassian Jira (v8.3.4#803005)
