[jira] [Comment Edited] (CALCITE-3959) Implement INSTR function

["Joey Moore (Jira)" <ji...@apache.org>]

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

Joey Moore edited comment on CALCITE-3959 at 4/5/23 8:12 PM:
-------------------------------------------------------------

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
 ** Is desugaring into a dialect specific function a bad idea?
 * 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

 


was (Author: JIRAUSER299634):
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)