.start_at doesn't work with filtering using "in" operator

ADR-007 commented 1 year ago

Thanks for stopping by to let us know something could be better!

PLEASE READ: If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.

Please run down the following list and make sure you've tried the usual "quick fixes":

Search the issues already opened: https://github.com/googleapis/python-firestore/issues
Search StackOverflow: https://stackoverflow.com/questions/tagged/google-cloud-platform+python

If you are still having issues, please be sure to include as much information as possible:

Environment details

OS type and version: MacOS 13.2.1
Python version: 3.10.10
pip version: 23.0
google-cloud-firestore version: 2.11.0

Steps to reproduce

Make a query with "in" operator
Add "start_at" with an object in order to fetch the next page
Execute the query

Code example

    query = client.collection('players').where('status', 'in', ['active', 'deleted']).limit(2)

    page1 = query.get()
    page2 = query.start_after(page1[-1]).get()  # error here:
    # google.api_core.exceptions.InvalidArgument:
    # 400 order by clause cannot contain a field with an equality filter status

    # (it adds 'status' to the order by clause)

Stack trace

Traceback (most recent call last):
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/api_core/grpc_helpers.py", line 162, in error_remapped_callable
    return _StreamingResponseIterator(
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/api_core/grpc_helpers.py", line 88, in __init__
    self._stored_first_result = next(self._wrapped)
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/grpc/_channel.py", line 475, in __next__
    return self._next()
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/grpc/_channel.py", line 881, in _next
    raise self
grpc._channel._MultiThreadedRendezvous: <_MultiThreadedRendezvous of RPC that terminated with:
    status = StatusCode.INVALID_ARGUMENT
    details = "order by clause cannot contain a field with an equality filter status"
    debug_error_string = "UNKNOWN:Error received from peer ipv4:142.250.184.234:443 {grpc_message:"order by clause cannot contain a field with an equality filter status", grpc_status:3, created_time:"2023-04-08T03:47:05.648492+02:00"}"
>

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/user/Projects/project/project/test_e2e/test_start_at.py", line 14, in test
    page2 = query.start_after(page1[-1]).get()  # error here:
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/cloud/firestore_v1/query.py", line 172, in get
    return list(result)
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/cloud/firestore_v1/query.py", line 287, in stream
    response_iterator, expected_prefix = self._get_stream_iterator(
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/cloud/firestore_v1/query.py", line 221, in _get_stream_iterator
    response_iterator = self._client._firestore_api.run_query(
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/cloud/firestore_v1/services/firestore/client.py", line 1309, in run_query
    response = rpc(
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/api_core/gapic_v1/method.py", line 113, in __call__
    return wrapped_func(*args, **kwargs)
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/api_core/retry.py", line 349, in retry_wrapped_func
    return retry_target(
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/api_core/retry.py", line 191, in retry_target
    return target()
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/api_core/timeout.py", line 120, in func_with_timeout
    return func(*args, **kwargs)
  File "/Users/user/Projects/project/project/.venv/lib/python3.10/site-packages/google/api_core/grpc_helpers.py", line 166, in error_remapped_callable
    raise exceptions.from_grpc_error(exc) from exc
google.api_core.exceptions.InvalidArgument: 400 order by clause cannot contain a field with an equality filter status

ADR-007 commented 1 year ago

The solution is to modify this part of BaseQuery._normalize_orders:

            should_order = [
                _enum_from_op_string(key)
                for key in _COMPARISON_OPERATORS
                if key not in (_EQ_OP, "array_contains")
            ]

by adding new "in" not the exception list.

I can provide PR. I would like to have this fix ASAP because it push me to do monkey patching :(

Also, it probably makes sense to add "orders" for ">", ">=", "<", and "<=" operators only. Because in other cases it requires one more index. (not just "decreasing/increasing" index in addition to "array" index)

daniel-sanche commented 6 months ago

Dropping to P3 because there is a workaround, but I would still like to address this. Let me know if it's still currently causing issues, and I can try to prioritize this

daniel-sanche commented 2 weeks ago

This is still currently on the backlog

googleapis / python-firestore