Repository: ibis-project/ibis

Issue:
Add STARTS_WITH and ENDS_WITH
In ibis-project/ibis-bigquery#61 , I suggested to add the big query functions `STARTS_WITH` and `ENDS_WITH` to the BigQuery repo. @tswast, rightfully, pointed out that this could go into the main repo. 

Should I go ahead and make a PR? Any thing I should look out for?

I understand the operation definitions belong in _operations.py_. Would a PR also require an implementation for each backend?

My current implementation looks for BigQuery looks like this:

```python
import ibis.expr.rules as rlz
from ibis.expr.operations import Arg, ValueOp
from ibis.expr.types import StringValue
from ibis_bigquery import BigQueryExprTranslator

compiles = BigQueryExprTranslator.compiles


class StartsWith(ValueOp):
    value = Arg(rlz.string)
    start_string = Arg(rlz.string)
    output_type = rlz.shape_like("value", "bool")


def startswith(value, start_string):
    return StartsWith(value, start_string).to_expr()


class EndsWith(ValueOp):
    value = Arg(rlz.string)
    end_string = Arg(rlz.string)
    output_type = rlz.shape_like("value", "bool")


def endswith(value, start_string):
    return EndsWith(value, start_string).to_expr()


StringValue.startswith = startswith
StringValue.endswith = endswith


@compiles(StartsWith)
def _startswith(t, expr):
    # pull out the arguments to the expression
    value, start_string = expr.op().args
    # compile the argument
    t_value = t.translate(value)
    t_start = t.translate(start_string)
    # return a SQL expression that calls the BigQuery STARTS_WITH function
    return f"STARTS_WITH({t_value}, {t_start})"


@compiles(EndsWith)
def _endswith(t, expr):
    # pull out the arguments to the expression
    value, start_string = expr.op().args
    # compile the argument
    t_value = t.translate(value)
    t_start = t.translate(start_string)
    # return a SQL expression that calls the BigQuery STARTS_WITH function
    return f"ENDS_WITH({t_value}, {t_start})"

```

Discussion:
pep8speaks: Hello @saschahofmann! Thanks for opening this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:

* In the file [`ibis/backends/base/sql/alchemy/registry.py`](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/alchemy/registry.py):

