[
https://issues.apache.org/jira/browse/CALCITE-5644?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tanner Clary resolved CALCITE-5644.
-----------------------------------
Fix Version/s: 1.36.0
Resolution: Fixed
Merged via
[bcf6bd8|https://github.com/apache/calcite/commit/bcf6bd8577b25c563b1c597c70704594a18ca1a3],
thanks for the review [~julianhyde]!
> Add CONTAINS_SUBSTR function (enabled for BigQuery)
> ---------------------------------------------------
>
> Key: CALCITE-5644
> URL: https://issues.apache.org/jira/browse/CALCITE-5644
> Project: Calcite
> Issue Type: Task
> Reporter: Tanner Clary
> Assignee: Tanner Clary
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> BigQuery offers the
> [CONTAINS_SUBSTR|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#contains_substr]
> function that returns {{TRUE}} if a substring is present in an expression
> and {{FALSE}} if it is not. A basic example of this may be seen in [1].
> The expression can take many forms (more info in the linked doc) which makes
> its implementation more complex than other string functions that only accept
> arguments of type {{STRING}}. For instance, the expression to be searched can
> be a column or table reference.
> The function also has an optional third argument called {{json_scope}} where
> the user can indicate the scope of JSON data (keys, values, or both) to be
> searched for the substring.
> I am curious if anyone has thoughts on how the search of rows or tables
> could be implemented. I have a basic implementation (that supports
> expressions of type {{STRING}} and nothing else) that I will open a draft PR
> for as a starting point. To me, the challenge is implementing the additional
> features like the {{JSON_SCOPE}} argument (seen in [2]) and performing a
> cross field search as seen in [3].
> [1] {{SELECT CONTAINS_SUBSTR("hello", "he");}} would return {{TRUE}}.
> [2] SELECT CONTAINS_SUBSTR(JSON '("lunch":"soup")', "lunch",
> json_scope=>"JSON_VALUES") AS result;
> would return {{FALSE}}.
> [3]
> {{SELECT *
> FROM Recipes
> WHERE CONTAINS_SUBSTR(
> (SELECT AS STRUCT Recipes.* EXCEPT (Lunch, Dinner)),
> 'potato'
> );}} would return:
> ||Breakfast||Lunch||Dinner||
> |Potato pancakes|Toasted cheese sandwich|Beef stroganoff|
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
