[
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)