> [Line 269:1](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/alchemy/registry.py#L269): [E302](https://duckduckgo.com/?q=pep8%20E302) expected 2 blank lines, found 1
> [Line 273:1](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/alchemy/registry.py#L273): [E302](https://duckduckgo.com/?q=pep8%20E302) expected 2 blank lines, found 1



* In the file [`ibis/backends/base/sql/registry/string.py`](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/registry/string.py):

> [Line 87:1](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/registry/string.py#L87): [W293](https://duckduckgo.com/?q=pep8%20W293) blank line contains whitespace
> [Line 88:18](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/registry/string.py#L88): [E225](https://duckduckgo.com/?q=pep8%20E225) missing whitespace around operator
> [Line 93:1](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/registry/string.py#L93): [E302](https://duckduckgo.com/?q=pep8%20E302) expected 2 blank lines, found 1
> [Line 95:1](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/registry/string.py#L95): [W293](https://duckduckgo.com/?q=pep8%20W293) blank line contains whitespace
> [Line 96:18](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/registry/string.py#L96): [E225](https://duckduckgo.com/?q=pep8%20E225) missing whitespace around operator
> [Line 99:58](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/backends/base/sql/registry/string.py#L99): [W292](https://duckduckgo.com/?q=pep8%20W292) no newline at end of file

* In the file [`ibis/expr/api.py`](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/expr/api.py):

> [Line 2890:1](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/expr/api.py#L2890): [E302](https://duckduckgo.com/?q=pep8%20E302) expected 2 blank lines, found 1
> [Line 2910:1](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/expr/api.py#L2910): [E302](https://duckduckgo.com/?q=pep8%20E302) expected 2 blank lines, found 1

* In the file [`ibis/expr/operations.py`](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/expr/operations.py):

> [Line 758:1](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/expr/operations.py#L758): [E302](https://duckduckgo.com/?q=pep8%20E302) expected 2 blank lines, found 1
> [Line 763:1](https://github.com/ibis-project/ibis/blob/662155ab085a2ee14ef87b934619cfb274723674/ibis/expr/operations.py#L763): [E302](https://duckduckgo.com/?q=pep8%20E302) expected 2 blank lines, found 1


saschahofmann: I added some tests, still working on the sqlalchemy tests.

As a general comment, I found that adding tests is pretty unintuitive. A lot of different approaches and some confusing stuff. e.g. why are the SQLAlchemy tests overwriting the `alltypes` table to be `functional_alltypes`? 

Not an expert but relying on children props in parent class methods seems like an anti-pattern (looking at `self.con` in `ExprTestCases`). I guess cleaning up the test suite is a huuuuge undertaking though 😅 
saschahofmann: Ok I added the sqlalchemy test but it fails. Anyone knows how I would make both queries return the same param names?

Test implementation:
```python
    def test_startswith(self):
        t = self.alltypes
        sat = self.sa_alltypes.alias('t0')

        expr = t.string_col.startswith('foo')
        sa_expr = sa.select([sat.c.string_col.startswith('foo').label('tmp')])

        assert self._compare_sqla(expr, sa_expr)
 ```

The ibis repr is
```SELECT (t0.string_col LIKE :string_col_1 || '%') AS tmp
FROM functional_alltypes AS to
```
And SQLAlchemy:
```SELECT (t0.string_col LIKE :param_1 || '%') AS tmp
FROM functional_alltypes AS to
```

So the only difference is in the param names (`string_col_1` and `param_1`). 


Do I need to test this in a backend as well?
saschahofmann: Looks like None of the similar string functions are tested in _test_sqlalchemy.py_. So I will leave it for now, let me know if I should try to include this there. Anyway, it seems to work just the parameter name is different.
saschahofmann: Added the functions to the backend test and to the pandas compiler. I think I also need to change something in the dask backend but let's see where else the tests fail. 
saschahofmann: Also looks like the black formatter is not in sync with the pep8 bot. I actually had to overwrite my on save changes from vscode to satisfy the bot but now the CI failed on the exact same line 😅 
 Is the formatted usually failing? Don't see what I changed in that file or should I merge these changes:

![image](https://user-images.githubusercontent.com/24508496/119510028-16ffa300-bd69-11eb-8450-ef8f6c008519.png)

saschahofmann: I made a silly mistake in the string compiler so hopefully, the impala and SQL tests pass now. I also added a pyspark implementation but I haven't used pyspark yet so might be worth checking that? 

I haven't managed to run the backend tests locally yet (see discussion above) but maybe someone could trigger the workflow again 🤓 .


saschahofmann: Is it possible that impala uses `||` as or operator rather than string concatenation? the error in the failing test suggests that much
datapythonista: > Is it possible that impala uses `||` as or operator rather than string concatenation? the error in the failing test suggests that much

I don't understand where the test failure is coming from, but Impala uses `OR`, as in standard SQL: https://impala.apache.org/docs/build/html/topics/impala_operators.html#ariaid-title13

And afaik, in SQL string concatenation is usually not done with an operator, but with a function. That seems to be the case for Impala: https://impala.apache.org/docs/build/html/topics/impala_string_functions.html#string_functions__concat

I hope this helps.
saschahofmann: I tried to find something about this in the Impala docs but only found this (contrary to how I understand the test failure):
> Logical/Boolean operators AND, OR, and NOT. Impala does not support the corresponding symbols &&, ||, and !.

If I use a function, I'd need to make sure it's the same for all maybe I just implement a separate case for Impala?
saschahofmann: Nvm I fixed another bug and now I am using the contact operator. If you could trigger the test suite again 🤞 
datapythonista: There is an issue with black. The other errors in the CI are unrelated to this PR.
saschahofmann: Ok should be fixed 
saschahofmann: Ok I think this is finally ready? 

Sorry for taking so long 😏 

Patch:
diff --git a/ci/deps/dask-min.yml b/ci/deps/dask-min.yml
index e29754213..9dc1b8f2e 100644
--- a/ci/deps/dask-min.yml
+++ b/ci/deps/dask-min.yml
@@ -1,2 +1,2 @@
-dask=2.22.0
+dask=2021.2.0
 pyarrow
diff --git a/ci/deps/dask.yml b/ci/deps/dask.yml
index cadb4acf0..0a8d37072 100644
--- a/ci/deps/dask.yml
+++ b/ci/deps/dask.yml
@@ -1,2 +1,2 @@
-dask
+dask=2021.5.0
 pyarrow
diff --git a/docs/source/release/index.rst b/docs/source/release/index.rst
index 4fdf12595..b59b1495f 100644
--- a/docs/source/release/index.rst
+++ b/docs/source/release/index.rst
@@ -12,6 +12,7 @@ Release Notes
    These release notes are for versions of ibis **1.0 and later**. Release
    notes for pre-1.0 versions of ibis can be found at :doc:`release-pre-1.0`
 
+* :feature:`2790` Add `startswith` and `endswith` operations
 * :feature:`2776` :feature:`2797` Allow more flexible return type for UDFs
 * :feature:`2779` Implement Clip in the Pyspark backend
 * :bug:`2770` Fix error when using reduction UDF that returns np.array in a grouped aggregation
diff --git a/ibis/backends/base/sql/alchemy/registry.py b/ibis/backends/base/sql/alchemy/registry.py
index 7c6696b04..e2e2774d2 100644
--- a/ibis/backends/base/sql/alchemy/registry.py
+++ b/ibis/backends/base/sql/alchemy/registry.py
@@ -267,6 +267,16 @@ def _string_like(t, expr):
     return result
 
 
+def _startswith(t, expr):
+    arg, start = expr.op().args
+    return t.translate(arg).startswith(t.translate(start))
+
+
+def _endswith(t, expr):
+    arg, start = expr.op().args
+    return t.translate(arg).endswith(t.translate(start))
+
+
 _cumulative_to_reduction = {
     ops.CumulativeSum: ops.Sum,
     ops.CumulativeMin: ops.Min,
@@ -444,6 +454,8 @@ sqlalchemy_operation_registry = {
     ops.StringLength: unary(sa.func.length),
     ops.StringReplace: fixed_arity(sa.func.replace, 3),
     ops.StringSQLLike: _string_like,
+    ops.StartsWith: _startswith,
+    ops.EndsWith: _endswith,
     # math
     ops.Ln: unary(sa.func.ln),
     ops.Exp: unary(sa.func.exp),
diff --git a/ibis/backends/base/sql/registry/main.py b/ibis/backends/base/sql/registry/main.py
index 45f645e3f..7d26a4c64 100644
--- a/ibis/backends/base/sql/registry/main.py
+++ b/ibis/backends/base/sql/registry/main.py
@@ -300,6 +300,8 @@ operation_registry = {
     ops.RegexExtract: fixed_arity('regexp_extract', 3),
     ops.RegexReplace: fixed_arity('regexp_replace', 3),
     ops.ParseURL: string.parse_url,
+    ops.StartsWith: string.startswith,
+    ops.EndsWith: string.endswith,
     # Timestamp operations
     ops.Date: unary('to_date'),
     ops.TimestampNow: lambda *args: 'now()',
diff --git a/ibis/backends/base/sql/registry/string.py b/ibis/backends/base/sql/registry/string.py
index e5fad0a87..598a4740a 100644
--- a/ibis/backends/base/sql/registry/string.py
+++ b/ibis/backends/base/sql/registry/string.py
@@ -80,3 +80,21 @@ def parse_url(translator, expr):
         return "parse_url({}, '{}', {})".format(
             arg_formatted, extract, key_fmt
         )
+
+
+def startswith(translator, expr):
+    arg, start = expr.op().args
+
+    arg_formatted = translator.translate(arg)
+    start_formatted = translator.translate(start)
+
+    return f"{arg_formatted} like concat({start_formatted}, '%')"
+
+
+def endswith(translator, expr):
+    arg, start = expr.op().args
+
+    arg_formatted = translator.translate(arg)
+    end_formatted = translator.translate(start)
+
+    return f"{arg_formatted} like concat('%', {end_formatted})"
diff --git a/ibis/backends/pandas/execution/strings.py b/ibis/backends/pandas/execution/strings.py
index 0071dae1f..8552eac2e 100644
--- a/ibis/backends/pandas/execution/strings.py
+++ b/ibis/backends/pandas/execution/strings.py
@@ -99,6 +99,16 @@ def execute_string_upper(op, data, **kwargs):
     return data.str.upper()
 
 
+@execute_node.register(ops.StartsWith, pd.Series)
+def execute_startswith(op, data, start, **kwargs):
+    return data.str.startswith(start)
+
+
+@execute_node.register(ops.EndsWith, pd.Series)
+def execute_endswith(op, data, end, **kwargs):
+    return data.str.endswith(end)
+
+
 @execute_node.register(ops.Capitalize, pd.Series)
 def execute_string_capitalize(op, data, **kwargs):
     return data.str.capitalize()
diff --git a/ibis/backends/pyspark/compiler.py b/ibis/backends/pyspark/compiler.py
index 3d3968ee2..f59575aca 100644
--- a/ibis/backends/pyspark/compiler.py
+++ b/ibis/backends/pyspark/compiler.py
@@ -438,6 +438,22 @@ def compile_not_contains(t, expr, scope, timecontext, **kwargs):
     return ~(col.isin(t.translate(op.options, scope, timecontext)))
 
 
+@compiles(ops.StartsWith)
+def compile_startswith(t, expr, scope, timecontext, **kwargs):
+    op = expr.op()
+    col = t.translate(op.arg, scope, timecontext)
+    start = t.translate(op.start, scope, timecontext)
+    return col.startswith(start)
+
+
+@compiles(ops.EndsWith)
+def compile_endswith(t, expr, scope, timecontext, **kwargs):
+    op = expr.op()
+    col = t.translate(op.arg, scope, timecontext)
+    end = t.translate(op.end, scope, timecontext)
+    return col.startswith(end)
+
+
 def compile_aggregator(
     t, expr, scope, timecontext, *, fn, context=None, **kwargs
 ):
diff --git a/ibis/expr/api.py b/ibis/expr/api.py
index f60edb1fd..79da5d9f7 100644
--- a/ibis/expr/api.py
+++ b/ibis/expr/api.py
@@ -2888,6 +2888,59 @@ def _string_join(self, strings):
     return ops.StringJoin(self, strings).to_expr()
 
 
+def _startswith(self, start):
+    """
+    Determine if `self` string starts with `start` string.
+
+    Parameters
+    ----------
+    start: string
+
+    Examples
+    --------
+    >>> import ibis
+    >>> text = ibis.literal('Ibis project)
+    >>> text.startswith('Ibis')
+    StartsWith[boolean]
+      Literal[string]
+        Ibis project
+      start:
+        Literal[string]
+          Ibis
+    Returns
+    -------
+    result : boolean
+    """
+    return ops.StartsWith(self, start).to_expr()
+
+
+def _endswith(self, end):
+    """
+    Determine if `self` string ends with `end` string.
+
+    Parameters
+    ----------
+    end: string
+
+    Examples
+    --------
+    >>> import ibis
+    >>> text = ibis.literal('Ibis project)
+    >>> text.endswith('project')
+    EndsWith[boolean]
+      Literal[string]
+        Ibis project
+      end:
+        Literal[string]
+          project
+
+    Returns
+    -------
+    result : boolean
+    """
+    return ops.EndsWith(self, end).to_expr()
+
+
 def _string_like(self, patterns):
     """
     Wildcard fuzzy matching function equivalent to the SQL LIKE directive. Use
@@ -3177,6 +3230,8 @@ _string_value_methods = {
     'find_in_set': _find_in_set,
     'split': _string_split,
     'join': _string_join,
+    'startswith': _startswith,
+    'endswith': _endswith,
     'lpad': _lpad,
     'rpad': _rpad,
     '__add__': _string_concat,
diff --git a/ibis/expr/operations.py b/ibis/expr/operations.py
index 63ee3f1db..e251e4002 100644
--- a/ibis/expr/operations.py
+++ b/ibis/expr/operations.py
@@ -756,6 +756,18 @@ class StringJoin(ValueOp):
         return rlz.shape_like(tuple(self.flat_args()), dt.string)
 
 
+class StartsWith(ValueOp):
+    arg = Arg(rlz.string)
+    start = Arg(rlz.string)
+    output_type = rlz.shape_like("arg", dt.boolean)
+
+
+class EndsWith(ValueOp):
+    arg = Arg(rlz.string)
+    end = Arg(rlz.string)
+    output_type = rlz.shape_like("arg", dt.boolean)
+
+
 class BooleanValueOp:
     pass
 
diff --git a/setup.py b/setup.py
index cfa5ec855..ec42ad98e 100644
--- a/setup.py
+++ b/setup.py
@@ -38,7 +38,7 @@ spark_requires = ['pyspark>=2.4.3']
 geospatial_requires = ['geoalchemy2', 'geopandas', 'shapely']
 
 dask_requires = [
-    'dask[dataframe, array]>=2.22.0',
+    'dask[dataframe, array]>=2021.1.1,<2021.5.1',
 ]
 
 all_requires = (


Test patch:
diff --git a/ibis/backends/tests/test_string.py b/ibis/backends/tests/test_string.py
index 68004ee5b..b74a94938 100644
--- a/ibis/backends/tests/test_string.py
+++ b/ibis/backends/tests/test_string.py
@@ -5,10 +5,13 @@ import ibis
 import ibis.expr.datatypes as dt
 
 
+def is_text_type(x):
+    return isinstance(x, str)
+
+
 def test_string_col_is_unicode(backend, alltypes, df):
     dtype = alltypes.string_col.type()
     assert dtype == dt.String(nullable=dtype.nullable)
-    is_text_type = lambda x: isinstance(x, str)  # noqa: E731
     assert df.string_col.map(is_text_type).all()
     result = alltypes.string_col.execute()
     assert result.map(is_text_type).all()
@@ -171,6 +174,16 @@ def test_string_col_is_unicode(backend, alltypes, df):
             id='length',
             marks=pytest.mark.xfail_backends(['omniscidb']),  # #2338
         ),
+        param(
+            lambda t: t.string_col.startswith('foo'),
+            lambda t: t.string_col.str.startswith('foo'),
+            id='startswith',
+        ),
+        param(
+            lambda t: t.string_col.endswith('foo'),
+            lambda t: t.string_col.str.endswith('foo'),
+            id='endswith',
+        ),
         param(
             lambda t: t.string_col.strip(),
             lambda t: t.string_col.str.strip(),
diff --git a/ibis/tests/expr/test_operations.py b/ibis/tests/expr/test_operations.py
index 152aaa51c..001222dbd 100644
--- a/ibis/tests/expr/test_operations.py
+++ b/ibis/tests/expr/test_operations.py
@@ -74,6 +74,8 @@ def test_ops_smoke():
     ops.StringReplace('asd', 'as', 'a')
     ops.StringSplit('asd', 's')
     ops.StringConcat(['s', 'e'])
+    ops.StartsWith('asd', 'as')
+    ops.EndsWith('asd', 'xyz')
 
 
 def test_instance_of_operation():
diff --git a/ibis/tests/expr/test_string.py b/ibis/tests/expr/test_string.py
index e634d29ca..b4030da9d 100644
--- a/ibis/tests/expr/test_string.py
+++ b/ibis/tests/expr/test_string.py
@@ -112,3 +112,13 @@ def test_add_radd(table, string_col):
     assert isinstance('bar' + literal('foo'), ir.StringScalar)
     assert isinstance(string_col + 'bar', ir.StringColumn)
     assert isinstance('bar' + string_col, ir.StringColumn)
+
+
+def test_startswith(table):
+    assert isinstance(table.g.startswith('foo'), ir.BooleanColumn)
+    assert isinstance(literal('bar').startswith('foo'), ir.BooleanScalar)
+
+
+def test_endswith(table):
+    assert isinstance(table.g.endswith('foo'), ir.BooleanColumn)
+    assert isinstance(literal('bar').endswith('foo'), ir.BooleanScalar)
diff --git a/ibis/tests/sql/test_compiler.py b/ibis/tests/sql/test_compiler.py
index 954ff2b55..0718a8c7f 100644
--- a/ibis/tests/sql/test_compiler.py
+++ b/ibis/tests/sql/test_compiler.py
@@ -808,6 +808,14 @@ class ExprTestCases:
 
         return expr1, expr2, expr3
 
+    def _case_startswith(self):
+        t1 = self.con.table('star1')
+        return t1.foo_id.startswith('foo')
+
+    def _case_endswith(self):
+        t1 = self.con.table('star1')
+        return t1.foo_id.endswith('foo')
+
 
 class TestSelectSQL(unittest.TestCase, ExprTestCases):
     @classmethod
@@ -2198,6 +2206,20 @@ FROM (
 
         assert result == expected
 
+    def test_startswith(self):
+        expr = self._case_startswith()
+        expected = """\
+SELECT `foo_id` like concat('foo', '%') AS `tmp`
+FROM star1"""
+        assert to_sql(expr) == expected
+
+    def test_endswith(self):
+        expr = self._case_endswith()
+        expected = """\
+SELECT `foo_id` like concat('%', 'foo') AS `tmp`
+FROM star1"""
+        assert to_sql(expr) == expected
+
 
 class TestUnions(unittest.TestCase, ExprTestCases):
     def setUp(self):